How to Migrate a PostgreSQL Database to Another Server

It is possible that you are going to need to move a database from one PostgreSQL server to another. Although it may appear complicated, PostgreSQL migration is possible with PostgreSQL's built-in utilities.

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

Visualization of moving PostgreSQL database

Prerequisites

Copy link

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 affordable cloud 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

Copy link

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

Copy link

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

Copy link

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

Copy link

Let's take an example of moving a database called e_commerce from a server equipped with IP address 166.1.227.252 to a server equipped with IP address 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

Copy link

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 db-admin@91.206.179.207:/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;

• db-admin@91.206.179.207:/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

Copy link

For larger databases, create a compressed archive:

pg_dump -h localhost -U postgres e_commerce > e_commerce.tar.gzip && scp e_commerce.tar.gzip db-admin@91.206.179.207:/var/lib/postgresql

Restore from the archive:

psql e_commerce < e_commerce.tar.gzip

Adding a Timestamp to the Archive Name

Copy link

You can include the precise date and time the database was backed up in the file name if you need to know that information.

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

Copy link

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.

A quick representation of terminal when moving PostgreSQL Database

Conclusion

Copy link

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

If you've ran into some troubles, check our instruction on how to create server on Ubuntu.

And if you’re looking for a reliable, high-performance, and budget-friendly solution for your workflows, Hostman has you covered with Linux VPS Hosting options, including Debian VPS, Ubuntu VPS, and VPS CentOS.

Frequently Asked Questions (FAQ)

Copy link

How do I move a Postgres database to another server? 

Copy link

The most common method for smaller databases is using pg_dump to export the data and psql to import it.

• Export (Source): pg_dump -U username -h localhost dbname > backup.sql

• Transfer: Copy the backup.sql file to the new server (e.g., via scp).

• Import (Target): psql -U username -h localhost -d new_db < backup.sql

What are the three main DB migration strategies?

Copy link

1. Big Bang (Offline): The entire system is taken offline, data is exported, moved, and imported. It is simple but requires significant downtime.

2. Trickle (Replication/Zero-Downtime): The new database is set up as a replica of the old one. Once they are synchronized, you switch the application to the new DB.

3. Dual-Write: The application is modified to write data to both the old and new databases simultaneously during the transition period.

PostgreSQL database migration checklist 

Copy link

Before switching over, ensure you have verified:

• Versions: Are the source and target Postgres versions compatible? (Newer targets can usually read older dumps, but not vice-versa).

• Extensions: Are all required extensions (like PostGIS or pgcrypto) installed on the target server?

• Users & Roles: Have you migrated the global users? (Standard pg_dump does not include users; use pg_dumpall --globals-only).

• Connectivity: Is the firewall on the new server configured to accept connections from your application?

• Collation/Encoding: Do both servers use the same locale (e.g., UTF-8) to prevent data corruption?

How do I migrate users and passwords? 

Copy link

Since pg_dump only backs up a specific database, it skips global data like users. To migrate roles, run: pg_dumpall -U postgres --globals-only > globals.sql Then restore this file on the new server before importing your database.

What is the difference between pg_dump and pg_basebackup?

Copy link

• pg_dump: Creates a logical backup (SQL commands). It is portable and works across different OS/versions but is slower for massive datasets.

• pg_basebackup: Creates a physical binary copy of the database files. It is faster for large databases but requires the OS and Postgres versions to be identical.