postgres 数据库备份

导出数据

pg_dump

将制定查询,制定数据库导出成文件,下操作将制定的数据库test 导成文件test.sql

pg_dump -d test -U postgres -f test.sql

Pg_dumpall

将所有数据库都导成文件,下面指令将数据库导为testall.sql

pg_dumpall -U postgres -f testall.sql 

pg_dump 备份数据库中的某张表，用-t 指定

$ pg_dump -t my_table -d mydatabase --inserts -f /backup/my_table_backup.sql 

以上两种方式是导出成copy 个是,这种格式备份数据,恢复时可能会存在一定问题,可以使用 --inserts 将配置备份成sql 样式,这样会更加稳定,上面的命令可修改成如下

pg_dump -d test -U postgres -f test.sql
pg_dumpall -U postgres -f testall.sql  --inserts

导入数据

导入数据就比较简单

psql -U postgres -f test.sql 

示例

To dump a database called mydb into an SQL-script file:

$ pg_dump mydb > db.sql

To reload such a script into a (freshly created) database named newdb:

$ psql -d newdb -f db.sql

To dump a database into a custom-format archive file:

$ pg_dump -Fc mydb > db.dump

To dump a database into a directory-format archive:

$ pg_dump -Fd mydb -f dumpdir

To dump a database into a directory-format archive in parallel with 5 worker jobs:

$ pg_dump -Fd mydb -j 5 -f dumpdir

To reload an archive file into a (freshly created) database named newdb:

$ pg_restore -d newdb db.dump

To reload an archive file into the same database it was dumped from, discarding the current contents of that database:

$ pg_restore -d postgres --clean --create db.dump

To dump a single table named mytab:

$ pg_dump -t mytab mydb > db.sql

To dump all tables whose names start with emp in the detroit schema, except for the table named employee_log:

$ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql

To dump all schemas whose names start with east or west and end in gsm, excluding any schemas whose names contain the word test:

$ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql

The same, using regular expression notation to consolidate the switches:

$ pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql

To dump all database objects except for tables whose names begin with ts_:

$ pg_dump -T 'ts_*' mydb > db.sql

To specify an upper-case or mixed-case name in -t and related switches, you need to double-quote the name; else it will be folded to lower case (see Patterns below). But double quotes are special to the shell, so in turn they must be quoted. Thus, to dump a single table with a mixed-case name, you need something like

$ pg_dump -t "\"MixedCaseName\"" mydb > mytab.sql