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.

Anvesh Patel

Leave a Reply

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

Notify of
Sort by:   newest | oldest | most voted
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 »

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