Shulou(Shulou.com)06/01 Report--

Postgresql Learning Notes (5) backup and recovery

1. Backup tools: pg_dump and pg_dumpall

Pg_dump can back up a specified database

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

2. Pg_dump and pg_dumpall tools do not support setting login passwords in command line options, so in order to perform automated tasks, you need to create a password file .pgpass under the home folder of your postgres operating system account; or you can use the PGPASSWORD environment variable to set the password.

3. If you want to back up every day, pg_dump is more appropriate than pg_dumpall, because the former supports specifying exactly which tables to back up, schema and database, while the latter does not.

4. Pg_dump can back up data to SQL text file format, or back up to user-defined compressed format or TAR package format. During data recovery, backup files in compressed format and TAR package format can be restored in parallel. This feature is supported from version 8.4.

5. Pg_dumpall tool can export all database data in the current postgresql service instance to SQL text (pg_dumpall does not support exporting formats other than SQL text), and can also export global objects such as inter-table definitions and roles at the same time.

6. Pg_dump usage:

(1) back up a database, and output the backup results in a custom compressed format:

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 result is output as SQL text, and the CREATE DATABASE statement should be included in the output.

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 output the backup results in a custom compressed 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 hr and payroll schema of a database, and output the backup results in a custom compressed 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 all the data in a database except the data in public schema, and output the backup results in a custom compressed 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 generated INSERT statement is a standard format with a list of field names, which can be used to import data into a lower version of PostgreSQL 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) backup in directory format, which solves the problem that the size of a single file may exceed the operating system limit when using other backup formats.

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

(8) parallel backup in directory format

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, while this large SQL text backup is used at the full library level.

Data recovery is extremely time-consuming, so it is generally recommended that you only use pg_dumpall to back up global objects rather than full database data. If you must use pg_dumpall to back up full database data, it is usually enough to perform it once a month.

(1) only backup roles and tablespace definitions

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

(2) if you only need to back up the role definition without backing up the tablespace, you can 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 backups in SQL text format generated by pg_dump or pg_dumpall tools

(2) use the pg_restore tool to restore custom compressed format, TAR package format or catalog format backups generated by the pg_dump tool

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

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

Psql-U postgres-f myglobals.sql

(2) restore a SQL backup file, and stop the recovery if you encounter any errors

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 the restore action, create the target database

Create database mydb

Perform a restore:

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

(2) if the database used for backup and recovery has the same name, you can omit the process of building a separate database by adding the-- create option.

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

(3) restore the table structure without restoring the table data

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.