Re: Changing locale of an existing database

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(dot)klaver(at)aklaver(dot)com>
napisał(a):

> On 6/17/25 09:20, Marcin Gozdalik wrote:
> > Hi
> >
> > I am using PostgreSQL 17 and would like to take advantage of performance
> > 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 would
> > like to migrate the DB to C.UTF-8. Ideally there'd be an "ALTER DATABASE
> > ... 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(dot)klaver(at)aklaver(dot)com
>
>

--
Marcin Gozdalik