PostgreSql逻辑备份工具(2)-pg_dump

pg_dump可以在postgres的数据库、schema、table级别导出关系对象的定义和数据。其语法格式如下

-bash-4.2$ 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=PATTERN         dump the specified schema(s) only
  -N, --exclude-schema=PATTERN do NOT dump the specified schema(s)
  -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=PATTERN          dump the specified table(s) only
  -T, --exclude-table=PATTERN  do NOT dump the specified 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=PATTERN do NOT dump data for the specified table(s)
  --extra-float-digits=NUM     override default setting for extra_float_digits
  --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
  --on-conflict-do-nothing     add ON CONFLICT DO NOTHING to INSERT commands
  --quote-all-identifiers      quote all identifiers, even if not key words
  --rows-per-insert=NROWS      number of rows per INSERT; implies --inserts
  --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 &lt;pgsql-bugs@lists.postgresql.org&gt;.

-bash-4.2$ 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=PATTERN dump the specified schema(s) only

-N, --exclude-schema=PATTERN do NOT dump the specified schema(s)

-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=PATTERN dump the specified table(s) only

-T, --exclude-table=PATTERN do NOT dump the specified 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=PATTERN do NOT dump data for the specified table(s)

--extra-float-digits=NUM override default setting for extra_float_digits

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

--on-conflict-do-nothing add ON CONFLICT DO NOTHING to INSERT commands

--quote-all-identifiers quote all identifiers, even if not key words

--rows-per-insert=NROWS number of rows per INSERT; implies --inserts

--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@lists.postgresql.org>.

pg_dump命令选项较多，在此不一一介绍，通常见文识意即可。

数据保存格式

pgdump对导出数据支持4种保存格式，分别为文本格式（-Fp），目录格式（-Fd），自定义归档格式(-Fc), tar归档格式(-Ft),其中文本格式保存为SQL Script，因此可以通过psql导入数据，而其他格式需要使用pgrestore来导入数据
文本格式演示如下

-bash-4.2$ pg_dump  -Fp -f tpcc001.sql tpcc001
Password:
-bash-4.2$ ls -l
total 4839820
drwx------  2 postgres postgres        287 Aug 29 00:17 backups
drwx------ 21 postgres postgres       4096 Aug 29 00:00 data
drwx------ 20 postgres postgres       4096 Jul 31 09:59 data-01
-rw-r--r--  1 postgres postgres 4875515993 Aug 14 04:52 data.tar.bz2
-rw-r--r--  1 postgres postgres   80447907 Aug 29 00:26 tpcc001.sql
-bash-4.2$ tail -20 tpcc001.sql
--
-- Name: customer_i2; Type: INDEX; Schema: public; Owner: postgres
--

CREATE UNIQUE INDEX customer_i2 ON public.customer USING btree (c_w_id, c_d_id, c_last, c_first, c_id);
--
-- Name: orders_i2; Type: INDEX; Schema: public; Owner: postgres
--

CREATE UNIQUE INDEX orders_i2 ON public.orders USING btree (o_w_id, o_d_id, o_c_id, o_id);


--
-- PostgreSQL database dump complete

-bash-4.2$ pg_dump -Fp -f tpcc001.sql tpcc001

Password:

-bash-4.2$ ls -l

total 4839820

drwx------ 2 postgres postgres 287 Aug 29 00:17 backups

drwx------ 21 postgres postgres 4096 Aug 29 00:00 data

drwx------ 20 postgres postgres 4096 Jul 31 09:59 data-01

-rw-r--r-- 1 postgres postgres 4875515993 Aug 14 04:52 data.tar.bz2

-rw-r--r-- 1 postgres postgres 80447907 Aug 29 00:26 tpcc001.sql

-bash-4.2$ tail -20 tpcc001.sql

-- Name: customer_i2; Type: INDEX; Schema: public; Owner: postgres

CREATE UNIQUE INDEX customer_i2 ON public.customer USING btree (c_w_id, c_d_id, c_last, c_first, c_id);

-- Name: orders_i2; Type: INDEX; Schema: public; Owner: postgres

CREATE UNIQUE INDEX orders_i2 ON public.orders USING btree (o_w_id, o_d_id, o_c_id, o_id);

-- PostgreSQL database dump complete

自定义归档格式演示如下

-bash-4.2$ pg_dump  -Fc -f tpcc001.dmp tpcc001
Password:
-bash-4.2$ ls -l
total 4888100
drwx------  2 postgres postgres        287 Aug 29 00:17 backups
drwx------ 21 postgres postgres       4096 Aug 29 00:00 data
drwx------ 20 postgres postgres       4096 Jul 31 09:59 data-01
-rw-r--r--  1 postgres postgres 4875515993 Aug 14 04:52 data.tar.bz2
-rw-r--r--  1 postgres postgres   49435913 Aug 29 00:27 tpcc001.dmp
-rw-r--r--  1 postgres postgres   80447907 Aug 29 00:26 tpcc001.sql

-bash-4.2$ pg_dump -Fc -f tpcc001.dmp tpcc001

Password:

-bash-4.2$ ls -l

total 4888100

drwx------ 2 postgres postgres 287 Aug 29 00:17 backups

drwx------ 21 postgres postgres 4096 Aug 29 00:00 data

drwx------ 20 postgres postgres 4096 Jul 31 09:59 data-01

-rw-r--r-- 1 postgres postgres 4875515993 Aug 14 04:52 data.tar.bz2

-rw-r--r-- 1 postgres postgres 49435913 Aug 29 00:27 tpcc001.dmp

-rw-r--r-- 1 postgres postgres 80447907 Aug 29 00:26 tpcc001.sql

tar归档格式演示如下

-bash-4.2$ pg_dump  -Ft -f tpcc001.tar tpcc001
Password:
-bash-4.2$ ls -l
total 4966708
drwx------  2 postgres postgres        287 Aug 29 00:17 backups
drwx------ 21 postgres postgres       4096 Aug 29 00:00 data
drwx------ 20 postgres postgres       4096 Jul 31 09:59 data-01
-rw-r--r--  1 postgres postgres 4875515993 Aug 14 04:52 data.tar.bz2
-rw-r--r--  1 postgres postgres   49435913 Aug 29 00:27 tpcc001.dmp
-rw-r--r--  1 postgres postgres   80447907 Aug 29 00:26 tpcc001.sql
-rw-r--r--  1 postgres postgres   80492032 Aug 29 00:30 tpcc001.tar
-bash-4.2$ tar -tvf tpcc001.tar
-rw------- postgres/postgres 28520 2020-08-29 00:30 toc.dat
-rw------- postgres/postgres 573621 2020-08-29 00:30 3765.dat
-rw------- postgres/postgres 17178293 2020-08-29 00:30 3766.dat
-rw------- postgres/postgres   573621 2020-08-29 00:30 3767.dat
-rw------- postgres/postgres      975 2020-08-29 00:30 3768.dat
-rw------- postgres/postgres   573621 2020-08-29 00:30 3769.dat
-rw------- postgres/postgres  1735196 2020-08-29 00:30 3770.dat
-rw------- postgres/postgres  7562641 2020-08-29 00:30 3771.dat
-rw------- postgres/postgres   573621 2020-08-29 00:30 3772.dat
-rw------- postgres/postgres    81905 2020-08-29 00:30 3773.dat
-rw------- postgres/postgres 19765125 2020-08-29 00:30 3774.dat
-rw------- postgres/postgres  1217526 2020-08-29 00:30 3775.dat
-rw------- postgres/postgres 30586071 2020-08-29 00:30 3776.dat
-rw------- postgres/postgres       87 2020-08-29 00:30 3777.dat
-rw------- postgres/postgres       87 2020-08-29 00:30 3778.dat
-rw------- postgres/postgres    28063 2020-08-29 00:30 restore.sql

-bash-4.2$ pg_dump -Ft -f tpcc001.tar tpcc001

Password:

-bash-4.2$ ls -l

total 4966708

drwx------ 2 postgres postgres 287 Aug 29 00:17 backups

drwx------ 21 postgres postgres 4096 Aug 29 00:00 data

drwx------ 20 postgres postgres 4096 Jul 31 09:59 data-01

-rw-r--r-- 1 postgres postgres 4875515993 Aug 14 04:52 data.tar.bz2

-rw-r--r-- 1 postgres postgres 49435913 Aug 29 00:27 tpcc001.dmp

-rw-r--r-- 1 postgres postgres 80447907 Aug 29 00:26 tpcc001.sql

-rw-r--r-- 1 postgres postgres 80492032 Aug 29 00:30 tpcc001.tar

-bash-4.2$ tar -tvf tpcc001.tar

-rw------- postgres/postgres 28520 2020-08-29 00:30 toc.dat

-rw------- postgres/postgres 573621 2020-08-29 00:30 3765.dat

-rw------- postgres/postgres 17178293 2020-08-29 00:30 3766.dat

-rw------- postgres/postgres 573621 2020-08-29 00:30 3767.dat

-rw------- postgres/postgres 975 2020-08-29 00:30 3768.dat

-rw------- postgres/postgres 573621 2020-08-29 00:30 3769.dat

-rw------- postgres/postgres 1735196 2020-08-29 00:30 3770.dat

-rw------- postgres/postgres 7562641 2020-08-29 00:30 3771.dat

-rw------- postgres/postgres 573621 2020-08-29 00:30 3772.dat

-rw------- postgres/postgres 81905 2020-08-29 00:30 3773.dat

-rw------- postgres/postgres 19765125 2020-08-29 00:30 3774.dat

-rw------- postgres/postgres 1217526 2020-08-29 00:30 3775.dat

-rw------- postgres/postgres 30586071 2020-08-29 00:30 3776.dat

-rw------- postgres/postgres 87 2020-08-29 00:30 3777.dat

-rw------- postgres/postgres 87 2020-08-29 00:30 3778.dat

-rw------- postgres/postgres 28063 2020-08-29 00:30 restore.sql

目录格式请见下文

-E, –encoding=ENCODING

标识导出数据的字符集格式，根据具体使用情况，通常建议设置为数据库的字符集，或者可以设置为当前导出客户端的字符集。例如，当前数据库的字符集为UTF8

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 db1       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 tpcc001   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 tpcc002   | tpcc002  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 tpcc100   | tpcc100  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

postgres=# \l

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

当前客户端的字符集为UTF8

-bash-4.2$ set | grep LANG
LANG=en_US.UTF-8

-bash-4.2$ set | grep LANG

LANG=en_US.UTF-8

以UTF8格式导出数据如下

-bash-4.2$ pg_dump -t public.t1 -Fp -E UTF8 postgres &gt; t1.sql
-bash-4.2$ cat t1.sql
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.3
-- Dumped by pg_dump version 12.3

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: t1; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.t1 (
    id integer,
    name character varying(30)
);


ALTER TABLE public.t1 OWNER TO postgres;

--
-- Data for Name: t1; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.t1 (id, name) FROM stdin;
1       见文识意
\.


--
-- PostgreSQL database dump complete

-bash-4.2$ pg_dump -t public.t1 -Fp -E UTF8 postgres > t1.sql

-bash-4.2$ cat t1.sql

-- PostgreSQL database dump

-- Dumped from database version 12.3

-- Dumped by pg_dump version 12.3

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: t1; Type: TABLE; Schema: public; Owner: postgres

CREATE TABLE public.t1 (

id integer,

name character varying(30)

);

ALTER TABLE public.t1 OWNER TO postgres;

-- Data for Name: t1; Type: TABLE DATA; Schema: public; Owner: postgres

COPY public.t1 (id, name) FROM stdin;

1 见文识意

-- PostgreSQL database dump complete

以GBK字符集导出数据，此时通过cat查看到乱码

-bash-4.2$ pg_dump -t public.t1 -Fp -E GBK postgres &gt; t1.sql
-bash-4.2$ cat t1.sql
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.3
-- Dumped by pg_dump version 12.3

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'GBK';
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: t1; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.t1 (
    id integer,
    name character varying(30)
);


ALTER TABLE public.t1 OWNER TO postgres;

--
-- Data for Name: t1; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.t1 (id, name) FROM stdin;
1       ▒▒▒▒ʶ▒▒
\.


--
-- PostgreSQL database dump complete
--

-bash-4.2$ pg_dump -t public.t1 -Fp -E GBK postgres > t1.sql

-bash-4.2$ cat t1.sql

-- PostgreSQL database dump

-- Dumped from database version 12.3

-- Dumped by pg_dump version 12.3

SET statement_timeout = 0;

SET lock_timeout = 0;

SET idle_in_transaction_session_timeout = 0;

SET client_encoding = 'GBK';

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: t1; Type: TABLE; Schema: public; Owner: postgres

CREATE TABLE public.t1 (

id integer,

name character varying(30)

);

ALTER TABLE public.t1 OWNER TO postgres;

-- Data for Name: t1; Type: TABLE DATA; Schema: public; Owner: postgres

COPY public.t1 (id, name) FROM stdin;

1 ▒▒▒▒ʶ▒▒

-- PostgreSQL database dump complete

并行导出数据

要并行导出数据，请使用-j选项，并且只支持导出为目录格式（-Fd），命令演示如下

-bash-4.2$ rm -rf ./backups/*
-bash-4.2$ ls
backups  data  data-01  data.tar.bz2
-bash-4.2$ pg_dump -v -j 4 -Fd -f backups tpcc001
Password:
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding the columns and types of table "public.a_dump"
pg_dump: finding the columns and types of table "public.customer"
pg_dump: finding the columns and types of table "public.d_dump"
pg_dump: finding the columns and types of table "public.district"
pg_dump: finding the columns and types of table "public.e_dump"
pg_dump: finding the columns and types of table "public.history"
pg_dump: finding the columns and types of table "public.item"
pg_dump: finding the columns and types of table "public.j_dump"
pg_dump: finding the columns and types of table "public.new_order"
pg_dump: finding the columns and types of table "public.order_line"
pg_dump: finding the columns and types of table "public.orders"
pg_dump: finding the columns and types of table "public.stock"
pg_dump: finding the columns and types of table "public.warehouse"
pg_dump: finding the columns and types of table "tpcc001.table1"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading indexes for table "public.customer"
pg_dump: reading indexes for table "public.district"
pg_dump: reading indexes for table "public.item"
pg_dump: reading indexes for table "public.new_order"
pg_dump: reading indexes for table "public.order_line"
pg_dump: reading indexes for table "public.orders"
pg_dump: reading indexes for table "public.stock"
pg_dump: reading indexes for table "public.warehouse"
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row security enabled for table "public.a_dump"
pg_dump: reading policies for table "public.a_dump"
pg_dump: reading row security enabled for table "public.customer"
pg_dump: reading policies for table "public.customer"
pg_dump: reading row security enabled for table "public.d_dump"
pg_dump: reading policies for table "public.d_dump"
pg_dump: reading row security enabled for table "public.district"
pg_dump: reading policies for table "public.district"
pg_dump: reading row security enabled for table "public.e_dump"
pg_dump: reading policies for table "public.e_dump"
pg_dump: reading row security enabled for table "public.history"
pg_dump: reading policies for table "public.history"
pg_dump: reading row security enabled for table "public.item"
pg_dump: reading policies for table "public.item"
pg_dump: reading row security enabled for table "public.j_dump"
pg_dump: reading policies for table "public.j_dump"
pg_dump: reading row security enabled for table "public.new_order"
pg_dump: reading policies for table "public.new_order"
pg_dump: reading row security enabled for table "public.order_line"
pg_dump: reading policies for table "public.order_line"
pg_dump: reading row security enabled for table "public.orders"
pg_dump: reading policies for table "public.orders"
pg_dump: reading row security enabled for table "public.stock"
pg_dump: reading policies for table "public.stock"
pg_dump: reading row security enabled for table "public.warehouse"
pg_dump: reading policies for table "public.warehouse"
pg_dump: reading row security enabled for table "tpcc001.table1"
pg_dump: reading policies for table "tpcc001.table1"
pg_dump: reading publications
pg_dump: reading publication membership
pg_dump: reading publication membership for table "public.a_dump"
pg_dump: reading publication membership for table "public.customer"
pg_dump: reading publication membership for table "public.d_dump"
pg_dump: reading publication membership for table "public.district"
pg_dump: reading publication membership for table "public.e_dump"
pg_dump: reading publication membership for table "public.history"
pg_dump: reading publication membership for table "public.item"
pg_dump: reading publication membership for table "public.j_dump"
pg_dump: reading publication membership for table "public.new_order"
pg_dump: reading publication membership for table "public.order_line"
pg_dump: reading publication membership for table "public.orders"
pg_dump: reading publication membership for table "public.stock"
pg_dump: reading publication membership for table "public.warehouse"
pg_dump: reading publication membership for table "tpcc001.table1"
pg_dump: reading subscriptions
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path =
pg_dump: saving database definition
pg_dump: dumping contents of table "public.order_line"
pg_dump: dumping contents of table "public.customer"
pg_dump: dumping contents of table "public.stock"
pg_dump: dumping contents of table "public.item"
pg_dump: finished item 3771 TABLE DATA item
pg_dump: dumping contents of table "public.history"
pg_dump: finished item 3770 TABLE DATA history
pg_dump: dumping contents of table "public.orders"
pg_dump: finished item 3766 TABLE DATA customer
pg_dump: dumping contents of table "public.new_order"
pg_dump: finished item 3773 TABLE DATA new_order
pg_dump: dumping contents of table "public.a_dump"
pg_dump: finished item 3775 TABLE DATA orders
pg_dump: finished item 3765 TABLE DATA a_dump
pg_dump: dumping contents of table "public.e_dump"
pg_dump: dumping contents of table "public.d_dump"
pg_dump: finished item 3769 TABLE DATA e_dump
pg_dump: dumping contents of table "public.j_dump"
pg_dump: finished item 3767 TABLE DATA d_dump
pg_dump: dumping contents of table "public.district"
pg_dump: finished item 3772 TABLE DATA j_dump
pg_dump: finished item 3768 TABLE DATA district
pg_dump: dumping contents of table "public.warehouse"
pg_dump: finished item 3777 TABLE DATA warehouse
pg_dump: dumping contents of table "tpcc001.table1"
pg_dump: finished item 3778 TABLE DATA table1
pg_dump: finished item 3774 TABLE DATA order_line
pg_dump: finished item 3776 TABLE DATA stock
-bash-4.2$ ls -l backups/
total 48248
-rw-r--r-- 1 postgres postgres    72383 Aug 29 00:17 3765.dat.gz
-rw-r--r-- 1 postgres postgres 11534229 Aug 29 00:17 3766.dat.gz
-rw-r--r-- 1 postgres postgres    72383 Aug 29 00:17 3767.dat.gz
-rw-r--r-- 1 postgres postgres      626 Aug 29 00:17 3768.dat.gz
-rw-r--r-- 1 postgres postgres    72383 Aug 29 00:17 3769.dat.gz
-rw-r--r-- 1 postgres postgres   534795 Aug 29 00:17 3770.dat.gz
-rw-r--r-- 1 postgres postgres  5448083 Aug 29 00:17 3771.dat.gz
-rw-r--r-- 1 postgres postgres    72383 Aug 29 00:17 3772.dat.gz
-rw-r--r-- 1 postgres postgres    18823 Aug 29 00:17 3773.dat.gz
-rw-r--r-- 1 postgres postgres  8745369 Aug 29 00:17 3774.dat.gz
-rw-r--r-- 1 postgres postgres   199021 Aug 29 00:17 3775.dat.gz
-rw-r--r-- 1 postgres postgres 22576368 Aug 29 00:17 3776.dat.gz
-rw-r--r-- 1 postgres postgres       99 Aug 29 00:17 3777.dat.gz
-rw-r--r-- 1 postgres postgres       99 Aug 29 00:17 3778.dat.gz
-rw-r--r-- 1 postgres postgres    28520 Aug 29 00:17 toc.dat

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

-bash-4.2$ rm -rf ./backups/*

-bash-4.2$ ls

backups data data-01 data.tar.bz2

-bash-4.2$ pg_dump -v -j 4 -Fd -f backups tpcc001

Password:

pg_dump: last built-in OID is 16383

pg_dump: reading extensions

pg_dump: identifying extension members

pg_dump: reading schemas

pg_dump: reading user-defined tables

pg_dump: reading user-defined functions

pg_dump: reading user-defined types

pg_dump: reading procedural languages

pg_dump: reading user-defined aggregate functions

pg_dump: reading user-defined operators

pg_dump: reading user-defined access methods

pg_dump: reading user-defined operator classes

pg_dump: reading user-defined operator families

pg_dump: reading user-defined text search parsers

pg_dump: reading user-defined text search templates

pg_dump: reading user-defined text search dictionaries

pg_dump: reading user-defined text search configurations

pg_dump: reading user-defined foreign-data wrappers

pg_dump: reading user-defined foreign servers

pg_dump: reading default privileges

pg_dump: reading user-defined collations

pg_dump: reading user-defined conversions

pg_dump: reading type casts

pg_dump: reading transforms

pg_dump: reading table inheritance information

pg_dump: reading event triggers

pg_dump: finding extension tables

pg_dump: finding inheritance relationships

pg_dump: reading column info for interesting tables

pg_dump: finding the columns and types of table "public.a_dump"

pg_dump: finding the columns and types of table "public.customer"

pg_dump: finding the columns and types of table "public.d_dump"

pg_dump: finding the columns and types of table "public.district"

pg_dump: finding the columns and types of table "public.e_dump"

pg_dump: finding the columns and types of table "public.history"

pg_dump: finding the columns and types of table "public.item"

pg_dump: finding the columns and types of table "public.j_dump"

pg_dump: finding the columns and types of table "public.new_order"

pg_dump: finding the columns and types of table "public.order_line"

pg_dump: finding the columns and types of table "public.orders"

pg_dump: finding the columns and types of table "public.stock"

pg_dump: finding the columns and types of table "public.warehouse"

pg_dump: finding the columns and types of table "tpcc001.table1"

pg_dump: flagging inherited columns in subtables

pg_dump: reading indexes

pg_dump: reading indexes for table "public.customer"

pg_dump: reading indexes for table "public.district"

pg_dump: reading indexes for table "public.item"

pg_dump: reading indexes for table "public.new_order"

pg_dump: reading indexes for table "public.order_line"

pg_dump: reading indexes for table "public.orders"

pg_dump: reading indexes for table "public.stock"

pg_dump: reading indexes for table "public.warehouse"

pg_dump: flagging indexes in partitioned tables

pg_dump: reading extended statistics

pg_dump: reading constraints

pg_dump: reading triggers

pg_dump: reading rewrite rules

pg_dump: reading policies

pg_dump: reading row security enabled for table "public.a_dump"

pg_dump: reading policies for table "public.a_dump"

pg_dump: reading row security enabled for table "public.customer"

pg_dump: reading policies for table "public.customer"

pg_dump: reading row security enabled for table "public.d_dump"

pg_dump: reading policies for table "public.d_dump"

pg_dump: reading row security enabled for table "public.district"

pg_dump: reading policies for table "public.district"

pg_dump: reading row security enabled for table "public.e_dump"

pg_dump: reading policies for table "public.e_dump"

pg_dump: reading row security enabled for table "public.history"

pg_dump: reading policies for table "public.history"

pg_dump: reading row security enabled for table "public.item"

pg_dump: reading policies for table "public.item"

pg_dump: reading row security enabled for table "public.j_dump"

pg_dump: reading policies for table "public.j_dump"

pg_dump: reading row security enabled for table "public.new_order"

pg_dump: reading policies for table "public.new_order"

pg_dump: reading row security enabled for table "public.order_line"

pg_dump: reading policies for table "public.order_line"

pg_dump: reading row security enabled for table "public.orders"

pg_dump: reading policies for table "public.orders"

pg_dump: reading row security enabled for table "public.stock"

pg_dump: reading policies for table "public.stock"

pg_dump: reading row security enabled for table "public.warehouse"

pg_dump: reading policies for table "public.warehouse"

pg_dump: reading row security enabled for table "tpcc001.table1"

pg_dump: reading policies for table "tpcc001.table1"

pg_dump: reading publications

pg_dump: reading publication membership

pg_dump: reading publication membership for table "public.a_dump"

pg_dump: reading publication membership for table "public.customer"

pg_dump: reading publication membership for table "public.d_dump"

pg_dump: reading publication membership for table "public.district"

pg_dump: reading publication membership for table "public.e_dump"

pg_dump: reading publication membership for table "public.history"

pg_dump: reading publication membership for table "public.item"

pg_dump: reading publication membership for table "public.j_dump"

pg_dump: reading publication membership for table "public.new_order"

pg_dump: reading publication membership for table "public.order_line"

pg_dump: reading publication membership for table "public.orders"

pg_dump: reading publication membership for table "public.stock"

pg_dump: reading publication membership for table "public.warehouse"

pg_dump: reading publication membership for table "tpcc001.table1"

pg_dump: reading subscriptions

pg_dump: reading large objects

pg_dump: reading dependency data

pg_dump: saving encoding = UTF8

pg_dump: saving standard_conforming_strings = on

pg_dump: saving search_path =

pg_dump: saving database definition

pg_dump: dumping contents of table "public.order_line"

pg_dump: dumping contents of table "public.customer"

pg_dump: dumping contents of table "public.stock"

pg_dump: dumping contents of table "public.item"

pg_dump: finished item 3771 TABLE DATA item

pg_dump: dumping contents of table "public.history"

pg_dump: finished item 3770 TABLE DATA history

pg_dump: dumping contents of table "public.orders"

pg_dump: finished item 3766 TABLE DATA customer

pg_dump: dumping contents of table "public.new_order"

pg_dump: finished item 3773 TABLE DATA new_order

pg_dump: dumping contents of table "public.a_dump"

pg_dump: finished item 3775 TABLE DATA orders

pg_dump: finished item 3765 TABLE DATA a_dump

pg_dump: dumping contents of table "public.e_dump"

pg_dump: dumping contents of table "public.d_dump"

pg_dump: finished item 3769 TABLE DATA e_dump

pg_dump: dumping contents of table "public.j_dump"

pg_dump: finished item 3767 TABLE DATA d_dump

pg_dump: dumping contents of table "public.district"

pg_dump: finished item 3772 TABLE DATA j_dump

pg_dump: finished item 3768 TABLE DATA district

pg_dump: dumping contents of table "public.warehouse"

pg_dump: finished item 3777 TABLE DATA warehouse

pg_dump: dumping contents of table "tpcc001.table1"

pg_dump: finished item 3778 TABLE DATA table1

pg_dump: finished item 3774 TABLE DATA order_line

pg_dump: finished item 3776 TABLE DATA stock

-bash-4.2$ ls -l backups/

total 48248

-rw-r--r-- 1 postgres postgres 72383 Aug 29 00:17 3765.dat.gz

-rw-r--r-- 1 postgres postgres 11534229 Aug 29 00:17 3766.dat.gz

-rw-r--r-- 1 postgres postgres 72383 Aug 29 00:17 3767.dat.gz

-rw-r--r-- 1 postgres postgres 626 Aug 29 00:17 3768.dat.gz

-rw-r--r-- 1 postgres postgres 72383 Aug 29 00:17 3769.dat.gz

-rw-r--r-- 1 postgres postgres 534795 Aug 29 00:17 3770.dat.gz

-rw-r--r-- 1 postgres postgres 5448083 Aug 29 00:17 3771.dat.gz

-rw-r--r-- 1 postgres postgres 72383 Aug 29 00:17 3772.dat.gz

-rw-r--r-- 1 postgres postgres 18823 Aug 29 00:17 3773.dat.gz

-rw-r--r-- 1 postgres postgres 8745369 Aug 29 00:17 3774.dat.gz

-rw-r--r-- 1 postgres postgres 199021 Aug 29 00:17 3775.dat.gz

-rw-r--r-- 1 postgres postgres 22576368 Aug 29 00:17 3776.dat.gz

-rw-r--r-- 1 postgres postgres 99 Aug 29 00:17 3777.dat.gz

-rw-r--r-- 1 postgres postgres 99 Aug 29 00:17 3778.dat.gz

-rw-r--r-- 1 postgres postgres 28520 Aug 29 00:17 toc.dat

一	二	三	四	五	六	日
« 8月
		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	31

数据保存格式

-E, –encoding=ENCODING

并行导出数据

发表评论 取消回复

发表评论取消回复