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.
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics