PostgreSQL

Learn about running secure, Managed PostgreSQL Databases on Aptible

Available Versions

The following versions of PostgreSQL are currently available:

Version	Status	End-Of-Life Date	Deprecation Date
14	Available	November 2026	February 2027
15	Available	November 2027	February 2028
16	Available	November 2028	February 2029
17	Available	November 2029	February 2030
18	Available	November 2030	February 2031

PostgreSQL releases new major versions annually, and supports major versions for 5 years before it is considered end-of-life and no longer maintained.

For databases on EOL versions, Aptible will prevent new databases from being provisioned and mark existing database as DEPRECATED on the deprecation date listed above. While existing databases will not be affected, we recommend end-of-life databases to be upgraded. Restoring a database from a backup will provision a new database that matches the version associated with the backup, even if that version is EOL or Deprecated. The latest version offered on Aptible will always be available for provisioning, regardless of end-of-life date.

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

The listed extensions alongside those listed in PostgreSQL’s documentation here are available for use.

Extension	Available in versions
pg_cron	15 - 18
pgaudit	14 - 18
pglogical	14 - 18
pg_repack	14 - 18
pgvector	14 - 18
postgis	14 - 18
wal2json	14 - 18

If you require a particular PostgreSQL extension, contact Aptible Support to identify whether it’s a good fit for Aptible.

pg_cron

pg_cron lets you schedule recurring database jobs using standard cron syntax. Unlike other extensions, pg_cron requires additional configuration before you can enable it.

Enabling pg_cron

First, add pg_cron to shared_preload_libraries:

ALTER SYSTEM SET shared_preload_libraries = pg_stat_statements, pglogical, pg_cron;

Restart your database using the Aptible CLI or Dashboard for the change to take effect.

Once the database has restarted, connect to the postgres database and configure pg_cron and max worker settings:

ALTER SYSTEM SET cron.database_name = 'postgres';
ALTER SYSTEM SET cron.use_background_workers = on;
ALTER SYSTEM SET cron.max_running_jobs = 5;
ALTER SYSTEM SET max_worker_processes = 16;

On Aptible, pg_cron must use background workers over localhost connections. The cron.max_running_jobs and max_worker_processes values above should be adjusted to account for the number of concurrently running cron jobs

Restart the database again, then create the extension:

CREATE EXTENSION pg_cron;

The pg_cron extension must be created in the database cron.database_name is set to. However, jobs scheduled with pg_cron can run queries against any database on the same PostgreSQL instance.

Scheduling jobs

Once enabled, schedule jobs using cron.schedule:

SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM ANALYZE my_table');

To schedule a job in a specific database:

SELECT cron.schedule_in_database('nightly-vacuum', '03 * * *', 'VACUUM ANALYZE my_table', '$DATABASE');

To view scheduled jobs:

SELECT * FROM cron.job;

To remove a scheduled job:

SELECT cron.unschedule('nightly-vacuum');

pgaudit

pgaudit provides detailed session and object audit logging for PostgreSQL, helping you meet compliance requirements by recording which statements were executed against your database.

Enabling pgaudit

First, add pgaudit to shared_preload_libraries:

ALTER SYSTEM SET shared_preload_libraries = pg_stat_statements, pglogical, pgaudit;

Restart your database using the Aptible CLI or Dashboard for the change to take effect.

Once the database has restarted, create the extension:

CREATE EXTENSION pgaudit;

Configuring audit logging

pgaudit supports two modes of logging: session (logs all statements from a session) and object (logs statements that affect specific relations).

To enable session audit logging for all DML statements (reads, writes, deletes):

ALTER SYSTEM SET pgaudit.log = 'read, write';

Restart the database for the change to take effect. You can also set pgaudit.log at the role or session level without a restart:

ALTER ROLE my_app_user SET pgaudit.log = 'read, write';

Available log classes include:

Class	Description
read	SELECT and COPY when source is a relation
write	INSERT, UPDATE, DELETE, TRUNCATE, COPY (target)
function	Function calls and DO blocks
role	GRANT, REVOKE, CREATE/ALTER/DROP ROLE
ddl	All DDL not covered by ROLE class
misc	Miscellaneous (e.g., DISCARD, FETCH, VACUUM)
all	All of the above

Object audit logging

For finer-grained control, assign an auditor role and grant it access only to the tables you want to audit:

CREATE ROLE auditor NOLOGIN;
ALTER SYSTEM SET pgaudit.role = 'auditor';
GRANT SELECT ON my_sensitive_table TO auditor;

After restarting, any SELECT on my_sensitive_table will be logged regardless of the pgaudit.log setting.

Viewing audit logs

pgaudit writes to the standard PostgreSQL log. On Aptible, these logs are available through the Log Drains configured for your environment.

pg_repack

pg_repack lets you remove bloat from tables and indexes without holding exclusive locks during the process.

Enabling pg_repack

Create the extension in the database you want to repack:

CREATE EXTENSION pg_repack;

Repacking tables

pg_repack is run using its CLI utility. Connect to your database through a database tunnel and run:

pg_repack -h $HOST -p $PORT -U $USER -d $DATABASE --table my_table

To repack all tables in a database:

pg_repack -h $HOST -p $PORT -U $USER -d $DATABASE

Common options

Option	Description
--table TABLE	Repack a specific table
--only-indexes	Repack only indexes on the specified table
--index INDEX	Repack a specific index
--no-order	Repack without reordering by cluster index
--wait-timeout N	Seconds to wait for lock acquisition (default: 60)

pg_repack requires free disk space roughly equal to the size of the table being repacked. Monitor your disk usage before running it on large tables.

pgvector

pgvector adds vector similarity search to PostgreSQL, enabling you to store embeddings and perform nearest-neighbor queries directly in your database.

Enabling pgvector

Create the extension:

CREATE EXTENSION vector;

Storing vectors

Add a vector column to your table by specifying the number of dimensions:

CREATE TABLE documents (
  id BIGSERIAL PRIMARY KEY,
  content TEXT,
  embedding VECTOR(1536)
);

Insert vector data as an array-like string:

INSERT INTO documents (content, embedding)
VALUES ('example document', '[0.1, 0.2, 0.3, ...]');

Querying vectors

Find the 5 nearest neighbors using cosine distance (<=>):

SELECT id, content, embedding <=> '[0.1, 0.2, 0.3, ...]' AS distance
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, 0.3, ...]'
LIMIT 5;

Other supported distance operators:

Operator	Distance metric
<->	L2 (Euclidean)
<=>	Cosine
<#>	Inner product (negative)

Indexing for performance

For large datasets, create an index to speed up similarity searches. pgvector supports two index types:

HNSW (recommended for most use cases):

CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops);

IVFFlat (faster to build, good for very large datasets):

CREATE INDEX ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

Choose the operator class that matches your distance metric: vector_cosine_ops for cosine, vector_l2_ops for L2, or vector_ip_ops for inner product.

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.

Aptible maintains a link between replicas and their source Database to ensure the source Database cannot be deleted before the replica. To deprovision the source Database after you’ve failed over to a promoted replica, users with the appropriate roles and permissions can unlink the replica from the source Database. Navigate to the replica’s settings page to complete the unlinking process. 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.

Point-in-time Recovery

Point-in-time Recovery (PITR) is available for PostgreSQL 13 and newer. PITR lets you restore your database to any specific moment in time, protecting against accidental data deletions, corruptions, or other errors.

Aptible automatically enables PITR for new PostgreSQL 13+ databases if your Environment’s backup retention policy has at least 1 day of recovery data retention configured.

For more details on configuring and using PITR, see Point-in-Time Recovery (PITR).

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.

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.

Autovacuum

Postgres Autovacuum is enabled by default on all supported Aptible PostgreSQL managed databases. Autovacuum is configured with default settings related to Vacuum, which can be inspected with:

SELECT * FROM pg_settings WHERE name LIKE '%autovacuum%;'

The settings associated with autovacuum can be adjusted with ALTER SYSTEM

Connection Security

Aptible PostgreSQL Databases support connections via the following protocols:

• For PostgreSQL version 14: TLSv1.2

• For PostgreSQL versions 15, 16, 17, and 18: TLSv1.2, TLSv1.3