How to Get Partition Table DDL

[adnanhamdussalam]

Hi,

I can get the table DDL using below command but unable to see the create partition DDL in it.
Need your support in this regard.

pg_dump -d gpafiniti -t test.table1 --schema-only > 123.sql

OutPut:

CREATE TABLE test.table1 (
combinedbtn character varying(200) NOT NULL,
startdate date NOT NULL,

)
PARTITION BY RANGE (startdate) DISTRIBUTED BY (combinedbtn);

[avamingli]

This is expected behavior in the current version. pg_dump -t matches only the exact table name — it does not automatically include child partitions. This is consistent with upstream PostgreSQL 14 behavior.
As a workaround, explicitly include the child partitions:

pg_dump -t table1 -t 'table1_*' --schema-only

Note that PostgreSQL 17 introduced --table-and-children to address exactly this use case. This could be something worth backporting to Cloudberry.

[avamingli]

CREATE TABLE table1_2024 PARTITION OF table1
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

create a child to verify.

pg_dump -t table1 -t 'table1_*' --schema-only
pg_dump: warning: unsupport am pax yet, current relation "t1" will be ignore

--
-- Name: table1; Type: TABLE; Schema: public; Owner: gpadmin
--

CREATE TABLE public.table1 (
    combinedbtn character varying(200) NOT NULL,
    startdate date NOT NULL
)
PARTITION BY RANGE (startdate) DISTRIBUTED BY (combinedbtn);


ALTER TABLE public.table1 OWNER TO gpadmin;

SET default_table_access_method = heap;

--
-- Name: table1_2024; Type: TABLE; Schema: public; Owner: gpadmin
--

CREATE TABLE public.table1_2024 (
    combinedbtn character varying(200) NOT NULL,
    startdate date NOT NULL
) DISTRIBUTED BY (combinedbtn);


ALTER TABLE public.table1_2024 OWNER TO gpadmin;

--
-- Name: table1_2024; Type: TABLE ATTACH; Schema: public; Owner: gpadmin
--

ALTER TABLE ONLY public.table1 ATTACH PARTITION public.table1_2024 FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');


--
-- Apache Cloudberry database dump complete
--

[avamingli]

Note that PostgreSQL 17 introduced --table-and-children to address exactly this use case. This could be something worth backporting to Cloudberry.

From PG16: https://www.postgresql.org/docs/16/app-pgdump.html