Postgres and Moving Databases
18/01/24 10:23 Filed in: Systems Administation
Recently I had to recover a Postgres DB and for 'convenience' I moved it from its original Linux environment to OS X. The process had a few wrinkles most important was that the linux environment used a locale of en_US.utf8 and MAC OS only has en_US.UTF-8
While all you really should have to do to move the database is copy the data directory over and start Postgres with the '-D' flag and the directory name you can run into locale issues.
For example:
and if you run 'locale -a' on MAC OS you will discover there is no 'en_US.UTF-8' and you can't write to /usr/share/locale
the solution is to create your own local locale
and start the postgres server in an environment that lets it find the new locale
For example:
% /usr/local/opt/postgresql@16/bin/postgres -D /Volumes/data
2024-01-17 23:03:14.615 UTC [93623] LOG: invalid value for parameter "lc_messages": "en_US.utf8"
2024-01-17 23:03:14.615 UTC [93623] LOG: invalid value for parameter "lc_monetary": "en_US.utf8"
2024-01-17 23:03:14.615 UTC [93623] LOG: invalid value for parameter "lc_numeric": "en_US.utf8"
2024-01-17 23:03:14.615 UTC [93623] LOG: invalid value for parameter "lc_time": "en_US.utf8"
2024-01-17 23:03:14.615 UTC [93623] FATAL: configuration file "/Volumes/data/postgresql.conf" contains errors
and if you run 'locale -a' on MAC OS you will discover there is no 'en_US.UTF-8' and you can't write to /usr/share/locale
the solution is to create your own local locale
mkdir ~/.locale
cp -rpf /usr/share/locale/en_US.UTF-8 ~/.locale/en_US.utf8
and start the postgres server in an environment that lets it find the new locale
env PATH_LOCALE=/Users/USERNAME/.locale /usr/local/opt/postgresql@16/bin/postgres -D /Volumes/data