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,

  1. Using pg_dump to copy a specific partition
  2. Copy partition using dblink extension
  3. 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.

  • I would expect pg_dump/pg_restore to be faster than copying through dblink. Another option is a foreign table in the target server. Feb 3 at 10:48


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy