PostgreSQL Copy Database

Summary: In this tutorial, you’ll learn how to copy a database within a PostgreSQL instance or between PostgreSQL servers.

When working with PostgreSQL, you often need to copy or clone a database. For example:

• Creating a backup of your database before making significant changes.
• Setting up a development or testing environment identical to production.
• Migrating data to a new server.

Fortunately, PostgreSQL provides various ways to copy databases efficiently using built-in commands and tools.

Copying Database Within the Same PostgreSQL Instance #

PostgreSQL allows you to make a copy of an existing database using the CREATE DATABASE statement with the TEMPLATE option:

CREATE DATABASE target_db
WITH TEMPLATE source_db
[OWNER role_name];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• Specify the new database’s name in the CREATE DATABASE clause.
• Provide the name of an existing database you want to copy in the WITH TEMPLATE clause.
• Assign the owner to the new database. If you don’t specify the owner, PostgreSQL will assign the role that runs the statement to the new database.

This statement can copy (or clone) a database within the same PostgreSQL instance. Additionally, the source database must not have any active connections.

We’ll show you step by step how to copy a database dev to test within the same PostgreSQL instance:

First, connect to your PostgreSQL server using psql:

psql -U postgresCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, create a new database called dev:

CREATE DATABASE dev;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, switch the current database to dev:

\c devCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Fourth, create a new table t inside the dev database and insert a new row into the t table:

CREATE TABLE t(id INT);
INSERT INTO t(id) VALUES(1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

This step is to demonstrate that you can clone a database completely.

Fifth, create a copy of the dev database to test database:

CREATE DATABASE test
WITH TEMPLATE dev;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If the source database has any active connections, you can check using this query:

SELECT pid, usename, client_addr
FROM pg_stat_activity
WHERE datname ='dev';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

And close all connections to the source database using this query:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'dev';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Then, you can rerun the CREATE DATABASE statement to clone the dev database.

Sixth, switch the current database to test:

\c testCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finally, select data from the t table in the test database:

SELECT * FROM t;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 id
----
  1Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Copying a Database Using pg_dump and pg_restore #

The most common way to copy a PostgreSQL database is by using pg_dump and pg_restore:

• pg_dump exports a database into a file.
• pg_restore restores a database from a dump file.

This method works well for creating a database copy on the same or a different PostgreSQL server.

Step 1: Export the Database with pg_dump #

Run this command to export the source_db database into a file using a custom format:

pg_dump -U username -h host -p 5432 -F c -d source_db -f source_db.dumpCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

• -U username – PostgreSQL user.
• -h host – Database host.
• -p 5432 – Port number.
• -F c – Custom format.
• -d source_db – Source database name.
• -f source_db.dump – Output dump file.

Step 2: Create a New Database #

Create a new database:

psql -U username -h host -p 5432 -c "CREATE DATABASE target_db;"Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Step 3: Restore the Dump to the New Database #

Run this command to restore the source_db backup into target_db:

pg_restore -U username -h localhost -p 5432 -d target_db -F c source_db.dumpCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Copying All Databases Using pg_dumpall #

Sometimes, you may copy all databases from a PostgreSQL instance, e.g., when you want to migrate the PostgreSQL server. In this case, you can use the pg_dumpall tool.

Step 1: Export All Databases into a File #

Run this command to export all databases on a PostgreSQL server to an SQL file:

pg_dumpall -U username -h host -p 5432 > all_databases.sqlCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Step 2: Import All Databases into a Server #

Run the following psql command to import all the databases into a server:

psql -U username -h host -p 5432 -f all_databases.sqlCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)