The construction of PostgreSQL hot standby

First, introduction:

PG provides a new feature after the 9.* release, which is the read and write separation of stream replication, which is a typical application of PG high availability. This feature is called Active Dataguard in Oracle and is called hot standby in PostgreSQL.

Second, the system environment

System platform: CentOS 6.2 PostgreSQL version: 9.5.0 master:183.60.192.238 slave:183.60.192.229 three . Construction Steps Database Setup Reference Address http://www.cnblogs.com/lottu/p/5149191.html Note: The slave library does not require an INIT database;

• Master Library Operations

1. Create a stream replication user Repuser

CREATE USER repuser replication LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD ' li0924 '; # Replication A user attribute used when making a stream copy, usually set separately. # Login user logon properties, CREATE user default, so the login keyword can be omitted.

2. Set the following configuration entry in the/data/pgdata/postgresql.conf file of the master database：

wal_level = Hot_standby max_wal_senders = 1 wal_keep_segments = # max_wal_senders is the number of nodes in the slave library, how many slave libraries are set, # wal_keep_segments The default value is 16, is the number of log files under Pg_xlog related parameters

3. Add the following configuration to the/var/lib/pgsql/data/pg_hba.conf in the master database:

Host replication Repuser 183.60.192.229/16 MD5 # The second item must be filled in replication;

4. Restart the primary database for the configuration to take effect:

Pg_stop;pg_start

5. Adopt the method of hot backup; the files under the Pgdata directory of the master database are uploaded to the slave library. At the same time, the created Data folder is also passed;

This step is the work of a database clone.

 

• Slave Library Operations

1. Set the following configuration entry in the/data/pgdata/postgresql.conf file of the slave database:

Hot_standby =

on

2. Under the Pgdata directory;/data/pgdata. new File recovery.conf

standby_mode = ' on ' primary_conninfo = ' host=183.60.192.238 port=5432 user=repuser password=li0924 ' trigger_file = '/data/pgdata/trigger_standby '

3. Delete the/data/pgdata/postmaster.pid file that originally came from the Master library,

Rm/data/pgdata/postmaster.pid

4. According to the environment variable files in the master library, modify the environment variables of the Postgres user of the slave library and then start the Standby library:

Pg_start

  Four, Validation Work

• View process

Master Library

[email protected]_210 ~]$ ps-ef | grep postgre Root 2021 556 0 15:18 pts/1 00:00:00 su-postgres Postgres 2022 2021 0 15:18 pts/1 00:00:00-bash postgres 2239 1 0 15:24 pts/1 00:00:00/opt/pgsql95/bin/postgres Postgres 2249 2239 0 15:24? 00:00:00 Postgres:checkpointer Process Postgres 2250 2239 0 15:24? 00:00:00 Postgres:writer Process postgres 2251 2239 0 15:24? 00:00:00 Postgres:wal Writer Process postgres 2252 2239 0 15:24? 00:00:00 postgres:autovacuum Launcher Process postgres 2253 2239 0 15:24? 00:00:00 Postgres:archiver Process last was 00000006000000000000001e.00000028.backup postgres 2254 2239 0 15:24? 00:00:00 postgres:stats Collector Process postgres 3235 2239 0 15:54? 00:00:00 Postgres:wal Sender Process Repuser 183.60.192.229 (40399) streaming 0/1f000d80

Slave Library

[email protected]_222 pgdata]$ ps-ef | grep postgres postgres 6856 1 0 15:54 pts/0 00:00:00/opt/pgsql/bin/postgres postgres 6863 6856 0 15:54? 00:00:00 postgres:startup Process recovering 00000006000000000000001F postgres 6864 6856 0 15:54? 00:00:00 Postgres:checkpointer Process postgres 6865 6856 0 15:54? 00:00:00 Postgres:writer Process postgres 6866 6856 0 15:54? 00:00:00 Postgres:wal receiver process streaming 0/1F000CA0 postgres 6867 6856 0 15:54? 00:00:00 postgres:stats Collector Process root 6922 30527 0 16:08 pts/0 00:00:00 su-postgres postgres 6923 6922 0 16:08 pts/0 00:00:00-bash postgres 6974 6923 0 16:49 pts/0 00:00:00 ps-ef postgres 6975 6923 0 16:49 pts/0 00:00:00 grep postgres

• Data validation

Operating in the Master Library

[email protected]_210 ~]$ psql mydb repuser psql (9.5.0) Type ' help ' for help. mydb=> \d List of Relations Schema |     Name |  Type | Owner --------+-------+---------------+---------- Public | dept | table | Lottu Public | emp | foreign table | postgres Public | test | table | Lottu Public | trade | table | Lottu (4 rows)   mydb=> CREATE TABLE T_lottu (ID int primary key,name varchar); CREATE TABLE mydb=> \d List of Relations Schema |     Name |  Type | Owner --------+---------+---------------+---------- Public | dept | table | Lottu Public | emp | foreign table | postgres Public | t_lottu | table | Repuser Public | test | table | Lottu Public | trade | table | Lottu (5 rows)   mydb=> INSERT INTO T_lottu values (1001, ' Lottu '); INSERT 0 1 mydb=> INSERT INTO T_lottu values (1002, ' Vincent '); INSERT 0 1 mydb=> INSERT INTO T_lottu values (1003, ' Rax '); INSERT 0 1 mydb=> SELECT * from T_lottu; ID | Name ------+--------- 1001 | Lottu 1002 | Vincent 1003 | Rax (3 rows)

Log in to Slave library to view data

[email protected]_222 pgdata]$ psql mydb repuser psql (9.5.0) Type ' help ' for help.   mydb=> SELECT * from T_lottu; ID | Name ------+--------- 1001 | Lottu 1002 | Vincent 1003 | Rax (3 rows) Mydb=> Delete from T_lottu where id = 1003;
Error:cannot execute DELETE in a read-only transaction

Reference Blog--http://my.oschina.net/kenyon/blog/54967

The construction of PostgreSQL hot standby