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

#安装成功如下



 Package Arch Version Repository Size =======================================================================================================
Installing:
 pgdg-centos96 noarch 9.6-3 /pgdg-centos96-9.6-3.noarch 2.7 k

Transaction Summary =======================================================================================================
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 ~]# yum install postgresql96-server
[root@test-8p123-lisong ~]# yum install postgresql96-server

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 ~]# chkconfig postgresql-9.6 on
[root@test-8p123-lisong ~]# chkconfig postgresql-9.6 on
[root@test-8p122-lisong ~]# service postgresql-9.6 start
[root@test-8p123-lisong ~]# service postgresql-9.6 start
Starting postgresql-9.6 service:                           [  OK  ]

3.主从配置

3.1.主数据库配置

3.1.1.修改配置文件授权

[root@test-8p122-lisong ~]# su - postgres
-bash-4.1$ psql
psql (9.6.3)
Type "help" for help.
#授权
postgres=# create role repl login replication encrypted password 'baifendian-2017';
CREATE ROLE


#编辑hba文件
-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 #从库的 max_connections要大于主库
archive_mode = on #允许归档 
archive_command = 'cp %p /var/lib/pgsql/9.6/data/pg_archive/%f'   #根据实际情况设置 

3.1.2.建立归档文件和日志文件

[root@test-8p122-lisong ~]# mkdir /var/lib/pgsql/9.6/data/pg_archive
[root@test-8p122-lisong ~]# chown -R postgres.postgres /var/lib/pgsql/9.6/data/pg_archive
[root@test-8p122-lisong ~]# mkdir /var/log/pgsql-log 
[root@test-8p122-lisong ~]# chown -R postgres.postgres /var/log/pgsql-log 

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 

3.2.2.修改配置文件

-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 = 'host=172.24.8.122 port=5432 user=repl password=baifendian-2017'
trigger_file = '/var/lib/pgsql/9.6/data/trigger.kenyon' 
recovery_target_timeline = 'latest'
-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;
 client_addr | sync_state --------------+------------
 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
eplay_location | sync_priority | sync_state -------+----------+---------+------------------+--------------+-----------------+-------------+--------
-----------------------+--------------+-----------+---------------+----------------+----------------+-- ---------------+---------------+------------
 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
相关文章
相关标签/搜索