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.klaver@aklaver.com