Migrate Postgres DBs across cloud providers with pgloader

…or how I migrated from AWS to Azure !

Migrating my EC2 instance

The first thing I migrated was my EC2 instance. I created a VM on Azure, and migrated my code on there. At that point, I still have no deployment process, so I simply created an SSH key for the VM, added it to my Gitlab account, pulled my repo, and ran docker-compose up.I then added a rule to allow for incoming HTTP traffic to the VM, and there: done for the EC2 migration !

Migrating my RDS instance

Now for the RDS instance. This part is the most interesting, as I did not want to lose any of my data.

CREATE DATABASE my_database;CREATE ROLE db_owner nologin;
GRANT db_owner TO admin;
ALTER DATABASE my_database OWNER TO db_owner;
CREATE USER user WITH PASSWORD ‘p455w0rd’;
GRANT db_owner TO user;
GRANT ALL PRIVILEGES ON DATABASE my_database TO db_owner;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO db_owner;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO db_owner;
docker run -it dimitri/pgloader:ccl.latest
LOAD DATABASE
FROM pgsql://username:password@psql_rds_host:port/database
INTO pgsql://new_username:new_password@psql_azure_host:port/database;

DevOps enthusiast, curious about ever evolving tech. Currently working at https://padok.fr/.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store