Copy table with partitions from one database to another in Postgres

Question

This thread here shows how to copy a table from one database to another, where a similar table already exists:

pg_dump -a -t my_table my_db | psql target_db

However, this pg_dump only works on either non-partitioned table or a partition itself. I have many partitions in the two databases:

table_1_part_1
table_1_part_2
...
table_1_part_n
...
table_k_part_m

So, using pg_dump, I have to do that for each partition.

What is a good solution for efficient copying in this case? Assuming that all partitions exist in both databases. If there is SQL query, I can use it in a Python script.

If you can use the dblink module and call a stored procedure from your Python script, then you can create a plpgsql procedure with a loop see the manual. — Edouard
– Edouard, Commented Dec 19, 2021 at 10:40

Sanket Mehta · Accepted Answer · 2023-02-22 15:27:41Z

1

I came across this same need to copy a partitioned table from one postgres server to another (essentially I had to restore a dropped table)

As mentioned by @Edouard, dblink does help to get this done. I came across the same suggestion in an answer here contributed by @tinychen & @SebaGra.

Here is how the query will look:

insert into destination_db.table 
select * from
dblink('host=host_adress dbname=db_name user=user_name
password=password', 'select * from source_db.table') as t1(column1
datatype, column2 datatype, ....columnN datatype)

In my requirement there were close to 100 partitions with huge data, and hence the server request timed out after 2 hours & had to do it partition by partition.

But, I'm sure it can handle moderate volume easily.

If it was helpful, do consider giving a thumbs up!

Thanks, Sanket

edited Feb 22, 2023 at 15:27

answered Feb 15, 2023 at 5:49

Sanket Mehta

112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Falco Preiseni · Accepted Answer · 2024-03-26 14:46:15Z

1

pg_dump for PostgreSQL 16 seems to support this by using the option --table-and-children=pattern instead of -t:

This is the same as the -t/--table option, except that it also includes any partitions or inheritance child tables of the table(s) matching the pattern.

pg_dump -U postgres -h HOST --table-and-children=schema.table DB_NAME > OUTPUT_FILE

answered Mar 26, 2024 at 14:46

Falco Preiseni

4965 silver badges14 bronze badges

Collectives™ on Stack Overflow

Copy table with partitions from one database to another in Postgres

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related