MercadoFresco's photos are already in S3, with their lifecycle and their versioning. But the heart of the business is still on the office server: the orders database. That is where the customers, the products, the baskets, the delivery routes and the invoicing live. It is the data that cannot be lost, the data that cannot be down on a Friday at 19:00, and the data that today is backed up with a pg_dump to an external disk nobody has ever tried to restore.

This lesson migrates that database to Amazon RDS (Relational Database Service), AWS's managed relational database service. It is not just "PostgreSQL in the cloud": it is delegating patching, backups, failover and replication to AWS, tasks that at MercadoFresco nobody did because nobody had the time. And with automated backups and point-in-time recovery we finally close problem 2, the part that was left open in 02-02 when we solved the backups of the disks but not those of the transactional data.

1. What a managed service is and what stops being your problem
2. RDS versus PostgreSQL installed on an EC2
3. Available engines and which one MercadoFresco chooses
4. Creating the RDS instance from the console
5. Creating the RDS instance from the CLI
6. Multi-AZ: what a synchronous standby replica really is
7. Read replicas: Sara's reports without punishing production
8. Automated backups, retention and point-in-time recovery
9. Manual snapshots and closing problem 2
10. Parameter groups and maintenance windows
11. Monitoring: basic metrics and Performance Insights
12. Connecting from the application with no credentials in the code
13. Vertical scaling and auto-scaling storage
14. Migrating from the office server
15. Costs and how to stop a test instance

On the office server, PostgreSQL was entirely Marta's responsibility: installing it, configuring it, upgrading it, watching it, backing it up and bringing it back when it fell over. In RDS, AWS takes on a specific and well-delimited part of that work.

It is the shared responsibility model from lesson 01-01 applied to one specific service: the line moves upwards, but it never disappears. RDS is not going to fix a query with no index, nor is it going to design your tables.

What RDS does not let you do, worth knowing before you choose it:

• There is no access to the operating system. No SSH to the database machine. If your operating procedures depend on scripts on the server, they have to be rethought.
• There is no real superuser. The master user has the rds_superuser role, powerful but limited: it cannot do everything a PostgreSQL superuser does.
• Extensions are on an allowlist. You can only install the ones AWS supports (which are many: PostGIS, pg_stat_statements, pgvector…).
• Versions have an end-of-support calendar and AWS will eventually upgrade you.

Luis's legitimate question is: "we could install PostgreSQL on an EC2 and it comes out cheaper, couldn't we?". The honest answer is that the hourly price is lower, and the total cost almost never.

Choose EC2 only if you need control of the operating system, an unsupported extension, a specific version outside the catalogue, or if you have a team of database administrators already doing that work. For MercadoFresco, which has Marta part-time for everything, RDS is not an option: it is the only sensible option.

The calculation that settles the argument: a db.t3.small in eu-west-1 costs around 30 USD a month against some 20 USD for the equivalent EC2. Ten dollars of difference. A single night-time PostgreSQL incident a year, with Marta awake at 03:00, costs more than that.

MercadoFresco was already using PostgreSQL on the office server, so RDS PostgreSQL allows the migration without rewriting queries. It is the lowest-risk decision.

One clarification that saves confusion: Aurora is also RDS, it is managed from the same console and shares almost all the operational work we will see here, but its storage architecture is different. In 06-03 we will weigh up whether it pays for MercadoFresco to migrate to Aurora when it opens in three more cities. And the general choice between relational, key-value or column store is lesson 06-01.

1. Console → RDS → check the region is Ireland (eu-west-1) → Create database.
2. Method: Standard create (the easy one hides options you need to see).
3. Engine: PostgreSQL, version 16.x.
4. Template: Production turns on Multi-AZ and provisioned storage by default. To learn, choose Free tier so you spend nothing; we describe the production one here.
5. Settings:
   • Identifier: mercadofresco-pedidos
   • Master user: mfadmin (do not use postgres or admin)
   • Password: tick Manage master credentials in AWS Secrets Manager. With this the password is never seen by you, never typed by you, and AWS rotates it on its own. Secrets Manager is lesson 04-03.
6. Instance configuration: db.t3.small (2 vCPU, 2 GiB). For MercadoFresco's real production, db.m6g.large.
7. Storage: gp3, 20 GiB, with storage autoscaling enabled and a maximum of 100 GiB.
8. Availability: Multi-AZ standby instance. It is the checkbox that solves half of problem 1 for the database.
9. Connectivity: default VPC, public access = No, security group sg-mercadofresco-basedatos. Networking is module 3 (03-01 VPC, 03-02 security groups).
10. Authentication: password; optionally IAM authentication, which does away with passwords.
11. Additional configuration:
    • Initial database name: pedidos
    • Automated backups: 7 days of retention
    • Backup window: 02:00-03:00 UTC (the small hours in Spain, minimum traffic)
    • Maintenance window: Sunday 04:00-05:00 UTC
    • Encryption enabled
    • Deletion protection: enabled
    • Performance Insights: enabled (7 days are free)
12. Review the monthly cost estimate the console shows and click Create database.

Creation takes between 5 and 15 minutes (longer with Multi-AZ, because it builds two instances).

Cost warning. A single-AZ db.t3.micro falls within the Free Tier for 12 months (750 h/month, 20 GB of storage and 20 GB of backups). Multi-AZ doubles the compute cost and is not in the Free Tier. If you are practising, create the instance without Multi-AZ and tick the box just for a while to see how it works. The final section explains how to stop it and delete it.

The parameters that matter most:

• --manage-master-user-password: AWS generates the password, stores it in Secrets Manager and rotates it automatically. You never type it and never see it. It is the current best practice and it avoids the classic --master-user-password "Password123" in your shell history.
• --no-publicly-accessible: the database has no public IP. It is only reachable from inside the VPC. Non-negotiable for customer data.
• --max-allocated-storage 100: turns on storage autoscaling. If the disk reaches 90 % occupancy, RDS grows it on its own up to that ceiling.
• --deletion-protection: stops the instance being deleted by mistake. To really delete it you have to turn this off first, in a deliberate and separate step.

The endpoint will look like this:

It is a DNS name, not an IP, and that is fundamental. When a failover happens, AWS will change what that name points to and the application will reconnect to the new server without changing a single line of configuration. If anywhere in MercadoFresco's code there is a hand-written database IP address, it has to go now.

There is a very widespread misunderstanding here that is worth clearing up plainly: the Multi-AZ standby instance does not serve traffic. It is not a second server sharing the load. You cannot connect to it. It is a synchronised copy waiting for the primary to fail.

How synchronous replication works: when the application commits a transaction, PostgreSQL writes to the primary and waits for the write to have been confirmed on the standby as well before answering "done" to the client. The consequence cuts both ways:

• Advantage: zero data loss (RPO = 0). What is committed is in both AZs.
• Cost: every write takes a little longer, because it includes a network round trip between AZs (typically 1-2 ms). On write-heavy workloads you notice it.

1. AWS detects the failure (of the instance, of the storage or of the whole AZ).
2. It changes the endpoint's DNS record so that it points to the standby instance.
3. The old standby becomes the primary.
4. AWS creates a new standby in the other AZ, in the background.

Typical duration: 60-120 seconds. During that time the application gets connection errors. Multi-AZ is not invisible magic: it is fast recovery, not the absence of an interruption. The application has to be ready:

• Retries with exponential backoff on database operations.
• Low DNS TTL in the client's resolver. A connection pool that caches the IP forever will keep trying to connect to the dead server.
• A connection pool that knows how to discard dead connections (pool_pre_ping in SQLAlchemy).

You can trigger a failover on purpose to check the application copes. Do it in a test environment, and do it before it happens without warning:

They are different things and they are confused constantly:

MercadoFresco needs both, and for different reasons: Multi-AZ because the business cannot lose orders, and read replicas because Sara's reports are killing the database.

The real, measured problem: every Monday morning Sara runs a query that aggregates the month's orders by district and by time slot. It takes 4 minutes and during that time the shop runs slowly, because the same server is serving the customers.

A read replica is an asynchronous copy of the database that accepts read-only queries. Writes still go to the primary; heavy reads are directed at the replica.

Look at the tagging: Componente=analitica, Propietario=sara, CentroCoste=marketing. The replica exists for Sara, so its cost is charged to marketing and not to operations. This is exactly what will make the cost allocation report in lesson 11-02 possible.

Points to be clear about:

• Replication is asynchronous: the replica runs a few seconds behind. Watch the ReplicaLag metric. For aggregated sales reports it is perfectly acceptable; for "check the status of the order I have just placed", it is not.
• The application has to route the traffic. AWS gives you a different endpoint for the replica; the code decides which one it uses. RDS does not split it for you.
• A replica can be promoted to a standalone instance, which breaks replication irreversibly. It is a valid strategy for migrating between regions.
• They can be created in another region, which additionally gives geographic disaster recovery.

In MercadoFresco's code, the separation looks like this:

The query in plain SQL, so you can see it whole and understand why it is expensive: it walks every order in the month, joins them with customers and order lines, and aggregates by two dimensions. With hundreds of thousands of rows that is a long sequential scan that saturates the CPU and evicts hot data from PostgreSQL's cache. Exactly what you do not want while 900 customers are shopping.

This is where MercadoFresco's problem 2 is closed.

RDS does two things continuously and automatically:

1. One full daily backup during the window you have set (02:00-03:00 UTC in our case). With Multi-AZ, it is taken from the standby instance, so production does not feel it.
2. Continuous copying of the transaction logs (WAL) to S3, every 5 minutes.

The combination of the two is what makes point-in-time recovery (PITR) possible: you can restore the database to any second within the retention period, not just to the moment of the daily backup.

The scenario that justifies all of this: on Tuesday at 11:47, a deployment by Luis mistakenly runs an UPDATE with no WHERE that sets the price of every product to zero. At 11:52 somebody notices.

The fact that the restore creates a new instance is deliberate and very useful: you can verify the data before touching production, or even extract just the affected table and copy it, instead of rolling the whole database back and losing the legitimate orders from those five minutes.

On retention:

Automated backups are stored free up to the size of the database; beyond that they are charged at snapshot prices. And there is one critical detail: when you delete the instance, the automated backups are deleted with it. Only manual snapshots survive.

A manual snapshot is a backup you create and that lives until you delete it, even if you delete the instance. It is the backup for milestones: before a schema migration, before a major version upgrade, at the end of the financial year.

A comparison of the three forms of recovery in RDS:

And that closes MercadoFresco's problem 2, adding up what we did in 02-02 and in this lesson:

With one warning that bears repeating: the quarterly restore test is part of the plan, not an extra. Marta has it in her calendar.

As there is no access to the operating system, you cannot edit postgresql.conf. Instead, RDS uses parameter groups: a named set of settings associated with one or more instances.

The default group cannot be modified, so the first thing is to create one of your own:

What we have configured and why:

• log_min_duration_statement = 1000: logs every query taking more than 1 second. It is the most direct way to find the queries that sink the shop on Fridays.
• shared_preload_libraries = pg_stat_statements: turns on the extension that accumulates per-query statistics. The basis of any serious optimisation work.
• ApplyMethod: immediate is applied straight away; pending-reboot requires restarting the instance. A dynamic parameter marked as pending is not doing anything yet, and this catches a lot of people out.

On the maintenance window: it is the weekly interval in which AWS applies operating system and engine patches. Choose it in the traffic trough (early on a Sunday morning for MercadoFresco, never a Friday afternoon). With Multi-AZ, maintenance is applied to the standby instance first, then the system fails over and only then is the other one patched: the interruption comes down to the failover time.

Minor version upgrades can be automatic (--auto-minor-version-upgrade); major version ones (from PostgreSQL 16 to 17) never are: they require your explicit action and they have to be tested first on an instance restored from a snapshot.

RDS publishes metrics to CloudWatch without any configuration. The ones to watch:

Performance Insights goes one step further: it is a dashboard showing the database load broken down by query, by user, by host and by wait type. That last dimension is the one that resolves incidents: it does not just tell you "the CPU is at 90 %", it tells you "70 % of the load is waiting on locks on the pedidos table, and this is the query responsible".

The 7 days of retention are free and it is always worth turning on. Monitoring in depth, with alarms, dashboards and log aggregation, is lesson 05-01.

First, the manual check from one of the storefront EC2 instances (remember: the database is not public, so you have to connect from inside the VPC):

Once inside, some useful checks:

And this is how the application connects, reading the secret at start-up and without a single credential touching the code or the repository:

The three rules this code sums up:

1. No credential in the code or in the repository. Only the identifier of the secret.
2. Authorisation by IAM role, not by access keys. The EC2 instance has a role that lets it read that one specific secret and nothing else.
3. sslmode=require: the connection is encrypted. RDS provides the certificate.

Vertical scaling (changing the instance class). It is a disruptive operation, except with Multi-AZ, where AWS modifies the standby first, fails over and then does the other one: the outage comes down to the 60-120 seconds of the failover.

--apply-immediately applies the change right now, with the corresponding interruption. Without that parameter, the change waits for the maintenance window, which is what you want in production unless it is urgent.

Storage scaling. It can be grown at any time and without stopping, but not shrunk. With --max-allocated-storage, RDS does it on its own:

When to scale what, in MercadoFresco's case:

The order matters: moving up in size is the last option, not the first. A missing index costs zero euros a month; a bigger instance class costs every month, for ever.

With MercadoFresco's database (a few GB and an acceptable overnight maintenance window), the classic dump-and-restore migration is enough.

Why --no-owner --no-privileges: the roles and permissions of the source server do not exist in RDS, and without those options the restore fills up with errors from trying to assign objects to users that are not there.

Mandatory verification before switching the traffic over:

MercadoFresco's cut-over plan, early on a Sunday morning:

1. 02:00 — Put the shop into maintenance mode (read only).
2. 02:05 — Final pg_dump from the office server.
3. 02:20 — pg_restore into RDS and ANALYZE.
4. 02:40 — Verify the counts and run the smoke test suite.
5. 02:50 — Point the application's environment variables at the new endpoint.
6. 03:00 — Take off maintenance mode and watch the metrics for an hour.
7. The office server is left switched on and in sync for a week, in case there is any need to go back.

When the shop cannot be stopped, the tool is AWS DMS (Database Migration Service): it copies the initial state and then replicates the changes continuously (change data capture), so that the cut-over window comes down to seconds. It can also migrate between different engines (from Oracle to PostgreSQL, for instance). For MercadoFresco it is overkill; for a migration with no downtime, it is the way.

Components of the RDS bill:

Monthly cost of MercadoFresco's production configuration:

Some €78 a month for a database with high availability, automated backups, 7-day PITR and a replica for analytics. Compared with the €6,000 of the original server — which had none of that — and with the time Marta spent maintaining it, the financial conversation becomes an easy one.

Stopping a test instance:

Two important limitations: RDS only lets you keep an instance stopped for 7 days; on the eighth it starts it on its own. And a Multi-AZ instance cannot be stopped. For development environments only used during office hours, the practical answer is to schedule the start and the stop, or simply delete and recreate from a snapshot.

Deleting it completely when you finish the exercise:

• Believing the Multi-AZ standby serves reads. It does not. That is what read replicas are for. It is the number one misunderstanding about RDS.
• Setting --publicly-accessible so you "can connect from home". It exposes the database to the internet. The correct way is a bastion, Session Manager or a VPN.
• Writing the password into the command or into the code. Use --manage-master-user-password and Secrets Manager (04-03).
• Backup retention set to 0 days. It disables PITR. It is the setting that turns a recoverable human error into a permanent loss.
• Forgetting that automated backups are deleted along with the instance. Before deleting, take a final snapshot.
• Changing a pending-reboot parameter and expecting it to act. It does nothing until a restart.
• Restoring and not running ANALYZE. With no statistics the planner makes dreadful decisions and it looks as if RDS "is slow".
• Putting the maintenance window at peak time. Early on a Sunday morning, never on a Friday afternoon.
• Moving up an instance class before looking at the queries. A missing index costs 0 USD a month; a bigger instance, every month.
• Reading from the replica what the user has just written. The asynchronous lag will sometimes mean it does not show up. Critical reads always go to the primary.
• Never testing a failover. Trigger one in a test environment and check the application reconnects. Finding out in production is expensive.
• Tip: turn on Performance Insights from day one. It is free for 7 days and the day there is an incident you will want the history.

MercadoFresco opens in three more cities (problem 3). The forecast is:

• The Friday peak goes up to 2,100 orders/hour (write intensive).
• Sara goes from a weekly report to a dashboard that refreshes every 15 minutes.
• A new requirement appears: if the whole eu-west-1 region is lost, the business must be able to operate again in under 4 hours.
• The requirement to lose no confirmed order still stands.

Design the RDS topology: which instances, of what type, in which AZs and regions, and what mechanism covers each requirement. State which requirement RDS cannot cover on its own.

On Thursday at 16:12, a catalogue synchronisation script mistakenly runs:

It has deleted 47,000 lines of historical orders that were in fact valid. Nobody notices until Friday at 10:30, when Sara sees that July's figures do not add up. In that time some 1,400 new orders have been recorded that cannot be lost.

Describe the complete recovery procedure, with the commands, and explain why you cannot simply restore the database to Thursday at 16:11.

For each MercadoFresco query, state whether it should go to the primary instance or to the read replica, and justify it in one sentence:

• A) INSERT of a new order from the basket.
• B) "My orders" for a customer who bought 3 seconds ago.
• C) Monthly report of sales aggregated by district.
• D) Listing the product catalogue on the home page.
• E) Stock check right before confirming an order.
• F) Overnight export of every order to S3 for analytics.

Solution 1.

The resulting topology:

What RDS does not cover on its own: the Friday write peak still depends on a single writer instance. Scaling vertically has a ceiling. The real solutions lie outside this lesson: queueing the orders with SQS to absorb the peak and write them at a sustained rate (module 7), caching the hot reads with ElastiCache (06-05), or spreading the writes with a different data model (DynamoDB, 06-02). Recognising that ceiling is part of knowing how to use RDS.

Solution 2.

Why you cannot simply restore to Thursday at 16:11: that would return the database to the state of that instant, and with it the 1,400 orders recorded between Thursday afternoon and Friday morning would disappear. You would fix one problem by creating a worse one. A full restore is only worth it when the damage is spotted within minutes.

The correct procedure is to restore in parallel and recover only what was deleted:

The lessons the incident leaves behind: the DELETE should have been run inside a transaction with the count verified beforehand; maintenance scripts should not use the master user but a role with narrow permissions (04-01); and an alarm on sudden variations in row counts would have warned on Thursday and not on Friday.

Solution 3.

The general pattern: writes and critical or immediate reads → primary; analytical, historical or lag-tolerant reads → replica.

MercadoFresco's orders database is now in AWS, and with it the heart of the business. You understand what a managed service means: AWS takes on the hardware, the operating system, the engine patches, the backups and the failover; you remain responsible for the schema, the indexes, the queries and the security of the application. You also know what you give up in exchange — operating system access, a real superuser, extensions outside the list — and in which specific cases that forces you to choose PostgreSQL on EC2 instead of RDS.

You have created the mercadofresco-pedidos instance from the console and from the CLI, with the decisions that matter taken deliberately: password managed in Secrets Manager so it is never typed, no public access, encryption enabled, deletion protection, storage autoscaling, 7 days of backup retention, and backup and maintenance windows placed in the traffic trough and never on a Friday afternoon.

You have undone the most common misunderstanding about RDS: the Multi-AZ standby does not serve traffic. It is a synchronous replica that guarantees zero data loss and fails over in 60-120 seconds by changing the endpoint's DNS, with the trade-off of adding latency to every write and of demanding that the application retries. Against it, read replicas are asynchronous, can indeed be queried and are the right tool for keeping Sara's dashboard from punishing production; you have seen in code and in a table which query goes where.

And you have closed MercadoFresco's problem 2. Between the EBS snapshots automated with DLM in lesson 02-02, the S3 versioning of 02-03 and now RDS's automated backups with transaction logs every five minutes, the company has gone from "a pg_dump to an external disk when somebody remembers" to being able to restore to any second in the last seven days. You have practised the real recovery of a mass deletion by restoring in parallel and reinserting only what was lost, without sacrificing the orders that came afterwards. To that you add the manual snapshots that survive deleting the instance, the parameter groups with log_min_duration_statement and pg_stat_statements for hunting slow queries, Performance Insights for knowing what the database is really waiting on, connecting from the application without a single credential in the code, vertical and storage scaling with its golden rule — look at the indexes before moving up a class — and the complete migration plan with pg_dump/pg_restore and its no-downtime alternative, AWS DMS.

One last piece of the module remains. We have servers that have to be sized, started and paid for even when they are idle. But there are MercadoFresco tasks that do not need a server sitting there waiting: generating the thumbnail of a photo when it is uploaded, answering a one-off query about the status of an order, processing a file. In lesson 02-05, "AWS Lambda", we will write code that runs only when something happens and is only paid for while it runs, we will finally connect the S3 event we left configured in 02-03 to generate the catalogue thumbnails, and we will recap which pieces of MercadoFresco are already in the cloud and what is left to build.