Asynchronous stream replication of PostgreSQL master-Slave implementation

one of the ways that PostgreSQL master-slave replication is implemented:
Standby-based asynchronous stream replication, which is a nice feature provided after the postgresql9.x version (2010.9), similar functionality is available in Oracle after 11g for active dataguard and SQL Server Log shipping provided after version 2012, here again for PG applause, is really a great open source database. Needless to say, this blog will be a detailed record of the implementation of hot standby asynchronous stream replication in pg9.5 full configuration process and considerations.

Standby Database principle:
First of all, we make the decision. The purpose of synchronization is to achieve high availability of the DB service, usually a primary database that provides read and write, then synchronizes data to another from the library, and then continuously apply the data received from the main library from the library, providing no write service from the library and only reading services. A server that provides read-write functionality in PostgreSQL is known as primary database or master database, which is called hot standby server while receiving the master library synchronizing data while providing read service from the library servers.

PostgreSQL maintains a Wal log file in the Pg_xlog subdirectory of the data directory, which is used to record every change in the database file, which provides a scheme for database hot-Backup That is, the database using the file system to back up the same time also the corresponding Wal-log backup, even if the backup of the data block inconsistent, you can replay the Wal log to push the contents of the backup to a consistent state. This is also a point-in-time backup (Point-in-time Recovery), referred to as Pitr. There are two ways to send the Wal log to another server, namely:

Wal log archive (base-file)
Stream replication (streaming replication)

The first is to write a Wal log, then copy the Wal log file to the standby database, in short, with the CP command to achieve remote backup, so usually the repository behind the main library a Wal log file. The second stream replication is a new method of delivering the Wal log after postgresql9.x, and the advantage is that as long as the master library generates a log, it is immediately passed to the standby library, with a lower synchronization delay than the first, so we will certainly choose the method of streaming replication.
One more thing to explain before you actually do this is the most critical step in standby's setup-generating the base backup of master in standby. postgresql9.1 provides a handy tool for--pg_basebackup, with detailed descriptions and parameter descriptions that can be viewed in the official website (pg_basebackup Tool). The following in the construction process to do the relevant specific instructions, about some of the basic concepts and principles introduced here first.
Pg_basebackup Tool official Website Introduction:
Https://www.postgresql.org/docs/current/static/p-pgbasebackup.html


Configure the environment in detail:
The following starts the actual combat, first prepares two servers, I here opened 2 virtual machine to do the test, respectively is:
Master Repository (Master) CentOS release 6.5 (Final) 10.0.0.100 PostgreSQL 9.5.9
From library (standby) CentOS release 6.7 (Final) 10.0.0.110 PostgreSQL 9.5.9

Start with the main library configuration.
First of all, in advance in the master machine 10.0.0.100 installed PostgreSQL, using a binary installation package, specifically refer to the Ben Boven PostgreSQL binary installation process.

Main Library configuration:
Note that this is done on the main library (10.0.0.100), first open the postgresql.conf file in the data directory and then make the following modifications:

1.listen_address = ' * ' (default localhost)
2.port = 10280 (default is 5432)
3.wal_level = Hot_standby (default is minimal)
4.max_wal_senders=2 (default is 0)
5.wal_keep_segments=64 (default is 0)
The above parameters are described briefly below
The first is to listen to any host, wal_level indicates that the boot build hot standby,max_wal_senders needs to be set to a number greater than 0, which indicates the maximum number of concurrent Standby databases The main library can have, and the last Wal_keep_ Segments should also be set to a value as large as possible to prevent the main library from generating the Wal log too fast, the logs have not yet been delivered to standby to be overwritten, but consider disk space permitting, the size of a Wal log file is 16M:
[Email protected] data]$ cd/data/pgsql100/data/pg_xlog/
[[email protected] pg_xlog]$ ls
000000010000000000000001 000000010000000000000002 000000010000000000000003 000000010000000000000004 000000010000000000000005 Archive_status
[Email protected] pg_xlog]$ Du-sh *
16M 000000010000000000000001
16M 000000010000000000000002
16M 000000010000000000000003
16M 000000010000000000000004
16M 000000010000000000000005
4.0K Archive_status
As above, a Wal log file is 16M, if Wal_keep_segments is set to 64, that is, will be reserved for standby library 64 wal log files, then will occupy 16*64=1GB disk space, so need to consider comprehensively, Setting a larger amount of disk space allows you to reduce the risk of standby re-building. The next step is to create a superuser in the main library that is specifically responsible for letting standby connect to the Wal log:
CREATE ROLE Replica Login replication encrypted password ' replica ';
Next open the pg_hba.conf file in the data directory and make the following changes:
[Email protected] pg_xlog]$ Tail-2/data/pgsql100/data/pg_hba.conf
#host replication postgres:: 1/128 Trust
host replication replica 10.0.0.110/32 MD5

As above, this line of configuration means allowing users to replica from10.0.0.110/32A stream replication connection initiated to this database on the network, in short, allows the main library to be connected from the library server to drag the Wal log data. The main library configuration is simple, even if this is the end, start the main library and continue to configure from the library
pg_ctl-d/data/pgsql100/data-l/data/pgsql100/log/postgres.log Stop
pg_ctl-d/data/pgsql100/data-l/data/pgsql100/log/postgres.log Start

from the library configuration:
The first thing to note is that the PostgreSQL database service needs to be installed from the beginning of the library and should be the base backup of the Master Master Library that requires the Pg_basebackup command tool to be generated from the library. But it is also important to emphasize that:The data directory specified when the database is initialized from the library/data/psql110/data needs to be emptied before the pg_basebackup command tool can be used from the library to generate the underlying backup data for the Master Master library.

From here to start configuring the Slave library (10.0.0.110), first generate the underlying backup from the library via the Pg_basebackup command-line tool:
[Email protected] data]$ pg_basebackup-h 10.0.0.100-u replica-p 10280-f p-x-p-r-d/data/psql110/data/-L Replback Up
Password: password (replica)
46256/46256 KB (100%), 1/1 tablespace
[Email protected] data]$
Simply do the parameter description (which can be viewed through pg_basebackup--help),
-h Specifies the host name or IP address of the connected database, which is the IP of the main library.
-u Specifies the user name of the connection, here is the REPL user specifically responsible for streaming replication we just created.
-f Specifies the format of the output, supports P (as-is output), or t (output in tar format).
-X means that after the backup starts, another stream replication connection starts receiving the Wal log from the main library.
-P indicates the progress of allowing a real-time print backup during the backup process.
-R means that the recovery.conf file is automatically generated after the backup is finished, which avoids manual creation.

-d specifies which directory to write the backup to, especially one thing to note here is that the data catalog (/data/psql110/data/) directory from the library needs to be manually emptied before the base backup is made.
-L indicates the identity of the specified backup.

[Email protected] data]$ cat/data/psql110/data/recovery.conf
Standby_mode = ' on '
Primary_conninfo = ' User=replica password=replica host=10.0.0.100 port=10280 sslmode=prefer sslcompression=1 Krbsrvname=postgres '

When you run the command, you see a progress prompt that shows that the underlying backup was generated successfully:
[Email protected] data]$ pg_basebackup-h 10.0.0.100-u replica-p 10280-f p-x-p-r-d/data/psql110/data/-L Replback Up
Password: password (replica)
46256/46256 KB (100%), 1/1 tablespace
[Email protected] data]$
As above due to the MD5 authentication method we specified in pg_hba.conf, we need to enter the password. Finally, you need to modify the postgresql.conf file from the Library data directory and change the Hot_standby to the enabled state, which isHot_standby=on。 Even if the configuration is over, we can now start from the library,
[Email protected] data]$ egrep-v ' ^#|^$ '/data/psql110/data/postgresql.conf|grep "Hot_standby"
Wal_level = hot_standby # Minimal, archive, Hot_standby, or logical
Hot_standby = on # "On" allows queries during recovery

[Email protected] data]$ pg_ctl-d/data/psql110/data-l/data/psql110/log/postgres.log start
Server starting

To view the stream replication process from the library:

[Email protected] data]$ Ss-lntup|grep Postgres
TCP LISTEN 0::: 10280:::* Users: (("Postgres", 23161,4))
TCP LISTEN 0 *:10280 *:* Users: (("Postgres", 23161, 3))
[Email protected] data]$ Ps-ef|grep Postgres
Root 5663 4716 0 18:12 pts/0 00:00:00 su-postgres
Postgres 5664 5663 0 18:12 pts/0 00:00:00-bash
Postgres 5855 5664 0 18:13 pts/0 00:00:00/bin/bash/usr/local/pgsql/bin/psql
Postgres 5857 5855 0 18:13 pts/0 00:00:00/usr/local/pgsql/bin/psql.bin
Root 12406 7244 0 18:34 pts/1 00:00:00 su-postgres
Postgres 12407 12406 0 18:34 pts/1 00:00:00-bash
Root 13861 13810 0 18:47 pts/3 00:00:00 su-postgres
Postgres 13862 13861 0 18:47 PTS/3 00:00:00-bash
Root 21768 21736 0 19:54 pts/2 00:00:00 su-postgres
Postgres 21769 21768 0 19:54 pts/2 00:00:00-bash
Postgres 23161 1 0 20:05 pts/2 00:00:00/usr/local/pgsql/bin/postgres-d/data/psql110/data
postgres 23164 23161 0 20:05? 00:00:00 postgres:startup Process recovering 000000010000000000000007
Postgres 23165 23161 0 20:05? 00:00:00 Postgres:checkpointer Process
Postgres 23166 23161 0 20:05? 00:00:00 Postgres:writer Process
Postgres 23167 23161 0 20:05? 00:00:00 postgres:stats Collector Process
postgres 23168 23161 0 20:05? 00:00:00 Postgres:wal receiver process streaming 0/7000140
Postgres 23240 21769 0 20:06 pts/2 00:00:00 ps-ef
Postgres 23241 21769 0 20:06 pts/2 00:00:00 grep postgres

To view the stream replication process on the main library:

[Email protected] pg_xlog]$ Ps-ef|grep Postgres
Root 2904 2642 0 00:40 pts/0 00:00:00 su-postgres
Postgres 2905 2904 0 00:40 pts/0 00:00:00-bash
Postgres 2939 1 0 00:42 pts/0 00:00:00/usr/local/pgsql/bin/postgres-d/data/pgsql100/data
Postgres 2941 2939 0 00:42? 00:00:00 Postgres:checkpointer Process
Postgres 2942 2939 0 00:42? 00:00:00 Postgres:writer Process
Postgres 2943 2939 0 00:42? 00:00:00 Postgres:wal Writer Process
Postgres 2944 2939 0 00:42? 00:00:00 postgres:autovacuum Launcher Process
Postgres 2945 2939 0 00:42? 00:00:00 postgres:stats Collector Process
Root 3109 3064 0 00:58 pts/2 00:00:00 su-postgres
Postgres 3110 3109 0 00:58 pts/2 00:00:00-bash
Postgres 3151 3110 0 00:59 pts/2 00:00:00/bin/bash/usr/local/pgsql/bin/psql-p10280
Postgres 3153 3151 0 00:59 pts/2 00:00:00/usr/local/pgsql/bin/psql.bin-p10280
Root 3189 3087 0 01:07 pts/3 00:00:00 su-postgres
Postgres 3190 3189 0 01:07 PTS/3 00:00:00-bash
Postgres 3272 2939 0 01:25? 00:00:00 postgres:postgres testdb01 [local] Idle
postgres 3415 2939 0 02:16? 00:00:00 Postgres:wal Sender Process Replica 10.0.0.110 (34021) streaming 0/7000140
Postgres 3422 3190 0 02:17 pts/3 00:00:00 ps-ef
Postgres 3423 3190 0 02:17 pts/3 00:00:00 grep postgres

The process of streaming replication is visible from the library, and the same master library can see the process. Indicates that the master-slave stream replication configuration was successful.

Synchronous Test Demo:
Create a library and build a table for testing, create a TESTDB02 library in the master server (10.0.0.100) and build a table and add a few pieces of data:

Operation on Master:
postgres=# CREATE DATABASE testdb02;
CREATE DATABASE
Check:
[Email protected] pg_xlog]$ psql-p10280-c ' \list ' |grep testdb02
TESTDB02 | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 |

testdb01=# \c TESTDB02
Connected to Database "TESTDB02" as User "Postgres".
testdb02=# \d
No relations found.
To create a table:
CREATE TABLE weather (city varchar, Temp_lo int, Temp_hi int, PRCP real,date date);
testdb02=# \d
List of relations
Schema | Name |  Type | Owner
--------+---------+-------+----------
Public | Weather | Table | Postgres
(1 row)

testdb02=# \d Weather
Table "Public.weather"
Column | Type | Modifiers
---------+-----------------------+-----------
City | Character varying (80) |
Temp_lo | Integer |
Temp_hi | Integer |
PRCP | Real |
Date | Date |

testdb02=#
testdb02=# INSERT into weather (city, Temp_lo, Temp_hi, PRCP, date) VALUES (' China05 ', ' 47 ', ' 59 ', ' 1.0 ', ' 1994-12-15 ');
INSERT 0 1
testdb02=# INSERT into weather (city, Temp_lo, Temp_hi, PRCP, date) VALUES (' China04 ', ' 46 ', ' 58 ', ' 2.0 ', ' 1994-12-14 ');
INSERT 0 1
testdb02=# select * from weather;
City | Temp_lo | Temp_hi |    PRCP | Date
---------+---------+---------+------+------------
China05 |      47 |    59 | 1 | 1994-12-15
China04 |      46 |    58 | 2 | 1994-12-14
(2 rows)
testdb02=#

Check from the library:
[Email protected] data]$ psql-p10280-c ' \list ' |grep testdb02
TESTDB02 | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 |

postgres=# \c testdb02;
Connected to Database "TESTDB02" as User "Postgres".
testdb02=# \d
List of relations
Schema | Name |  Type | Owner
--------+---------+-------+----------
Public | Weather | Table | Postgres
(1 row)

testdb02=# \d Weather;
Table "Public.weather"
Column | Type | Modifiers
---------+-----------------------+-----------
City | Character varying (80) |
Temp_lo | Integer |
Temp_hi | Integer |
PRCP | Real |
Date | Date |

testdb02=# select * from weather;
City | Temp_lo | Temp_hi |    PRCP | Date
---------+---------+---------+------+------------
China05 |      47 |    59 | 1 | 1994-12-15
China04 |      46 |    58 | 2 | 1994-12-14
(2 rows)

testdb02=#
can see the perfect synchronization, then whether the library can be deleted? Test it:
Test Delete database testdb02 from the library;
postgres=# drop Database testdb02;
Error:cannot Execute DROP DATABASE in a read-only transaction
postgres=# drop Database testdb01;
Error:cannot Execute DROP DATABASE in a read-only transaction

standby data cannot be deleted, as we said earlier, standby only provides read-only services, and only master can read and write operations, so master has permission to delete data. The data in the standby will also be deleted synchronously while the master is deleted .

View replication Status

Execution in the main library

650) this.width=650; "Src=" Https://s4.51cto.com/oss/201710/29/7cdecd8be51e6a025b4afd68ea3b68ed.png-wh_500x0-wm_3 -wmp_4-s_675295564.png "title=" 2222.png "alt=" 7cdecd8be51e6a025b4afd68ea3b68ed.png-wh_ "/>

The content about asynchronous stream replication is here.

Reference blog:

http://blog.csdn.net/wzyzzu/article/details/53331206

This article is from the "10931853" blog, please be sure to keep this source http://wujianwei.blog.51cto.com/10931853/1977210

Asynchronous stream replication of PostgreSQL master-Slave implementation