Postgres数据备份与恢复

本文主要基于应用数据迁移,PG数据库的库、表、部分字段、自增序列如何很好地、快速有效地进行迁移。主要还是围绕原始命令展开,不涵盖三方工具,有好用的三方工具欢迎分享。

Postgres 版本: 11.4

Docker 部署

接上文:Docker部署Postgres的内容,以下将介绍如何将数据库表中的数据进行迁移。

原生的指令这边介绍两种:pg_dump、copy

内容目录:

  • 1. PG_DUMP
    • 指令参数说明如下
    • 1.1 将sms库中userinfo表结构、数据及其相关序列信息导出
    • 1.2 将sms库中userinfo表结构、序列导出
    • 1.3 将sms库中userinfo表数据、序列值导出
  • 2. COPY
    • 2.1 导出数据指令
    • 2.2 导入数据指令
    • 2.3 重置序列值

1. PG_DUMP

能够通过外部指令,直接导出指定库、指定表的结构、数据、序列信息,可压缩,比较高效,适合大量数据提取或备份

指令参数说明如下

postgres@a:/$ pg_dump --help
pg_dump dumps a database as a text file or to other formats.

Usage:
  pg_dump [OPTION]... [DBNAME]

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
  --no-sync                    do not wait for changes to be written safely to disk
  -?, --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
  -B, --no-blobs               exclude 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
  -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
  -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)
  --binary-upgrade             for use by upgrade utilities only
  --column-inserts             dump data as INSERT commands with column names
  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting
  --disable-triggers           disable triggers during data-only restore
  --enable-row-security        enable row security (dump only content user has
                               access to)
  --exclude-table-data=TABLE   do NOT dump data for the named table(s)
  --if-exists                  use IF EXISTS when dropping objects
  --inserts                    dump data as INSERT commands, rather than COPY
  --load-via-partition-root    load partitions via the root table
  --no-comments                do not dump comments
  --no-publications            do not dump publications
  --no-security-labels         do not dump security label assignments
  --no-subscriptions           do not dump subscriptions
  --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
  --snapshot=SNAPSHOT          use given snapshot for the dump
  --strict-names               require table and/or schema include patterns to
                               match at least one entity each
  --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

If no database name is supplied, then the PGDATABASE environment
variable value is used.

Report bugs to <pgsql-bugs@postgresql.org>.

以上参数中,本次将使用到

  • -f --file=FILENAME :指定输出文件地址
  • -F, --format=c|d|t|p :选择文件内容格式(custom, directory, tar,plain text (default)),默认就是文本格式
  • -a, --data-only :选择导出-仅数据(含自增序列的增量)
  • -s, --schema-only : 选择导出-仅结构(含自增序列的定义)
  • -t, --table=TABLE 仅导出哪些表
  • -T, --exclude-table=TABLE 仅去除哪些表
  • –column-inserts 导出为insert语句形式,而不是COPY
  • -d, --dbname=DBNAME 指定库
  • -h, --host=HOSTNAME 指定host
  • -p, --port=PORT 连接端口
  • -U, --username=NAME 访问用户名,需要是super user权限

1.1 将sms库中userinfo表结构、数据及其相关序列信息导出

 pg_dump -U postgres -d sms -t userinfo -t userinfo_userid_seq -Fp -f /var/lib/postgresql/data/dumpsql/userinfoall

以上以postgres管理员用户,导出sms库中userinfo数据库、userinfo_userid_seq序列的结构+数据,到/var/lib/postgresql/data/dumpsql/userinfoall文件中

将sms库导出的数据导入sms2库:

psql -U postgres -d sms2 -f /var/lib/postgresql/data/dumpsql/userinfoall

SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
ALTER TABLE

1.2 将sms库中userinfo表结构、序列导出

pg_dump -U postgres -d sms -t userinfo  -Fp  -s -f /var/lib/postgresql/data/dumpsql/userinfoschema

以上仅导出结构:

--
-- PostgreSQL database dump
--

-- Dumped from database version 11.4 (Debian 11.4-1.pgdg90+1)
-- Dumped by pg_dump version 11.4 (Debian 11.4-1.pgdg90+1)

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;

--
-- Name: userinfo_userid_seq; Type: SEQUENCE; Schema: public; Owner: test
--

CREATE SEQUENCE public.userinfo_userid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.userinfo_userid_seq OWNER TO test;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: userinfo; Type: TABLE; Schema: public; Owner: test
--

CREATE TABLE public.userinfo (
    userid bigint DEFAULT nextval('public.userinfo_userid_seq'::regclass) NOT NULL,
    account character varying(255) NOT NULL,
    appkey character varying(255),
    createtime timestamp(6) without time zone,
    createuserid character varying(255),
    password character varying(255),
    showname character varying(255),
    status integer,
    updatetime timestamp(6) without time zone,
    updateuserid character varying(255)
);


ALTER TABLE public.userinfo OWNER TO test;

--
-- Name: userinfo userinfo_account_key; Type: CONSTRAINT; Schema: public; Owner: test
--

ALTER TABLE ONLY public.userinfo
    ADD CONSTRAINT userinfo_account_key UNIQUE (account);


--
-- Name: userinfo userinfo_pkey; Type: CONSTRAINT; Schema: public; Owner: test
--

ALTER TABLE ONLY public.userinfo
    ADD CONSTRAINT userinfo_pkey PRIMARY KEY (userid);


--
-- PostgreSQL database dump complete
--

1.3 将sms库中userinfo表数据、序列值导出

pg_dump -U postgres -d sms -t userinfo  -Fp  -a -f /var/lib/postgresql/data/dumpsql/userinfodata

以上仅导出数据:

--
-- PostgreSQL database dump
--

-- Dumped from database version 11.4 (Debian 11.4-1.pgdg90+1)
-- Dumped by pg_dump version 11.4 (Debian 11.4-1.pgdg90+1)

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;

--
-- Data for Name: userinfo; Type: TABLE DATA; Schema: public; Owner: test
--

COPY public.userinfo (userid, account, appkey,  createtime, createuserid,  password, status, updatetime, updateuserid, usertype) FROM stdin;
1	admin	21232f********c3 2016-09-01 13:41:57.200126	1	11111111111	admin	1	2021-11-22 15:56:31.265	1	
\.


--
-- Name: userinfo_userid_seq; Type: SEQUENCE SET; Schema: public; Owner: test
--

SELECT pg_catalog.setval('public.userinfo_userid_seq', 1, true);


--
-- PostgreSQL database dump complete
--


2. COPY

copy指令用于灵活地导出所需的数据,异构数据、表结构不一致数据等

2.1 导出数据指令

-- 全部字段同步
COPY (select * from userinfo where userid >8 and userid < 10 ) TO '{somepath}' WITH csv;

-- 同步指定字段
COPY (select userid,username,sex,mobile from userinfo where userid >8 and userid < 10 ) TO '{somepath}' WITH csv;

2.2 导入数据指令

COPY userinfo FROM '{somepath}' WITH csv;

2.3 重置序列值

在COPY指令下,通常导入数据后,对序列值未重置,可能后面页面新增就会出现主键重复,因为需要手动重置一下序列

SELECT setval('userinfo_userid_seq', max(userid)) FROM userinfo;

你可能感兴趣的