PostgreSQL Online Backup and recovery

1. Experimental environment

OS:  RedHat Linux enterprisedb 6.3db:postgresql 9.3PGHOME:/opt/postgresql/9.3pgdata:/opt/postgresql/9.3/ Data Archive directory:/opt/pg_archive base backup directory:/opt/base_archive

--directories such as archives and data in a production environment should be placed on different partitions, or on storage.

2. Modify the archive configuration to add test data

• Modify The following entry in the PG configuration file PostgreSQL. conf

Wal_level = Archivearchive_mode = on archive_command = ' cp-i%p/opt/pg_archive/%f </dev/null '

• Create an archive directory and restart the database for the archive to take effect

[Email protected]/]# mkdir-p/opt/pg_archive/[[email protected]/]# chown-r Postgres:postgres/opt/pg_archive/[[email  Protected]/]# chmod-r 700/opt/pg_archive/[[email protected]/]# mkdir-p/opt/base_archive[[email protected]/]# chown -R postgres:postgres/opt/base_archive[[email protected]/]# chmod-r 700/opt/base_archive[[email protected] opt]# Su- postgres-bash-4.1$  pg_ctl Restart

• Create a test table

-bash-4.1$ psql-d Testpsql.bin (9.3.6) Type ' help ' for help.test=# create TABLE time (CTime time); CREATE tabletest=# INSERT into time values (current_time), insert 0 1test=# select * from time;      CTime      -----------------11:40:26.229787 (1 row)
test=# \q

3. Perform a basic backup and add test data

• Back up the Data directory and compress

-bash-4.1$ psql postgres-c "Select Pg_start_backup (' Hot_backup ');" Pg_start_backup-----------------0/a000028 (1 row)- bash-4.1$  tar--exclude $PGDATA/pg_xlog-cvjpf/opt/base_archive/pgbackup.tar.bz2 $PGDATAtar: Removing leading '/' From member names/opt/postgresql/9.3/data//opt/postgresql/9.3/data/pg_clo......-bash-4.1$ psql postgres-c "Select PG _stop_backup (); " NOTICE:  pg_stop_backup Complete, all required WAL segments has been archived Pg_stop_backup----------------0/a0000 B8 (1 row)

--pg_start_backup will perform a checkpoint checkpoint and may take a while

--pg_stop_backup terminates the backup mode and automatically switches to the next Wal file until the last Wal segment file is archived and the Pg_stop_backup command returns.

• Continue to add test data

-bash-4.1$ psql-d Testpsql.bin (9.3.6) Type ' help ' for help.test=# insert into time values (current_time); Insert 0 1test=# Insert into time values (current_time), insert 0 1test=# select * from time;      CTime      -----------------11:40:26.229787 11:49:56.836129 11:52:04.235879 (3 rows) test=# \q

• Perform a manual archive

-bash-4.1$ Psqlpsql.bin (9.3.6) Type ' Help ' for help.postgres=# select Pg_switch_xlog (); Pg_switch_xlog----------------0/b000368 (1 row) postgres=# \q-bash-4.1$ ls-alt/opt/pg_archive/total 196628-rw------- . 1 postgres postgres 16777216  5 11:54 00000002000000000000000Bdrwx------. 2 postgres postgres     4096 may  5 1 1:54.-RW-------. 1 Postgres postgres      294 may  5 11:48 00000002000000000000000a.00000028.backup-rw-------. 1 postgres postgres 16777216  5 11:48 00000002000000000000000a-rw-------. 1 postgres postgres 16777216 may  5 11:45 000000020000000000000009-RW-------. 1 postgres postgres 16777216 may  5 000000020000000000000008drwxr-xr-x. 7 root     root         4096 may  4 12:01..

4. Simulating a database crash

[[email protected] pg_archive]# ps-ef |grep postgrespostgres 8112 1 0 pts/4 00:00:00/opt/postgresql/        9.3/bin/postgres-d/opt/postgresql/9.3/datapostgres 8113 8112 0 10:30?        00:00:00 Postgres:logger process Postgres 8115 8112 0 10:30?        00:00:00 postgres:checkpointer process Postgres 8116 8112 0 10:30?        00:00:00 postgres:writer process Postgres 8117 8112 0 10:30?        00:00:00 postgres:wal writer Process Postgres 8118 8112 0 10:30?        00:00:00 postgres:autovacuum launcher Process Postgres 8119 8112 0 10:30?        00:00:00 Postgres:archiver Process Last was 00000002000000000000000Bpostgres 8120 8112 0 10:30? 00:00:00 postgres:stats collector process root 9161 9034 0 14:02 pts/3 00:00:00 grep Postgres[[email prOtected] pg_archive]# kill-9 8112[[email protected] pg_archive]# ps-ef |grep postgresroot 9174 9034 0 14:06 PTS/3 00:00:00 grep postgres

5. Restore the database to the specified time 11:50:00

• Remove Data Directory

[Email protected] pg_archive]# Cd/opt/postgresql/9.3/[[email protected] 9.3]# MV Data Data_bak

-Generally for security reasons, when the storage space is sufficient, a backup of the file when the accident occurs, rather than deleting it directly. Specifically, the entire data directory and archive directory should not be deleted.

• Copy the underlying backup to the specified location and check the owner and permissions

[[email protected] 9.3]# cd/opt/base_archive/[[email protected] base_archive]# Lspgbackup.tar.bz2[[email protected] base_archive]# tar-xvf pgbackup.tar.bz2 ... [[Email protected] base_archive]# CD Opt/postgresql/9.3/[[email protected] 9.3]# lsdata[[email protected] 9.3]# cp-r data //opt/postgresql/9.3/[[email protected] 9.3]# cd/opt/postgresql/9.3/[[email protected] 9.3]# ls-al |grep datadrwx----- -. Root     root        4096  5 14:15 datadrwx------. postgres postgres 4096 may  5 11:48 data_bak[[email P Rotected] 9.3]# chown-r postgres:postgres data[[email protected] 9.3]# chmod-r data

• Create the Pg_xlog directory in the data directory

[Email protected] data]# mkdir-p/opt/postgresql/9.3/data/pg_xlog[[email protected] data]# chown-r postgres:postgres< C0/>/opt/postgresql/9.3/data/pg_xlog[[email protected] data]# chmod-r  /opt/postgresql/9.3/data/pg_xlog

• Creating subdirectories in the Pg_xlog directory

[Email protected] data]# mkdir-p/opt/postgresql/9.3/data/pg_xlog/archive_status[[email protected] data]# chown-r Postgres:postgres  /opt/postgresql/9.3/data/pg_xlog/archive_status/[[email protected] data]# chmod-r  opt/postgresql/9.3/data/pg_xlog/archive_status/

• In the $pgdata directory, create a recovery configuration file recovery.conf

--Recovery.conf will be automatically renamed to Recovery.done when the recovery is complete.

6. Check the data recovery situation

• Start the database service

[[Email protected] data]# service postgresql-9.3 startstarting PostgreSQL 9.3:pg_ctl:another server might be running; Trying to start server anywaywaiting for server to start .... doneserver Startedpostgresql 9.3 started successfully

• Connect to the database to see if it reverts to the corresponding point in time

[[email protected] archive_status]# su-postgres-bash-4.1$ psql-d testpsql.bin (9.3.6) Type ' Help ' for help.test=# Select * From time;      CTime      

You can see that the table has been successfully restored to a specified point in time.

Reference Document: Https://wiki.postgresql.org/wiki/9.1%E7%AC%AC%E4%BA%8C%E5%8D%81%E5%9B%9B%E7%AB%A0

PostgreSQL Online Backup and recovery