Posgresql backup and recovery

PostgreSQL provides three backup and recovery methods: SQL dump, file system replication, and online hot backup. Each backup method has its own advantages and disadvantages, which will be described in detail below.

9.1 SQL dump
This backup method generates a text file that contains SQL statements for creating various database objects and data in each table. In addition, the data in the index created on the table is not exported, but the index definition information is exported. When the database is restored, the index is rebuilt. You can use the database tools pg_dumpall and pg_dump for backup. Pg_dumpall backs up all information and data in a database cluster. Pg_dump only backs up the data of a database in the database cluster. It does not export information related to the role and tablespace, because the information is shared by the entire database cluster and does not belong to a separate database. The basic usage of pg_dump is as follows:
Pg_dumpDatabase Name>Backup File Name
Pg_dump writes the result to the standard output. You can use the OS redirection command to write the result to the file.

You can run the pg_dump command on the machine that runs the database or other machines. You can use options-H and-P to specify the Host Name of the running database and the port of the Database Listener. For example:
Pg_dump-H db_server1-P 5432
Product> backup_file
This command connects to the database listened to at Port 5432 on db_server1 and backs up the data of the database product to the file backup_file. If the pg_dump command does not use the-H and-P options, the environment variable pghost value is used as the machine name and the environment variable pgport value as the database port. If you do not define the environment variable pghost, the local name is used by default as the name of the machine running the database.

By default, pg_dump uses the current operating system user as the user used to connect to the database. You can use option-U or the environment variable pguser to specify the user name used to connect to the database. For example:
Pg_dump-u liming-H db_server1-P 5432
Product> backup_file
This command uses Liming to connect to the database listened to at Port 5432 on db_server1 and back up the data of the database product to the file backup_file.

Generally, the super user should be used to connect to the database for backup, because the super user can access any information in the database. If you use a common data user to connect to a database, some tables may not be accessible.

When pg_dump is run, other operations can be performed normally on the data. However, operations such as alter table that modify the definition of database objects will be affected, and may be waiting for a long time and cannot be executed. Therefore, when running the pg_dump command, do not modify Database object definitions in the database.

In addition, if some tables in the database use oid to implement foreign key constraints, the table OID information should be backed up at the same time during database backup, when pg_dump is used, add option-O to achieve this goal.
9.1.1
Restore database
The backup file created by pg_dump can be identified by Psql. Therefore, you can use Psql to read the backup files created by pg_dump to restore the database. For example:
PsqlDbname<Backup_file
The database specified by the dbname parameter after Psql must already exist. If the database does not exist, you should first create a database specified by dbname and then execute the data recovery command. Psql also supports command line options like pg_dump, such as-H and-P. When creating a database dbname, you must use template0 as the template database. You can use createdb to create a database, or run the SQL command CREATE DATABASE IN Psql to create a database. The following are two instances:
(1) createdb-T template0Dbname
(2) Create DatabaseDbnameTemplate =Template0

In addition, before performing the data recovery operation, users who have database objects in the database backup or who have access to these objects must already exist in the database. Otherwise, after the database is restored, the owner of the database objects in the Database Backup changes.

By default, the Psql command is executed until it is finished. Even if an SQL error occurs in the middle, the recovery operation continues. If you want Psql to stop the Restoration Operation after an error occurs during execution, you can run the following command in Psql before executing the Restoration Operation:
\ Set on_error_stop

If Psql encounters an error during execution, only a portion of the data is correctly restored, and the data in the recovered database is incomplete. Psql provides another recovery mode. In this mode, once the recovery operation encounters any errors during execution, the recovered data is automatically deleted from the database. You can use the command line option-l or -- Single-transaction of Psql to open this mode.
After the recovery operation is complete, use the analyze command to re-collect the statistics of the optimizer.
9.1.2
Use pg_dumpall

Pg_dump only backs up the data of a database in the database cluster. It does not export information related to roles and tablespaces. Pg_dumpall can be used to export data from all databases in the entire database cluster, and the definition information of roles, users, and tablespace is also exported. The General Command Format for using pg_dumpall is as follows:
Pg_dumpall>Backup_file

Pg_dumpall also supports command line options like pg_dump, such as-H and-P. You can also use Psql to restore the database from the backup file created by pg_dumpall. Database superusers should be used to restore the database. The command format is as follows:
Psql-FBackup_filePostgres

During execution, pg_dumpall uses s as the user name to connect to the database. The content in Postgres of the database automatically created by the system will also be exported, and the content in template0 and template1 of the database will not be exported.

9.1.3
Backup and recovery of Large Databases
If the database size is large, the size of the generated backup file is the maximum size of a single file allowed by the operating system, this problem can be solved by compressing and dividing the backup file into two parts.
(1) The compression method can be used by any compression tool provided by the operating system. Gzip is commonly used. For example:
Pg_dumpDbname| Gzip>Filename. GZ
Run the following command to restore the instance:
Gunzip-CFilename. GZ | PsqlDbname
You can also use the following command to restore the database:
CatFilename. GZ | gunzip | PsqlDbname

(2) divide the backup file into multiple parts. Use the Operating System Tool split. For example:
Pg_dumpDbname| Split-B 1 m -Filename
In this example, database backup is divided into multiple files with a size of 1 MB.
Run the following command to restore the instance:
CatFilename* | PsqlDbname

(3) Use the compression function provided by pg_dump. The backup file generated by this method is also compressed. Compared with the first method, it has the advantage that only the data of a table in the backup file can be restored. The command format for this method is as follows, that is, the option-Fc is added:
Pg_dump-FcDbname>Filename
You cannot use the Psql command to restore the data backed up in this way. You must use pg_restore to restore the data. The command format is as follows:
Pg_restore-dDbname
Filename

For a very large database, you can use both the first and second methods, or the second and third methods ).
9.2 File System Replication

File System replication this method directly copies all database files and stores them on other storage media. This is the simplest way to back up a database. You can use the command of the operating system to complete the backup, for example:
Tar-CF backup.tar/usr/local/pgsql/Data

Before copying data files, you must close the database. The backup file generated by this backup method is large because the index data is also backed up. When restoring the database, you only need to copy the backup file to the directory where the data file is stored.

9.3 online hot backup and archive restoration 9.3.1
Online Hot Backup
You do not need to close the database for online hot backup. The database can perform other operations normally. To enable online hot backup, the database must run in archive mode and set the parameter database archive_mode to on, then, set the archive_dir parameter to a directory with the read and write permissions of the operating system that starts the database, and the database runs in the archive mode. To make these two parameters take effect, you must restart the database.

Remarks: After Postgres is installed in windwos, you can open the command window in program-> PostgreSQL 8.2-> command prompt.

Command reference: http://www.postgresql.org/docs/8.1/static/app-psql.html