Introduction of PostgreSQL cold backup and reduction

Background
PostgreSQL cold backup, refers to the database shutdown state of the database data files are backed up.

This backup is not practical in production, the general production environment needs to ensure that the database 7*24 hours.

A cold backup needs to be backed up by the database Cluster master directory ($PGDATA), the table space directory, and the transaction log (Pg_xlog) directory.

If you specify a different directory or file in the parameter file, some also need to be backed up.

First, before the backup, we need to figure out what needs to be backed up:
1. Database directory:

Pg93@db-172-16-3-33-> CD $PGDATA
The location of the home directory.

pg93@db-172-16-3-33-> pwd
/pgdata1999
Files and folders in the home directory

Pg93@db-172-16-3-33-> LL
Total 140K
DRWX------9 pg93 pg93 4.0K 16:33 Base
DRWX------2 pg93 pg93 4.0K may 11:07 Global
Drwxr-xr-x 2 pg93 pg93 4.0K may 11:16 Pgbak
DRWX------2 pg93 pg93 4.0K may 14:52 Pg_clog
-RW-------1 pg93 pg93 4.6K 17:59 pg_hba.conf
-RW-------1 pg93 pg93 1.7K may 14:30 pg_ident.conf
DRWX------2 pg93 pg93 12K may 11:06 Pg_log
DRWX------4 pg93 pg93 4.0K May 5 20:26 pg_multixact
DRWX------2 pg93 pg93 4.0K may 11:06 pg_notify
DRWX------2 pg93 pg93 4.0K May 5 20:26 pg_serial
DRWX------2 pg93 pg93 4.0K May 5 20:26 pg_snapshots
DRWX------2 pg93 pg93 4.0K may 11:06 Pg_stat
DRWX------2 pg93 pg93 4.0K may 11:16 pg_stat_tmp
DRWX------2 pg93 pg93 16K 15:30 Pg_subtrans
DRWX------2 pg93 pg93 4.0K may 11:15 PG_TBLSPC
DRWX------2 pg93 pg93 4.0K May 5 20:26 pg_twophase
-RW-------1 pg93 pg93 4 May 5 20:26 pg_version
lrwxrwxrwx 1 pg93 pg93 11:15 pg_xlog->/pgdata/digoal/1921/data03/pg93/1999/pg_xlog
-RW-------1 pg93 pg93 20K may 09:36 postgresql.conf
-RW-------1 pg93 pg93 11:06 postmaster.opts
-RW-------1 pg93 pg93 11:06 postmaster.pid
-rw-r--r--1 root root 4.7K May 8 15:37 Recovery.done
-rw-r--r--1 pg93 pg93 2.5K may 13:46 root.crt
-rw-r--r--1 pg93 pg93 1.3K may 13:37 server.crt
-R--------1 pg93 pg93 1.7K 13:32 Server.key
2. Transaction log Directory

This example is

$PGDATA/pg_xlog
3. Table Space Directory

To enter the table space directory to view the table space:

Pg93@db-172-16-3-33-> CD pg_tblspc/
Pg93@db-172-16-3-33-> LL
Total 0
lrwxrwxrwx 1 pg93 pg93 11:15 26417->/pgdata/digoal/1921/data03/pg93/1999/tbs_digoal
4. Files or directories specified in the configuration file that are outside the home directory

Return to $pgdata to see if any files other than $pgdata are configured in the postgresql.conf

pg93@db-172-16-3-33-> grep-e-i "Dir|file" postgresql.conf
#data_directory = ' Configdir ' # Use data in another directory
#hba_file = ' configdir/pg_hba.conf ' # host-based Authentication file
#ident_file = ' configdir/pg_ident.conf ' # ident configuration file
Ssl_cert_file = ' server.crt ' # (change requires restart)
Ssl_key_file = ' Server.key ' # (change requires restart)
Ssl_ca_file = ' root.crt ' # (change requires restart)
#ssl_crl_file = ' # (change requires restart)
#krb_server_keyfile = '
#include_dir = ' conf.d ' # include files ending in '. conf ' from
# directory ' CONF.D '
#include_if_exists = ' exists.conf ' # include file only if it exists
#include = ' special.conf ' # include file
The directory or file specified in the above configuration file also needs to be backed up.

Second, check which is not required to back up the
1. Does the file in Pg_xlog need to be fully backed up?

After the database is closed, you can know what files need to be backed up by controlling the file's information.

Note that you want to view the database after it is closed, otherwise it is inaccurate.

Pg93@db-172-16-3-33-> Pg_controldata
Pg_control version number:937
Catalog version number:201304271
Database system identifier:5874470726249995168
Database Cluster State:shut down
Pg_control last Modified:sun 2013 11:28:29 AM CST
Latest Checkpoint location:e/e9000028
Prior Checkpoint location:e/e8000028
Latest checkpoint ' s REDO location:e/e9000028
Latest checkpoint ' s REDO WAL file:000000030000000e000000e9
Latest checkpoint ' s Timelineid:3
Latest checkpoint ' s Prevtimelineid:3
Latest checkpoint ' s Full_page_writes:on
Latest checkpoint ' s nextxid:0/221848459
Latest checkpoint ' s nextoid:26418
Latest checkpoint ' s nextmultixactid:1
Latest checkpoint ' s nextmultioffset:0
Latest checkpoint ' s oldestxid:150439034
Latest checkpoint ' s Oldestxid ' s db:12815
Latest checkpoint ' s oldestactivexid:0
Latest checkpoint ' s oldestmultixid:1
Latest checkpoint ' s Oldestmulti ' s db:1
Time of latest Checkpoint:sun 2013 11:28:29 AM CST
Fake LSN counter for unlogged RELS:0/1
Minimum Recovery Ending location:0/0
Min recovery ending Loc ' s timeline:0
Backup Start location:0/0
Backup End location:0/0
End-of-backup Record Required:no
Current Wal_level Setting:hot_standby
Current Max_connections setting:100
Current Max_prepared_xacts setting:0
Current Max_locks_per_xact setting:64
Maximum Data Alignment:8
Database Block size:8192
Blocks per segment of large relation:131072
WAL Block size:16384
Bytes per WAL segment:16777216
Maximum Length of identifiers:64
Maximum columns in a index:32
Maximum size of a TOAST chunk:1996
Date/time type storage:64-bit integers
FLOAT4 argument Passing:by value
Float8 argument Passing:by value
Data Page Checksum version:0
The information we need is

Latest Checkpoint location:e/e9000028
Latest checkpoint ' s REDO location:e/e9000028
Latest checkpoint ' s Timelineid:3
From these 3 messages, you can get 1 pg_xlog filenames representing the Xlog location of the last successful checkpoint of this database.

000000030000000e000000e9
For PostgreSQL 9.3 It's OK to look at this directly.

Latest checkpoint ' s REDO WAL file:000000030000000e000000e9
So the pg_xlog content that needs to be backed up is a series of xlog files that start at the beginning of this file and are generated later.

This is the only file in this case, as follows.

Pg93@db-172-16-3-33-> CD Pg_xlog
Pg93@db-172-16-3-33-> Ll-rt
-RW-------1 pg93 pg93 16M may 11:28 000000030000000e000000e9
DRWX------2 pg93 pg93 32K may 11:28 archive_status
2. Not required for files that are not part of the database system.

For example, in $pgdata,

Drwxr-xr-x 2 pg93 pg93 4.0K may 11:16 Pgbak
This directory is not a directory of database clusters, so no backup is required.

Also mention, try not to put other files or directories in $pgdata or other database directories, which will complicate the backup process because you have to filter these files and waste space without filtering.

3. Database logs also do not need to be backed up, such as the $pgdata/pg_log directory in this example.

Third, backup
1. Prepare the necessary space for your backup. can be local or offsite storage.

To view the size of a database:

digoal=# Select round (SUM (pg_database_size (OID))/1024/1024.0,2) | | MB ' from Pg_database;
? column?
----------
73.59MB
(1 row)
Ready to put down the entire backup directory

[Root@db-172-16-3-33 1999]# Df-h
FileSystem Size Used Avail use% mounted on
/dev/cciss/c0d0p1 29G 16G 12G 56%/
Tmpfs 6.9G 0 6.9G 0%/dev/shm
/dev/mapper/vgdata01-lv03
135G 25G 104G 20%/pgdata/digoal/1921/data03
/dev/mapper/vgdata01-lv04
135G 69G 59G 54%/pgdata/digoal/1921/data04
/dev/mapper/vgdata01-lv05
135G 85G 44G 66%/pgdata/digoal/1921/data05
/dev/mapper/vgdata01-lv06
98G 189M 93G 1%/mnt
/mnt/enc_dir 98G 189M 93G 1%/mnt/enc_dir

[Root@db-172-16-3-33 1999]# mkdir-p/pgdata/digoal/1921/data04/pg93backup
[Root@db-172-16-3-33 1999]# chown Pg93:pg93/pgdata/digoal/1921/data04/pg93backup
2. Stop the Library

Pg93@db-172-16-3-33-> pg_ctl stop-m Fast
Waiting for server to shut down ... done
Server stopped
3. Backup $pgdata, excluding Pg_xlog, Pg_log, and directory pgbak that do not need to be backed up.

pg93@db-172-16-3-33-> rsync-acvz-l--exclude "Pg_xlog"--exclude "Pgbak"--exclude "Pg_log" $PGDATA/PGDATA/DIGOAL/1 921/data04/pg93backup/
4. Backup Pg_xlog

Pg93@db-172-16-3-33-> Pg_controldata |grep Checkpoint
Latest Checkpoint location:e/eb000028
Prior Checkpoint location:e/ea000028
Latest checkpoint ' s REDO location:e/eb000028
Latest checkpoint ' s REDO WAL file:000000030000000e000000eb
Latest checkpoint ' s Timelineid:3
Latest checkpoint ' s Prevtimelineid:3
Latest checkpoint ' s Full_page_writes:on
Latest checkpoint ' s nextxid:0/221848464
Latest checkpoint ' s nextoid:26421
Latest checkpoint ' s nextmultixactid:1
Latest checkpoint ' s nextmultioffset:0
Latest checkpoint ' s oldestxid:150439034
Latest checkpoint ' s Oldestxid ' s db:12815
Latest checkpoint ' s oldestactivexid:0
Latest checkpoint ' s oldestmultixid:1
Latest checkpoint ' s Oldestmulti ' s db:1
Time of latest Checkpoint:sun 2013 12:24:37 PM CST
Create the Pg_xlog directory in the backup directory

Pg93@db-172-16-3-33-> mkdir-p/pgdata/digoal/1921/data04/pg93backup/pgdata1999/pg_xlog
Modify Directory Permissions

pg93@db-172-16-3-33-> chmod 700/pgdata/digoal/1921/data04/pg93backup/pgdata1999/pg_xlog
Find the Pg_xlog file you want

Pg93@db-172-16-3-33-> CD $PGDATA
pg93@db-172-16-3-33-> Ll-rt $PGDATA/pg_xlog/000000030000000e000000e*
-RW-------1 pg93 pg93 16M may 12:24/pgdata1999/pg_xlog/000000030000000e000000ea
-RW-------1 pg93 pg93 16M may 12:24/PGDATA1999/PG_XLOG/000000030000000E000000EB
Copy the required Pg_xlog file

pg93@db-172-16-3-33-> CP $PGDATA/pg_xlog/000000030000000e000000eb/pgdata/digoal/1921/data04/pg93backup/ pgdata1999/pg_xlog/
5. Check backup directory, Backup is normal

Pg93@db-172-16-3-33-> LL
Total 112K
DRWX------9 pg93 pg93 4.0K 16:33 Base
DRWX------2 pg93 pg93 4.0K may 11:49 Global
DRWX------2 pg93 pg93 4.0K may 14:52 Pg_clog
-RW-------1 pg93 pg93 4.6K 17:59 pg_hba.conf
-RW-------1 pg93 pg93 1.7K may 14:30 pg_ident.conf
DRWX------4 pg93 pg93 4.0K May 5 20:26 pg_multixact
DRWX------2 pg93 pg93 4.0K may 11:45 pg_notify
DRWX------2 pg93 pg93 4.0K May 5 20:26 pg_serial
DRWX------2 pg93 pg93 4.0K May 5 20:26 pg_snapshots
DRWX------2 pg93 pg93 4.0K may 11:49 Pg_stat
DRWX------2 pg93 pg93 4.0K may 11:49 pg_stat_tmp
DRWX------2 pg93 pg93 4.0K 15:30 Pg_subtrans
DRWX------3 pg93 pg93 4.0K may 11:15 PG_TBLSPC
DRWX------2 pg93 pg93 4.0K May 5 20:26 pg_twophase
-RW-------1 pg93 pg93 4 May 5 20:26 pg_version
DRWX------2 pg93 pg93 4.0K may 12:13 Pg_xlog
-RW-------1 pg93 pg93 20K may 09:36 postgresql.conf
-RW-------1 pg93 pg93 11:45 postmaster.opts
-rw-r--r--1 pg93 pg93 4.7K May 8 15:37 Recovery.done
-rw-r--r--1 pg93 pg93 2.5K may 13:46 root.crt
-rw-r--r--1 pg93 pg93 1.3K may 13:37 server.crt
-R--------1 pg93 pg93 1.7K 13:32 Server.key
Pg93@db-172-16-3-33-> CD pg_tblspc/
Pg93@db-172-16-3-33-> LL
Total 4.0K
DRWX------3 pg93 pg93 4.0K May 26 11:15 26417
6. Other
If the table space directory is very large, you can also make a separate backup.

Exclude the PG_TBLSPC directory when backing up $pgdata, and then step through the table space directory backup.

Four, restore
There are several issues to be aware of before restoring a cold backup:

The database software in the restore environment needs to be consistent with the version of the backup (for example, 9.0 of the backup cannot be restored to 9.1).

The small version of the database software in the restore environment is as far as possible consistent with the original environment (for example, the backup version is 9.0.4, the restored version also uses 9.0.4 as much as possible, please refer to release instructions for version upgrades).

The Lib library used by the database in the restore environment should be consistent with the original environment, such as PostGIS in the original environment, and the restore environment must compile the same version of PostGIS.

The database software compilation entries for the restore environment should be consistent with the original environment (especially the size of the blocks) found in the config.log of the original environment, either by using Pg_config or by viewing the contents of the control file:

./configure--prefix=/opt/pgsql9.3beta1--with-pgport=2099--with-segsize=8--with-wal-segsize=64- with-wal-blocksize=64--with-perl--with-python--with-openssl--with-pam--with-ldap- Enable-thread-safety
Or you can get it using Pg_config.

Pg93@db-172-16-3-33-> Pg_config--configure
'--prefix=/opt/pgsql9.3 '--with-pgport=1999 '--with-perl '--with-tcl '--with-python '--with-openssl ' With-pam '--without-ldap '--with-libxml '--with-libxslt '--enable-thread-safety '--with-wal-blocksize=16 ' Enable-debug '
The operating system platform is consistent (for example, Linux backups cannot be restored to Windows),

Consistent hardware architecture (for example, x86 backups cannot be restored to a small machine)

If you are restoring in your local environment, then you do not need to consider the above issues, if you are recovering on another server, please configure the restore environment as above, and then start recovery.

1. The database directory, table space directory, pg_xlog all deleted.

Pg93@db-172-16-3-33-> CD $PGDATA
Pg93@db-172-16-3-33-> RM-RF *
Pg93@db-172-16-3-33-> Cd/pgdata/digoal/1921/data03/pg93/1999/pg_xlog
Pg93@db-172-16-3-33-> RM-RF *
Pg93@db-172-16-3-33-> Cd/pgdata/digoal/1921/data03/pg93/1999/tbs_digoal
Pg93@db-172-16-3-33-> RM-RF *
2. Restore Backup

pg93@db-172-16-3-33-> cp-r/pgdata/digoal/1921/data04/pg93backup/pgdata1999/*/pgdata1999/
3. Create a log directory

Pg93@db-172-16-3-33-> CD $PGDATA
pg93@db-172-16-3-33-> mkdir Pg_log
pg93@db-172-16-3-33-> chmod Pg_log
4. If Pg_xlog, PG_TBLSPC uses a soft link.

Also restore:

Pg93@db-172-16-3-33-> mv/pgdata1999/pg_tblspc/26425/*/pgdata/digoal/1921/data03/pg93/1999/tbs_digoal/
Pg93@db-172-16-3-33-> rm-rf/pgdata1999/pg_tblspc/26425
Pg93@db-172-16-3-33-> ln-s/pgdata/digoal/1921/data03/pg93/1999/tbs_digoal/pgdata1999/pg_tblspc/26425
If it is not a soft chain, don't delete it.

5. Start the database

Pg93@db-172-16-3-33-> Pg_ctl Start
Server starting
pg93@db-172-16-3-33-> log:00000:loaded Library "Pg_stat_statements"
Location:load_libraries, miscinit.c:1296
6. Verify, check a few data to see.

Pg93@db-172-16-3-33-> Psql
Psql (9.3devel)
Type ' help ' for help.

digoal=# \db 
                            List of tablespaces 
     name    |  owner   |                     location                       
---------- --+----------+------------------------------------------------- 
 pg_default | Postgres |   
 pg_global  | postgres |  
 tbs_digoal | postgres |/pgdata/digoal/1921/data03/ pg93/1999/tbs_digoal 
(3 rows)  
digoal=# Select COUNT (*) from test; 
 count   ;
------- 
 10000 
(1 row)  

digoal=# \d test 
     Table "public.test"  
 column, |  type & nbsp; | modifiers  
--------+---------+----------- 
 id     | integer |   
Tablespace: "Tbs_digoal"