Copy a table from one database to another in Postgres

Question

I am trying to copy an entire table from one database to another in Postgres  Any suggestions

User · Answer

Use pg dump to dump table data  and then restore it with psql

User · Answer

Using psql  on linux host that have connectivity to both servers    export PGPASSWORD password1    psql -U user1 -h host1 database1     -c  copy  select field1 field2 from table1  to stdout with csv           export PGPASSWORD password2    psql -U user2 -h host2 database2       -c  copy table2  field1  field2  from stdin csv

User · Answer

Check this python script  python db copy table py  host 192 168 1 1 port 5432 user admin password admin dbname mydb   host localhost port 5432 user admin password admin dbname mydb  alarmrules -w  WHERE id 19  -v Source number of rows   2 INSERT INTO alarmrules  id login notifybyemail notifybysms  VALUES  19  mister1  true false   INSERT INTO alarmrules  id login notifybyemail notifybysms  VALUES  19  mister2  true false

User · Answer

You can also use the backup functionality in pgAdmin II   Just follow these steps    In pgAdmin  right click the table you want to move  select  Backup  Pick the directory for the output file and set Format to  plain  Click the  Dump Options  1  tab  check  Only data  or  only Schema   depending on what you are doing  Under the Queries section  click  Use Column Inserts  and  User Insert Commands   Click the  Backup  button   This outputs to a  backup file Open this new file using notepad   You will see the insert scripts needed for the table data   Copy and paste these into the new database sql page in pgAdmin   Run as pgScript -  Query- Execute as pgScript F6   Works well and can do multiple tables at a time

User · Answer

Here is what worked for me  First dump to a file   pg dump -h localhost -U myuser -C -t my table -d first db gt  tmp table dump   then load the dumped file   psql -U myuser -d second db lt  tmp table dump

User · Answer

If the both DBs from  amp  to  are password protected  in that scenario terminal won t ask for the password for both the DBs  password prompt will appear only once  So  to fix this  pass the password along with the commands   PGPASSWORD  lt password gt  pg dump -h  lt hostIpAddress gt  -U  lt hostDbUserName gt  -t  lt hostTable gt   gt   lt hostDatabase gt    PGPASSWORD  lt pwd gt  psql -h  lt toHostIpAddress gt  -d  lt toDatabase gt  -U  lt toDbUser gt

User · Answer

You could do the following   pg dump -h  lt host ip address gt  -U  lt host db user name gt  -t  lt host table gt   gt   lt host database gt    psql -h localhost -d  lt local database gt  -U  lt local db user gt

User · Answer

Using dblink would be more convenient  truncate table tableA   insert into tableA select   from dblink  hostaddr xxx xxx xxx xxx dbname mydb user postgres                select a b from tableA          as t1 a text b text

User · Answer

If you have both remote server then you can follow this   pg dump -U Username -h DatabaseEndPoint -a -t TableToCopy SourceDatabase   psql -h DatabaseEndPoint -p portNumber -U Username -W TargetDatabase   It will copy the mentioned table of source Database into same named table of target database  if you already have existing schema

User · Answer

If you run pgAdmin  Backup  pg dump  Restore  pg restore  from Windows it will try to output the file by default to c  Windows System32 and that s why you will get Permission Access denied error and not because the user postgres  is not elevated enough  Run pgAdmin as Administrator or just choose a location for the output other than system folders of Windows

User · Answer

Extract the table and pipe it directly to the target database   pg dump -t table to copy source db   psql target db   Note  If the other database already has the table set up  you should use the -a flag to import data only  else you may see weird errors like  Out of memory    pg dump -a -t my table my db   psql target db

User · Answer

You have to use DbLink to copy one table data into another table at different database   You have to install and configure DbLink extension to execute cross database query    I have already created detailed post on this topic   Please visit this link

User · Answer

First install dblink   Then  you would do something like   INSERT INTO t2 select   from  dblink  host 1 2 3 4  user        password         dbname D1    select   t1   tt         id int    col 1 character varying    col 2 character varying    col 3 int    col 4 varchar

User · Answer

As an alternative  you could also expose your remote tables as local tables using the foreign data wrapper extension  You can then insert into your tables by selecting from the tables in the remote database  The only downside is that it isn t very fast

User · Answer

To move a table from database A to database B at your local setup  use the following command   pg dump -h localhost -U owner-name -p 5432 -C -t table-name database1   psql -U owner-name -h localhost -p 5432 database2

User · Answer

I tried some of the solutions here and they were really helpful  In my experience best solution is to use psql command line  but sometimes i don t feel like using psql command line  So here is another solution for pgAdminIII  create table table1 as   select t1     from dblink      dbname dbSource user user1 password passwordUser1       select   from table1        as t1      fieldName1 as bigserial      fieldName2 as text      fieldName3 as double precision           The problem with this method is that the name of the fields and their types of the table you want to copy must be written

User · Answer

Same as answers by user5542464 and Piyush S  Wanare but split in two steps   pg dump -U Username -h DatabaseEndPoint -a -t TableToCopy SourceDatabase  gt  dump cat dump   psql -h DatabaseEndPoint -p portNumber -U Username -W TargetDatabase   otherwise the pipe asks the two passwords in the same time

User · Answer

I was using DataGrip  By Intellij Idea   and it was very easy copying data from one table  in a different database to another    First  make sure you are connected with both DataSources in Data Grip   Select Source Table and press F5 or  Right-click -  Select Copy Table to    This will show you a list of all tables  you can also search using a table name in the popup window   Just select your target and press OK    DataGrip will handle everything else for you

User · Answer

pg dump does not work always   Given that you have the same table ddl in the both dbs  you could hack it from stdout and stdin as follows      grab the list of cols straight from bash   psql -d   src db  -t -c     SELECT column name   FROM information schema columns   WHERE 1 1   AND table name    table to copy           filter autogenerated cols if needed        psql -d   src db  -c      copy   SELECT col 1   col2 FROM table to copy  TO STDOUT      psql -d   tgt db  -c   copy table to copy  col 1   col2  FROM STDIN

[postgresql] Copy a table from one database to another in Postgres

Examples related to postgresql

Examples related to copy

Examples related to database-table