在 PostgreSQL 中创建数据库的副本

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

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

不过，您可能会得到：

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

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

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

---

Bell's answer 的命令行版本：

createdb -O ownername -T originaldb newdb

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

---

要使用 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 将终止与源数据库的所有连接以避免错误

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

---

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

pg_dump production-db | psql test-db

---

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

psql mydatabase < my dump

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

---

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

sudo su postgres

转到 PostgreSQL 命令行：

psql

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

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

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

pg_dump old_database_name | psql new_database_name

---

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

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

---

复制“负载不足”的数据库

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

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

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

nohup pg_dump exampledb > example-01.sql createdb -O postgres exampledbclone_01 我的用户是“postgres” nohup psql exampledbclone_01 < example-01.sql

---

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

回答：

CREATE DATABASE newdb WITH TEMPLATE originaldb;

尝试和测试。

---

这是仅使用 pgadmin4 GUI（通过备份和恢复）在数据库上创建副本的整个过程

Postgres 带有 Pgadmin4。如果您使用 macOS，您可以按 CMD+SPACE 并输入 pgadmin4 来运行它。这将在 chrome 中打开一个浏览器选项卡。

复制步骤

1. 创建备份

通过右键单击数据库->“备份”来执行此操作

https://i.stack.imgur.com/2hQt9.png

2. 给文件命名。

像test12345。点击备份。这会创建一个二进制文件转储，它不是 .sql 格式

https://i.stack.imgur.com/FyEZm.png

3.查看下载位置

屏幕右下角应该有一个弹出窗口。单击“更多详细信息”页面以查看您的备份下载到的位置

https://i.stack.imgur.com/5cXqM.png

4.找到下载文件的位置

在这种情况下，它是 /users/vincenttang

https://i.stack.imgur.com/vJiyy.png

5.从pgadmin恢复备份

假设您正确执行了步骤 1 到 4，您将拥有一个还原二进制文件。有时您的同事可能想在他们的本地计算机上使用您的还原文件。说过人去 pgadmin 并恢复

通过右键单击数据库->“恢复”来执行此操作

https://i.stack.imgur.com/bAUhb.png

6.选择文件查找器

确保手动选择文件位置，不要将文件拖放到 pgadmin 中的上传器字段中。因为你会遇到错误权限。相反，找到您刚刚创建的文件：

https://i.stack.imgur.com/0FL3F.png

7.找到所说的文件

您可能需要将右下角的过滤器更改为“所有文件”。之后从第 4 步开始查找文件。现在点击右下角的“选择”按钮进行确认

https://i.stack.imgur.com/woFcW.png

8.恢复所述文件

您将再次看到此页面，并选择文件的位置。继续并恢复它

https://i.stack.imgur.com/i7cTv.png

9. 成功

如果一切正常，右下角应该会弹出一个显示成功恢复的指示器。您可以导航到您的表以查看每个表上的数据是否已正确恢复。

10. 如果不成功：

如果第 9 步失败，请尝试删除数据库上的旧公共架构。转到“查询工具”

https://i.stack.imgur.com/bqmyP.png

执行此代码块：

DROP SCHEMA public CASCADE; CREATE SCHEMA public;

https://i.stack.imgur.com/BaHyr.png

现在再次尝试步骤 5 到 9，它应该可以解决

编辑 - 一些附加说明。如果您在上传过程中遇到错误，请更新 PGADMIN4

---

从 documentation 开始，不鼓励将 createdb 或 CREATE DATABASE 与模板一起使用：

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

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

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

pg_dumpall > db.out

并恢复

psql -f db.out postgres

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

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

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

pg_restore -d newdb db.dump

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

其他需要考虑的事情

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

---

对于那些仍然感兴趣的人，我想出了一个 bash 脚本，它（或多或少）做了作者想要的。我必须在生产系统上制作日常业务数据库副本，这个脚本似乎可以解决问题。请记住更改数据库名称/用户/密码值。

#!/bin/bash

if [ 1 -ne $# ]
then
  echo "Usage `basename $0` {tar.gz database file}"
  exit 65;
fi

if [ -f "$1" ]
then
  EXTRACTED=`tar -xzvf $1`
  echo "using database archive: $EXTRACTED";
else
  echo "file $1 does not exist"
  exit 1
fi


PGUSER=dbuser
PGPASSWORD=dbpw
export PGUSER PGPASSWORD

datestr=`date +%Y%m%d`


dbname="dbcpy_$datestr"
createdbcmd="CREATE DATABASE $dbname WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;"
dropdbcmp="DROP DATABASE $dbname"

echo "creating database $dbname"
psql -c "$createdbcmd"

rc=$?
if [[ $rc != 0 ]] ; then
  rm -rf "$EXTRACTED"
  echo "error occured while creating database $dbname ($rc)"
  exit $rc
fi


echo "loading data into database"
psql $dbname < $EXTRACTED > /dev/null

rc=$?

rm -rf "$EXTRACTED"

if [[ $rc != 0 ]] ; then
  psql -c "$dropdbcmd"
  echo "error occured while loading data to database $dbname ($rc)"
  exit $rc
fi


echo "finished OK"

---

PostgreSQL 9.1.2：

$ CREATEDB new_db_name -T orig_db_name -O db_user;

---

创建数据库转储

cd /var/lib/pgsql/
pg_dump database_name> database_name.out

重新设置数据库转储

psql -d template1
CREATE DATABASE database_name WITH  ENCODING 'UTF8' LC_CTYPE 'en_US.UTF-8' LC_COLLATE 'en_US.UTF-8' TEMPLATE template0;
CREATE USER  role_name WITH PASSWORD 'password';
ALTER DATABASE database_name OWNER TO role_name;
ALTER USER role_name CREATEDB;
GRANT ALL PRIVILEGES ON DATABASE database_name to role_name;


CTR+D(logout from pgsql console)
cd /var/lib/pgsql/

psql -d database_name -f database_name.out

---

如果数据库有打开的连接，这个脚本可能会有所帮助。我每天晚上都使用它从实时生产数据库的备份中创建一个测试数据库。这假设您有一个来自生产数据库的 .SQL 备份文件（我在 webmin 中执行此操作）。

#!/bin/sh

dbname="desired_db_name_of_test_enviroment"
username="user_name"
fname="/path to /ExistingBackupFileOfLive.sql"

dropdbcmp="DROP DATABASE $dbname"
createdbcmd="CREATE DATABASE $dbname WITH OWNER = $username "

export PGPASSWORD=MyPassword



echo "**********"
echo "** Dropping $dbname"
psql -d postgres -h localhost -U "$username" -c "$dropdbcmp"

echo "**********"
echo "** Creating database $dbname"
psql -d postgres -h localhost -U "$username" -c "$createdbcmd"

echo "**********"
echo "** Loading data into database"
psql -d postgres -h localhost -U "$username" -d "$dbname" -a -f "$fname"

---

使用 pgAdmin，断开要用作模板的数据库。然后选择它作为创建新数据库的模板，这样可以避免出现已经在使用的错误。

---

如果要复制整个架构，可以使用以下命令创建 pg_dump：

pg_dump -h database.host.com -d database_name -n schema_name -U database_user --password

当您想导入该转储时，您可以使用：

psql "host=database.host.com user=database_user password=database_password dbname=database_name options=--search_path=schema_name" -f sql_dump_to_import.sql

有关连接字符串的详细信息：https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING

或者然后将其组合在一个衬里中：

pg_dump -h database.host.com -d postgres -n schema_name -U database_user --password | psql "host=database.host.com user=database_user password=database_password dbname=database_name options=--search_path=schema_name”

---

在 pgAdmin 中打开主窗口，然后在 pgAdmin 的主窗口中打开另一个查询工具窗口，

断开要用作模板的“模板化”数据库。

转到查询工具窗口

运行 2 个查询，如下所示

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

（上面的 SQL 语句将终止与 TemplateDB 的所有活动会话，然后您现在可以选择它作为模板来创建新的 TargetDB 数据库，这样可以避免出现已经在使用的错误。）

CREATE DATABASE 'TargetDB'
  WITH TEMPLATE='TemplateDB'
       CONNECTION LIMIT=-1;

---

pgAdmin4：

1.选择要复制的数据库并断开连接

右键单击“断开数据库”

2.在旧数据库旁边创建一个新数据库：

给它一个名字。

在“定义”选项卡中选择第一个表作为模板（下拉菜单）

点击创建，然后左键单击新数据库以重新连接。

---

新版本的 pgAdmin（肯定是 4.30）支持从模板创建新数据库。您需要填充的只是新的数据库名称和现有的模板数据库。

https://i.stack.imgur.com/bjtXp.png

---

使用模板 originaldb OWNER dbuser 创建数据库 newdb；

如果您使用的是 Ubuntu。 1 方式 createdb -O Owner -T old_db_name new_db_name

2 路 createdb test_copy pg_dump old_db_name | psql test_copy

---

尝试这个：

CREATE DATABASE newdb WITH ENCODING='UTF8' OWNER=owner TEMPLATE=templatedb LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' CONNECTION LIMIT=-1;

GL XD

---