Saturday, June 7, 2014

Clone a PostgreSQL database

At some point in development, you may want to make a copy of your existing development database to play with an unstable new feature. Postgres makes it super trivial to do that by allow us to create a new database by using existing database as a template.

In postgres console, run
=# CREATE DATABASE new_database_name WITH TEMPLATE original_database_name;

Boom!, you got a cloned database named new_database_name.

Credit: Creating a copy of a database in Postgres
PS: I've seen a convenient way to make a clone across remote server too. (Haven't try) How to copy postgres database to another server

Update Aug 27, 2015:
Instead of running command in psql, we can use createdb with -T flag like so
$ createdb -T original_database_name new_database_name
Credit: http://stackoverflow.com/a/6739995 

5 comments:

Emiliano Zilocchi said...

Nice!. Do you know if this is going to copy data as well? or just the schema.

Tap said...

Thanks. It does copy the data. You'll get the exact same database with the original one.

Bader Bader said...

Hi
clone database is it slower or faster than "pg_restore" utility?

Thanks
Bader

Bader Bader said...

Hi
clone database is it slower or faster than "pg_restore" utility?

Thanks
Bader

Tap said...

I don't know actually, but cloning seems pretty fast to me

Config number of file descriptors on Linux

Check file descriptor limit for a process. Vary depending on owner of the process $ cat /proc/{process-id}/limits Check current number of...