How to clone a test database from a production one in one single action?

Question

I am looking for a basic script/command that will create a copy of a live database (let name them mydb and mydb_test, both on the same server).

Requirements

• it has to run even if the mydb_test already exists and have records
• it has to work even if mydb and mydb_test do have existing connections
• it have to clean the potentially existing database if necessary

Hints:

• drop database cannot be used if you have existing connections

Answer 1

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;

However, there is an important limitation violating your second requirement: the template (source) database cannot have additional connections to it. I quote the manual:

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

In versions before Postgres 9.2 use procpid instead of pid:

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

Related:

• Force drop db while others may be connected

---

If you cannot afford to terminate concurrent sessions, go with piping the output of pg_dump to psql like has been suggested by other answers already.

Answer 2

That's what I was looking for, but I had to compile it myself :P

I only wish I knew a way to keep the same user and not having to put it inside the script.

#!/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}"

Answer 3

Since you didn't say it was a problem to drop objects in the database, I think running pg_dump with the --clean option will do what you want. You can pipe the output of pg_dump into psql for this sort of thing.

Answer 4

Are you looking into the Hot Standby with Streaming Replication here?