w3resource

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.


Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/PostgreSQL/snippets/creating-a-copy-of-a-database-in-postgresql.php