PostgreSQL Best Practice - Introduction to Cold Backup and Restoration - Alibaba Cloud Developer Forums: Cloud Discussion Forums

Gordon
Assistant Engineer
Assistant Engineer
  • UID622
  • Fans2
  • Follows0
  • Posts52
Reads:2456Replies:0

[PostgreSQL Development]PostgreSQL Best Practice - Introduction to Cold Backup and Restoration

Created#
More Posted time:Sep 23, 2016 9:39 AM
Background
PostgreSQL cold backup refers to the mechanism for backing up data files in databases when a database server is shut down.
This mechanism is not useful during production. Generally, a production environment requires the database service on a 7x24 basis.
Cold backup covers the main directory ($PGDATA), tablespace directory, and transaction log (pg_xlog) directory of a database cluster.
If other directories or files have been specified in a parameter file, cold backup must cover some of them as well.
I. Determine the backup scope.
1. Database directories
pg93@db-172-16-3-33-> cd $PGDATA

Main directory
pg93@db-172-16-3-33-> pwd  
/pgdata1999


Files and directories in the main directory
pg93@db-172-16-3-33-> ll  
total 140K  
drwx------ 9 pg93 pg93 4.0K May 25 16:33 base  
drwx------ 2 pg93 pg93 4.0K May 26 11:07 global  
drwxr-xr-x 2 pg93 pg93 4.0K May 26 11:16 pgbak  
drwx------ 2 pg93 pg93 4.0K May 24 14:52 pg_clog  
-rw------- 1 pg93 pg93 4.6K May 25 17:59 pg_hba.conf  
-rw------- 1 pg93 pg93 1.7K May 24 14:30 pg_ident.conf  
drwx------ 2 pg93 pg93  12K May 26 11:06 pg_log  
drwx------ 4 pg93 pg93 4.0K May  5 20:26 pg_multixact  
drwx------ 2 pg93 pg93 4.0K May 26 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 26 11:06 pg_stat  
drwx------ 2 pg93 pg93 4.0K May 26 11:16 pg_stat_tmp  
drwx------ 2 pg93 pg93  16K May 16 15:30 pg_subtrans  
drwx------ 2 pg93 pg93 4.0K May 26 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   44 May 26 11:15 pg_xlog -> /pgdata/digoal/1921/data03/pg93/1999/pg_xlog  
-rw------- 1 pg93 pg93  20K May 24 09:36 postgresql.conf  
-rw------- 1 pg93 pg93   27 May 26 11:06 postmaster.opts  
-rw------- 1 pg93 pg93   64 May 26 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 24 13:46 root.crt  
-rw-r--r-- 1 pg93 pg93 1.3K May 24 13:37 server.crt  
-r-------- 1 pg93 pg93 1.7K May 24 13:32 server.key


2. Transaction log directory
In this example, the directory is as follows:
$PGDATA/pg_xlog

3. Tablespace directory
View the tablespaces in the tablespace directory:
pg93@db-172-16-3-33-> cd pg_tblspc/  
pg93@db-172-16-3-33-> ll  
total 0  
lrwxrwxrwx 1 pg93 pg93 47 May 26 11:15 26417 -> /pgdata/digoal/1921/data03/pg93/1999/tbs_digoal


4. Files or directories (other than the main directory) specified in the configuration file
Access $PGDATA, and check whether files or directories other than $PGDATA are specified in 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 directories and files must be backed up as well.
II. Identify the files and directories that do not need to be backed up.
1. Check whether all the files in pg_xlog must be backed up.
After the database is shut down, check the control file to identify the files that need to be backed up.
Note that you need to shut down the database before checking the control file. Otherwise, the check result will not be accurate.
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 26 May 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 26 May 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 an 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 following information needs to the checked:
Latest checkpoint location:           E/E9000028  
Latest checkpoint's REDO location:    E/E9000028  
Latest checkpoint's TimeLineID:       3


The lines in the information indicate one pg_xlog file name, which represents the xlog location of the last successful checkpoint of the database.
000000030000000E000000E9

For PostgreSQL 9.3, check the following information:
Latest checkpoint's REDO WAL file:    000000030000000E000000E9

The information indicates that you only need to back up the indicated file as well as the xlog files generated after it.
In this example, only the indicated file must be backed up. Run the following commands:
pg93@db-172-16-3-33-> cd pg_xlog  
pg93@db-172-16-3-33-> ll -rt  
-rw------- 1 pg93 pg93 16M May 26 11:28 000000030000000E000000E9  
drwx------ 2 pg93 pg93 32K May 26 11:28 archive_status


2. Files other than database system files do not need to be backed up.
For example, $PGDATA contains the following directory:
drwxr-xr-x 2 pg93 pg93 4.0K May 26 11:16 pgbak

This directory is not a database cluster directory and therefore does not need to be backed up.
It is recommended that you do not store files or directories unrelated to the database system in the $PGDATA directory or other database directories. Storing such files and directories in the database directories will make backup complicated, because the files and directories must be excluded from the backup scope. If they are not excluded, the backup will require more storage space.
3. Database logs need not be backed up, such as the logs in $PGDATA/pg_log included in this example.
III. Back up data.
1. Prepare local or remote storage space for backing up the data.
View the database size as follows:
digoal=# select round(sum(pg_database_size(oid))/1024/1024.0,2)||'MB' from pg_database;  
 ?column?  
----------  
 73.59MB  
(1 row)


Prepare a directory that can hold the entire backup file.
[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. Shut down the database.
pg93@db-172-16-3-33-> pg_ctl stop -m fast  
waiting for server to shut down.... done  
server stopped


3. Back up $PGDATA, excluding pg_xlog, pg_log and pgbak.
pg93@db-172-16-3-33-> rsync -acvz -L --exclude "pg_xlog" --exclude "pgbak" --exclude "pg_log" $PGDATA /pgdata/digoal/1921/data04/pg93backup/

4. Back up 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 26 May 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

Search for the pg_xlog files to be backed up.
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 26 12:24 /pgdata1999/pg_xlog/000000030000000E000000EA  
-rw------- 1 pg93 pg93 16M May 26 12:24 /pgdata1999/pg_xlog/000000030000000E000000EB


Copy the pg_xlog files to be backed up.
pg93@db-172-16-3-33-> cp $PGDATA/pg_xlog/000000030000000E000000EB /pgdata/digoal/1921/data04/pg93backup/pgdata1999/pg_xlog/

5. Check the backup directory to see whether the backup process is normal.
pg93@db-172-16-3-33-> ll  
total 112K  
drwx------ 9 pg93 pg93 4.0K May 25 16:33 base  
drwx------ 2 pg93 pg93 4.0K May 26 11:49 global  
drwx------ 2 pg93 pg93 4.0K May 24 14:52 pg_clog  
-rw------- 1 pg93 pg93 4.6K May 25 17:59 pg_hba.conf  
-rw------- 1 pg93 pg93 1.7K May 24 14:30 pg_ident.conf  
drwx------ 4 pg93 pg93 4.0K May  5 20:26 pg_multixact  
drwx------ 2 pg93 pg93 4.0K May 26 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 26 11:49 pg_stat  
drwx------ 2 pg93 pg93 4.0K May 26 11:49 pg_stat_tmp  
drwx------ 2 pg93 pg93 4.0K May 16 15:30 pg_subtrans  
drwx------ 3 pg93 pg93 4.0K May 26 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 26 12:13 pg_xlog  
-rw------- 1 pg93 pg93  20K May 24 09:36 postgresql.conf  
-rw------- 1 pg93 pg93   27 May 26 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 24 13:46 root.crt  
-rw-r--r-- 1 pg93 pg93 1.3K May 24 13:37 server.crt  
-r-------- 1 pg93 pg93 1.7K May 24 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. Perform other operations.
If the tablespace directory is large in size, you can back up the tablespaces separately.
To do so, exclude the pg_tblspc directory when backing up $PGDATA and then back up the tablespaces in the directory separately.
IV. Restore data.
Before restoring data, pay attention to the following:
The database software version in the environment where data will be restored must be the same as the database software version used when the data is backed up. (For example, the data backed up for PostgreSQL 9.0 cannot be restored to PostgreSQL 9.1.)
If possible, the minor database software version in the environment where data will be restored must be the same as that in the environment where the data is backed up. (For example, if data is backed up for PostgreSQL 9.0.4, the data must be restored to PostgreSQL 9.0.4 if possible. For version upgrade details, refer to release notes.)
The database in the environment where data is to be restored must use the same library as the environment where the data is backed up. For example, if a PostGIS version is used in the environment where the data is backed up, the same version must be compiled for the environment where data will be restored.
The compilation items (especially the data block size) of the environment where data will be restored must be the same as those of the environment where the data is backed up. You can locate the items in config.log of the original environment or use pg_config to locate the items. The config.log contains the following information:
./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 --with-libxml --with-libxslt --enable-thread-safety

pg_config shows the following information:
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 environments must use the same OS. (For example, the data backed up using a Linux OS cannot be restored using a Windows OS.)
The environments must have the same hardware structure. (For example, the data backed up on anx86 computer cannot be restored to a mid-range computer.)
If data is restored locally, ignore the preceding requirements. Otherwise, configure the data restoration environment as required and start restoring the data.
1. Delete the database directory, tablespace directory, and pg_xlog.
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 data using the 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 700 pg_log


4. If pg_xlog and pg_tblspc use soft links,
restore the soft link.
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


Do not delete the links if they are not soft links.
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 data randomly.
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   | Modifiers  
--------+---------+-----------  
 id     | integer |  
Tablespace: "tbs_digoal"
Guest