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:

TODO

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 load

ed

        from the Jasmine gem. To silence this warning, set "USE_JASMINE_RAKE=true" in your envir

onment

        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 /etc/postgresql/9.1/main/pg_hba.conf so that the first line is

local all postgres 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

TODO list commands to do this

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

  1. Create a new cluster TODO

  2. 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 a lot 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