PostgreSQL 9.5.5 Asynchronous stream replication with master-slave implementation (hot Standby)

Objective

Simply record one of the implementations of PostgreSQL master-slave-asynchronous stream replication based on standby, This is a nice feature that is provided after the postgresql9.x version (2010.9), similar to the log shipping that is provided after the active Dataguard and SQL Server 2012 versions of Oracle are available after 11g, 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 Principles

Briefly introduce some basic concepts and principles, first of all we take the decision from the synchronization is to achieve high availability of the DB service, usually a master database to provide read and write, and then synchronize the data to another from the library, and then from the library constantly apply the data received from the main library, from the library does not provide write service, only provide read services. A server that provides read-write functionality in PostgreSQL, called Primary database or master database, is called Hot from a library server that receives the master library synchronizing data while providing read services. Standby server.

PostgreSQL maintains a Wal log file in the Pg_xlog subdirectory of the data directory that records each change to the database file, which provides a way for a database to be hot-backed, namely: Back up the database using the file system and also back up the corresponding Wal logs, even if the data blocks backed up are 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:

1. Wal log archive (base-file)
2. 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, which has a lower synchronization latency than the first, so we will certainly choose the way the stream is copied.

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. After postgresql9.1 provides a very convenient tool--pg_basebackup, on its detailed introduction and parameter description can be viewed in the official website (pg_basebackup tool), the following in the construction process to do the relevant specific instructions, Some basic concepts and principles are introduced here first.

Detailed configuration

The following starts the actual combat, first prepares two servers, I here opened 2 virtual machine to do the test, respectively is:

1. Main Library (Master) centos-release-7-2.1511 192.168.111.101 PostgreSQL 9.5.5
2. From library (standby) centos-release-7-2.1511 192.168.111.102 PostgreSQL 9.5.5

Start with the main library configuration.

Main Library Configuration

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

1. listen_address = ' * ' (default localhost)
2. Wal_level = Hot_standby (default is minimal)
3. max_wal_senders=2 (default is 0)
4. Wal_keep_segments=64 (default is 0)

The first, needless to say, wal_level indicates that starting 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:

For example, a Wal log file is 16M, if the wal_keep_segments is set to 64, that is, the standby library will be reserved 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 user repl superuser password ‘111111‘;

Next open the pg_hba.conf file in the data directory and make the following changes:

For example, this line of configuration means allowing the user to repl a stream replication connection from the 192.168.111.0/24 network to the database , in short, allowing the repository to be connected to the main library 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.

Configure from Library

From here to start configuring the Slave library (192.168.111.102), first generate the underlying backup from the library via the Pg_basebackup command-line tool, with the following command:

 /pg_basebackup -h  192.168  .111  .101  -u  repl -f  P  -X  -p  -r  -d /usr/local /postgresql/data  / -l  replbackup20161122  

The following is a simple parameter description (which can be pg_basebackup --help viewed), -h specifying the host name or IP address of the connected database, which is the IP of the main library. -USpecify the user name of the connection, here is the REPL user who is specifically responsible for streaming replication that we just created. -FSpecifies the format of the output, which supports P (as-is output) or t (output in tar format). -xindicates that after the start of the backup, another stream replication connection is initiated to receive the Wal log from the main library. -Prepresents the progress of allowing a real-time print backup during the backup process. -Rindicates that the recovery.conf file is automatically generated after the backup is finished, which avoids manual creation. -Dspecify which directory to write the backup to, especially note that it is necessary to manually empty the data directory (/usr/local/postgresql/data) directory from the library before making the base backup. -lindicates the identity of the specified backup, and the following progress prompt after running the command indicates that the underlying backup was generated successfully:

For example, because we specify the MD5 authentication method in pg_hba.conf, we need to enter the password. Finally, you need to modify the postgresql.conf file from the Library data directory, which will change to the hot_standby enabled state, that is hot_standby=on . So far, even if the configuration is finished, we can now start from the library, if the success of the boot indicates a successful configuration, after running from the library, look at the pg_ctl start -l /usr/local/postgresql/log/pg_server.log log:

For example, do report a serious error, according to the hint that the permissions of the data directory is problematic, the permissions of the directory should be set to RWX (0700), then we cut back to the root user to re-set permissions to the data directory:

-R0700 /usr/local/postgresql/data/

After re-cutting back the Postgres user launches from the library, this time the discovery does not have any error logs, by looking at the ps -ef|grep postgres process:

For example, you can see the process of streaming replication, the same main library can see the process, now you can build a table for testing, in the master server (192.168.111.101) in the Postgres library to build a table and add a few data:

Then, in the standby server (192.168.111.102), view the synchronization situation:

For example, if you can see the perfect synchronization, is it possible to delete it from the library? Test it:

For example, standby data can not be deleted, as we said before, standby only provide read-only service, and only master can read and write operations, so master has permission to delete data, master delete the data in standby will also be deleted synchronously, The contents of asynchronous stream replication have all been introduced here.

Summarize

Simply record PostgreSQL9.5.5 the process of master-slave synchronization through stream replication, hoping to help friends who meet the same problem, the End.

PostgreSQL 9.5.5 Asynchronous stream replication with master-slave implementation (hot Standby)