使用pg_dump对RDS for PostgreSQL数据库进行备份

pg_dump 是 PostgreSQL 原生的备份工具。pg_dump 生成的备份文件可以是一个 SQL 脚本文件，也可以是一个归档文件。

• SQL脚本文件是纯文本格式的文件，它包含许多SQL命令，执行这些SQL命令可以重建该数据库并将之恢复到保存成脚本时的状态。
• 归档格式的备份文件必须与pg_restore一起使用来重建数据库，这种格式允许pg_restore选择恢复哪些数据，甚至可以在恢复之前对需要恢复的条目重新排序

预计部署时间：20分钟
级别：初级
相关产品：关系型数据库 PostgreSQL 12 版本
受众： 通用

1. 如果还没有火山引擎账号，点击此链接注册账号

2. 如果您还没有 VPC，请先点击链接创建VPC

3. 关系型数据库 PostgreSQL 12 版本

4. 云服务器ECS：Centos 7

5. 在 ECS 主机上准备 pg_dump 工具

准备相关数据

由于仅做测试使用，数据库中仅有少量数据。

lxb=# create database dumptest;
CREATE DATABASE

lxb=# \c dumptest;
You are now connected to database "dumptest" as user "lxb".

dumptest=# create table dumptable(id int primary key, content char(50));
CREATE TABLE

dumptest=# insert into dumptable values(1,'aa');
INSERT 0 1
dumptest=# insert into dumptable values(2,'bb');
INSERT 0 1

进行单库备份

运行命令如下：

[root@iv-ebgymdaodpe2tgjofpe5 ~]# pg_dump -h postgres76be41028654***.rds-pg.ivolces.com -Ulxb dumptest > /tmp/dumptest.sql
Password: 

备份结束后，备份内容在 dumptest.sql 中。查看文件内容如下(文件中省略部分内容)：

[root@iv-ebgymdaodpe2tgjofpe5 ~]# cat /tmp/dumptest.sql
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.10
-- Dumped by pg_dump version 12.10

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: dumptable; Type: TABLE; Schema: public; Owner: lxb
--

CREATE TABLE public.dumptable (
    id integer NOT NULL,
    content character(50)
);


ALTER TABLE public.dumptable OWNER TO lxb;

--
-- Data for Name: dumptable; Type: TABLE DATA; Schema: public; Owner: lxb
--

COPY public.dumptable (id, content) FROM stdin;
1       aa                                                
2       bb                                                
\.


--
-- Name: dumptable dumptable_pkey; Type: CONSTRAINT; Schema: public; Owner: lxb
--

ALTER TABLE ONLY public.dumptable
    ADD CONSTRAINT dumptable_pkey PRIMARY KEY (id);


--
-- PostgreSQL database dump complete
--

进行单表备份

准备第二表 dumptable2

dumptest=# CREATE TABLE dumptable2 as (select * from dumptable);)
SELECT 2

dumptest-# \d
          List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 public | dumptable  | table | lxb
 public | dumptable2 | table | lxb
(2 rows)

进行单表备份的命令如下，请您参考，其中指定了只需要备份 dumptable 表。

[root@iv-ebgymdaodpe2tgjofpe5 ~]# pg_dump -h postgres76be41028*****.rds-pg.ivolces.com -Ulxb -t dumptable dumptest > /tmp/dumptest1.sql
Password: 

查看备份的 SQL 文件，只备份了 dumptable 表。

[root@iv-ebgymdaodpe2tgjofpe5 ~]# cat /tmp/dumptest1.sql
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.10
-- Dumped by pg_dump version 12.10

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: dumptable; Type: TABLE; Schema: public; Owner: lxb
--

CREATE TABLE public.dumptable (
    id integer NOT NULL,
    content character(50)
);


ALTER TABLE public.dumptable OWNER TO lxb;

--
-- Data for Name: dumptable; Type: TABLE DATA; Schema: public; Owner: lxb
--

COPY public.dumptable (id, content) FROM stdin;
1       aa                                                
2       bb                                                
\.


--
-- Name: dumptable dumptable_pkey; Type: CONSTRAINT; Schema: public; Owner: lxb
--

ALTER TABLE ONLY public.dumptable
    ADD CONSTRAINT dumptable_pkey PRIMARY KEY (id);


--
-- PostgreSQL database dump complete
--

恢复备份文件

先在数据库中删除 dumptable 这个张表

dumptest-# \d
          List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 public | dumptable  | table | lxb
 public | dumptable2 | table | lxb
(2 rows)

dumptest=# drop table dumptable;
DROP TABLE

SQL脚本文件恢复比较简单，用psql程序来执行该SQL脚本文件即可恢复数据

[root@iv-ebgymdaodpe2tgjofpe5 ~]# psql  -h postgres76be41028654c908.rds-pg.ivolces.com -Ulxb  dumptest < /tmp/dumptest1.sql
Password for user lxb: 
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 2
ALTER TABLE

pg_dump连接选项参数说明：

• -h host或--host=host：指定运行服务器的主机名
• p port或--port=port：指定服务器正在侦听的TCP端口，默认为5432
• -U username或--username=username：指定要连接的用户名。
• dumptest ： 备份的数据库名字。

更多参数，请您参考文档[1]。

清除资源

您可以从 RDS 和 ECS 控制台中轻松删除实例。如果您不再使用，建议您执行删除操作，以免继续为其付费。

[1] https://www.postgresql.org/docs/current/app-pgdump.html

如果您有其他问题，欢迎您联系火山引擎技术支持服务。