流复制-pg_start_backup(带自定义表空间)

时间：2023-01-29 12:16:33

一.准备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.在命令行下备份

http://blog.itpub.net/7721556/viewspace-1408159/

  • 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无法载入
标签：

相关文章