How to migrate multiple Postgres databases between cloud environments and local environments

One of the clients I worked with needed to transfer their data from AWS to their on-premise cloud, seemingly a clear and simple task to perform, apparently, it is not. How can you do it? I thought to myself. Of course — with the AWS Data Migration Service, but it didn’t work.

I started to configure the DMS to perform the database migration. In the endpoint configuration tab, I saw that the form asks to specify the database name that needed to be migrated — a specific one. However, my customer’s data includes 49 Postgres databases. I searched, but there was no out-of-the-box option to migrate several databases at once! This meant using the AWS migration service would be inefficient and painstaking work because I would have to migrate the databases one by one.

So, how can I migrate 49 Postgres databases at once? I decided to be creative and build an optimal, dynamic, short, and fast solution. Most importantly, I wanted to create a solution that would help any DevOps engineer tackle such an issue: a simple script that uses  pg_dump to extract data from each database in the Postgres machine and manually copy it to the new destination database.

So, DevOps engineers, this article is for you! I’m sure that even if you don’t need to transfer data right now, the article will give you ideas on how to do that when required easily and quickly.

Why is using a bash script to migrate Postgres databases the optimal solution?

1. Using a bash script to migrate databases is a dynamic solution. You can migrate the databases to and from all server farms, such as Azure, AWS, and even an on-premise cloud.
2. This is a quick and easy solution to run — all you have to do is copy the above code snippet, change the desired variables, and just run it! You will save time filling dozens of inputs with unnecessary information.
3. Cost Savings: While migrating data to cloud environments often involves financial expenses, my migration solution offers a cost-effective alternative. One of the primary cost differences arises from managed service charges. By utilizing Bash script to migrate multiple Postgres databases, you can bypass these charges. However, it’s important to note that the solution isn’t entirely free, as networking expenses may still apply. Depending on your chosen provider, these networking costs can vary.

How to migrate multiple Postgres databases to and from cloud environments and local environments

I created a bash script that moves multiple databases between cloud environments and local environments using pg_dump. The migration script was initially designed for PostgreSQL databases, but the core logic is expected to be compatible with any database when using a dedicated tool. Note: To enable the migration to function, a network connection is required between the source and the script and between the script and the destination.

What is pg_dump?

pg_dump is a utility provided by PostgreSQL, the open-source relational database management system. It is used for creating customizable backups of PostgreSQL databases. This utility allows you to generate a logical snapshot of your database, including its schema (tables, views, indexes, etc.) and data, which can be easily restored later. This capability is particularly valuable for migrating databases between various environments, such as cloud-based setups and local installations.

Installing pg_dump

The pg_dump utility is included with PostgreSQL installations. To use it, you must have PostgreSQL installed on the system where you plan to perform the database migration. If you haven’t installed PostgreSQL yet, here are the steps to do so:

Linux (Ubuntu/Debian)

1. Open a terminal.

2. Update the package list: 

sudo apt-get update

3. Install PostgreSQL and its utilities: 

sudo apt-get install postgresql

 macOS (Homebrew)

1. Open a terminal.

2. Install Homebrew (if not installed): 

`/bin/bash -c 
"$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"`

3. Install PostgreSQL: 

brew install postgresql

Windows

1. Download the PostgreSQL installer from the official website: https://www.postgresql.org/download/windows/

2. Run the installer and proceed by following the installation instructions.

Using pg_dump

After successfully installing PostgreSQL, you can use the pg_dump command to create a backup of your database. The basic syntax is as follows:

bash```
pg_dump -h <hostname> -U <username> -d <databasename> -F c -f <outputfile>
```

– `<hostname>`: The hostname or IP address of the database server.

– `<username>`: Your PostgreSQL username.

– `<databasename>`: The name of the database you want to back up.

– `<outputfile>`: The filename for the backup file.

For example, to create a backup of a database named ‘mydatabase’ and save it as ‘backup_custom_format.backup,’ you would run:

```bash
pg_dump -h localhost -U myuser -d mydatabase -F c -f backup_custom_format.backup
```

Remember to replace the placeholders with your actual values.

Now, you will proceed to create the script:

1. Copy the following script to a new bash file

#!/bin/bash
allDB=("database1"  "database2")
for db in ${!allDB[@]};
do
  db=${allDB[$db]}
  echo $db
  pg_dump -h $from_host -p 5432 -U $from_user postgresql://$from_user:$from_password@$from_host:5432/$db > $db.sql
  psql -h $to_host -p 5432 -U $to_user postgresql://$to_user:$to_password@$to_host:5432/postgres -c "create database $db;"
  psql -h $to_host -p 5432 -U root postgresql://$to_user:$to_password@$to_host:5432/$db < $db.sql
done

2. Row 2: in the var “allDB”, put all the database names you want to migrate in double quotes separated by spaces.

3. Row 7: $from_host appears twice. Enter the name of the VM host from which you want to migrate the databases (the origin VM). For example: cassandra-db-read-replica.cjglzryejc7h.eu-west-1.rds.amazonaws.com

4. Row 7: “$from_user” appears twice. Enter a user with permission to connect to the host of the origin databases, and in the var $from_password, enter the password of this user.

5. Row 8 and 9: repeat step 3 and 4 so that $to_host is the name of the VM host to which you want to migrate the databases (the destination VM). $to_user and $to_password are the authentication credentials to connect to this host.

6. Run this script

How to make sure the databases have migrated successfully

1. Connect to the host with the following command: 

psql -h $to_host -p 5432 -U root postgresql://$to_user:$to_password@$to_host:5432/$db

2. You can see all the databases with the following command: \l
3. You can connect to one of the databases with the following command: \connect $DBNAME. You can see the tables in this database by the following command: \dt
4. I recommend performing steps 2 and 3 on several random databases in order to check that all the databases migrated in their entirety.

In the course of this article, I started with the installation of pg_dump on various operating systems. We’ve also provided a ready-made bash script template to  migrate multiple PostgreSQL databases between different machines. Lastly, we’ve emphasized the importance of verifying the migration’s success by connecting to the destination host, listing the migrated databases, and inspecting their tables.

That’s it! You’ve now got the knowledge to migrate multiple Postgres databases between the cloud and your local environment – the cool and creative way!

Important Note: The migration script was initially designed for PostgreSQL databases, but the core logic is expected to be compatible with any database when using a dedicated tool.