PostgreSQL: Script to create a copy of the Existing Database

In this post, I am providing script to make a copy of the PosgreSQL database.

We can also create copies of the database using pgAdmin tool, but I always preferred to use and create SQL script to perform this kind Database Administrator activities.

In the development environment, I am following one practice that after every release of the sprint, I take backup of database and as well as create previous version of the database.

If any user wants to check existing functionality in the previous version, they can use this old database version.

Below is a script to make a copy of an existing database:

After executing this,
If you get any error, you should kill all the running sessions of that existing database.

You can use this script to kill all running sessions:
You can also visit this article.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

Leave a Reply

2 Comments on "PostgreSQL: Script to create a copy of the Existing Database"

Notify of

Sort by:   newest | oldest | most voted
7 months 27 days ago
Hello Anvesh Patel. Thanks q so much for that sharing , but i have a question , after i follow you script i’ve successfully copied new db by run the follow script: create database newdb with template chatdevdb owner chat_real; But after I check the tablespace of both databases (chatdevdb(old),newdb(new)) they are stored in the same tablespace and the data size is different: List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description ———–+———–+———-+————+————+——————-+———+————–+——————————————– a | pgdba | UTF8 | ko_KR.utf8 | ko_KR.utf8 | | 6803 kB | pg_default… Read more »
7 months 22 days ago

We want to say thnx you for creating this website and keep up the great work!