Troubleshoot PostgreSQL

Some basic commands are included on wiki page PostgreSQL. Additional information is included below.

Using PostgreSQL versions < 8.4

If you encounter problems related to setup/conversion of vaccines (see the log files) during conversion of v13 to v14 please switch to using GNUmed 0.8.1/v14.1 packages.

Moving from PostgreSQL 8.3 to 8.4

If using the pg_upgradecluster tool on Debian/Testing, hiccups are 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

Otherwise, if you email the list or report bugs, please advise…

  1. Do your GNUmed client get a successful login connection to the public database? If not, please attach the client log file.
  2. Which timezone are you in? GNUmed did have problems in some timezones… please attach the gnumed client log file and the gnumed.conf file
  3. If you are trying to use a local GNUmed database
    • Was the bootstrapper script run to the point of advising "We were very likely successful?" (if not, see below)
    • Did this version of the GNUmed database previously work for you ?
    • Do you use the default pg_hba.conf, or did you edit it? If you edited it, please supply it.
    • if running debian and some other systems, from the command line as root, run the command pg_lsclusters … what does it return?
    • Are you able to "see" the databases in, say, pgAdmin III (see PostgreSQL)?

If you had trouble during the bootstrapping…

  1. Was bootstrapping done as root?
  2. Debian Squeeze (testing) installation suffers from a bug in which enabling a database server as part of the install can result in no cluster starting at boot time. Bug reference is on the IssuesLinux page.
  3. Postgres under some systems will create its database clusters under the outdated ASCII encoding SQL_ASCII instead of UTF-8 needed by GNUmed for language support. This needs compatibility with the template database created by Postgres, at setup, based on encoding as set from the then-current locale as confirmed at this external thread. If your locale was C (or some other locales) you may be affected. Ensure that your locale is configured to want UTF-8 and then either re-install Postgres or, if you wish to preserve configuration information in pg_hba.conf and postgresql.conf, backup any data and then drop and create a new database cluster.

    For more information, see this GNUmed devel thread and this not-a-GNUmed bug report.

    The problem is suggested by log lines such as
      "Your PostgreSQL installation seems not to have lc_ctype UTF-8 or C.<br>This will most probably lead to failures of the GNUmed server installation."
      2008-03-30 20:28:45  [PANIC]  (./bootstrap_gm_db_system.py:__create_db@701):  >>>[
                               create database "gnumed_v2" with
                                       owner = "gm-dbo"
                                       template = "template1"
                                       encoding = 'unicode'
                               ;]<<< failed <br>
      2008-03-30 20:28:45  [PANIC]  (./bootstrap_gm_db_system.py:__create_db@701):  exception type : psycopg2.ProgrammingError
      2008-03-30 20:28:45  [PANIC]  (./bootstrap_gm_db_system.py:__create_db@701):  exception value:  Encoding UTF8 stimmt nicht mit 
                                                  der Server-Locale de_DE@euro ??berein
                                                  DETAIL:  Die LC_TYPE-Locale des Servers verlangt die Kodierung LATIN9.

MS Windows

  • As a Windows user you can install PostgreSQL (8.3 is recommended) via the installer provided by the PostgreSQL community. Pay attention to the configuration dialog that comes up.
  • It is recommended that you select 'English' as 'language to be used during installation'.
  • In the 'Service configuration' dialog it is recommended to tick 'Install as service' . Please set and write down the account password. This relates to a Windows system account does not (yet) have anything to do with the database superuser.
  • In the 'Initialize database cluster' it is important to tick 'Accept connections on all addresses', select nothing else but 'C' as Locale stetting, select 'UTF8' for both 'Encoding (Server)' and '(Client)'. In this screen select and write down the password for the database superuser 'postgres'.
  • In the 'Enable procedural languages' dialog tick 'PL/python'.
  • note, when translating source files encoded in Windows character sets, that the Windows character set is not identical with ISO 8859-1 (see here ).

SuSE 10.1

  • install the python-devel rpm
    • needed for distutils
  • download and install psycopg2
  • pg_hba.conf and postgresql.conf are to be found in /var/lib/pgsql/
  • remember to pg_ctl reload any configuration changes

Debian

  • configuration files are in /etc/postgresql/8.x/main/

  • some historical differences among several Linux distributions are described here.

Checking and Configuring PostgreSQL (quoting Steven Duffy)

Usually PostgreSQL should be started automatically at bootup. Most modern distributions configure PostgreSQL correctly to do so. If you installed the package from sources, some steps may be required to get the PostgreSQL database system running.

First, make sure you have the PostgreSQL server daemon running. Just enter the following lines on the command line:

$ ps ax | grep "post"

This should print something like

212  ?  S    0:00 /usr/local/pgsql/bin/postgres -i -o -F -D/usr/local/pgsql/...

(It might be postmaster rather than postgres in versions < 8.3)

You might as well check under /etc/rc.d/ or run Linuxconf, rcconf or whatever your distribution offers to check for demons running in certain runlevels.

If postmaster isn't running find the appropriate wrapper scripts or enable it for the runlevel with the tools appropriate to your distribution.

If all else fails you can set it up with a variation on these commands as root:

su - postgres
postmaster -D ~postgres/data >logfile 2>&1 &

You will have to know what you do to make them run successfully.

You should enable TCP/IP access to the database. In order to do this you can either add the '-i' option when starting postmaster (see above and below) or do the following:

  • find and open /etc/postgresql/postgresql.conf
  • change listen_addresses = "" to whatever is appropriate
    • this used to be called tcpip_socket
  • save changes and restart PostgreSQL
If you'd rather like to add the '-i' option and don't know where postmaster is started:

If your distribution uses as postgresql script to start the server, it is likely the pg_ctl is used to run postmaster. In this case, find where pg_ctl is executed and add insert -o \"-i\" as one of the options to pg_ctl. Otherwise, find the script from which postmaster is executed and simply add -i as an argument.
Topic revision: 24 Nov 2011, JamesBusser
 
Download.png
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Foswiki? Send feedback
Powered by Olark