Amazon EC2

Installing PostgreSQL using Amazon EC2

Time requirement: 30 mins

Review content in AWS RDS walkthru for comparison and cost information

Prerequisite: VPC with public subnet.
Knowing your IP Address will be helpful for security group later

** Create EC2 Instance** using latest AMI and preferred hardware type (t2.micro suitable for demo)

1. in AWS EC2 Console, click “Launch Instance” button
2. Add Name, select Amazon Linux
3. provide key pair name - or creae new pair if-needed (chmod 400 if newly created)
4. Select Network Settings to pre-created VPC and Public Subnet
   

• verify “Auto-assign public IP” is “Enabled”

1. Allow SSH traffic from “My IP” in the drop-down. (optional, but recommended)
2. Launch Instance in lower right button

Deploy Postgres-17 install and expose ports

1. Connect to your EC2 Instance
   • ssh -i {pem file} ec2-user@{ec2 DNS name}
2. Identify latest supported version of PostgreSQL:
   • sudo yum search “postgres”
3. sudo yum install postgresql17-server.x86_64
4. Initialize database:
   • sudo /usr/bin/postgresql-setup –initdb
5. if above command fails, this may be related to invalid directory permissions:
   • sudo mkdir /var/lib/pgsql/
     
   • sudo chown postgres:postgres /var/lib/pgsql/
6. Recreate database if initial command fails:
   • sudo postgresql-setup –initdb
7. Use Amazon Linux commands to start/enable datbaase:
   • sudo systemctl start postgresql
   • sudo systemctl enable postgresql
   • sudo systemctl status postgresql
8. Change credentials for initialized database:
   • sudo su - postgres
   • psql -c “ALTER USER postgres WITH PASSWORD ‘{new_password}’;”
9. Update default listen_address from localhost to accept IPv4 connections:
   • vi /var/lib/pgsql/data/postgresql.conf
   • update listen_addresses = ’localhost’ to *
   • vi /var/lib/pgsql/data/pg_hba.conf
   • update ipV4 to 0.0.0.0/0 md5
10. Restart postgres to process updated files:
    • sudo systemctl restart postgresql
11. You can now connect to the database
    • verify security group allows access to port 5432
    • verify route allows inbound access to port 5432

If connections fail, verify VPC security groups

• Select the security group associated with your newly created VPC most likely the last, or longest-named group ID
• Edit inbound rules to add Postgres port 5432
• Click “Add Rule” > Type > PostgreSQL > Source: My IP Optionally add description for todays date, as your IP may change or add a CIDR range, allowing your (/20) network access

Route table for the VPC may also require whitelisting port 5432

Connection and further maintenance

In my example, I was provided the endpoint used to connect as:

psql -h database-1.cvf3het7fmyg.us-west-1.rds.amazonaws.com -U postgres

Cleanup EC2 resources

Using EC2 console, check running instance name and use “Instance state” > Terminate

Termination may take several minutes.