PostgreSQL uses Pg_basebackup to build a master and backup-stream replication environment

Today, Pg_basebackup is used to build a master and Standby flow replication environment, the operation process is simple, can be operated online, quite convenient.

Environment are CentOS6.6 + postgresql9.4, I use the installed library to do the test, the port is inconsistent, does not affect the environment to build, but pay attention to some related configuration:

Master: 192.168.3.201 port:5431

Preparation: 192.168.3.202 port:5432

The database installation process is omitted. can refer to

http://blog.csdn.net/baiyinqiqi/article/details/45560229

Main Library parameter configuration postgresql.conf:

Wal_level = Hot_standby

Checkpoint_segments = 16

Checkpoint_timeout = 5min

Archive_mode = On

Max_wal_senders = 3

Wal_keep_segments = 16

The main library creates a user with replication permissions:

Create USER Rep replication login encrypted password ' UDBAC ';

The main library modifies the authentication profile and adds the rep's Replication certification information:

Host replication Rep 192.168.3.202/32 MD5

Main Library Reload Database configuration information: Pg_ctl Reload

Only the database software is installed from the library and the database is not initialized.

Create a log directory, table space directory, based on the main library-related path configuration.

For example, the table space for the main library is as follows:

Create the same directory from the library and authorize the Postgres user to:

[Email protected]_202 postgres]# mkdir-p/pg_tablespace/pg_5431/ts_bigtable
[Email protected]_202 postgres]# chown-r postgres.postgres/pg_tablespace
[Email protected]_202 postgres]# chown-r postgres.postgres/pg_tablespace/pg_5431
[Email protected]_202 postgres]# chown-r postgres.postgres/pg_tablespace/pg_5431/ts_bigtable
[Email protected]_202 postgres]# mkdir-p/pg_tablespace/pg_5431/ts_udbac
[Email protected]_202 postgres]# chown-r Postgres.postgres/pg_tablespace/pg_5431/ts_udbac
[Email protected]_202 postgres]# chmod 0700/pg_tablespace
[Email protected]_202 postgres]# chmod 0700/pg_tablespace/pg_5431
[Email protected]_202 postgres]# chmod 0700/pg_tablespace/pg_5431/ts_bigtable
[Email protected]_202 postgres]# chmod 0700/pg_tablespace/pg_5431/ts_udbac

Of course the $pgdata directory is also indispensable.

To configure a password file that accesses the main library without a password from the library:

In the Postgres user home directory, create the. pgpass file and add the following:

192.168.3.201:5431:replication:rep:udbac

The. pgpass file permissions are 0600:chmod 0600. Pgpass

Test if no password can connect to the main library:

Psql-h 192.168.3.201-p 5431-u rep-d postgres

Use Pg_basebackup for database backup recovery:

Pg_basebackup-d $PGDATA-F p-x stream-v-p-h 192.168.3.201-p 5431-u Rep

To view data file recovery conditions:

Table Space Recovery scenario:

To configure the repository parameter postgresql.conf:

Hot_standby=on

Note: I'm not using the same port as the main repository here, I need to modify the configuration file in detail, such as port, some directories are named by ports and need to be modified. The first time I did not find the log log, because my installation directory with a port number, so I can't find it. In general, try to use the same port as possible.

To configure the standby recovery.conf configuration file:

CP $PGHOME/share/recovery.conf.sample $PGDATA/recovery.conf

VI $PGDATA/recovery.conf

Modify the following parameters:

Standby_mode = On

Primary_conninfo = ' host=192.168.3.201 port=5431 user=rep '

Trigger_file = '/usr/local/postgresql/9.4.1/pg5432/data/postgresql.trigger.5431 '

To start the repository and view the service process, note that there is a Wal receiver Progress process, which is the process of receiving the Wal log:

The same main repository also has a Wal sender process, which is used for log sending.

Test:

Create a new table tbl6 in the main library and insert some data:

Queries from the library can be queried from the library, but the data cannot be updated:

Reference article:

http://francs3.blog.163.com/blog/static/4057672720136210240967/

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

PostgreSQL uses Pg_basebackup to build a master and backup-stream replication environment