I have a range (day wise) partitioned table managed using pg_partman extension and replica of the same is created in a different database.
I want to copy a partition specific of that day into another database automatically on a daily basis. What would be the ideal way to approach it?
I am evaluating below approaches,
- Using pg_dump to copy a specific partition
- Copy partition using dblink extension
- By running a daily cron job to copy all the rows which are yet to be copied for a specific day using dblink
I feel second option is good enough but I have to provide a specific partition name that I want to copy which I found a bit difficult to derive and append it into a dblink query.
pg_dump/pg_restoreto be faster than copying through dblink. Another option is a foreign table in the target server.