There may be reasons to change the locale of your Postgres cluster. A popular one is your development system's locale being used by default (which may be annoying). Here is how to do that.
Beware: By following the steps below, you will drop and recreate your cluster. You will lose all data (including roles). Instructions below include a procedure for dumping and restoring all cluster data (including roles). While it worked at the time of writing, you should have extra backup strategies for a production database.
Find the cluster you want to change.
Output looks like this:
Ver Cluster Port Status Owner Data directory Log file 9.3 main 5433 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log 9.4 main 5434 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log 9.5 main 5435 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
Take note of the cluster version and name, and port. You will need them below.
As an example, we will be modifying
9.5 main (running on port
Become a PostgreSQL superuser
sudo su postgres
Backup entire database
pg_dumpall -p 5435 > 5435.sql
Make sure to pass the correct port! (default is 5432)
Drop your cluster (its data will be lost!):
pg_dropcluster --stop 9.5 main
Create the cluster anew (we create it with the
en_US.UTF-8 locale here):
pg_createcluster --locale en_US.UTF-8 --start 9.5 main
Confirm cluster locale settings:
psql -p 5435
postgres=# SHOW LC_COLLATE; lc_collate ------------- en_US.UTF-8 (1 row) postgres=# SHOW LC_CTYPE; lc_ctype ------------- en_US.UTF-8 (1 row)
You may also list all tables via
\l and check their locales.
Load your backup
psql -p 5435 -f 5435.sql postgres