在PostgreSQL中创建数据库的副本

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

455013 次浏览
小开

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

psql mydatabase < my dump

以还原所有表及其数据以及所有访问权限。

小开
最佳答案

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_activityWHERE pg_stat_activity.datname = 'originaldb' AND pid <> pg_backend_pid();
小开

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

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

贝尔的回答的命令行版本：

createdb -O ownername -T originaldb newdb

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

小开

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

答复：

CREATE DATABASE newdb WITH TEMPLATE originaldb;

尝试和测试。

小开

PostgreSQL 9.1.2：

$ CREATEDB new_db_name -T orig_db_name -O db_user;
小开

对于那些仍然感兴趣的人，我想出了一个bash脚本，它（或多或少）符合作者的要求。我不得不在生产系统上制作日常业务数据库副本，这个脚本似乎很有用。记得更改库名/user/pw值。

#!/bin/bash
if [ 1 -ne $# ]thenecho "Usage `basename $0` {tar.gz database file}"exit 65;fi
if [ -f "$1" ]thenEXTRACTED=`tar -xzvf $1`echo "using database archive: $EXTRACTED";elseecho "file $1 does not exist"exit 1fi

PGUSER=dbuserPGPASSWORD=dbpwexport 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 ]] ; thenrm -rf "$EXTRACTED"echo "error occured while creating database $dbname ($rc)"exit $rcfi

echo "loading data into database"psql $dbname < $EXTRACTED > /dev/null
rc=$?
rm -rf "$EXTRACTED"
if [[ $rc != 0 ]] ; thenpsql -c "$dropdbcmd"echo "error occured while loading data to database $dbname ($rc)"exit $rcfi

echo "finished OK"
小开

如果数据库有打开的连接，此脚本可能会有所帮助。我使用它每晚从实时生产数据库的备份中创建测试数据库。这假设您有来自生产数据库的.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断开要用作模板的数据库。然后选择它作为创建新数据库的模板，这可以避免出现已经在使用中的错误。

小开

试试这个：

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

小开

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

/* KILL ALL EXISTING CONNECTION FROM ORIGINAL DB (sourcedb)*/SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activityWHERE 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;

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

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

复制“负载不足”数据库

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

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

以下是我的工作（前缀#0的命令将输出移动到文件中并防止服务器断开连接）：

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

    我的用户是“postgres”

  3. nohup psql exampledbclone_01 < example-01.sql
小开

在正式生产环境中，原始数据库处于流量状态，我只是使用：

pg_dump production-db | psql test-db
小开

创建数据库转储

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

重新分配数据库转储 

psql -d template1CREATE 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
小开

首先，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
小开

从留档开始，不鼓励在模板中使用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设置。但是我刚刚发现了备份角色的技巧。角色在集群级别进行管理，您可以要求pg_dumpall使用--roles-only命令行开关仅备份角色。

小开

如果您想复制整个模式，您可以使用以下命令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”
小开
  1. 在pgAdmin中打开主窗口，然后打开另一个查询工具窗口
  2. 在pgAdmin的主窗口中，

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

  1. 转到查询工具窗口

运行如下2个查询

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

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

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

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

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

复制步骤

1.创建备份

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

在此处输入图片描述

2.为文件命名。

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

在此处输入图片描述

3.查看下载位置

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

在此处输入图片描述

4.找到下载文件的位置

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

在此处输入图片描述

5.从pgadmin恢复备份

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

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

在此处输入图片描述

6.选择文件查找器

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

在此处输入图片描述

7.查找所述文件

您可能必须将底部的过滤器更改为“所有文件”。然后从第4步找到该文件。现在点击底部的“选择”按钮以确认

在此处输入图片描述

8.恢复上述档案

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

在此处输入图片描述

9.成功

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

10.如果不成功：

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

在此处输入图片描述

执行此代码块：

DROP SCHEMA public CASCADE; CREATE SCHEMA public;

在此处输入图片描述

现在再次尝试步骤5到9，它应该工作

编辑-一些额外的注释。如果您在上传过程中遇到错误，请更新PGADMIN4，在恢复过程中使用类似于“存档头1.14不受支持的版本”的内容

小开

pgAdmin4：

1.选择要复制的DB并断开连接

右键单击断开数据库

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

  • 给它一个名字。
  • 在“定义”选项卡中选择第一个表为模板（下拉框）

点击创建，只需左键单击新数据库即可重新连接。

小开

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

输入图片描述

小开

使用模板原始db所有者dbuser创建数据库newdb；

如果您使用Ubuntu。1路Createdb-O Owner-Told_db_namenew_db_name

2路createdbtest_copypg_dumpold_db_name|psqltest_copy


提交答案