​

环境

  • 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
  1. 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.