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

This article mainly explains "how to build stream replication in PostgreSQL". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to build stream replication in PostgreSQL".

PostgreSQL can easily build a highly available HA environment by streaming Streaming Replication.

Step 1 main library: creating users

Create replication user replicator

Testdb=# CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'replicator';CREATE ROLE

Step 2 main library: parameter configuration

Configure parameters such as archive_mode

Archive_mode = ONwal_level = replicamax_wal_senders = 10archive_command ='/ home/xdb/archive.sh% p% f'listen_addresses ='*'

You can also modify it with the alter system command

ALTER SYSTEM SET wal_level TO 'replica';ALTER SYSTEM SET archive_mode TO' ON';ALTER SYSTEM SET max_wal_senders TO'10 investors alter SYSTEM SET listen_addresses TO'*'

Restart the database

Pg_ctl-D $PGDATA restart-mf

Step 3 main library: access configuration

Modify the pg_hba.conf file

Host replication replicator 192.168.26.26/32 md5

Effective configuration

Pg_ctl-D $PGDATA reload

Step 4 Slave: restore from master backup

Create a standby library using pg_basebackup on the slave library

192.168.26.25 is the master library IP,192.168.26.26 is the slave library IP

Pg_basebackup-h 192.168.26.25-U replicator-p 5432-D $PGDATA-P-Xs-R

Configure slave library postgres.conf

Hot_standby = ONhot_standby_feedback = ONALTER SYSTEM SET hot_standby TO 'ON';ALTER SYSTEM SET hot_standby_feedback TO' ON'

Configure slave library recovery.conf

$cat $PGDATA/recovery.confstandby_mode = 'on'primary_conninfo =' host=192.168.26.25 port=5432 user=replicator password=replicator'restore_command ='cp / data/archivelog/%f% p'archive_cleanup_command = 'pg_archivecleanup / data/archivelog% r'

Step 5 slave library: start the database

[xdb@localhost testdb] $pg_ctl-D $PGDATA startwaiting for server to start....2019-03-13 12 PGDATA startwaiting for server to start....2019 1330 CST [1870] LOG: listening on IPv4 address "0.0.0.0", port 54322019-03-13 12 12 PGDATA startwaiting for server to start....2019 13 30. 239 CST [1870] LOG: listening on IPv6 address ":" Port 54322019-03-13 12 LOG: listening on Unix socket "/ tmp/.s.PGSQL.5432" 2019-03-13 12 CST [1870] LOG: redirecting log output to logging collector process2019-03-13 12 listening on Unix socket 13 listening on Unix socket 30.379 CST [1870] HINT: Future log output will appear in directory "pg_log". Doneserver started

Step 6 verifies the replication environment

Confirm that the relevant process has been started

# Master library [xdb@localhost testdb] $ps-ef | grep senderxdb 1646 1532 0 12:13? 00:00:00 postgres: walsender replicator 192.168.26.26 (35294) streaming 0/43000140xdb 1659 1440 0 12:17 pts/1 00:00:00 grep-color=auto sender [xdb@localhost testdb] $# slave library [xdb@localhost testdb] $ps-ef | grep receiverxdb 1879 1870 0 12:13? 00:00:00 postgres: walreceiver streaming 0 / 43000140xdb 1884 1799 0 12:18 pts/0 00:00:00 grep-- color=auto receiver [xdb@localhost testdb] $ps-ef | grep startupxdb 1872 1870 0 12:13? 00:00:00 postgres: startup recovering 000000100000000000000043xdb 1887 1799 0 12:18 pts/0 00:00:00 grep-- color=auto startup [xdb@localhost testdb] $

Step 7 Monitorin

Query pg_stat_replication data dictionary table

Testdb=# SELECT * FROM pg_stat_replication -[RECORD 1]-+-- pid | 1646usesysid | 90113usename | replicatorapplication_name | walreceiverclient_addr | 192.168.26.26client_hostname | client_port | 35294backend_start | 2019-03-13 12:13:30.852269+08backend_xmin | state | streamingsent_lsn | | 0/43000140write_lsn | 0/43000140flush_lsn | 0/43000140replay_lsn | 0/43000140write_lag | flush_lag | replay_lag | sync_priority | 0sync_state | asynctestdb=# |

Synchronous replication

Configure the parameter recovery.conf from the library and add application_name to primary_conninfo

Primary_conninfo = 'user=replicator password=replicator host=192.168.26.25 port=5432 application_name = standby_26'

Main library configuration parameters

Synchronous_standby_names = 'standby_26'synchronous_commit = on

Restart the database to verify that the configuration is successful

Testdb=#\ xExpanded display is on.testdb=# SELECT * FROM pg_stat_replication -[RECORD 1]-+-- pid | 2257usesysid | 90113usename | replicatorapplication_name | standby_26client_addr | 192.168.26.26client_hostname | client_port | 35418backend_start | 2019-03-13 15:17:57.330573+08backend_xmin | 634state | streamingsent_lsn | | 0/54D4DBD0write_lsn | 0/54D4DBD0flush_lsn | 0/54D4DBD0replay_lsn | 0/54D4DBD0write_lag | 00:00:00.00101flush_lag | 00:00:00.001954replay_lag | 00:00:00.002145sync_priority | 1sync_state | sync so far | I believe you have a deeper understanding of "how to build stream replication in PostgreSQL", so you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.