在 PostgreSQL 中创建数据库的副本

Creating a copy of a database in PostgreSQL

提问by egaga

What's the correct way to copy entire database (its structure and data) to a new one in pgAdmin?

在 pgAdmin 中将整个数据库（其结构和数据）复制到新数据库的正确方法是什么？

回答by Bell

Postgres allows the use of any existing database on the server as a template when creating a new database. I'm not sure whether pgAdmin gives you the option on the create database dialog but you should be able to execute the following in a query window if it doesn't:

Postgres 允许在创建新数据库时使用服务器上的任何现有数据库作为模板。我不确定 pgAdmin 是否在创建数据库对话框中为您提供了选项，但如果没有，您应该能够在查询窗口中执行以下操作：

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

Still, you may get:

不过，您可能会得到：

ERROR:  source database "originaldb" is being accessed by other users

To disconnect all other users from the database, you can use this query:

要断开所有其他用户与数据库的连接，您可以使用以下查询：

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'originaldb' AND pid <> pg_backend_pid();

回答by zbyszek

A command-line version of Bell's answer:

贝尔答案的命令行版本：

createdb -O ownername -T originaldb newdb

This should be run under the privileges of the database master, usually postgres.

这应该在数据库管理员的权限下运行，通常是 postgres。

回答by Brugolo

To clone an existing database with postgres you can do that

要使用 postgres 克隆现有数据库，您可以这样做

/* KILL ALL EXISTING CONNECTION FROM ORIGINAL DB (sourcedb)*/
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'SOURCE_DB' AND pid <> pg_backend_pid();

/* CLONE DATABASE TO NEW ONE(TARGET_DB) */
CREATE DATABASE TARGET_DB WITH TEMPLATE SOURCE_DB OWNER USER_DB;

IT will kill all the connection to the source db avoiding the error

IT 将终止与源数据库的所有连接，避免错误

ERROR:  source database "SOURCE_DB" is being accessed by other users

回答by Tregoreg

In production environment, where the original database is under traffic, I'm simply using:

在原始数据库流量不足的生产环境中，我只是使用：

pg_dump production-db | psql test-db

回答by TrayMan

Don't know about pgAdmin, but pgdumpgives you a dump of the database in SQL. You only need to create a database by the same name and do

不知道 pgAdmin，但pgdump在 SQL 中为您提供了数据库的转储。您只需要创建一个同名的数据库并执行

psql mydatabase < my dump

to restore all of the tables and their data and all access privileges.

恢复所有表及其数据和所有访问权限。

回答by Mathieu Rodic

First, sudoas the database user:

首先，sudo作为数据库用户：

sudo su postgres

Go to PostgreSQL command line:

转到 PostgreSQL 命令行：

psql

Create the new database, give the rights and exit:

创建新数据库，赋予权限并退出：

CREATE DATABASE new_database_name;
GRANT ALL PRIVILEGES ON DATABASE new_database_name TO my_user;
\d

Copy structure and data from the old database to the new one:

将旧数据库中的结构和数据复制到新数据库中：

pg_dump old_database_name | psql new_database_name

回答by fusion27

I pieced this approach together with the examples from above. I'm working on an "under load" server and got the error when I attempted the approach from @zbyszek. I also was after a "command line only" solution.

我将这种方法与上面的例子拼凑在一起。我正在一个“负载不足”的服务器上工作，当我尝试使用@zbyszek 的方法时出现错误。我也在寻求“仅限命令行”的解决方案。

createdb: database creation failed: ERROR: source database "exampledb" is being accessed by other users.

createdb: database creation failed: ERROR: source database "exampledb" is being accessed by other users.

Here's what worked for me (Commands prepended with nohupto move output into a file and protect from a server disconnect):

这是对我有用的nohup（命令前面带有将输出移动到文件中并防止服务器断开连接）：

1. nohup pg_dump exampledb > example-01.sql
2. createdb -O postgres exampledbclone_01

   my user is "postgres"

3. nohup psql exampledbclone_01 < example-01.sql

1. nohup pg_dump exampledb > example-01.sql
2. createdb -O postgres exampledbclone_01

   我的用户是“postgres”

3. nohup psql exampledbclone_01 < example-01.sql

回答by Isomorph

In pgAdmin you can make a backup from your original database, and then just create a new database and restore from the backup just created:

在 pgAdmin 中，您可以从原始数据库进行备份，然后创建一个新数据库并从刚刚创建的备份中恢复：

1. Right click the source database, Backup... and dump to a file.
2. Right click, New Object, New Database... and name the destination.
3. Right click the new database, Restore... and select your file.

1. 右键单击源数据库，备份...并转储到文件。
2. 右键单击，新建对象，新建数据库...并命名目标。
3. 右键单击新数据库，还原...并选择您的文件。

回答by Anirban Chakrabarti

What's the correct way to copy entire database (its structure and data) to a new one in pgAdmin?

在 pgAdmin 中将整个数据库（其结构和数据）复制到新数据库的正确方法是什么？

Answer:

回答：

CREATE DATABASE newdb WITH TEMPLATE originaldb;

Tried and tested.

尝试和测试。

回答by bfris

From the documentation, using createdbor CREATE DATABASEwith templates is not encouraged:

从文档中，不鼓励使用createdb或CREATE DATABASE使用模板：

Although it is possible to copy a database other than template1 by specifying its name as the template, this is not (yet) intended as a general-purpose “COPY DATABASE” facility. The principal limitation is that no other sessions can be connected to the template database while it is being copied. CREATE DATABASE will fail if any other connection exists when it starts; otherwise, new connections to the template database are locked out until CREATE DATABASE completes.

尽管可以通过将数据库名称指定为模板来复制除 template1 之外的数据库，但这还不是（还）用作通用“复制数据库”工具。主要限制是在复制模板数据库时不能将其他会话连接到模板数据库。如果启动时存在任何其他连接，CREATE DATABASE 将失败；否则，在 CREATE DATABASE 完成之前，与模板数据库的新连接将被锁定。

pg_dumpor pg_dumpallis a good way to go for copying database AND ALL THE DATA. If you are using a GUI like pgAdmin, these commands are called behind the scenes when you execute a backup command. Copying to a new database is done in two phases: Backup and Restore

pg_dump或者pg_dumpall是复制数据库和所有数据的好方法。如果您使用的是像 pgAdmin 这样的 GUI，当您执行备份命令时，这些命令会在后台调用。复制到新数据库分两个阶段完成：备份和还原

pg_dumpallsaves all of the databases on the PostgreSQL cluster. The disadvantage to this approach is that you end up with a potentially very large text file full of SQL required to create the database and populate the data. The advantage of this approach is that you get all of the roles (permissions) for the cluster for free. To dump all databases do this from the superuser account

pg_dumpall保存 PostgreSQL 集群上的所有数据库。这种方法的缺点是您最终会得到一个可能非常大的文本文件，其中包含创建数据库和填充数据所需的 SQL。这种方法的优点是您可以免费获得集群的所有角色（权限）。要转储所有数据库，请从超级用户帐户执行此操作

pg_dumpall > db.out

and to restore

并恢复

psql -f db.out postgres

pg_dumphas some compression options that give you much smaller files. I have a production database I backup twice a day with a cron job using

pg_dump有一些压缩选项可以为您提供更小的文件。我有一个生产数据库，我每天使用 cron 作业备份两次

pg_dump --create --format=custom --compress=5 --file=db.dump mydatabase

where compressis the compression level (0 to 9) and createtells pg_dumpto add commands to create the database. Restore (or move to new cluster) by using

哪里compress是压缩级别（0 到 9）并create告诉pg_dump添加命令来创建数据库。使用以下命令恢复（或移动到新集群）

pg_restore -d newdb db.dump

where newdb is the name of the database you want to use.

其中 newdb 是您要使用的数据库的名称。

Other things to think about

其他需要考虑的事情

PostgreSQL uses ROLES for managing permissions. These are not copied by pg_dump. Also, we have not dealt with the settings in postgresql.confand pg_hba.conf(if you're moving the database to another server). You'll have to figure out the conf settings on your own. But there is a trick I just discovered for backing up roles. Roles are managed at the cluster level and you can ask pg_dumpallto backup just the roles with the --roles-onlycommand line switch.

PostgreSQL 使用 ROLES 来管理权限。这些不是由pg_dump. 此外，我们还没有处理postgresql.conf和pg_hba.conf 中的设置（如果您要将数据库移动到另一台服务器）。您必须自己弄清楚 conf 设置。但是我刚刚发现了一个用于备份角色的技巧。角色在集群级别进行管理，您可以要求pg_dumpall使用--roles-only命令行开关仅备份角色。