Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Advertise on LowEndTalk.com

In this Discussion

PostgreSQL 9.2 DB migrate to PostGres 11
New on LowEndTalk? Please read our 'Community Rules' by clicking on it in the right menu!

PostgreSQL 9.2 DB migrate to PostGres 11

mehargagsmehargags Member
edited May 10 in Help

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

  • raindog308raindog308 Moderator
    edited May 10

    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.

    Thanked by 2simonindia vimalware

    For LET support, please visit the support desk.

Sign In or Register to comment.