FAQ: PostgreSQL 8

Print Friendly, PDF & Email

Q: ?How do I set up a pg8 copy of a current db on devbox?
A: Example dump from sabre for database sconul:
/usr/local/projects/postgres/create-postgres-project-8.3.sh pg8
/usr/bin/pg_dump -h sabre.ilrt.bris.ac.uk -U cmetc -Fc sconul > sconul_dump.sql
/usr/bin/psql -p 56162 template1 < recreate_db.sql
/usr/bin/pg_restore -p 56162 -U cmetc -d sconul sconul_dump.sql

recreate_db.sql =
CREATE DATABASE sconul WITH TEMPLATE = template0;
CREATE USER sconul WITH PASSWORD '******';

(because I couldnt get dump restore to do this automatically)

Q: How do I make this accessible to demo or prod sites?
A: In your created postgres directory:

postgres/var/data/pg_hba.conf
and add something like

host all all 137.222.16.17 255.255.255.0 md5

but must also add the following to

postgres/var/data/postgres.conf
listen_addresses='*';

then

bin/postgresql-8.3 restart

Q: I’m getting errors like: “ERROR: operator does not exist: integer integer at character 58” I had a bug with an old zope app due to the following

A: This is a difference in the strictness of the way postgres handles sql syntax in 8.2 compared to 7.4
I had an zsql query that were using a like clause with an integer e.g

select foo from bar where p_ins_id like -1

With 7.4 it treated like as = for integers but now it raises an error instead.


About this entry