database►Postgresql►postgresql master/slave

postgresql master slave

1. 源码安装

1
2
3
4
5
[root@localhost app]# tar xzf postgresql-9.6.1.tar.gz
[root@localhost app]# cd postgresql-9.6.1
[root@localhost postgresql-9.6.1]# ./configure --prefix=/mnt/app/pgsql
[root@localhost postgresql-9.6.1]# make
[root@localhost postgresql-9.6.1]# make install

2. 插件安装

1
2
[root@localhost postgresql-9.6.1]# cd contrib/
[root@localhost contrib]# make install

补充: 安装一个插件

1
2
[root@localhost postgresql-9.6.1]# cd contrib/pgcrypto/
[root@localhost pgcrypto]# mkdir install

3. 环境变量设置

1
2
3
4
[root@localhost postgresql-9.6.1]# echo 'export PGSQL_HOME=/mnt/app/pgsql' | tee /etc/profile.d/postgresql.sh
[root@localhost postgresql-9.6.1]# echo 'export PGSQL_BIN=${PGSQL_HOME}/bin' | tee -a /etc/profile.d/postgresql.sh                   
[root@localhost postgresql-9.6.1]# echo 'export PATH=${PGSQL_BIN}:$PATH' | tee -a /etc/profile.d/postgresql.sh                        
[root@localhost postgresql-9.6.1]# source /etc/profile

4. 创建数据和日志存放位置

1
2
[root@localhost postgresql-9.6.1]# mkdir -p /mnt/{data,log}/pgsql
[root@localhost postgresql-9.6.1]# chown -R smallasa.smallasa /mnt/{data,log}/pgsql

5. PG master服务器上操作

1. 初始化数据库
1
2
[root@localhost postgresql-9.6.1]# su - smallasa
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/initdb -D /mnt/data/pgsql/
2. 设置访问权限
1
2
3
4
5
6
[smallasa@localhost ~]$ vim /mnt/data/pgsql/pg_hba.conf
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
host    replication     repl            192.168.0.0/16          md5     =>追加语句
host    all             all             192.168.0.0/16          md5     =>其它用户访问策略
3. 修改配置文件
1
2
3
4
5
6
7
8
9
[smallasa@localhost ~]$ vim /mnt/data/pgsql/postgresql.conf
listen_addresses = '\*'
port = 5432
wal_level = replica
max_wal_senders = 5
wal_keep_segments = 64
synchronous_standby_names = '\*'
archive_mode = on
archive_command = 'DIR=/mnt/log/pgsql/archive/$(date +%Y%m%d);test ! -d $DIR && mkdir -p $DIR;test ! -f $DIR/%f && cp %p $DIR/%f'

补充:设置PG连接数,需要修改内核参数kernel.sem

1
2
3
4
[root@localhost postgresql-9.6.1]# cat >> /etc/sysctl.conf <<EOF
> kernel.sem = 50100 128256000 50100 2560
> EOF
[root@localhost postgresql-9.6.1]# sysctl -p

4. 启动服务
1
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/pg_ctl -D /mnt/data/pgsql/ -l /mnt/log/pgsql/pgsql.log start
5. 登录PG数据库,创建同步账号
1
2
3
4
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d postgres
postgres=# create user repl superuser password 'klip2[gE0_%y';
postgres-# \q
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/pg_ctl -D /mnt/data/pgsql/ -l /mnt/log/pgsql/pgsql.log restart

5. PG slave服务器上操作

1. 同步master数据
1
2
3
4
5
[root@localhost postgresql-9.6.1]# su - smallasa
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/pg_basebackup -F p --progress -R -D /mnt/data/pgsql/ -h 192.168.18.226 -p 5432 -U repl --password
Password:
22824/22824 kB (100%), 1/1 tablespace
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
2. 修改recovery配置文件
1
2
3
4
[smallasa@localhost ~]$ vim /mnt/data/pgsql/recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=repl password=klip2[gE0_%y host=192.168.18.226 port=5432 sslmode=disable sslcompression=1'
recovery_target_timeline = 'latest'
3. 修改配置文件
1
2
3
4
5
[smallasa@localhost ~]$ vim /mnt/data/pgsql/postgresql.conf
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 1s
hot_standby_feedback = on
4. 启动服务
1
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/pg_ctl -D /mnt/data/pgsql/ -l /mnt/log/pgsql/pgsql.log start

6. 登录数据库,创建数据库进行主从验证

1
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d postgres