PostgreSQL backup and recovery-SQL dump

Have you ever accidentally deleted a database table or damaged the disk where the database is stored? If you encounter this situation, I believe you will feel very frustrated, because the results of your hard work for a few weeks may be overwhelmed. Just like anything that contains precious data, PostgreSQL databases should also be backed up frequently. If you store data in a PostgreSQL table, you can back up the database regularly to avoid the above tragedy. PostgreSQL comes with built-in tools to perform backup. In addition, you can use these tools to perform "rollback" in case of system corruption or exceptions ", and restore the system to its initial state through the previously saved snapshots.

1. Summary;

There are three completely different methods for PostgreSQL database to back up PostgreSQL data:

SQL dump

File System-level backup

Online backup

Each type of backup has its own advantages and disadvantages. The following describes the SQL dump method;

2. Data Backup;

The SQL dump method is to create a text file, which contains SQL commands. When the file is returned to the server, the database in the same state as the dump will be rebuilt.

2.1 pg_dump;

PostgreSQL's built-in backup tool is pg_dump. This tool reads a specified database through a series of SQL statements, copies the content of the database, takes it as a snapshot, and uses it for future data recovery. The client-to-server connection is used for backup. These backup files are the snapshots mentioned above and can be used for future data recovery. To ensure the connection between the client and the server is necessary for backup. The basic usage of this command is:

Pg_dump dbname> OUTFILE

Note: before performing the preceding operations, make sure that you have the permissions to log on to the server and access the database or table to be backed up. You can use PostgreSQL command line client-Psql to log on to the server. Enter the Host Name (-h), user name (-U), password (-P), and database name in the client, and then check whether you are authorized to access the database.

Using pg_dump is very simple-you only need to enter the name of the database to be exported after the command prompt, as shown in the following example (change the PostgreSQL path based on your own installation path ):

Xiaop @ xiaop-LAPTOP :~ $/Usr/lib/PostgreSQL/8.2/bin/pg_dump-D-H localhost-u xiaop (User Name) mydb (Database Name)> mydb. Bak

The preceding command creates a file named mydb. Bak, which records the SQL commands used to restore the database.

As you can see, pg_dump outputs the results to the standard output. We can see below what are the benefits of doing so.

Pg_dump is a common PostgreSQL client application (although it is quite clever .) This means that you can back up data from any remote host that can access the database. However, remember that pg_dump does not run with any special permissions. Specifically, it must have the read permission for the table you want to back up. Therefore, you almost always need to become a database superuser.

To declare which user should pg_dump be connected, use the command line options-H host and-P port. The default host is the value declared by the local host or your environment variable pghost. Similarly, the default port is the environment variable pgport or compiled default value (if it does not exist. (The server usually has the same default value, so it is quite convenient .)

Like any other PostgreSQL client application, pg_dump does not require time-saving connection with a database user name with the same name as the current operating system user name. To override this name, either declare the-u option or set the environment variable pguser. Note that the connection of pg_dump must also pass the customer authentication mechanism like that of common customer applications.

The backups created by pg_dump are consistent internally. That is to say, database updates will not be dumped when pg_dump is running. Pg_dump does not block other database operations. (But it will block the operations that require the exclusive lock, such as vacuum full .)

NOTE: If your database structure depends on OID (for example, used as a foreign key), you must tell pg_dump to reverse the oId. To reverse the OID, you can use the-O command line option. It also does not dump "large objects ". If you are using a large object, see the pg_dump command manual page.

2.2 pg_dumpall;

If you want to back up all the databases in the system (instead of backing up only one database), you can run the pg_dumpall command instead of pg_dump command. Execute this command to back up all the databases (including their own system databases) recognized by PostgreSQL to a file. An example is provided below:

Xiaop @ xiaop-LAPTOP :~ $/Usr/lib/PostgreSQL/8.2/bin/pg_dumpall-D-H localhost-u xiaop (username)> All. Bak

In this way, all the databases of localhost will be backed up to the All. Bak file;

2.3 scheduled tasks;

To ensure that your backup time remains updated, You can regularly perform the backup by adding the pg_dump or pg_dumpall command to the cron table. Two cron entries examples are provided here. The first is to back up the test database at every day, and the second is to back up all the databases at every Friday:

Xiaop @ xiaop-LAPTOP :~ $0 3 ***/usr/lib/PostgreSQL/8.2/bin/pg_dump-D-H localhost-u xiaop (username) mydb (Database Name)> /home/xiaop/mydb. bak0 21 **
Xiaop @ xiaop-LAPTOP :~ $5/usr/lib/PostgreSQL/8.2/bin/pg_dumpall-D-H localhost-u xiaop (User Name)>/home/xiaop/all. Bak

3. Restore from dump

3.1 restore with pg_dump;

Restoring data from a backup is easier than performing a backup. All you need to do is execute the SQL command in the backup file to restore the database. If you use pg_dump to back up a database, the create table statement will be used in the backup to copy the source table. Of course, you must first create an empty database to store these data tables. You can use the createdb tool to complete this step. This tool is also part of the PostgreSQL suite:
Xiaop @ xiaop-LAPTOP :~ $/Usr/lib/PostgreSQL/8.2/bin/createdb mydb (database name)

Now you can execute the SQL command in the backup file to restore the database. The text file generated by pg_dump can beProgramRead. The common command format for recovering from the dump is:

Psql dbname <infile

For example:

Xiaop @ xiaop-LAPTOP :~ $/Usr/lib/PostgreSQL/8.2/bin/Psql-H localhost-u xiaop (User Name)-D mydb (Database Name) <mydb. Bak

3.2 restore with pg_dumpall;

If you use pg_dumpall to back up all the databases, there is no need to create a new database because the backup file contains the call related to completing the create database operation. Here, you only need to enter the corresponding backup file in the Psql command line client, instead of specifying the target database:

Xiaop @ xiaop-LAPTOP :~ $/Usr/lib/PostgreSQL/8.2/bin/Psql-H localhost-u xiaop (User Name) <all. Bak

Once the data recovery is complete, you can log on to the server and view the recovered data.

3.3 analyze;

Once the recovery is completed, it is wise to run analyze on each database, so that the optimizer has useful statistics. You can always run vacuumdb-a-Z to vacuum analyze all databases; this is equivalent to running vacuum analyze manually;

4. process large databases;

4.1 output large databases;

Because PostgreSQL allows a table to be larger than the maximum file size allowed by your system, it is possible to dump the table to a file, because the generated file may be larger than the maximum file allowed by your system. Because pg_dump is output to the standard output, you can use the standard UNIX tool to bypass this problem:
Use a compressed dump. Use a compressed program that you are familiar with, such as gzip.

Xiaop @ xiaop-LAPTOP :~ $ Pg_dump mydb (Database Name) | gzip> mydbback.gz

4.2 restore large databases;

Run the following command to restore data:
Xiaop @ xiaop-LAPTOP :~ $ Createdb mydbnew (new database name)
Xiaop @ xiaop-LAPTOP :~ $ Gunzip-C mydbback.gz | Psql mydbnew

Or
Xiaop @ xiaop-LAPTOP :~ $ Cat mydbback.gz | gunzip | Psql mydbnew

4.3 Use split;

4.3.1 segmentation;

The split command allows you to use the following method to split the output into acceptable sizes of the operating system. The usage of split can be found in "cat introduction to file splitting and splitting Tools. For example, set the size of each block to 1 MB:
Xiaop @ xiaop-LAPTOP :~ $ Pg_dump dbname | split-B 1 m-filename

4.3.2 merge;

You can use the following command to restore the split:
Xiaop @ xiaop-LAPTOP :~ $ Createdb dbname
Xiaop @ xiaop-LAPTOP :~ $ Cat filename * | Psql dbname

5. About this article;

We will discuss the other two methods of postgresql database backup and recovery, file system-level backup and online backup. Most of the information in this article is based on Chinese documents, the purpose is to make it easier for the brothers to search for details in Chinese documents. Thank you for your advice :)