How to Migrate a PostgreSQL Database to Another Server

When working with PostgreSQL, you might eventually need to transfer a database from one server to another. Despite seeming complex, PostgreSQL migration can be accomplished using PostgreSQL's built-in tools.

This article outlines various methods to transfer a PostgreSQL database from one server to another on Ubuntu 22.04.

Prerequisites

To migrate a Postgres database, you will need:

1. A current server running Ubuntu 22.04 with PostgreSQL installed. The database to be transferred should already exist in PostgreSQL.

2. A new cloud server or virtual machine with Ubuntu 22.04 and PostgreSQL pre-installed. This article uses PostgreSQL version 15. On Hostman, you can easily deploy such a server by choosing the PostgreSQL image when creating a server.

3. Optionally, the pgAdmin client application for connecting and managing PostgreSQL databases. PgAdmin can be installed on any OS, including Windows, Linux, and macOS.

Preparation Before Database Transfer

Before starting the Postgres migration, ensure that both servers have:

1. A network connection between them. Use tools like ping and telnet to verify this (with telnet, check ports 22 and 5432).

2. Ports 22 (SSH) and 5432 (PostgreSQL) open.

3. Enough free disk space.

Configuring PostgreSQL for Remote Connections

Ensure PostgreSQL can accept remote connections on both servers:

1. Edit the postgresql.conf file. If using a different version, replace 15 with your version number:

nano /etc/postgresql/15/main/postgresql.conf

Find the listen_addresses parameter. By default, it is commented out and accepts only local connections (localhost). Allow remote connections from specific addresses or all addresses (for testing purposes, use *):

listen_addresses = '*'

In production environments, specify only the required addresses. 

Save and exit the file.

2. Edit the pg_hba.conf file:

nano /etc/postgresql/15/main/pg_hba.conf

Find the line for IPv4 local connections (# IPv4 local connections) and update it to allow connections from specific addresses or all addresses for testing:

host all all 0.0.0.0/0 md5

Save and exit the file.

3. Restart the PostgreSQL server:

systemctl restart postgresql

Set a strong password for the PostgreSQL user on both servers:

sudo -i -u postgres psql

In the psql console run:

ALTER USER postgres WITH PASSWORD 'nsH7z*0kl>&7?7';

Where postgres is the username and nsH7z*0kl>&7?7 is the password.

Transferring the Database Using pg_dump and psql

Typically, transferring a database involves three steps:

1. Creating a backup of the database.

2. Transferring the backup to the target server.

3. Restoring the database on the new server.

These steps can be performed using PostgreSQL's pg_dump and psql tools.

Transferring a Database

For instance, let's look at transferring a database named e_commerce from a server with IP 166.1.227.252 to a server with IP 91.206.179.207.

Create the target database beforehand:

CREATE DATABASE e_commerce;

Then run:

pg_dump -h localhost -U postgres e_commerce | psql -h 91.206.179.207 -U postgres e_commerce

Explanation:

• pg_dump creates a backup of the database.

• -h localhost specifies the database server address.

• -U postgres specifies the username.

• e_commerce is the database name on the current and new server. 

• psql connects to the remote PostgreSQL server and loads the database.

• -h 91.206.179.207 specifies the target server address.

Transferring Without Remote Access

If remote access is unavailable, save the database to a file, transfer it using scp, and restore it on the target server:

pg_dump -h localhost -U postgres e_commerce > e_commerce.sql && scp e_commerce.sql [email protected]:/var/lib/postgresql

When executing the command, the password for the postgres system user will be requested, not the password for the postgres user defined within the database.

Where:

• pg_dump creates a database backup;

• -h localhost is the address of the server (IP address or domain name) where the database is located. In this example, the database server address matches the server address itself. If the database is on another server and network access is available, you can specify the address of the remote database;

• -U postgres is the username used to connect to the database;

• e_commerce is the name of the database to be transferred;

• e_commerce.sql is the name of the file in .sql format where the database will be saved;

• scp is a utility for secure file copying between hosts. It uses the SSH protocol for data transfer and protection;

• [email protected]:/var/lib/postgresql means username_on_remote_server@address_of_remote_server:full_path where the backup file will be saved.

After entering the command, you first need to enter the password for the database user account (in this example, it is the postgres user), and then enter the password for the remote server user (in this example, it is the db-admin user).

Now you need to upload the file to the database. Run these commands on the target server.

Create a database in psql:

CREATE DATABASE e_commerce;

Then, exit psql and run in the terminal:

psql e_commerce < e_commerce.sql

Creating a Compressed Archive

For larger databases, create a compressed archive:

pg_dump -h localhost -U postgres e_commerce > e_commerce.tar.gzip && scp e_commerce.tar.gzip [email protected]:/var/lib/postgresql

Restore from the archive:

psql e_commerce < e_commerce.tar.gzip

Adding a Timestamp to the Archive Name

If you need to know the exact date and time that the database was backed up, you can include it in the name of the file being created. 

To do this, use the date command and the date format. The example below will use the day-month-year date format:

pg_dump -h localhost -U postgres e_commerce > e_commerce_$(date +%d-%m-%y).sql

Transferring the Database Using pgAdmin

Alternatively, you can use pgAdmin's graphical interface for the Postgres database migration.

Backup Database

1. Launch pgAdmin: Open pgAdmin and connect to your PostgreSQL server.

2. Register Server: Right-click on Object Explorer, select Register, then Server.

3. Configure Connection:

• Name: In the General tab, enter a name for the connection (e.g., my_db).

Next, go to the Connection tab and specify:

1. • Host name/address: Specify the IP address or domain name of the PostgreSQL server.

   • Port: Default is 5432; change if needed.

   • Maintenance database: Name of the database for backup.

   • Username and Password: Enter credentials for database access.

4. Connect: Click Save to connect. If successful, the database appears on the left sidebar.

5. Backup Database:

   • Right-click on the database name and select Backup.

 

• • Set a Filename for the backup file.

  • Choose a Format and Encoding (UTF8 recommended).

  • Select specific Objects to include.

• • Click Backup to start.

Restore Database

1. Prepare New Database:

   • Open psql shell.

   • Execute:

CREATE DATABASE e_commerce;

2. Connect to PostgreSQL Server:

   • In pgAdmin, connect to the new PostgreSQL server, selecting e_commerce as the database.

3. Restore Database:

   • Right-click on the database name and choose Restore.

• • Set the Format (ensure it matches the backup file).

  • Specify the Filename of the backup file.

  • Click Restore to begin.

Wait for the Process completed confirmation.

Conclusion

PostgreSQL offers several methods to migrate databases between servers, including using built-in tools for flexible and robust database backups and transfers.