Migrating Your PostgreSQL Databases Between Servers

Recently, I switched from a US-based server provider to a European provider. This was mainly for performance and compliance reasons. However, moving my databases was a bit daunting at first. After some research and testing, I found a workflow that is both efficient and easy to follow.

Step 1: Deploy the Application

The first step is to deploy your application on the new server. Most frameworks and platforms will automatically generate empty tables in the database when the application runs for the first time. Once the tables exist, the next step is moving your actual data.

Step 2: Export Data from the Source Database

I log into the source database and run the following command:

pg_dump --data-only --disable-triggers old_db > data.sql

This command generates a SQL file containing only the data, without the table structure. The --disable-triggers flag ensures that triggers are temporarily disabled during the import, which speeds up the process and avoids potential errors.

Step 3: Transfer the Data File

Once the SQL file is generated, we need to move it to the new server. I usually use scp for this:

scp source_db:/home/forge/data.sql target_db:/home/forge/data.sql

Make sure to replace source_db and target_db with your actual server names or IP addresses. This securely copies the SQL file from the old server to the new one.

Step 4: Import Data into the Target Database

Now that the SQL file is on the target server, importing it into the new database is straightforward:

psql new_db < data.sql

This command inserts all the data into new_db. Remember, since we used --data-only earlier, this import does not touch table structures or schema—only the data itself.

Final Thoughts

Migrating databases can seem intimidating, but with this workflow, it’s relatively painless. Always make sure you have backups, especially when dealing with production data. Using pg_dump and psql makes the process fast and reliable. This approach can save you a lot of time compared to more manual methods, and it works perfectly for large datasets.