Thread: database encoding migration from a pg_dumpall set

Postgresql 14.3 Fedora 36

I have been meaning to move a few databases from an ISO encoding to UTF-8 for a while. I had taken a pg_dumpall dump of
the complete instance and then created a new instance with a UTF-8 encoding and imported the pg_dumpall. The databases
were all imported ok, but still show as LATIN9/ISO... - so presumably the restore from pg_dumpall takes the instruction
for the encoding from the dump records.
Is there an official way for migrating database encodings?

Steve T <stevetucknott@yahoo.co.uk> writes:
> Is there an official way for migrating database encodings?

Not via pg_dumpall.  If you dump the databases individually,
then you can create them manually on the target installation
with whatever encoding (or other properties) you want,
finishing by restoring the per-DB dumps into the respective
new databases.

Another way is to manually edit the pg_dumpall output
script and change the encoding options in the CREATE
DATABASE commands (but NOT the "SET client_encoding"
commands).

            regards, tom lane

On Sat, 2022-08-13 at 11:33 -0400, Tom Lane wrote:
> Not via pg_dumpall.  If you dump the databases individually,
> then you can create them manually on the target installation
> with whatever encoding (or other properties) you want,
> finishing by restoring the per-DB dumps into the respective
> new databases.
>
> Another way is to manually edit the pg_dumpall output
> script and change the encoding options in the CREATE
> DATABASE commands (but NOT the "SET client_encoding"
> commands).

Thanks Tom.
I did try doing another dumpall with the encoding set on that - but no joy.
I'll edit the dumpall if that's a legit way.

Thanks again.
Steve