PostgreSQL for hosts #

This page is for system administrators at a hosting company who provide PostgreSQL databases for Exponential sites. It answers: which PostgreSQL versions to run, how to create each site's database and user so the installer succeeds, what the pgcrypto extension has to do with it, and how to back up and troubleshoot. PostgreSQL is second in the official order and the recommended database server. Sources: Database support and chapter 9.4.

Contents #

1. Why PostgreSQL
2. Versions and extension
3. Creating a database per site
4. pgcrypto
5. Settings and install
6. Sequences
7. Backups
8. Problems and their causes
9. Checklist
10. Related pages

Why PostgreSQL #

The project calls PostgreSQL the recommended database server: strict SQL, row-level locking, replication and point-in-time recovery of its own, and a database test job in the project's CI on every push (PostgreSQL 15). It is the first choice when a site needs a database server, for example when several web servers share one database (Database support).

Versions and extension #

Run a release your vendor still supports with security fixes. The wizard floor is not a recommendation.

Creating a database per site #

The installers do not create databases or users. Create both before the install, and make the site's user the owner of its database:

sudo -u postgres createuser --pwprompt <customer>
sudo -u postgres createdb --owner=<customer> --encoding=UTF8 --template=template0 <customer>

template0 lets you choose the encoding regardless of the server's default. A panel that creates PostgreSQL databases may set a different owner or encoding; check your panel's documentation and compare with the commands above (Databases and users in control panels).

pgcrypto #

The driver computes MD5 with digest() from pgcrypto. The installer runs CREATE EXTENSION IF NOT EXISTS pgcrypto; as a trusted extension (PostgreSQL 13 and later) the database owner may create it. If the server lacks it, install your distribution's PostgreSQL contrib package, then as the owner or a superuser:

sudo -u postgres psql -d <customer> -c 'CREATE EXTENSION IF NOT EXISTS pgcrypto;'

When it is missing, the setup wizard says: "The 'digest' function is not available in your database, and Exponential cannot run without it."

Settings and install #

[DatabaseSettings]
DatabaseImplementation=ezpostgresql
Server=localhost
Port=5432
User=<customer>
Password=<the password>
Database=<customer>
UsePersistentConnection=disabled

The installers write this to settings/override/site.ini.append.php. A console install, with the password from the environment so it stays out of the shell history:

EXP_INSTALL_DB_PASSWORD='<the password>' php bin/php/console exp:install --apply --db=pgsql \
    --db-host=127.0.0.1 --db-name=<customer> --db-user=<customer> --allow-root-user

--db=postgresql and --db=postgres are accepted too; the kickstarter takes [database_choice] Type=pgsql. The setup wizard prefills port 5432 and user postgres (settings/setup.ini DefaultPort_pgsql, DefaultUser_pgsql). UsePersistentConnection=enabled uses pg_pconnect instead of pg_connect; under Velocity each persistent worker keeps its connection anyway.

Sequences #

Auto-increment columns use sequences named <table>_<column>_seq. After loading data with explicit ids (an import, a restore of selected tables), correct them, or the next insert fails with a duplicate key:

SELECT setval('ezcontentobject_id_seq', (SELECT max(id) FROM ezcontentobject));

The schema installer does this itself after it loads data.

Backups #

pg_dump -U <customer> -Fc <customer> > <customer>.dump
pg_restore -U <customer> -d <customer> --clean <customer>.dump

A complete backup also contains var/storage, settings/override and settings/siteaccess. Switch on maintenance mode for a restore and clear every cache afterwards (Backup and restore). Point-in-time recovery and replication are PostgreSQL's own features; configure them with the PostgreSQL documentation.

Problems and their causes #

Checklist #

• To do: A supported PostgreSQL release, 13 or later, with the contrib package installed.
• To do: One database per site, encoding UTF8, owned by the site's user.
• To do: pgsql in the web and the command line PHP.
• To do: pg_hba.conf allows the site's user from the web servers only.
• To do: pg_dump backups scheduled and a restore tested.

Related pages #

• Databases in the official order
• Database support: PostgreSQL
• Chapter 9.4: PostgreSQL
• Database backups per engine
• Several web nodes
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.