0

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.

Tristan Tran
  • 1,129
  • 4
  • 14
  • If you can use the [dblink module](https://www.postgresql.org/docs/current/dblink.html) and call a stored procedure from your Python script, then you can create a plpgsql procedure with a loop see the [manual](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS). – Edouard Dec 19 '21 at 10:40

0 Answers0