New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
PostgreSQL 9.2 DB migrate to PostGres 11
I'm getting stuck with a PostgreSQL 9.2 on CentOS 7 to be migrated to a new Postgres 11 fresh install on Debian 10.
I don't have experience with PGSQL as such so getting looped in the concept of Dump > < Restore and not getting in my head.
I noticed the old PG 9.2 DB has Encoding SQL_ASCII Collate C
But when I create new DB in PG v11 it defaults to UTF8 encoding.
So I guess we need to either create new DB with same encoding or convert to UTF8 which again might be a pain.
Should be a straightforward thing I presume so seeking out help if we we have someone experienced with PGSQL administration here on LET...
Many thanks
Comments
You should definitely test but you're going from 7-bit (ASCII) to 8-bit (UTF-8) so you should be fine. I have not done this on Pg but have done ASCII->UTF8 moves on other RDBMSes without issues.
Also, pg_dump has the -E (--encoding) flag you could try, to dump into utf-8 and then import.
BTW, if you're desperate, you can create a database in 11 with a specific coding...the CREATE DATABASE command supports ENCODING=, etc. E.g.:
You could change those values to create an ASCII database. But really, upgrade your DB to UTF-8 because, well, it's 2020.