pg_dump 备份日志解析
为了了解pg_dump 备份流程， 我们可以通过日志来分析，pg_dump备份的时候，做了那些动作。 当然，对于那些大神来讲，通过看源码可能会更清晰。我这里就通过简单的test 库和两张测试表，来演示简单的备份流程。
1.表结构
查看test 数据库的表信息
test=# \d
List of relations
Schema | Name | Type | Owner
--------+-------+-------+----------
public | asher | table | postgres
public | test | table | postgres
(2 rows)
test=# \d asher
Table "public.asher"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
name | text | | |
test=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
i | integer | | |
Indexes:
"idx" btree (i)2.设置参数
# 设置参数
echo "log_statement = all" >> postgresql.auto.conf
echo "log_destination = 'stderr'" >> postgresql.auto.conf
#执行备份命令， 这里默认的是以文件的方式输出 。
pg_dump -d test > /tmp/1.sql3.分析日志
# 1.连数据库，并参数相关参数， 包括 设置会话可重复读
2022-04-19 08:47:37 HKT [26559]: db=[unknown],user=[unknown],app=[unknown],client=[local] LOG: connection received: host=[local]
2022-04-19 08:47:37 HKT [26559]: db=test,user=postgres,app=[unknown],client=[local] LOG: connection authorized: user=postgres database=test application_name=pg_dump
2022-04-19 08:47:37 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT pg_catalog.set_config('search_path', '', false);
2022-04-19 08:47:37 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT pg_catalog.pg_is_in_recovery()
2022-04-19 08:47:37 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT pg_catalog.set_config('search_path', '', false);
2022-04-19 08:47:37 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SET DATESTYLE = ISO
2022-04-19 08:47:37 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SET INTERVALSTYLE = POSTGRES
2022-04-19 08:47:37 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SET extra_float_digits TO 3
2022-04-19 08:47:37 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SET synchronize_seqscans TO off
2022-04-19 08:47:37 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SET statement_timeout = 0
2022-04-19 08:47:37 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SET lock_timeout = 0
2022-04-19 08:47:37 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SET idle_in_transaction_session_timeout = 0
2022-04-19 08:47:37 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SET row_security = off
2022-04-19 08:47:37 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: BEGIN
2022-04-19 08:47:37 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY
# 2.查询需要备份的对象
调用tblinfo = getSchemaData(fout, &numTables);决定导出哪些数据库对象。 为了存储每个对象的元数据，这些函数会malloc申请空间，直到pg_dump进程结束才释放。
2022-04-19 08:47:37 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT x.tableoid, x.oid, x.extname, n.nspname, x.extrelocatable, x.extversion, x.extconfig, x.extcondition FROM pg_extension x JOIN pg_namespace n ON n.oid = x.extnamespace
2022-04-19 08:47:37 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT classid, objid, refobjid FROM pg_depend WHERE refclassid = 'pg_extension'::regclass AND deptype = 'e' ORDER BY 3
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT n.tableoid, n.oid, n.nspname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) AS rolname, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(n.nspacl,pg_catalog.acldefault('n',n.nspowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('n',n.nspowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) as nspacl, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('n',n.nspowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(n.nspacl,pg_catalog.acldefault('n',n.nspowner))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) as rnspacl, NULL as initnspacl, NULL as initrnspacl FROM pg_namespace n LEFT JOIN pg_init_privs pip ON (n.oid = pip.objoid AND pip.classoid = 'pg_namespace'::regclass AND pip.objsubid = 0)
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT c.tableoid, c.oid, c.relname, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(c.relacl,pg_catalog.acldefault(CASE WHEN c.relkind = 'S' THEN 's' ELSE 'r' END::"char",c.relowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(CASE WHEN c.relkind = 'S' THEN 's' ELSE 'r' END::"char",c.relowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) AS relacl, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(CASE WHEN c.relkind = 'S' THEN 's' ELSE 'r' END::"char",c.relowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(c.relacl,pg_catalog.acldefault(CASE WHEN c.relkind = 'S' THEN 's' ELSE 'r' END::"char",c.relowner))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) as rrelacl, NULL AS initrelacl, NULL as initrrelacl, c.relkind, c.relnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = c.relowner) AS rolname, c.relchecks, c.relhastriggers, c.relhasindex, c.relhasrules, 'f'::bool AS relhasoids, c.relrowsecurity, c.relforcerowsecurity, c.relfrozenxid, c.relminmxid, tc.oid AS toid, tc.relfrozenxid AS tfrozenxid, tc.relminmxid AS tminmxid, c.relpersistence, c.relispopulated, c.relreplident, c.relpages, am.amname, CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, d.refobjid AS owning_tab, d.refobjsubid AS owning_col, (SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, array_remove(array_remove(c.reloptions,'check_option=local'),'check_option=cascaded') AS reloptions, CASE WHEN 'check_option=local' = ANY (c.reloptions) THEN 'LOCAL'::text WHEN 'check_option=cascaded' = ANY (c.reloptions) THEN 'CASCADED'::text ELSE NULL END AS checkoption, tc.reloptions AS toast_reloptions, c.relkind = 'S' AND EXISTS (SELECT 1 FROM pg_depend WHERE classid = 'pg_class'::regclass AND objid = c.oid AND objsubid = 0 AND refclassid = 'pg_class'::regclass AND deptype = 'i') AS is_identity_sequence, EXISTS (SELECT 1 FROM pg_attribute at LEFT JOIN pg_init_privs pip ON (c.oid = pip.objoid AND pip.classoid = 'pg_class'::regclass AND pip.objsubid = at.attnum)WHERE at.attrelid = c.oid AND ((SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(at.attacl,pg_catalog.acldefault('c',c.relowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('c',c.relowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) IS NOT NULL OR (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('c',c.relowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(at.attacl,pg_catalog.acldefault('c',c.relowner))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) IS NOT NULL OR NULL IS NOT NULL OR NULL IS NOT NULL))AS changed_acl, pg_get_partkeydef(c.oid) AS partkeydef, c.relispartition AS ispartition, pg_get_expr(c.relpartbound, c.oid) AS partbound FROM pg_class c LEFT JOIN pg_depend d ON (c.relkind = 'S' AND d.classid = c.tableoid AND d.objid = c.oid AND d.objsubid = 0 AND d.refclassid = c.tableoid AND d.deptype IN ('a', 'i')) LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid AND c.relkind <> 'p') LEFT JOIN pg_am am ON (c.relam = am.oid) LEFT JOIN pg_init_privs pip ON (c.oid = pip.objoid AND pip.classoid = 'pg_class'::regclass AND pip.objsubid = 0) WHERE c.relkind in ('r', 'S', 'v', 'c', 'm', 'f', 'p') ORDER BY c.oid
# 3.设置共享锁
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: LOCK TABLE public.test IN ACCESS SHARE MODE
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: LOCK TABLE public.asher IN ACCESS SHARE MODE
# 4.调用getTableData函数，获取表对应的数据，
实际上，并不是表真正数据，而是为表数据建立一个“导出对象”，将来导出时，依据导出对象获取真是的数据再导出。虽然先把导出对象放到AH->toc链表上，真正导出时导出数据，不会占用大量内存空间，但是针对这些元数据，当表特别多的时候，由于进程不退出不释放内存，占用内存还是非常在的。
该函数调用makeTableDataInfo：tdinfo->dobj.catId.tableoid、tdinfo->dobj.catId.oid、tdinfo->dobj.name、tdinfo->dobj.namespace 信息，并将dobj保存到dumpIdMap数组
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT p.tableoid, p.oid, p.proname, p.prolang, p.pronargs, p.proargtypes, p.prorettype, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(p.proacl,pg_catalog.acldefault('f',p.proowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('f',p.proowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) AS proacl, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('f',p.proowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(p.proacl,pg_catalog.acldefault('f',p.proowner))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) AS rproacl, NULL AS initproacl, NULL AS initrproacl, p.pronamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = p.proowner) AS rolname FROM pg_proc p LEFT JOIN pg_init_privs pip ON (p.oid = pip.objoid AND pip.classoid = 'pg_proc'::regclass AND pip.objsubid = 0) WHERE p.prokind <> 'a'
AND NOT EXISTS (SELECT 1 FROM pg_depend WHERE classid = 'pg_proc'::regclass AND objid = p.oid AND deptype = 'i')
AND (
pronamespace != (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog')
OR EXISTS (SELECT 1 FROM pg_cast
WHERE pg_cast.oid > 16383
AND p.oid = pg_cast.castfunc)
OR EXISTS (SELECT 1 FROM pg_transform
WHERE pg_transform.oid > 16383 AND
(p.oid = pg_transform.trffromsql
OR p.oid = pg_transform.trftosql))
OR p.proacl IS DISTINCT FROM pip.initprivs)
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT t.tableoid, t.oid, t.typname, t.typnamespace, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(t.typacl,pg_catalog.acldefault('T',t.typowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('T',t.typowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) AS typacl, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('T',t.typowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(t.typacl,pg_catalog.acldefault('T',t.typowner))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) AS rtypacl, NULL AS inittypacl, NULL AS initrtypacl, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = t.typowner) AS rolname, t.typelem, t.typrelid, CASE WHEN t.typrelid = 0 THEN ' '::"char" ELSE (SELECT relkind FROM pg_class WHERE oid = t.typrelid) END AS typrelkind, t.typtype, t.typisdefined, t.typname[0] = '_' AND t.typelem != 0 AND (SELECT typarray FROM pg_type te WHERE oid = t.typelem) = t.oid AS isarray FROM pg_type t LEFT JOIN pg_init_privs pip ON (t.oid = pip.objoid AND pip.classoid = 'pg_type'::regclass AND pip.objsubid = 0)
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT l.tableoid, l.oid, l.lanname, l.lanpltrusted, l.lanplcallfoid, l.laninline, l.lanvalidator, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(l.lanacl,pg_catalog.acldefault('l',l.lanowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('l',l.lanowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) AS lanacl, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('l',l.lanowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(l.lanacl,pg_catalog.acldefault('l',l.lanowner))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) AS rlanacl, NULL AS initlanacl, NULL AS initrlanacl, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = l.lanowner) AS lanowner FROM pg_language l LEFT JOIN pg_init_privs pip ON (l.oid = pip.objoid AND pip.classoid = 'pg_language'::regclass AND pip.objsubid = 0) WHERE l.lanispl ORDER BY l.oid
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT p.tableoid, p.oid, p.proname AS aggname, p.pronamespace AS aggnamespace, p.pronargs, p.proargtypes, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = p.proowner) AS rolname, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(p.proacl,pg_catalog.acldefault('f',p.proowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('f',p.proowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) AS aggacl, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('f',p.proowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(p.proacl,pg_catalog.acldefault('f',p.proowner))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) AS raggacl, NULL AS initaggacl, NULL AS initraggacl FROM pg_proc p LEFT JOIN pg_init_privs pip ON (p.oid = pip.objoid AND pip.classoid = 'pg_proc'::regclass AND pip.objsubid = 0) WHERE p.prokind = 'a' AND (p.pronamespace != (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog') OR p.proacl IS DISTINCT FROM pip.initprivs)
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT tableoid, oid, oprname, oprnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = oprowner) AS rolname, oprkind, oprcode::oid AS oprcode FROM pg_operator
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT tableoid, oid, amname, amtype, amhandler::pg_catalog.regproc AS amhandler FROM pg_am
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT tableoid, oid, opcname, opcnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = opcowner) AS rolname FROM pg_opclass
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT tableoid, oid, opfname, opfnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = opfowner) AS rolname FROM pg_opfamily
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT tableoid, oid, prsname, prsnamespace, prsstart::oid, prstoken::oid, prsend::oid, prsheadline::oid, prslextype::oid FROM pg_ts_parser
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT tableoid, oid, tmplname, tmplnamespace, tmplinit::oid, tmpllexize::oid FROM pg_ts_template
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT tableoid, oid, dictname, dictnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = dictowner) AS rolname, dicttemplate, dictinitoption FROM pg_ts_dict
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT tableoid, oid, cfgname, cfgnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = cfgowner) AS rolname, cfgparser FROM pg_ts_config
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT f.tableoid, f.oid, f.fdwname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = f.fdwowner) AS rolname, f.fdwhandler::pg_catalog.regproc, f.fdwvalidator::pg_catalog.regproc, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(f.fdwacl,pg_catalog.acldefault('F',f.fdwowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('F',f.fdwowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) AS fdwacl, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('F',f.fdwowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(f.fdwacl,pg_catalog.acldefault('F',f.fdwowner))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) AS rfdwacl, NULL AS initfdwacl, NULL AS initrfdwacl, array_to_string(ARRAY(SELECT quote_ident(option_name) || ' ' || quote_literal(option_value) FROM pg_options_to_table(f.fdwoptions) ORDER BY option_name), E',
') AS fdwoptions FROM pg_foreign_data_wrapper f LEFT JOIN pg_init_privs pip ON (f.oid = pip.objoid AND pip.classoid = 'pg_foreign_data_wrapper'::regclass AND pip.objsubid = 0)
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT f.tableoid, f.oid, f.srvname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = f.srvowner) AS rolname, f.srvfdw, f.srvtype, f.srvversion, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(f.srvacl,pg_catalog.acldefault('S',f.srvowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('S',f.srvowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) AS srvacl, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('S',f.srvowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(f.srvacl,pg_catalog.acldefault('S',f.srvowner))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) AS rsrvacl, NULL AS initsrvacl, NULL AS initrsrvacl, array_to_string(ARRAY(SELECT quote_ident(option_name) || ' ' || quote_literal(option_value) FROM pg_options_to_table(f.srvoptions) ORDER BY option_name), E',
') AS srvoptions FROM pg_foreign_server f LEFT JOIN pg_init_privs pip ON (f.oid = pip.objoid AND pip.classoid = 'pg_foreign_server'::regclass AND pip.objsubid = 0)
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT d.oid, d.tableoid, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = d.defaclrole) AS defaclrole, d.defaclnamespace, d.defaclobjtype, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(defaclacl,pg_catalog.acldefault(CASE WHEN defaclobjtype = 'S' THEN 's' ELSE defaclobjtype END::"char",defaclrole))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(CASE WHEN defaclobjtype = 'S' THEN 's' ELSE defaclobjtype END::"char",defaclrole))) AS init(init_acl) WHERE acl = init_acl)) as foo) AS defaclacl, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(CASE WHEN defaclobjtype = 'S' THEN 's' ELSE defaclobjtype END::"char",defaclrole))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(defaclacl,pg_catalog.acldefault(CASE WHEN defaclobjtype = 'S' THEN 's' ELSE defaclobjtype END::"char",defaclrole))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) AS rdefaclacl, NULL AS initdefaclacl, NULL AS initrdefaclacl FROM pg_default_acl d LEFT JOIN pg_init_privs pip ON (d.oid = pip.objoid AND pip.classoid = 'pg_default_acl'::regclass AND pip.objsubid = 0)
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT tableoid, oid, collname, collnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = collowner) AS rolname FROM pg_collation
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT tableoid, oid, conname, connamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = conowner) AS rolname FROM pg_conversion
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT tableoid, oid, castsource, casttarget, castfunc, castcontext, castmethod FROM pg_cast ORDER BY 3,4
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT tableoid, oid, trftype, trflang, trffromsql::oid, trftosql::oid FROM pg_transform ORDER BY 3,4
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT inhrelid, inhparent FROM pg_inherits
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT e.tableoid, e.oid, evtname, evtenabled, evtevent, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = evtowner) AS evtowner, array_to_string(array(select quote_literal(x) from unnest(evttags) as t(x)), ', ') as evttags, e.evtfoid::regproc as evtfname FROM pg_event_trigger e ORDER BY e.oid
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT conrelid, confrelid FROM pg_constraint JOIN pg_depend ON (objid = confrelid) WHERE contype = 'f' AND refclassid = 'pg_extension'::regclass AND classid = 'pg_class'::regclass;
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT
a.attnum,
a.attname,
a.atttypmod,
a.attstattarget,
a.attstorage,
t.typstorage,
a.attnotnull,
a.atthasdef,
a.attisdropped,
a.attlen,
a.attalign,
a.attislocal,
pg_catalog.format_type(t.oid, a.atttypmod) AS atttypname,
a.attgenerated,
CASE WHEN a.atthasmissing AND NOT a.attisdropped THEN a.attmissingval ELSE null END AS attmissingval,
a.attidentity,
pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM pg_catalog.pg_options_to_table(attfdwoptions) ORDER BY option_name), E',
') AS attfdwoptions,
CASE WHEN a.attcollation <> t.typcollation THEN a.attcollation ELSE 0 END AS attcollation,
array_to_string(a.attoptions, ', ') AS attoptions
FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t ON a.atttypid = t.oid
WHERE a.attrelid = '26178'::pg_catalog.oid AND a.attnum > 0::pg_catalog.int2
ORDER BY a.attnum
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT
a.attnum,
a.attname,
a.atttypmod,
a.attstattarget,
a.attstorage,
t.typstorage,
a.attnotnull,
a.atthasdef,
a.attisdropped,
a.attlen,
a.attalign,
a.attislocal,
pg_catalog.format_type(t.oid, a.atttypmod) AS atttypname,
a.attgenerated,
CASE WHEN a.atthasmissing AND NOT a.attisdropped THEN a.attmissingval ELSE null END AS attmissingval,
a.attidentity,
pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM pg_catalog.pg_options_to_table(attfdwoptions) ORDER BY option_name), E',
') AS attfdwoptions,
CASE WHEN a.attcollation <> t.typcollation THEN a.attcollation ELSE 0 END AS attcollation,
array_to_string(a.attoptions, ', ') AS attoptions
FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t ON a.atttypid = t.oid
WHERE a.attrelid = '26185'::pg_catalog.oid AND a.attnum > 0::pg_catalog.int2
ORDER BY a.attnum
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT t.tableoid, t.oid, t.relname AS indexname, inh.inhparent AS parentidx, pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, i.indnkeyatts AS indnkeyatts, i.indnatts AS indnatts, i.indkey, i.indisclustered, i.indisreplident, c.contype, c.conname, c.condeferrable, c.condeferred, c.tableoid AS contableoid, c.oid AS conoid, pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, (SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, t.reloptions AS indreloptions, (SELECT pg_catalog.array_agg(attnum ORDER BY attnum) FROM pg_catalog.pg_attribute WHERE attrelid = i.indexrelid AND attstattarget >= 0) AS indstatcols,(SELECT pg_catalog.array_agg(attstattarget ORDER BY attnum) FROM pg_catalog.pg_attribute WHERE attrelid = i.indexrelid AND attstattarget >= 0) AS indstatvals FROM pg_catalog.pg_index i JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) JOIN pg_catalog.pg_class t2 ON (t2.oid = i.indrelid) LEFT JOIN pg_catalog.pg_constraint c ON (i.indrelid = c.conrelid AND i.indexrelid = c.conindid AND c.contype IN ('p','u','x')) LEFT JOIN pg_catalog.pg_inherits inh ON (inh.inhrelid = indexrelid) WHERE i.indrelid = '26178'::pg_catalog.oid AND (i.indisvalid OR t2.relkind = 'p') AND i.indisready ORDER BY indexname
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT tableoid, oid, stxname, stxnamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = stxowner) AS rolname FROM pg_catalog.pg_statistic_ext
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT tableoid, oid, rulename, ev_class AS ruletable, ev_type, is_instead, ev_enabled FROM pg_rewrite ORDER BY oid
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT oid, tableoid, pol.polname, pol.polcmd, pol.polpermissive, CASE WHEN pol.polroles = '{0}' THEN NULL ELSE pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(rolname) from pg_catalog.pg_roles WHERE oid = ANY(pol.polroles)), ', ') END AS polroles, pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck FROM pg_catalog.pg_policy pol WHERE polrelid = '26178'
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT oid, tableoid, pol.polname, pol.polcmd, pol.polpermissive, CASE WHEN pol.polroles = '{0}' THEN NULL ELSE pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(rolname) from pg_catalog.pg_roles WHERE oid = ANY(pol.polroles)), ', ') END AS polroles, pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck FROM pg_catalog.pg_policy pol WHERE polrelid = '26185'
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT p.tableoid, p.oid, p.pubname, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = p.pubowner) AS rolname, p.puballtables, p.pubinsert, p.pubupdate, p.pubdelete, p.pubtruncate FROM pg_publication p
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT pr.tableoid, pr.oid, p.pubname FROM pg_publication_rel pr, pg_publication p WHERE pr.prrelid = '26178' AND p.oid = pr.prpubid
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT pr.tableoid, pr.oid, p.pubname FROM pg_publication_rel pr, pg_publication p WHERE pr.prrelid = '26185' AND p.oid = pr.prpubid
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT s.tableoid, s.oid, s.subname,(SELECT rolname FROM pg_catalog.pg_roles WHERE oid = s.subowner) AS rolname, s.subconninfo, s.subslotname, s.subsynccommit, s.subpublications FROM pg_subscription s WHERE s.subdbid = (SELECT oid FROM pg_database WHERE datname = current_database())
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: WITH RECURSIVE w AS ( SELECT d1.objid, d2.refobjid, c2.relkind AS refrelkind FROM pg_depend d1 JOIN pg_class c1 ON c1.oid = d1.objid AND c1.relkind = 'm' JOIN pg_rewrite r1 ON r1.ev_class = d1.objid JOIN pg_depend d2 ON d2.classid = 'pg_rewrite'::regclass AND d2.objid = r1.oid AND d2.refobjid <> d1.objid JOIN pg_class c2 ON c2.oid = d2.refobjid AND c2.relkind IN ('m','v') WHERE d1.classid = 'pg_class'::regclass UNION SELECT w.objid, d3.refobjid, c3.relkind FROM w JOIN pg_rewrite r3 ON r3.ev_class = w.refobjid JOIN pg_depend d3 ON d3.classid = 'pg_rewrite'::regclass AND d3.objid = r3.oid AND d3.refobjid <> w.refobjid JOIN pg_class c3 ON c3.oid = d3.refobjid AND c3.relkind IN ('m','v') ) SELECT 'pg_class'::regclass::oid AS classid, objid, refobjid FROM w WHERE refrelkind = 'm'
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT l.oid, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = l.lomowner) AS rolname, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(l.lomacl,pg_catalog.acldefault('L',l.lomowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('L',l.lomowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) AS lomacl, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('L',l.lomowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(l.lomacl,pg_catalog.acldefault('L',l.lomowner))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) AS rlomacl, NULL AS initlomacl, NULL AS initrlomacl FROM pg_largeobject_metadata l LEFT JOIN pg_init_privs pip ON (l.oid = pip.objoid AND pip.classoid = 'pg_largeobject'::regclass AND pip.objsubid = 0)
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT classid, objid, refclassid, refobjid, deptype FROM pg_depend WHERE deptype != 'p' AND deptype != 'e'
UNION ALL
SELECT 'pg_opfamily'::regclass AS classid, amopfamily AS objid, refclassid, refobjid, deptype FROM pg_depend d, pg_amop o WHERE deptype NOT IN ('p', 'e', 'i') AND classid = 'pg_amop'::regclass AND objid = o.oid AND NOT (refclassid = 'pg_opfamily'::regclass AND amopfamily = refobjid)
UNION ALL
SELECT 'pg_opfamily'::regclass AS classid, amprocfamily AS objid, refclassid, refobjid, deptype FROM pg_depend d, pg_amproc p WHERE deptype NOT IN ('p', 'e', 'i') AND classid = 'pg_amproc'::regclass AND objid = p.oid AND NOT (refclassid = 'pg_opfamily'::regclass AND amprocfamily = refobjid)
ORDER BY 1,2
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT pg_catalog.current_schemas(false)
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT proretset, prosrc, probin, pg_catalog.pg_get_function_arguments(oid) AS funcargs, pg_catalog.pg_get_function_identity_arguments(oid) AS funciargs, pg_catalog.pg_get_function_result(oid) AS funcresult, array_to_string(protrftypes, ' ') AS protrftypes, prokind, provolatile, proisstrict, prosecdef, proleakproof, proconfig, procost, prorows, prosupport, proparallel, (SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) AS lanname FROM pg_catalog.pg_proc WHERE oid = '42960'::pg_catalog.oid
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT description, classoid, objoid, objsubid FROM pg_catalog.pg_description ORDER BY classoid, objoid, objsubid
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: SELECT label, provider, classoid, objoid, objsubid FROM pg_catalog.pg_seclabel ORDER BY classoid, objoid, objsubid
# 5.用copy 的方式导出
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: COPY public.asher (id, name) TO stdout;
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: statement: COPY public.test (i) TO stdout;
2022-04-19 08:47:38 HKT [26559]: db=test,user=postgres,app=pg_dump,client=[local] LOG: disconnection: session time: 0:00:00.572 user=postgres database=test host=[local]
4. 流程图
流程图根据以下连接整理：https://cloud.tencent.com/developer/article/1734520
