What you’ll build
You will create a small Amazon RDS database, keep it private to your VPC, connect to it from a client, and create a table. The guide also covers backups, what keeps billing, and how to remove the database when you are done.
Before you start
- An AWS account and an IAM user with permission to create RDS instances, security groups and Secrets Manager secrets.
- A PostgreSQL or MySQL client on your computer, such as
psqlormysql. If you plan to connect from an EC2 instance instead, use that instance’s SSH session. - Your default VPC in the Region you choose. It exists in every Region unless it was deleted.
Step 1: Choose an engine and a template
In the RDS console, choose Create database. RDS supports MySQL, PostgreSQL, MariaDB, Oracle, SQL Server, Db2, and the Aurora engines. For a first project, PostgreSQL or MySQL is the most common choice.
Choose Standard create so you can set each option. For the template, choose Free tier if your account is eligible, or Dev/Test otherwise. Choose a single-AZ deployment for a learning database. Free tier eligibility depends on your account type, so check the Billing console for the free-tier usage shown there.
Step 2: Set the master credentials
- Master username: the console defaults to
postgresfor PostgreSQL andadminfor MySQL and MariaDB. Use the default unless you have a reason to change it. - Master password: choose Manage master user password in AWS Secrets Manager. RDS then creates a secret and can rotate the password. You can read the password from the secret when you need it, so you do not have to store it in code. Secrets Manager charges about $0.40 per secret per month.
If you choose a self-managed password instead, store it in a password manager, and never place it in code or in Git.
Step 3: Choose the instance size and storage
Pick the smallest instance class that works for your tests, such as a db.t4g.micro or db.t3.micro. Check the free tier notes for your account first.
For storage, choose General Purpose SSD (gp3). The minimum allocation is 20 GB. You can turn on storage autoscaling and set a maximum. Storage can grow, but it can never shrink, so start small.
Step 4: Keep the database private
Under Connectivity:
- Choose your default VPC.
- Set Public access to No. The database then has only a private address inside your VPC.
- Under VPC security group, create a new security group, such as
rds-sg. Do not allow traffic from0.0.0.0/0.
Add an inbound rule to rds-sg for the database port, and set its source to the security group of the app that connects to it. Use port 5432 for PostgreSQL, 3306 for MySQL and MariaDB, 1433 for SQL Server, or 1521 for Oracle. If you want to connect from your laptop, add a rule for your own IP address on the same port. Do not open the port to the internet.
Step 5: Set backups, encryption and protection
Under Additional configuration:
- Initial database name: enter a name if you want RDS to create a database for you. Otherwise you create one after you connect.
- Backup retention: the console default is 7 days, and you can set 0 to 35 days.
- Encryption: turn on encryption at rest now. You cannot turn it on for an existing instance without making an encrypted copy from a snapshot.
- Deletion protection: turn it on for any database you want to keep. You need to turn it off before you can delete the database.
Choose Create database. Provisioning can take several minutes. When the status shows Available, open the instance and copy its Endpoint from the Connectivity & security section.
Step 6: Connect to the database
Use the endpoint’s DNS name, not an IP address. The DNS name stays the same after a failover, and the IP address can change.
For PostgreSQL, from a machine inside the VPC:
psql -h <endpoint> -U postgres -p 5432 -d postgres
For MySQL or MariaDB:
mysql -h <endpoint> -P 3306 -u admin -p
Enter the password from the Secrets Manager secret when prompted. The connection must come from a machine that the security group allows. If you connect from an EC2 instance, launch the instance in the same VPC, and allow its security group in the rds-sg rule.
Use TLS for connections. Both engines support it, and your client can require it. The RDS documentation describes the certificate bundle to download.
Step 7: Create a table and test it
Run these PostgreSQL commands after you connect:
CREATE TABLE notes (
id SERIAL PRIMARY KEY,
body TEXT NOT NULL
);
INSERT INTO notes (body) VALUES ('Hello from RDS');
SELECT * FROM notes;
The last query should return one row. In a real app, store the endpoint, database name, user and password in environment variables or in the Secrets Manager secret, not in the code.
Backups, snapshots and high availability
- Automated backups let you restore the database to any point within the retention period, down to about five minutes before the latest change.
- Manual snapshots stay until you delete them, even after you delete the database.
- A Multi-AZ instance keeps a standby in another Availability Zone for failover. The standby does not serve reads. It roughly doubles the instance cost.
- Read replicas serve reads, and you can create up to 15 for MySQL, MariaDB and PostgreSQL.
Costs and clean up
RDS bills for the instance hours, the storage, and backup storage beyond the free allowance. Stopping the instance does not stop storage or backups from billing. RDS starts a stopped instance again automatically after seven days.
To remove the database:
- Turn off deletion protection in the instance’s Modify settings.
- Choose Actions, then Delete.
- Decide whether to keep a final snapshot. Keep one if you might need the data later.
- Delete the
rds-sgsecurity group and the Secrets Manager secret if you no longer need them.
Troubleshooting
- The connection times out. The security group does not allow your client or app, the database is not in the subnet you expect, or public access is set in a way you did not intend. Check the inbound rule and the client’s location.
password authentication failed. The username or password is wrong. Read the current password from the Secrets Manager secret.could not translate host name. The endpoint is mistyped, or the client cannot resolve the DNS name. Copy the endpoint from the console again.- Storage is full. Storage can grow but cannot shrink. Turn on storage autoscaling or increase the allocation.
Security checklist
- Keep public access set to No.
- Allow traffic only from the security groups of the apps that need it.
- Store the master password in Secrets Manager.
- Encrypt the database at creation.
- Turn on deletion protection for databases you want to keep.
- Use TLS for connections.
- Remember that RDS does not give you operating system access.
Next steps
- Connect an app: deploy a Node.js app on EC2 or deploy a Spring Boot app on EC2 both use a database set up this way.
- Read the guide to avoiding unexpected AWS charges for the cost traps that involve databases.
- Compare a serverless option in the DynamoDB tutorial.