Percona Server for PostgreSQL 18.6

Option A: AWS Marketplace console

• Open the SecureAnyCloud Hardened PostgreSQL 18, PostGIS & Postgres Extension AMIs listing on AWS Marketplace. Choose View purchase options, then Subscribe. One subscription covers every version in the listing.
• When the subscription is active, choose Launch your software.
• 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.
• Pick an instance type (m5.large recommended; the software charge is $0.04 per vCPU per hour) and your key pair.
• Set the security group. The listing's default group allows SSH (22) only. Restrict it to your own IP address, and do not open 5432 to the internet.
• Keep both volumes. You can make the data volume bigger at launch, and 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. Replace 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, because the AMI already requires IMDSv2. The data volume is part of the AMI's block device mapping, so it is created with the instance.

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 command leaves out the password line. 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 (sac 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 in the following steps 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>.

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 and 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)

The SQL extension version of pg_stat_monitor is 2.4. The library itself reports the release, 2.4.0.

Read the demo row, add 1,000 rows and 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 sac_demo ORDER BY id;" \
  -c "INSERT INTO sac_demo (note) SELECT 'row ' || g FROM generate_series(1, 1000) AS g;" \
  -c "SELECT count(*) FROM sac_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 '%sac_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 sac - 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 sac_demo (note) SELECT 'row ' || g FROM gen postgres | 1 | 1 | 0.14 | SELECT count(*) FROM sac_demo WHERE note LIKE 'row %' postgres | 1 | 1 | 0.05 | SELECT id, note FROM sac_demo ORDER BY id (3 rows)

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

pgAudit writes an AUDIT: line to the PostgreSQL log for every DDL statement and role change (pgaudit.log = 'ddl, role'). Create a table and drop it, 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 statement with <REDACTED>. PostgreSQL's own error and slow-query logging does not, so always change passwords with \password (see Maintenance).

To audit reads and writes as well, add classes to pgaudit.log (for example 'ddl, role, write') in 10-sac.conf and restart PostgreSQL.

pg_repack rebuilds a table and its indexes while the table stays readable and writable. Enable it in the database and 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 sac_demo WHERE note LIKE 'row %';" PGSSLMODE=require pg_repack -h 127.0.0.1 -U postgres -d appdb --table=sac_demo pg_repack --version

CREATE EXTENSION DELETE 1000 INFO: repacking table "public.sac_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-sac.conf and restart PostgreSQL. Then create a slot with SELECT pg_create_logical_replication_slot('cdc', 'wal2json');.

PostgreSQL accepts connections on localhost only. There are 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-sac.conf, change listen_addresses = '127.0.0.1' to listen_addresses = '*' and 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/sac-server.crt) and the role's password. These commands make the configuration change and restart PostgreSQL:

sudo sed -i "s/^listen_addresses = '127.0.0.1'/listen_addresses = '*'/" /etc/postgresql/18/main/conf.d/10-sac.conf
sudo systemctl restart postgresql@18-main.service

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

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

OUT="$(PGPASSWORD=<set-at-first-boot> 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"

Confirm that the PostgreSQL unit carries the metadata block, that PostgreSQL listens on loopback and uses this instance's own certificate, and that its data lives 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/sac-server.crt /var/lib/percona-postgresql/18/main /dev/nvme1n1 /var/lib/percona-postgresql ext4