PostgreSQL - OpenGroupware/OpenGroupware.org GitHub Wiki

Running PG in a Docker Container

Looks like the PG container requires that passwords are set for the user.

Example spin up:

docker run \
  --name PG \
  -p 5432:5432 \
  -e POSTGRES_PASSWORD=$PWD \
  -d \
  postgres:16

the name can be used to link the PostgreSQL to an OGo Docker
-p 5432:5432 maps the standard PostgreSQL port
-d starts the container in daemon mode
uses the PG 16 image, PG version doesn't matter much for OGo

For a real setup, the PGDATA directory should live on a volume:

-e PGDATA=/var/lib/postgresql/data/pgdata \
-v /custom/mount:/var/lib/postgresql/data \

Using `.pgpass` to specify PG passwords

The PG tools don't usually allow specifying the database as a command line argument. But they can be put into the ~/.pgpass file with chmod 600, e.g.:

localhost:5432:postgres:postgres:jsjsjs
localhost:5432:OGo:postgres:7272773
localhost:5432:OGo:OGo:djhdhjdh

The format is:

host:port:database:user:password

Create the OGo Database

Creating the OGo database & user

Since PG 15 an arbitrary user cannot write the public schema anymore. Which is a good thing, there is no reason that the OGo process should be able to modify the database schema.

So the setup here is:

the schema is created using the postgres user
then read/write permissions are granted to the OGo user

createuser \
  -h localhost \
  -U postgres \
  --no-createdb \
  --login \
  --pwprompt \
  --connection-limit=100 \
  OGo
createdb \
  -h localhost \
  -U postgres \
  OGo \
  "OpenGroupware.org Database"

Loading the Schema

The schema is available (and the same) in both the OGoCore repository and the main OpenGroupware one. It is loaded using the postgres user here:

psql \
  -h localhost \
  -U postgres \
  OGo \
  < ~/dev/OpenGroupware/OGoCore/database/PostgreSQL/pg-build-schema.psql

The schema still has some ancient naming, but got a little bump (nicer views etc) when OGoCore was implemented.

Giving the `OGo` PG user Access

The schema above is loaded as the postgres user, because there is no reason the OGo user/application needs to modify the schema. It does need the permission to read/write though, so w/ psql -h localhost -U postgres OGo:

GRANT SELECT, INSERT, UPDATE, DELETE 
   ON ALL TABLES    IN SCHEMA public TO "OGo";
GRANT SELECT, UPDATE, USAGE
   ON ALL SEQUENCES IN SCHEMA public TO "OGo";
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO "OGo";

(this assumes the DB is OGo only, otherwise this has to be done for each table/sequence/function)

Afterwards the tables should be accessible using the OGo user:

psql -h localhost -U OGo OGo
OGo=> SELECT * FROM staff;
 staff_id | company_id | description | login | is_team | is_account | db_status 
----------+------------+-------------+-------+---------+------------+-----------
    10001 |      10000 |             |       |         |          1 | 
     9998 |       9999 |             |       |         |          1 | 
    10004 |      10003 |             |       |       1 |            | 
     9992 |       9991 |             |       |       1 |            | 
     9982 |       9981 |             |       |       1 |            | 
(5 rows)

Linking other containers to the DB container

E.g. an OGo container:

docker run -it --rm \
  --name OGoDev  \
  --link PG:PG   \
  -p 12000:12000 \
  -v "/Users/helge/dev/OpenGroupware:/src" \
  helje5/gnustep-base-devsetup:0.1.0 \
  bash

Use the --link to expose the DB container named PG to the other container. It will be reachable as host PG, e.g. for a connection dictionary like this:

defaults write NSGlobalDomain \
  LSConnectionDictionary \
  '{databaseName=OGo;hostName=PG;port=5432;userName=OGo;password=...;}'

macOS / Homebrew Notes

Looks like installing modern PG's using Homebrew doesn't install links to psql and such anymore.

This seems to do the trick:

brew install postgresql@16
brew link --force libpq

Using the PostgreSQL.app also works.

But since the deployment is most likely to happen on Linux, using the Docker setup is probably best.

Importing a Dump and Upgrading an older OGo Database

needs a little cleanup

Drop the DB if necessary:

dropdb -h localhost -p 5432 -U postgres OGo

Create the DB and restore the dump:

createdb -h localhost -p 5432 -U postgres -O OGo OGo
pg_restore -h localhost -p 5432 -d OGo -U OGo \
  -v ~/dumps/OpenGroupwareDatabase.pgdump

Upgrading the Schema

Newer OGoCore needs some enhancement, backwards compatible. It is currently a 2-3 step process.

First there is a smaller update:

psql -h localhost -p 5432 -U OGo OGo \
  <~/dev/OGoCore/database/PostgreSQL/pg-update-1.x-to-5.5.psql

Now, depending on your database, you may have to ensure that the company_value table email1 attribute has no duplicates for person contacts.

There was/is a bug in OGo that created such dupes. Not anymore w/ the constraint.

Finally, run the OGoCore update:

psql -h localhost -p 5432 -U OGo OGo \
  <~/dev/OGoCore/database/PostgreSQL/pg-update-1.0to5.4.psql

This has a few dupes w/ the above, but adds more things on top. A constraint being added will fail if the "email1" attribute has dupes. Feel free to ignore or not :-)

Adjusting Documents

The documents folder in OGo contains notes, documents and also defaults. Those are usually in ISO-Latin-1 in existing servers, but now have to be in UTF-8 for GNUstep-base (the default encoding, which also makes sense).

To convert from Latin 1 to UTF-8, use iconv:

for i in *.txt; do \
  iconv -f ISO-8859-1 -t UTF-8 $i > tmpfile; \
  mv -f tmpfile $i ; \
done
for i in *.defaults; do \
  iconv -f ISO-8859-1 -t UTF-8 $i > tmpfile; \
  mv -f tmpfile $i ; \
done

Also, the new setup uses CET instead of MET as the timezone. This has to be patched in the defaults:

for file in *.defaults; do \
  mv $file $file.org && \
  sed <${file}.org >$file \
    's#timezone = MET#timezone = CET#g' && \
  rm $file.org; \
done