Posted over 5 years ago. Visible to the public.

How to change the locale of a PostgreSQL cluster

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.

  1. Find the cluster you want to change.

    Copy
    pg_lsclusters

    Output looks like this:

    Copy
    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 5435) below.

  2. Become a PostgreSQL superuser

    Copy
    sudo su postgres
  3. Backup entire database

    Copy
    pg_dumpall -p 5435 > 5435.sql

    Make sure to pass the correct port! (default is 5432)

  4. Drop your cluster (its data will be lost!):

    Copy
    pg_dropcluster --stop 9.5 main
  5. Create the cluster anew (we create it with the en_US.UTF-8 locale here):

    Copy
    pg_createcluster --locale en_US.UTF-8 --start 9.5 main
  6. Confirm cluster locale settings:

    Copy
    psql -p 5435
    Copy
    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.

  7. Load your backup

    Copy
    psql -p 5435 -f 5435.sql postgres

makandra has been working exclusively with Ruby on Rails since 2007. Our laser focus on a single technology has made us a leader in this space.

Owner of this card:

Avatar
Arne Hartherz
Last edit:
over 1 year ago
by Emanuel De
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Arne Hartherz to makandra dev
This website uses cookies to improve usability and analyze traffic.
Accept or learn more