How to create copy of existing database in PostgreSQL

In this article, we will see how to create copy of existing database in PostgreSQL.

Postgres allows the use of any existing database on the server as a template when creating a new database.

Syntax to create copy of PostgreSQL Database:

CREATE DATABASE newdb WITH TEMPLATE existing_database OWNER db_user;

If the “existing_database” is being accessed by a user(s), then we have to disconnect all other users from the database by using below query:

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

1. Create copy of existing database in PostgreSQL using SQL statement:

1. Lets verify the list of databases available in your cluster.

\l 
or 
select  datname from pg_database;

2. Create database ‘sales’ by cloning existing database ‘r2schools’.

CREATE DATABASE sales WITH TEMPLATE r2schools owner postgres;

It has thrown below error. Because there are some active connections to the database.

ERROR:  source database "r2schools" is being accessed by other users
DETAIL:  There is 1 other session using the database.

Remedy for the above error is, we have to disconnect the connections of the copy database. Run the below command.

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

After connections disconnect, try to create the the database.

CREATE DATABASE sales WITH TEMPLATE r2schools owner postgres;

2. Create copy of existing database in PostgreSQL using pgadmin:

1. Connect to pgAdmin, then right click on database select CREATE –> Database

2. Provide name as ‘sales’

3. In this definition tab, select template as r2schools.

4. Then, click on save to create copy of existing database in PostgreSQL.