Amazon Linux 2023 | Production-Ready | Auto-Tuning
Complete AMI guide for your PostgreSQL 17 + pgAdmin4. Learn how to access your database, find credentials, and configure your instance for production use.
Follow these steps to access your PostgreSQL database and pgAdmin4 web interface.
Connect to your EC2 instance using SSH with your key pair.
ssh -i your-key.pem ec2-user@<your-instance-ip>
View the auto-generated PostgreSQL and pgAdmin4 passwords.
sudo cat /var/lib/pgsql/credentials.txt
Open pgAdmin4 in your browser using your instance's public IP.
http://<your-instance-ip>/pgadmin4
Login with:
Email: admin@localhost
Password: (from credentials file)
Connect via command line or add a server in pgAdmin4.
# On the instance:
sudo -u postgres psql
# From remote:
psql -h <your-instance-ip> -U postgres -d postgres
All credentials are automatically generated and stored securely on your instance.
sudo cat /var/lib/pgsql/credentials.txt
postgresql://postgres:<password>@<your-instance-ip>:5432/postgres?sslmode=require
pgAdmin4 is a powerful web-based administration tool for PostgreSQL. Here's how to get started.
Open your browser and navigate to the pgAdmin4 URL.
http://<your-instance-ip>/pgadmin4
Email: admin@localhost
Password: (from credentials file)
Register your PostgreSQL server in pgAdmin4.
localhost5432postgresCreate a new database for your application.
Use the Query Tool to execute SQL commands.
Right-click database → Backup...
Choose format (custom, tar, plain) and destination.
Right-click database → Restore...
Select your backup file and restore options.
Right-click table → View/Edit Data → All Rows
Browse, filter, and edit data directly.
Click Dashboard tab on server.
View active sessions, locks, and live statistics.
Right-click Tables → Create → Table...
Define columns, constraints, and indexes.
Right-click table → Import/Export Data...
Supports CSV, binary, and text formats.
Follow these steps to secure your PostgreSQL deployment.
Important: Change the default passwords immediately after your first login. The pre-generated passwords should only be used for initial access.
Update PostgreSQL password via psql and pgAdmin4 password via the web interface.
Restrict port 5432 access to only your IP addresses or VPC CIDR range.
SSL is enabled by default. All connections use TLSv1.3 encryption.
sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'your-new-secure-password';"
Common issues and how to resolve them.
Symptom: Connection refused or timeout when connecting from your machine.
Solution:
5432sudo systemctl status postgresqlSymptom: Can't log in to pgAdmin4 web interface.
Solution:
sudo cat /var/lib/pgsql/credentials.txtsudo sqlite3 /var/lib/pgadmin4/pgadmin4.db "UPDATE user SET locked=0, login_attempts=0 WHERE id=1;"sudo systemctl restart pgadmin4Symptom: Connections hang and eventually time out.
Solution:
Symptom: "password authentication failed for user postgres"
Solution:
sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'newpassword';"Common questions and quick solutions.
sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'newpassword';"
sudo tail -100 /pgdata/postgresql/logs/postgresql-$(date +%Y-%m-%d).log
sudo systemctl restart postgresql
sudo systemctl restart pgadmin4 httpd
Data is stored on a separate EBS volume:
/pgdata/postgresql/data
sudo systemctl status postgresql pgadmin4 httpd
Choose the right EC2 instance type for your workload.
| Use Case | Instance Type | vCPUs | RAM | Recommended For |
|---|---|---|---|---|
| Development | t3.small |
2 | 2 GB | Testing, learning, small dev projects |
| Small Production | t3.medium |
2 | 4 GB | Small apps, low traffic websites |
| Medium Production | m6i.large |
2 | 8 GB | Medium traffic, business applications |
| Large Production | m6i.xlarge |
4 | 16 GB | High traffic, enterprise workloads |
| High Performance | r6i.xlarge |
4 | 32 GB | Memory-intensive, analytics, large datasets |
Protect your data with regular backups.
The easiest way to back up your entire database.
Export databases to SQL files.
# Backup single database
sudo -u postgres pg_dump mydb > mydb_backup.sql
# Backup all databases
sudo -u postgres pg_dumpall > all_databases.sql
# Custom format (compressed)
sudo -u postgres pg_dump -Fc mydb > mydb.dump
Restore databases from SQL or dump files.
# Restore from SQL
sudo -u postgres psql mydb < mydb_backup.sql
# Restore from custom format
sudo -u postgres pg_restore -d mydb mydb.dump
# Create db and restore
sudo -u postgres createdb newdb
sudo -u postgres pg_restore -d newdb mydb.dump
Recommended backup frequency:
Your AMI comes with these production-ready extensions pre-installed and enabled.
| Extension | Description |
|---|---|
pg_stat_statements |
Query performance monitoring and statistics |
pgcrypto |
Cryptographic functions for encryption/hashing |
uuid-ossp |
UUID generation functions |
hstore |
Key-value pair storage within a single value |
pg_trgm |
Trigram text similarity and search |
btree_gist |
GiST index operator classes for common types |
btree_gin |
GIN index operator classes for common types |
citext |
Case-insensitive text data type |
tablefunc |
Crosstab and pivot table functions |
unaccent |
Text search dictionary for accent removal |
PostgreSQL is automatically configured based on your instance size.
On first boot, the auto-tuning service detects your instance's RAM and CPU count, then calculates optimal PostgreSQL settings.
Key configuration and log file locations:
Our team is available 24/7 to assist you with any questions or issues.
support@flowopsconsulting.co.uk