Postgresql

Installation on Gentoo

• The ebuilds that should be used are dev-db/postgresql-server-*, since they are newer and modularized instead of monolithic (old ebuilds are dev-db/postgresql).
• There can be a problem with the postgres USE flag of php. It can pull in libpq which would block the modularized postgresql-server package. The solution is to build postgresql-server first (removing libpq if already installed), then rebuild PHP. This will probably be done automatically once postgresql-server is stabilized.
• Similarly you need a version of psycopg (Python driver for Postgresql) that does not depend on libpq (2.0.10 is fine but unstable at the time of this writing).

Initial configuration

• By default on Gentoo the socket file (on /var/run/postgresql directory) allows only reading (and writing) to the postgres user. This means that other users (like apache via mod_php) cannot connect to the DB via the UNIX socket. This implies that to make phppgadmin works (for instance) you have to explicitely write "localhost" as the server name in the configuration file.
• To install the initial DB cluster:

emerge postgresql-server --config

• This means also that if you want to give commands when using another user, never forget to add the -h localhost option (which will prevent the use of the local socket and will use TCP).

Concepts

Users and Roles

• An user is just a Role that can login.
• A DB or a table owner is actually a role (not an user, although see the previous remark).

Authentication

• By default on Gentoo trust authentication is used which means that no password is actually needed to connect to databases on the DB. More information on PostgreSQL authentication methods.
• You can configure the authentication on /var/lib/postgresql/8.4/data/pg_hba.conf. Password authentication is possible.

Changing the ownership

• REASSIGN OWNED won't work for reassigning stuff that belongs to the "cluster owner" (postgres role in general I think).
• One way may be to export and reimport a DB as a different role. For this, use the --no-owner option of pg_dump:

sudo -u postgres pg_dump -O db_name > data.sql

• Note that ALTER ... OWNER TO ... can probably help too.

Tools

• psql is the command line interface for sending SQL commands to PostgreSQL.

Commands

• \l+ lists all databases.
• \dt+ lists all tables in the current database.

Updating

• You should dump the tables *before* you upgrade to another version (for example from 8.3 to 8.4). You can then restore the tables in the new version - but the initial dump should be done on the previous one.

sudo -u postgres pg_dump crm > crm.sql
psql crm < crm.sql # This is for restoring, you can also use: psql -U openerp crm < crm.sql

• There is also a program called pg_upgrade that eliminates the need to dump and reimport. But it is not necessarily simpler; both versions must be installed at update time (this is relatively easy on Gentoo), both servers must be stopped and you issue the following command (example):

sudo -u postgres pg_upgrade -u postgres -b /usr/lib/postgresql-9.0/bin/ -B /usr/lib/postgresql-9.1/bin/ -d /var/lib/postgresql/9.0/data -D /var/lib/postgresql/9.1/data

• Note that in Gentoo for this to work you must copy the configuration files from /etc/postgresql-x/ to /var/lib/postgresql/x/data

Management

• To list databases:

psql -U openerp -l

• Use the following command to copy a database to another one in the same server (the source DB must NOT be used):

create database newdb template olddb;

This is a Postgresql command, not standard SQL.

• The following command (shell program) will create a database (using the openerp user):

createdb -U openerp new_db_name

• To create a new user (role):

 createuser -U postgres new_user_name

• To add to a role the permission to create databases:

alter role openerp createdb;

• You can drop via dropdb.