When you need to create a copy of a database there are several ways to achieve it in PostgeSQL.

TL;DR: createdb -O ownerusername -T originaldb newdb

If you need to clon a database, you need to plan it carefully. Because you approach will change either if your database is under traffic or not. In this article, i’ll assume, your database is not handling trafic at that moment. In the end of this article i’ll share a trick about creating a copy of a database under traffic.

PostgreSQL has a technology: database templates, and lets you to use any database as a database template to create new databases. For instance, you might design a database for a product and create new databases using this template for each customer.

Creating Copy with SQL

Let’s create a database and a table in it as an example.

postgres=# CREATE DATABASE testdatabase;
CREATE DATABASE
postgres=# \c testdatabase
You are now connected to database "testdatabase" as user "postgres".
testdatabase=# CREATE TABLE testtable(a int, b varchar(5));
CREATE TABLE
testdatabase=#

Now we will create copy of it using SQL statement:

testdatabase=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# CREATE DATABASE clonedatabase WITH TEMPLATE testdatabase OWNER postgres;
CREATE DATABASE
postgres=# \c clonedatabase
You are now connected to database "clonedatabase" as user "postgres".
clonedatabase=# select * from testtable;
 a | b
---+---
(0 rows)

As you see, we created clone database using create database with template statement.

Creating Copy with Bash

I’ll drop that database now, and try doing it from command line:

clonedatabase=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop database clonedatabase;
DROP DATABASE

postgres@bbe89c90b5eb:~$ createdb -O postgres -T testdatabase clonedatabase
postgres@bbe89c90b5eb:~$ psql -d clonedatabase
psql (15.3 (Debian 15.3-1.pgdg110+1))
Type "help" for help.

clonedatabase=#

Of course, those two options to create a copy of a database works with database with no applications connected to it.

Copying Database with Traffic

This is not a copying method actually. This is a backup restore method, but under traffic (of course, not under heavy traffic with high IO load) this method is applicable.

pg_dump original_db | psql newdb

Conclusion

If you have any comments, or any question, please do not hesitate to leave a comment below.