Import and Export PostgreSQL databases

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