Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pw797-0001vC-Ht for pgsql-general@arkaria.postgresql.org; Mon, 08 May 2023 20:07:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pw796-0007bO-CB for pgsql-general@arkaria.postgresql.org; Mon, 08 May 2023 20:07:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pw795-0007bE-Vj for pgsql-general@lists.postgresql.org; Mon, 08 May 2023 20:07:44 +0000 Received: from mail-ej1-x636.google.com ([2a00:1450:4864:20::636]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pw78y-001QqF-U8 for pgsql-general@lists.postgresql.org; Mon, 08 May 2023 20:07:43 +0000 Received: by mail-ej1-x636.google.com with SMTP id a640c23a62f3a-9659e9bbff5so902052966b.1 for ; Mon, 08 May 2023 13:07:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20221208.gappssmtp.com; s=20221208; t=1683576455; x=1686168455; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=1/Ri3mYrCbYPP41+/plioQG5fpZvO9eGVMdQcx+fsKE=; b=LUZ5tp85Ug0wCsffeFvLaXXz/BYpUcU/FICM69DhcdTzRlqhZSzFZJzmFyJGtP/C0T SCagWOEqMm1tadxilzu0QDOl3z1cUsX5sM5Jp1O+eneNbL65knPEwAVauyrqo2p9+Z1f gdlZqUwjaZFbC2s9fYRKxse6DweZlUreHvR7L5Hr6rXbEoLqkf1yRwpp0qh4zAYoSZk3 ktM0ZMqsiHfp8f+GT+I96ibmeWKakedzo0f2wHl5fvLGKJLLvOsJj6SbtOhz2flppwC9 XIey4xXzw5naX5WobRs98lGFOBW5S7ll0LfhAWtJajgLzzMG61eqmCIvli1kZtbr21y8 MJXg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1683576455; x=1686168455; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=1/Ri3mYrCbYPP41+/plioQG5fpZvO9eGVMdQcx+fsKE=; b=BCIhk4a2W4qICnk9givT6fqReRGkXSFo/KCZPjcy4MDxEEeiiWN8765nLx71cP1ThK xdWHMZrf4tmGBHHWRM3tBvd4f56QapWxmGb7x8IRh00bQhOeuMykBXThYfCLgNLDydJm vHjWbxo904o0Z1gkWRRe3XvpU+byPNIa58r85e2bfyDAjslpAIX1Xp5Gg3VpW44G/6mv 8aJPISU8o4xYpUxE6Z/k6zwzake7jo5MXUEkTvQfU7PUHZnbq4WuiTleXSZA0qEWtDQw 1p60b52VoJY4n7VGmU3A5W2KFDns8N9h4H4bfkO5Mr4Qp1XDSFY7J/a81lYdLHkLOrFw ZzZA== X-Gm-Message-State: AC+VfDyYTERFeLozKp3GRnj4aL00OsI+NNyKCp7AVWM892T0PeS348gj 0HqsNeJwDWQadWP0StUxu2e/oNY1AOA4rN4/LhY= X-Google-Smtp-Source: ACHHUZ4LOuBb2luPyJNvQb0aGlCmd1XPBULz7gPkUy/MTIFPAVUTdKtIpAdOdIQV4kB5zQ8fRi/t+g== X-Received: by 2002:a17:907:6d10:b0:965:fb87:4215 with SMTP id sa16-20020a1709076d1000b00965fb874215mr8948329ejc.15.1683576455577; Mon, 08 May 2023 13:07:35 -0700 (PDT) Received: from localhost.localdomain (178-190-175-138.adsl.highway.telekom.at. [178.190.175.138]) by smtp.gmail.com with ESMTPSA id n25-20020a170906379900b00965c529f103sm377142ejc.86.2023.05.08.13.07.35 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 08 May 2023 13:07:35 -0700 (PDT) Message-ID: Subject: Re: ICU, locale and collation question From: Laurenz Albe To: Oscar Carlberg , pgsql-general@lists.postgresql.org Date: Mon, 08 May 2023 22:07:34 +0200 In-Reply-To: <2a1de395-c69a-3dae-4459-dbdb633df637@fortnox.se> References: <2a1de395-c69a-3dae-4459-dbdb633df637@fortnox.se> Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Mon, 2023-05-08 at 16:35 +0200, Oscar Carlberg wrote: > We have a bunch of existing Postgres 10 clusters running on CentOS 7,=20 > which have been initialized (initdb) with these collation options; >=20 > -E 'UTF-8' > --lc-collate=3Dsv_SE.UTF-8 > --lc-ctype=3Dsv_SE.UTF-8 > --lc_monetary=3Dsv_SE.UTF-8 > --lc-numeric=3Dsv_SE.UTF-8 > --lc-time=3Dsv_SE.UTF-8 > --lc-messages=3Den_US.UTF-8 >=20 > And createdb were provided with these locale options when setting up=20 > databases: > --lc-collate=3Dsv_SE.UTF-8 > --lc-ctype=3Dsv_SE.UTF-8 >=20 > We're upgrading the servers using logical replication, and would like to= =20 > take the opportunity to switch to ICU rather than relying on glibc, to= =20 > avoid future problems with index corruption if using physical=20 > replication between servers with different versions of glibc. >=20 > We're trying to figure out the most correct way to configure postgres to= =20 > achieve this. Currently we have: >=20 > -E 'UTF-8' > --locale-provider=3Dicu > --icu-locale=3Dsv-SE-x-icu >=20 > And createdb are provided with locale options: > --lc-collate=3DC > --lc-ctype=3DC >=20 > Is this a safe configuration to avoid index corruption, and other=20 > problems, while still being compatible with the previous locale=20 > settings? Yes, that is safe. But it is not compatible with the old setup when it comes to lc_time, lc_messages and the others. You should use sv_SE.UTF-8 for these locale categories. Note that that won't cause problems with upgrading the C library. Yours, Laurenz Albe