How to migrate PostgreSQL on-premises to Azure – Part2
Welcome to a new post on the Aleson ITC blog. In the first part of this blog series, we saw how to create our Azure Database for PostgreSQL flexible server service.
In this blog, we will look at the prerequisites and configurations to apply before you can migrate
Types of migration
Offline migration: In this type of migration, all applications that connect to the source instance are stopped and the databases are copied to a flexible server.
Online migration: In this type of migration, applications are not stopped while the DBs are copied to a flex server, the initial copy is followed by replication to keep the flex server in sync with the source instance.
The following table shows the advantages and disadvantages of online and offline migration:
First let’s start with the online migration, we go through the points to check.
Check PostgreSQL source version
The source PostgreSQL server version has to be 9.5 or higher, in case you have a lower version, first migrate to 9.5 or higher locally before starting the migration.
Install test_decoding (Source Server)
Test_decoding, is a plugin that saves the changes that have been made to the database and transforms them into a specific/readable format. This plugin is mainly intended to capture and examine the changes made to the database.
Configure Azure server target
Have created our Azure Database for PostgreSQL flexible server service.
The SKU (Stock Keeping Unit) for the service must match the one we have on-premises, this is basically the type of server in terms of hardware. Azure provides three pricing tiers:
Burstable: Workloads that do not need the entire CPU continuously to run.
General Purpose: Workloads that require a balanced memory size with scalable I/O performance, e.g., web and mobile app servers.
Memory Optimised: High performance DB workloads that require in-memory performance to process transactions faster. Example, real-time data processing and data analytics servers.
In the following link you will find more information about these SKU types.
https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-compute
TabTable with the different SKUs
Enable CDC
test_decoding captures the modified source records.
On the source postgres server, set the following parameters in the postgresql.conf file
wal_level = logical
This value allows us to use logical replication functions, such as logical decoding, which allows us to replicate row-level changes.
Just for information, wal_level also has more values.
wal_level = minimal: Records the minimum amount of information required for failover. This level is not suitable for replication.
wal_level = replica: Records sufficient information to support physical replication and failover. This is the default value.
max_replication_slots, set a value greater than 1, the value must be greater than the number of databases selected for migration.
max_wal_senders, set a value greater than 1, must be set to at least the same value as max_replication_slots, plus the number of senders already used.
Ensures that sufficient processes are available to transmit WAL data to all configured replicas.
wal_sender_timeout, this parameter terminates replication connections that are idle for longer than the set number of milliseconds. The default value is 60000 milliseconds (60 seconds), we can set it as we see fit.
These parameters must be put at the end of the postgresql.conf file, just under CUSTOMIZED OPTIONS.
This is an example configuration, the values have to be adjusted according to the scenario we are in.
Network configuration
We have to make sure that the migration service works correctly and also make sure that the source PostgreSQL server can communicate with the destination Azure Database for PostgreSQL server.
Below is a link to the configuration we need to check depending on the scenario we are in.
Additional considerations:
Configuration of pg_hba.conf, to facilitate connectivity between the source and target PostgreSQL instances. This file includes client authentication and must be configured to allow the target PostgreSQL to connect to the source.
This file is located in the data directory of the PostgreSQL installation.
Habilitar extensiones
In this part, we have to make sure that if we have any extensions installed on our source PostgreSQL server they are also available and we can install them on our flexible Azure database for PostgreSQL server.
For more information about PostgreSQL extensions, we leave the following link:
Review custom configuration parameters
If in the PostgreSQL configuration files, we have custom parameters, we have to review and replicate them in azure, and we will be ready to perform our online migration.
With all these configurations we will be ready to perform our Online migration.
Offline Migration
With offline migration, the steps to follow are the same except that we don’t have to enable CDC.
On the other hand, in azure Azure Database fro PostgreSQL flexible server it is important to disable high availability and read replication in the target environment. These functions should only be enabled after the migration is complete.
In both cases, the last step is to create the migration project, but we will see this step in the third part of the blog.
¿Do you need a quick and seamless migration for your business? Contact us!
Data Analyst Associate.
