Core Concepts
Reference
How-to Guides
Troubleshooting
PostgreSQL
Available Versions
The following versions of PostgreSQL are available on Aptible:
- PostgreSQL 9.3, 9.4, 9.5, 9.6
- PostgreSQL 10
- PostgreSQL 11
- PostgreSQL 12
- PostgreSQL 13
- PostgreSQL 14
Connecting to PostgreSQL
Aptible PostgreSQL Databases require authentication and SSL to connect.
Connecting with SSL
Most PostgreSQL clients will attempt connection over SSL by default. If yours doesn't, try appending ?ssl=true
to your connection URL, or review your client's documentation.
Most PostgreSQL clients will not attempt verification of the server certificate by default, please consult your client's documentation to enable verify-full
, or your client's equivalent option. The relevant documentation for libpq is here.
By default, PostgreSQL Databases on Aptible use a server certificate signed by Aptible for SSL / TLS termination. Databases that have been running since prior to Jan 15th, 2021 will only have a self-signed certificate. See Database Encryption in Transit for more details.
Extensions
Aptible supports two families of images for Postgres: default and contrib.
- The default images have a minimal number of extensions installed, but do include PostGIS.
- The alternative contrib images have a larger number of useful extensions installed. The list of available extensions is visible in the repository we use to manage those images: aptible/docker-postgresql.
If you require a particular PostgreSQL plugin, contact Aptible Support to identify whether a contrib image is a good fit. Alternatively, you can launch a new PostgreSQL database using a contrib image with the aptible db:create
command.
Replication
Primary-standby replication is available for PostgreSQL. Replicas can be created using the aptible db:replicate
command.
Failover
PostgreSQL replicas can be manually promoted to stop following the primary and start accepting writes. To do so, run one of the following commands depending on your Database's version:
PostgreSQL 12 and higher
SELECT pg_promote();
PostgreSQL 11 and lower
COPY (SELECT 'fast') TO '/var/db/pgsql.trigger';
After the replica has been promoted, you should update your Apps to use the promoted replica as the primary Database. Once you start using the replica, you should not go back to using the original primary Database. Instead, continue using the promoted replica and create a new replica off of it.
Databases with replicas cannot be deleted so, in order to deprovision and old Database once you've failed over to a promoted replica, you'll need to contact Aptible Support to unlink the Databases. See the Deprovisioning a Database documentation for considerations when deprovisioning a Database.
Data Integrity and Durability
On Aptible, PostgreSQL is configured with default settings for write-ahead logging. Committed transactions are therefore guaranteed to be written to disk.
Configuration
A PostgreSQL database's pg_settings
can be changed with ALTER SYSTEM
. Changes made this way are written to disk and will persist across database restarts.
PostgreSQL databases on Aptible autotune the size of their caches and working memory based on the size of their container in order to improve performance. See the image's public git repo for details.
The following settings are autotuned:
shared_buffers
effective_cache_size
work_mem
maintenance_work_mem
checkpoint_completion_target
default_statistics_target
Modifying these settings is not recommended as the setting will no longer scale with the size of the database's container.
Connection Security
Aptible PostgreSQL Databases support connections via the following protocols:
- For PostgreSQL versions 9.3, 9.4, 9.5, 9.6, 10, 11, and 12:
TLSv1.0
,TLSv1.1
,TLSv1.2
- For PostgreSQL versions 13 and 14:
TLSv1.2