PostgreSQL 8.2.13

Description:  Full Featured SQL DB
Maintainer:  Roman Plessl <roman.plessl@oetiker.ch>
Created:  2009-04-09
Updated:  2009-04-09 (Package Prepared)
Support:  OETIKER <support@oetiker.ch>
Links:  Local Docs, Homepage, SEPP Dir, INSTALL
OS:  linux
Categories:  programming
Binaries:  ecpgpg_ctlpg_dumppsql
More Info
Getting Started

Local Setup

  • create a new useraccount where you are going to run postgresql (for example pgsql).
  • su - to that account
  • Link /usr/sepp/var/postgresql-8.2.13-rp/data.HOSTNAME to a place where you want to keep you actual datastore
  • run /usr/pack/postgresql-8.2.13-rp/amd64-linux-ubuntu8.04/bin/initdb --pgdata /usr/sepp/var/postgresql-8.2.13-rp/data --pwprompt to setup the database structure. Note, the password gets stored in CLEARTEXT ... the name of the superuser is the name of the user the postgresql daemon is running under.
  • edit the security setup in /usr/sepp/var/postgeresql-8.2.13-rp/data/pg_hba.conf and replace trust with md5 (or crypt if the passwords are stored in this format).
  • pg_ctl -o "-i" start (the add '-o "-i"' enables intenet accessability of the db) otherwhise you can only access it locally
DB Administration: How to create a new Database

  • create an admin user for this new database with appropriate rights. This will require you to login with an account which can create users. /usr/pack/postgresql-8.2.13-rp/bin/createuser --username=pgsql --password --createdb --pwprompt newadmin
  • use the new account to create a database as this new user. This will allow it to create tables in 'his' db. /usr/pack/postgresql-8.2.13-rp/amd64-linux-ubuntu8.04/bin/createdb --username=newadmin --password newdb
  • optionally you can now log in a pgsql (master user) again and remove the database creation priviledge from this user
User Stuff

  • run psql -h dbhost to connect to the postgresql server
Upgrading

  • When upgrading a database from previous postgresql versions you must dump the contents of the database to a file and restore this in the new database. Note that in order for pg_dumpall to work you may have to edit the pg_hba.conf file and allow the postgresql root user to use all local databases without password.
local all trust

  • You can restore the output of pg_dumpall by feeding it to psql.
  • You might have to play with the permissions as well.
NOTE

To access the perlbindings, use the following line in your script

use lib qw(/usr/pack/postgresql-8.2.13-rp/lib/perl);

See the POD documentation.