前端之家收集整理的这篇文章主要介绍了
postgresql部署[主从搭建]，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
postgresql部署
1.环境信息
| 操作系统 |
IP地址 |
机器名 |
安装服务 |
备注 |
| CentOS6.5 |
172.24.8.122 |
test-8p122-lisong |
postgresql9.6 |
主 |
| CentOS6.5 |
172.24.8.123 |
test-8p123-lisong |
postgresql9.6 |
从 |
2.安装
2.1.下载安装包
[root@test-8p122-lisong ~]# yum install http://172.24.8.123:8090/postgresql/centos6/9.6/pgdg-centos96-9.6-3.noarch.rpm
[root@test-8p123-lisong ~]# yum install http://172.24.8.123:8090/postgresql/centos6/9.6/pgdg-centos96-9.6-3.noarch.rpm
#安装成功如下
Installing:
pgdg-centos96 noarch 9.6-3 /pgdg-centos96-9.6-3.noarch 2.7 k
Install 1 Package(s)
Total size: 2.7 k
Installed size: 2.7 k
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : pgdg-centos96-9.6-3.noarch 1/1
Verifying : pgdg-centos96-9.6-3.noarch 1/1
Installed:
pgdg-centos96.noarch 0:9.6-3
Complete!
2.2.安装客户端
[root@test-8p122-lisong ~]# groupadd postgres
[root@test-8p123-lisong ~]# groupadd postgres
[root@test-8p122-lisong ~]# useradd -g postgres postgres
[root@test-8p123-lisong ~]# useradd -g postgres postgres
[root@test-8p122-lisong ~]# passwd postgres
[root@test-8p123-lisong ~]# passwd postgres
[root@test-8p122-lisong ~]# yum install postgresql96
[root@test-8p123-lisong ~]# yum install postgresql96
#安装成功如下
Total 73 kB/s | 1.6 MB 00:22
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : postgresql96-libs-9.6.3-4PGDG.rhel6.x86_64 1/2
Installing : postgresql96-9.6.3-4PGDG.rhel6.x86_64 2/2
Verifying : postgresql96-libs-9.6.3-4PGDG.rhel6.x86_64 1/2
Verifying : postgresql96-9.6.3-4PGDG.rhel6.x86_64 2/2
Installed:
postgresql96.x86_64 0:9.6.3-4PGDG.rhel6
Dependency Installed:
postgresql96-libs.x86_64 0:9.6.3-4PGDG.rhel6
Complete!
2.3.安装服务端
[root@test-8p122-lisong ~]
[root@test-8p123-lisong ~]
Install 1 Package(s)
Total download size: 4.8 M
Installed size: 18 M
Is this ok [y/N]: y Downloading Packages: Setting up and reading Presto delta Metadata Processing delta Metadata Package(s) data still to download: 4.8 M postgresql96-server-9.6.3-4PGDG.rhel6.x86_64.rpm | 4.8 MB 00:09 Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Installing : postgresql96-server-9.6.3-4PGDG.rhel6.x86_64 1/1 error reading information on service postgresql-96: No such file or directory Verifying : postgresql96-server-9.6.3-4PGDG.rhel6.x86_64 1/1
Installed:
postgresql96-server.x86_64 0:9.6.3-4PGDG.rhel6
Complete!
2.4.启动master的postgresql数据库
2.4.1.初始化数据库
[root@test-8p122-lisong ~]# service postgresql-9.6 initdb
[root@test-8p123-lisong ~]# service postgresql-9.6 initdb
#如下是初始化成功的结果
postgresql96-server-9.6.3-4PGDG.rhel6.x86_64.rpm | 4.8 MB 00:09
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : postgresql96-server-9.6.3-4PGDG.rhel6.x86_64 1/1
error reading information on service postgresql-96: No such file or directory
Verifying : postgresql96-server-9.6.3-4PGDG.rhel6.x86_64 1/1
Installed:
postgresql96-server.x86_64 0:9.6.3-4PGDG.rhel6
Complete!
2.4.2..启动数据库
[root@test-8p122-lisong ~]
[root@test-8p123-lisong ~]
[root@test-8p122-lisong ~]
[root@test-8p123-lisong ~]
Starting postgresql-9.6 service: [ OK ]
3.主从配置
3.1.主数据库配置
[root@test-8p122-lisong ~]
-bash-4.1$ psql
psql (9.6.3)
Type "help" for help.
postgres=
CREATE ROLE
-bash-4.1$ vim /var/lib/pgsql/9.6/data/pg_hba.conf
host replication repl 172.24.8.0/24 md5
host all repl 172.24.8.0/24 trust
-bash-4.1$ vim /var/lib/pgsql/9.6/data/postgresql.conf
listen_addresses = 172.24.8.122
wal_level = hot_standby
max_wal_senders= 6
wal_keep_segments = 10240
wal_send_timeout = 60s
max_connections = 512
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/9.6/data/pg_archive/%f'
3.1.2.建立归档文件和日志文件
[root@test-8p122-lisong ~]
[root@test-8p122-lisong ~]
[root@test-8p122-lisong ~]
[root@test-8p122-lisong ~]
3.2.从数据库配置
3.2.1.同步
[root@test-8p123-lisong ~]# su - postgres
-bash-4.1$ rm -rf /var/lib/pgsql/9.6/data/* -bash-4.1$ pg_basebackup -h 172.24.8.122 -U repl -D /var/lib/pgsql/9.6/data -X stream -P
-bash-4.1$ cp /usr/pgsql-9.6/share/recovery.conf.sample /var/lib/pgsql/9.6/data/recovery.conf
-bash-4.1$ vim /var/lib/pgsql/9.6/data/recovery.conf
standby_mode = on
primary_conninfo =
trigger_file =
recovery_target_timeline =
-bash-4.1$ vim /var/lib/pgsql/9.6/data/postgresql.conf
listen_addresses = 172.17.10.189
wal_level = hot_standby
max_connections = 1000 #一般从的最大链接要大于主的。
hot_standby = on #说明这台机器不仅仅用于数据归档，也用于查询
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s #多久向主报告一次从的状态。
hot_standby_Feedback = on #如果有错误的数据复制，是否向主进行范例
4.检查主从
[root@test-8p122-lisong ~]# su - postgres
-bash-4.1$ psql
psql (9.6.4)
Type "help" for help.
postgres=#
postgres=# select client_addr,sync_state from pg_stat_replication;
172.24.8.123 | async
(1 row)
postgres=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port |
backend_start | backend_xmin | state | sent_location | write_location | flush_location | r
26797 | 16384 | repl | walreceiver | 172.24.8.123 | | 41277 | 2017-08
-11 15:38:18.665813+08 | 1685 | streaming | 0/C0000D0 | 0/C0000D0 | 0/C0000D0 | 0
/C0000D0 | 0 | async
(1 row)
[主]-bash-4.1$ ps aux|grep postgres
postgres 28459 0.0 0.0 325960 14896 ? S 15:52 0:00 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data
postgres 28461 0.0 0.0 178852 1528 ? Ss 15:52 0:00 postgres: logger process
postgres 28463 0.0 0.0 325960 3252 ? Ss 15:52 0:00 postgres: checkpointer process
postgres 28464 0.0 0.0 325960 2968 ? Ss 15:52 0:00 postgres: writer process
postgres 28465 0.0 0.0 325960 5936 ? Ss 15:52 0:00 postgres: wal writer process
postgres 28466 0.0 0.0 326384 2632 ? Ss 15:52 0:00 postgres: autovacuum launcher process
postgres 28467 0.0 0.0 180952 1560 ? Ss 15:52 0:00 postgres: archiver process
postgres 28468 0.0 0.0 180952 1740 ? Ss 15:52 0:00 postgres: stats collector process
postgres 28472 0.0 0.0 326360 2848 ? Ss 15:52 0:00 postgres: wal sender process repl 172.24.8.123(41280) streaming 0/D000370
root 28580 0.0 0.0 145484 1612 pts/0 S 15:53 0:00 su - postgres
postgres 28581 0.0 0.0 108320 1900 pts/0 S 15:53 0:00 -bash
postgres 28613 0.0 0.0 173948 3576 pts/0 T 15:53 0:00 psql
postgres 28614 0.0 0.0 327152 5952 ? Ss 15:53 0:00 postgres: postgres postgres [local] idle
postgres 30658 0.0 0.0 110248 1172 pts/0 R+ 16:12 0:00 ps aux
postgres 30659 0.0 0.0 103260 892 pts/0 R+ 16:12 0:00 grep postgres
[从]-bash-4.1$ ps aux|grep postgres
postgres 16501 0.0 0.1 367612 42252 ? S 07:53 0:00 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data
postgres 16503 0.0 0.0 178880 1528 ? Ss 07:53 0:00 postgres: logger process
postgres 16504 0.0 0.0 367676 2356 ? Ss 07:53 0:00 postgres: startup process recovering 00000001000000000000000D
postgres 16505 0.0 0.0 367612 3016 ? Ss 07:53 0:00 postgres: checkpointer process
postgres 16506 0.0 0.0 367612 2768 ? Ss 07:53 0:00 postgres: writer process
postgres 16507 0.0 0.0 180980 1660 ? Ss 07:53 0:00 postgres: stats collector process
postgres 16513 0.0 0.0 374320 3136 ? Ss 07:53 0:01 postgres: wal receiver process streaming 0/D000370
root 16528 0.0 0.0 145484 1596 pts/0 S 07:53 0:00 su - postgres
postgres 16529 0.0 0.0 108320 1896 pts/0 S 07:53 0:00 -bash
postgres 16566 0.0 0.0 173984 3592 pts/0 T 07:53 0:00 psql
postgres 16567 0.0 0.0 369332 6276 ? Ss 07:53 0:00 postgres: postgres postgres [local] idle
postgres 16913 2.0 0.0 110252 1164 pts/0 R+ 08:12 0:00 ps aux
postgres 16914 1.0 0.0 103264 884 pts/0 S+ 08:12 0:00 grep postgres
原文链接：https://www.f2er.com/postgresql/193345.html
