Summary: and everything that contains valuable data, like something, PostgreSQL databases should be backed up regularly. There are three ways to backup the PostgreSQL database, pros and cons of various methods, this article will speak about SQL dump method.
- 2.1 pg_dump; 2.2 pg_dumpall; 2.3 scheduled tasks;
- 1 Review; 2 data backup; 3 to recover from the dump; 4 to handle large databases; 5 of this article; 6 update log; 7. reference documentation; 8 related documentation;
Have you experienced because they do not carefully and delete a database table, or database storage disk damaged the situation? If you encounter this situation, I believe you will feel very frustrated, because you work for a few weeks of work may be wiped up. And any data containing precious things, like, PostgreSQL databases should be backed up regularly. If you store data in PostgreSQL table, you can make regular database backups to avoid the above tragedy. PostgreSQL comes with built-in tools to perform the backup, and damage to the system, or accident occurs, you can use these tools to "roll back", and through a previously saved snapshot of the system back to its initial state.
PostgreSQL PostgreSQL database from the backup data in three different ways:
File system level backup
Each backup has its own advantages and disadvantages, the following describes the SQl dump method;
(2) data backup;
SQL dump method is to create a text file, the text inside the SQL commands that, when fed back to the server, will recreate the same state as when the database dump.
PostgreSQL comes with built-in backup tool called pg_dump. This tool is to read through a series of SQL statements to a specified database and copy the contents to it as a snapshot and for future data recovery. Client to server connection is used to perform the backup job. The backup file is a snapshot in front of the speakers and can be used for future data recovery. And to ensure that client and server connection is required to perform backups. The basic usage of this command is:
pg_dump dbname> outfile
Note: Before carrying out the work, first make sure you have logged on to the server and access you will want to back up the database or table privileges. You can use the PostgreSQL command-line client - psql, the server login. Enter the client host name (-h), the user name (-u) and password (-p), and database name, then you can verify whether you are authorized to access.
Use pg_dump very simple - just at the command prompt type the name of the database to be exported back to work can be carried out, the following example (based on your own path to change the PostgreSQL installation path):
[email protected]:~$ /usr/lib/postgresql/8.2/bin/pg_dump -D -h localhost -U xiaop( User name ) mydb( Database name ) > mydb.bak
Through the above command will create a file called mydb.bak files, records used to restore the database in the SQL command.
As you can see, pg_dump writes its output to standard output. Here we can see to do any good.
pg_dump is a regular PostgreSQL client application (albeit a clever one) which means you can access from any remote host that the database backup job. But remember that pg_dump does not operate with special permissions. Specifically, it must have a backup of the table you want to read permissions, so, in fact, you almost always have a database superuser.
Pg_dump to declare which user should be connected, use the command line options-h host and-p port. The default host is localhost or your PGHOST environment variable specifies the value. Similarly, the default port is the environment variable PGPORT or (if it does not exist) the compiled-in default values. (Servers usually have the same default, so fairly easily.)
And any other PostgreSQL client application, pg_dump will by default use the current operating system user name to connect as the database user name. To override this, either specify the-U option or set the environment variable PGUSER. Remember that pg_dump connections are subject to the same client application through the normal client authentication mechanisms.
Backup created by the pg_dump are internally consistent, that is, in pg_dump is running the database update will not be dumped. pg_dump does not block other work on the database. (Exceptions are those operations require exclusive lock, such as VACUUM FULL.)
Note:: If your database schema relies on OID (for instance as foreign keys), then you must instruct pg_dump to OID also poured out. To pour OID, you can use the-o command line option. By default it will not dump "large objects." If you use large objects, refer to the pg_dump reference page.
If you want the system to back up all the words of the database (rather than just a database of a backup), you can use the command pg_dumpall instead of pg_dump. Run this command for PostgreSQL can recognize all of the database (including its own system database) backup to a file. Here is an example use:
[email protected]:~$ /usr/lib/postgresql/8.2/bin/pg_dumpall -D -h localhost -U xiaop( User name ) > all.bak
This will be all the localhost database to a file in a all.bak;
2.3 Scheduled Tasks;
To ensure your backup and keep updated, you can add to the cron table is pg_dumpall command pg_dump or to regularly backup. Here are two examples of cron entries. The first is at 3 am every day of the test database backup, and the second in the 9 pm every Friday for all database backup:
[email protected]:~$ 0 3 * * * /usr/lib/postgresql/8.2/bin/pg_dump -D -h localhost -U xiaop( User name ) mydb( Database name ) > /home/xiaop/mydb.bak0 21 * *
[email protected]:~$ 5 /usr/lib/postgresql/8.2/bin/pg_dumpall -D -h localhost -U xiaop( User name ) > /home/xiaop/all.bak
3. Restoring the dump
Restore using pg_dump 3.1;
Recover data from a backup job even easier than the backup - you have to do is backup file by executing SQL commands to restore the database. If you are using a database of a pg_dump backup, then the backup will have the CREATE TABLE statement to copy the source table. Of course, you must first create a new empty database to store the data tables. You can use the createdb tool to complete this work this step, the tool suite is part of PostgreSQL:
[email protected]:~$ /usr/lib/postgresql/8.2/bin/createdb mydb( Database name )
Now you can execute SQL commands in the backup file to restore the database, pg_dump generated text file can be read by the psql program. Used to restore a dump from the command format is:
psql dbname <infile
The following example:
[email protected]:~$ /usr/lib/postgresql/8.2/bin/psql -h localhost -U xiaop( User name ) -d mydb( Database name ) < mydb.bak
3.2 with pg_dumpall recovery;
If you are using pg_dumpall all the database backup, there is no need to first create a database, because the backup file already contains a complete CREATE DATABASE work-related calls. Here, only the psql command-line client, enter the corresponding backup file on it, without the need to specify the target database:
[email protected]:~$ /usr/lib/postgresql/8.2/bin/psql -h localhost -U xiaop( User name ) < all.bak
Once the data recovery is complete, you can log into the server and view the recovered data.
Once restored, run ANALYZE on each database is a wise move, so the optimizer has useful statistics data. You can always run vacuumdb-a-z to VACUUM ANALYZE all databases; this is equivalent to manually run the VACUUM ANALYZE;
4 to handle large databases;
4.1 out large databases;
Since PostgreSQL allows tables larger than your system allows the maximum file size, the table may be dumped to a file, because the resulting file will likely be more than your system allows the maximum file size. Since pg_dump can write to standard output, you can use standard Unix tools to work around this problem:
Use compressed dumps. Use your favorite compression program, for example gzip.
[email protected]:~$ pg_dump mydb( Database ) | gzip > mydbBACK.gz
4.2 to restore a large database;
With the following command to restore:
[email protected]:~$ cat mydbBACK.gz | gunzip | psql mydbNEW
4.3 Using the split;
The split command allows you to use the following method to output into an acceptable size of the operating system. The use of the split in the "file segmentation tool split and combine cat introduced" in the query. For example, so that each block size of 1 megabyte:
[email protected]:~$ pg_dump dbname | split -b 1m - filename
After the split can be restored with the following command:
5 About this article;
For PostgreSQl database backup and recovery of the other two methods, "File system level backup" and "online backup", we discussed later, most of this information are based on Chinese documents, the purpose is to allow the brothers to find convenient, detailed things in the Chinese documents are, thank you brothers pointing:)
6 update log;
7. Reference documentation;
"PostgreSQL 8.1 Chinese documents."
8 relevant documents;
"PostgreSQL installation and simple to use."
"PostgreSQL configuration files and user permissions"
"PostgreSQL database user authentication."
"PostgreSQL database maintenance work."