PostgreSQL 9.2 DB migrate to PostGres 11

mehargags

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

raindog308

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.:

CREATE DATABASE foo WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

You could change those values to create an ASCII database. But really, upgrade your DB to UTF-8 because, well, it's 2020.