PG Database Backup Restore __ Database

Backup

1, configure the archive mode

Configuring the archive requires editing the postgresql.conf file, which defaults to the/usr/local/pgsql/data/directory

Vim/usr/local/pgsql/data/postgesql.conf

Wal_level = Hot_standby

Archive_mode= on

archive_command= ' cp%p/usr/local/pgsql/backup/%f '//backup must have postgres user (database user) permissions, chown postgres backup

Note:%p the path to the log file to be archived,%f is the file name of the log file to be archived

2, start the database

Pg_ctl Start–d/usr/local/pgsql/data

3, create the database arch

Createdb Arch

4, create a table and insert a record

Psql Arch

arch=# CREATE TABLE TB (a int);

arch=# INSERT into TB (a) values (1);

5, create the underlying backup

Arch= #select pg_start_backup (' baseline ');

6, back up the entire data Directory

tar–jcvf/usr/local/pgsql/backup/baseline.tar.bz2/usr/local/pgsql/data/

7, stop Backup

Psql Arch

arch=# select Pg_stop_backup ();

8, insert new record, then switch log, repeat 3 times

arch=# INSERT into TB (a) values (2);

arch=# select Pg_switch_xlog ();

arch=# INSERT into TB (a) values (3);

arch=# select Pg_switch_xlog ();

arch=# INSERT into TB (a) values (4);

arch=# select Pg_switch_xlog ();

9, copy the Wal log files under/data/pg_xlog/to the preset archive directory to ensure that the resulting Wal logs are archived.

Recovery

1, Stop the database

Pg_ctl stop–d/usr/local/pgsql/data/

2, delete/data/

rm–r/usr/local/pgsql/data/

3, restore Backup

Tar–jxvf/usr/local/pgsql/backup/baseline.tar.bz2–c///If there is no permission, the root user may be required to perform

4, clear all files in the/data/pg_xlog/directory

rm–r/usr/local/pgsql/data/pg_xlog/

5, create the/pg_xlog/and the Archive_status directory below it

mkdir/usr/local/pgsql/data/pg_xlog/

Mkdir/usr/local/pgsql/data/pg_xlog/archive_status

6, create recovery.conf in/data/directory

Vim/usr/local/pgsql/data/recovery.conf

Restore_command= ' cp/usr/local/pgsql/backup/%f '%p '

7, start the database

Pg_ctl start–d/usr/local/pgsql/data/

All normal words the database will automatically apply the Wal log for recovery

8, see if the database arch is restored

Psql Arch

arch=# SELECT * from TB;

A

---

1

2

3

4

(4rows)

At this point, the data has been successfully recovered.