PostgreSQL on Amazon RDS: Setup, Secure Connection, and Best Practices

What this means in plain English

When you run PostgreSQL yourself on a virtual machine, you handle the installation, OS patches, backup scripts, storage management, and failover configuration. None of that work is related to your application.

Amazon RDS takes that operational layer away. You create a PostgreSQL instance through the console or CLI and connect to it using a standard connection string. AWS handles everything underneath.

This page is for developers setting up PostgreSQL on RDS for the first time, or engineers evaluating whether RDS is the right choice for their workload. By the end you will know how to create an instance, connect securely, enable extensions, and avoid the most common mistakes.

When to use PostgreSQL on Amazon RDS

RDS PostgreSQL is a strong choice when:

• You are building a web application, API, or internal tool that needs a relational database
• Your team already knows PostgreSQL and wants to avoid managing the server
• You need JSONB columns, window functions, CTEs, or PostGIS for geospatial data
• You want automated backups, point-in-time recovery, and Multi-AZ without configuring them yourself
• You are migrating an existing self-managed PostgreSQL instance to AWS

Common use cases include transactional backends, SaaS application databases, analytics-adjacent workloads, and content management systems.

Not ideal when:

• You need extreme write throughput or sub-10ms failover: Aurora PostgreSQL is a better fit
• You have a simple key-value or document access pattern: DynamoDB may be more appropriate
• You are building a global multi-region active-active database: RDS does not support this natively

PostgreSQL on RDS vs MySQL on RDS

Both engines are well-supported and receive full managed-service treatment from AWS. The choice comes down to your workload and team preference.

If you are starting fresh and your requirements are non-trivial, PostgreSQL is a reasonable default. For a detailed MySQL setup walkthrough, see Running MySQL on Amazon RDS.

PostgreSQL on RDS vs Aurora PostgreSQL

Aurora PostgreSQL is compatible with PostgreSQL at the protocol level but uses a different underlying storage architecture designed for higher throughput and faster failover.

Choose standard RDS PostgreSQL when:

• Cost is a priority: RDS is cheaper than Aurora for the same instance class
• Your workload is straightforward and does not require Aurora’s throughput
• You want simpler capacity planning without Aurora’s serverless pricing model

Choose Aurora PostgreSQL when:

• You need faster failover (typically under 30 seconds vs 60-120 seconds for RDS Multi-AZ)
• Your read traffic can be distributed across multiple Aurora read replicas
• You want Aurora Serverless for variable or unpredictable workloads

See the full RDS vs Aurora comparison for a detailed breakdown.

Before you begin

Before creating a PostgreSQL instance, make sure you have:

• An AWS account with permissions to create RDS instances and VPC resources
• A VPC with private subnets in at least two Availability Zones
• A DB subnet group spanning those subnets (this is required by RDS)
• A security group that allows inbound traffic on port 5432 from your application or bastion host only
• A PostgreSQL client (psql) to test connections

How RDS PostgreSQL works

When you create an RDS PostgreSQL instance, AWS provisions the following:

DB instance: a virtual machine running PostgreSQL, sized by the instance class you choose (e.g., db.t3.medium, db.r6g.large).

Endpoint: a DNS hostname that always resolves to the current primary instance. Your application connects here rather than to an IP address, so failover is transparent.

Storage: EBS-backed block storage. gp3 is the recommended general-purpose option. Storage can be set to auto-scale so you do not run out of disk unexpectedly.

Automated backups: RDS takes a daily snapshot and stores transaction logs, enabling point-in-time recovery within your retention window. See RDS Backups and High Availability for the full picture.

Multi-AZ: when enabled, RDS maintains a synchronous standby replica in a different Availability Zone. If the primary fails, RDS automatically promotes the standby and updates the endpoint DNS. Failover typically completes in 60-120 seconds.

Parameter groups: configuration files that control PostgreSQL settings like shared_buffers, max_connections, and which libraries are preloaded at startup. You cannot edit these directly on the instance. You create a custom parameter group and attach it.

Maintenance windows: a weekly time window during which AWS applies pending patches. You control the window; AWS does the patching.

Extensions: PostgreSQL functionality can be extended through installable modules. RDS supports a large set of them. Some extensions need parameter group changes before they can be activated.

Keep the instance in private subnets with no public IP. Your application connects from inside the VPC, not from the internet.

Create a PostgreSQL DB instance

Use the AWS CLI to create the instance. The command below is a production-conscious starting point, not a minimal quickstart. It includes Multi-AZ, deletion protection, and private placement by default.

aws rds create-db-instance \
  --db-instance-identifier myapp-postgres-prod \
  --db-instance-class db.t3.medium \
  --engine postgres \
  --engine-version 16.4 \
  --master-username dbadmin \
  --master-user-password REPLACE_WITH_STRONG_PASSWORD \
  --allocated-storage 50 \
  --storage-type gp3 \
  --storage-encrypted \
  --db-subnet-group-name myapp-db-subnet-group \
  --vpc-security-group-ids sg-0abc12345 \
  --backup-retention-period 14 \
  --multi-az \
  --no-publicly-accessible \
  --deletion-protection \
  --db-name myappdb

A few things worth noting:

• --engine-version: use a currently supported major version that matches your application’s requirements. Check the AWS console or CLI for available versions at the time you create the instance.
• --storage-encrypted: encrypts the instance and all automated backups at rest. Enable this from the start — you cannot add it later without restoring to a new instance.
• --no-publicly-accessible: keeps the instance off the public internet. This is the correct default for all production databases.
• --db-name: creates an initial database inside the instance. If you omit this, the default PostgreSQL database is named postgres.

The instance typically takes 5-10 minutes to become available.

Connect securely with psql

Once the instance is available, retrieve the endpoint and connect from inside the VPC:

# Retrieve the endpoint address
aws rds describe-db-instances \
  --db-instance-identifier myapp-postgres-prod \
  --query 'DBInstances[0].Endpoint.Address' \
  --output text

Connect using SSL in verify-full mode, which encrypts the connection and verifies the server certificate:

# Download the RDS global certificate bundle (do this once)
curl -o global-bundle.pem https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem

# Connect with SSL verification
psql "host=myapp-postgres-prod.xxxx.eu-west-2.rds.amazonaws.com \
      port=5432 \
      dbname=myappdb \
      user=dbadmin \
      sslmode=verify-full \
      sslrootcert=/path/to/global-bundle.pem"

Run psql from an EC2 instance or bastion host inside the same VPC. Do not run it from your laptop against a publicly accessible endpoint. For connecting from a local machine, use SSM Session Manager port forwarding or an SSH tunnel through a bastion host. The Connecting to Amazon RDS Securely page covers all four connection patterns in detail.

Enable PostgreSQL extensions

RDS PostgreSQL supports most popular extensions, but the setup path varies depending on the extension.

Extensions that can be enabled directly (no parameter group change needed):

-- Generate UUIDs
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Store and query vector embeddings
CREATE EXTENSION IF NOT EXISTS vector;

-- Cryptographic functions
CREATE EXTENSION IF NOT EXISTS pgcrypto;

Extensions that require a parameter group change first:

Some extensions must be added to shared_preload_libraries and loaded at startup before CREATE EXTENSION will work. pg_stat_statements is the most common example.

# Create a custom parameter group
aws rds create-db-parameter-group \
  --db-parameter-group-name myapp-postgres16-params \
  --db-parameter-group-family postgres16 \
  --description "Custom PostgreSQL 16 parameters"

# Add pg_stat_statements to shared_preload_libraries
aws rds modify-db-parameter-group \
  --db-parameter-group-name myapp-postgres16-params \
  --parameters \
    ParameterName=shared_preload_libraries,ParameterValue=pg_stat_statements,ApplyMethod=pending-reboot

# Attach the parameter group and reboot
aws rds modify-db-instance \
  --db-instance-identifier myapp-postgres-prod \
  --db-parameter-group-name myapp-postgres16-params \
  --apply-immediately

aws rds reboot-db-instance --db-instance-identifier myapp-postgres-prod

After the reboot, enable the extension in the database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

pgvector for AI and vector search

pgvector adds a vector data type to PostgreSQL and enables similarity search. It is useful for storing embeddings from language models and running nearest-neighbour queries directly in the database.

-- Create a table for document embeddings
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  content TEXT,
  embedding vector(1536)
);

-- Create an approximate nearest-neighbour index
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);

-- Find the 5 most similar documents to a query embedding
SELECT id, content, 1 - (embedding <=> '[0.1, 0.2, ...]') AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'
LIMIT 5;

pgvector is one capability among many in RDS PostgreSQL. It does not require a specialised database service for most workloads.

RDS Proxy for connection pooling

PostgreSQL enforces a connection limit per instance class. A db.t3.medium supports roughly 340 simultaneous connections. For traditional applications on EC2 or ECS with a small number of long-running processes, this is rarely a problem. Application-level pooling (PgBouncer, HikariCP) handles it.

The problem arises with Lambda and other serverless compute. Every Lambda invocation can open a new database connection. At high concurrency, hundreds of concurrent invocations can exhaust the connection limit in seconds, causing new connections to be rejected.

RDS Proxy solves this. Your Lambda functions connect to the proxy endpoint; the proxy maintains a smaller pool of long-lived connections to the actual database.

# Create an RDS Proxy backed by a Secrets Manager secret for credentials
aws rds create-db-proxy \
  --db-proxy-name myapp-postgres-proxy \
  --engine-family POSTGRESQL \
  --auth '[{
    "AuthScheme": "SECRETS",
    "SecretArn": "arn:aws:secretsmanager:eu-west-2:123456789012:secret:myapp-db-creds"
  }]' \
  --role-arn arn:aws:iam::123456789012:role/rds-proxy-role \
  --vpc-subnet-ids subnet-0abc12345 subnet-0def67890 \
  --vpc-security-group-ids sg-proxy-sg

RDS Proxy reads credentials from AWS Secrets Manager and handles rotation transparently. Your Lambda function connects to the proxy endpoint without needing to manage credential updates. For all the secure connection patterns (bastion host, SSM tunnel, IAM database auth), see Connecting to Amazon RDS Securely.

Backups, high availability, and monitoring

Automated backups: RDS takes a daily snapshot and retains transaction logs for the duration of your retention period (up to 35 days). This enables point-in-time recovery to any second within that window. Set a retention period of at least 7 days for production. Enable deletion protection to prevent accidental instance deletion.

Multi-AZ: when Multi-AZ is enabled, RDS maintains a synchronous standby in a second Availability Zone. Writes are replicated to the standby before being acknowledged. If the primary fails, RDS promotes the standby and updates the endpoint DNS automatically. Applications see a brief connection interruption (typically 60-120 seconds) and then reconnect to the new primary.

Maintenance windows: choose a window during off-peak hours. AWS applies minor version patches and parameter group changes during this window. Some changes require a reboot, so plan for this in your operations schedule.

Monitoring: RDS publishes metrics to Amazon CloudWatch, including CPU utilisation, free storage space, database connections, and read/write latency. Set alarms on free storage and CPU to catch problems before they affect users.

Performance Insights: a deeper view into query-level performance, showing which queries are consuming the most database time. AWS Performance Insights is available for most instance classes and is a practical tool for diagnosing slow queries without external profiling.

For a full walkthrough of backup configuration, snapshot strategies, Multi-AZ failover testing, and read replicas, see RDS Backups and High Availability.