Pg_basebackup of PostgreSQL Backup

First, prepare

Connect on master node to create replication user

# CREATE ROLE Replicator with login replication password 123456;

Modify Whitelist pg_hba.conf

# Host replication All XXXXX/32 MD5

Second, make the basic backup

# pg_basebackup --format=tar \  #使用打包方式备份--xlog-method=fetch \     #获取wal文件方式, when the backup is finished, all the Wal backup--compress=1 \     #压缩等级--checkpoint=fast \      #执行检查点的方式--label=backup \     #标签--pgdata=/data/backup/ # Backup directory--progress \     #打印过程--verbose \     #打印详细信息--host= ' 172.17.5.45 '  \     #master节点ip--port=5432 \    # Master node Port--user=postgres     #连接master节点的用户Password:  transaction log start  point: 2/34000028 on timeline 1414272/414272 kB  (100%), 3/3  tablespaces                                           transaction log end point: 2/34000128pg_basebackup: base backup completed 

Backup defaults to a max_wal_senders connection, requires a larger setting, and can be used

-R,--max-rate=rate maximum transfer rate to transfer Data directory (in kb/s, or use suffix "k "or" M ")

Limit transfer speed

# ll Backup/total 123196-rw-r--r--. 1 root root 129 Sep 8 01:21 25375.tar.gz-rw-r--r--. 1 root root 8 Sep 01:21 25376.tar.gz-rw-r--r--. 1 root root 126141406 Sep 8 01:22 base.tar.gz

Base.tar.gz is a backup of the data catalog, and the other two are table space backups

Third, recovery1. Unzip

# cd /data/backup/# mkdir pgdata 25375 25376# tar zxf base.tar.gz  -C pgdata/# ls pgdata/backup_label  global   pg_dynshmem   pg_ident.conf  pg_logical    pg_notify    pg_serial      pg_stat      pg_subtrans  pg_twophase   pg_xlog                Postgresql.confbase          pg_clog  pg_hba.conf   pg_log         pg_multixact  pg_replslot   pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION    postgresql.auto.conf  recovery.conf# tar zxf 25375.tar.gz -C  25375# tar zxf 25376.tar.gz -c 25376 # ls25375  25375.tar.gz  25376   25376.tar.gz  base.tar.gz  pgdata

2. Modify table space Soft Connect

# ln-sf/data/backup/25375 pgdata/pg_tblspc/25375 [[email protected] backup]# ln-sf/data/backup/25376 PGDATA/PG_TBLSPC /25376 [[email protected] backup]# ll pgdata/pg_tblspc/total 0lrwxrwxrwx. 1 root root 8 01:35 25375-/data/backup/25375lrwxrwxrwx. 1 root root Sep 8 01:35 25376-/data/backup/25376

3. Modify Data Directory Permissions

# chown -r postgres:postgres pgdata/ 25375 25376 # chmod 0700  pgdata/# lltotal 123208drwxr-xr-x.  3 postgres postgres       4096 sep  8 01:32 25375-rw-r--r--.   1 root      root           129 sep   8 01:21 25375.tar.gzdrwxr-xr-x.  3 postgres postgres       4096 sep  8 01:33 25376-rw-r--r--.   1 root      root           128  Sep  8 01:21 25376.tar.gz-rw-r--r--.   1 root      ROOT     126141406 SEP  8 01:22 BASE.TAR.GZDRWX------.   19 postgres postgres&nbSp;     4096 sep  8 01:31 pgdata 

4. Start

# su postgres$ pg_ctl -d pgdata/ startserver starting [     2015-09-08 05:40:04.510 UTC 11107 55ee74b4.2b63 1 0]LOG:   redirecting log output to logging collector process[     2015-09-08 05:40:04.511 utc 11107 55ee74b4.2b63 2 0]hint:  future  log output will appear in directory  "Pg_log" .$ psql psql  ( 9.4.4) type  "Help"  for help.postgres=# \db+                                    list of tablespaces    name     |  owner   |      location       | access privileges | options | description ------------+----------+------ --------------+-------------------+---------+------------- pg_default | postgres |                      |                    |         |  pg_global  |  postgres |                     |                    |         |   tblspc1    | postgres | /data/backup/25375 |                    |          |  tblspc2    | postgres | /data/backup/25376 |                    |          |  (4 rows)

Iv. production from the Library
1. Get Backup files

# pg_basebackup --format=plain \     #使用目录方式--write-recovery-conf \      #生成简单recovery. conf--xlog-method=stream \    # Using stream to back up the Wal file will take up a Max_wal_sender,wal file. Lifetime achievements will be backed up--checkpoint=fast --label=backup --pgdata=/data/ test_pgdata_94 --progress --verbose --host= ' 172.17.5.45 '  --port=5432 --user= postgrespassword: transaction log start point: 2/38000028 on timeline  1pg_basebackup: starting background wal receiver397887/397887 kb  (100%),  3/ 3 tablespaces                                           transaction log end point: 2/380000f0pg_ Basebackup: waiting for background process to finish streaming ...pg_basebackup: base backup  Completed

2. Modify Directory Permissions

# chown -r postgres:postgres test_pgdata_94 tblspc1/ tblspc2/# chmod  0700 TEST_PGDATA_94# LL TEST_PGDATA_94TOTAL 104-RW-------.  1 postgres  POSTGRES  189 SEP  8 01:43 BACKUP_LABELDRWX------.  6 postgres  POSTGRES 4096 SEP  8 01:43 BASEDRWX------.  2 postgres  POSTGRES 4096 SEP  8 01:43 GLOBALDRWX------.  2 postgres postgres  4096 SEP  8 01:43 PG_CLOGDRWX------.  2 postgres postgres  4096 SEP  8 01:43 PG_DYNSHMEM-RW-------.  1 postgres postgres 4581  SEP  8 01:43 PG_HBA.CONF-RW-------.  1 postgres postgres 1636  sep  8 01:43 pg_ident.confdrwxr-xr-x. 2 postgres postgres 4096  sep  8 01:43 PG_LOGDRWX------.  4 postgres postgres 4096 sep  8 01:43 pg_ LOGICALDRWX------.  4 postgres postgres 4096 sep  8 01:43 pg_ MULTIXACTDRWX------.  2 postgres postgres 4096 sep  8 01:43 pg_ NOTIFYDRWX------.  2 postgres postgres 4096 sep  8 01:43 pg_ REPLSLOTDRWX------.  2 postgres postgres 4096 sep  8 01:43 pg_ SERIALDRWX------.  2 postgres postgres 4096 sep  8 01:43 pg_ SNAPSHOTSDRWX------.  2 postgres postgres 4096 sep  8 01:43 pg_ STATDRWX------.  2 postgres postgres 4096 sep  8 01:43 pg_stat_ TMPDRWX------.  2 postgres postgres 4096 sep  8 01:43 pg_ SUBTRANSDRWX------.  2 postgres postGRES 4096 SEP  8 01:43 PG_TBLSPCDRWX------.  2 postgres postgres  4096 SEP  8 01:43 PG_TWOPHASE-RW-------.  1 postgres postgres     4 SEP  8 01:43 PG_VERSIONDRWX------.  3 postgres  POSTGRES 4096 SEP  8 01:43 PG_XLOG-RW-------.  1 postgres  POSTGRES   88 SEP  8 01:43 POSTGRESQL.AUTO.CONF-RW-------.  1  postgres postgres 4676 sep  8 01:43 postgresql.conf-rw-r--r--.  1  postgres postgres  131 Sep  8 01:43 recovery.conf# ll  TBLSPC1/TOTAL 4DRWX------.  2 postgres postgres 4096 sep  8 01:43  PG_9.4_201409291# LL TBLSPC2TOTAL 4DRWX------.  2 postgres postgres  4096 sep  8 01:43 pg_9.4_201409291 

3. Start

# su postgres$ pg_ctl -d test_pgdata_94 startserver starting$ [     2015-09-08 05:49:55.383 UTC 12100 55ee7703.2f44 1 0]LOG:   redirecting log output to logging collector process[     2015-09-08 05:49:55.383 UTC 12100 55ee7703.2f44 2 0]HINT:   future log output will appear in directory  "Pg_log". $ psql psql   (9.4.4) type  "Help"  for help.postgres=# \db+                                  list of tablespaces    name     |  Owner   |   Location     | access privileges | options | description ------------+----------+---------------+-------------------+---- -----+------------- pg_default | postgres |                |                    |         |   pg_global  | postgres |                |                    |         |   tblspc1    | postgres | /data/tblspc1 |                    |          |  tblspc2    | postgres | /data/tblspc2 |                    |          |  (4 rows)

Reference: http://www.postgresql.org/docs/9.4/static/app-pgbasebackup.html

Pg_basebackup of PostgreSQL Backup