Postgresql – Copy result of a query to a new PostgreSQL database

bulkcopypostgresql

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.

Best Answer

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.

Related Question