Postgresql single server to flexible server migration using Azure Portal
The Azure Portal can be used to perform an offline migration of a PostgreSQL Single Server instance to PostgreSQL Flexible Server.
This simple method has lower chances of failure and no restrictions in terms of database objects it can handle.
Limitations
- Will result in downtime for applications.
Prerequisite
- Create target Postgresql flexible server using terraform module. Make sure database names and sizes are identical and equal or larger in size respectively.
- Enable
extensionson flexible server. List of extensions should be identical to single server.
# run SQL query on single server to get list of extensions.
select * from pg_extension;
# Update/Add this `terraform variable` with the list of required extensions.
pgsql_server_configuration = [
{
name = azure.extensions
value = <name of extension eg. plpgsql>
},
{
name = azure.extensions
value = <name of extension eg. pg_stat_statements>
}
]
Set allow access to azure services to “Yes” on Postgresql single server
Within Azure Portal:
- Select the PostgreSQL single server
- Under Settings -> connection security
- Ensure “Allow access to Azure Services” is set to Yes
If not, you may notice error similar to the below during the migration attempt:
bash
S0002: Connecting to the Source DB server failed. ErrorMessage: Validation of one or more databases failed.Failed to run `select 1;` with exception 28000: SSL connection is required. Please specify SSL options and retry.. Parameter name: SourceDBServerResourceId
Migration Steps
AzureDevops pipeline has been created to facilitate migration process. Follow instructions in README.MD file located in repo.
Azure documentation.