PostgreSQL 9.5 won’t start after Windows 10 fall update

postgresqlpostgresql-9.5windows 10

I've installed Windows 10 Fall update (1709) and now my PostgreSQL 9.5 server won't start. It worked yesterday before the update and I haven't made any changes to the configuration.

I've checked event viewer and found following error messages:

2017-10-19 11:32:32 CEST LOG:  invalid value for parameter "lc_monetary": "Czech_Czech Republic.1250"
2017-10-19 11:32:32 CEST LOG:  invalid value for parameter "lc_numeric": "Czech_Czech Republic.1250"
2017-10-19 11:32:32 CEST LOG:  invalid value for parameter "lc_time": "Czech_Czech Republic.1250"
2017-10-19 11:32:32 CEST FATAL:  configuration file "C:/Program Files/PostgreSQL/9.5/data/postgresql.conf" contains errors

It seems like Microsoft changed locale name with Fall update, I couldn't find any list of available locale names so I decided to install Postgres 10 and it confirmed my suspicion, the postgresql.conf in Postgres 10 now reads:

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'Czech_Czechia.1250'          # locale for system error message
                # strings
lc_monetary = 'Czech_Czechia.1250'          # locale for monetary formatting
lc_numeric = 'Czech_Czechia.1250'           # locale for number formatting
lc_time = 'Czech_Czechia.1250'              # locale for time formatting

I changed the config values for PostgreSQL 9.5 server to 'Czech_Czechia.1250' and it started ok, but the problem is that now I can't connect to any database, pgAdmin III gives me following error:

pgAdmin error

Is there a way to get the data back? I can't create dump or run pg_upgrade since the databases now have invalid locale and I can't connect to them. Maybe there is a way to change the locale of the database manually? In theory, it shouldn't cause any problems since its only different name for the same encoding.

Best Answer

Thanks to suggestion from Daniel Vérité I was able to fix it without dumping the entire db server. My understanding of locales on Windows is very limited but from what I've learned it seems like Microsoft changed region name of czech locale from "Czech Republic" to "Czechia" during Fall Creators update (presumably so it conforms to ISO 3166-1).

Postgres somehow loads locale by combination of language name and region name, so databases created before this change cannot be connected to since the combination of language and region is no longer valid.

So to fix it, I downloaded Locale Builder 2.0 from Microsoft, and then:

  1. Created new locale based on Czech locale (cs-CZ), kept the same locale name (cs-CZ)
  2. Changed region name to "Czech Republic" (from "Czechia")
  3. Created installer by selecting "Build" -> "Build Locale Installer"
  4. Installed the newly created locale by running .msi file created in previous step
  5. Restarted the PostgreSQL server, this time it started without problem

Now I can run PostgreSQL 9.5 with 'Czech_Czech Republic.1250' and PostgreSQL 10 with 'Czech_Czechia.1250' and they both work.

I'm not sure whether it's ok to create the locale with same locale name (cs-CZ), but it doesn't seem to cause any problems, maybe someone can elaborate more.

Related Question