Transfer Data between databases with postgres

Question

I need to transfer some data from another Database. The old database is called paw1.moviesDB and the new database is paw1. The schema of each table are the following

Awards (name of the table)(new DB) Id [PK] Serial Award

Nominations (name of the table) (old DB) Id [PK] Serial nominations

I want to copy the data from old DB to the new DB.

Answer 1

Databases are isolated in postgresql; when you connect to a postgresql server you connect to just one database, you can't copy data from one database to another using a SQL query.

If you come from Mysql: what Mysql calls (lousily) "databases" are in Postgresql "schemas", sort of namespaces. A Postgresql database can have many schemas, each one with its tables and views, and you can copy from one schema to another with the schema.table syntax.

If you really have two distinct Postgresql databases, the common way of transfering data from one to another would be to export your tables (with pg_dump -t ) to a file, a import them into the other database (with psql)

Answer 2

Just like leonbloy suggested, using two schemas in a database is the way to go. Suppose a source schema (old DB) and a target schema (new DB), you can try something like this (you should consider column names, types, etc.):

INSERT INTO target.Awards SELECT * FROM source.Nominations;

Answer 3

There are three options for copying it if this is a one off:

1. Use a db_link (I think it is still in contrib)
2. Have the application do the work.
3. Export/import

If this is an ongoing need, the answers are:

1. Change to schemas in the same DB
2. db_link