So recently I was upgrading the database in production from Postgres 9.3 to 10. In addition to upgrade, we wanted to move the database to another machine. I know, doing it "in place" would be easier and simpler, but we had our reasons. Here is some data about the environment
- We keep all db data on separate virtual hard drive for flexibility and possible changing of its size.
- Before upgrade I did not ensure that databases were initiated with the same locales pl_PL.UTF-8, pg_upgrade told me about that
- Old machine: Ubuntu 14.04, system locale pl_PL.UTF-8
- New machine: Ubuntu 18.04, system locale en_US.UTF-8
I executed following procedure
- Turned off the old machine
- Detached the disk with db from the old machine
- Attached virtual disk with db to the new machine
- Upgraded the database with
pg_upgrade, it checked the locale, at first they did not match, I reinited the db and it worked like a charm
- gathered new statistics
After that db has started and we launched the application. It turned out quite fast howeber, that there is some problem, we dig into that and found out the text indices has become corrupted. Application by using queries could not fetch the result, we got us some not wanted duplicates as well. After reindexing the db started to operate normally, all the problems have disappeared.
I have reproduced the issue to explain it, but I have ran out of ideas. Here is what I have found.
such query does not work (does not return a result). It uses an index
select full_username from account where full_username = 'pattern';
but these ones do
select full_username from account where full_username like '%pattern'; select full_username from account where upper(full_username) like 'pattern'; --this one under condition pattern in dtabase is written with uppercase select full_username from account where full_username = ('pattern' COLLATE "pl_PL"); select full_username from account where (full_username COLLATE "pl_PL") = 'pattern'
after turning off index scans (by forcing seq scan) it started to work
SET enable_indexscan = off; SET enable_indexonlyscan = off; SET enable_bitmapscan = off; select full_username from account where full_username = 'pattern';
- the used pattern was always present in the table, I ensured that, problem occured only with index scans
patterncontained various characters (upper- lower case, special characters etc.)
- problem occurs in every possible combination of collation settings. I have upgraded db with en_US locale on OS, with pl_PL locales set, I have changed it for current session, changed it globally in OS and restarted the machine, changed it in postgres config file, nothing works, the first query (the one not modifying the data) does not return result
I have searched but was not able to explain that. One of the queries shows, that the problem most probably lies in collation. This has definitely something to do with moving the data to another machine. My question is - what have gone wrong and why were the indices corrupted. I still have the environment to run some tests, so if any more data is needed I can provide it. I will be grateful for any help with that