Creating a Copy of a Database in PostgreSQL

How to Copy a Database in PostgreSQL?

Copying a database in PostgreSQL can be done in several ways, such as using the CREATE DATABASE ... WITH TEMPLATE command, the pg_dump and pg_restore utilities, or psql. Here’s a step-by-step guide on each method, with examples and explanations.

1. Using the CREATE DATABASE Command with TEMPLATE

One way to create an exact copy of a database in PostgreSQL is by using the CREATE DATABASE command with the TEMPLATE option.

Syntax:

CREATE DATABASE new_database WITH TEMPLATE original_database;

Example Code:

-- Create a copy of the database named original_db
CREATE DATABASE new_db WITH TEMPLATE original_db;  -- Copies the structure and data from original_db to new_db

Explanation:

• CREATE DATABASE new_db: Creates a new database called new_db.
• WITH TEMPLATE original_db: Uses original_db as a template, copying all its contents, including schema, data, and roles.

Note: This approach requires that no active connections exist to the template database (original_db). Use this method when both databases are on the same PostgreSQL instance and when you want an identical clone.

2. Using pg_dump and pg_restore to Copy Database Across Servers

If you need to copy a database to a different server, you can use the pg_dump and pg_restore utilities to export and import data.

Steps:

• Export the Original Database: Use pg_dump to create a backup file.
• Import to New Database: Use pg_restore to create the new database from the backup file.

Example Code:

a. Exporting the database:

# Create a backup of original_db to original_db.bak file
pg_dump -U postgres -F c -d original_db -f /path/to/original_db.bak

Explanation:

• pg_dump: PostgreSQL utility to create a database backup.
• -U postgres: Specifies the user (e.g., postgres) to perform the dump.
• -F c: Specifies the custom format, which is useful for pg_restore.
• -d original_db: The name of the database you want to back up.
• -f /path/to/original_db.bak: The output file path for the backup.

b. Restoring the database:

# Restore the backup to create a new database named new_db
pg_restore -U postgres -d new_db /path/to/original_db.bak

Explanation:

• pg_restore: Utility to restore a backup.
• -U postgres: Specifies the user.
• -d new_db: Specifies the new database to restore the backup into.
• /path/to/original_db.bak: The path to the backup file created by pg_dump.

3. Using psql with pg_dump to Copy Database

If pg_restore is not available or if you’re copying a smaller database, you can use pg_dump with psql.

Example Code:

a. Exporting the database:

# Dump original_db to a plain SQL file
pg_dump -U postgres -d original_db -f /path/to/original_db.sql

b. Restoring with psql:

# Create a new database and import the SQL file
createdb -U postgres new_db  # Create the new database
psql -U postgres -d new_db -f /path/to/original_db.sql  # Import data

Explanation:

• pg_dump -U postgres -d original_db -f /path/to/original_db.sql: Creates a plain SQL backup file of original_db.
• createdb -U postgres new_db: Creates an empty database named new_db.
• psql -U postgres -d new_db -f /path/to/original_db.sql: Runs the SQL commands in the backup file, replicating original_db’s structure and data in new_db.

Important Notes

• Permissions: Ensure that the user has sufficient privileges for pg_dump, pg_restore, and database creation.
• Same Server Requirement: The TEMPLATE method works only on the same PostgreSQL instance, while pg_dump and pg_restore can be used across servers.
• Active Connections: Close active connections to the database before using it as a template.

All PostgreSQL Questions, Answers, and Code Snippets Collection.