PostgreSQL数据导出与恢复

基础操作之___数据导出与恢复

1、导出数据表（pg_dump）

导出表为txt文本

$ pg_dump -t httab -f httab.txt htdb
$ ll
-rw-rw-r--. 1 postgres postgres 429564 Aug 24 00:19 httab.txt
1
2
3

导出表为dmp文件

$ pg_dump -t httab -FC -f httab.dmp htdb
$ ll
-rw-rw-r--. 1 postgres postgres 243696 Aug 24 00:22 httab.dmp
1
2
3

导出表定义

$ pg_dump -t httab -s -f httab.metadata htdb
$ ll
-rw-rw-r--. 1 postgres postgres   1501 Aug 24 00:22 httab.metadata
1
2
3

参数说明：

• -t：指定表名
• -f：指定导出文件名
• -Fc：默认导出文件格式为文本，-Fc使用用户自定义格式
• -s：只导出表定义

注意：若想导出索引只能导出索引创建语句，不导出索引数据

2、恢复导出文件到数据库

根据导出格式不同，恢复命令也不相同

（1）恢复dmp格式文件（pg_restore）

删除当前数据库已有表

$ psql -c 'select count(1) from  httab' -Uhtuser htdb
 count 
-------
 10000
(1 row)
1
2
3
4
5

$ psql -c 'drop table httab' -Uhtuser htdb
DROP TABLE
1
2

将dmp格式文件恢复到数据库

$ pg_restore -Uhtuser -d htdb httab.dmp
$
1
2

验证：

$ psql -c 'select count(1) from  httab' -Uhtuser htdb
 count 
-------
 10000
(1 row)
1
2
3
4
5

（2）恢复txt格式文件（psql）

删除当前数据库已有表

$ psql -c 'drop table httab' -Uhtuser htdb
DROP TABLE
1
2

将txt格式文件恢复到数据库

$ psql -Uhtuser htdb < httab.txt
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 10000
 setval 
--------
  10000
(1 row)

ALTER TABLE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

验证：

$ psql -c 'select count(1) from  httab' -Uhtuser htdb
 count 
-------
 10000
(1 row)
1
2
3
4
5

补充说明：

pg_dump 实质是用 copy 命令跑的，只不过普通用户没有权限，可以用\copy

二者区别：

• copy ：使用pg数据库本地的文件
• \copy ：使用客户端本地的文件

3、物理备份及恢复

（1）全库物理备份

$ pg_basebackup -D /pg_bak -Xstream -cfast -P
34368/34368 kB (100%), 1/1 tablespace
1
2

参数说明：

• -D：指定备份文件所在目录，要求为空，否则报错
• -Xstream：使用流模式备份所需的wal日志，可能需适当上调max_wal_senders 参数值
• -cfast：备份前会等待库执行检查点，fast表示立即执行
• -P：备份过程中显示进度

（2）全库物理恢复

1、关闭PG实例

$ pg_ctl -D /pgdata12/ -m smart stop
waiting for server to shut down.... done
server stoppe
1
2
3

2、清空原PGDATA目录

$ rm -rf /pgdata12/*
1

3、还原数据库

$ cp -r /pg_bak/* /pgdata12/
1

4、启动PG实例

$ pg_ctl start -D /pgdata12
waiting for server to start....2022-08-24 00:55:08.824 CST [10446] LOG:  starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2022-08-24 00:55:08.825 CST [10446] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-08-24 00:55:08.825 CST [10446] LOG:  listening on IPv6 address "::", port 5432
2022-08-24 00:55:08.826 CST [10446] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-08-24 00:55:08.842 CST [10446] LOG:  redirecting log output to logging collector process
2022-08-24 00:55:08.842 CST [10446] HINT:  Future log output will appear in directory "log".
 done
server started
1
2
3
4
5
6
7
8
9

5、登录数据库验证

$ psql -Uhtuser htdb
psql (12.3)
Type "help" for help.

htdb=# \c
You are now connected to database "htdb" as user "htuser".
1
2
3
4
5
6

（初次学习、诸多不足、请多指教）