Postgresql - Backup and Restore

备份

# 一般来说备份分为两种，备份表，或者备份库

# 备份表，采用sql模式。

pg_dump -U postgres -d postgres -t table_name -f /data/backup/ table_name.sql

# 如果单表太大，我们采用-Fc的模式，

pg_dump -U postgres -d postgres -Fc -t table_name -f /data/backup/ table_name.dmp

# 看一下导出的sql文件

**********************************************************************************************

--

-- PostgreSQL database dump

--

-- Dumped from database version 10.4

-- Dumped by pg_dump version 10.4

SET statement_timeout = 0;

SET lock_timeout = 0;

SET idle_in_transaction_session_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = off;

SELECT pg_catalog.set_config('search_path', '', false);

SET check_function_bodies = false;

SET client_min_messages = warning;

SET escape_string_warning = off;

SET row_security = off;

SET default_tablespace = '';

SET default_with_oids = false;

--

-- Name: test01; Type: TABLE; Schema: public; Owner: mytest

--

CREATE TABLE public.test01 (

id integer DEFAULT nextval('public.seq_test01_id'::regclass) NOT NULL,

col1 character varying(10)

);

ALTER TABLE public.test01 OWNER TO mytest;

--

-- Data for Name: test01; Type: TABLE DATA; Schema: public; Owner: mytest

--

COPY public.test01 (id, col1) FROM stdin;

1 aaa

2 bbb

\.

--

-- PostgreSQL database dump complete

--

**********************************************************************************************

## 说明：

## 在建表语句中存在序列，但是导出文件中没有创建序列的语句，说明需要自己创建相关序列

# 备份数据库

pg_dump -U postgres -d mytest -f /data/mytest.sql

pg_dump -U postgres -d mytest -f /data/mytest.dmp -Fc

# 看一下备份数据库的sql文件（通过-Fc压缩过的导出dmp文件无法打开）。

**********************************************************************************************

--

-- PostgreSQL database dump

--

-- Dumped from database version 10.4

-- Dumped by pg_dump version 10.4

SET statement_timeout = 0;

SET lock_timeout = 0;

SET idle_in_transaction_session_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = off;

SELECT pg_catalog.set_config('search_path', '', false);

SET check_function_bodies = false;

SET client_min_messages = warning;

SET escape_string_warning = off;

SET row_security = off;

--

-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:

--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

--

-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:

--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

--

-- Name: seq_mytest_id; Type: SEQUENCE; Schema: public; Owner: mytest

--

CREATE SEQUENCE public.seq_mytest_id

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE public.seq_mytest_id OWNER TO mytest;

--

-- Name: seq_test01_id; Type: SEQUENCE; Schema: public; Owner: mytest

--

CREATE SEQUENCE public.seq_test01_id

START WITH 1

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

ALTER TABLE public.seq_test01_id OWNER TO mytest;

SET default_tablespace = '';

SET default_with_oids = false;

--

-- Name: test01; Type: TABLE; Schema: public; Owner: mytest

--

CREATE TABLE public.test01 (

id integer DEFAULT nextval('public.seq_test01_id'::regclass) NOT NULL,

col1 character varying(10)

);

ALTER TABLE public.test01 OWNER TO mytest;

--

-- Data for Name: test01; Type: TABLE DATA; Schema: public; Owner: mytest

--

COPY public.test01 (id, col1) FROM stdin;

1 aaa

2 bbb

\.

--

-- Name: seq_mytest_id; Type: SEQUENCE SET; Schema: public; Owner: mytest

--

SELECT pg_catalog.setval('public.seq_mytest_id', 1, false);

--

-- Name: seq_test01_id; Type: SEQUENCE SET; Schema: public; Owner: mytest

--

SELECT pg_catalog.setval('public.seq_test01_id', 2, true);

--

-- PostgreSQL database dump complete

--

**********************************************************************************************

# 注意：文件中包括了所有数据库中的对象，

# 但备份数据库中没有说明数据库名称，所以在恢复的时候需要首先创建数据库，并在恢复命令中指明恢复到哪个数据库。

恢复

# 如果备份的时候没有加-Fc参数，备份成sql文件的时候，恢复就要使用psql去执行。

# 恢复表

# 恢复sql文件

psql -U postgres -d postgres < test01.sql

# 恢复dmp文件

pg_dump -U postgres -d postgres test01.dmp

# 恢复数据库

# 首先要创建数据库

psql -U postgres -d postgres -c "create database mytest;"

# 恢复sql文件

psql -U postgres -d postgres < test01.sql

# 恢复dmp文件

pg_restore -U postgres -j 12 -Fc -d mytest mytest.dmp

**********************************************************************************************************

# 通过help了解一下所有参数

pg_dump --help

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

--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.