Databases AWS

Percona Server for PostgreSQL 18.6 on AWS User Guide

| Product: Percona Server for PostgreSQL 18.6 on Ubuntu 24.04 on AWS

Overview

This guide covers launching and verifying Percona Server for PostgreSQL 18.6 on Ubuntu 24.04 from cloudimg on Amazon EC2. It is one version of the cloudimg Hardened PostgreSQL 18, PostGIS & Postgres Extension AMIs listing on AWS Marketplace; you pick it as the software version when you subscribe and launch. The other PostgreSQL builds and extensions in the listing are described on the listing's guide hub.

Percona Server for PostgreSQL is Percona's distribution of PostgreSQL: the PostgreSQL 18.6 server plus a set of production extensions Percona builds, tests and ships together. The image installs it from Percona's own apt repository (repo.percona.com, the ppg-18 release channel) with these components:

  • pg_stat_monitor 2.4.0: Percona's query performance monitor, a bucketed and richer pg_stat_statements (calls, timings, rows, client, plan and error details per query). Version 2.4.0 also stops unprivileged users reading details of other users' queries.
  • pgAudit 18.0: session and object audit logging. The image audits DDL and role changes out of the box.
  • pg_repack 1.5.3: removes table and index bloat online, without the long exclusive locks of VACUUM FULL.
  • wal2json 2.6: a logical decoding output plugin that streams changes as JSON (for change data capture).
  • The standard PostgreSQL contrib modules.

The packages are not held, so apt upgrade keeps delivering Percona's PostgreSQL 18 minor (security) releases, and unattended security upgrades stay on for the operating system.

What is on the image:

  • PostgreSQL 18.6 (postgresql@18-main.service) with pg_stat_monitor and pgaudit preloaded
  • A dedicated 40 GiB gp3 data volume mounted at /var/lib/percona-postgresql, holding the cluster's data directory, so database storage is separate from the operating system disk and can be resized on its own
  • A brand-new cluster created on your instance's first boot, with an appdb database, the pg_stat_monitor and pgaudit extensions and a one-row cloudimg_demo table
  • TLS with a certificate generated on your instance's first boot (TLS 1.2 minimum)
  • A credential guard that runs on every boot and refuses to serve if a published or example password ever authenticates

Default user: ubuntu · Architecture: x86_64 · Volumes: 8 GiB gp3 root + 40 GiB gp3 data

Security posture

  • No database ships in the image. The data directory is empty in the AMI. First boot runs initdb on your instance, so no role, password, demo row or cluster identity is shared with any other instance.
  • No default password. First boot generates a 32-character password for the postgres superuser and writes it to /root/percona-postgresql-credentials.txt, readable by root only. Locally, postgres is also reachable as the postgres system user (peer authentication).
  • Nothing is served before first boot finishes. PostgreSQL will not start until first boot has created the cluster, the certificate and the password. While it sets the password, the server listens on its local socket only, with no TCP port and with audit and statement logging off.
  • Per-instance TLS certificate. First boot creates this instance's own private key and self-signed certificate under /etc/postgresql/18/main/ and turns TLS on. No key ships in the image, so no two instances share one.
  • Localhost only. PostgreSQL listens on 127.0.0.1:5432, and the listing's security group opens SSH (22) only. Reach the database through an SSH tunnel, or open it to your VPC as described in Step 7.
  • Remote connections require TLS. pg_hba.conf accepts non-loopback clients only over hostssl with scram-sha-256; there is no plain-text remote rule and no trust rule.
  • No metadata access for the database. A database user can make PostgreSQL open outbound connections (for example COPY ... FROM PROGRAM as a superuser), so every PostgreSQL unit and both first-boot services are blocked from reaching the instance metadata service (169.254.169.254 and fd00:ec2::254). Amazon Time Sync (169.254.169.123) still works. If you add WAL archiving or backups to S3 from inside the database, give them explicit credentials; they cannot use the instance role.
  • IMDSv2 required. The AMI is registered with ImdsSupport v2.0, so instances launched from it refuse token-less (IMDSv1) metadata requests.
  • Fails closed. If first boot cannot finish, PostgreSQL stays stopped and first boot tries again on the next boot.
  • Per-instance identity. SSH host keys and the machine ID are generated fresh on every instance.

Prerequisites

  1. An AWS account and permission to launch EC2 instances and create security groups
  2. An EC2 key pair in the Region you launch in
  3. A VPC subnet, with a public IP on the instance if you connect over the internet

Recommended instance type: m5.large (2 vCPU, 8 GiB). The image's memory settings (shared_buffers = 1GB, effective_cache_size = 2GB) are sized for it; raise them in /etc/postgresql/18/main/conf.d/10-cloudimg.conf on larger instances.

Connecting to your instance

Version Operating system SSH user
Percona Server for PostgreSQL 18.6 on Ubuntu 24.04 Ubuntu 24.04 LTS ubuntu

Step 1: Launch the instance

Option A: AWS Marketplace console

  1. Open the cloudimg Hardened PostgreSQL 18, PostGIS & Postgres Extension AMIs listing on AWS Marketplace and choose View purchase options, then Subscribe. One subscription covers every version in the listing.
  2. Once the subscription is active, choose Launch your software.
  3. Choose One-click launch from AWS Marketplace (or Launch from EC2 Console for the full launch wizard). In Version, choose Percona Server for PostgreSQL 18.6 on Ubuntu 24.04, then choose your Region.
  4. Pick an instance type (m5.large recommended; the software charge is $0.04 per vCPU per hour) and your key pair.
  5. Security group: the listing's default group allows SSH (22) only. Restrict it to your own IP address. Do not open 5432 to the internet.
  6. Keep both volumes. You can make the data volume bigger at launch (the cluster uses whatever size it has). Choose your VPC and subnet, leave the metadata defaults (the image already requires IMDSv2), then choose Launch.

Option B: AWS CLI

Run this on your own workstation. Replace the AMI ID with the one shown for your Region on the Marketplace launch page, and the VPC, subnet, key pair and CIDR with your own:

SG=$(aws ec2 create-security-group --group-name percona-postgresql \
  --description "Percona Server for PostgreSQL" --vpc-id vpc-0123456789abcdef0 \
  --query GroupId --output text)
aws ec2 authorize-security-group-ingress --group-id "$SG" --protocol tcp --port 22 --cidr <your-mgmt-cidr>
aws ec2 run-instances \
  --image-id ami-0123456789abcdef0 \
  --instance-type m5.large \
  --key-name my-key-pair \
  --security-group-ids "$SG" \
  --subnet-id subnet-0123456789abcdef0 \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=percona-postgresql}]'

You do not need --metadata-options: the AMI already makes IMDSv2 required. The data volume is part of the AMI's block device mapping, so it is created with the instance.

Step 2: Connect and read your credentials

Connect as ubuntu with the key pair you chose:

ssh -i ~/.ssh/my-key-pair.pem ubuntu@<public-ip>

First boot finishes within a minute of launch. Show the connection details (the password line is left out here; read it with sudo cat /root/percona-postgresql-credentials.txt and keep it in your secrets manager):

sudo grep -v -e '^postgres.password=' /root/percona-postgresql-credentials.txt | head -13

# Percona Server for PostgreSQL 18.6 (cloudimg AWS Marketplace image)
# Generated on THIS instance's first boot by percona-postgresql-firstboot.service; this
# password exists nowhere else and is shown in plain text only here. Rotate it (never on a
# command line) with:  sudo -u postgres psql   then   \password postgres
# and update this file to match.

postgres.host=127.0.0.1
postgres.port=5432
postgres.role=postgres
postgres.database=appdb
postgres.sslmode=require
postgres.private_ip=172.31.88.130

The commands below read the password from that file at run time, so it never appears on screen. In your own sessions you can instead put it in ~/.pgpass (mode 0600) as 127.0.0.1:5432:appdb:postgres:<POSTGRES_PASSWORD>.

Step 3: Confirm Percona Server for PostgreSQL is running

psql --version
systemctl is-active postgresql@18-main.service percona-postgresql-firstboot.service percona-postgresql-credguard.service
sudo ss -Hltn

psql (PostgreSQL) 18.6 - Percona Server for PostgreSQL 18.6.1
active
active
active
LISTEN 0      4096      127.0.0.54:53   0.0.0.0:*
LISTEN 0      200        127.0.0.1:5432 0.0.0.0:*
LISTEN 0      4096   127.0.0.53%lo:53   0.0.0.0:*
LISTEN 0      4096         0.0.0.0:22   0.0.0.0:*
LISTEN 0      4096            [::]:22      [::]:*

PostgreSQL listens on 127.0.0.1:5432 only; port 53 on 127.0.0.53/127.0.0.54 is the operating system's local DNS stub. Now connect over TLS, and show the server version, the installed extensions and the TLS session:

export PGPASSWORD="$(sudo sed -n 's/^postgres.password=//p' /root/percona-postgresql-credentials.txt)"
psql "host=127.0.0.1 dbname=appdb user=postgres sslmode=require" \
  -c "SELECT version();" \
  -c "SELECT extname, extversion FROM pg_extension ORDER BY 1;" \
  -c "SELECT pg_stat_monitor_version();" \
  -c "SELECT ssl, version AS tls_version FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

                                                                    version
------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 18.6 - Percona Server for PostgreSQL 18.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
(1 row)

     extname     | extversion
-----------------+------------
 pg_stat_monitor | 2.4
 pgaudit         | 18.0
 plpgsql         | 1.0
(3 rows)

 pg_stat_monitor_version
-------------------------
 2.4.0
(1 row)

 ssl | tls_version
-----+-------------
 t   | TLSv1.3
(1 row)

pg_stat_monitor's SQL extension version is 2.4; the library itself reports the release, 2.4.0.

Step 4: See your queries in pg_stat_monitor

Read the demo row, add 1,000 rows, count them, then ask pg_stat_monitor what ran against the table, with call counts, rows and time:

export PGPASSWORD="$(sudo sed -n 's/^postgres.password=//p' /root/percona-postgresql-credentials.txt)"
psql "host=127.0.0.1 dbname=appdb user=postgres sslmode=require" \
  -c "SELECT id, note FROM cloudimg_demo ORDER BY id;" \
  -c "INSERT INTO cloudimg_demo (note) SELECT 'row ' || g FROM generate_series(1, 1000) AS g;" \
  -c "SELECT count(*) FROM cloudimg_demo WHERE note LIKE 'row %';"
psql "host=127.0.0.1 dbname=appdb user=postgres sslmode=require" \
  -c "SELECT username, calls, rows, round(total_exec_time::numeric, 2) AS total_ms, left(query, 60) AS query FROM pg_stat_monitor WHERE datname = 'appdb' AND query LIKE '%cloudimg_demo%' AND query NOT LIKE '%pg_stat_monitor%' ORDER BY calls DESC, total_exec_time DESC LIMIT 5;"

 id |                              note
----+-----------------------------------------------------------------
  1 | Percona Server for PostgreSQL by cloudimg - first boot complete
(1 row)

INSERT 0 1000
 count
-------
  1000
(1 row)

 username | calls | rows | total_ms |                            query
----------+-------+------+----------+--------------------------------------------------------------
 postgres |     1 | 1000 |     2.42 | INSERT INTO cloudimg_demo (note) SELECT 'row ' || g FROM gen
 postgres |     1 |    1 |     0.14 | SELECT count(*) FROM cloudimg_demo WHERE note LIKE 'row %'
 postgres |     1 |    1 |     0.05 | SELECT id, note FROM cloudimg_demo ORDER BY id
(3 rows)

pg_stat_monitor groups statistics into time buckets (bucket, bucket_start_time) and also records the client address, application name, error messages (elevel, sqlcode, message) and the query plan. Run \d pg_stat_monitor in psql for every column.

Step 5: Audit DDL with pgAudit

pgAudit writes an AUDIT: line to the PostgreSQL log for every DDL statement and role change (pgaudit.log = 'ddl, role'). Create and drop a table, then read the two audit records:

export PGPASSWORD="$(sudo sed -n 's/^postgres.password=//p' /root/percona-postgresql-credentials.txt)"
psql "host=127.0.0.1 dbname=appdb user=postgres sslmode=require" \
  -c "CREATE TABLE audit_demo (id int);" -c "DROP TABLE audit_demo;"
sudo grep -a 'AUDIT: SESSION' /var/log/postgresql/postgresql-18-main.log | grep -a audit_demo | tail -2 | sed -E 's/^.* LOG:  //'

CREATE TABLE
DROP TABLE
AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.audit_demo,CREATE TABLE audit_demo (id int),<not logged>
AUDIT: SESSION,2,1,DDL,DROP TABLE,TABLE,public.audit_demo,DROP TABLE audit_demo,<not logged>

pgAudit replaces the password in a logged ALTER ROLE ... PASSWORD with <REDACTED>, but PostgreSQL's own error and slow-query logging does not, so always change passwords with \password (Maintenance below). To audit reads and writes as well, add classes to pgaudit.log (for example 'ddl, role, write') in 10-cloudimg.conf and restart.

Step 6: Remove bloat online with pg_repack

pg_repack rebuilds a table and its indexes while the table stays readable and writable. Enable it in the database, delete the 1,000 rows from Step 4 (which leaves dead space behind), then repack the table:

export PGPASSWORD="$(sudo sed -n 's/^postgres.password=//p' /root/percona-postgresql-credentials.txt)"
psql "host=127.0.0.1 dbname=appdb user=postgres sslmode=require" \
  -c "CREATE EXTENSION IF NOT EXISTS pg_repack;" \
  -c "DELETE FROM cloudimg_demo WHERE note LIKE 'row %';"
PGSSLMODE=require pg_repack -h 127.0.0.1 -U postgres -d appdb --table=cloudimg_demo
pg_repack --version

CREATE EXTENSION
DELETE 1000
INFO: repacking table "public.cloudimg_demo"
pg_repack 1.5.3

wal2json is installed as a logical decoding plugin. To use it for change data capture, set wal_level = logical in 10-cloudimg.conf, restart PostgreSQL, and create a slot with SELECT pg_create_logical_replication_slot('cdc', 'wal2json');.

Step 7: Connect from your application

PostgreSQL accepts connections on localhost only. Two ways to reach it:

  • SSH tunnel (no change to the instance). On your workstation run ssh -N -L 5432:127.0.0.1:5432 ubuntu@<public-ip>, then connect your client to 127.0.0.1:5432 as postgres (or a role you create) with sslmode=require.
  • Open it to your VPC. In /etc/postgresql/18/main/conf.d/10-cloudimg.conf change listen_addresses = '127.0.0.1' to listen_addresses = '*', restart PostgreSQL, then add an inbound rule for 5432 to the instance's security group from your application subnet or VPC CIDR only (never 0.0.0.0/0). Remote clients must use TLS (sslmode=require, or verify-full with this instance's certificate /etc/postgresql/18/main/cloudimg-server.crt) and the role's password:
sudo sed -i "s/^listen_addresses = '127.0.0.1'/listen_addresses = '*'/" /etc/postgresql/18/main/conf.d/10-cloudimg.conf
sudo systemctl restart postgresql@18-main.service

For an application, create its own least-privilege role rather than using postgres: in sudo -u postgres psql -d appdb run CREATE ROLE app LOGIN; and then \password app.

A plain-text (non-TLS) connection from another host is refused by pg_hba.conf, and a wrong password is refused by scram-sha-256:

OUT="$(PGPASSWORD=wrong-password psql "host=127.0.0.1 dbname=appdb user=postgres sslmode=require" -c "SELECT 1" 2>&1 || true)"
echo "${OUT#*failed: }"

FATAL:  password authentication failed for user "postgres"

Step 8: Check the security controls

The PostgreSQL unit carries the metadata block, PostgreSQL listens on loopback, uses this instance's own certificate, and keeps its data on the dedicated volume:

systemctl show -p IPAddressDeny postgresql@18-main.service
sudo -u postgres psql -tAc "SHOW listen_addresses;"
sudo -u postgres psql -tAc "SHOW ssl_cert_file;"
sudo -u postgres psql -tAc "SHOW data_directory;"
findmnt -no SOURCE,TARGET,FSTYPE /var/lib/percona-postgresql

IPAddressDeny=fd00:ec2::254/128 169.254.169.254/32 168.63.129.16/32
127.0.0.1
/etc/postgresql/18/main/cloudimg-server.crt
/var/lib/percona-postgresql/18/main
/dev/nvme1n1 /var/lib/percona-postgresql ext4

Maintenance

  • Change a password. Run sudo -u postgres psql and type \password postgres at the prompt; it asks for the new password twice, sends only a SCRAM verifier and never puts it on a command line or in a log. Then update /root/percona-postgresql-credentials.txt (the credential guard reads it on every boot) and your secrets manager.
  • Configuration. cloudimg's settings are in /etc/postgresql/18/main/conf.d/10-cloudimg.conf (listener, memory, preloaded extensions, pgAudit classes, logging) and 20-cloudimg-tls.conf (this instance's certificate). Apply changes with sudo systemctl restart postgresql@18-main.service.
  • Storage. To grow the database, enlarge the data volume in the EC2 console (Modify volume), then run sudo resize2fs /dev/nvme1n1 (check the device name with findmnt /var/lib/percona-postgresql).
  • Updates. sudo apt update && sudo apt upgrade installs Percona's PostgreSQL 18 minor releases and extension updates from repo.percona.com, and Ubuntu security updates. After an extension update, run ALTER EXTENSION pg_stat_monitor UPDATE; (and likewise for pgaudit) in each database.
  • Backups. Take EBS snapshots of the data volume or pg_dump -Fc appdb backups regularly.
  • Logs. /var/log/postgresql/postgresql-18-main.log (including pgAudit records) and journalctl -u postgresql@18-main.service; first boot and the credential guard log to journalctl -u percona-postgresql-firstboot.service and journalctl -u percona-postgresql-credguard.service.
  • Licences. Percona Server for PostgreSQL, the contrib modules, pg_stat_monitor and pgAudit are under The PostgreSQL License; pg_repack and wal2json are BSD-3-Clause. /usr/share/doc/percona-postgresql-cloudimg/ATTRIBUTION.txt and BUILD_VERSIONS list the exact packages.

Support

cloudimg supports this image by email at support@cloudimg.co.uk. Include the instance ID, the version (Percona Server for PostgreSQL 18.6 on Ubuntu 24.04) and what you were doing.

PostgreSQL is a trademark of the PostgreSQL Community Association of Canada. Percona is a trademark of Percona LLC. Ubuntu is a trademark of Canonical Ltd. cloudimg is not affiliated with or endorsed by these organisations.