Re: Migrate whole cluster to utf8

On 10/21/2015 06:34 PM, NTPT wrote:
> Hi all
>
> I have a db cluster (around 50GB of data ) in LATIN2 encoding. Now I
> need to dump whole cluster because of upgrade to newer version of
> pstgresql. But I need to have new cluster created with utf8 encoding
> And databases in that clusters tooo (with cs_CZ locale)
>
> what is the best /safe practice ?

pg_dump or pg_dumpall will set client_encoding to whatever the original
encoding was, in this case LATIN2:

http://www.postgresql.org/docs/9.4/interactive/app-pgdump.html

"
-E encoding
--encoding=encoding

Create the dump in the specified character set encoding. By
default, the dump is created in the database encoding. (Another way to
get the same result is to set the PGCLIENTENCODING environment variable
to the desired dump encoding.)"

From here:

http://www.postgresql.org/docs/9.4/interactive/multibyte.html#AEN35768

"
Table 22-2. Client/Server Character Set Conversions

Server Character Set Available Client Character Sets

UTF8 all supported encodings "

So the restore will be automatically converted to UTF8. That being said
I would test first.

Do:

http://www.postgresql.org/docs/9.4/interactive/app-pg-dumpall.html

pg_dumpall -g -f global.sql

To get the global objects.

On new cluster

psql -d postgres -U, etc -f global.sql

To set up globals.

Then I would take a some subset of the old cluster, say one database,
using a schema only dump:

http://www.postgresql.org/docs/9.4/interactive/app-pgdump.html
"
-s
--schema-only

Dump only the object definitions (schema), not data. "

Restore to new cluster/

Then a data only dump of a table:

"
a
--data-only

Dump only the data, not the schema (data definitions). Table data,
large objects, and sequence values are dumped.

-t table
--table=table

Dump only tables (or views or sequences or foreign tables) matching
table."

Then restore this in the new cluster to verify the encoding is being
handled properly. Rinse and repeat across a sample of tables.

>
> thanx for help

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com