How to Migrate On-Premises PostgreSQL to Azure – Part 1
In this series of 3 posts about PostgreSQL we are going to see how to migrate our PostgreSQL On-Premise server to Azure.
In this first post we will create our Azure Database for PostgreSQL – Flexible Server and configure it to be accessible from pgadmin.
Before we start we need to know the 3 types of services that Azure offers.
PostgreSQL Service Types in Azure
Azure Database for PostgreSQL – Single Server
This is a service that is managed with minimal DB configuration requirements. This service is designed for most database administration functions, i.e. backups, high availability, security with minimal user configuration and setup.
This service was made available in 2018, but due to new developments, availability, scalability and performance functionalities. For this reason, as of November 2023, the creation of new Single Server instances is no longer supported.
Compatible with PostgreSQL version 10 and 11.
But this type of service will be supported by Microsoft until March 2025, so it is important that, if we have our server on this type of service, we migrate it to Flexible Sever.
Azure Database for PostgreSQL – Flexible Server
This service provides greater flexibility over database administration functions and configuration options. In general, this service provides greater flexibility and customisations of server configuration based on user requirements.
Flexible server architecture allows users to opt for high availability within a single availability zone or across multiple availability zones.
Supports postgres 11, 12, 13, 14, 15 and 16.
Azure Database for PostgreSQL – Hyperscale (Citus)
Existing Hyperscale (Citus) server clusters were automatically converted to Azure Cosmos DB for PostgreSQL clusters under the new name in October 2022. All features and pricing, including reserved compute pricing and regional availability, were retained under the new name.
Here are some links that can help us to get more information.
Comparison table single server vs. flexible server
Brief overview on Single Server and Flexible Server
Now that we know the three types of services offered, we will proceed to create our service in azure.
How to create our PostgreSQL Flexible Server service
First of all, we go to our azure account and look for postgres.
The following page will open:
We click on create and then we configure it according to our needs, in this case, as it is a test, we will select the Development type.
We inform the marked fields, name, region and the version of postgres we are going to install the last one the 16.
Below we have to select our authentication type.
In the case of postgres, inform the user and password and click Next: Networking.
In the networking part we select if we want to give public or private access, in this case we are going to select public and below in Firewall rules we can select the public ip to which we want to give access. We also have the option to Add current client IP address. This will add our public IP address to the firewall.
Once the networking part is configured, click on Next: Security.
Here we have to select the certificate with which we want to encrypt our data.
- Service-managed key, i.e. managed by Microsoft.
- Customer-managed key, i.e. our own certificate.
The difference between the two is that SMK is managed entirely by Microsoft and cannot be modified or disabled by customers.
The CMK, however, provides greater control over the encryption of your data, allowing customers to manage their own encryption keys, which can be revoked if necessary.
But for this test, we will check Service-managed key.
Finished with the Security part, click on Next: Tags.
The tags are used to organise, classify and allow us to keep track of our environment. For example, if we want to see the invoicing of our production environment, we can filter by the production tag and see the cost.
Once we have finished with the labels, we click on Next: Review + create.
And we will see a summary of all the configuration we have applied.
Check that everything is OK and click on Create.
The creation of the service will take a few minutes, just wait.
Connecting from Pgadmin to our PostgreSQL Flexible service in Azure
To be able to connect to our postgres service in azure, we need to register a server on our premises.
We open our pgadmin, right click on Servers – Register – Server
We report the name of the connection.
In the tab of Connection, we inform the host name or ip, port, BBDD to which we are going to connect by default is going to be postgres, user and pass.
The rest of the tabs we leave them by default, informed of the necessary fields we click on save, when saving what postgres is going to do is to make a first connection test.
Possible error that can give us when connecting
FATAL: no pg_hba.conf entry for host. To fix this error, we first need to check our postgres service. In the networking configuration we have added our public IP from which we are trying to access.
Second: we have to download our SSL certificate, and leave it in a path from which we have access with our local postgres.
And in the configuration of our postgres server, in the tab parameters
Click on save, and with this new configuration our connection problem will be solved.
Once it connects, we will see that the following databases appear in the new server.
Azure_maintenance and azure_sys, these are the azure DBs that cannot be deleted and are necessary for the correct functioning of our service in azure.
We can launch the following query, to see that it works correctly.
And we will also see the version of postgres that we have in azure.
And with this we would be connected to our azure service correctly.
In this entry we have seen how to create our Azure Database for PostgreSQL – Flexible Server and how we have connected to it from pgadmin. In the next post we will talk about the pre-configuration needed for migration, roles and users.
Do you need a quick and seamless migration for your business? Contact us!
Data Analyst Associate.
