We have pretty much completed adding support for PostgreSQL to WACS and it was part of the Wacs 1.0.0 release. With the release of Wacs 1.0.1, we have switched our main internal development DB to PostgreSQL (from Oracle). Please check the release notes for the up-to-date position of this support, but we are hoping it will prove fully functional - certainly going forward it will be a major focus. Some of the customisation available with other databases may not be available however due to the complexity and different design style of the PostgreSQL authors.
![[Note]](images/note.png) | Note |
|---|---|
We have created a wacs-for-psql package for
both Fedora and Ubuntu that attempts do to what is necessary to create a
working Wacs installation. To this end, we create the basic database and
a special |
PostgreSQL is rather different from Oracle and MySQL as it tends towards
using the host operating system authentication mechanisms for identifying users
rather than a more conventional username and password combination.
It can be reconfigured to allow username and password login, and we attempt
to do this in as minimalist a way as possible to preserve normal operation
for other users of the database. By default, the system manager account,
postgres gains automatic database manager authority when
it invokes any of the postgres tools (like the SQL command line,
psql).
![[Warning]](images/warning.png) | Warning |
|---|---|
NB: some Linux distributions, eg Ubuntu, support simultaneous
installation of multiple versions of Postgresql. In this case, you may find
that the TCP/IP port number on which the database listens has changed from
the default of 5432. In one case on an Ubuntu Xenial system which had been
upgraded from previous releases; PostgreSQL 9.1 had port 5432, PostgreSQL 9.3
had port 5433 and PostgreSQL 9.5 had 5434. All of these were configured in
/etc/postgres/<release_no>/main/postgresql.conf so do
check the port = directive to make sure you are connecting
to the version of the database you think you are. |
With more recent versions of PostgreSQL configuration has been relocated
into /var/lib/pgsql and the key configuration files like
pg_hba.conf and pg_ident.conf held in the data
sub-directory of /var/lib/pgsql. The wacssetup
and package post install scripts make the necessary changes to
these files but we've seen issues with the postmaster
process not restarting properly once the changes Wacs needed have been
made. You may also have to edit the postgresql.conf file
in /var/lib/pgsql/data to include pg_hba.conf
and pg_ident.conf to make these new settings
active. A reboot or restart of postmaster should rectify
things once these changes are in place. We will try to improve the handling
of these install activities in the next release, but the version in Wacs
1.0.1 does the correct edits to the config files for you.
A key point to understand is that the Perl DBD driver and similar interfaces to PostgreSQL use the network domain rather than the named pipe for communication and this is not by default enabled by the installation packages for PostgreSQL. In the next section, we will take you through the setup and configuration steps needed for PostgreSQL to interact with the network properly.
There are three steps you need to take to enable network-based operation in postgres; to do these as described belowe you will need access to either the postgres or root accounts for the server computer. If you do not have access to this, there may be other ways to do what is needed via cPanel or similar but you will have to consult a suitable administrator for the system you are using.
The first thing we need to do is to get the PostgreSQL
server to listen for connections on the network port. This is done by editing
the postgresql.conf file which can be found in
/var/lib/pgsql/data for PostgreSQL 13 or maybe in
/etc/postgresql/version/main older versions
or other distributions.
To use WACS effectively the database is all managed under a dedicated
WACS user account and it is thus easier to provide username and password
without the normal host-based user authentication taking place. If you
wish to use the interactive PostgreSQL command line interpreter,
psql for fixing up issues or making ad hoc database queries, it
is best to use the -h option with the host name of the
database service. You will also need the -U option to
specify the username to use, and maybe -W to force
interactive prompts for the password (optional). A sample invocation of
the psql SQL command line would therefore be:
psql -d wacs -h myserver -U wacs
It may also be productive to install the psql command line client on another system and attempt a connection over the local network.
Here we give a few tips and tricks on using PostgreSQL which may not be immediately aparrent.
PostgreSQL does support referential integrity on the various interlinked tables within the Wacs schema. This is a great thing and improves the quality of the data in your database no end. However it can create issues when trying to import data, particularly to the sets table which is self-referential where of course you can get into a chicken-and-egg situation very easily. The video clip set record points to the images set record and vice versa, but you can't create the first one in your import file because it refers to another set that doesn't exist yet. If you use the copy from STDIN feature, you can make this work as follows:
SET search_path = wacs,public; BEGIN; SET client_encoding to 'UTF8'; SET sychronous_commit TO off; SET CONSTRAINTS TO DEFERRED; SET search_path = wacs,public; COPY sets(setno,stype,sstatus,sauto,....) FROM STDIN; 2 I A N,.... 7 I A N,.... \. COMMIT;
This is then run from the psql command line tool
using \i sets-import.sql assuming that is what you called
the file containing the data to be imported above. Note that \N
is used as a shorthand for a NULL value in these import files.