Save the Orders at a Known Recovery Point

Introduction

PAPER & FORM sells paper prints and desk objects. A move to another service is approaching, and the shop owner needs to know exactly which orders a backup can bring back. An existing file is not enough: its contents must include each order and its item details at a clear recovery point.

You will place a small order, save a PostgreSQL custom-format backup, then add another order and see why it is outside that backup. Basic terminal familiarity is enough. The database, tools and website are prepared; you will not write SQL or install software. All customers, prices and orders are synthetic; there is no payment or email service.

Use Original Shop for the working store and Workstation for commands. Restored Copy is an independent empty destination used in the next lab; do not restore into it in this unit. Every unit starts with its own data. Keep the supplied program and uploads/ unchanged. This is a logical database export exercise, not complete disaster recovery or point-in-time recovery (PITR).

Record the Order That Belongs in the Backup

In this step, you will record a real order with its item details. Begin in Original Shop. The blue banner identifies the original database, shop_source. Open Explore the collection ↗. The collection offers Print pair at $18.00 per item and Desk tray at $26.00. Open Orders in the top navigation, select R-101, and read its item row: two Print pair items total $36.00. Select Download receipt ↓ and read the saved text. It contains the reference, customer, item, quantity and total. Return using PAPER & FORM.

A database stores structured records. Here orders stores each order's reference and customer; order_items stores its item rows. Each item points to its parent order. Keeping only customer names or only totals would lose the relationship the shop needs. The record page and receipt read these real linked rows.

Switch to Workstation. pwd prints the working directory; ls lists its files:

pwd

/home/labex/project/shop-backup

ls

You should see backups, shopctl, and uploads. Save your exports in backups/. uploads/packing-note.txt is an unrelated file and must stay unchanged. A logical database export does not include files outside the database.

./shopctl runs the supplied helper in the current directory. Its identity action displays actual database connections. A role is the database account that determines permissions. A host and port identify the database service. Run:

./shopctl identity

Find source shop_source with read-only client role d09_reader, and target shop_copy with writable role d09_copy, both at 127.0.0.1:5432. Sharing a server does not make them the same database. The copy role cannot connect to the source. The website uses a separate source writing role. These are training boundaries, not protection against someone with system administrator access.

Switch to Original Shop, open Collection, and scroll to Place an order. Before submitting, read Writing to: shop_source · ORIGINAL SHOP. Use these exact values:

Select Place order ↗. The record opens with R-103, Rae, two Print pair items, and $36.00 total. This is the cutoff order: it must be in the backup. Unknown references and copy-only references are refused in the original form without writing a record. R-104 submitted before a valid, protected cutoff-kept.dump also returns Keep the earlier point first and writes nothing. If you meant R-103, return to Collection and correct the reference. This protects this rehearsal’s required record sequence; a production shop does not generally wait for a backup before accepting orders. If you typed the wrong customer, item or quantity for an existing R-103, correct only that reserved practice record from Workstation:

./shopctl correct source R-103

The helper displays the real destination, then changes only R-103 to Rae / Print pair / 2. It refuses original baseline references and preserves other orders, later records, uploads and all archives. This is an explicit learner correction, never something the checkpoint does. Correct the record and reread it before exporting. If the reference already exists after a repeated click, the form returns Already recorded; open Orders and inspect that record instead of submitting duplicates.

Return to Workstation. records prints the real linked data; the final numbers are quantity and unit price in cents:

./shopctl records source

Find R-103 | Rae | Print pair | 2 | 1800 and total $98.00 across the three orders. You have added the intended order without changing the original R-101 and R-102 records.

Save a Consistent Custom-Format Archive

In this step, you will save the three committed orders. A recovery point is the database state captured by the export, not the file's name or modification time. PostgreSQL's pg_dump makes a consistent snapshot: it keeps related tables from one database view even when other users are active. For this exercise, finish R-103 before exporting and wait until the export completes before adding the next order. That order makes the cutoff unambiguous without comparing clocks.

Stay in Workstation. pg_dump exports a database; pg_restore reads its archive formats. Check the installed client versions:

pg_dump --version

pg_restore --version

Both should report PostgreSQL 14 in this prepared environment. The server is also version 14. Using a newer server with an older pg_dump can fail; do not work around a version error by saving an empty file. These matching tools were prepared for this lab.

Display the connections again before selecting the source:

./shopctl identity

PostgreSQL connection services are saved connection settings. The supplied service=shop-source chooses shop_source and its read-only role; service=shop-copy chooses the independent target. --dbname selects that connection. Always name the service explicitly instead of relying on the client's defaults.

--format=custom creates PostgreSQL's compressed archive format, which needs pg_restore, rather than a plain SQL script. --file names the output. --no-privileges omits source access grants. For a custom archive, pg_dump --no-owner is ignored; ownership is skipped later by passing --no-owner to pg_restore. That restore option lets the target role own restored objects. The following command reads the source and writes only the named local backup file:

pg_dump --dbname=service=shop-source --format=custom --no-privileges --file=backups/cutoff.dump

A successful dump normally prints nothing. Do not type the next business order until this command finishes. If a connection or file error appears, check pwd, ./shopctl identity and the output path, then retry. Rerunning replaces backups/cutoff.dump; do that only while the source still has the intended cutoff state. A later export captures later data.

pg_restore --list reads an archive's table of contents without importing it:

pg_restore --list backups/cutoff.dump

Near the top, find Format: CUSTOM, dbname: shop_source, and matching dumped database/client versions. Farther down, find entries for orders, order_items, their data, sequences and constraints. These entries describe the archive structure. They do not prove that the right business rows are present; restoring and reading those rows is stronger evidence.

The checkpoint tests your actual archive in a new disposable database, including the cutoff order and item relationship. It does not restore your Restored Copy tab. Keep the archive for the next step.

Before advancing to the next step, read the checkpoint result and preserve the verified archive. cp copies a file to a second name. chmod a-w removes write permission for all users, protecting both names from an accidental pg_dump --file overwrite:

cp backups/cutoff.dump backups/cutoff-kept.dump

chmod a-w backups/cutoff.dump backups/cutoff-kept.dump

Do this only after the backup checkpoint passes. A direct export to either protected file now fails with Permission denied rather than replacing your recovery point. If the original filename is accidentally removed, cp backups/cutoff-kept.dump backups/cutoff.dump restores its bytes. The kept file is a second local copy for ordinary mistakes, not off-site disaster protection.

Distinguish Later Orders from the Saved Point

In this step, you will see the source move forward while the saved archive stays at its earlier point. Switch to Original Shop, open Collection, and read Writing to: shop_source · ORIGINAL SHOP before writing. Submit:

The new record totals $26.00. Open Orders and confirm R-101, R-102, R-103 and R-104 are present. R-104 was committed after pg_dump finished, so it is outside backups/cutoff.dump. A backup is not a live connection that keeps receiving orders. Do not export over the cutoff archive now.

In Workstation, read the source again:

./shopctl records source

The output now includes R-104 | Sol | Desk tray | 1 | 2600 and total $124.00. The source remains usable; you have not destroyed business data to create a recovery exercise. Listing the archive again still reports its original structure:

pg_restore --list backups/cutoff.dump

The archive checkpoint restores it only into a disposable test database. It expects R-101 through R-103 and excludes R-104. That proves a recovery point rather than merely checking that a file exists. Leave Restored Copy empty in this unit.

If you mistyped R-104, ./shopctl correct source R-104 displays the source and corrects only that existing practice order to Sol / Desk tray / 1, preserving all earlier records. An unknown reference is refused by the form, so fix the reference and resubmit.

If you accidentally removed or replaced cutoff.dump, recover from the retained earlier bytes, not from a new future snapshot. In Workstation, chmod u+w gives the owner write permission for that one file; then cp restores it and chmod a-w protects it again:

chmod u+w backups/cutoff.dump

cp backups/cutoff-kept.dump backups/cutoff.dump

chmod a-w backups/cutoff.dump

If the file was removed, omit the first command and use the kept copy directly. Recheck the archive checkpoint afterward. Do not delete R-104 or rewind the source: later business records must remain. If every copy of an earlier point has actually been lost, a new logical export cannot recreate the past. That is why the previous step checks and keeps a protected copy before allowing the source to advance. In the next independent lab, a prepared archive gives you that same cutoff so you can concentrate on safe restoration.

Summary

You placed a real order, exported a consistent PostgreSQL custom archive, and distinguished that saved point from a later source order. The archive checkpoint tested actual restored relationships in a disposable database. The next independent lab opens the saved state as a usable shop copy.