Subject: database encoding migration from a pg_dumpall set

From: Steve T <[email-masked]>
Date: Sat, 13 Aug 2022 16:10:10 +0100

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?

---

From: Tom Lane <[email-masked]>
Date: Sat, 13 Aug 2022 11:33:47 -0400

> 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

---

From: Steve T <[email-masked]>
Date: Sun, 14 Aug 2022 07:17:25 +0100

> DATABASE commands (but NOT the "SET client_encoding"

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