PostgreSQL

Installing PostgreSQL on Debian/Ubuntu

apt-get install postgresql postgresql-client postgresql-contrib postgresql-server-dev-8.3

(note that on newer versions of Ubuntu (e.g. 10.04 LTS, the dev package is instead:

postgresql-server-dev-8.4

If you also want to install PhpPgAdmin

apt-get install phppgadmin

Then you have to edit the configuration at: /etc/apache2/conf.d/phppgadmin to uncomment "allow from all" and comment the IP filtered allow line.

Create a postgres superuser

$ createuser -P -s -e johndoe
Enter password for new role: ZaXSysGCNerD
Enter it again: ZaXSysGCNerD

$ psql
CREATE ROLE johndoe PASSWORD 'ZaXSysGCNerD' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

You should then see echoed:

CREATE ROLE

Creating the initial user

su - postgres
$ psql
CREATE DATABASE schedules;
create user schedules with password 'XXXXXXX';
grant all privileges on database schedules to schedules;

Import data from .sql file

psql database_name  < file.sql

Solving "duplicate key violates unique constraint"

Your primary key index is likely out of sync

login to psql and run:

SELECT MAX(id) FROM entities;

Make a note of the result and then do:

SELECT nextval('entities_id_seq');

This should be higher than the previous result. If it's not here's the fix:

SELECT setval('entities_id_seq', (SELECT MAX(id) FROM entities)+1);