Azure PostgreSQL服务器服务排序规则创建错误 [英] Azure PostgreSQL Server Service Collation Create Error

问题描述

我正在尝试将当前的现有数据库导入运行于Azure PostgreSQL服务器上的postgre实例

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

我已经配置了蔚蓝的postgresql服务器参数以将编码用作UTF8（我不确定它是否无需重启就可以应用，即使我没有重启它的选项）

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:

错误：无法创建语言环境 en_US。 utf8：没有错误

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

我在此过程中潜入并尝试使用UTF8（而不是UTF-8）和其他选项来运行

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');

这是导致该错误的原因。我尝试在Azure postgre实例上手动执行它，我也遇到了同样的错误。

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

通过我已经使用以下SQL从控制台创建该数据库的方式：

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.

有人可以帮我吗？

推荐答案

我使用以下命令解决了我的问题：

I resolved my issue with this command:

sudo -u postgres pg_dump -Fp —no-owner DBNAME |sed "/COLLATE/s/en_US.utf8/English_United States.1252/ig"|sed "/CREATE COLLATION/s/en_US.utf8/English_United States.1252/ig"|psql --host=aaa.postgres.database.azure.com --port=5432 --username=xxx@yyy --dbname=DBNAME

此问题来自服务器操作系统差异。我当前的PostgreSQL服务器使用Ubuntu，但Azure PostgreSQL服务器使用Windows，因此排序规则名称有些不同。

The issue is coming from the server O.S. differences. My current PostgreSQL server is using Ubuntu but Azure PostgreSQL Server is using windows so the collate names are little bit different.

这篇关于Azure PostgreSQL服务器服务排序规则创建错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！