Creating a copy of a database in PostgreSQL

#database #postgresql

Postgres allows the use of any existing database on the server as a template when creating a new database. I'm not sure whether pgAdmin gives you the option on the create database dialog but you should be able to execute the following in a query window if it doesn't:

1CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

Still, you may get:

ERROR:  source database "originaldb" is being accessed by other users

To disconnect all other users from the database, you can use this query:

1SELECT
2  pg_terminate_backend(pg_stat_activity.pid)
3FROM
4  pg_stat_activity
5WHERE
6  pg_stat_activity.datname = 'originaldb'
7  AND pid <> pg_backend_pid();

source

If this post was enjoyable or useful for you, please share it! If you have comments, questions, or feedback, you can email my personal email. To get new posts, subscribe use the RSS feed.