Migrating to Azure Database for PostgreSQL server

When migrating Relatude CMS databases to Azure there are a few things to consider before you get started. This article explains one possible way of migrating databases successfully.

Why use PostgreSQL?

PostgreSQL is an enterprise level database provider with much lower hostings costs than MSSQL. We created a blog post about this some time ago, comparing the two providers.

At Relatude we host most of our infrastructure and services in Azure and although PostgreSQL does have some quirks that Microsoft products does not have (depending on if you've been in the Linux or Microsoft camp), we believe that PostgreSQL is a very good alternative which we use and will use even more in the future.

Azure and non-english Locale and Collations

Azure Database for PostgreSQL is a database server located in the Azure region you select during configuration. This configuration does not give you an option to specify Locale or Collation for the database server. 

This means that creating new databases will have a limited set of installed character sets and collations available.

Using pgAdmin 4 to create a new database will only allow you to specify "C", "POSIX" or "English_United States.1252" as values for Collation and Character type.

After you have created atleast one database using the method specified in this article you'll be able to use the pgAdmin-interface to create databases with the correct settings.

Azure Cloud Shell

The simplest way to be able to connect to the database server using CLI is from Azure Cloud Shell which is available in the Azure web portal.

The first time you open this shell it will require that you create or connect a storage to the profile to serve as a home directory for the profile so that you can work with and save files.

You can also specify if the CLI will use Powershell or Bash command shell. This tutorial is based on using Powershell.

Creating the first database using CLI

First we'll connect to the database server using the following command:

psql "host=<yourdatabaseserver>.postgres.database.azure.com port=5432 user=<yourdatabaseuser> dbname=postgres sslmode=require"

The sslmode parameter is optional, depending on your server configuration, but you should require SSL if there are no good reason to not do it.

Once connected you're ready to create the new database. There might be several other ways to complete this task using other LC_COLLATE and LC_CTYPE values, but this works as well:

CREATE DATABASE new_database
WITH TEMPLATE = template0
ENCODING = 'UTF8'
LC_COLLATE = 'nb_NO.UTF8'
LC_CTYPE = 'nb_NO.UTF8'; 

This will create a database for use with norwegian data. You will need to look up the correct values for your database.

When pressing enter to execute the command, you might see some warning or information about the use of underscore in the values and that a hyphen will be used instead. This will still work, and you can not replace nb_NO.UTF8 with nb-NO.UTF8 even though the warning suggests that it might be more correct.

Using pgAdmin to browse the databases now will reveal the new database and when inspecting the database properties you will see the collation being set correctly.

After the first database is created using CLI, you can try to create a new database using pgAdmin. You should now be able to select the new character set and collation.

Migrating data from MSSQL to PostgreSQL

You can use the Relatude setup program for downloading databases from a live site.

After selecting the site to download you specify how much of the site you want to download. In this case we only need the database, so choose that.

The database will be save in a .site-file. You can use the setup program to restore the .site-file directly to the azure PostgreSQL database, but we recommend that you restore it locally to your local PostgreSQL installation, perform a backup and a restore to the Azure database using PostgreSQL commands and utilities. This will be much faster.

Backing up the local postgres database using pg_dump

On a windows PC use Powershell and navigate to the bin folder of the Postgres installation directory. eg: C:\Program Files\PostgreSQL\13\bin

All executable files need to be prefixed with .\

The example shows how to use pg_dump to backup "someDB" to a backup directory:

.\pg_dump -h localhost -U postgres someDB > c:\backups\someDB.dump.out

Restore database to remote server using psql

Again use Powershell. Since the psql command normally would be used with < character, which is not working in Powershell there is a workaround:

Get-Content C:\backups\someDB.dump.out | .\psql.exe -h <youdatabaseserver>.postgres.database.azure.com -U <your_postgres_user> <destinationdb>

Here Get-Content will pipe the file into the psql executable which uses the normal switches for host and user.

There are several improvements to these backup and restore examples. One would be to pipe the backup result into the psql restore command (stackoverflow). 

Case sensitive queries

PostgreSQL is case sentitive on Where clauses so be aware when writing queries.

To enable case insensitive LIKE queries CITEXT extension has to be enabled on the database using this command: 

CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;