Sometimes you may find yourself needing to duplicate a postgres database – complete with schema, data; exactly. Sometimes I need to do this because I want to try out some ideas on an existing database but without the hassle of having to backup and restore or write rollbacks for the changes I want to make.
Luckily, it’s super easy to do this. First ensure that there are not active connections to the source database; and then open the SQL Terminal of your choice and execute:
CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;
This will create a new database, by using the source database as a template.
If you get the message: “ERROR: Database being accessed by other users.” don’t worry; it just means that there are still open database connections, and these will need to be closed before it will work.