PostgreSQL stream replication (streaming replication)

Basic Environment Description:

9.3 PostgreSQL version: 9.3. 6 Master: 192.168. 56.101 Standby: 192.168. 56.102

Installation process slightly, based on pkg package

1. Configure the Master side

# psql-u pgsql-d postgres-c"CREATE USER Rep REPLICATION LOGIN ENCRYPTED PASSWORD ' PASSWORD ';"
# cd/usr/local/pgsql# Vim data/postgresql.conflisten_addresses='*'Wal_level='Hot_standby'max_wal_senders=1# vim Data/pg_hba.confhost replication Rep 192.168.56.102/32 MD5

Modifying the configuration requires a restart of PostgreSQL
#/usr/local/etc/rc.d/postgresql Restart

2. Copying data to the standby end

Master-side operation:"Select Pg_start_backup (' backup ')"

# Cd/usr/local/pgsql
# tar ZCF data.tgz data
# SCP Data.tgz 192.168.56.102:/usr/local/pgsql

# psql-u Pgsql postgres-c "select Pg_stop_backup ()"

3. Configure the Standby side

#/usr/local/etc/rc.d/postgresql Stop

# Cd/usr/local/pgsql
# RM-R Data
# tar zxf data.tgz
# rm-r Data/postmaster.pid data/pg_xlog/* Data/backup_label

# Vim Data/postgresql.conf
Hot_standby = On
--------Delete the three configuration statements on the master side------------
#listen_addresses = ' * '
#wal_level = ' Hot_standby '
#max_wal_senders = 1
-------------------------------------------

# Vim Data/recovery.conf
Standby_mode = ' on '
Primary_conninfo = ' host=192.168.56.101 port=5432 user=rep password=password '

# Vim Data/pg_hba.conf
---------Delete a configuration clause on the master side-----------
#host Replication Rep 192.168.56.102/32 MD5
-------------------------------------------
#/usr/local/etc/rc.d/postgresql Start

4. Test data is configured successfully

--U pgsql mydbmydb=# CREATE TABLE foo (ID  bigint); mydbSelect * FROM Generate_series (1,100000);
-U pgsql mydbmydbSelect Count (*) from Foo;

Reference Address:

Https://www.byvoid.com/zhs/blog/postgresql-wal-replication

http://blog.sciencenet.cn/home.php?mod=space&uid=419883&do=blog&id=537939

Implementation principle

The master server records data churn with a pre-write log (WAL) when each transaction request is accepted. Specifically, a transaction takes two Phase commits, that is, the changes are written to the pre-write log before the database is actually changed. This guarantees that the timestamp of the pre-write log will never lag behind the database, even if the server is suddenly crashing at the time of the write, and the data can be recovered from the pre-write log after the restart because the pre-write log retains the newer version than the database record. The asynchronous replication solution for PostgreSQL is the use of a pre-written log to transfer the pre-written log from the primary server (master Sever) to the standby server (Standby server) and record the changes in the standby server back-up (Replay) out of the pre-write log. So as to achieve master-slave replication. PostgreSQL uses two ways to transfer the pre-write log: archive (archive) and streaming (streaming).

The principle of archival replication is that the primary server actively copies the pre-write log to a secure location (either directly to the standby server or to a third server), while the standby server periodically scans the location and copies the pre-written logs to the standby server and back again. This way, even if the primary server crashes, the standby server can obtain a complete record from this secure location to ensure that no data is lost. Streaming replication simplifies this step by transferring logs from the primary server to the standby server directly through the TCP protocol, avoiding the overhead of two replication and reducing the direct data latency of the standby server and the primary server. However, when the primary server crashes, logs that are not transferred to the standby server are lost, resulting in data loss. PostgreSQL supports both archival and streaming modes, and when both modes are turned on, the standby server periodically checks to see if an archive has reached the specified location and plays back the log. Once the specified location is detected without a new log, it switches to streaming mode, attempting to transfer the log directly from the network, then checking the archive, repeating the cycle.

Standby Side Start Error:

05:43:51 93b postgres[634]: [3-1] fatal:could not receive data from Wal stream:ERROR:requested Wal segment 000 000010000000000000002 has already been removed

This is because when the standby end is started, the file is looked up from the Wal location recorded in the above data.tgz to the Data/pg_xlog directory on the master side, and the Data/pg_xlog log file on the master side The process of recovering data on the standby side has been replaced by the master-side update operation Override, and 000000010000000000000002 of the file has been found.

Workaround: Re-go to master-side backup data copy to standby redo, if the test environment remember not to complete the configuration standby end, to the master side to update the data. The actual environment is not tested.

PostgreSQL stream replication (streaming replication)