Copy results from a PostgreSQL view in one DB to a table in another

Question

Complete PostgreSQL newb.

I have 7-8 views worth of data in db1 that I need to copy into tables with a matching schemae (schemas?) in a different database, db2. The destination database may be the same instance of PostgreSQL, or one on a different box altogether.

I know 2-3 different ways to accomplish this with the databases I'm familiar with, but I'm helpless on this one. Can someone suggest some basic strategies for me?

In a perfect world, I'd prefer not to have to do anything that feels too ETL-ish - I'd rather do some sort of

SELECT FROM instance1.db1.viewname INTO instance2.db5.tablename

then dump data out of the view as text file and reload into the destination table.

Since I don't know PostgreSQL, I don't really know what is within the realm of possibility, though.

Answer 1

Any query can be the source for COPY TO since Postgres 8.2.

COPY (SELECT * FROM view1) TO '/var/lib/postgres/myfile1.csv';

To also quickly copy the table schema, create the needed table(s) locally with:

CREATE table tbl1 AS
SELECT * FROM view1
LIMIT 0;   -- no data, just the schema.

Then copy the (reverse engineered) DDL instructions and create all tables in the target db. pgAdmin is one convenient GUI for that. Delete the empty table(s) in the source DB afterwards. Load data with:

COPY tbl1 FROM '/var/lib/postgres/myfile1.csv';

If you lack necessary privileges or don't have access to the server file system, consider \copy in psql instead. See:

• Export specific rows from a PostgreSQL table as INSERT SQL script
• How to update selected rows with values from a CSV file in Postgres?

Dump / restore (like @wildplasser describes it) is another way.

For a one time transfer one of those methods is advisable. For repeated application, dblink or SQL/MED (Management of External Data) may be more suitable.
Or consider replication.