PostgreSQL学习实践：逻辑备份工具pg_dump与pg_dumpall

软件信息：

逻辑备份：

包括：pg_dump,pg_dumpall等。
pg_dump优点：
即使数据库正在被并发使用，它也能创建一致的备份。pg_dump不阻塞其他用户访问数据库。
可以支持跨版本平台数据导入。
pg_dump缺点：
只能备份单个数据库。
同oracle的逻辑备份相似，无法恢复到故障发生的时刻。

pg_dumpall优点：
可以备份所有数据库，并且备份角色，表空间，数据库用户和组以及适用于整个数据库的访问权限。
pg_dumpall缺点：
pg_dumpall需要多次连接到postgresql服务器，每个数据库一次。如果你使用口令认证，可能每次都会要求口令。这种情况下使用一个~/.pgpass会比较方便。

pg_dump恢复实验：

一般情况下使用pg_dump —help语句查看帮助，普通文件可使用psql恢复，二进制文件使用pg_restore恢复。

数据准备：

数据库：wydb01
用户名：wy01
表名：wytb01

实验一：

--备份数据
[postgres@wypg15 backup]$ pg_dump -h 192.168.70.15 -U wy01  wydb01>wybak1.sql
Password:
[postgres@wypg15 backup]$ ll
total 4
-rw-rw-r--. 1 postgres postgres 1398 Jan  4 20:05 wybak1.sql

--删除数据库
[postgres@wypg15 backup]$ psql
psql (15.3)
Type "help" for help.
postgres=# drop database wydb01;
DROP DATABASE

--创建数据库
postgres=# create database wydb01 with owner=wy01;
CREATE DATABASE

--恢复数据库
postgres=# \q
[postgres@wypg15 backup]$ psql -h 192.168.70.15 -U wy01 wydb01<wybak1.sql
Password for user wy01:
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 3
ALTER TABLE
CREATE PUBLICATION
ALTER PUBLICATION
ALTER PUBLICATION

--查看数据库恢复情况
[postgres@wypg15 backup]$ psql -h 192.168.70.15 -U wy01 wydb01 -c "select * from wytb01;"
Password for user wy01:
 id | name
----+------
  1 | 小李
  2 | 小张
  4 | 老徐
(3 rows)

实验二：

--二进制格式备份文件：-F c,可使用j参数指定同时几个进程同时执行
[postgres@wypg15 backup]$ pg_dump -F c -f wybak2.dmp -C -E UTF8 -h 192.168.70.15 -U wy01 wydb01
Password:
[postgres@wypg15 backup]$ ll
total 8
-rw-rw-r--. 1 postgres postgres 1398 Jan  4 20:05 wybak1.sql
-rw-rw-r--. 1 postgres postgres 2011 Jan  4 20:10 wybak2.dmp

#查看二进制文件中的内容
[postgres@wypg15 backup]$ pg_restore -l wybak2.dmp
;
; Archive created at 2024-01-04 20:10:08 CST
;     dbname: wydb01
;     TOC Entries: 9
;     Compression: -1
;     Dump Version: 1.14-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 15.3
;     Dumped by pg_dump version: 15.3
;
;
; Selected TOC Entries:
;
214; 1259 16433 TABLE public wytb01 wy01
3456; 0 16433 TABLE DATA public wytb01 wy01
3311; 2606 16437 CONSTRAINT public wytb01 wytb01_pkey wy01
3454; 6104 16438 PUBLICATION - pub01 wy01
3455; 6106 16439 PUBLICATION TABLE public pub01 wytb01 wy01

--删除数据库
[postgres@wypg15 backup]$ psql
psql (15.3)
Type "help" for help.
postgres=# drop database wydb01;
DROP DATABASE

--创建数据库
postgres=# create database wydb01 with owner=wy01;
CREATE DATABASE

--恢复数据库
postgres=# \q
[postgres@wypg15 backup]$ pg_restore -h 192.168.70.15 -U wy01 -d wydb01 wybak2.dmp
Password:

--查看数据库恢复情况
[postgres@wypg15 backup]$ psql -h 192.168.70.15 -U wy01 wydb01 -c"select * from wytb01;"
Password for user wy01:
 id | name
----+------
  1 | 小李
  2 | 小张
  4 | 老徐
(3 rows)

pg_dumpall恢复实验：

--修改密码配置文件
cd
vi ~/.pgpass
192.168.70.15:6543:*:postgres:postgres
chmod 600 ~/.pgpass

--备份数据库集簇
[postgres@wypg15 backup]$ pg_dumpall -h 192.168.70.15 -U postgres -f wybak_all.sql
[postgres@wypg15 backup]$ ll
total 20
-rw-rw-r--. 1 postgres postgres 1398 Jan  4 20:05 wybak1.sql
-rw-rw-r--. 1 postgres postgres 2011 Jan  4 20:10 wybak2.dmp
-rw-rw-r--. 1 postgres postgres 8333 Jan  4 20:19 wybak_all.sql

--删除数据库
[postgres@wypg15 backup]$ psql
psql (15.3)
Type "help" for help.
postgres=# drop database wydb01;
DROP DATABASE

--恢复数据库，不需要创建数据库(已存在对象的创建可忽略报错)
[postgres@wypg15 backup]$ psql -h 192.168.70.15 -U postgres<wybak_all.sql
SET
SET
SET
ERROR:  role "postgres" already exists
ALTER ROLE
ERROR:  role "repuser" already exists
ALTER ROLE
ERROR:  role "wy01" already exists
ALTER ROLE
ERROR:  role "wy03" already exists
ALTER ROLE
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
You are now connected to database "postgres" as user "postgres".
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
SET
ERROR:  relation "wytb01" already exists
ALTER TABLE
ERROR:  duplicate key value violates unique constraint "wytb01_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY wytb01, line 1
ERROR:  multiple primary keys for table "wytb01" are not allowed
GRANT
GRANT
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "wydb01" as user "postgres".
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 3
ALTER TABLE
CREATE PUBLICATION
ALTER PUBLICATION
ALTER PUBLICATION
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
ERROR:  database "wydb03" already exists
ALTER DATABASE
You are now connected to database "wydb03" as user "postgres".
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
SET
ERROR:  relation "wytb03" already exists
ALTER TABLE
ERROR:  relation "wytb04" already exists
ALTER TABLE
ERROR:  relation "wytb05" already exists
ALTER TABLE
ERROR:  relation "wytb06" already exists
ALTER TABLE
ALTER TABLE
ERROR:  duplicate key value violates unique constraint "wytb03_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY wytb03, line 1
ERROR:  duplicate key value violates unique constraint "wytb04_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY wytb04, line 1
COPY 0
COPY 1
ERROR:  multiple primary keys for table "wytb03" are not allowed
ERROR:  multiple primary keys for table "wytb04" are not allowed
ERROR:  multiple primary keys for table "wytb05" are not allowed
ERROR:  publication "pub03" already exists
ALTER PUBLICATION


--查看数据库恢复情况
[postgres@wypg15 backup]$ psql -h 192.168.70.15 -U wy01 wydb01 -c"select * from wytb01;"
 id | name
----+------
  1 | 小李
  2 | 小张
  4 | 老徐
(3 rows)