在Postgres中将一个表从一个数据库复制到另一个数据库

我试图在Postgres中从一个数据库复制整个表到另一个数据库。有什么建议吗?

356091 次浏览
小开

使用pg_dump转储表数据，然后使用psql恢复。

小开

使用dblink会更方便!

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

您还可以使用pgAdmin II中的备份功能。只需遵循以下步骤:

  • 在pgAdmin中，右键单击要移动的表，选择“Backup”
  • 选择输出文件的目录，并将格式设置为“plain”
  • 点击“转储选项#1”选项卡，选择“仅数据”或“仅架构”(取决于你正在做什么)
  • 在“查询”部分，单击“使用列插入”和“用户插入命令”。
  • 点击“备份”按钮。这将输出到.backup文件
  • 使用记事本打开这个新文件。您将看到表/数据所需的插入脚本。复制并粘贴到pgAdmin中的新数据库sql页面。以pgScript执行- Query->以pgScript执行F6

工作良好，可以做多个表在一个时间。

小开
最佳答案

提取表并将其直接输送到目标数据库:

pg_dump -t table_to_copy source_db | psql target_db

注意:如果另一个数据库已经设置了表，你应该使用-a标志只导入数据，否则你可能会看到奇怪的错误，如“内存不足”:

pg_dump -a -t table_to_copy source_db | psql target_db
小开

使用psql，在与两个服务器都有连接的linux主机上

( 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" )
小开

第一个安装dblink

然后，你可以这样做:

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
);
小开
你必须使用DbLink将一个表的数据复制到不同数据库中的另一个表中。 您必须安装和配置DbLink扩展才能执行跨数据库查询。< / p > 我已经创建了关于这个主题的详细帖子。 请访问此链接 < / p >
小开

要在本地设置中将一个表从数据库a移动到数据库B，使用以下命令:

pg_dump -h localhost -U owner-name -p 5432 -C -t table-name database1 | psql -U owner-name -h localhost -p 5432 database2
小开

我尝试了这里的一些解决方案，它们真的很有帮助。根据我的经验，最好的解决方案是使用psql命令行，但有时我不喜欢使用psql命令行。所以这里是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
)
)

这种方法的问题是，必须写入字段的名称及其要复制的表的类型。

小开

如果你有两个远程服务器，那么你可以这样做:

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

它会将源数据库中提到的表复制到目标数据库中同名的表，如果您已经有了模式。

小开

与user5542464和Piyush S. Wanare的答案相同，但分为两步:

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

否则，管道将同时询问两个密码。

小开
以下是对我有效的方法。 第一次转储到一个文件:

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

然后加载转储文件:

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

pg_dump并不总是有效。

假设你在两个dbs中有相同的表ddl 你可以从stdout和stdin中破解它，如下所示 

 # 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"
小开

你可以这样做:

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>
小开

如果你在Windows上运行pgAdmin (Backup: pg_dump, Restore: pg_restore)，它会在默认情况下尝试将文件输出到c:\Windows\System32，这就是为什么你会得到拒绝权限/访问错误，而不是因为用户postgres不够高。以管理员身份执行命令pgAdmin或只是为输出选择一个位置，而不是Windows的系统文件夹。

小开

检查这个python脚本

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

如果两个db (from &to)是密码保护的，在这种情况下，终端不会要求两个数据库的密码，密码提示只会出现一次。 因此，为了解决这个问题，在命令中传递密码 

PGPASSWORD=<password> pg_dump -h <hostIpAddress> -U <hostDbUserName> -t <hostTable> > <hostDatabase> | PGPASSWORD=<pwd> psql -h <toHostIpAddress> -d <toDatabase> -U <toDbUser>
小开

作为一种替代方法，您还可以使用外部数据包装器扩展将远程表公开为本地表。然后，您可以通过从远程数据库中的表进行选择，将数据插入到您的表中。唯一的缺点是速度不是很快。

小开

我正在使用DataGrip (By Intellij Idea)。将数据从一个表(在不同的数据库中)复制到另一个表非常容易。

首先，确保你在Data Grip中连接了两个数据源。

选择源表并按F5或(右键单击->选择复制表到。)

这将显示所有表的列表(您也可以在弹出窗口中使用表名进行搜索)。选择你的目标，然后按OK。

DataGrip将为您处理其他一切。

小开

对于DBeaver工具用户，你可以“出口data"到另一个数据库中的表。

enter image description here

我一直面临的唯一错误是由于错误的postgres驱动。

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

这是一个关于如何导出数据的官方维基: https://github.com/dbeaver/dbeaver/wiki/Data-transfer

小开

你可以在两个简单的步骤中这样做:

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


# restore the database
pg_restore -d newdb db.dump

对于远程数据库:

# 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
小开

如果你想将数据从一个服务器数据库复制到另一个服务器数据库，那么你必须创建< >强dblink < / >强连接两个数据库，否则你可以在csv中导出表数据，并在其他数据库中导入数据，表字段应该与主表相同。


提交答案