社区博客 | Postgres数据备份与恢复

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

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

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

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

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

-- 全部字段同步
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;

COPY userinfo FROM '{somepath}' WITH csv;

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