Summary: in this tutorial, you will learn how to copy a PostgreSQL database on the same server or from one server to another.

PostgreSQL copy database within the same server
Sometimes, you want to copy a PostgreSQL database within a database server for testing purposes.
PostgreSQL makes it easy to do so via the CREATE DATABASE
statement, as follows:
CREATE DATABASE targetdb
WITH TEMPLATE sourcedb;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
This statement copies the sourcedb
to the targetdb
. For example, to copy the dvdrental
sample database to the dvdrental_test
database, you use the following statement:
CREATE DATABASE dvdrental_test
WITH TEMPLATE dvdrental;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Depending on the size of the source database, copying may take some time to complete.
If the dvdrental
database has active connections, you will encounter the following error:
ERROR: source database "dvdrental" is being accessed by other users
DETAIL: There is 1 other session using the database.
Code language: HTTP (http)
The following query returns the active connections:
SELECT pid, usename, client_addr
FROM pg_stat_activity
WHERE datname ='dvdrental';
Code language: JavaScript (javascript)
To terminate the active connections to the dvdrental
database, you use the following query:
SELECT pg_terminate_backend (pid)
FROM pg_stat_activity
WHERE datname = 'dvdrental';
Code language: JavaScript (javascript)
After that, you can execute the CREATE TABLE WITH TEMPLATE
statement again to copy the dvdrental
database to dvdrental_test
database.
PostgreSQL copy database from one server to another
There are several ways to copy a database between PostgreSQL database servers.
If the size of the source database is big and the connection between the database servers is slow, you can dump the source database to a file, copy the file to the remote server, and restore it:
First, dump the source database into a file.
pg_dump -U postgres -d sourcedb -f sourcedb.sql
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Second, copy the dump file to the remote server.
Third, create a new database in the remote server:
CREATE DATABASE targetdb;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Finally, restore the dump file on the remote server:
psql -U postgres -d targetdb -f sourcedb.sql
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Copying the dvdrental database example
The following steps illustrate how to copy the dvdrental
database from the local server to the remote
server.
First, dump the dvdrental
database into a dump file such as dvdrental.sql
:
pg_dump -U postgres -O dvdrental -f dvdrental.sql
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Second, copy the dump file to the remote
server.
Third, create the dvdrental
database on the remote
server:
CREATE DATABASE dvdrental;
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Fourth, restore the dvdrental.sql
dump file in the remote
server:
psql -U postgres -d dvdrental -f dvdrental.sql
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
If the connection between the servers is fast and the size of the database is not big, you can use the following command:
pg_dump -C -h local -U localuser sourcedb | psql -h remote -U remoteuser targetdb
Code language: Shell Session (shell)
For example, to copy the dvdrental
database from the localhost
server to the remote
server, you can execute the following command:
pg_dump -C -h localhost -U postgres dvdrental | psql -h remote -U postgres dvdrental
Code language: Shell Session (shell)
In this tutorial, you have learned how to copy a PostgreSQL database within a database server, or from one database server to another.