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

This article mainly explains "how to build postgresql-pg simple asynchronous flow replication", the content of the article is simple and clear, easy to learn and understand, now please follow the editor's ideas slowly in depth, together to study and learn "how to build postgresql-pg simple asynchronous flow replication" bar!

Stream replication is a new method of delivering WAL logs that has been available since PostgreSQL 9.0. Through stream replication, the slave database constantly synchronizes the corresponding data from the master database, and apply each WAL record in the slave database, where each transfer unit of stream replication is the record of the WAL log. Just like the dataguard technology of oracle, the stream replication of pg is divided into synchronous stream replication and asynchronous stream replication according to the synchronous mode. Synchronous flow replication can ensure data consistency when the primary database is down, but when there is a problem with the network or standby database, it will cause the main database transaction hang to live. Asynchronous flow replication does not have such a disadvantage and will not affect the performance of the production library, but a small portion of the data may be lost when the main library goes down.

The installation of stream replication of pg is very simple, which can be done after the following configuration is completed. The following version is the configuration of pg11 in rhel7. Note that you need to build the same version of pg service on both servers in advance:

01 main library operation

1.1 create replication users for master-slave synchronization

Postgres=# create role repl login replication encrypted password '123456'

CREATE ROLE

1.2 modify the pg_hba.conf file

Main library

Host allall192.168.0.0/24md5

Host replication repl192.168.0.52/24md5

The first line # allows XX this slave server to connect to the master server

The second line # allows replica users to copy, and the second field must be repl (the user added in the previous step)

# psql

Psql > select pg_reload_conf (); # effective

1.3Modification of postgresql.conf

Listen_addresses='*'# is usually configured after installing the server. Check it.

Wal_level=replica # default replica, do not move

Wal_keep_segments=256# sets the maximum number of xlog reserved for stream replication

# restart the pg service takes effect.

# only listen_address changes need to be restarted, otherwise select pg_reload_conf () is fine.

Pg_ctl stop-D $PGDATA

Pg_ctl start-D $PGDATA

1.4 testing whether the connection is successful from the slave library

Psql-h 192.168.0.51 UV p 5432 Muru postgres

02 prepare library operation

2.1 stop the pg service and empty the data directory

[postgres@apple-standby ~] $echo $PGDATA

/ data/pg11.5/data

[postgres@apple-standby data] $rm-rf / data/pg11.5/data

2.2 copy data from master node to slave node

$pg_basebackup-h 192.168.0.51 UV U repl-D / data/pg11.5/data-X stream-P

Could not identify current directory: No such file or directory

Password:

45238 kB (100%), 1 tablespace

2.3 configure recovery.conf

Cd $PGHOME (software installation directory)

Cp share/recovery.conf.sample $PGDATA/recovery.conf

Vi recovery.conf

Standby_mode = on

Primary_conninfo='host=192.168.0.51 port=5432 user=repl password=123456'

Recovery_target_timeline='latest'

# # #

Information about the primary_conninfo master server and the users connected

Standby_mode = on # indicates that the node is from the server

Recovery_target_timeline # specifies to restore to a specific timeline. The default setting is to restore along the current timeline when performing a basic backup. Setting this to latest will restore to the latest timeline found in the archive

2.4 start the service

Pg_ctl start-D $PGDATA

Query on the main database, as shown below, you can see that sync_state,async represents asynchronous flow replication.

Postgres=# select pid,usename,application_name,client_addr,state,sync_state from pg_stat_replication

As above, simply build a stream replication database!

Thank you for your reading, the above is the content of "how to build postgresql-pg simple asynchronous flow replication". After the study of this article, I believe you have a deeper understanding of how to build postgresql-pg simple asynchronous flow replication, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.