In our journey to consolidate various self-hosted servers, we recently encountered the need to migrate a PostgreSQL database from one server to another. In this blog post, we will walk you through the detailed process of achieving a smooth and efficient migration, emphasising the use of pg_dump for the data export.

Pre-migration Preparation

Before diving into the migration process, ensure you have PostgreSQL installed locally using Homebrew. If you haven't installed it yet, follow the instructions on the PostgreSQL Wiki page dedicated to Homebrew: PostgreSQL Homebrew Installation.

brew install postgresql

Reference :: https://wiki.postgresql.org/wiki/Homebrew

Step 1: Exporting Database Dump with pg_dump

To begin the migration, we used Datagrip for this example, but the same process can be executed using the pg_dump functionality. Follow these steps:

1. Connect to the existing database using Datagrip or your preferred PostgreSQL client.
2. Navigate to the desired table on the left side of the interface.
3. Right-click on the table name, choose 'Import/Export,' and select 'Export with pg_dump.'
4. A popup window will appear, allowing you to configure different settings.
5. If using Datagrip, the process will be visualised, showing the progress of the data dump.

/opt/homebrew/bin/pg_dump --dbname=clickvision --schema=public --table=public.\"{{table_name}}\" --file=/{{path}}/{{file_name}}.sql --create --username={{username}} --host={{db_host}} --port={{port}}

If Datagrip is unavailable, you can execute this command directly from the terminal.

Step 2: Importing Dump into the New Database

To import the dump file into the new database, execute the following command:

psql postgres://{{username}}:{{password}@{{hostname}}:{{port}}/postgres -f {{file_name}}.sql

This command will seamlessly import all data from the dump file into the new database.

Conclusion

Migrating a PostgreSQL database doesn't have to be a daunting task. By leveraging the power of pg_dump and following these step-by-step instructions, you can ensure a smooth transition from one server to another. Stay tuned for more insightful content on data engineering in our upcoming blog posts!