replication_slot and PostgreSQL Replication
2016-05-04 15:01 DataBases 阅读(1381) 评论(0) 编辑 收藏 举报主库IP：192.168.230.128
备库IP：192.168.230.129
PostgreSQL版本：
主备机PostgreSQL源码包均位于/opt/soft_bak
OS:CentOS5
主备库PostgreSQL均安装在/usr/local/pg952目录下
数据目录均在/usr/local/pg952/data
主备机为为postgres用户配置如下环境变量：
export PGPORT=5432（postgresql.conf中的端口）
export PGDATA=/usr/local/pg952/data
export LANG=en_US.utf8
export PGHOME=/usr/local/pg952
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE='date + "%Y%m%d%H%M"'
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA
export PGDATABASE=postgres
export PGUSER=postgres
alias rm='rm -i'
alias ll='ls -lh'
安装PostgreSQL（主、备机器均需要安装PostgreSQL）
创建postgres用户
useradd postgres -p 123456
创建PostgreSQL安装目录
[root@localhost ~]# cd /usr/local/
[root@localhost local]# mkdir pg952
chown -R postgres:postgres /usr/local/pg952/
安装PostgreSQL运行所需要的依赖软件包
yum -y install readline readline-devel zlib zlib-devel openssl openssl-devel gcc make flex bison
解压PostgreSQL
chown -R postgres:postgres /opt/soft_bak/
su - postgres
[postgres@localhost soft_bak]$ tar zxvf postgresql-9.5.2.tar.gz
指定PostgreSQL的安装目录并安装
[postgres@localhost soft_bak]$ cd postgresql-9.5.2
[postgres@localhost postgresql-9.5.2]$ ./configure --prefix=/usr/local/pg952/
[postgres@localhost postgresql-9.5.2]$ gmake world -j 32
[postgres@localhost postgresql-9.5.2]$ gmake install-world -j 32
主库创建PostgreSQL 数据目录
[postgres@localhost postgresql-9.5.2]$ cd /usr/local/pg952/
[postgres@localhost pg952]$ mkdir data
[postgres@localhost pg952]$ ls
bin data include lib share
备库创建PostgreSQL数据目录
[postgres@localhost postgresql-9.5.2]$ cd /usr/local/pg952/
[postgres@localhost pg952]$ mkdir data
[postgres@localhost pg952]$ chmod 0700 /usr/local/pg952/data/
[postgres@localhost pg952]$ ls
bin data include lib share
主库初始化PostgreSQL集群
[postgres@localhost pg952]$ initdb -D $PGDATA -k -E UTF8 --locale=C -U postgres -W
配置主库
postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 500
superuser_reserved_connections = 10
unix_socket_directories = '.'
password_encryption = on
shared_buffers = 2048MB
wal_level = hot_standby
fsync = on
synchronous_commit = on
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 5
hot_standby = on
max_replication_slots = 1
max_wal_size = 10GB
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600
log_truncate_on_rotation = on
pg_hba.conf
host all all 127.0.0.1/32 trust
host all all 192.168.230.0/24 trust
host replication postgres 127.0.0.1/32 trust
host replication postgres 192.168.230.129/24 trust
启动主库
[postgres@localhost pg952]$ pg_ctl -D $PGDATA start
主库创建slot
[postgres@localhost pg952]$ psql -h 192.168.230.128 -p 5432 -U postgres
Password for user postgres:
psql (9.5.2)
Type "help" for help.
postgres=# select pg_is_in_recovery();
-[ RECORD 1 ]-----+--
pg_is_in_recovery | f
postgres=# select * from pg_create_physical_replication_slot('slot1');
slot_name | xlog_position
-----------+---------------
slot1 |
(1 row)
postgres=# \x
Expanded display is on.
postgres=# select * from pg_replication_slots;
-[ RECORD 1 ]+---------
slot_name | slot1
plugin |
slot_type | physical
datoid |
database |
active | f
active_pid |
xmin |
catalog_xmin |
restart_lsn |
备库从主库做basebackup（即主库的基础备份）
[postgres@localhost bin]$ pg_basebackup -h 192.168.230.128 -P -Fp -Xs -v -p 5432 -U postgres -D /usr/local/pg952/data/
WARNING: skipping special file "./.s.PGSQL.5432"
transaction log start point: 0/7000024 on timeline 1
pg_basebackup: starting background WAL receiver
WARNING: skipping special file "./.s.PGSQL.5432"g952/data//backup_label)
21715/21715 kB (100%), 1/1 tablespace
transaction log end point: 0/7000118
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
[postgres@localhost bin]$ cp /usr/local/pg952/share/postgresql/recovery.conf.sample /usr/local/pg952/data/recovery.conf
[postgres@localhost bin]$ cd /usr/local/pg952/data/
recovery.conf
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=192.168.230.128 port=5432 user=postgres password=postgres'
primary_slot_name = 'slot1'
启动备库
[postgres@localhost data]$ pg_ctl -D $PGDATA start
在主库上验证slot工作状态
[postgres@localhost pg952]$ psql -h 192.168.230.128 -p 5432 -U postgres
psql (9.5.2)
Type "help" for help.
postgres=# \x
Expanded display is on.
postgres=# select * from pg_replication_slots;
-[ RECORD 1 ]+----------
slot_name | slot1
plugin |
slot_type | physical
datoid |
database |
active | t
active_pid | 30131
xmin |
catalog_xmin |
restart_lsn | 0/80001F8
在主库上查看复制状态
postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid | 30131
usesysid | 10
usename | postgres
application_name | walreceiver
client_addr | 192.168.230.129
client_hostname |
client_port | 44356
backend_start | 2016-04-25 21:40:27.740259-07
backend_xmin |
state | streaming
sent_location | 0/80181F0
write_location | 0/80181F0
flush_location | 0/80181F0
replay_location | 0/80181F0
sync_priority | 0
sync_state | async
验证主备复制
主库创建表并插入数据
[postgres@localhost pg952]$ psql -h 192.168.230.128 -p 5432 -U postgres
psql (9.5.2)
Type "help" for help.
postgres=# create table test(id int);
CREATE TABLE
postgres=# insert into test values (1),(2),(3),(4),(5);
INSERT 0 5
postgres=# select * from test ;
-[ RECORD 1 ]
id | 1
-[ RECORD 2 ]
id | 2
-[ RECORD 3 ]
id | 3
-[ RECORD 4 ]
id | 4
-[ RECORD 5 ]
id | 5
在从库上验证数据是否从主库复制到备库
[postgres@localhost data]$ psql -h 192.168.230.129 -p 5432 -U postgres
psql (9.5.2)
Type "help" for help.
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | test | table | postgres
(1 row)
postgres=# select * from test ;
id
----
1
2
3
4
5
(5 rows)
活跃状态的 slot 不可以删除,需要取消从库的 primary_slotname = 'slot1' 设置, 之后重启从库
active 表示是否使用, 如果不想使用 replication_slot ,需要删掉 slots.
删除slot
select pg_drop_replication_slot('slot1');
