provide a tool to export database schema like pg_dump

[BugenZhao]

With PostgreSQL, pg_dump is a commonly-used tool to backup the database. Apart from backing up data, it can also help to export the schema of the database.

For example,

pg_dump --schema-only -d postgres > db.sql

will execute a series of statements in the Postgres server as the following...

Full statements

2024-03-06 13:35:08.884 CST [26701] LOG:  statement: SELECT pg_catalog.set_config('search_path', '', false);
2024-03-06 13:35:08.887 CST [26701] LOG:  statement: SELECT pg_catalog.pg_is_in_recovery()
2024-03-06 13:35:08.888 CST [26701] LOG:  statement: SELECT pg_catalog.set_config('search_path', '', false);
2024-03-06 13:35:08.888 CST [26701] LOG:  statement: SET DATESTYLE = ISO
2024-03-06 13:35:08.888 CST [26701] LOG:  statement: SET INTERVALSTYLE = POSTGRES
2024-03-06 13:35:08.888 CST [26701] LOG:  statement: SET extra_float_digits TO 3
2024-03-06 13:35:08.888 CST [26701] LOG:  statement: SET synchronize_seqscans TO off
2024-03-06 13:35:08.888 CST [26701] LOG:  statement: SET statement_timeout = 0
2024-03-06 13:35:08.888 CST [26701] LOG:  statement: SET lock_timeout = 0
2024-03-06 13:35:08.888 CST [26701] LOG:  statement: SET idle_in_transaction_session_timeout = 0
2024-03-06 13:35:08.889 CST [26701] LOG:  statement: SET row_security = off
2024-03-06 13:35:08.889 CST [26701] LOG:  statement: BEGIN
2024-03-06 13:35:08.889 CST [26701] LOG:  statement: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY
2024-03-06 13:35:08.889 CST [26701] LOG:  statement: SELECT oid, rolname FROM pg_catalog.pg_roles ORDER BY 1
2024-03-06 13:35:08.901 CST [26701] 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
2024-03-06 13:35:08.905 CST [26701] LOG:  statement: SELECT classid, objid, refobjid FROM pg_depend WHERE refclassid = 'pg_extension'::regclass AND deptype = 'e' ORDER BY 3
2024-03-06 13:35:08.912 CST [26701] LOG:  statement: SELECT n.tableoid, n.oid, n.nspname, n.nspowner, n.nspacl, acldefault('n', n.nspowner) AS acldefault FROM pg_namespace n
2024-03-06 13:35:08.912 CST [26701] LOG:  statement: SELECT c.tableoid, c.oid, c.relname, c.relnamespace, c.relkind, c.reltype, c.relowner, c.relchecks, c.relhasindex, c.relhasrules, c.relpages, c.relhastriggers, c.relpersistence, c.reloftype, c.relacl, acldefault(CASE WHEN c.relkind = 'S' THEN 's'::"char" ELSE 'r'::"char" END, c.relowner) AS acldefault, CASE WHEN c.relkind = 'f' THEN (SELECT ftserver FROM pg_catalog.pg_foreign_table WHERE ftrelid = c.oid) ELSE 0 END AS foreignserver, c.relfrozenxid, tc.relfrozenxid AS tfrozenxid, tc.oid AS toid, tc.relpages AS toastpages, tc.reloptions AS toast_reloptions, d.refobjid AS owning_tab, d.refobjsubid AS owning_col, tsp.spcname AS reltablespace, false AS relhasoids, c.relispopulated, c.relreplident, c.relrowsecurity, c.relforcerowsecurity, c.relminmxid, tc.relminmxid AS tminmxid, 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, am.amname, (d.deptype = 'i') IS TRUE AS is_identity_sequence, c.relispartition AS ispartition 
	FROM pg_class c
	LEFT JOIN pg_depend d ON (c.relkind = 'S' AND d.classid = 'pg_class'::regclass AND d.objid = c.oid AND d.objsubid = 0 AND d.refclassid = 'pg_class'::regclass AND d.deptype IN ('a', 'i'))
	LEFT JOIN pg_tablespace tsp ON (tsp.oid = c.reltablespace)
	LEFT JOIN pg_am am ON (c.relam = am.oid)
	LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid AND tc.relkind = 't' AND c.relkind <> 'p')
	WHERE c.relkind IN ('r', 'S', 'v', 'c', 'm', 'f', 'p')
	ORDER BY c.oid
2024-03-06 13:35:08.925 CST [26701] LOG:  statement: LOCK TABLE public.my_secret_table IN ACCESS SHARE MODE
2024-03-06 13:35:08.927 CST [26701] LOG:  statement: SELECT p.tableoid, p.oid, p.proname, p.prolang, p.pronargs, p.proargtypes, p.prorettype, p.proacl, acldefault('f', p.proowner) AS acldefault, p.pronamespace, p.proowner 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)
2024-03-06 13:35:08.942 CST [26701] LOG:  statement: SELECT tableoid, oid, typname, typnamespace, typacl, acldefault('T', typowner) AS acldefault, typowner, typelem, typrelid, CASE WHEN typrelid = 0 THEN ' '::"char" ELSE (SELECT relkind FROM pg_class WHERE oid = typrelid) END AS typrelkind, typtype, typisdefined, typname[0] = '_' AND typelem != 0 AND (SELECT typarray FROM pg_type te WHERE oid = pg_type.typelem) = oid AS isarray FROM pg_type
2024-03-06 13:35:08.948 CST [26701] LOG:  statement: SELECT tableoid, oid, lanname, lanpltrusted, lanplcallfoid, laninline, lanvalidator, lanacl, acldefault('l', lanowner) AS acldefault, lanowner FROM pg_language WHERE lanispl ORDER BY oid
2024-03-06 13:35:08.949 CST [26701] LOG:  statement: SELECT p.tableoid, p.oid, p.proname AS aggname, p.pronamespace AS aggnamespace, p.pronargs, p.proargtypes, p.proowner, p.proacl AS aggacl, acldefault('f', p.proowner) AS acldefault 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)
2024-03-06 13:35:08.951 CST [26701] LOG:  statement: SELECT tableoid, oid, oprname, oprnamespace, oprowner, oprkind, oprcode::oid AS oprcode FROM pg_operator
2024-03-06 13:35:08.952 CST [26701] LOG:  statement: SELECT tableoid, oid, amname, amtype, amhandler::pg_catalog.regproc AS amhandler FROM pg_am
2024-03-06 13:35:08.954 CST [26701] LOG:  statement: SELECT tableoid, oid, opcname, opcnamespace, opcowner FROM pg_opclass
2024-03-06 13:35:08.955 CST [26701] LOG:  statement: SELECT tableoid, oid, opfname, opfnamespace, opfowner FROM pg_opfamily
2024-03-06 13:35:08.956 CST [26701] LOG:  statement: SELECT tableoid, oid, prsname, prsnamespace, prsstart::oid, prstoken::oid, prsend::oid, prsheadline::oid, prslextype::oid FROM pg_ts_parser
2024-03-06 13:35:08.957 CST [26701] LOG:  statement: SELECT tableoid, oid, tmplname, tmplnamespace, tmplinit::oid, tmpllexize::oid FROM pg_ts_template
2024-03-06 13:35:08.959 CST [26701] LOG:  statement: SELECT tableoid, oid, dictname, dictnamespace, dictowner, dicttemplate, dictinitoption FROM pg_ts_dict
2024-03-06 13:35:08.960 CST [26701] LOG:  statement: SELECT tableoid, oid, cfgname, cfgnamespace, cfgowner, cfgparser FROM pg_ts_config
2024-03-06 13:35:08.961 CST [26701] LOG:  statement: SELECT tableoid, oid, fdwname, fdwowner, fdwhandler::pg_catalog.regproc, fdwvalidator::pg_catalog.regproc, fdwacl, acldefault('F', fdwowner) AS acldefault, array_to_string(ARRAY(SELECT quote_ident(option_name) || ' ' || quote_literal(option_value) FROM pg_options_to_table(fdwoptions) ORDER BY option_name), E',
	    ') AS fdwoptions FROM pg_foreign_data_wrapper
2024-03-06 13:35:08.963 CST [26701] LOG:  statement: SELECT tableoid, oid, srvname, srvowner, srvfdw, srvtype, srvversion, srvacl, acldefault('S', srvowner) AS acldefault, array_to_string(ARRAY(SELECT quote_ident(option_name) || ' ' || quote_literal(option_value) FROM pg_options_to_table(srvoptions) ORDER BY option_name), E',
	    ') AS srvoptions FROM pg_foreign_server
2024-03-06 13:35:08.964 CST [26701] LOG:  statement: SELECT oid, tableoid, defaclrole, defaclnamespace, defaclobjtype, defaclacl, CASE WHEN defaclnamespace = 0 THEN acldefault(CASE WHEN defaclobjtype = 'S' THEN 's'::"char" ELSE defaclobjtype END, defaclrole) ELSE '{}' END AS acldefault FROM pg_default_acl
2024-03-06 13:35:08.965 CST [26701] LOG:  statement: SELECT tableoid, oid, collname, collnamespace, collowner FROM pg_collation
2024-03-06 13:35:08.968 CST [26701] LOG:  statement: SELECT tableoid, oid, conname, connamespace, conowner FROM pg_conversion
2024-03-06 13:35:08.971 CST [26701] LOG:  statement: SELECT tableoid, oid, castsource, casttarget, castfunc, castcontext, castmethod FROM pg_cast c WHERE NOT EXISTS ( SELECT 1 FROM pg_range r WHERE c.castsource = r.rngtypid AND c.casttarget = r.rngmultitypid ) ORDER BY 3,4
2024-03-06 13:35:08.973 CST [26701] LOG:  statement: SELECT tableoid, oid, trftype, trflang, trffromsql::oid, trftosql::oid FROM pg_transform ORDER BY 3,4
2024-03-06 13:35:08.973 CST [26701] LOG:  statement: SELECT inhrelid, inhparent FROM pg_inherits
2024-03-06 13:35:08.974 CST [26701] LOG:  statement: SELECT e.tableoid, e.oid, evtname, evtenabled, evtevent, 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
2024-03-06 13:35:08.975 CST [26701] 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;
2024-03-06 13:35:08.977 CST [26701] LOG:  statement: SELECT
	a.attrelid,
	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,
	array_to_string(a.attoptions, ', ') AS attoptions,
	CASE WHEN a.attcollation <> t.typcollation THEN a.attcollation ELSE 0 END AS attcollation,
	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,
	a.attcompression AS attcompression,
	a.attidentity,
	CASE WHEN a.atthasmissing AND NOT a.attisdropped THEN a.attmissingval ELSE null END AS attmissingval,
	a.attgenerated
	FROM unnest('{16400,24584,32791,32794,32799,32802,40986,49196,49215,57370,57548,57553,57561,57572,57582}'::pg_catalog.oid[]) AS src(tbloid)
	JOIN pg_catalog.pg_attribute a ON (src.tbloid = a.attrelid) LEFT JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid)
	WHERE a.attnum > 0::pg_catalog.int2
	ORDER BY a.attrelid, a.attnum
2024-03-06 13:35:08.981 CST [26701] LOG:  statement: SELECT a.tableoid, a.oid, adrelid, adnum, pg_catalog.pg_get_expr(adbin, adrelid) AS adsrc
	FROM unnest('{16400,57548,57553}'::pg_catalog.oid[]) AS src(tbloid)
	JOIN pg_catalog.pg_attrdef a ON (src.tbloid = a.adrelid)
	ORDER BY a.adrelid, a.adnum
2024-03-06 13:35:08.984 CST [26701] LOG:  statement: SELECT t.tableoid, t.oid, i.indrelid, t.relname AS indexname, pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, i.indkey, i.indisclustered, 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, i.indisreplident, inh.inhparent AS parentidx, i.indnkeyatts AS indnkeyatts, i.indnatts AS indnatts, (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, i.indnullsnotdistinct FROM unnest('{49215,57553,57561,57572}'::pg_catalog.oid[]) AS src(tbloid)
	JOIN pg_catalog.pg_index i ON (src.tbloid = i.indrelid) 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.indisvalid OR t2.relkind = 'p') AND i.indisready ORDER BY i.indrelid, indexname
2024-03-06 13:35:08.987 CST [26701] LOG:  statement: SELECT tableoid, oid, stxname, stxnamespace, stxowner, stxrelid, stxstattarget FROM pg_catalog.pg_statistic_ext
2024-03-06 13:35:08.988 CST [26701] LOG:  statement: SELECT c.tableoid, c.oid, conrelid, conname, confrelid, conindid, pg_catalog.pg_get_constraintdef(c.oid) AS condef
	FROM unnest('{32791}'::pg_catalog.oid[]) AS src(tbloid)
	JOIN pg_catalog.pg_constraint c ON (src.tbloid = c.conrelid)
	WHERE contype = 'f' AND conparentid = 0 ORDER BY conrelid, conname
2024-03-06 13:35:08.988 CST [26701] LOG:  statement: SELECT t.tgrelid, t.tgname, t.tgfoid::pg_catalog.regproc AS tgfname, pg_catalog.pg_get_triggerdef(t.oid, false) AS tgdef, t.tgenabled, t.tableoid, t.oid, t.tgparentid <> 0 AS tgispartition
	FROM unnest('{}'::pg_catalog.oid[]) AS src(tbloid)
	JOIN pg_catalog.pg_trigger t ON (src.tbloid = t.tgrelid) LEFT JOIN pg_catalog.pg_trigger u ON (u.oid = t.tgparentid) WHERE ((NOT t.tgisinternal AND t.tgparentid = 0) OR t.tgenabled != u.tgenabled) ORDER BY t.tgrelid, t.tgname
2024-03-06 13:35:08.989 CST [26701] LOG:  statement: SELECT tableoid, oid, rulename, ev_class AS ruletable, ev_type, is_instead, ev_enabled FROM pg_rewrite ORDER BY oid
2024-03-06 13:35:08.991 CST [26701] LOG:  statement: SELECT pol.oid, pol.tableoid, pol.polrelid, 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 unnest('{16400,24584,32791,32794,32799,32802,40986,49196,49215,57370,57548,57553,57561,57572,57582}'::pg_catalog.oid[]) AS src(tbloid)
	JOIN pg_catalog.pg_policy pol ON (src.tbloid = pol.polrelid)
2024-03-06 13:35:08.992 CST [26701] LOG:  statement: SELECT p.tableoid, p.oid, p.pubname, p.pubowner, p.puballtables, p.pubinsert, p.pubupdate, p.pubdelete, p.pubtruncate, p.pubviaroot FROM pg_publication p
2024-03-06 13:35:08.993 CST [26701] LOG:  statement: SELECT tableoid, oid, prpubid, prrelid, pg_catalog.pg_get_expr(prqual, prrelid) AS prrelqual, (CASE
	  WHEN pr.prattrs IS NOT NULL THEN
	    (SELECT array_agg(attname)
	       FROM
	         pg_catalog.generate_series(0, pg_catalog.array_upper(pr.prattrs::pg_catalog.int2[], 1)) s,
	         pg_catalog.pg_attribute
	      WHERE attrelid = pr.prrelid AND attnum = prattrs[s])
	  ELSE NULL END) prattrs FROM pg_catalog.pg_publication_rel pr
2024-03-06 13:35:08.995 CST [26701] LOG:  statement: SELECT tableoid, oid, pnpubid, pnnspid FROM pg_catalog.pg_publication_namespace
2024-03-06 13:35:08.996 CST [26701] LOG:  statement: SELECT s.tableoid, s.oid, s.subname,
	 s.subowner,
	 s.subconninfo, s.subslotname, s.subsynccommit,
	 s.subpublications,
	 s.subbinary,
	 s.substream,
	 s.subtwophasestate,
	 s.subdisableonerr
	FROM pg_subscription s
	WHERE s.subdbid = (SELECT oid FROM pg_database
	                   WHERE datname = current_database())
2024-03-06 13:35:08.997 CST [26701] 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
2024-03-06 13:35:09.002 CST [26701] LOG:  statement: SELECT DISTINCT attrelid FROM pg_attribute WHERE attacl IS NOT NULL
2024-03-06 13:35:09.003 CST [26701] LOG:  statement: SELECT objoid, classoid, objsubid, privtype, initprivs FROM pg_init_privs
2024-03-06 13:35:09.004 CST [26701] LOG:  statement: SELECT description, classoid, objoid, objsubid FROM pg_catalog.pg_description ORDER BY classoid, objoid, objsubid
2024-03-06 13:35:09.027 CST [26701] LOG:  statement: SELECT label, provider, classoid, objoid, objsubid FROM pg_catalog.pg_seclabel ORDER BY classoid, objoid, objsubid
2024-03-06 13:35:09.029 CST [26701] LOG:  statement: SELECT pg_catalog.current_schemas(false)
2024-03-06 13:35:09.029 CST [26701] LOG:  statement: PREPARE dumpCompositeType(pg_catalog.oid) AS
	SELECT a.attname, a.attnum, pg_catalog.format_type(a.atttypid, a.atttypmod) AS atttypdefn, a.attlen, a.attalign, a.attisdropped, CASE WHEN a.attcollation <> at.typcollation THEN a.attcollation ELSE 0 END AS attcollation FROM pg_catalog.pg_type ct JOIN pg_catalog.pg_attribute a ON a.attrelid = ct.typrelid LEFT JOIN pg_catalog.pg_type at ON at.oid = a.atttypid WHERE ct.oid = $1 ORDER BY a.attnum
2024-03-06 13:35:09.029 CST [26701] LOG:  statement: EXECUTE dumpCompositeType('57581')
2024-03-06 13:35:09.029 CST [26701] DETAIL:  prepare: PREPARE dumpCompositeType(pg_catalog.oid) AS
	SELECT a.attname, a.attnum, pg_catalog.format_type(a.atttypid, a.atttypmod) AS atttypdefn, a.attlen, a.attalign, a.attisdropped, CASE WHEN a.attcollation <> at.typcollation THEN a.attcollation ELSE 0 END AS attcollation FROM pg_catalog.pg_type ct JOIN pg_catalog.pg_attribute a ON a.attrelid = ct.typrelid LEFT JOIN pg_catalog.pg_type at ON at.oid = a.atttypid WHERE ct.oid = $1 ORDER BY a.attnum
2024-03-06 13:35:09.030 CST [26701] LOG:  statement: EXECUTE dumpCompositeType('57589')
2024-03-06 13:35:09.030 CST [26701] DETAIL:  prepare: PREPARE dumpCompositeType(pg_catalog.oid) AS
	SELECT a.attname, a.attnum, pg_catalog.format_type(a.atttypid, a.atttypmod) AS atttypdefn, a.attlen, a.attalign, a.attisdropped, CASE WHEN a.attcollation <> at.typcollation THEN a.attcollation ELSE 0 END AS attcollation FROM pg_catalog.pg_type ct JOIN pg_catalog.pg_attribute a ON a.attrelid = ct.typrelid LEFT JOIN pg_catalog.pg_type at ON at.oid = a.atttypid WHERE ct.oid = $1 ORDER BY a.attnum
2024-03-06 13:35:09.031 CST [26701] LOG:  statement: EXECUTE dumpCompositeType('32774')
2024-03-06 13:35:09.031 CST [26701] DETAIL:  prepare: PREPARE dumpCompositeType(pg_catalog.oid) AS
	SELECT a.attname, a.attnum, pg_catalog.format_type(a.atttypid, a.atttypmod) AS atttypdefn, a.attlen, a.attalign, a.attisdropped, CASE WHEN a.attcollation <> at.typcollation THEN a.attcollation ELSE 0 END AS attcollation FROM pg_catalog.pg_type ct JOIN pg_catalog.pg_attribute a ON a.attrelid = ct.typrelid LEFT JOIN pg_catalog.pg_type at ON at.oid = a.atttypid WHERE ct.oid = $1 ORDER BY a.attnum
2024-03-06 13:35:09.031 CST [26701] LOG:  statement: EXECUTE dumpCompositeType('32777')
2024-03-06 13:35:09.031 CST [26701] DETAIL:  prepare: PREPARE dumpCompositeType(pg_catalog.oid) AS
	SELECT a.attname, a.attnum, pg_catalog.format_type(a.atttypid, a.atttypmod) AS atttypdefn, a.attlen, a.attalign, a.attisdropped, CASE WHEN a.attcollation <> at.typcollation THEN a.attcollation ELSE 0 END AS attcollation FROM pg_catalog.pg_type ct JOIN pg_catalog.pg_attribute a ON a.attrelid = ct.typrelid LEFT JOIN pg_catalog.pg_type at ON at.oid = a.atttypid WHERE ct.oid = $1 ORDER BY a.attnum
2024-03-06 13:35:09.032 CST [26701] LOG:  statement: PREPARE dumpFunc(pg_catalog.oid) AS
	SELECT
	proretset,
	prosrc,
	probin,
	provolatile,
	proisstrict,
	prosecdef,
	lanname,
	proconfig,
	procost,
	prorows,
	pg_catalog.pg_get_function_arguments(p.oid) AS funcargs,
	pg_catalog.pg_get_function_identity_arguments(p.oid) AS funciargs,
	pg_catalog.pg_get_function_result(p.oid) AS funcresult,
	proleakproof,
	array_to_string(protrftypes, ' ') AS protrftypes,
	proparallel,
	prokind,
	prosupport,
	pg_get_function_sqlbody(p.oid) AS prosqlbody
	FROM pg_catalog.pg_proc p, pg_catalog.pg_language l
	WHERE p.oid = $1 AND l.oid = p.prolang
2024-03-06 13:35:09.032 CST [26701] LOG:  statement: EXECUTE dumpFunc('57590')
2024-03-06 13:35:09.032 CST [26701] DETAIL:  prepare: PREPARE dumpFunc(pg_catalog.oid) AS
	SELECT
	proretset,
	prosrc,
	probin,
	provolatile,
	proisstrict,
	prosecdef,
	lanname,
	proconfig,
	procost,
	prorows,
	pg_catalog.pg_get_function_arguments(p.oid) AS funcargs,
	pg_catalog.pg_get_function_identity_arguments(p.oid) AS funciargs,
	pg_catalog.pg_get_function_result(p.oid) AS funcresult,
	proleakproof,
	array_to_string(protrftypes, ' ') AS protrftypes,
	proparallel,
	prokind,
	prosupport,
	pg_get_function_sqlbody(p.oid) AS prosqlbody
	FROM pg_catalog.pg_proc p, pg_catalog.pg_language l
	WHERE p.oid = $1 AND l.oid = p.prolang
2024-03-06 13:35:09.032 CST [26701] LOG:  statement: SELECT pg_catalog.format_type('57581'::pg_catalog.oid, NULL)
2024-03-06 13:35:09.032 CST [26701] LOG:  statement: SELECT pg_catalog.format_type('57589'::pg_catalog.oid, NULL)
2024-03-06 13:35:09.032 CST [26701] LOG:  statement: EXECUTE dumpFunc('57534')
2024-03-06 13:35:09.032 CST [26701] DETAIL:  prepare: PREPARE dumpFunc(pg_catalog.oid) AS
	SELECT
	proretset,
	prosrc,
	probin,
	provolatile,
	proisstrict,
	prosecdef,
	lanname,
	proconfig,
	procost,
	prorows,
	pg_catalog.pg_get_function_arguments(p.oid) AS funcargs,
	pg_catalog.pg_get_function_identity_arguments(p.oid) AS funciargs,
	pg_catalog.pg_get_function_result(p.oid) AS funcresult,
	proleakproof,
	array_to_string(protrftypes, ' ') AS protrftypes,
	proparallel,
	prokind,
	prosupport,
	pg_get_function_sqlbody(p.oid) AS prosqlbody
	FROM pg_catalog.pg_proc p, pg_catalog.pg_language l
	WHERE p.oid = $1 AND l.oid = p.prolang
2024-03-06 13:35:09.033 CST [26701] LOG:  statement: SELECT pg_catalog.format_type('23'::pg_catalog.oid, NULL)
2024-03-06 13:35:09.033 CST [26701] LOG:  statement: EXECUTE dumpFunc('57599')
2024-03-06 13:35:09.033 CST [26701] DETAIL:  prepare: PREPARE dumpFunc(pg_catalog.oid) AS
	SELECT
	proretset,
	prosrc,
	probin,
	provolatile,
	proisstrict,
	prosecdef,
	lanname,
	proconfig,
	procost,
	prorows,
	pg_catalog.pg_get_function_arguments(p.oid) AS funcargs,
	pg_catalog.pg_get_function_identity_arguments(p.oid) AS funciargs,
	pg_catalog.pg_get_function_result(p.oid) AS funcresult,
	proleakproof,
	array_to_string(protrftypes, ' ') AS protrftypes,
	proparallel,
	prokind,
	prosupport,
	pg_get_function_sqlbody(p.oid) AS prosqlbody
	FROM pg_catalog.pg_proc p, pg_catalog.pg_language l
	WHERE p.oid = $1 AND l.oid = p.prolang
2024-03-06 13:35:09.033 CST [26701] LOG:  statement: EXECUTE dumpFunc('57578')
2024-03-06 13:35:09.033 CST [26701] DETAIL:  prepare: PREPARE dumpFunc(pg_catalog.oid) AS
	SELECT
	proretset,
	prosrc,
	probin,
	provolatile,
	proisstrict,
	prosecdef,
	lanname,
	proconfig,
	procost,
	prorows,
	pg_catalog.pg_get_function_arguments(p.oid) AS funcargs,
	pg_catalog.pg_get_function_identity_arguments(p.oid) AS funciargs,
	pg_catalog.pg_get_function_result(p.oid) AS funcresult,
	proleakproof,
	array_to_string(protrftypes, ' ') AS protrftypes,
	proparallel,
	prokind,
	prosupport,
	pg_get_function_sqlbody(p.oid) AS prosqlbody
	FROM pg_catalog.pg_proc p, pg_catalog.pg_language l
	WHERE p.oid = $1 AND l.oid = p.prolang
2024-03-06 13:35:09.033 CST [26701] LOG:  statement: PREPARE getColumnACLs(pg_catalog.oid) AS
	SELECT at.attname, at.attacl, '{}' AS acldefault, pip.privtype, pip.initprivs FROM pg_catalog.pg_attribute at LEFT JOIN pg_catalog.pg_init_privs pip ON (at.attrelid = pip.objoid AND pip.classoid = 'pg_catalog.pg_class'::pg_catalog.regclass AND at.attnum = pip.objsubid) WHERE at.attrelid = $1 AND NOT at.attisdropped AND (at.attacl IS NOT NULL OR pip.initprivs IS NOT NULL) ORDER BY at.attnum
2024-03-06 13:35:09.033 CST [26701] LOG:  statement: EXECUTE getColumnACLs('6100')
2024-03-06 13:35:09.033 CST [26701] DETAIL:  prepare: PREPARE getColumnACLs(pg_catalog.oid) AS
	SELECT at.attname, at.attacl, '{}' AS acldefault, pip.privtype, pip.initprivs FROM pg_catalog.pg_attribute at LEFT JOIN pg_catalog.pg_init_privs pip ON (at.attrelid = pip.objoid AND pip.classoid = 'pg_catalog.pg_class'::pg_catalog.regclass AND at.attnum = pip.objsubid) WHERE at.attrelid = $1 AND NOT at.attisdropped AND (at.attacl IS NOT NULL OR pip.initprivs IS NOT NULL) ORDER BY at.attnum
2024-03-06 13:35:09.034 CST [26701] LOG:  statement: SELECT format_type(seqtypid, NULL), seqstart, seqincrement, seqmax, seqmin, seqcache, seqcycle FROM pg_catalog.pg_sequence WHERE seqrelid = '57552'::oid
2024-03-06 13:35:09.035 CST [26701] LOG:  statement: SELECT pg_get_partkeydef('32791')

However, due to the completely different architecture and system designing in RisingWave, pg_dump may not work perfectly with RisingWave. For example, we customize the DDL syntaxes and define new objects like FUNCTION or SOURCE. So we may want to provide a similar tool ourselves to provide such functionalities.

[fuyufjh]

How about offering a risectl command to print show table for all relations?

[neverchanje]

I am not sure if the intention is to build a user-facing interface or just to simplify our internal troubleshootting. If it's the former, I suggest to add a SQL command instead of risectl command:

psql -c 'show tables;'

so that user don't have to download any other tools.

[BugenZhao]

psql -c 'show tables;'

show table is to show the list of table names by convention. Perhaps you meant something like SHOW CREATE TABLES. However, we get many more kinds of relations or objects to dump.

[neverchanje]

These TODO items (non-exhausted list) are too much for us in the shorterm. I would prefer writing a rw_dump command line tool in Golang instead.

• Support a hidden tableoid column for every table:
  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

• Support acldefault('n', n.nspowner)
SELECT n.tableoid, n.oid, n.nspname, n.nspowner, n.nspacl, acldefault('n', n.nspowner) AS acldefault FROM pg_namespace n;

• Support reltoastrelid for table pg_class.

  SELECT c.tableoid, c.oid, c.relname, c.relnamespace, c.relkind, c.reltype, c.relowner, c.relchecks, c.relhasindex, c.relhasrules, c.relpages, c.relhastriggers, c.relpersistence, c.reloftype, c.relacl, acldefault(CASE WHEN c.relkind = 'S' THEN 's'::"char" ELSE 'r'::"char" END, c.relowner) AS acldefault, CASE WHEN c.relkind = 'f' THEN (SELECT ftserver FROM pg_catalog.pg_foreign_table WHERE ftrelid = c.oid) ELSE 0 END AS foreignserver, c.relfrozenxid, tc.relfrozenxid AS tfrozenxid, tc.oid AS toid, tc.relpages AS toastpages, tc.reloptions AS toast_reloptions, d.refobjid AS owning_tab, d.refobjsubid AS owning_col, tsp.spcname AS reltablespace, false AS relhasoids, c.relispopulated, c.relreplident, c.relrowsecurity, c.relforcerowsecurity, c.relminmxid, tc.relminmxid AS tminmxid, 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, am.amname, (d.deptype = 'i') IS TRUE AS is_identity_sequence, c.relispartition AS ispartition 
    FROM pg_class c
    LEFT JOIN pg_depend d ON (c.relkind = 'S' AND d.classid = 'pg_class'::regclass AND d.objid = c.oid AND d.objsubid = 0 AND d.refclassid = 'pg_class'::regclass AND d.deptype IN ('a', 'i'))
    LEFT JOIN pg_tablespace tsp ON (tsp.oid = c.reltablespace)
    LEFT JOIN pg_am am ON (c.relam = am.oid)
    LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid AND tc.relkind = 't' AND c.relkind <> 'p')
    WHERE c.relkind IN ('r', 'S', 'v', 'c', 'm', 'f', 'p')
    ORDER BY c.oid
  

• Support LOCK TABLE public.my_secret_table IN ACCESS SHARE MODE;

• Add the catalog table: pg_init_privs

• Add column pg_cast.castfunc

• Add the catalog table: pg_transform;.

• Add column pg_proc.prolang

  SELECT p.tableoid, p.oid, p.proname, p.prolang, p.pronargs, p.proargtypes, p.prorettype, p.proacl, acldefault('f', p.proowner) AS acldefault, p.pronamespace, p.proowner 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);
  

[github-actions]

This issue has been open for 60 days with no activity.

If you think it is still relevant today, and needs to be done in the near future, you can comment to update the status, or just manually remove the no-issue-activity label.

You can also confidently close this issue as not planned to keep our backlog clean.
Don't worry if you think the issue is still valuable to continue in the future.
It's searchable and can be reopened when it's time. 😄