postgres 文件系统级别的备份 pg_dump
一,pg_dump 介绍
[postgres@kafka01 psql]$ 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 --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>.
二, 应用
pg_dump -h 192.168.0.188 -U kingle -p 5432 -Fc -Z 9 -t tbl_partition >test_20200608 kingledb --9级压缩备份
--转储表 [postgres@kafka01 backup]$ pg_dump -h 192.168.0.188 -U kingle -p 5432 -Fp --insert --column-inserts -t tbl_partition >test_20200608_2 kingledb
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[postgres@kafka01 backup]$ cat test_20200608_2 -- -- PostgreSQL database dump -- -- Dumped from database version 10.12 -- Dumped by pg_dump version 10.12 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_with_oids = false; -- -- Name: tbl_partition; Type: TABLE; Schema: kingle; Owner: kingle -- CREATE TABLE kingle.tbl_partition ( id integer, name character varying(20), gender boolean, join_date date, dept character(4) ); ALTER TABLE kingle.tbl_partition OWNER TO kingle; -- -- Data for Name: tbl_partition; Type: TABLE DATA; Schema: kingle; Owner: kingle -- -- -- Name: tbl_partition insert_tbl_partition_trigger; Type: TRIGGER; Schema: kingle; Owner: kingle -- CREATE TRIGGER insert_tbl_partition_trigger BEFORE INSERT ON kingle.tbl_partition FOR EACH ROW EXECUTE PROCEDURE kingle.tbl_partition_insert_trigger(); -- -- PostgreSQL database dump complete -- [postgres@kafka01 backup]$
人生就像一滴水,非要落下才后悔!
--kingle