Shulou(Shulou.com)05/31 Report--

This article shows you how to expand the PostgreSQL database stand-alone to stream replication, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

1. Install the postgres database on the standby server without initialization.

For details of the installation process, please see: http://www.cnblogs.com/ilifeilong/p/6979288.html

two。 Create a replication user with REPLICATION privileges on the primary server

Postgres=# CREATE ROLE repl WITH REPLICATION PASSWORD 'repl' LOGIN

3. Allow replication users to connect remotely to the primary server

$grep "^ host" pg_hba.conf host all all 127.0.0.1 trust 32 trust host replication repl 0.0.0.0 trust 0 md5 host all all:: 1

4. Set the parameters related to stream replication on the primary server

$mkdir / usr/local/pgsql/arch $egrep "archive_mode | max_wal_senders | wal_keep_segments | archive_command | wal_level | hot_standby" postgresql.conf al_level = hot_standby # minimal, archive, hot_standby, or logical archive_mode = on # enables archiving Off, on, or always archive_command = 'test!-f / usr/local/pgsql/arch/%f & & cp% p / usr/local/pgsql/arch/%f' max_wal_senders = 5 # max number of walsender processes wal_keep_segments = 30 # in logfile segments, 16MB each; 0 disables hot_standby = on # "on" allows queries during recovery # hot_standby_feedback = off # send info from standby to prevent

5. Restart the primary server process

$pg_ctl stop-m fast $pg_ctl start

6. Complete the primary server and transfer it to the standby server

Backup through the pg_ (start | stop) _ backup function on the primary server

Postgres=# SELECT pg_start_backup ('label', true); pg_start_backup-7/E6000060 (1 row) $rsync-az-- progress ${PGDATA} postgres@10.189.100.195:/usr/local/pgsql/-- exclude postmaster.pid postgres=# SELECT pg_stop_backup () NOTICE: pg_stop_backup complete, all required WAL segments have been archived pg_stop_backup-7/E60005C8 (1 row)

Backup is carried out on the standby server through the pg_basebackup command, which requires that the PGDATA directory of standby is empty.

$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=stream Password: transaction log start point: 7/EA000028 on timeline 1 pg_basebackup: starting background WAL receiver 65933562 kB, 1 tablespace transaction log end point: 7/EA000830 pg_basebackup: waiting for background process to finish streaming. Pg_basebackup: basebackup completed

7. Set the relevant parameters of standby database replication, so that standby can work as the main database after failure transfer.

$mkdir / usr/local/pgsql/arch $egrep "archive_mode | max_wal_senders | wal_keep_segments | archive_command | wal_level | hot_standby" postgresql.conf wal_level = hot_standby # minimal, archive, hot_standby, or logical archive_mode = on # enables archiving Off, on, or always archive_command = 'test!-f / usr/local/pgsql/arch/%f & & cp% p / usr/local/pgsql/arch/%f' max_wal_senders = 5 # max number of walsender processes wal_keep_segments = 30 # in logfile segments, 16MB each; 0 disables hot_standby = on # "on" allows queries during recovery # hot_standby_feedback = off # send info from standby to prevent

8. Create a recovery file in the standby file

$cat recovery.conf restore_command ='cp / usr/local/pgsql/arch/%f "% p" 'standby_mode =' on' primary_conninfo = 'user=repl password=repl host=10.189.102.118 port=5432 sslmode=disable sslcompression=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. Start the standby database process and automatically start stream replication

Pg_ctl start-w waiting for server to start....LOG: could not create IPv6 socket: Address family not supported by protocol LOG: redirecting log output to logging collector process HINT: Future log output will appear in directory "pg_log". Done server started

10. Check latency for primary and standby databases

View through functions and system tables

Edbstore=# select * from pg_stat_replication # check [RECORD 1]-+-- pid | 15013 usesysid | 19206 usename | repl application_name | walreceiver client_addr | 10.189.100.195 client_hostname | client_port | 56072 backend_start | 2017-06-13 08:10: 35.400508-07 backend_xmin | state | streaming sent_location | 7/EC01A588 write_location | 7/EC01A588 flush_location | 7/EC01A588 replay_location | 7/EC01A588 sync_priority | 0 sync_state | async edbstore=# SELECT pg_current_xlog_location () # check pg_current_xlog_location-- 7/EC01A588 (1 row) postgres=# select pg_last_xlog_receive_location (), pg_last_xlog_replay_location (), pg_last_xact_replay_timestamp () in the main primary library # View pg_last_xlog_receive_location in standby repository | pg_last_xlog_replay_location | pg_last_xact_replay_timestamp-- +-- -7/EC01A588 | 7/EC01A588 | 2017-06-13 08 row 2515 20.281568-07 (1 row)

View through the process

$ps-ef | grep sender | grep-v grep # View postgres 15013 24883 0 08:10? 00:00:00 postgres: wal sender process repl 10.189.100.195 (56072) streaming 7/EC01A668 $ps-ef | grep receiver | grep-v grep # View postgres 12857 12843 0 08:10 in standby Library? 00:00:00 postgres: wal receiver process streaming 7/EC01A668 the above is how PostgreSQL database standalone extends to stream replication. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.