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

User · Answer

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   quot databases quot  are  quot schemas quot  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  quot foreign data wrapper quot  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

User · Answer

There are three options for copying it if this is a one off    Use a db link  I think it is still in contrib  Have the application do the work  Export import   If this is an ongoing need  the answers are    Change to schemas in the same DB db link

User · Answer

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

User · Answer

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

User · Answer

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  because apparently you can t move data between databases    At the commandline   pg dump -U postgres -t  lt old table gt   lt old database gt    psql -U postgres -d  lt new database gt     Just adding extra space here so scrollbar doesn t hide the command   Next  grant permissions of the copied table to the user of the new database   Log into psql   psql -U postgres -d  lt new database gt   ALTER TABLE  lt old table gt  OWNER TO  lt new user gt     q   Finally  copy data from the old table to the new table   Log in as the new user and then   INSERT INTO  lt new table gt   field1  field2  field3   SELECT field1  field2  field3 from  lt old table gt     Done

User · Answer

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  gt  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 gt  COPY elements TO   tmp elements csv  DELIMITER     CSV HEADER      scp host com  tmp elements csv  tmp elements csv   psql-local gt  COPY elements FROM   tmp elements csv  DELIMITER     CSV

User · Answer

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

User · Answer

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     Create plperlu language in your database A 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    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- gt connect  connection string  user   pass    AutoCommit  gt 0 RaiseError  gt 1 PrintError  gt 1 pg enable utf8  gt 1      my  sql    dbh- gt  prepare   command    eval    sql- gt  execute      my  error    dbh- gt  state   sql- gt  finish  if    error      dbh- gt  rollback     else     dbh- gt  commit      dbh- gt  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

User · Answer

If your source and target database resides in the same local machine  you can use   Note - Sourcedb already exists in your database  CREATE DATABASE targetdb WITH TEMPLATE sourcedb   This statement copies the sourcedb to the targetdb   If your source and target databases resides on different servers  you can use following steps   Step 1 -  Dump the source database to a file  pg dump -U postgres -O sourcedb sourcedb sql  Note - Here postgres is the username so change the name accordingly  Step 2 - Copy the dump file to the remote server  Step 3 - Create a new database in the remote server CREATE DATABASE targetdb   Step 4 - Restore the dump file on the remote server psql -U postgres -d targetdb -f sourcedb sql   pg dump is a standalone application  i e   something you run in a shell command-line  and not an Postgres SQL command   This should do it

[sql] Transfer data between databases with PostgreSQL

Examples related to sql

Examples related to postgresql

Examples related to insertion