Import and export of Postgresql databases

Command operation: data export: pg_dump-Upostgres (User Name) (-t table name) Database Name (lack of time and the same user name) c: fulldb. SQL data import: psql-Upostgres (User Name) Database Name (lack of time and the same user name) C: fulldb. sqlpgAdmin operation: export data: Right-click the database name -- backup -- OK to save data

Command operation: export data: pg_dump-U postgres (User Name) (-t table name) Database Name (lack of time and the same user name) c: \ fulldb. SQL data import: psql-U postgres (User Name) Database Name (lack of time and the same user name) C: \ fulldb. SQL pgAdmin operation: export data: Right-click the database name -- backup -- OK to save data

Command operation:
Export data: pg_dump-U postgres (User Name) (-t table name) Database Name (lack of time saving and the same user name)> c: \ fulldb. SQL
Data Import: psql-U postgres (User Name) Database Name (lack of time and the same user name) <C: \ fulldb. SQL
PgAdmin operation:
Export data: Right-click the database name --> backup --> OK to save the data to the. backup file.

Import data: Right-click the database name --> restore --> note that the path to the. backup file cannot contain spaces --> OK

Bytes ------------------------------------------------------------------------------------------------------------------------

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