search

risingwavelabs / risingwave

Best-in-class stream processing, analytics, and management. Perform continuous analytics, or build event-driven applications, real-time ETL pipelines, and feature stores in minutes. Unified streaming and batch. PostgreSQL compatible.
https://go.risingwave.com/slack
Apache License 2.0
7.05k stars 580 forks source link

provide a tool to export database schema like `pg_dump` #15480

Open BugenZhao opened 8 months ago

BugenZhao commented 8 months ago

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 commented 7 months ago

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

neverchanje commented 6 months ago

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 commented 6 months ago 
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 commented 6 months ago

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.

github-actions[bot] commented 3 months ago

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