PostgreSQL: Use pg_basebackup build streaming replication environment

Back in PostgreSQL 9.1 version had a pg_basebackup new tools to build a stream copy the library, until there is no

Practice, and today make up.

Traditional backup copy of the database to build the stream into the following steps:

    

1. select pg_start_backup ();
2. Copy the data files;
3. select pg_stop_backup ();

The pg_basebackup omit these steps, one step to get, there are multiple data directories for libraries is, pg_basebackup

Tool than the above steps to be much simpler and online operations under the following presentation.

An environmental information

Host: Notebook Virtual Machine

System: Red Hat Enterprise Linux Server release 6.2

Version: PostgreSQL 9.3beta1

Main Library IP: 192.168.1.36 Host Name: redhatB

Standby database IP: 192.168.1.35 host name redhat6

Note: PostgreSQL installation slightly.

The two main library operation

- 2.1 to create a replication user

CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 2 ENCRYPTED PASSWORD 'rep123us345er';

- 2.2 Set pg_hba.conf, add the following

host replication repuser 192.168.1.35/32 md5

  

- 2.3 Setting the main library postgresql.conf

checkpoint_segments = 16 archive_mode = on archive_command = '/ bin / date' max_wal_senders = 3 wal_keep_segments = 16 max_wal_senders = 3

  Note: only lists the main parameters, the other parameters according to the actual situation.

- 2.4 reload the configuration file

[Pg93 @ redhatB ~] $ pg_ctl reload-D $ PGDATA server signaled

  

- 2.5 See table space directory

postgres = # \ db List of tablespaces Name | Owner | Location --------------- + ---------- + ----------------------- -------------- pg_default | postgres | pg_global | postgres | tbs_francs | postgres | / database/pg93/pg_tbs/tbs_francs tbs_source_db | postgres | / database/pg93/pg_tbs/tbs_source_db (4 rows)

  

- 2.6 to view the data directory

[Pg93 @ redhatB pg_xlog] $ echo $ PGDATA / Database/pg93/pg_root

Note: first check table space directory and data directory, because these directories need to be created manually on the standby database host.

Three standby database operation

- Create a directory and empower 3.1

[Root @ redhat6 pgsql9.3beta1] # mkdir-p / database/pg93/pg_tbs/tbs_francs [Root @ redhat6 pgsql9.3beta1] # mkdir-p / database/pg93/pg_tbs/tbs_source_db [Root @ redhat6 pgsql9.3beta1] # mkdir-p / database/pg93/pg_root [Root @ redhat6 pgsql9.3beta1] # chown-R pg93: pg93 / database/pg93/pg_tbs/tbs_francs [Root @ redhat6 pgsql9.3beta1] # chown-R pg93: pg93 / database/pg93/pg_tbs/tbs_source_db [Root @ redhat6 pgsql9.3beta1] # chown-R pg93: pg93 / database/pg93/pg_root [Root @ redhat6 pgsql9.3beta1] # chmod 0700 / database/pg93/pg_root

  

- 3.2 Creating pgpass.

[Pg93 @ redhat6 ~] $ cat. Pgpass 192.168.1.36:1925: replication: repuser: rep123us345er [Pg93 @ redhat6 ~] $ chmod 0600. Pgpass

Note: Note pgpass file permissions to 0600.

- 3.3 pg_basebackup generated using library equipment

[Pg93 @ redhat6 pg93] $ pg_basebackup-D / database/pg93/pg_root-Fp-Xs-v-P-h 192.168.1.36-p 1925-U repuser transaction log start point: 1/1B000024 on timeline 1 pg_basebackup: starting background WAL receiver 651493/651493 kB (100%), 3/3 tablespaces transaction log end point: 1/1B0000DC pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: base backup completed

   Note: At this table space directory, $ PGDATA directory has been copied, and used here-X parameter, after the backup is complete, to the main library

Was collected during the execution of pg_basebackup WAL logs after version 9.2 support-Xs ie, stream form, this

WAL file mode does not require collection of the main library, which can be copied directly to the stream to catch up with the main library.

- 3.4 postgresql.conf settings from the library

hot_standby = on

  

- 3.5 settings from the library recovery.conf

- 3.5.1 generated recovery.conf

[Pg93 @ redhat6 pg_root] $ cp / opt/pgsql9.3beta1/share/recovery.conf.sample recovery.conf

  

- Modify the following parameters 3.5.2

standby_mode = on primary_conninfo = 'host = 192.168.1.36 port = 1925 user = repuser' trigger_file = '/ database/pg93/pg_root/postgresql.trigger.1925'

  

- 3.6 Kai Service

[Pg93 @ redhat6 pg_root] $ pg_ctl start-D $ PGDATA server starting

  

- 3.7 See standby database process                      

[Pg93 @ redhat6 pg_xlog] $ ps-ef | grep pg93 pg93 31398 1 0 21:09 pts / 0 00:00:00 / opt/pgsql9.3beta1/bin/postgres-D / database/pg93/pg_root pg93 31399 31398 0 21:09 00:00:00 postgres:? logger process pg93 31400 31398 0 21:09 00:00:00 postgres:? startup process waiting for 00000001000000010000001A pg93 31401 31398 0 21:09 00:00:00 postgres:? checkpointer process pg93 31402 31398 0 21:09 00:00:00 postgres:? writer process pg93 31403 31398 0 21:09 00:00:00 postgres:? stats collector process pg93 31404 31398 0 21:09 00:00:00 postgres:? wal receiver process

  

- Check the main library processes 3.8

[Pg93 @ redhatB pg_xlog] $ ps-ef | grep pg93 pg93 2504 1 0 Jun28? 00:00:26 / opt/pgsql9.3beta1/bin/postgres-D / database/pg93/pg_root pg93 2505 2504 0 Jun28 00:00:00 postgres:? logger process pg93 2507 2504 0 Jun28 00:00:08 postgres:? checkpointer process pg93 2508 2504 0 Jun28 00:00:28 postgres:? writer process pg93 2509 2504 0 Jun28 00:00:08 postgres:? wal writer process pg93 2510 2504 0 Jun28 00:00:19 postgres:? autovacuum launcher process pg93 2511 2504 0 Jun28 00:00:00 postgres:? archiver process last was 000000010000000100000019.00000024.backup pg93 2512 2504 0 Jun28 00:00:44 postgres:? stats collector process pg93 31898 2504 0 21:09 00:00:00 postgres:? wal sender process repuser 192.168.1.35 (39545) idle

  

Four test

- 4.1 Main Library

[Pg93 @ redhatB ~] $ psql psql (9.3beta1) Type "help" for help. postgres = # create table test_1 (id int4, create_time timestamp (0) without time zone); CREATE TABLE postgres = # insert into test_1 values ​​(1, now ()); INSERT 0 1 postgres = # select * from test_1; id | create_time ---- + --------------------- 1 | 2013-07-01 21:15:34 (1 row)

  

- 4.2 library equipment

[Pg93 @ redhat6 pg_xlog] $ psql psql (9.3beta1) Type "help" for help. postgres = # select * from test_1 postgres = # select * from test_1; id | create_time ---- + --------------------- 1 | 2013-07-01 21:15:34 (1 row)

  Note: Streaming replication completed structures.

Five laws: pg_basebackup parameters

[Pg93 @ redhat6 pg_xlog] $ pg_basebackup - help pg_basebackup takes a base backup of a running PostgreSQL server. Usage: pg_basebackup [OPTION] ... Options controlling the output: -D, - pgdata = DIRECTORY receive base backup into directory -F, - format = p | t output format (plain (default), tar) -R, - write-recovery-conf write recovery.conf after backup -X, - xlog include required WAL files in backup (fetch mode) -X, - xlog-method = fetch | stream include required WAL files with specified method -Z, - gzip compress tar output -Z, - compress = 0-9 compress tar output with given compression level General options: -C, - checkpoint = fast | spread set fast or spread checkpointing -L, - label = LABEL set backup label -P, - progress show progress information -V, - verbose output verbose messages -V, - version output version information, then exit -?, - Help show this help, then exit Connection options: -D, - dbname = CONNSTR connection string -H, - host = HOSTNAME database server host or socket directory -P, - port = PORT database server port number -S, - status-interval = INTERVAL time between status packets sent to server (in seconds) -U, - username = NAME connect as specified database user -W, - no-password never prompt for password -W, - password force password prompt (should happen automatically) Report bugs to <pgsql-bugs@postgresql.org>.