Share via

Collation Encoding Error when trying to migrate from Single Server to Flexible Server

Tino Merl 20 Reputation points
2023-07-25T10:41:15.7066667+00:00

I tried migrating from a single PostgreSQL Server to a flexible PostgreSQL Server via the Azure Migration Tool. I followed both of these articles to get everything setup.

https://learn.microsoft.com/en-us/azure/postgresql/migrate/concepts-single-to-flexible https://learn.microsoft.com/en-us/azure/postgresql/migrate/how-to-migrate-single-to-flexible-portal

However whenever i try to migrate after exactly 2 minutes and 3 Seconds i get the following error message:

Collation/Encoding not Supported Error: Failed to run ` SELECT a.table_schema, a.table_name, a.column_name, a.collation_name, cast(b.collnamespace AS text) AS collnamespace, cast(b.collowner AS text) AS collowner, b.collcollate, b.collctype, b.collencoding, pg_encoding_to_char(collencoding) AS encoding FROM information_schema.columns AS a INNER JOIN pg_collation AS b ON a.collation_name = b.collname AND b.collencoding = ( SELECT ENCODING FROM pg_database WHERE datname = '{0}') WHERE a.collation_name NOT IN ( SELECT DISTINCT ON (t1.collname, t2.collation_sche... (rest of the failure message is truncated.)

and the migration is cancelled. The error persists if i try using the cli or the portal. I also tried using it with a freshly created database which also doesn't work. The collations of my databases are the followings.

source

datname datcollate
template1 English_United States.1252
template0 English_United States.1252
azure_maintenance English_United States.1252
azure_sys English_United States.1252
postgres English_United States.1252

destination

datname datcollate
azure_maintenance en_US.utf8
template1 en_US.utf8
postgres en_US.utf8
template0 en_US.utf8
azure_sys en_US.utf8

Edit: Improved Readability of collations

Azure Database for PostgreSQL

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tino Merl 20 Reputation points
    2023-08-02T13:13:28.97+00:00

    Hello @SSingh-MSFT ,

    Thanks for answering and following up. Unfortunately your linked solution couldn't be implemented, as i don't have access to the console in neither the Flex Server or the Single Server. However, it gave me an Idea. I Created the Collation English_United States.1252 in my new Flex Server via the following SQL Command.

    CREATE COLLATION English_United States.1252 (LOCALE = 'en_US.utf8')

    Afterwards i could start the migration. Whenever i ran into a problem in the migration and had to start anew i had to drop the collation and create it again.

    This was very tiresome and i don't think doing this should be the case. Because when i tried migrating this Server the first time i didn't have to Create this collation at all. Furthermore the collations were just the Standard ones used by the Azure PostgreSQL Servers.

    There should be at least a remark in the official Docs how to handle this error when it happens.

    Was this answer helpful?

  2. SSingh-MSFT 16,461 Reputation points Moderator
    2023-07-25T11:42:50.17+00:00

    Hi Tino Merl •,

    Welcome to Microsoft Q&A forum and thanks for using Azure Services.

    As I understand, you are getting Collation Encoding Error when trying to migrate from Single Server to Flexible Server.

    We are sorry about the inconvenience.

    Could you please refer to the solution mentioned here: https://community.atlassian.com/t5/Confluence-questions/The-database-collation-is-not-supported-but-no-options-for-the/qaq-p/1764064

    if this helps in your case.

    Do let us know so that we can further investigate.

    Thank you.

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.