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?

Answer 1

bash -c "psql [postgres connection string B] -c 'TRUNCATE \"TABLE NAME\";' && pg_dump -a -t '\"TABLE NAME\"' -d [postgres connection string A] | psql [postgres connection string B]"

I'm on fish shell, sharing this if anyone is still struggling.

This does 3 things

1. Truncate the destination table
2. Dump the table from source DB
3. Pipe to the destination DB via psql

This is in memory data replication

Answer 2

Just use CREATE TABLE:

CREATE TABLE new_table AS TABLE existing_table;

Answer 3

Having done this wrong several times, I'll contribute a solution to SAFELY and RELIABLY copy a table from one remote db to another. There's a lot that can go wrong between the dump and restore. For clarity, some additional criteria in this solution:

• Copy only one table
• Does not delete anything in either source/dest database
• Makes sure the id sequence resumes in the to_table, instead of resetting to 1
• Avoids drop table or --clean mistakes from hasty copy-paste
• Separates dump and restore into two different steps
• Allows flexibility in customizing the to_table (different indexes, etc)
• Both databases are remote
• Each database has a different hostname, port, username, pass

Prerequsites: get pg_dump, pg_restore, psql matching the remote db version

# Figure out which database version is running
#   to use the pg_dump, pg_restore with the version.
# Run the query:
#   select version() # PostgreSQL 14.10

# Then install the matching version
brew tap homebrew/versions
brew search postgresql@
brew install postgresql@14

# Later we can switch back
brew install postgresql@16

Export a table from the remote db, including all large objects in the table

# Dump from 10.0.1.123:1234
#
# -Fc Uses "format custom" optimized for pg_restore
# -b include all large objects, i.e. blobs, bytea, etc
# -U username
# -h hostname
# -p port
# -a only include table data and large objects
# -t table name
# PGPASSWORD is the supported env var to pass in a password
PGPASSWORD="FROM-DB-PASSWORD" pg_dump -Fc -b -U FROM-DB-USERNAME -h 10.0.1.123 -p 1234 -a -t from_table from_db_name > from_table.dump

# Get the last id sequence for restore later
psql -h 10.0.1.123 -p 1234 -d from_db_name -U FROM-DB-USERNAME -W -c "select * from from_table_name_id_seq;"
# last_value == 9999

Import the table into another remote db

# NO CLEAN, NO DROP/DELETE
#
# Safely create a table with a different name for now.
# This helps avoid copy-paste errors accidentally
#   importing back to or deleting things in from_db.
psql -h 10.0.1.456 -p 4567 -d to_db_name -U TO-DB-USERNAME -W -c "create table to_table (id bigserial not null primary key, . . . );"

# Restore to 10.0.1.456:4567
#
# -U username
# -h hostname
# -p port
# -a only include table data and large objects
# -t table name
# -d database name
PGPASSWORD="TO-DB_PASSWORD" pg_restore -h 10.0.1.456 -p 4567 -d to_db_name -U TO-DB-USERNAME -a -t to_table_name from_table.dump

# Restore the id sequence we got from the last export step above.
psql -h 10.0.1.456 -p 4567 -d to_db_name -U TO-DB-USERNAME -W -c "alter sequence to_table_name_id_seq restart with 9999;"

# Rename the table to match the from_table_name
psql -h 10.0.1.456 -p 4567 -d to_db_name -U TO-DB-USERNAME -W -c "alter table to_table_name rename to name_matching_from_table_name;"

# Cleanup
rm from_table.dump

Answer 4

for postgres version >= 8.4.0 the below worked for me

pg_dump -U user -h host --column-inserts --data-only --table=table_name database_name | psql -h host -p port -U user -W database_name

Answer 5

On my mac using a | asked for two passwords at the same time which didn't work. here is what I did.

pg_dump -h {host} -U {user} -t {table} {db} | psql postgresql://{user}:{password}@{host}:{port}/{db}

Answer 6

It could be done fairly simple manner. Just use the following command

pg_dump –U <user_name> –t <table_name> <source_database> | psql –U <user_name> <targeted_database>

replace values in <> with your specific parameters and also remove <>.

Answer 7

Combining this answer and this answer, which is more convenient as you don't need to specify the columns:

TRUNCATE TABLE tableA;

INSERT INTO tableA
SELECT (rec).*
FROM dblink('hostaddr=xxx.xxx.xxx.xxx dbname=mydb user=postgres',
            'SELECT myalias FROM tableA myalias')
       AS t1(rec tableA);

Answer 8

Without any piping, on Windows, you can use:

Dump - Edit this to be on one line

"C:\Program Files\PostgreSQL\14\bin\pg_dump.exe"
--host="host-postgres01"
--port="1234"
--username="user01"
-t "schema01.table01"
--format=c
-f "C:\Users\user\Downloads\table01_format_c.sql"
"DB-01"

Restore - Edit this to be on one line

"C:\Program Files\PostgreSQL\14\bin\pg_restore.exe"
--host="host-postgres02"
--port="5678"
--username="user02"
-1
--dbname="DB-02"
"C:\Users\user\Downloads\table01_format_c.sql"

You will be prompted for user passwords.

This solution will put the new table in a schema with the same name (schema01).

Answer 9

if you want to copy data from one server database to another server database then you have create dblink connection both database otherwise you can export the table data in csv and import the data in other database table, table fields should be same as primary table.

Answer 10

You can do in Two simple steps:

# dump the database in custom-format archive
pg_dump -Fc mydb > db.dump

# restore the database
pg_restore -d newdb db.dump

In case of Remote Databases:

# dump the database in custom-format archive
pg_dump -U mydb_user -h mydb_host -t table_name -Fc mydb > db.dump

# restore the database
pg_restore -U newdb_user -h newdb_host -d newdb db.dump

Answer 11

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 table_to_copy source_db | psql target_db

Answer 12

for DBeaver tool users, you can "Export data" to table in another database.

Only error I kept facing was because of wrong postgres driver.

SQL Error [34000]: ERROR: portal "c_2" does not exist
    ERROR: Invalid protocol sequence 'P' while in PortalSuspended state.

Here is a official wiki on how to export data: https://github.com/dbeaver/dbeaver/wiki/Data-transfer

Answer 13

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);

Answer 14

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.

Answer 15

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" )

Answer 16

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.

Answer 17

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.

Answer 18

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 
);

Answer 19

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"

Answer 20

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

Answer 21

If the both DBs(from & 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=<password> pg_dump -h <hostIpAddress> -U <hostDbUserName> -t <hostTable> > <hostDatabase> | PGPASSWORD=<pwd> psql -h <toHostIpAddress> -d <toDatabase> -U <toDbUser>

Answer 22

You could do the following:

pg_dump -h <host ip address> -U <host db user name> -t <host table> > <host database> | psql -h localhost -d <local database> -U <local db user>

---

Answer 23

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);

Answer 24

Here is what worked for me. First dump to a file:

pg_dump -h localhost -U myuser -C -t my_table -d first_db>/tmp/table_dump

then load the dumped file:

psql -U myuser -d second_db</tmp/table_dump

Answer 25

Same as answers by user5542464 and Piyush S. Wanare but split in two steps:

pg_dump -U Username -h DatabaseEndPoint -a -t TableToCopy SourceDatabase > dump
cat dump | psql -h DatabaseEndPoint -p portNumber -U Username -W TargetDatabase

otherwise the pipe asks the two passwords in the same time.

Answer 26

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.

Answer 27

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.

Answer 28

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

Answer 29

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.

Answer 30

Use pg_dump to dump table data, and then restore it with psql.