Postgresql database migration to other host

user_19246813 0
Viewed 85

Im trying to move my postgres db from one host (xenial) to another (focal). I've tried using pgdump for it

pg_dump -d mydb > backup.sql

But the problem is that after I've restored db on my new host with

psql -d mydb < backup.sql

I get an error in logs: "Your collation seems to have change: rebuild your index then empty the table access_db_check". I have no problem doing that but I want to figure out what causing collation to change. DB on new and old host both have |UTF8|en_US.UTF-8|en_US.UTF-8 for encoding|Collate|Ctype. Maybe it's because of version difference? Old on is 12.7-1 and new one is 12.9

artifactory migration postgresql database
1 Answers

Can you confirm if the source and destination postgres versions are same.

If yes, can you check if the collation of your source database and destination database is same. Mostly it should be "UTF-8"

The below command might help you. SELECT datcollate FROM pg_database WHERE datname='<database name>';

user_178126070
Related