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,351
  • 1
  • 10
  • 36
  • 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

1 Answers1

0

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

Sanket Mehta
  • 1
  • 1