postgresql主从配置

2019-08-10 14:32 数据库 postgresql

"postgresql主从配置"

Master server

安装postgresql


xxxxxxxxxx
$ sudo apt-get install -y postgresql-9.5 postgresql-contrib-9.5

修改postgresql.conf


xxxxxxxxxx
$ sudo vim /etc/postgresql/9.5/main/postgresql.conf 
listen_addresses = '*'
wal_level = hot_standby
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/9.5/main/archive/%f'
# 多少server就填多少
max_wal_senders = 2
wal_keep_segments = 10

创建archive文件夹


xxxxxxxxxx
$ sudo mkdir -p /var/lib/postgresql/9.5/main/archive/
$ sudo chmod 700 /var/lib/postgresql/9.5/main/archive/
$ sudo chown -R postgres:postgres /var/lib/postgresql/9.5/main/archive/

修改pg_hba.conf


xxxxxxxxxx
$ sudo vim /etc/postgresql/9.5/main/pg_hba.conf 
host    replication     replica          0.0.0.0/0            md5

重启postgresql


xxxxxxxxxx
$ /etc/init.d/postgresql restart

增加replica用户


xxxxxxxxxx
$ sudo su - postgres
$ psql
postgres=# CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'test';

Slave Server

关闭数据库服务


xxxxxxxxxx
$ sudo /etc/init.d/postgresql stop

修改postgresql.conf


xxxxxxxxxx
$ sudo vim /etc/postgresql/9.5/main/postgresql.conf 
listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 2
wal_keep_segments = 10
hot_standby = on

从Master复制数据到Slave


xxxxxxxxxx
$ sudo su - postgres
$ cd /var/lib/postgresql/9.5
$ mv main main_bak
$ mkdir main/
$ chmod 700 main/
$ pg_basebackup -h 13.124.196.164 -U replica -D /var/lib/postgresql/9.5/main -P --xlog-method=stream

新增 recovery.conf


xxxxxxxxxx
$ cd /var/lib/postgresql/9.5/main/
$ vim recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=13.124.196.164 port=5432 user=replica password=test'
restore_command = 'cp /var/lib/postgresql/9.5/main/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'
$ chmod 600 recovery.conf

重启postgresql