Since this commit, pg_dump CREATEs tables and then ATTACHes them: |commit 33a53130a89447e171a8268ae0b221bb48af6468 |Author: Alvaro Herrera <alvhe...@alvh.no-ip.org> |Date: Mon Jun 10 18:56:23 2019 -0400 | | Make pg_dump emit ATTACH PARTITION instead of PARTITION OF (reprise) |... | This change also has the advantage that the partition is restorable from | the dump (as a standalone table) even if its parent table isn't | restored.
I like the idea of child tables being independently restorable, but it doesn't seem to work. |psql postgres -c 'DROP TABLE IF EXISTS t' -c 'CREATE TABLE t(i int) PARTITION BY RANGE(i)' -c 'CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1)TO(2)' |pg_dump postgres -Fc -t t1 >dump.t1 |psql postgres -c 'DROP TABLE t' |pg_restore -d postgres ./dump.t1 |pg_restore: while PROCESSING TOC: |pg_restore: from TOC entry 457; 1259 405311409 TABLE t1 pryzbyj |pg_restore: error: could not execute query: ERROR: relation "public.t" does not exist |Command was: CREATE TABLE public.t1 ( | i integer |); |ALTER TABLE ONLY public.t ATTACH PARTITION public.t1 FOR VALUES FROM (1) TO (2); | |pg_restore: error: could not execute query: ERROR: relation "public.t1" does not exist |Command was: ALTER TABLE public.t1 OWNER TO pryzbyj; | |pg_restore: from TOC entry 4728; 0 405311409 TABLE DATA t1 pryzbyj |pg_restore: error: could not execute query: ERROR: relation "public.t1" does not exist |Command was: COPY public.t1 (i) FROM stdin; |pg_restore: warning: errors ignored on restore: 3 Now that I look, it seems like this is calling PQexec(), which sends a single, "simple" libpq message with: |CREATE TABLE ..; ALTER TABLE .. ATTACH PARTITION; ..which is transactional, so when the 2nd command fails, the CREATE is rolled back. https://www.postgresql.org/docs/9.5/libpq-exec.html#LIBPQ-EXEC-MAIN Telsasoft does a lot of dynamic DDL, so this happens sometimes due to columns added or promoted. Up to now, when this has come up, I've run: pg_restore |grep -v 'ATTACH PARTITION' |psql. Am I missing something ? The idea of being independently restorable maybe originated with Tom's comment here: https://www.postgresql.org/message-id/30049.1555537881%40sss.pgh.pa.us -- Justin
