流复制-pg_start_backup(带自定义表空间)
一.准备slave库
archive_mode = on ---开启归档模式
archive_command = 'test ! -f /mysqldata/pg/archive_active/%f && cp %p /mysqldata/pg/pgarch/%f' ---指定归档路径
1.1.如果主库上有了自定义表空间，无法使用pg_basebackup
[postgres@mycat02 pgsql_data]$ pg_basebackup -F p --progress -D /data/pgsql_data -h 172.16.10.100 -p 5432 -U repl --password
Password:
pg_basebackup: could not create directory "/data/pgsql_data/pgdata01": File exists
1.2.在命令行下备份
- 1.备份文件
pdb1=# select pg_start_backup('baseline');
-[ RECORD 1 ]---+-----------
pg_start_backup | 0/15000028
# 在$PGDATA目录下产生文件
[root@mysql56 pgsql_data]# cat backup_label
START WAL LOCATION: 0/15000028 (file 000000010000000000000015)
CHECKPOINT LOCATION: 0/15000060
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2018-11-27 15:34:26 CST
LABEL: baseline
# 出来shell命令行执行文件打包或者拷贝tar -cf data.tar pgsql_data
pdb1=# select pg_stop_backup();
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
-[ RECORD 1 ]--+-----------
pg_stop_backup | 0/15000128
# 停止备份后backup_label文件消失
# 切换归档日志
pdb1=# select pg_switch_xlog();
-[ RECORD 1 ]--+-----------
pg_switch_xlog | 0/170000B8
- 2.清空日志目录
# 清空/data/pg_xlog/目录下所有文件
rm –r /data/pgsql_data/pg_xlog
# 创建/pg_xlog/及其下面的archive_status目录
mkdir /data/pgsql_data/pg_xlog
mkdir /data/pgsql_data/pg_xlog/archive_status
- 3.修改postgres.conf配置
max_connections = 200 # 比主库稍大即可
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
- 4.在$PGDATA录下创建recovery.conf
# archive_command = 'test ! -f /mysqldata/pg/archive_active/%f && cp %p /mysqldata/pg/pgarch/%f'
# conf中的命令是将归档日志从/data/pgsql_data/pg_xlog 拷贝到指定目录，类似oracle的archve被copy备份
restore_command = 'cp /mysqldata/pg/pgarch/%f "%p"'
- 4.如果是作为slave，recovery.conf
standby_mode = on # 指定为从库
primary_conninfo = 'host=172.16.10.100 port=5432 user=repl password=replica' # 对应的主库信息
recovery_target_timeline = 'latest' # 这个说明这个流复制同步到最新的数据
- 5.启动数据库
pg_ctl –D /usr/local/pgsql/data/ start
# 修改hot_standby必须重启实例，reload无法载入
