2019独角兽企业重金招聘Python工程师标准>>> 
数据是很宝贵的,要时候谨记备份的重要性. 这里讲一下通过SQL Dump方式来做备份与恢复. pg_dump 导出某一个数据库,通过将数据库中的结构信息及数据通过sql方式输出来备份数据库.它是在执行命令那一刻时数据库一致性状态的保存. 恢复时只许将这输出在目标库上重建就可以了.
#使用pg_dump命令备份
pg_dump 默认输出到控制台,不指定参数默认是导出连接着的数据库.
[postgresfnddb data]$ pg_dump | more---- PostgreSQL database dump--SET statement_timeout 0;SET lock_timeout 0;......---- PostgreSQL database dump complete--
通常的做法是备份到一个文件中.
[postgresfnddb ~]$ pg_dump database1 > db1.dump
可以导出一个schema,当然也可以只导出一个表
[postgresfnddb ~]$ pg_dump database2 -n schema01---- PostgreSQL database dump--SET statement_timeout 0;......ALTER SCHEMA schema01 OWNER TO postgres;---- PostgreSQL database dump complete--[postgresfnddb ~]$ pg_dump -U user2 database2 -t t1 > database2.dump
pg_dump是一个客户端命令行工具,可以通过pg_dump --help查看
#恢复pg_dump出来的文件.
使用psql dbname
dbname是使用template0创建出来的全新数据库,否则有可能已经有对象在其中.
先查看下要备份的数据库中有哪些表,属于哪些用户
[postgresfnddb ~]$ psql database1psql (9.4.1)Type "help" for help.database1# \dtList of relationsSchema | Name | Type | Owner -------------------------------public | t1 | table | user1public | t2 | table | user1public | t3 | table | user2public | tab2 | table | postgrespublic | tab3 | table | postgres(5 rows)
备份数据库database1
[postgresfnddb ~]$ pg_dump database1 > database1.pgdmp
使用template0创建一个全新数据库
[postgresfnddb ~]$ createdb -T template0 db01
进行恢复并检查
[postgresfnddb ~]$ psql db01
#测试一下如果某个用户不存在会如何
删除user2,需要把user2涉及的对象权限删除或者授权给其他用户
database1# drop role user1;ERROR: role "user1" cannot be dropped because some objects depend on itDETAIL: owner of database database12 objects in database db012 objects in database database2database1# reassign owned by user2 to hippo;REASSIGN OWNEDdatabase1# \c database2You are now connected to database "database2" as user "postgres".database2# reassign owned by user2 to hippo;REASSIGN OWNEDdatabase2# \c db01You are now connected to database "db01" as user "postgres".db01# reassign owned by user2 to hippo;REASSIGN OWNEDdb01# revoke create on tablespace ts02 from user2;REVOKEdb01# alter database database1 owner to hippo;ALTER DATABASEdb01# drop role user2;DROP ROLEdb01# \q
尝试恢复
[postgresfnddb ~]$ createdb -T template0 db02[postgresfnddb ~]$ psql db02
[postgresfnddb ~]$ createdb -T template0 db03[postgresfnddb ~]$ psql --set ON_ERROR_STOPon db03
通过指定-1选项,可以将恢复进程放在一个事务中
[postgresfnddb ~]$ hostname -i192.168.10.74[postgresfnddb ~]$ psql -h 192.168.10.72Password: psql (9.4.1, server 9.3.5)Type "help" for help.postgres# create database db01 template template0;CREATE DATABASEpostgres# \q[postgresfnddb ~]$ pg_dump database1 | psql -1 -h 192.168.10.72 db01Password: SET......GRANTERROR: role "r2" does not existERROR: current transaction is aborted, commands ignored until end of transaction blockERROR: current transaction is aborted, commands ignored until end of transaction blockERROR: current transaction is aborted, commands ignored until end of transaction blockERROR: current transaction is aborted, commands ignored until end of transaction block[postgresfnddb ~]$ psql -h 192.168.10.72 db01Password: psql (9.4.1, server 9.3.5)Type "help" for help.db01# \dtNo relations found.
把需要的role创建好再试一次
db01# create role user1;CREATE ROLEdb01# create role user2;CREATE ROLEdb01# create role r1;CREATE ROLEdb01# create role r2;CREATE ROLEdb01# create role jack;CREATE ROLEdb01# \q[postgresfnddb ~]$ pg_dump database1 | psql -1 -h 192.168.10.72 db01Password: SET.......[postgresfnddb ~]$ psql -h 192.168.10.72 db01Password: psql (9.4.1, server 9.3.5)Type "help" for help.db01# \dtList of relationsSchema | Name | Type | Owner -------------------------------public | t3 | table | hippopublic | tab2 | table | postgrespublic | tab3 | table | postgres(3 rows)
#pg_dumpall命令
pg_dump只导出一个数据库,并且不导出role,tablespace等cluster级别的对象. pg_dumpall导出所有包括role,tablespace及所有数据库. pg_dumpall因为要导出role,tablespace信息,所以此命令需要superuser用户
##pg_dumpall备份全库
[postgresfnddb ~]$ pg_dumpall > all.pgdmp
只导出Cluster-wide数据,不包括数据库级别的对象及数据
[postgresfnddb ~]$ pg_dumpall -g > all.pgdmp
可以再使用pg_dump来导出单独一个数据库来使用
##恢复pg_dumpall备份的文件
恢复使用psql -f infile来进行,最好是使用一个全新的cluster来进行恢复
[postgresfnddb ~]$ pg_dumpall | psql -h 192.168.10.72 -f -Password: SET......psql::34: ERROR: directory "/var/lib/pgsql/tsdata" does not existpsql::35: ERROR: directory "/var/lib/pgsql/tsdata02" does not existpsql::36: ERROR: tablespace "ts02" does not existpsql::37: ERROR: tablespace "ts02" does not existpsql::38: ERROR: tablespace "ts02" does not existpsql::45: ERROR: tablespace "ts01" does not existpsql::46: ERROR: database "database1" does not existpsql::47: ERROR: database "database1" does not existpsql::48: ERROR: database "database1" does not existpsql::49: ERROR: database "database1" does not existpsql::50: ERROR: tablespace "ts01" does not existCREATE DATABASECREATE DATABASECREATE DATABASEREVOKEREVOKEGRANTGRANTpsql::60: \connect: FATAL: database "database1" does not exist
在目标库上的表空间目录需要事先创建好 然后再重建...
###注意点
pg_dump,pg_dumpall是备份恢复某一个时刻时,数据库的一致性状态.. 如果需要将数据库从崩溃火丢失数据中,恢复到运行时最新的状态,则需要使用在线备份恢复技术.
//END
转:https://my.oschina.net/hippora/blog/377253