postgresql - 如何通过一个单一的操作从生产数据库中克隆测试数据库？

Question

我正在寻找一个基本的脚本/命令来创建一个实时数据库的副本(让它们命名为 mydb 和 mydb_test，它们都在同一台服务器上)。

要求

• 即使 mydb_test 已经存在并且有记录，它也必须运行
• 即使 mydb 和 mydb_test 确实有现有连接，它也必须工作
• 如有必要，它必须清理可能存在的数据库

提示:

• drop database 如果您有现有连接则不能使用

Answer 1

创建现有(事件)数据库的完整副本的最简单和最快的方法是使用 CREATE DATABASE with a TEMPLATE :

CREATE DATABASE mydb_test TEMPLATE mydb;

但是，有一个重要的限制违反了您的第二个要求:模板(源)数据库不能有额外的连接。 I quote the manual:

It is possible to create additional template databases, and indeed onecan copy any database in a cluster by specifying its name as thetemplate for CREATE DATABASE. It is important to understand, however,that this is not (yet) intended as a general-purpose "COPY DATABASE"facility. The principal limitation is that no other sessions can beconnected to the source database while it is being copied. CREATE DATABASEwill fail if any other connection exists when it starts; duringthe copy operation, new connections to the source database are prevented.

如果您拥有 pg_terminate_backend() 的必要权限，您可以终止与模板数据库的所有 session 。 .
要暂时禁止重新连接，revoke the CONNECT privilege (稍后返回 GRANT)。

REVOKE CONNECT ON DATABASE mydb FROM PUBLIC;

-- while connected to another DB - like the default maintenance DB "postgres"
SELECT pg_terminate_backend(pid)
FROM   pg_stat_activity
WHERE  datname = 'mydb'                    -- name of prospective template db
AND    pid <> pg_backend_pid();            -- don't kill your own session

CREATE DATABASE mydb_test TEMPLATE mydb;

GRANT CONNECT ON DATABASE mydb TO PUBLIC;  -- only if they had it before

在 Postgres 9.2 之前的版本中使用 procpid 而不是 pid:

• How to drop a PostgreSQL database if there are active connections to it?

相关:

• Force drop db while others may be connected

---

如果您无力终止并发 session ，请将 pg_dump 的输出通过管道传输到 psql，就像其他答案已经建议的那样。