> ## Documentation Index
> Fetch the complete documentation index at: https://www.aptible.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL

> Learn about running secure, Managed PostgreSQL Databases on Aptible

# Available Versions

The following versions of [PostgreSQL](https://www.postgresql.org/) 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  |

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

<Note>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](https://www.aptible.com/docs/core-concepts/managed-databases/managing-databases/database-upgrade-methods#database-upgrades). 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.</Note>

# Connecting to PostgreSQL

Aptible PostgreSQL [Databases](/core-concepts/managed-databases/overview) 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](https://www.postgresql.org/docs/current/libpq-ssl.html#LIBQ-SSL-CERTIFICATES).

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](/core-concepts/managed-databases/managing-databases/database-encryption/database-encryption-in-transit#self-signed-certificates) for more details.

# Extensions

The listed extensions alongside those listed in [PostgreSQL's documentation here](https://www.postgresql.org/docs/current/contrib.html) 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](/how-to-guides/troubleshooting/aptible-support) to identify whether it's a good fit for Aptible.

<Accordion title="pg_cron">
  [pg\_cron](https://github.com/citusdata/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`:

  ```sql theme={null}
  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:

  ```sql theme={null}
  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;
  ```

  <Note>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 </Note>

  Restart the database again, then create the extension:

  ```sql theme={null}
  CREATE EXTENSION pg_cron;
  ```

  <Note>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.</Note>

  ### Scheduling jobs

  Once enabled, schedule jobs using `cron.schedule`:

  ```sql theme={null}
  SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM ANALYZE my_table');
  ```

  To schedule a job in a specific database:

  ```sql theme={null}
  SELECT cron.schedule_in_database('nightly-vacuum', '03 * * *', 'VACUUM ANALYZE my_table', '$DATABASE');
  ```

  To view scheduled jobs:

  ```sql theme={null}
  SELECT * FROM cron.job;
  ```

  To remove a scheduled job:

  ```sql theme={null}
  SELECT cron.unschedule('nightly-vacuum');
  ```
</Accordion>

<Accordion title="pgaudit">
  [pgaudit](https://github.com/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`:

  ```sql theme={null}
  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:

  ```sql theme={null}
  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):

  ```sql theme={null}
  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:

  ```sql theme={null}
  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:

  ```sql theme={null}
  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](/core-concepts/observability/logs/log-drains/overview) configured for your environment.
</Accordion>

<Accordion title="pg_repack">
  [pg\_repack](https://github.com/reorg/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:

  ```sql theme={null}
  CREATE EXTENSION pg_repack;
  ```

  ### Repacking tables

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

  ```bash theme={null}
  pg_repack -h $HOST -p $PORT -U $USER -d $DATABASE --table my_table
  ```

  To repack all tables in a database:

  ```bash theme={null}
  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) |

  <Note>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.</Note>
</Accordion>

<Accordion title="pgvector">
  [pgvector](https://github.com/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:

  ```sql theme={null}
  CREATE EXTENSION vector;
  ```

  ### Storing vectors

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

  ```sql theme={null}
  CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536)
  );
  ```

  Insert vector data as an array-like string:

  ```sql theme={null}
  INSERT INTO documents (content, embedding)
  VALUES ('example document', '[0.1, 0.2, 0.3, ...]');
  ```

  ### Querying vectors

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

  ```sql theme={null}
  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):

  ```sql theme={null}
  CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops);
  ```

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

  ```sql theme={null}
  CREATE INDEX ON documents
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);
  ```

  <Note>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.</Note>
</Accordion>

# Replication

Primary-standby [replication](/core-concepts/managed-databases/managing-databases/replication-clustering) is available for PostgreSQL. Replicas can be created using the [`aptible db:replicate`](/reference/aptible-cli/cli-commands/cli-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](/core-concepts/apps/overview) 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](/core-concepts/security-compliance/access-permissions#full-permission-type-matrix) 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](/how-to-guides/platform-guides/deprovision-resources) documentation for considerations when deprovisioning a Database.

# Data Integrity and Durability

On Aptible, PostgreSQL is configured with default settings for [write-ahead logging](https://www.postgresql.org/docs/current/static/wal-intro.html). 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)](/core-concepts/managed-databases/managing-databases/point-in-time-recovery).

# Configuration

A PostgreSQL database's [`pg_settings`](https://www.postgresql.org/docs/current/view-pg-settings.html) can be changed with [`ALTER SYSTEM`](https://www.postgresql.org/docs/current/sql-altersystem.html). 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](https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM) is enabled by default on all supported Aptible PostgreSQL managed databases. Autovacuum is configured with
default settings related to [Vacuum](https://www.postgresql.org/docs/current/sql-vacuum.html), which can be inspected with:

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

The settings associated with autovacuum can be adjusted with [ALTER SYSTEM](https://www.postgresql.org/docs/current/sql-altersystem.html)

# 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`