Tutorial on postgresql import and export pg_dump

Export

Parameter introduction

You can use pg_dump, followed by the database to be exported

-U indicates the user
-F specifies the file to import.
-T specifies which table to export only
-H from which server to export
-- Password indicates that a password is required.
-S: this is added when only the table structure is exported.

Example

Export only the table structure

The-s parameter is used to control whether only the table structure is exported.

Pg_dump-s-h 135.33.2.36 monitor-U monitor -- password-f monitor_struct.dump

Remote export

Pg_dump wuguan-U wuguan-f wuguan. SQL-h 42.96.204.146 -- password

Export locally

H is not required for local export.

Pg_dump-s hold-U hold -- password-f hold. dump

Export on the specified port

Add p to the specified port.

Pg_dump-s-h highwe.net-p 5433 hold-U hold -- password-f hold_c.dump

Export a specified table

Find the tables to export

Select '-t' | tablename from pg_tables where tableowner = 'monitor' and tablename not like 'log %' and tablename not like 'his %'

Add t to export

Pg_dump monitor-U monitor-t upload-t tuxedo_mon-t vc_file-t bsn_tmp-t host_info-t webservice-t webservice_detail_mon-t sys_user-t sys-t client-t bss_org-t vc_detail-t user_info-t sys_log-t response-t process_index-t vc_feecard-t client_bak-t response-t process_mon-t process-t all_process_mon-t response-t threshold -t packet-t warning_relation-t packet-t vc_monitor-t packet-t base_mon-t packet-t all_cpu_mon-t cpu_mon-t mem_mon-t all_mem_mon-t sr_net_mon-t packet- t all_disk_mon-t disk_mon-t all_oracle_mon-t oracle_mon-t blocks-t tablespace_mon-t tbs_mon-t blocks-t pid_mon-t all_warning-t all_file_mon-t warning-t file_mon-t removed_warning-t all_user_mon-t user_mon-t all_net_mon-t net_mon-t all_oracle_user_mon-t oracle_user_mon-f monitor. SQL .20130604

Import

You can use psql without specifying a password on the local machine.

-D specifies the database
-U indicates the user
-F: specifies the exported file.
-H specifies the server
-- Password indicates that a password is required.
Local Machine

Psql-d yemai-U yemai-f yemai. SQL
Psql-d hold-U hold-f. SQL

Remote

Psql-h 135.33.2.36-d monitor-U monitor -- password-f tmp. dump

You can also use

# Psql datebasename <data. SQL

Remote

Pg_dump-h 135.33.2.36 monitor-U monitor-p 5432 -- password-f monitor. SQL
Pg_dump-h djoin.net redmine-U redmine-p 5432 -- password-f redmine. SQL
Pg_dump-h portal-bigzhu.dotcloud.com template1-U root-p 14458 -- password-f database. SQL

Pg_dump-h 135.32.9.99 itsm-U postgres-p 5432 -- password-f itsm. SQL

Pg_dump-h djoin.net monitor-U monitor-f monitor. dump

Pg_dump-t base_mon-h djoin.net monitor-U monitor -- password-f tmp. dump
Pg_dump-t base_mon-h 135.33.2.36 monitor-U monitor -- password-f tmp. dump

Pg_dump-h 135.32.9.98-T 'all _ app_mon * '-t' removed _ warning'-t' * bak * '-t' his *'- T 'log * 'monitor-U monitor-F c-B-v-f ". /monitor_db_20140411.bak"

Import database

Psql-h 135.33.2.36-d monitor-U monitor-f tmp. dump

Psql-d monitor-f monitor. SQL
Psql portal-U bigzhu -- password-f portal. SQL
Psql-d bigzhu-f bigzhu. SQL
Psql-d monitor-f monitor. SQL

Psql-d monitor-h djoin.net-U monitor-f client. SQL
Psql-d monitor-h djoin.net-U monitor-f sys
You can also use # psql datebasename <data. SQL

Binary

Import

Pg_restore-U monitor-W-d monitor-v "./monitor_db_20140325.bak"
Nohup pg_dump monitor-h 135.33.2.36-p 5432-U monitor-F c-B-v-f "./monitor_db_20130923.bak "&
Nohup pg_dump monitor-U monitor-F c-B-v-f "./monitor_db_20131041.bak "&
No dump some table

Nohup pg_dump-T 'his * 'monitor-h 135.33.2.36-p 5432-U monitor-F c-B-v-f "./monitor_db_20130923.bak "&
Pg_dump-T 'his * '-T 'log *'-t' vc _ detail 'monitor-U monitor-F c-B-v-f "./monitor_db_20131209.bak"
Pg_dump-T 'his * '-T 'log *'-t' vc _ detail 'monitor-U monitor-F c-B-v-f "./monitor_db_20140311.bak"
Pg_dump-T 'his * '-T 'log *'-t' vc _ detail 'monitor-U monitor-F c-B-v-f "./monitor_db_20140325.bak"
Pg_dump-T 'his * '-T 'log *'-t' vc _ detail 'monitor-U monitor-F c-B-v-f "./monitor_db_20140519.bak"