这是我所期待的,但我不得不汇编一下:

我只希望我知道保持同一用户的方式,而不是把它放在文字上。

#!/bin/bash
DB_SRC=conf
DB_DST=conf_test
DB_OWNER=confuser

T="$(date +%s)"

psql -c "select pg_terminate_backend(procpid) from pg_stat_activity where datname= $DB_DST ;" || { echo "disconnect users failed"; exit 1; }
psql -c "drop database if exists $DB_DST;" || { echo "drop failed"; exit 1; }
psql -c "create database $DB_DST owner confuser;" || { echo "create failed"; exit 1; }
pg_dump $DB_SRC|psql $DB_DST || { echo "dump/restore failed"; exit 1; }

T="$(($(date +%s)-T))"
echo "Time in seconds: ${T}"

The simplest and fastest method to create a complete copy of an existing (live) database is to use CREATE DATABASE with a TEMPLATE:

CREATE DATABASE mydb_test TEMPLATE mydb;

然而,有<明星>进口限制违反了你的第二项要求:模板(来源)数据库不能与它有其他链接。 我引用该手册:。

It is possible to create additional template databases, and indeed one can copy any database in a cluster by specifying its name as the template 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 be connected to the source database while it is being copied. CREATE DATABASE will fail if any other connection exists when it starts; during the copy operation, new connections to the source database are prevented.

You can terminate all sessions to the template database if you have the necessary privileges with pg_terminate_backend().
To temporarily disallow reconnects, revoke the CONNECT privilege (and GRANT back later).

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

员额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

如果你无力结束同时举行的届会,就象其他答复所建议的那样,着手计算<代码>pg_dump至psql的产出。

由于你没有说在数据库中投放objects是一个问题,我认为,在-clean的选项下,可操作pg_dump<>。 你们可以把粉.的产量推到粉.中,以达到这种目的。