PostgreSQL Learning Notes (v) Backup and recovery

PostgreSQL Learning Notes (v) Backup and recovery

1. Backup tools: Pg_dump and Pg_dumpall

The pg_dump can back up a specified database

Pg_dumpall can back up all database data and system global data at once

2. The Pg_dump and Pg_dumpall tools do not support setting a login password in command-line options, so in order to facilitate automatic task execution, You need to create a password file under the home folder of the Postgres operating system account. Pgpass to store the password, or you can use the PGPASSWORD environment variable to set the password.

3, if you want to make a backup every day, then use pg_dump more appropriate than Pg_dumpall, because the former support the precise designation of the table to be backed up, schema and database, the latter is not supported.

4, Pg_dump can back up the data into the SQL text file format, also supports the backup for the user-defined compression format or the TAR package format, in the data recovery, the compressed format and the format of the TAR package backup files can be implemented in parallel recovery, the feature is supported from version 8.4.

5. The Pg_dumpall tool can export all database data in the current PostgreSQL service instance to SQL text (Pg_dumpall does not support formatting other than the export of SQL text), or you can export global objects such as inter-table definitions and roles.

6. Pg_dump Usage:

(1) Back up a database, the backup results in a custom compression format output:

Pg_dump-h 127.0.0.1-p 5432-u postgres-f c-b-v-f test.backup test

(2) Back up a database, the backup results are output in SQL text, the output should include the CREATE DATABASE statement;

Pg_dump-h 127.0.0.1-p 5432-u postgres-c-F p-b-v-f Test.sql test

(3) Back up all tables in a database whose names begin with "pay", and the results are output in a custom compression format:

Pg_dump-h 127.0.0.1-p 5432-u postgres-f c-b-v-t *.pay*-F pay.backup Test

(4) Back up all the data in the two schemas of the HR and payroll in a database, and the backup results are output in a custom compression format:

Pg_dump-h 127.0.0.1-p 5432-u postgres-f c-b-v-n hr-n payroll-f hr.backup mydb

(5) Back up any data in a database other than the public schema, and the backup results are output in a custom compression format:

Pg_dump-h 127.0.0.1-p 5432-u postgres-f c-b-v-n public-f all_sch_except_pub.backup mydb

(6) Back up the data as a SQL text file, and the resulting INSERT statement is a standard format with a list of field names that can be used to import data into PostgreSQL below the current version or other non-PostgreSQL databases that support SQL.

Pg_dump-h 127.0.0.1-p 5432-u postgres-f p--column-inserts-f select_tables.backup mydb

(7) directory format backup resolves an issue in which a single file size that may exist in other backup formats exceeds the operating system limit

Pg_dump-h 127.0.0.1-p 5432-u postgres-f d-f/somepath/a_directory mydb

(8) directory format parallel backup

Pg_dump-h 127.0.0.1-p 5432-u postgres-j 3-fd-f/somepath/a_directory mydb

7. Pg_dumpall recommends that global objects such as roles and tablespace definitions be backed up every day, but it is not recommended to use Pg_dumpall to back up full-Library data every day, because Pg_dumpall only supports exporting to SQL text format. And using this huge SQL text backup for the full-Library level

Data recovery is extremely time-consuming, so it is generally only recommended to use Pg_dumpall to back up global objects instead of full-database data, if you must use Pg_dumpall to back up the whole library data, usually one months to execute once is enough.

(1) Backup roles and tablespace definitions only

Pg_dumpall-h localhost-u postgres--port=5432-f myglobals.sql--globals-only

(2) Only need to back up the role definition without backing up the tablespace, then add the--roles-only option

Pg_dumpall-h localhost-u postgres--port=5432-f myglobals.sql--roles-only

8. Data recovery method

(1) Use Psql to restore data backup of SQL text format generated by pg_dump or Pg_dumpall tool;

(2) Use the Pg_restore tool to restore the custom compression format generated by the Pg_dump tool, the TAR package format, or the directory format backup

9. Use Psql to restore data backup in SQL text format

(1) Restore a SQL backup file and ignore any errors that may occur during the process

Psql-u postgres-f Myglobals.sql

(2) Restore a SQL backup file, in case of any errors stand up to stop recovery

Psql-u postgres--set on_error_stop=on-f myglobals.sql

(3) Restore the data in the SQL text to a specified database:

Psql-u postgres-d mydb-f Select_objects.sql

10. Use Pg_restore for recovery

(1) before using Pg_restore to perform a recovery action, create a target database

Create DATABASE MyDB;

Perform recovery:

Pg_restore--dbname=mydb--jobs=4--verbose mydb.backup

(2) If the database with the same name is used for backup and restore, you can omit the process of building the library separately by adding the--create option

Pg_restore--dbname=postgres--create--jobs=4--verbose mydb.backup

(3) Restore table structure without restoring table data

Create DATABASE Mydb2;pg_resotre--dbname=mydb2--section=pre-data--jobs=4 mydb.backup

This article is from the "Corasql" blog, make sure to keep this source http://corasql.blog.51cto.com/5908329/1920949

PostgreSQL Learning Notes (v) Backup and recovery