PostgreSQL

PostgreSQL version is determined at installation time via pgsql_version. Changing minor versions on a production server is ill-advised, instead consider migrating to another ApisCP platform using the migration tool. Patch releases (11.3 => 11.4) are supported and deployed automatically without issue.

Namespacing

All accounts are prefixed with a database namespace. In service metadata, this value is pgsql,dbaseprefix. A prefix must end with an underscore ("_"). If not supplied, it will be automatically generated from the primary domain on the account.

A prefix can be adjusted a couple ways. First, if [auth] => allow_database_change is enabled (Tuneables.md), then Site Administrators may change it under Account > Settings. If this value is disabled, then the Appliance Administrator may change the prefix either in Nexus or from the command-line using EditDomain.

# Change the prefix to "foo_"
EditDomain -c pgsql,dbaseprefix=foo_ bar.com

When a prefix is changed, all authentication details must be updated to reference the new prefix. These are not updated on prefix change.

Enabling remote connections

data_center_mode is a Bootstrapper setting that opens remote access to PostgreSQL. Once opened, PostgreSQL is protected by Rampart. data_center_mode opens up remote PostgreSQL access in addition to a slew of other features. If you'd like to just open PostgreSQL, use the pgsql.remote-access Scope.

Troubleshooting

Depopulating databases

New in 3.2.6

Both MySQL and PostgreSQL have a double throw safety switch built into service metadata. To remove databases and access rights, both enabled and dbaseprefix must be disabled/nulled respectively.

EditDomain -c pgsql,enabled=0 -c pgsql,dbaseprefix=None -D domain.com

In the above, PostgreSQL is disabled and all databases/grants removed from an account. To temporarily disable database creation without removing these grants, specify pgsql,enabled=0 without nulling dbaseprefix.

Upgrading PostgreSQL

It is not advised to upgrade PostgreSQL major versions, e.g. 11 -> 12. Instead, migrate the sites to another server using automated [server-to-server](Migrations - server.md) migrations. Upgrade-in-place requires exporting the database via pg_dumpall then importing following upgrade.

PostgreSQL version may be chosen at install time using the ApisCP Customizer (opens new window).

Pruning WAL

Write-ahead logging ensures data integrity in PostgreSQL. Once written to the WAL, data may then be committed to database. WAL logs are automatically expired by PostgreSQL, but in the event of improper configuration may quickly grow to exceed reasonable storage limits.

Before doing so, consult /var/lib/pgsql/XX/data/postgresql.conf settings, specifically max_wal_size and min_wal_size (opens new window).

DANGER

This is considered very risky. There is a risk of permanent data loss. Do not proceed unless absolutely necessary.

The following interaction assumes PostgreSQL 11 is installed. 11 would change to 10 or 12 as appropriate.

systemctl stop postgresql
systemctl stop monit
# Find the last pg_wal or maybe last few, by recent timestamp
/usr/pgsql-11/bin/pg_archivecleanup -n /var/lib/pgsql/11/data/pg_wal/00000001000002320000007E
# Delete all records older than
/usr/pgsql-11/bin/pg_archivecleanup -d /var/lib/pgsql/11/data/pg_wal/00000001000002320000007E
# Now your database is toast because the WAL indicator has vanished
# Shows the WAL log
/usr/pgsql-11/bin/pg_resetwal  -n /var/lib/pgsql/11/data/
# Reset WAL
/usr/pgsql-11/bin/pg_resetwal  /var/lib/pgsql/11/data/
systemctl start postgresql
systmectl start monit