Is there a simple way to create a copy of a database or schema in PostgreSQL 8.1?
I'm testing some software which does a lot of updates to a particular schema within a database, and I'd like to make a copy of it so I can run some comparisons against the original.
If it's on the same server, you just use the CREATE DATABASE command with the TEMPLATE parameter. For example:
CREATE DATABASE newdb WITH TEMPLATE olddb;
This can be done by running the following command:
CREATE DATABASE [Database to create] WITH TEMPLATE [Database to copy] OWNER [Your username];
Once filled in with your database names and your username, this will create a copy of the specified database. This will work as long as there are no other active connections to the database you wish to copy. If there are other active connections you can temporarily terminate the connections by using this command first:
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = '[Database to copy]'
AND pid <> pg_backend_pid();
A good article that I wrote for Chartio's Data School which goes a bit more in depth on how to do this can be found here: https://dataschool.com/learn/how-to-create-a-copy-of-a-database-in-postgresql-using-psql