如何在 PostgreSQL 中将表从服务器复制到另一个服务器？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15335209/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to copy table from server to another in PostgreSQL?
提问by Kliver Max
I have two servers on PostgreSQL 8.4:
我在 PostgreSQL 8.4 上有两台服务器：
server1:5432
server2:5432
Now i want to copy table from server1 and put it in data base on server2.
Its possible to do?
现在我想从 server1 复制表并将其放入 server2 上的数据库中。
有可能吗？
UPDATE
更新
I try do like in @Valery Viktorovsky's answer:
我试着像@Valery Viktorovsky 的回答那样做：
pg_dump --server1:5432 --encoding=utf8 --no-owner --username=postgres --123456 np_point > D:\np_point.sql
psql --server2:5432 --username=postgres mrsk -f D:\np_point.sql
and get error:
并得到错误：
ERROR: syntax error at or near "pg_dump"
LINE 1: pg_dump --server1:5432 --encoding=utf8 --no-owner --use...
回答by Valery Viktorovsky
The safest way is to use pg_dump.
最安全的方法是使用 pg_dump。
pg_dump --host server1 --encoding=utf8 --no-owner --username=foo --password -t table_name db_name > server1_db.sql
psql --host server2 --username=foo db_name -f server1_db.sql
回答by A4L
Try this
试试这个
using the COPY command from psql. Connect to server1 and export to CSVthen connect to server2 and import from CSV
使用 psql 中的 COPY 命令。连接到 server1 并导出到 CSV，然后连接到 server2 并从 CSV 导入
回答by KRB
exact commands -
确切的命令 -
1. Export via pg_dumpinto a file:
1. 导出pg_dump至文件：
pg_dump --host "source hostname" --port 5432 --username "username" --no-password --verbose --file "filename" --table "source schema.tablename" "source db name"
this will create a file called "filename" at the directory where you ran above command - that will have schema and data for the source table. You can can give any absolute path too.
这将在您运行上述命令的目录中创建一个名为“filename”的文件 - 它将包含源表的架构和数据。您也可以给出任何绝对路径。
2. Import via psql:
2.通过psql导入：
psql --host "target hostname" --port 5432 --username "username" --password --verbose --file "file name" "target db name"
->this will prompt for password
->这将提示输入密码
Before running import, drop the target table if exists.
在运行导入之前，删除目标表（如果存在）。
Worked like charm and finished 10M rows within 2mins
像魅力一样工作并在 2 分钟内完成了 1000 万行
回答by Roee Anuar
I've written a small script in python that can help. Note - it will only work with small tables. You first need to pip install pandas, sqlalchemy and psycopg2. Create the file as pycopy.py and run it with "python pycopy.py tablename" (without the quotes). You can change the source and destination to work with any other db type - just change the source and destination strings.
我用 python 编写了一个小脚本，可以提供帮助。注意 - 它只适用于小桌子。您首先需要 pip install pandas、sqlalchemy 和 psycopg2。将文件创建为 pycopy.py 并使用“python pycopy.py tablename”（不带引号）运行它。您可以更改源和目标以使用任何其他数据库类型 - 只需更改源和目标字符串。
The script:
剧本：
import pandas as pd
import sys
import sqlalchemy as sa
strengine_source='postgresql://user:password@db_ip:5432/dbsourcename'
strengine_dest='postgresql://user:password@db_ip:5432/dbdestinationname'
if len(sys.argv) > 1:
tblname = sys.argv[1]
df=pd.read_sql("select * from " + tblname,sa.create_engine(strengine_source))
df.to_sql(tblname,sa.create_engine(strengine_dest), index=False)
回答by Dhruvil Thaker
this will copy only a particular table from a particular database to destination database
这只会将特定表从特定数据库复制到目标数据库
pg_dump -h localhost -U postgres -p 5432 -C -t table_name source_db_name | ssh -C username@ip "psql -h localhost -U postgres -p 5432 destination_db_name"
回答by Anvesh
You can also do use Linux shell script to migrate your table data from one server to another PostgreSQL server.
您还可以使用 Linux shell 脚本将表数据从一台服务器迁移到另一台 PostgreSQL 服务器。
I just posted my answer on below similar stack question please refer this. Copying PostgreSQL database to another server
我刚刚在下面的类似堆栈问题上发布了我的答案，请参考这个。 将 PostgreSQL 数据库复制到另一台服务器
