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