I have a Postgres database A with 2 tables, and I need to copy part of these tables (recent records in 1 month) to the new database with the same structures.

How to achieve this, as I know cross database references are not implemented. So I can't select records from the old database to copy to the new one.

I can code, so any ideas are welcome.


Some ideas:

  • You can use COPY (SELECT ...) TO ... to write the relevant parts of the tables to a file on the database server and use COPY ... FROM on the destination database to load the data.

    If the databases are not on the same machine, you could use NFS or similar for a shared directory, or you can use COPY ... TO PROGRAM ... to pipe the result into scp.

  • You can use a postgres_fdw foreign table and just use

    INSERT INTO foreign_tab
       SELECT ... FROM localtab;

    to transfer the data.

| improve this answer | |
  • I will try it tomorrow and feedback here. Thank you. – Tiana987642 Jun 5 '19 at 19:01

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.