PostgreSQL

This is the open source database server for all the GNUmed project. If you are new to Postgres:

• the PostgreSQL project home is here
• be aware of the information below (you may need to come back to it)

See Postgres GNUmed caveats here.

Advanced: a bit about how Postgres is deployed in GNUmed is here.

Some PostgreSQL basics

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

• some factors affecting the version(s) of PostreSQL supported by GNUmed
• 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")
• if you are having more problems than this, more about checking and configuring Postgres are within the page Troubleshoot PostgreSQL

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