RetroSearch Browse

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.w3resource.com/PostgreSQL/snippets/creating-a-copy-of-a-database-in-postgresql.php below:

Website Navigation

Creating a Copy of a Database in PostgreSQL

Creating a Copy of a Database in PostgreSQLLast update on December 23 2024 07:41:58 (UTC/GMT +8 hours)

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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4