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

Source: Internet
Author: User
Tags log log postgresql file permissions

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.