Read more

How to change the locale of a PostgreSQL cluster

Arne Hartherz
October 01, 2013Software engineer at makandra GmbH

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.

Illustration online protection

Rails professionals since 2007

Our laser focus on a single technology has made us a leader in this space. Need help?

  • We build a solid first version of your product
  • We train your development team
  • We rescue your project in trouble
Read more Show archive.org snapshot

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.

    pg_lsclusters
    

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

  2. Become a PostgreSQL superuser

    sudo su postgres
    
  3. Backup entire database

    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!):

    pg_dropcluster --stop 9.5 main
    
  5. 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
    
  6. 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.

  7. Load your backup

    psql -p 5435 -f 5435.sql postgres
    
Arne Hartherz
October 01, 2013Software engineer at makandra GmbH
Posted by Arne Hartherz to makandra dev (2013-10-01 11:02)