database 将 PostgreSQL 数据库复制到另一台服务器

Copying PostgreSQL database to another server

提问by Robin Barnes

I'm looking to copy a production PostgreSQL database to a development server. What's the quickest, easiest way to go about doing this?

我希望将生产 PostgreSQL 数据库复制到开发服务器。执行此操作的最快、最简单的方法是什么？

回答by Ferran

You don't need to create an intermediate file. You can do

您不需要创建中间文件。你可以做

pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

or

或者

pg_dump -C -h remotehost -U remoteuser dbname | psql -h localhost -U localuser dbname

using psqlor pg_dumpto connect to a remote host.

使用psql或pg_dump连接到远程主机。

With a big database or a slow connection, dumping a file and transfering the file compressed may be faster.

对于大型数据库或慢速连接，转储文件和传输压缩文件可能会更快。

As Kornel said there is no need to dump to a intermediate file, if you want to work compressed you can use a compressed tunnel

正如 Kornel 所说，不需要转储到中间文件，如果你想压缩工作，你可以使用压缩隧道

pg_dump -C dbname | bzip2 | ssh  remoteuser@remotehost "bunzip2 | psql dbname"

or

或者

pg_dump -C dbname | ssh -C remoteuser@remotehost "psql dbname"

but this solution also requires to get a session in both ends.

但是这个解决方案还需要在两端进行会话。

Note:pg_dumpis for backing up and psqlis for restoring. So, the first command in this answer is to copy from local to remote and the second one is from remote to local. More -> https://www.postgresql.org/docs/9.6/app-pgdump.html

注意：pg_dump用于备份和psql用于恢复。因此，此答案中的第一个命令是从本地复制到远程，第二个命令是从远程复制到本地。更多 -> https://www.postgresql.org/docs/9.6/app-pgdump.html

回答by unmounted

pg_dump the_db_name > the_backup.sql

Then copy the backup to your development server, restore with:

然后将备份复制到您的开发服务器，使用以下命令恢复：

psql the_new_dev_db < the_backup.sql

回答by unmounted

Use pg_dump, and later psqlor pg_restore- depending whether you choose -Fp or -Fc options to pg_dump.

使用pg_dump，稍后使用psql或pg_restore- 取决于您是否为 pg_dump 选择 -Fp 或 -Fc 选项。

Example of usage:

用法示例：

ssh production
pg_dump -C -Fp -f dump.sql -U postgres some_database_name
scp dump.sql development:
rm dump.sql
ssh development
psql -U postgres -f dump.sql

回答by Eric H.

If you are looking to migrate between versions (eg you updated postgres and have 9.1 running on localhost:5432 and 9.3 running on localhost:5434) you can run:

如果您希望在版本之间迁移（例如，您更新了 postgres 并在 localhost:5432 上运行了 9.1，而在 localhost:5434 上运行了 9.3），您可以运行：

pg_dumpall -p 5432 -U myuser91 | psql -U myuser94 -d postgres -p 5434

Check out the migration docs.

查看迁移文档。

回答by Reshad user2701173

pg_basebackupseems to be the better way of doing this now, especially for large databases.

pg_basebackup现在似乎是更好的方法，尤其是对于大型数据库。

You can copy a database from a server with the same or older major version. Or more precisely:

您可以从具有相同或较旧主要版本的服务器复制数据库。或者更准确地说：

pg_basebackupworks with servers of the same or an older major version, down to 9.1. However, WAL streaming mode (-X stream) only works with server version 9.3 and later, and tar format mode (--format=tar) of the current version only works with server version 9.5 or later.

pg_basebackup适用于相同或更旧的主要版本（低至 9.1）的服务器。但是，WAL 流模式 ( -X stream) 仅适用于服务器版本 9.3 及更高版本，--format=tar当前版本的tar 格式模式 ( ) 仅适用于服务器版本 9.5 或更高版本。

For that you need on the source server:

为此，您需要在源服务器上：

1. listen_addresses = '*'to be able to connect from the target server. Make sure port 5432 is open for that matter.
2. At least 1 available replication connection: max_wal_senders = 1(-X fetch), 2for -X stream(the default in case of PostgreSQL 12), or more.
3. wal_level = replicaor higher to be able to set max_wal_senders > 0.
4. host replication postgres DST_IP/32 trustin pg_hba.conf. This grants access to the pgcluster to anyone from the DST_IPmachine. You might want to resort to a more secure option.

1. listen_addresses = '*'能够从目标服务器连接。确保端口 5432 就此打开。
2. 至少1可用的复制连接：max_wal_senders = 1（-X fetch），2用于-X stream（在PostgreSQL中12的情况下，缺省值）或更多。
3. wal_level = replica或更高才能设置max_wal_senders > 0。
4. host replication postgres DST_IP/32 trust在pg_hba.conf。这授予pg任何人从DST_IP机器访问集群的权限。您可能想求助于更安全的选项。

Changes 1, 2, 3 require server restart, change 4 requires reload.

更改 1、2、3 需要重新启动服务器，更改 4 需要重新加载。

On the target server:

在目标服务器上：

# systemctl stop postgresql@VERSION-NAME
postgres$ pg_basebackup -h SRC_IP -U postgres -D VERSION/NAME --progress
# systemctl start postgresql@VERSION-NAME

回答by user01

Run this command with database name, you want to backup, to take dump of DB.

使用要备份的数据库名称运行此命令以转储数据库。

 pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}

 eg. pg_dump -U postgres mydbname -f mydbnamedump.sql

Now scp this dump file to remote machine where you want to copy DB.

现在将此转储文件 scp 到要复制数据库的远程机器。

eg. scp mydbnamedump.sql user01@remotemachineip:~/some/folder/

On remote machine run following command in ~/some/folder to restore the DB.

在远程机器上，在 ~/some/folder 中运行以下命令来恢复数据库。

 psql -U {user-name} -d {desintation_db}-f {dumpfilename.sql}

 eg. psql -U postgres -d mynewdb -f mydbnamedump.sql

回答by pastullo

I struggled quite a lot and eventually the method that allowed me to make it work with Rails 4 was:

我挣扎了很多，最终让我让它与 Rails 4 一起工作的方法是：

on your old server

在你的旧服务器上

sudo su - postgres
pg_dump -c --inserts old_db_name > dump.sql

I had to use the postgres linux user to create the dump. also i had to use -c to force the creation of the database on the new server. --inserts tells it to use the INSERT() syntax which otherwise would not work for me :(

我不得不使用 postgres linux 用户来创建转储。我还必须使用 -c 来强制在新服务器上创建数据库。--inserts 告诉它使用 INSERT() 语法，否则对我不起作用:(

then, on the new server, simpy:

然后，在新服务器上，简单地：

sudo su - postgres
psql new_database_name < dump.sql

to transfer the dump.sql file between server I simply used the "cat" to print the content and than "nano" to recreate it copypasting the content.

为了在服务器之间传输 dump.sql 文件，我只是使用“cat”来打印内容，而不是“nano”来重新创建它复制粘贴内容。

Also, the ROLE i was using on the two database was different so i had to find-replace all the owner name in the dump.

此外，我在两个数据库上使用的角色不同，因此我必须在转储中查找替换所有所有者名称。

回答by Anvesh

Let me share a Linux shell script to copy your table data from one server to another PostgreSQL server.

让我分享一个 Linux shell 脚本，将您的表数据从一台服务器复制到另一台 PostgreSQL 服务器。

Reference taken from this blog:

参考来自此博客：

Linux Bash Shell Script for data migration between PostgreSQL Servers:

用于 PostgreSQL 服务器之间数据迁移的 Linux Bash Shell 脚本：

#!/bin/bash
psql \
    -X \
    -U user_name \
    -h host_name1 \
    -d database_name \
    -c "\copy tbl_Students to stdout" \
| \
psql \
    -X \
    -U user_name \
    -h host_name2 \
    -d database_name \
    -c "\copy tbl_Students from stdin"

I am just migrating the data; please create a blank table at your destination/second database server.

我只是在迁移数据；请在您的目标/第二个数据库服务器上创建一个空白表。

This is a utility script. Further, you can modify the script for generic use something like by adding parameters for host_name, database_name, table_name and others

这是一个实用程序脚本。此外，您可以修改脚本以用于通用用途，例如通过为主机名、数据库名、表名等添加参数

回答by MisterJoyson

---

---

Dump your database : pg_dump database_name_name > backup.sql

转储您的数据库： pg_dump database_name_name > backup.sql

---

---

Import your database back: psql db_name < backup.sql

导入您的数据库： psql db_name < backup.sql

回答by zoran

Accepted answer is correct, but if you want to avoid entering the password interactively, you can use this:

接受的答案是正确的，但如果您想避免以交互方式输入密码，您可以使用：

PGPASSWORD={{export_db_password}} pg_dump --create -h {{export_db_host}} -U {{export_db_user}} {{export_db_name}} | PGPASSWORD={{import_db_password}} psql -h {{import_db_host}} -U {{import_db_user}} {{import_db_name}}