Import and Export PostgreSQL databases

Source: Internet
Author: User
Tags psql

Brief description of PostgreSQL database operations

PostgreSQL database version

Psql -- version
Psql (PostgreSQL) 9.1.3

I. Database Backup

1. Backup database structure

Su-postgres
Pg_dump-Fc-s-f testdbschema. SQL testdb

2. Back up database data

Su-postgres
Pg_dump-Fc-a-f testdbdata. SQL testdb

3. Back up the database structure and data

Su-postgres
Pg_dump-Fc-f testdbschemadata. SQL testdb

4. Specify the table structure in the backup database

Pg_dump-Fc-s-t citycode-f citycode_schema. SQL testdb

5. Backup the specified table data in the database

Pg_dump-Fc-a-t citycode-f citycode_data. SQL testdb

. 6. Specify the table (structure and data) in the backup database)

Pg_dump-Fc-t citycode-f citycode_schemadata. SQL testdb

Ii. delete a database

Su-postgres

Dropdb testdb

Iii. Restore database

1. Create a new database testdb

Su-postgres

Createdb testdb;


2. Restore the data structure (only schema)

Su-postgres

Pg_restore-s-d testdb testdbschema. SQL
 

3. Restore database data (only data)

Su-postgres

Pg_restore-a-d testdb testdbdata. SQL

4. Restore the database structure and data (schema and data)

Su-postgres

Pg_restore-d testdb testdbschemadata. SQL

5. Specify table data recovery

1) delete a table

Psql testdb

Drop table citycode;

2) restore the table structure

Pg_restore-s-t citycode-d testdb citycode_schema. SQL

3) Restore table data

Pg_restore-a-t citycode-d testdb citycode_data. SQL

4) restore a table (structure and data)

Pg_restore-t citycode-d testdb citycode_schemadata. SQL

The above Backup recovery operations can be used for static (no data growth) databases.

Important: pg_restore can be used to restore data files backed up by the pg_dump command in the format of (Fc \ Ft. If the parameter declaration is not provided when the pg_dump BACKUP command is executed, the error message "pg_restore: [archiver] input file does not appear to be a valid archive" may appear during pg_restore recovery ".

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.