逻辑备份与恢复

逻辑备份的原理是将数据库中的对象和数据转储成对应的SQL命令。通常我们使用pg_dump/pg_restore命令来完成逻辑备份和恢复。

备份

假设我们要备份名为halotest的数据库，我们可以使用如下命令：

$ pg_dump -Fc -v -f halotest.dump halotest
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding the columns and types of table "public.t_a"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row-level security policies
pg_dump: reading publications
pg_dump: reading publication membership
pg_dump: reading subscriptions
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path = public
pg_dump: saving database definition
pg_dump: dumping contents of table "public.t_a"

命令成功结束后，会生成一个halotest.dump的备份文件。

恢复

假设我们需要对halotest进行恢复，我们可以使用如下命令：

$ pg_restore -v -C -d halo0root halotest.dump
pg_restore: connecting to database for restore
pg_restore: creating DATABASE "halotest"
pg_restore: connecting to new database "halotest"
pg_restore: creating TABLE "public.t_a"
pg_restore: processing data for table "public.t_a"

这样，halotest数据库就完成了恢复。