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:
CREATE DATABASE newDB WITH TEMPLATE oldDB OWNER dbUser;
After executing this,
If you get any error, you should kill all the running sessions of that existing database.
ERROR: source database "originaldb" is being accessed by other users
You can use this script to kill all running sessions:
You can also visit this article.
WHERE datname = 'datbase_name'
AND pid <> pg_backend_pid();
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 dbrnd.com, 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.