What is hotstandby backup in postgresql and how to setup that?

https://access.redhat.com/solutions/1412753

 SOLUTION UNVERIFIED - 已更新 2016年六月23日12:21 - 

English 

环境

• Red Hat Enterprise Linux 6
  • postgresql92
• Red Hat Enterprise Linux 7
  • postgresql-9.2

问题

Is there a hot backup function in postgresql so that the data can be backup concurrently?

决议

There is no hot backup function in postgresql before version 9.0, but this function is added in postgresql-9.2 shipped with RHEL7, and postgresql92 in RHEL6 which is provided in optional channel. About method to use packages in optional channel, please refer to the How to subscribe a RHEL system to the Tools, Optional, or Supplementary channels?.

Hot Standby:

Raw

This feature allows users to create a 'Standby' database, that is, a second database instance (normally on a separate
server) replaying the primary's binary log, while making that standby server available for read-only queries.

Streaming Replication:

Raw

This time, the goal is improving the archiving mechanism to make it as continuous as possible and to not rely on log file
shipping. Standby servers can now connect to the master/primary and get sent, whenever they want, what they are missing from the Write Ahead Log, not in terms of complete files ('wal segments'), but in terms of records in the WAL (you can think of them as fragments of these files).

About setup, please refer to the example in Diagnostic Steps.

根源

N/A

诊断步骤

Following is an example about setup.

Environment:

• Primary server: 192.168.122.10
• Standby server: 192.168.122.20
• iptables in both servers are in stop status.

1. Install postgresql92 or postgresql-9.2 by RHN, RHSM or local iso and init db in both servers. posgresql-9.2 is used in this example.

2. In primary server:

   1. Start postgresql.

      Raw

      # su - postgres
      
      $ pg_ctl -D data start
      

   2. create a database and super user;

      Raw

      $ createdb testdb01
      $ createuser  -s -N testdbadmin01
      

   3. Modify the following parameters in postgresql.conf.

      Raw

      wal_level = hot_standby
      max_wal_senders = 2
      wal_keep_segments = 32
      

   4. Add the following setup in pg_hba.conf so that standby serve can pull data from primary server with user testdbadmin01.

      Raw

      host replication testdbadmin01 192.168.122.0/24 trust
      

   5. Enter into backup mode.

      Raw

      $ psql -d testdb01 -U testdbAdmin01
      
      testdb01=#select pg_start_backup('/');
      

   6. Copy data/* to standby server

      Raw

      $ scp -r data/* 192.168.122.20:/var/lib/pgsql/data/
      

   7. Stop backup

      Raw

      testdb01=#select pg_stop_backup();
      

   8. Restart postgresql.

      Raw

      $ pg_ctl -D data restart 
      

3. In standby server.

   1. Modify the following parameter in postgresql.conf.

      Raw

      hot_standby = on
      

   2. Create recovery.conf in data directory with the following contents.

      Raw

      standby_mode = 'on'
      primary_conninfo = 'host=192.168.122.10 port=5432 user=testdbadmin01'
      

   3. Restart postgresql.

      Raw

      $ pg_ctl -D data restart
      

Make a test:

1. In primary server:

   Raw

   $ psql -d testdb01 -U testdbadmin01
   
   testdb01=# CREATE TABLE testinfo (idnum serial, person varchar(20), age float4);
   testdb01=# insert into  testinfo (person, age) values ('test1', 38);
   testdb01=# insert into  testinfo (person, age) values ('test2', 30);
   

2. In standby server:

   Raw

   $ psql -d testdb01 -U testdbadmin01
   
   testdb01=# select * from testinfo where idnum=1;
   testdb01=# select * from testinfo where idnum=2;
   

Note: the standby server is read-only.