Azure PostgreSQL Server Service Collation Create Error

Question

I'm trying to import my current existing database to postgre instance that is running on Azure PostgreSQL Server

I already configured my azure postgresql server parameters to use encoding as UTF8 (I'm not sure that it's applying without restart but even if i don't have option to restart it)

I'm trying to do this action with this command:

sudo -u postgres pg_dump --encoding="UTF-8" --no-owner DBNAME | psql --host=xxx.postgres.database.azure.com --port=5432 --username=xxx@yyy --dbname=DBNAME

However it's getting an error something like this:

ERROR: could not create locale "en_US.utf8": No error

I dive in the process and try to run it with UTF8 (Instead of UTF-8) and other options but it's got same error everytime.

I created a dump file to check the contents and the line that is generating this error is:

CREATE COLLATION "en_US.utf8" (lc_collate = 'en_US.utf8', lc_ctype = 'en_US.utf8');

This is causing that error. I tried to execute it manually on Azure postgre instance and i got same error too.

By the way I already created that DATABASE from console by using this SQL:

create database "DBNAME" with owner=xxx encoding='UTF-8' template template0;

Also i tried other options to create and import but non of them work.

Can someone help me?

Answer 1

CREATE COLLATION creates a operating system independent name that can be used to refer to OS locales (in queries etc).

Here lc_collate = 'en_US.utf8' and lc_ctype = 'en_US.utf8' refer to Linux operating system locales, which are named differently on Windows, which Azure PostgreSQL uses (and they're different on MacOS, too).

On Windows, this should work

CREATE COLLATION "en_US.utf8" (lc_collate = 'English_United States', lc_ctype = 'English_United States');

And on MacOS,

CREATE COLLATION "en_US.utf8" (lc_collate = 'en_US', lc_ctype = 'en_US');

But most of the time, you don't want to create your own collations with CREATE COLLATION, but can just use the pre-included collations in PostgreSQL. And normally a dump created with pg_dump does not include any CREATE COLLATION statements, since you haven't created any yourself.

Unless you're doing something special with collations, is it possible to remove those schema-specific collations, so they won't get included in the dump?