Postgresql database server, the backup and restore ------ SQL dump papers

2011-02-13  来源:本站原创  分类:Database  人气:140 

Author: P
From: LinuxSir.Org
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.


 Directory


+++++++++++++++++++++++++++++++++++++++++++
Text
+++++++++++++++++++++++++++++++++++++++++++

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.


1 Review;

PostgreSQL PostgreSQL database from the backup data in three different ways:

SQL dump

File system level backup

Online 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.


2.1 pg_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.


2.2 pg_dumpall;

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.


3.3 ANALYZE;

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]:~$ createdb mydbNEW( New database name )
[email protected]:~$ gunzip -c mydbBACK.gz | psql mydbNEW

Or

[email protected]:~$ cat mydbBACK.gz | gunzip | psql mydbNEW


4.3 Using the split;


4.3.1 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


4.3.2 merge;

After the split can be restored with the following command:

[email protected]:~$createdb dbname
[email protected]:~$cat filename* | psql dbname


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."

相关文章
  • Postgresql database server, the backup and restore ------ SQL dump papers 2011-02-13

    Author: P From: LinuxSir.Org 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 artic

  • Postgresql backup and restore ------ SQL dump papers 2011-03-05

    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

  • mysqldump database backup and restore, sql file import and export Xiangjie 2010-04-07

    mysqldump backup restore mysqldump import and export statements Daquan Xiangjie mysqldump backup: mysqldump-u username-p password-h host database, a-w "sql conditions" - lock-all-tables> path Case: mysqldump-uroot-p1234-hlocalhost db1 a-w &qu

  • SQL Server 2008 backup and restore issues 2011-04-19

    Today, the restored backup sql server 2008 database, throw such an error. error: the media set has 2 media families but only 1 are provided. all members must be provided. To find a post illustrates the problem. I'm the PM that owns SQL backup. I'll s

  • SQL Server 2005 backup and restore. Bak files 2011-05-23

    1, right-click the instance of SQL Server 2005 under the "database" folder. Is with security, server object at the same level of the directory. Select New Database. 2, add the database name, database name and the name of the backup line, click O

  • (Sql Server advanced skills) to use for backup and restore SQL statement 2011-08-22

    (1) backup database BBS to disk = 'c: / northwind.bak' - Filelistonly see the backup file information restore filelistonly from disk = 'c: / northwind2.bak' restore database BBS1 from disk = 'c: / northwind2.bak' with move 'BBS_Data' to 'C: / BBS1.md

  • Replication and backup and restore SQL table 2011-05-04

    -- If the destination table already exists : insert into DATAHR.DBO.GBITEM select * from DEMO.DBO.GBITEM -- If the destination table does not exist : select * into DATAHR.DBO.GBITEM from DEMO.DBO.GBITEM -- Cross-database import select * into [zk_news

  • T-SQL backup and restore 2010-06-10

    Summarizes some commonly used backup to restore SQL Server T-SQL statement, the code is basically to support 2005/2008, but some features require Enterprise Edition database: Original Address http://www.cnblogs.com/bhtfg538/archive/2009/09/14/1566127

  • mysql import and export. sql file backup and restore database 2010-12-11

    mysql import and export. sql file backup and restore database Database export database file: 1. Mydb database export to e: \ mysql \ mydb.sql file: Open the Start - "Run -> enter cmd into the command line c: \> mysqldump-h localhost-u root-p my

  • Detailed Backup and restore postgresql 2011-03-07

    Backup and restore Chapter PostgreSQL provides three backup and recovery methods: SQL dump, file system replication and online hot backup. Each backup has its own advantages and disadvantages, detailed below. 9.1 SQL Dump This backup method produces

  • mysqldump backup restore (sql level) 2010-06-16

    (Mysqldump command in the mysql / bin / directory) Existing environment with two mysql servers, one IP is: 192.168.102.2 192.168.102.3 another (1) a full backup of a MySQL database mysqldump-h hostname-u username-p password databasename> backupfile.s

  • MYSQL database backup and restore common statement 2010-07-12

    MySQL database backup command, you can add options to select different parameters to achieve the requirements of different formats. mysqldump-h host-u username-p password database name> file Backup MySQL database to delete the table with the format,

  • oracle database backup and restore (local and remote operation) 2010-12-17

    oracle database backup and restore (local and remote operation) Execution environment: Can SQLPLUS.EXE or DOS (command line) in the implementation, DOS can be executed in the oracle 8i in the installation directory \ ora81 \ BIN is set to the global

  • restore database from online backup 2010-12-21

    db2 => list history backup all for db sample List History File for sample Number of matching file entries = 2 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID -- --- ------------------ ---- --- ------------ ------------ ---------

  • T-SQL statement to implement database backup and restore 2011-01-12

    Using T-SQL statement, implement database backup and restore features Embodied knowledge of SQL Server in four points: 1. Get SQL Server default directory on the server 2. Backup using SQL statement 3. Restore the use of SQL statement, taking into ac

  • SMO + PowerShell achieve SQLServer database backup and restore 2011-05-17

    Recently, demand, need to use the script / command line in the form of implementation of the database backup and restore! Try several times, and that depressed ah! So few examples of the Internet, most of them and can not run, it is silent people, ge

  • Database backup and restore processing 2011-07-16

    Using T-SQL statements, implementing database backup and restore features in SQL Server reflects the knowledge of the four points: 1. Get SQL Server default directory on the server 2. Backup using SQL statements 3. Restore the use of SQL statements,

  • Database backup and restore 2011-04-19

    http://www.21andy.com/blog/20071102/655.html MySQL database backup command mysqldump-hhostname-uusername-ppassword databasename> backupfile.sql Backup MySQL database to delete a table format with mysqldump - add-drop-table-uusername-ppassword databas

  • oracle database backup and restore (both local and remote operation) 2010-12-17

    oracle database backup and restore (both local and remote operation) Execution environment: Can SQLPLUS.EXE or DOS (command line) to perform, DOS can be executed in due oracle 8i installation directory \ ora81 \ BIN is set to the global path, The dir

  • Backup and restore database with stored procedures 2011-04-12

    Backup and restore database with a stored procedure or if you just back up the database tables, the MySQL database directory data folder or copy the relevant contents of the command prompt type the following statement to complete backup. This tutoria