本篇文章为大家展示了PostgreSQL数据库单机怎样扩展为流复制，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

1. 在standby服务器安装postgres数据库,不需要初始化.

安装过程详见:http://www.cnblogs.com/ilifeilong/p/6979288.html

2. 在primary服务器创建具有REPLICATION权限的复制用户

postgres=#CREATEROLEreplWITHREPLICATIONPASSWORD‘repl‘LOGIN;

3. 允许复制用户远程连接到primary服务器

$grep"^host"pg_hba.confhostallall127.0.0.1/32trusthostreplicationrepl0.0.0.0/0md5hostallall::1/128trust

4. 在primary服务器设置流复制相关的参数

$mkdir/usr/local/pgsql/arch$egrep"archive_mode|max_wal_senders|wal_keep_segments|archive_command|wal_level|hot_standby"postgresql.confal_level=hot_standby#minimal,archive,hot_standby,orlogicalarchive_mode=on#enablesarchiving;off,on,oralwaysarchive_command=‘test!-f/usr/local/pgsql/arch/%f&&cp%p/usr/local/pgsql/arch/%f‘max_wal_senders=5#maxnumberofwalsenderprocesseswal_keep_segments=30#inlogfilesegments,16MBeach;0disableshot_standby=on#"on"allowsqueriesduringrecovery#hot_standby_feedback=off#sendinfofromstandbytoprevent

5. 重新启动primary服务器进程

$pg_ctlstop-mfast$pg_ctlstart

6. 对primary服务器做一个全备并传输到standby服务器

在primary服务器通过pg_(start|stop)_backup函数进行备份

postgres=#SELECTpg_start_backup(‘label‘,true);pg_start_backup-----------------7/E6000060(1row)$rsync-az--progress${PGDATA}postgres@10.189.100.195:/usr/local/pgsql/--excludepostmaster.pidpostgres=#SELECTpg_stop_backup();NOTICE:pg_stop_backupcomplete,allrequiredWALsegmentshavebeenarchivedpg_stop_backup----------------7/E60005C8(1row)

在standby服务器通过pg_basebackup命令进行备份,要求standby的PGDATA目录为空

$pg_basebackup--host=10.189.102.118--username=repl--port=5432--label=backup--verbose--progress--pgdata=/usr/local/pgsql/data--checkpoint=fast--format=p--xlog-method=streamPassword:transactionlogstartpoint:7/EA000028ontimeline1pg_basebackup:startingbackgroundWALreceiver65933562/65933562kB(100%),1/1tablespacetransactionlogendpoint:7/EA000830pg_basebackup:waitingforbackgroundprocesstofinishstreaming...pg_basebackup:basebackupcompleted

7. 设置standby数据库复制相关参数,使得standby失效转移后可以作为主库工作

$mkdir/usr/local/pgsql/arch$egrep"archive_mode|max_wal_senders|wal_keep_segments|archive_command|wal_level|hot_standby"postgresql.confwal_level=hot_standby#minimal,archive,hot_standby,orlogicalarchive_mode=on#enablesarchiving;off,on,oralwaysarchive_command=‘test!-f/usr/local/pgsql/arch/%f&&cp%p/usr/local/pgsql/arch/%f‘max_wal_senders=5#maxnumberofwalsenderprocesseswal_keep_segments=30#inlogfilesegments,16MBeach;0disableshot_standby=on#"on"allowsqueriesduringrecovery#hot_standby_feedback=off#sendinfofromstandbytoprevent

8. 在standby文件创建恢复文件

$catrecovery.confrestore_command=‘cp/usr/local/pgsql/arch/%f"%p"‘standby_mode=‘on‘primary_conninfo=‘user=replpassword=replhost=10.189.102.118port=5432sslmode=disablesslcompression=1‘archive_cleanup_command=‘pg_archivecleanup-d/usr/local/pgsql/arch%r>>/usr/local/pgsql/arch/archive_cleanup.log‘trigger_file=‘/usr/local/pgsql/data/trigger_active_standby‘

9. 启动standby数据库进程,自动启动流复制

$pg_ctlstart-wwaitingforservertostart....LOG:couldnotcreateIPv6socket:AddressfamilynotsupportedbyprotocolLOG:redirectinglogoutputtologgingcollectorprocessHINT:Futurelogoutputwillappearindirectory"pg_log".doneserverstarted

10. 检查primary和standby数据库的延迟

通过函数和系统表查看

edbstore=#select*frompg_stat_replication;#在primary主库查看-[RECORD1]----+------------------------------pid|15013usesysid|19206usename|replapplication_name|walreceiverclient_addr|10.189.100.195client_hostname|client_port|56072backend_start|2017-06-1308:10:35.400508-07backend_xmin|state|streamingsent_location|7/EC01A588write_location|7/EC01A588flush_location|7/EC01A588replay_location|7/EC01A588sync_priority|0sync_state|asyncedbstore=#SELECTpg_current_xlog_location();#在primary主库查看pg_current_xlog_location--------------------------7/EC01A588(1row)postgres=#selectpg_last_xlog_receive_location(),pg_last_xlog_replay_location(),pg_last_xact_replay_timestamp();#在standby备库查看pg_last_xlog_receive_location|pg_last_xlog_replay_location|pg_last_xact_replay_timestamp-------------------------------+------------------------------+-------------------------------7/EC01A588|7/EC01A588|2017-06-1308:25:20.281568-07(1row)

通过进程查看

$ps-ef|grepsender|grep-vgrep#在primary库查看postgres1501324883008:10?00:00:00postgres:walsenderprocessrepl10.189.100.195(56072)streaming7/EC01A668$ps-ef|grepreceiver|grep-vgrep#在standby库查看postgres1285712843008:10?00:00:00postgres:walreceiverprocessstreaming7/EC01A668

上述内容就是PostgreSQL数据库单机怎样扩展为流复制，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注亿速云行业资讯频道。