Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Transfer data between databases with PostgreSQL

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 

How do I copy the data from old database to the new database?

like image 290
Nicopuri Avatar asked Jun 15 '10 23:06

Nicopuri


People also ask

What is data migration in PostgreSQL?

PostgreSQL database migration is the process of moving data from a source database to a target one. Benefit from quick and seamless Postgres data migration and get a simple method to transfer your PostgreSQL data between different servers, databases, and IDEs.

Is PostgreSQL an ETL tool?

The Stitch PostgreSQL integration is an ETL that copies data from PostgreSQL databases to other PostgreSQL data warehouses. Stitch can also extract data from many sources, including MySQL and MongoDB, and then load it to PostgreSQL.

How do I start PostgreSQL migration?

Migrate a PostgreSQL DatabaseFrom the database's Overview page, click the Actions button and then select Set Up Migration. In the PostgreSQL migration window, click Continue, then enter the source database's credentials. Once you have entered the source database's credentials, click Start Migration.


2 Answers

I just had to do this exact thing so I figured I'd post the recipe here. This assumes that both databases are on the same server.

First, copy the table from the old db to the new db. At the commandline:

pg_dump -U postgres -t <old_table> <old_database> | psql -U postgres -d <new_database> 

Next, grant permissions of the copied table to the user of the new database. Log into psql:

psql -U postgres -d <new_database>  ALTER TABLE <old_table> OWNER TO <new_user>;  \q 

At this point your copied table in your new database still has the name <old_table> from your old database. Assuming you want to move the data somewhere else, say to <new_table>, you can just use regular SQL queries:

INSERT INTO <new_table> (field1, field2, field3)  SELECT field1, field2, field3 from <old_table>; 

Done!

like image 77
Nate Avatar answered Oct 05 '22 08:10

Nate


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 (loosely) "databases" are "schemas" in PostgreSQL - 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 transferring data from one to another would be to export your tables (with pg_dump -t ) to a file, and import them into the other database (with psql).

If you really need to get data from a distinct PostgreSQL database, another option - mentioned in Grant Johnson's answer - is dblink, which is an additional module (in contrib/).

Update:

Postgres introduced "foreign data wrapper" in 9.1 (which was released after the question was asked). Foreign data wrappers allow the creation of foreign tables through the Postgres FDW which makes it possible to access a remote table (on a different server and database) as if it was a local table.

like image 45
leonbloy Avatar answered Oct 05 '22 10:10

leonbloy