PostgreSQL database Backup

I. Tool BACKUP Data

Open a command window under Windows: Start the directory with the->cmd-> installation database and enter the bin directory;

Export command: pg_dump–h localhost–u postgres–p 5432–d postgres–f "D:/test.dmp"

-H: Server address;

-P: Port number;

-U: "U" here to capitalize, user identity

-D: Database name;

-F: The directory and name of the file output;

Recover data:

Psql-h localhost-u postgres-d new_db-f "D:\test.dmp"

Two. Backup Archive

Create a WAL backup directory, for example: D:\pg_xlog_archive 1

Modify the Archive_command settings in the postgresql.conf, for example: Archive_command = ' copy '%p ' d:\\pg_xlog_archive\\%f ' Note:%p to use dual  Quotation marks are handled in cases where there are spaces in the installation directory. Archive_mode = on wal_level= ' archive '

2 Log in to the database server with Psql, and then perform the following procedure Psql-ubeigang < Beigang_dump.pgdump:SELECT pg_reload_conf ();  

3 Psql then executes the process SELECT pg_start_backup (' label '); Note: Label recommends using the full path of the backup directory.

4 Psql then executes the process SELECT pg_stop_backup ();  

5 Check the Wal log archive directory, and some will succeed.

Recovery:

 1, stopping the database

2. Delete   data directory

3, restore backup

jar cvfm Classes.jar mymanifest-c foo/.

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

 5, create/pg_xlog/and its archive_status directory below

6, create recovery.conf

in the/data/directory

7. Start the database

Three. Cold Backup

postgresql cold Backup, which is a backup of a database's data files in a database shutdown

Cold backups need to be backed up by the database Cluster home directory ($PGDATA), tablespace directory, and transaction log (Pg_xlog) directory.

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

Restore: Environment, database version consistent; Replace the above backup content (delete first)

Four-master copy backup

1. Primary Database Configuration

Open the postgresql.conf file in the data directory and make the following changes:

2. listen_address = ' * ' (default localhost)
4. Wal_level = Hot_standby (default is minimal)
6. max_wal_senders=2 (default is 0)
8. Wal_keep_segments=64 (default is 0)
9. Create a Superuser in the main library to be specifically responsible for letting standby connect to drag the Wal log：create user repl superuser password ' 111111 ';
10. Open the pg_hba.conf file in the data directory and make the following changes:
    

2 Configuring from the database

The Pg_basebackup command-line tool generates the underlying backup from the library, with the following command:

./pg_basebackup-H192.168.111.101-U REPL-F P-X-P-R-d/usr/local/postgresql/Data/-L replbackup201611
From the postgresql.conf file in the Library data directory,hot_standby  change to Enabled state, i.e. hot_standby=on  

PostgreSQL database Backup