MIME-Version: 1.0
References: <CADu1mRP=rTC2WX=-EWqCX-CWjp9q09xxuEkK=2Se3wx1HasSkQ@mail.gmail.com>
 <603f35e6-32b2-4044-bbfb-5e70bba5164b@aklaver.com>
In-Reply-To: <603f35e6-32b2-4044-bbfb-5e70bba5164b@aklaver.com>
From: Marcin Gozdalik <gozdal@gmail.com>
Date: Tue, 17 Jun 2025 17:30:37 +0000
Message-ID: <CADu1mROP5-zURekH1gF69W3Hz3pteryM=8T4-VJWM3shKM9mKQ@mail.gmail.com>
Subject: Re: Changing locale of an existing database
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000009d3a810637c7dff9"
Archived-At: <https://www.postgresql.org/message-id/CADu1mROP5-zURekH1gF69W3Hz3pteryM%3D8T4-VJWM3shKM9mKQ%40mail.gmail.com>
Precedence: bulk

--0000000000009d3a810637c7dff9
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Around 100 DBs, ranging from 1TB to 50TB.
Initial testing showed that it takes 1.5h to dump the DB and 3h to restore
it for a DB of around 3TB.

Just recreating the indexes takes around 30 minutes on the same DBs.

I understand that pg_dump/pg_restore is the safe route but it's too slow
for me so I'd like to understand if I have any alternative.


wt., 17 cze 2025 o 16:55 Adrian Klaver <adrian.klaver@aklaver.com>
napisa=C5=82(a):

> On 6/17/25 09:20, Marcin Gozdalik wrote:
> > Hi
> >
> > I am using PostgreSQL 17 and would like to take advantage of performanc=
e
> > and stability across OS updates of builtin C.UTF-8 locale.
> > I have a cluster with a DB created with en_US.UTF-8 libc locale. I woul=
d
> > like to migrate the DB to C.UTF-8. Ideally there'd be an "ALTER DATABAS=
E
> > ... SET LOCALE ..." command that would take care of it but it seems it
> > doesn't exist. I was thinking that I could change the collation of all
> > TEXT/CHAR/VARCHAR columns in all the tables to pg_c_utf8, REINDEX all
> > those columns and change the default locale in the pg_database table.
> >
> > Is it a sensible plan? Am I missing some steps? I can't find any
> > reference to anybody doing that before or discouraging it.
>
> How big a database are we talking about?
>
> To me it would seem easier to create a new database with new locale and
> do either a pg_dump/pg_restore or logical replication to the new
> instance. Of course this may depend on the answer to the question above.
>
> >
> > Thanks,
> > Marcin
> >
> > --
> > Marcin Gozdalik
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

--=20
Marcin Gozdalik

--0000000000009d3a810637c7dff9
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Around 100 DBs, ranging from 1TB to 50TB.</div><div>I=
nitial testing showed that it takes 1.5h to dump the DB and 3h to restore i=
t for a DB of around 3TB.</div><div><br></div><div>Just recreating the inde=
xes takes around 30 minutes on the same DBs.</div><div><br></div><div>I und=
erstand that pg_dump/pg_restore is the safe route but it&#39;s too slow for=
 me so I&#39;d like to understand if I have any alternative.</div><div><br>=
</div></div><br><div class=3D"gmail_quote gmail_quote_container"><div dir=
=3D"ltr" class=3D"gmail_attr">wt., 17 cze 2025 o 16:55=C2=A0Adrian Klaver &=
lt;<a href=3D"mailto:adrian.klaver@aklaver.com">adrian.klaver@aklaver.com</=
a>&gt; napisa=C5=82(a):<br></div><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex">On 6/17/25 09:20, Marcin Gozdalik wrote:<br>
&gt; Hi<br>
&gt; <br>
&gt; I am using PostgreSQL 17 and would like to take advantage of performan=
ce <br>
&gt; and stability across OS updates of builtin C.UTF-8 locale.<br>
&gt; I have a cluster with a DB created with en_US.UTF-8 libc locale. I wou=
ld <br>
&gt; like to migrate the DB to C.UTF-8. Ideally there&#39;d be an &quot;ALT=
ER DATABASE <br>
&gt; ... SET LOCALE ...&quot; command that would take care of it but it see=
ms it <br>
&gt; doesn&#39;t exist. I was thinking that I could change the collation of=
 all <br>
&gt; TEXT/CHAR/VARCHAR columns in all the tables to=C2=A0pg_c_utf8, REINDEX=
 all <br>
&gt; those columns and change the default locale in the pg_database table.<=
br>
&gt; <br>
&gt; Is it a sensible plan? Am I missing some steps? I can&#39;t find any <=
br>
&gt; reference to anybody=C2=A0doing that before or discouraging=C2=A0it.<b=
r>
<br>
How big a database are we talking about?<br>
<br>
To me it would seem easier to create a new database with new locale and <br=
>
do either a pg_dump/pg_restore or logical replication to the new <br>
instance. Of course this may depend on the answer to the question above.<br=
>
<br>
&gt; <br>
&gt; Thanks,<br>
&gt; Marcin<br>
&gt; <br>
&gt; -- <br>
&gt; Marcin Gozdalik<br>
<br>
-- <br>
Adrian Klaver<br>
<a href=3D"mailto:adrian.klaver@aklaver.com" target=3D"_blank">adrian.klave=
r@aklaver.com</a><br>
<br>
</blockquote></div><div><br clear=3D"all"></div><br><span class=3D"gmail_si=
gnature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature">Ma=
rcin Gozdalik</div>

--0000000000009d3a810637c7dff9--