PostgreSQL 17 (Amazon Linux 2023) AMI Administrator Guide

1. Quick Start Information

Connection Methods:

Access the instance via SSH using the ec2-user user. Use sudo to run commands requiring root privileges. To switch to the root user, use sudo su - root.

Install Information:

OS: Amazon Linux 2023
PostgreSQL version: 17
Port: 5432
Superuser: postgres
Password: Your AWS EC2 Instance ID (set automatically on first boot)
Data directory: /var/lib/pgsql/data/
Config file: /var/lib/pgsql/data/postgresql.conf
HBA config: /var/lib/pgsql/data/pg_hba.conf
Log directory: /var/lib/pgsql/data/log/

How to view your password:

After the instance boots, SSH in and read the readme file:

cat /home/ec2-user/readme

The readme contains the host, port, username, and password for your instance.

PostgreSQL Service Management:

Start PostgreSQL: sudo systemctl start postgresql
Stop PostgreSQL: sudo systemctl stop postgresql
Restart PostgreSQL: sudo systemctl restart postgresql
Check status: sudo systemctl status postgresql
Enable auto-start: sudo systemctl enable postgresql

Quick Verification Commands:

Check version: psql --version
Connect locally (no password): sudo -u postgres psql
Check port listening: sudo ss -tuln | grep 5432
View init log: cat /var/log/init_postgres_password.log

Required Ports (Security Group):

Port	Protocol	Purpose	Required
22	TCP	SSH access	Yes
5432	TCP	PostgreSQL	Required for remote connections

2. First Launch & Verification

Step 1: Verify PostgreSQL is Running

Connect via SSH and check the service:

ssh -i your-key.pem ec2-user@YOUR_PUBLIC_IP
sudo systemctl status postgresql --no-pager

Expected Output:

● postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; ...)
     Active: active (running) since ...

Step 2: Confirm Port 5432 is Listening

sudo ss -tuln | grep 5432

Expected Output:

tcp   LISTEN 0      244    0.0.0.0:5432    0.0.0.0:*
tcp   LISTEN 0      244       [::]:5432       [::]:*

Step 3: Connect Locally via Unix Socket

The postgres system user can connect without a password via the Unix socket (peer authentication):

sudo -u postgres psql

Expected Output:

psql (17.x)
Type "help" for help.

postgres=#

Step 4: Verify Password Initialization

Check that the cloud-init password script ran successfully on first boot:

cat /var/log/init_postgres_password.log

Expected Output:

=== PostgreSQL password init started at ...
Instance ID retrieved: i-0xxxxxxxxxxxxxxxxx
ALTER ROLE
Password set successfully to Instance ID.
=== Init complete at ...

Read the readme file to get your connection details and password:

cat /home/ec2-user/readme

Test TCP authentication using the password shown in the readme:

psql -h 127.0.0.1 -U postgres -W

A successful connection confirms the password was set correctly.

3. Architecture & Detailed Configuration

This AMI runs PostgreSQL 17 on Amazon Linux 2023. The postgres superuser password is automatically set to the AWS EC2 Instance ID on first boot via a cloud-init per-instance script — each instance gets a unique, secure password without any manual setup.

Installation Architecture:

[Amazon Linux 2023]
         ↓
[dnf install postgresql17-server postgresql17-contrib]
/usr/bin/postgres          → PostgreSQL server binary
/var/lib/pgsql/data/       → data directory (initdb output)
         ↓
[Configuration]
/var/lib/pgsql/data/postgresql.conf  → main server config (listen_addresses = '*')
/var/lib/pgsql/data/pg_hba.conf      → client authentication rules
         ↓
[Systemd Service]
postgresql.service → Auto-start on boot
         ↓
[cloud-init: per-instance password initialization]
Password = EC2 Instance ID (set once on buyer's first boot)
         ↓
[Listening on 0.0.0.0:5432]

Key Design Decisions:

postgresql17-contrib included: Provides essential extensions such as pg_stat_statements (query performance monitoring), uuid-ossp (UUID generation), pgcrypto, and tablefunc — ready to enable without additional installation
listen_addresses = '*': Accepts connections on all network interfaces, enabling remote access from tools like DBeaver, Navicat, or DataGrip
scram-sha-256 for remote auth: Modern, secure authentication protocol for all TCP connections
peer auth preserved for Unix socket: sudo -u postgres psql always works — a critical administrative recovery path that does not depend on password
Instance ID as password: Unique per instance, immediately available to the buyer, no manual password distribution needed
Auto-start enabled: postgresql.service starts automatically on every boot

3.1. postgresql.conf (Key Settings)

File Location: /var/lib/pgsql/data/postgresql.conf

Modified Settings:

# Accept connections on all network interfaces
listen_addresses = '*'

# Default port
port = 5432

How This Works:

listen_addresses = '*': By default, PostgreSQL only listens on localhost. Changing this to * allows TCP connections from any IP address — required for remote database clients.
All other settings remain at PostgreSQL defaults and can be tuned after deployment.
Restart is required after any change to postgresql.conf: sudo systemctl restart postgresql

3.2. pg_hba.conf (Client Authentication)

File Location: /var/lib/pgsql/data/pg_hba.conf

Complete Contents:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Unix domain socket connections (local admin access)
local   all             all                                     peer
# IPv4 local connections
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections
host    all             all             ::1/128                 ident
# Remote connections (all IPs)
host    all             all             0.0.0.0/0               scram-sha-256
# Replication
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

Authentication Method Summary:

Connection Type	Method	Effect
Unix socket (local)	`peer`	No password — `sudo -u postgres psql` works directly
IPv4 local (127.0.0.1)	`scram-sha-256`	Password required for local TCP connections
IPv6 local (::1)	`ident`	OS user matching for IPv6 local connections
Remote (0.0.0.0/0)	`scram-sha-256`	Password required for all remote connections

Why peer is preserved for Unix socket:

The local Unix socket with peer authentication is the critical administrative escape hatch. If you need to change or recover the postgres password, sudo -u postgres psql still works without any credentials, allowing ALTER USER postgres WITH PASSWORD '...' to be run directly.

Note on IPv6 local (::1): The ident method is kept for local IPv6 connections. If your application connects via ::1, change this to scram-sha-256 for consistent password-based authentication.

3.3. Cloud-Init Password Initialization Script

File Location: /var/lib/cloud/scripts/per-instance/init_postgres_password.sh

Complete Contents:

#!/bin/bash
# PostgreSQL per-instance password initialization
# Sets the postgres superuser password to the AWS EC2 Instance ID
# Runs once on first boot of each new instance

LOG="/var/log/init_postgres_password.log"
echo "=== PostgreSQL password init started at $(date) ===" >> "$LOG"

# Retrieve Instance ID via IMDSv2 (with IMDSv1 fallback)
TOKEN=$(curl -s -m 5 -X PUT \
  -H 'X-aws-ec2-metadata-token-ttl-seconds: 21600' \
  'http://169.254.169.254/latest/api/token' 2>/dev/null)

if [ -n "$TOKEN" ]; then
  INSTANCE_ID=$(curl -s -m 10 \
    -H "X-aws-ec2-metadata-token: $TOKEN" \
    'http://169.254.169.254/latest/meta-data/instance-id' 2>/dev/null)
else
  INSTANCE_ID=$(curl -s -m 10 \
    'http://169.254.169.254/latest/meta-data/instance-id' 2>/dev/null)
fi

if [ -z "$INSTANCE_ID" ]; then
  echo "Instance ID not yet available. Retrying..." >> "$LOG"
  for i in {1..15}; do
    sleep 60
    INSTANCE_ID=$(curl -s -m 10 \
      'http://169.254.169.254/latest/meta-data/instance-id' 2>/dev/null)
    [ -n "$INSTANCE_ID" ] && break
  done
fi

if [ -z "$INSTANCE_ID" ]; then
  echo "FATAL: Instance ID unavailable after retries. Aborting." >> "$LOG"
  exit 1
fi

echo "Instance ID retrieved: $INSTANCE_ID" >> "$LOG"

# Wait for PostgreSQL to be ready
for i in {1..15}; do
  if sudo -u postgres psql -c '\q' 2>/dev/null; then
    break
  fi
  echo "Waiting for PostgreSQL to start (attempt $i/15)..." >> "$LOG"
  sleep 10
done

# Set the postgres superuser password via Unix socket (peer auth — no password needed)
sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD '$INSTANCE_ID';" >> "$LOG" 2>&1

if [ $? -eq 0 ]; then
  echo "Password set successfully to Instance ID." >> "$LOG"
else
  echo "ERROR: Failed to set password." >> "$LOG"
  exit 1
fi

# Write connection info for the instance owner
PUBLIC_IP=$(curl -s -m 10 \
  -H "X-aws-ec2-metadata-token: $TOKEN" \
  'http://169.254.169.254/latest/meta-data/public-ipv4' 2>/dev/null || echo "YOUR_PUBLIC_IP")

cat > /home/ec2-user/readme << EOF
PostgreSQL 17 Connection Information
=====================================
Host:     $PUBLIC_IP
Port:     5432
User:     postgres
Password: $INSTANCE_ID  (your EC2 Instance ID)

Local access (no password):
  sudo -u postgres psql

Remote TCP access:
  psql -h $PUBLIC_IP -U postgres -W
  (Enter the Instance ID as the password)
EOF

chown ec2-user:ec2-user /home/ec2-user/readme
echo "=== Init complete at $(date) ===" >> "$LOG"

How This Works:

The script runs exactly once on first boot of each new instance (via /var/lib/cloud/scripts/per-instance/)
It retrieves the EC2 Instance ID using IMDSv2, with an IMDSv1 fallback for compatibility with older VPC configurations
A retry loop waits up to 15 minutes for PostgreSQL to fully start before setting the password
The ALTER USER command is executed via the local Unix socket using peer authentication — no existing password is required
A readme file is written to /home/ec2-user/readme with complete connection details for the instance owner

4. How-To-Create: Reproduce This Environment

This section explains how this AMI was built, allowing you to reproduce the installation on any Amazon Linux 2023 system.

Step 1: Update the System

sudo dnf update -y

How This Works:

Ensures all system packages are current before installing new software, preventing dependency conflicts.

Step 2: Install PostgreSQL 17 Server and Contrib

sudo dnf install -y postgresql17-server postgresql17-contrib

How This Works:

postgresql17-server: The PostgreSQL 17 server binary, service files, and the postgresql-setup initialization tool
postgresql17-contrib: Additional official extensions including:
- pg_stat_statements — tracks execution statistics for all SQL statements; essential for query performance monitoring
- uuid-ossp — generates universally unique identifiers (UUIDs)
- pgcrypto — cryptographic functions (hashing, encryption)
- tablefunc — table functions including crosstab (pivot tables)

Why contrib matters:

Most production deployments require at least one contrib extension. Pre-installing it avoids a second package installation step after deployment and ensures all common extensions are immediately available via CREATE EXTENSION.

Step 3: Initialize the Data Directory

sudo /usr/bin/postgresql-setup --initdb

How This Works:

PostgreSQL requires a data directory to be initialized before the service can start. postgresql-setup --initdb creates the initial database cluster at /var/lib/pgsql/data/, including:

System catalog databases (pg_catalog, information_schema, postgres, template0, template1)
Default configuration files (postgresql.conf, pg_hba.conf, pg_ident.conf)
Write-ahead log directory (pg_wal/)

Expected Output:

Initializing database ... OK

Step 4: Configure `postgresql.conf`

sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/" \
  /var/lib/pgsql/data/postgresql.conf

How This Works:

Changes listen_addresses from the default localhost to *, enabling PostgreSQL to accept TCP connections on all network interfaces. Without this change, remote connections are rejected at the network level regardless of pg_hba.conf settings.

Step 5: Configure `pg_hba.conf`

sudo tee /var/lib/pgsql/data/pg_hba.conf > /dev/null << 'EOF'
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Unix domain socket connections (local admin access)
local   all             all                                     peer
# IPv4 local connections
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections
host    all             all             ::1/128                 ident
# Remote connections (all IPs)
host    all             all             0.0.0.0/0               scram-sha-256
# Replication
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
EOF

How This Works:

local ... peer: Unix socket connections use OS-level user matching — sudo -u postgres psql always works without a password
host ... 127.0.0.1/32 ... scram-sha-256: Local TCP connections require password authentication (changed from the default ident, which would fail for non-OS-mapped users)
host ... 0.0.0.0/0 ... scram-sha-256: Remote connections from any IP require password authentication using SCRAM-SHA-256, the modern secure protocol

Step 6: Start and Enable PostgreSQL

sudo systemctl enable --now postgresql

How This Works:

enable: Configures postgresql.service to start automatically on every boot
--now: Also starts the service immediately without a separate systemctl start command

Step 7: Install the Cloud-Init Password Script

sudo tee /var/lib/cloud/scripts/per-instance/init_postgres_password.sh > /dev/null << 'SCRIPT'
[complete script from Section 3.3]
SCRIPT
sudo chmod +x /var/lib/cloud/scripts/per-instance/init_postgres_password.sh

Step 8: Set a Temporary Password Before AMI Capture

Before capturing the AMI snapshot, set a placeholder password so the database is in a valid state:

sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'TempAMIPassword';"

Step 9: AMI Pre-Capture Cleanup

Before taking the final AMI snapshot, clean up all instance-specific state:

# Reset cloud-init so per-instance scripts run again on first buyer boot
sudo cloud-init clean --logs

# Remove the readme (will be regenerated with the buyer's instance ID)
rm -f /home/ec2-user/readme

# Clear PostgreSQL logs
sudo find /var/lib/pgsql/data/log/ -type f -delete

# Clear the password init log
sudo truncate -s 0 /var/log/init_postgres_password.log 2>/dev/null || true

# Reset machine ID for proper cloud-init behavior on new instances
sudo truncate -s 0 /etc/machine-id

Step 10: Verify the Installation

psql --version
sudo systemctl status postgresql --no-pager
sudo ss -tuln | grep 5432
sudo -u postgres psql -c "SELECT version();"

Expected Results:

psql (PostgreSQL) 17.x

Active: active (running) ...

tcp   LISTEN 0   244   0.0.0.0:5432   0.0.0.0:*

                                  version
--------------------------------------------------------------------------
 PostgreSQL 17.x on x86_64-pc-linux-gnu, compiled by gcc ... (GCC) ..., 64-bit

5. Using PostgreSQL

5.1. Connecting Locally via Unix Socket

The simplest way to connect on the server — no password required:

sudo -u postgres psql

5.2. Connecting via TCP with Password

For local TCP connections or testing password authentication:

# Connect locally via TCP
psql -h 127.0.0.1 -U postgres -W

# Connect to a specific database
psql -h 127.0.0.1 -U postgres -d mydb -W

5.3. Common psql Commands

-- List databases
\l

-- Connect to a database
\c mydb

-- List tables
\dt

-- Show current user
SELECT current_user;

-- Show PostgreSQL version
SELECT version();

-- Enable query statistics extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Enable UUID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Exit psql
\q

5.4. Database Administration

# Create a database
sudo -u postgres createdb myapp

# Create a user with password
sudo -u postgres psql -c "CREATE USER myuser WITH PASSWORD 'strongpassword';"

# Grant privileges on a database
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;"

# Backup a database
sudo -u postgres pg_dump myapp > /tmp/myapp_backup.sql

# Restore a database
sudo -u postgres psql myapp < /tmp/myapp_backup.sql

6. Important File Locations

File Path	Purpose
`/var/lib/pgsql/data/postgresql.conf`	Main server configuration
`/var/lib/pgsql/data/pg_hba.conf`	Client authentication rules
`/var/lib/pgsql/data/pg_ident.conf`	OS username mapping
`/var/lib/pgsql/data/log/`	PostgreSQL server logs
`/var/lib/pgsql/data/`	Data directory (databases, WAL)
`/usr/bin/postgres`	PostgreSQL server binary
`/usr/bin/psql`	psql client binary
`/usr/bin/pg_dump`	Backup utility
`/usr/lib/systemd/system/postgresql.service`	Systemd service file
`/var/lib/cloud/scripts/per-instance/init_postgres_password.sh`	Password initialization script
`/var/log/init_postgres_password.log`	Password init log
`/home/ec2-user/readme`	Connection info (generated on first boot)

7. Troubleshooting

Issue 1: PostgreSQL Service Fails to Start

Symptoms:

Active: failed

Diagnosis:

sudo journalctl -u postgresql -n 50 --no-pager
sudo ls /var/lib/pgsql/data/log/
sudo tail -50 /var/lib/pgsql/data/log/postgresql-*.log

Common Causes:

Data directory not initialized:

sudo /usr/bin/postgresql-setup --initdb
sudo systemctl start postgresql

Port 5432 already in use:

sudo lsof -i :5432

Permission issue on data directory:

sudo chown -R postgres:postgres /var/lib/pgsql/data/
sudo systemctl start postgresql

Issue 2: Password Authentication Failed

Symptoms:

psql: error: FATAL: password authentication failed for user "postgres"

Diagnosis:

# Check if the cloud-init script ran
cat /var/log/init_postgres_password.log

# View your password from the readme
cat /home/ec2-user/readme

Solution:

If the script did not run, set the password manually using the peer-authenticated Unix socket:

sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'YOUR_INSTANCE_ID';"

Issue 3: Cannot Connect from Remote Host

Symptoms:

Connection times out or is refused from an external client (DBeaver, Navicat, psql from another machine).

Diagnosis:

# Check listen_addresses
sudo grep listen_addresses /var/lib/pgsql/data/postgresql.conf

# Check port is listening on all interfaces (should show 0.0.0.0:5432)
sudo ss -tuln | grep 5432

Solutions:

Verify listen_addresses = '*' is set in postgresql.conf
Verify pg_hba.conf contains host all all 0.0.0.0/0 scram-sha-256
Open port 5432 in your AWS EC2 security group (inbound TCP rule)
Restart after any configuration change: sudo systemctl restart postgresql

Issue 4: Repository Package Not Found

Symptoms:

No match for argument: postgresql17-server

Solution:

Clear the dnf cache and retry:

sudo dnf clean all
sudo dnf makecache
sudo dnf install -y postgresql17-server postgresql17-contrib

8. Final Notes

Key Takeaways

PostgreSQL 17 installed with postgresql17-contrib — essential extensions available to enable immediately
Password = EC2 Instance ID — set automatically on first boot, unique per instance
Remote access enabled — listen_addresses = '*' and 0.0.0.0/0 scram-sha-256 configured
Local peer auth preserved — sudo -u postgres psql always works as an administrative escape hatch
The installation is production-ready and AMI-optimized with auto-start enabled

PostgreSQL Use Cases

Relational data storage: OLTP workloads, transactional applications
JSON/JSONB storage: Semi-structured data with full indexing support
Full-text search: Built-in tsvector/tsquery for document search
Analytics: Window functions, CTEs, and parallel query execution
Geospatial data: PostGIS extension for location-based applications

Recommended Instance Types

Workload	Instance	Reason
Development / Testing	t3.micro / t3.small	Low cost, light load
Small production	t3.medium / t3.large	Balanced compute and memory
High-traffic OLTP	m5.large / m5.xlarge	Memory-optimized for buffer cache
Analytics / reporting	r5.large+	Large RAM for working sets

Additional Resources

PostgreSQL 17 Documentation: https://www.postgresql.org/docs/17/
pg_stat_statements: https://www.postgresql.org/docs/17/pgstatstatements.html
Amazon Linux 2023 PostgreSQL Guide: https://docs.aws.amazon.com/linux/al2023/ug/postgresql.html

For support or questions, please contact the Easycloud team.

1. Quick Start Information​

2. First Launch & Verification​

Step 1: Verify PostgreSQL is Running​

Step 2: Confirm Port 5432 is Listening​

Step 3: Connect Locally via Unix Socket​

Step 4: Verify Password Initialization​

3. Architecture & Detailed Configuration​

3.1. postgresql.conf (Key Settings)​

3.2. pg_hba.conf (Client Authentication)​

3.3. Cloud-Init Password Initialization Script​

4. How-To-Create: Reproduce This Environment​

Step 1: Update the System​

Step 2: Install PostgreSQL 17 Server and Contrib​

Step 3: Initialize the Data Directory​

Step 4: Configure postgresql.conf​

Step 5: Configure pg_hba.conf​

Step 6: Start and Enable PostgreSQL​

Step 7: Install the Cloud-Init Password Script​

Step 8: Set a Temporary Password Before AMI Capture​

Step 9: AMI Pre-Capture Cleanup​

Step 10: Verify the Installation​

5. Using PostgreSQL​

5.1. Connecting Locally via Unix Socket​

5.2. Connecting via TCP with Password​

5.3. Common psql Commands​

5.4. Database Administration​

6. Important File Locations​

7. Troubleshooting​

Issue 1: PostgreSQL Service Fails to Start​

Issue 2: Password Authentication Failed​

Issue 3: Cannot Connect from Remote Host​

Issue 4: Repository Package Not Found​

8. Final Notes​

Key Takeaways​

PostgreSQL Use Cases​

Recommended Instance Types​

Additional Resources​

1. Quick Start Information

2. First Launch & Verification

Step 1: Verify PostgreSQL is Running

Step 2: Confirm Port 5432 is Listening

Step 3: Connect Locally via Unix Socket

Step 4: Verify Password Initialization

3. Architecture & Detailed Configuration

3.1. postgresql.conf (Key Settings)

3.2. pg_hba.conf (Client Authentication)

3.3. Cloud-Init Password Initialization Script

4. How-To-Create: Reproduce This Environment

Step 1: Update the System

Step 2: Install PostgreSQL 17 Server and Contrib

Step 3: Initialize the Data Directory

Step 4: Configure `postgresql.conf`

Step 5: Configure `pg_hba.conf`

Step 6: Start and Enable PostgreSQL

Step 7: Install the Cloud-Init Password Script

Step 8: Set a Temporary Password Before AMI Capture

Step 9: AMI Pre-Capture Cleanup

Step 10: Verify the Installation

5. Using PostgreSQL

5.1. Connecting Locally via Unix Socket

5.2. Connecting via TCP with Password

5.3. Common psql Commands

5.4. Database Administration

6. Important File Locations

7. Troubleshooting

Issue 1: PostgreSQL Service Fails to Start

Issue 2: Password Authentication Failed

Issue 3: Cannot Connect from Remote Host

Issue 4: Repository Package Not Found

8. Final Notes

Key Takeaways

PostgreSQL Use Cases

Recommended Instance Types

Additional Resources