Skip to content

PostgreSQL

Jump to bottom
Helge Heß edited this page Nov 19, 2024 · 6 revisions

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

Links

Clone this wiki locally