PostgreSQL

This is the open source database server for all the GNUmed project. The PostgreSQL project home is at this web page.

Please note that anyone moving from PostgreSQL 8.3 to 8.4 using the pg_upgradecluster tool on Debian/Testing will note some hiccups as captured at this thread. There exist a variety of ways to handle this, including:

• (if you are not in-production) bootstrapping a fresh database on 8.4
• dump-restore from 8.3 -> 8.4 will work fine, too
• fix the hiccups after the fact using this script

Additional GNUmed reference to Postgres:

• TroubleshootPostgreSQL
• PerformanceDesign

More about PostgreSQL

Things you may already know, or may need to know:

• some factors affecting the version(s) of PostreSQL supported by GNUmed
• the postgresql-8.3 family packages in Debian Lenny's PostgreSQL-8.3 server and perhaps others have limited language support for certain locales which may therefore require that the version native to Lenny be purged and then (having in the meantime set your locale) re-installed as per this incident
• the package name, in debian, is postgresql
• you need to become the operating system user under which PostgreSQL was installed (usually postgres, therefore su postgres) in order to create any other PostgreSQL user account, incuding the gm-dbo account created for GNUmed
• in debian Linux, pg_hba.conf and related files are located in /etc/postgresql/8.x/main
• about the GNUmed database:
  • distinctions between the postgres system account and the postgres and gm-dbo database accounts are on the devel archive here
  • during bootstrapping, the precaution is taken to save, into the database defaults, settings that include transactions as read-only:
    curs.execute("alter database %s set default_transaction_read_only to on" % self.name)
    and it is sanity checked during client startup:
    u'default_transaction_read_only': [u'on', u'accidental database writes', False],
  • accordingly, when trying to run a write operation, one must run set transaction_read_only to off; within the same transaction in which one is trying to run a write operation (such as deleting a person).

PostgreSQL server related commands (as root)

• pg_lsclusters (reports Version, Cluster, Status, Data directory, Log file)
• to restart postgres
  • if running postgresql 8.X under *nix, one of
    • /etc/init.d/postgresql-8.X restart (preferred, for restarting)
    • pg_ctlcluster 8.X main restart (less-preferred, for restarting)
  • or reboot your machine (think "Windows")

psql bits & pieces

• checking that you can enter psql (as user postgres): psql template1 or (after reconfiguration) psql template1 -U postgres
• \h for help with SQL commands
• \q for help with psql commands
• \q to exit

pgAdmin III bits & pieces

• Using pgAdmin III is outlined in these two devel list postings:
  • general approach (with screenshot)
  • GNUmed-specific clarifications
• As a bridge to learning native use of pgAdmin III, some have found helpful the following Windows software whose GUI query builder queries can be cut-and-pasted into pgAdmin III:
  • EMS SQL Manager for PostgreSQL

Potentially useful but at risk of getting outdated as new PostgreSQL versions are released:

• 8.3x Manual
• 8.3x pg_hba.conf
• 8.3x Backup and Restore