Summary: in this tutorial, you will learn how to copy a PostgreSQL database on the same server or from a 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 so easy to do it via the CREATE DATABASE statement as follows:
1 2 | CREATE DATABASE targetdb WITH TEMPLATE sourcedb; |
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:
1 2 | CREATE DATABASE dvdrental_test WITH TEMPLATE dvdrental; |
Depending on the size of the source database, it may take a while to complete copying.
PostgreSQL copy database from a 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.
Here is the command of each step:
First, dump the source database to a file.
1 | pg_dump -U postgres -O sourcedb sourcedb.sql |
Second, copy the dump file to the remote server.
Third, create a new database in the remote server:
1 | CREATE DATABASE targetdb; |
Fourth, restore the dump file on the remote server:
1 | psql -U postgres -d targetdb -f sourcedb.sql |
For example, to copy the dvdrental database from the local server to the remote server, you do it as follows:
First, dump the dvdrental database into a dump file e.g., dvdrental.sql:
1 | pg_dump -U postgres -O dvdrental dvdrental.sql |
Second, copy the dump file to the remote server.
Third, create the dvdrental database on the remote server:
1 | CREATE DATABASE dvdrental; |
Fourth, restore the dvdrental.sql dump file in the remote server:
1 | psql -U postgres -d dvdrental -f dvdrental.sql |
In case the connection between servers are fast and the size of the database is not big, you can use the following command:
1 | pg_dump -C -h local -U localuser sourcedb | psql -h remote -U remoteuser targetdb |
For example, to copy the dvdrental database from the localhost server to the remote server, you do it as follows:
1 | pg_dump -C -h localhost -U postgres dvdrental | psql -h remote -U postgres dvdrental |
In this tutorial, you have learned how to copy a PostgreSQL database within a database server, or from a database server to anther.