4

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Russell Christopher
  • 1,677
  • 3
  • 20
  • 36
  • Thanks to you both. dblink_connect appears to be exactly what I need. However, it looks like the function isn't installed automatically in the distro I've just dropped on my box. Is there some sort of command I need to fire from inside pgAdmin to get it installed? – Russell Christopher Nov 06 '11 at 13:15
  • Never mind. Found that it was an Extension that I could install against the database in question using pgAdmin. I'm up and running. – Russell Christopher Nov 06 '11 at 13:44

2 Answers2

6

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:

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    Oops. my bad. But copying a view FROM file is still impossible :-) [which is actually a pity, since the rule system allows the construction of updatable views] – wildplasser Nov 06 '11 at 10:53
3
CREATE TEMPORARY TABLE mytmp
AS SELECT * from myview
WHERE 1=1
;


COPY mytmp TO '/tmp/test.csv'
;

An even better method is to:

  • copy the views into tables (create table1 as select * from view1; ...)
  • use pg_dump -t table1 -t table2 ... mydbname >myfile.out
  • use myfile.out to recreate and fill the tables.
wildplasser
  • 43,142
  • 8
  • 66
  • 109
  • The reason for this extra copying is that views cannot be used as source or target for COPY statements. The create table AS takes care of (most of) the datatypes. Constraints will not be copied to the new table. – wildplasser Nov 05 '11 at 18:19