使用PostgreSQL在数据库之间传输数据 [英] Transfer data between databases with PostgreSQL

问题描述

我需要从另一个数据库传输一些数据。旧数据库名为paw1.movi​​esDB，新数据库名为paw1。每个表的架构如下。

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?

推荐答案

我只需要做这个确切的事情，所以我想把食谱贴在这里。假定两个数据库都在同一服务器上。

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 <old_table> <old_database> | psql -U postgres -d <new_database>

# Just adding extra space here so scrollbar doesn't hide the command

下一步，将复制表的权限授予新数据库的用户。登录到psql：

Next, grant permissions of the copied table to the user of the new database. Log into psql:

psql -U postgres -d <new_database>

ALTER TABLE <old_table> OWNER TO <new_user>;

\q

最后，将数据从旧表复制到新表。以新用户身份登录，然后：

Finally, copy data from the old table to the new table. Log in as the new user and then:

INSERT INTO <new_table> (field1, field2, field3) 
SELECT field1, field2, field3 from <old_table>;

完成！

这篇关于使用PostgreSQL在数据库之间传输数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！