Intent is to migrate postgres DB Version 12 installed in Ubuntu System to Azure Postgres DB
1 st step, we need to install Postgres 12 in Ubuntu
Installing PostgreSQL 12 on Ubuntu
Run the following command to create the file repository configuration:
sudo usermod -aG sudo postgres sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' Import the repository signing key by running the following command
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - Update the package lists apt a.k.a Advanced Packaging Tool by running the following command
sudo apt update
sudo apt -y install postgresql-12
Add Postgres User to SUDO Group
sudo usermod -aG sudo postgres
Now we are ready with source postgres db in ubuntu
Now, enter the following command to into the PostgreSQL shell:
psql
CREATE DATABASE mytestdb;
CREATE USER mytestuser WITH ENCRYPTED PASSWORD 'postgres';
GRANT ALL PRIVILEGES ON DATABASE mytestdb to mytestuser;
sudo nano /etc/postgresql/12/main/postgresql.conf
sudo systemctl restart postgresql
Server is ready
DB server is ready now
Creating blank DB in Destination Server
Next we need to create Replicatio role
create role db_migrationuser with login superuser createdb createrole inherit replication connection limit -1 password 'postgres'
grant postgres to db_migrationuser
new role created in onprem DB
need to do some config changes
sudo nano /etc/postgresql/12/main/pg_hba.conf
Use pg_dump -s command to create a schema dump file for a database.
pg_dump -O -h 20.29.58.169 -U postgres -d mytestdb -s > mytestdb_schema.sql;
This is the schema content
Deploying this schema to Azure destinatio database
psql -h vmpostgresazure.postgres.database.azure.com -U postgres -d mytestdb < mytestdb_schema.sql
you can see schema created in destination Azure DB
Remember, only schema is loaded
Next step is to create DMS for migration
Created a migration project in DMS
I am migrating only one database
Migration initiated
Migration is successfully completed for one table
Now I am going to add new rows in Ubuntu Onprem DB
Immediately all new records migrated to Azure DB
Migration of table data and incriment load is working fine
Now I am going to edit this project and adding new table in ubuntu postgres DB
Created new table and added few records in source DB
We need to add new activity for the new migration and use pg_pump to push new schema changes to destination DB
for existing tables, you will get error message, but schema will copy to destination DB
Now come back and proceed with new activity to include new table
If existing table, it will truncate data
Both tables are loaded fully in destination DB
You can see list resources used for this live DB migration activities
