0

I have a small hobby project in Django backed by Cloud SQL Postgres instance. Recently I realised that data sorting is not correct and identified it as issue with collation setting.

Indeed collation is set to en_US.UTF8 instead of pl_PL.utf8 (is there difference between UTF8 and utf8?). Using this answer I learned that it's not possible to just change the collation - you have to create database with new collation and then move the data.

I now created new database with:

create database db_pl WITH OWNER 'cloudsqlsuperuser' LC_COLLATE='pl_PL.utf8' LC_CTYPE='pl_PL.utf8' template template0;

I'm struggling with copying the data.

Will this pg_dump be fine and cause no collation change related issues?

pg_dump -h HOST -p PORT -U USER db -f db.sql

For restoring should pg_restore or psql be used? What options are needed?

1
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Jan 4 at 16:50
1

Will this pg_dump be fine and cause no collation change related issues?
pg_dump -h HOST -p PORT -U USER db -f db.sql

Yes, that's fine. The resulting file is ready to be played into a database with a different default collation.

For restoring should pg_restore or psql be used? What options are needed?

psql into an empty database that has been created with the desired LC_COLLATE.

pg_restore is only necessary when the dump file is in custom or directory format. Otherwise it's just a text file with SQL commands.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.