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

This article focuses on "what is the process of building stream replication in PostgreSQL 12". 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 what is the process of building stream replication in PostgreSQL 12.

Main library

Create replication user

[pg12@localhost pg120db] $psql-c "CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'test'"-d testdbTiming is on.Expanded display is used automatically.CREATE ROLETime: 30.796 ms

General parameter configuration

[pg12@localhost pg120db] $grep 'listen' postgresql.conf listen_addresses =' *'# what IP address (es) to listen on;pg12@localhost pg120db] $grep 'replication' pg_hba.conf # DATABASE can be "all", "sameuser", "samerole", "replication", a # keyword does not match "replication". Access to replication# "all", "sameuser", "samerole" or "replication" makes the name lose# Allow replication connections from localhost By a user with the# replication privilege.local replication all trusthost replication all 127.0.0.1 by a user with the# replication privilege.local replication all trusthost replication all 32 trusthost replication all 192.168.0 trusthost replication all 16 md5host replication all:: 1 trust 128 [pg12@localhost pg120db] $

Reload configuration parameters

[pg12@localhost pg120db] $psql-c "select pg_reload_conf ()"-d testdbTiming is on.Expanded display is used automatically. Pg_reload_conf-t (1 row) Time: 454.580 ms

Confirm that the library is the main master library

[pg12@localhost pg120db] $psql-c "select pg_is_in_recovery ()"-d testdbTiming is on.Expanded display is used automatically. Pg_is_in_recovery-f (1 row) Time: 23.530 ms [pg12@localhost pg120db] $

Prepare the library

Perform a backup of the main library using pg_basebackup

[pg12@localhost] $pg_basebackup-h 192.168.26.28-U replicator-p 5432-D $PGDATA-Fp-Xs-P-RPassword: 426401 tablespace 9113562 kB

Where-Fp represents data in plain format,-Xs indicates that the required WAL files are included in stream,-P indicates display progress, and-R indicates write configuration information for replication.

Backup is completed, using the-R option, the standby.signal "signal" file is automatically generated in the data directory (can be generated manually using the touch command) and the postgresql.auto.conf file is updated, and the connection information of the main library is written in postgresql.auto.conf (primary_conninfo information can be added manually).

[pg12@localhost] $pg_basebackup-h 192.168.26.28-U replicator-p 5432-D $PGDATA-Fp-Xs-P-RPassword: 9113571 kB 1 tablespace [pg12@localhost ~] $[pg12@localhost ~] $cd $PGDATA [pg12@localhost testdb] $lsbackup_label pg_commit_ts pg_log pg_replslot pg_stat_tmp PG_VERSION postgresql.confbase pg_dynshmem pg_logical pg_serial pg_subtrans pg_wal standby.signalcurrent_logfiles pg_hba.conf pg_multixact pg_snapshots pg_tblspc pg_xactglobal Pg_ident.conf pg_notify pg_stat pg_twophase postgresql.auto.conf [pg12@localhost testdb] $ll standby.signal-rw-. 1 pg12 pg12 0 Nov 12 16:35 standby.signal [pg12@localhost testdb] $[pg12@localhost testdb] $cat postgresql.auto.conf# Do not edit this file Manuallyhands # It will be overwritten by the ALTER SYSTEM command.primary_conninfo = 'user=replicator password=test host=192.168.26.28 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any' [pg12@localhost testdb] $[pg12@localhost testdb] $grep' primary_conninfo' postgresql.*postgresql.auto.conf:primary_conninfo = 'user=replicator password=test host=192.168.26.28 port=5432 Sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'postgresql.conf:#primary_conninfo =''# connection string to sending server [pg12@localhost testdb] $

In PG 11, you need to create a recovery.conf file in which you configure the standby_mode and primary_conninfo parameters, which is no longer needed by PG 12, but is replaced by a standby.signal file and set directly through the configuration parameters.

Start the database from the slave database and confirm whether it is configured properly by pg_is_in_recovery.

[pg12@localhost testdb] $pg_ctl-D $PGDATA startwaiting for server to start....2019-11-12 16CST 31.635 CST [20436] LOG: starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit2019-11-12 16VR 46CST 31.636 CST [20436] LOG: listening on IPv4 address "0.0.0.0" Port 54322019-11-12 16 LOG 4615 31.636 CST [20436] LOG: listening on IPv6 address "::", port 54322019-11-12 16 redirecting log output to logging collector process2019 46 redirecting log output to logging collector process2019 31.638 CST [20436] LOG: listening on Unix socket "/ tmp/.s.PGSQL.5432" 2019-11-12 16V 4631.750 CST [20436] LOG: redirecting log output to logging collector process2019-11-12 16V 46 redirecting log output to logging collector process2019 [20436] HINT: Future log output will appear in directory "pg_log" Doneserver started [pg12@localhost testdb] $psql-c "select pg_is_in_recovery ()"-d testdb pg_is_in_recovery-t (1 row) [pg12@localhost testdb] $

Complete the construction

Replication status can be queried through pg_stat_replication (asynchronous replication)

[pg12@localhost pg120db] $psql-x-c "select * from pg_stat_replication"-d testdbTiming is on.Expanded display is used automatically.- [RECORD 1]-+-pid | 4503usesysid | 155959usename | replicatorapplication_name | walreceiverclient_addr | 192.168.26.25client_hostname | client_port | 35172backend_start | 2019-11-12 16:46:31.000236+08backend_xmin | state | streamingsent_lsn | 6/A3000148write_lsn | 6/A3000148flush_lsn | 6/A3000148replay_lsn | 6/A3000148write_lag | flush_lag | replay_lag | sync_priority | 0sync_state | asyncreply_time | 2019-11-12 16:48:32.509887+08Time: 149.682 ms [pg12@localhost pg120db] $so far I believe you have a deeper understanding of "what is the process of building stream replication in PostgreSQL 12". 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.