1、常用命令

# 备份文书表数据
pg_dump -U postgres -d merger -t doc_detail | gzip > /data/PostgresqlBackup/20220314_doc_detail.gz

# 备份问题表数据
pg_dump -U postgres -d merger -t db_question | gzip > /data/PostgresqlBackup/20220314_db_question.gz

# 备份非文书表、非问题表的数据
pg_dump -U postgres -d merger -T db_question -T doc_detail | gzip > /data/PostgresqlBackup/20220314_other.gz

# 导入数据
gunzip -c ./20220314_other.gz | psql -U postgres db_demo

注：压缩备份的差别很大。不压缩备份出来后有1.1Gb，压缩后大约100Mb

PostgreSQL支持以下两种数据恢复方法：

1、使用psql恢复pg_dump或pg_dumpall工具生成的SQL文本格式的数据备份。

2、使用pg_restore工具来恢复由pg_dump工具生成的自定义压缩格式、TAR包格式或者目录格式备份。

---

---

二、命令详解

• 基本命令

pg_dump [OPTION]... [DBNAME]

　　注：数据库名放最后，不指定默认是系统变量PGDATABASE指定的数据库。

•  详解　　

General options:(一般选项)
  -f, --file=FILENAME          output file or directory name导出后保存的文件名
  -F, --format=c|d|t|p            output file format (custom, directory, tar,导出文件的格式  plain text (default))
  -j, --jobs=NUM                  use this many parallel jobs to dump并行数   多线程还原
  -v, --verbose                　  verbose mode 详细模式 
  -V, --version                　  output version information, then exit输出版本信息, 然后退出
  -Z, --compress=0-9          compression level for compressed formats被压缩格式的压缩级别
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock在等待表锁超时后操作失败
  -?, --help                   show this help, then exit显示此帮助信息, 然后退出

Options controlling the output content:(控制输出的选项)
  -a, --data-only              dump only the data, not the schema只导出数据，不包括模式
  -b, --blobs                  include large objects in dump在转储中包括大对象
  -c, --clean                  clean (drop) database objects before recreating在重新创建之前，先清除（删除）数据库对象
  -C, --create                 include commands to create database in dump在转储中包括命令,以便创建数据库（包括建库语句，无需在导入之前先建数据库）
  -E, --encoding=ENCODING      dump the data in encoding ENCODING转储以ENCODING形式编码的数据
  -n, --schema=SCHEMA          dump the named schema(s) only只转储指定名称的模式
  -N, --exclude-schema=SCHEMA  do NOT dump the named schema(s)不转储已命名的模式
  -o, --oids                   include OIDs in dump在转储中包括 OID
  -O, --no-owner               skip restoration of object ownership in在明文格式中, 忽略恢复对象所属者  plain-text format 
  -s, --schema-only            dump only the schema, no data只转储模式, 不包括数据(不导出数据)
  -S, --superuser=NAME         superuser user name to use in plain-text format在转储中, 指定的超级用户名
  -t, --table=TABLE            dump the named table(s) only只转储指定名称的表
  -T, --exclude-table=TABLE    do NOT dump the named table(s)只转储指定名称的表
  -x, --no-privileges          do not dump privileges (grant/revoke)不要转储权限 (grant/revoke)
  --binary-upgrade             for use by upgrade utilities only只能由升级工具使用
  --column-inserts             dump data as INSERT commands with column names以带有列名的INSERT命令形式转储数据
  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting取消美元 (符号) 引号, 使用 SQL 标准引号
  --disable-triggers           disable triggers during data-only restore在只恢复数据的过程中禁用触发器
  --exclude-table-data=TABLE   do NOT dump data for the named table(s)以INSERT命令，而不是COPY命令的形式转储数据
  --inserts                    dump data as INSERT commands, rather than COPY
  --no-security-labels         do not dump security label assignments
  --no-synchronized-snapshots  do not use synchronized snapshots in parallel jobs
  --no-tablespaces             do not dump tablespace assignments不转储表空间分配信息
  --no-unlogged-table-data     do not dump unlogged table data
  --quote-all-identifiers      quote all identifiers, even if not key words
  --section=SECTION            dump named section (pre-data, data, or post-data)
  --serializable-deferrable    wait until the dump can run without anomalies
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:(控制连接的选项)
  -d, --dbname=DBNAME      database to dump 数据库名
  -h, --host=HOSTNAME      database server host or socket directory数据库服务器的主机名或套接字目录
  -p, --port=PORT          database server port number数据库服务器的端口号
  -U, --username=NAME      connect as specified database user以指定的数据库用户联接
  -w, --no-password        never prompt for password永远不提示输入口令 
  -W, --password           force password prompt (should happen automatically)强制口令提示 (自动) 
  --role=ROLENAME          do SET ROLE before dump

一： 纯文件格式的脚本： 
示例：
1. 只导出postgres数据库的数据，不包括模式 -s

pg_dump -U postgres -f /postgres.sql -s postgres(数据库名)

2. 导出postgres数据库（包括数据）

 pg_dump -U postgres -f /postgres.sql  postgres(数据库名)

3. 导出postgres数据库中表test01的数据

create database "test01" with owner="postgres" encoding='utf-8';(单引号，双引号不能错) pg_dump -U postgres -f /postgres.sql -t test01 postgres(数据库名)

4. 导出postgres数据库中表test01的数据,以insert语句的形式

pg_dump -U postgres -f /postgres.sql -t test01 --column-inserts postgres(数据库名)
 

5. 恢复数据到bk01数据库

psql -U postgres -f /postgres.sql bk01

二、 使用归档文件格式：

pg_restore
使用pg_restore纯文本恢复纯文本格式的脚本，无法恢复
[root@localhost postgres-9.3.5]# pg_restore -U postgres -d bk01  /mnt/hgfs/window\&ubuntu\ shared\ folder/vendemo.sql 
pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

pg_restore和归档文件格式一起使用重建数据库。

1. 先备份： 
   pg_dump -U postgres -F t -f /vendemo.tar vendemo  备份下来有800多k
 . 恢复：
   pg_restore -U postgres -d bk01 /vendemo.tar 
2. 先备份： 
   pg_dump -U postgres -F c -f /vendemo.tar vendemo  备份下来有300多k
 . 恢复：
   pg_restore -U postgres -d bk01 /vendemo.tar 

三、 压缩备份与恢复：
处理大数据库：
1. 使用压缩的转储. 使用你熟悉的压缩程序，比如说 gzip。
 . 先备份：
   pg_dump -U postgres vendemo | gzip > /vendemo.gz 备份下来只有30多k
 . 恢复：
   gunzip -c /vendemo.gz | psql -U postgres bk02
 或者
   cat /vendemo.gz | gunzip | psql -U postgres bk02
2. 使用 split。. split 命令允许你 你用下面的方法把输出分解成操作系统可以接受的大小。 比如，让每个块大小为 1 兆字节： 
 . 先备份：
   pg_dump -U postgres -d vendemo | split -b 100k - /vend/vend
   导出来的样子是   vendaa 100k
   vendab 100k
   vendac 100k
   vendad 16k
 . 恢复：
  cat /vend/vend* | psql -U postgres bk02

---

---

1、Postgresql数据库备份的3种方式

PostgreSQL提供了三种备份和恢复的方式:SQL dump、文件系统复制和联机热备份。 每一种备份方式都有自己的优点和缺点，下面将详细介绍。

9.1 SQL Dump

这种备份方式产生一个文本文件，里面包含创建各种数据库对象的SQL语句和每个表中的数据。另外，表上创建的索引中的数据不会被导出，只会导出索引的定义信息。在恢复数据库的时候，索引会被重建。可以使用数据库提供的工具pg_dumpall和pg_dump来进行备份。pg_dumpall会备份一个数据库集群中的所有信息和数据。pg_dump只备份数据库集群中的某个数据库的数据，它不会导出角色和表空间相关的信息，因为这些信息是整个数据库集群共用的，不属于某个单独的数据库。pg_dump的基本用法如下：

pg_dump 数据库名 > 备份文件名

pg_dump将结果写到标准输出中，可以用操作系统的重定向命令将结果写到文件中。

可以在运行数据库的机器上执行pg_dump命令，也可以在其它的机器上执行pg_dump命令。 可以使用选项-h和-p来指定运行数据库的主机名和数据库监听的端口。例如：

pg_dump -h db_server1 -p 5432 product > backup_file

该命令连接机器db_server1上在端口5432处监听的数据库，将数据库product的数据备份到文件backup_file中。如果pg_dump命令没有使用-h和-p选项，将使用环境变量PGHOST的值作为机器名，使用环境变量PGPORT的值作为数据库的端口。如果用户没有定义环境变量PGHOST，默认使用本机名作为运行数据库的机器名。

默认的情况下，pg_dump使用当前的操作系统用户作为连接数据库时使用的用户。可以使用选项-U或者设置环境变量PGUSER来指定连接数据库时使用的用户名。例如：

pg_dump -U liming -h db_server1 -p 5432 product > backup_file

该命令使用用户liming连接机器db_server1上在端口5432处监听的数据库，将数据库product的数据备份到文件backup_file中。

一般情况下，应该使用超级用户连接数据库进行备份操作，因为超级用户可以访问数据库中的任何信息。使用普通数据用户连接数据库，有些表可能无法访问。

运行pg_dump时，数据可以正常地执行其它操作。但ALTER TABLE这类修改数据库对象定义的操作会受到影响，可能会长时间处于等待状态而无法执行，所以在运行pg_dump命令时，不要在数据库中运行修改数据库对象定义的操作。

另外要注意的是，如果数据库中有些表使用OID来实现外键约束，应当在备份数据库时同时备份表的OID信息，使用pg_dump时加上选项–o即可达到这个目的。

9.1.1 恢复数据库

pg_dump创建的备份文件可以被工具psql识别。因此可以使用psql来读取pg_dump创建的备份文件，实现恢复数据库的功能。例如：

psql dbname < backup_file

psql后面的参数dbname指定的数据库必须已经存在。如果不存在，用户应当先创建dbname指定的数据库，然后再执行恢复数据的命令。psql也支持和pg_dump一样的命令行选项，如-h和-p等。创建数据库dbname时，必须使用template0作为模板数据库，可以使用工具createdb创建数据库，也可以在psql中执行SQL命令create database来创建数据库。下面是两个实例：

（1）createdb -T template0 dbname

（2）create database dbname template=template0

另外，在执行恢复数据的操作以前，那些拥有数据库备份中的数据库对象或则对这些对象有访问权限的数据库的用户必须已经在数据库中存在，否则，恢复数据库以后，数据库备份中的数据库对象的所有者会发生改变。

默认的情况下，psql命令会一直执行下去直到结束，即使中间遇到SQL错误，恢复操作也会继续执行。如果想让psql在执行过程中遇到错误以后，停止恢复操作，可以在执行恢复操作以前，在psql中运行下面的命令：

\set ON_ERROR_STOP

如果psql在执行过程中遇到错误，则只有一部分数据被正确地恢复，这时被恢复数据库中的数据是不完整的。psql提供了另外一种恢复模式，在这种模式下，一旦恢复操作执行过程中遇到任何错误，已经恢复的数据都会自动从数据库中被删除。可以使用psql的命令行选项-l或–single-transaction来打开这种模式。

在恢复操作结束以后，应该使用ANALYZE命令来重新收集查询优化器统计数据。

9.1.2 使用pg_dumpall

pg_dump只备份数据库集群中的某个数据库的数据，它不会导出角色和表空间相关的信息。pg_dumpall则可以导出整个数据库集群中所有的数据库中的数据，同时也会导出角色、用户和表空间的定义信息。使用pg_dumpall的一般命令格式如下：

pg_dumpall > backup_file

pg_dumpall也支持和pg_dump一样的命令行选项，如-h和-p等。同样可以使用psql来从pg_dumpall创建的备份文件中恢复数据库。应该使用数据库超级用户来进行恢复数据库的操作。命令格式如下：

psql -f backup_file postgres

pg_dumpall在执行的过程中，用postgres作为用户名来连接数据库。系统自动创建的数据库postgres中的内容也会被导出来，数据库template0和template1中的内容不会被导出来。

9.1.3 大型数据库的备份和恢复

如果数据库的规模比较大，产生的备份文件的大小超级了操作系统能够允许的单个文件的大小的最大值，可以使用压缩和将备份文件分成对个部分这两个方法来解决这个问题。

（1）采用压缩的方法，可以采用操作系统提供的任何一种压缩工具来实现，常用的是gzip。例如：

pg_dump dbname | gzip > filename.gz

恢复时，使用下面的命令：

gunzip -c filename.gz | psql dbname

也可以使用下面的命令来恢复数据库：

cat filename.gz | gunzip | psql dbname

（2）将备份文件分成多个部分。使用操作系统的工具split来实现。例如:

pg_dump dbname | split -b 1m - filename

在这个例子中，数据库备份被分成多个大小为1MB的文件。

使用下面的命令进行恢复操作：

cat filename* | psql dbname

（3）使用pg_dump自带的压缩功能。这种方法产生的备份文件也是被压缩的，同第一种方法相比，它有一个优点，就是可以只恢复备份文件中的某个表的数据。这种方法的命令格式如下，就是增加了选项-Fc：

pg_dump -Fc dbname > filename

不能使用psql命令恢复用这种方法备份的数据，必须使用pg_restore来进行恢复操作。命令格式如下：

pg_restore -d dbname filename

对于非常大的数据库，可以将压缩与分割的方法同时使用（同时使用第一种和第二种方法，或者同时使用第二种和第三种方法）。

9.2文件系统复制

文件系统复制这种方法是直接复制所有的数据库文件，存放到其它的存储介质上。这是最简单的备份数据库的方法。可以使用操作系统的命令来完成备份，例如：

tar -cf backup.tar /usr/local/pgsql/data

复制数据文件以前，必须关闭数据库。这种备份方法产生的备份文件比较大，因为索引数据也会被备份。恢复数据库时只要把备份文件复制到存放数据文件的目录中即可。

9.3 联机热备份与归档恢复

9.3.1 联机热备份

进行联机热备份时，不用关闭数据库。数据库可以正常地执行其它操作。如果要使联机热备份，数据库必须运行在归档模式下，将参数数据库archive_mode设为on，然后再将参数archive_dir设成一个启动数据库的操作系统用户有读写权限的目录，数据库就会运行在归档模式。要使这两个参数生效，必须重新启动数据库。

进行联机热备份的步骤如下：

（1）检查数据库是否运行在归档模式下。

（2）用超级用户连接数据库（推荐使用psql），然后执行下面的命令：
SELECT pg_start_backup(‘label’);

label是一个字符串，用来确定创建的备份，可以选取一个有明显的含义的名字作为label。

pg_start_backup命令可能会执行比较长的时间才会结束，因为数据库会自动开始一个检查点操作。

（3）使用操作系统命令(如cp)，将所有的数据库文件复制到其它的存储介质上。

（4）执行下面的命令结束备份操作：

SELECT pg_stop_backup();

备份操作结束以后，会在pg_xlog子目录下产生一个备份描述文件，该文件以“.backup”结尾，例如000000010000000000000000.004535C0.backup。注意数据库归档进程会自动将备份操作产生的备份描述文件从pg_xlog子目录复制到存放归档事务日志的目录中（参数archive_dir指定的目录），如果在pg_xlog目录中找不到备份描述文件，应该在存放归档事务日志的目录中去寻找它。恢复数据库的时候需要使用备份描述文件中的信息。备份描述文件中存放有下列信息：

（1）开始事务日志文件名。

（2）结束事务日志文件名。

（3）检查点位置。

（4）备份操作开始的时间。

（5）备份操作结束的时间。

（6）备份的名字（就是pg_start_backup命令中指定的名字）。

下面是一个备份描述文件的实例：

START WAL LOCATION: 0/4535C0 (file 000000010000000000000000)

STOP WAL LOCATION: 0/453A98 (file 000000010000000000000000)

CHECKPOINT LOCATION: 0/4535C0

START TIME: 2009-03-28 23:02:34 CST

LABEL: b1

STOP TIME: 2009-03-28 23:04:05 CST

从该文件中可以看出备份操作开始的时间是2009-03-28 23:02:34，结束的时间是2009-03-28 23:04:05，备份的名字是b1，开始事务日志的名字是 000000010000000000000000，结束事务日志的名字也是 000000010000000000000000，检查点的位置是0/4535C0。

从开始事务日志文件到结束事务日志文件之间的所有事务日志文件（包括这两个事务日志文件）必须被保存好，不能丢失，否则创建的数据库备份将是无效的，不能将数据库恢复到一个一致的状态。

备份操作在执行的过程中会在数据文件目下产生一个名为backup_label的文件，该文件叫做备份标号文件。备份标号文件在备份操作结束以后会被系统自动删除。在执行上面的第三步操作的过程中，必须同时复制备份标号文件，因为恢复数据库的时候需要使用备份标号文件中的信息。

9.3.2 归档恢复

进行归档恢复以前，应该准备好一个名为recovery.conf的文件，该文件中包含一些恢复操作的配置参数，这些参数决定恢复操作如何进行。下面详细介绍这些参数：

（1）archive_log_dir

该参数指定存放归事务日志的目录，所有需要的归档事务日志都应该存放在该目录中，系统在进行恢复操作时会自动从该目录中读取需要的事务日志文件。

（2）recovery_target_time

该参数指定一个时间，恢复操作进行到该时间时会自动停止。该参数用来实现时间点恢复（point-In-Time Recovery）。recovery_target_time和下面的recovery_target_xid只能指定一个。

（3）recovery_target_xid

该参数指定一个事务id，恢复操作进行到该事务时会自动停止。recovery_target_xid和上面的recovery_target_time只能指定一个。

（4）recovery_target_inclusive

该参数的值是true或false。默认值是true。它影响参数recovery_target_time和recovery_target_xid，如果它的值为true，恢复操作在指定的目标（时间或事务ID）以后停止，如果它的值为false，恢复操作在指定的目标以后停止。

参数archive_log_dir必须出现在recovery.conf的文件中，其它的参数则是可选的，如果recovery_target_xid和recovery_target_time都没有被指定，则默认恢复到最后一个事务日志文件确定的数据库的最近的状态。如果想进行时间点恢复，应该指定参数recovery_target_time。

下面是一个recovery.conf文件的实例，所有的参数的值都必须用两个单引号引起来：

archive_log_dir = ‘/home/yan/archive_log’

recovery_target_time = ‘2004-07-14 22:39:00 EST’

recovery_target_xid = ‘1100842’

recovery_target_inclusive = ‘true’

下面介绍进行归档恢复的具体步骤：

（１）停止数据库服务器。将当前数据库备份到其它目录中。

（２）准备好recovery.conf文件，将所有恢复操作需要的归档事务日志都存放在参数archive_log_dir指定的目录中，备份描述文件也必须被存放在参数archive_log_dir指定的目录中。

（３）将以前创建的数据库备份复制到数据文件目录中（必须与以前的数据文件目录相同）。如果数据库使用了表空间，请验证pg_tblspc子目录下面的每个符号链接是否有效。将准备好的recovery.conf文件存放到数据文件目录中。编辑文件pg_hba.conf，不允许任何用户在恢复的过程中连接数据库。

（４） 确保数据文件目录中存在一个名为backup_label的文件。删除pg_xlog子目录中的所有文件，重新在pg_xlog中创建一个名为archive_status的子目录。

（５）启动数据库，数据库在启动以后将自动进行恢复操作，恢复操作成功完成以后，数据库将自动打开，进入正常的工作状态。恢复操作成功以后，系统会将文件recovery.conf重命名为recovery.done。

（６）检查数据库中的内容是否正确。

归档恢复成功结束以后，数据库会自动打开，进入正常的工作状态，可以开始响应用户的连接请求，应该修改pg_hba.conf文件，允许用户连接数据库。归档恢复成功结束以后，在数据库的运行日志中会有下面的提示信息：

……

日志： 00000： 归档恢复结束。

文件backup_label在恢复操作执行结束以后会被自动重命名为backup_label.old。确定归档恢复成功以后，应该删除backup_label.old，因为它已经没有任何作用。

9.3.3 注意事项

进行归档恢复时，有下面几个注意事项：

（１）哈希索引上面的操作没有被记录到事务日志中，归档恢复完成以后，必须对每个哈希索引执行REINDEX操作。

（２）在创建数据库备份时不要修改模板数据库。

9.3.4 时间线（timeline）

时间线是PostgreSQL独有的概念。它是一个整数值，与归档恢复有关。在用initdb创建一个初始的数据库集群以后，该数据库集群的时间线是１。每进行一次归档恢复，就会产生一个新的时间线，新的时间线的值在上一个时间线的值的基础上加一。每次归档恢复完成以后，都会产生一个时间线历史文件，该文件以“.history”结尾，例如00000002.history。时间线历史文件首先被存放在pg_xlog目录中，数据库归档进程以后会自动将时间线历史文件从pg_xlog子目录复制到存放归档事务日志的目录中（参数archive_dir指定的目录）。

---

---

一、命令操作：
  数据的导出：

$ pg_dump -U postgres(用户名)  (-t 表名)  数据库名(缺省时同用户名)  > 路径/文件名.sql

例：

postgres@debian:~$ pg_dump -U postgres -t system_calls wangye > ./test.sql
postgres@debian:~$ ls
  test.sql

 数据的导入：

备份数据为 ”表备份“的话需要先创建数据库：

（备份数据为整库备份的话无须手动创建数据库）

进入psql  创建数据库： $ create database newdatabase

导入语句：

$ psql -d databaename(数据库名) -U username(用户名) -f < 路径/文件名.sql  

例：

$ psql -d newdatabase -U postgres -f  mydatabase.sql   // sql 文件在当前路径下
INSERT 0 1
INSERT 0 1
INSERT 0 1

SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 521955
ALTER TABLE
......

二、pgAdmin操作：
数据的导出：
    在库名上右击-->backup-->ok，即将数据保存到.backup文件中。

数据的导入：
    在库名上右击-->restore-->注意填写.backup文件的路径不能有空格-->ok