PostgreSQL Database Branching Guide: Two Methods Compared

This guide covers two methods for creating a branch (clone) of a PostgreSQL database for feature development, testing, or isolation purposes.

Method 1: CREATE DATABASE WITH TEMPLATE

This method uses PostgreSQL's built-in template functionality to create a byte-for-byte copy of a database.

Steps

1. Connect to a maintenance database (not the one you're copying):

   psql -U your_username -h localhost -p 5432 -d postgres
   

2. Check and terminate existing connections:

   -- Check connections
   SELECT pid, datname, usename, application_name, client_addr, state
   FROM pg_stat_activity
   WHERE datname = 'source_database_name';
   
   -- Terminate if needed
   SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE datname = 'source_database_name'
   AND pid <> pg_backend_pid();
   

3. Create the new database:

   CREATE DATABASE new_database_name
   WITH TEMPLATE source_database_name
   OWNER database_owner
   

Pros

• Speed: Much faster, especially for large databases
• Completeness: Perfect byte-for-byte copy including all objects and settings
• Simplicity: Single command, no intermediate files
• Consistency: Guaranteed atomic operation

Cons

• Connectivity: Requires no active connections to source database
• Server limitation: Must be done on the same PostgreSQL server
• Permissions: Requires elevated permissions (CREATEDB or superuser)

Method 2: pg_dump and Restore

This method exports the database to a SQL dump file and imports it into a new database.

Steps