Transfer data between databases with PostgreSQL

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

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

Answer 1

This worked for me to copy a table remotely from my localhost to Heroku's postgresql:

pg_dump -C -t source_table -h localhost source_db | psql -h destination_host -U destination_user -p destination_port destination_db

This creates the table for you.

For the other direction (from Heroku to local) pg_dump -C -t source_table -h source_host -U source_user -p source_port source_db | psql -h localhost destination_db

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

You can not perform a cross-database query like SQL Server; PostgreSQL does not support this.

The DbLink extension of PostgreSQL is used to connect one database to another database. You have install and configure DbLink to execute a cross-database query.

I have already created a step-by-step script and example for executing cross database query in PostgreSQL. Please visit this post: PostgreSQL [Video]: Cross Database Queries using the DbLink Extension

Answer 4

Actually, there is some possibility to send a table data from one PostgreSQL database to another. I use the procedural language plperlu (unsafe Perl procedural language) for it.

Description (all was done on a Linux server):

1. Create plperlu language in your database A

2. Then PostgreSQL can join some Perl modules through series of the following commands at the end of postgresql.conf for the database A:

   plperl.on_init='use DBI;'
   plperl.on_init='use DBD::Pg;'
   

3. You build a function in A like this:

   CREATE OR REPLACE FUNCTION send_data( VARCHAR )
   RETURNS character varying AS
   $BODY$
   my $command = $_[0] || die 'No SQL command!';
   my $connection_string =
   "dbi:Pg:dbname=your_dbase;host=192.168.1.2;port=5432;";
   $dbh = DBI->connect($connection_string,'user','pass',
   {AutoCommit=>0,RaiseError=>1,PrintError=>1,pg_enable_utf8=>1,}
   );
   my $sql = $dbh-> prepare( $command );
   eval { $sql-> execute() };
   my $error = $dbh-> state;
   $sql-> finish;
   if ( $error ) { $dbh-> rollback() } else {  $dbh-> commit() }
   $dbh-> disconnect();
   $BODY$
   LANGUAGE plperlu VOLATILE;
   

And then you can call the function inside database A:

SELECT send_data( 'INSERT INTO jm (jm) VALUES (''zzzzzz'')' );

And the value "zzzzzz" will be added into table "jm" in database B.

Answer 5

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

Answer 6

From: hxxp://dbaspot.c om/postgresql/348627-pg_dump-t-give-where-condition.html (NOTE: the link is now broken)

# create temp table with the data
psql mydb
CREATE TABLE temp1 (LIKE mytable);
INSERT INTO temp1 SELECT * FROM mytable WHERE myconditions;
\q

# export the data to a sql file
pg_dump --data-only --column-inserts -t temp1 mtdb > out.sql
psql mydb
DROP TABLE temp1;
\q

# import temp1 rows in another database
cat out.sql | psql -d [other_db]
psql other_db
INSERT INTO mytable (SELECT * FROM temp1);
DROP TABLE temp1;

Another method useful in remotes

  # export a table csv and import in another database
  psql-remote> COPY elements TO '/tmp/elements.csv' DELIMITER ',' CSV HEADER;
  $ scp host.com:/tmp/elements.csv /tmp/elements.csv
  psql-local> COPY elements FROM '/tmp/elements.csv' DELIMITER ',' CSV;