Skip to content

PostgreSQL problems in Debian

Jump to bottom Edit New page
David Corking edited this page Mar 7, 2014 · 10 revisions

I encountered a couple of problems when trying to do development and testing in Debian Linux 7.3 (aka Debian wheezy) and PostgreSQL 9.1

The first symptom was a complaint that a database could not be created because of a locale error. This can occur with db:create or db:init or db:test:prepare. Locale is what systems use to customize character set, sort order and date style to a particular culture. At LocalSupport, as of February 2014, our locale is specified by the encoding: unicode line in database.yml. An example of the error is:

bundle exec rake db:test:prepare
...          
PG::InvalidParameterValue: ERROR:  encoding UTF8 does not match locale en_GB
DETAIL:  The chosen LC_CTYPE setting requires encoding LATIN1.
: CREATE DATABASE "ls_test" ENCODING = 'unicode'
/home/david2/.rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.14/lib/active_record/connection_adapters/postgresql_adapter.rb:650:in `exec'
...

When fixing this I came across user authentication errors like this:

$ bundle exec rake db:create
...
FATAL:  Peer authentication failed for user "postgres"
...
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "database"=>"ls_develo
pment", "pool"=>5, "username"=>"postgres", "password"=>nil}

Database user management in Debian

User authentication errors look something like this:

$ bundle exec rake db:create

            You no longer need to have jasmine.rake in your project, as it is now automatically loaded
           from the Jasmine gem. To silence this warning, set "USE_JASMINE_RAKE=true" in your environment
           or remove jasmine.rake.
          
FATAL:  Peer authentication failed for user "postgres"
/home/david2/.rvm/gems/ruby-1.9.3-p448/gems/activerecord-3.2.14/lib/active_record/connection_adapter
s/postgresql_adapter.rb:1222:in `initialize'
...

One solution is to change the postgres cluster settings so that the your unix postgres user automatically authenticates to the cluster. Edit the first uncommented line of /etc/postgresql/9.1/main/pg_hba.conf so that it looks like this:

local all postgres trust

(Usually you just have to swap peer for trust)

Then reload the configs

sudo -u postgres /etc/init.d/postgresql reload

Locale management in Debian

You need to set the locale of the postgres unix user to be en_US.UTF-8, before you create a database cluster. A cluster is the place in your machine that stores a group of databases. A development machine probably one has one cluster, called, by default 9.1 main. However, Debian probably created this cluster for you when it installed. If the default locale in your machine is wrong, you will need to

  1. Set the locale for the postgres unix user.

    $ sudo su - postgres

    If your locale looks like this, move on to the next step.

    postgres@methone:~$ locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

    If not, consult Debian docs for solutions:

  2. Find out the name of your cluster

    $ pg_ls Version Cluster Port Status Owner Data directory Log file 9.1 main 5432 down postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgr esql-9.1-main.log

    This one is called 9.1 main.

  3. Drop the cluster

    !!! WARNING - the next command deletes all the data, tables, passwords and settings in all your PostgreSQL databases !!!

    $ pg_dropcluster --stop 9.1 main

  4. Create a new cluster

    $ pg_createcluster 9.1 main

  5. Create, migrate and seed your LocalSupport databases as described in the normal installation instructions. You should get normal NOTICEs such as:

david2@methone:~/LocalSupport$ bundle exec rake db:test:prepare`

            You no longer need to have jasmine.rake in your project, as it is now automatically load
ed
            from the Jasmine gem. To silence this warning, set "USE_JASMINE_RAKE=true" in your envir
onment
            or remove jasmine.rake.
          
NOTICE:  CREATE TABLE will create implicit sequence "categories_id_seq" for serial column "categorie
s.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "categories_pkey" for table "categori
es"
...

When the cluster is successfully created, your databases and templates should all have the same encoding, collate and Ctype, as in this example:

ls_development=# \l
                                    List of databases
      Name      |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
----------------+----------+----------+-------------+-------------+-----------------------
 ls_development | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 ls_test        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                |          |          |             |             | postgres=CTc/postgres
 template1      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                |          |          |             |             | postgres=CTc/postgres
(5 rows)

You can use Postgres commands to create databases that override the defaults in your system, but then you lose some of the automated help you get when you manage your Rails server with rake tasks.

Further reading

Add a custom footer
Add a custom sidebar
Clone this wiki locally