Postgres in depth · Chapter 63 of 65
The old LMS
By the end of this chapter you can query another Postgres database as if its tables were your own, see which part of a query runs over there, and run a one-off query on another server with dblink. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Extensions and graduation
- 4 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
Before LCO, Kabir ran a pilot batch on an older learning platform, the old LMS. It is a separate Postgres database, lco_old_lms, with its own students, courses and enrollments, numbered its own way. Sana keeps getting emails from pilot students who cannot find their courses on LCO. Priya wants their enrollments brought over, without exporting files or writing a script.
Until now every query ran inside one database. A query in lco cannot name a table in lco_old_lms: databases are separate worlds, even on the same server. Two extensions cross the gap. postgres_fdw makes a remote table look local; dblink sends one query to another server and gives you the rows.
Make the old LMS in your own lab first (it is a copy of the edge dataset, which really is a small older LCO):
psql -d postgres -c "CREATE DATABASE lco_old_lms TEMPLATE lco_edge"What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
connect.sql: reach the old LMS
Connect this database to the old LMS with postgres_fdw (already installed): a foreign server called old_lms for the database lco_old_lms on host localhost, a user mapping for the current user that logs in as app with the password app, a schema called old_lms, and foreign tables in it for the old LMS's students, courses and enrollments (its schema public), imported rather than typed by hand.
Afterwards: old_lms.students, old_lms.courses and old_lms.enrollments are foreign tables of the server old_lms, and they return the old LMS's rows.
In your own terminal, make the old LMS first, as the lesson shows; the Check makes its own.
- Task 2
not-migrated.sql: pilot students who never joined LCO
List the old LMS's students (deleted or not) who have no LCO account: no student in LCO has the same email. The Check's copy is connected as in task 1.
Return two columns: name and email (both from the old LMS). Order by email.
Careful: the two databases numbered their students independently, so the same id is a different person in each.
- Task 3
bring-enrollments.sql: copy the pilot enrollments
Copy the old LMS's enrollments into LCO's enrollments, for every old enrollment whose student has an LCO account with the same email and whose course exists on LCO with the same slug. Use LCO's own student and course ids, and keep enrolled_at, price_paid and completed_at from the old LMS. Skip an enrollment LCO already has for that student and course, instead of failing. The Check's copy is connected as in task 1.
Afterwards: LCO's enrollments hold every matching old enrollment exactly once, and nothing else changed.
- Task 4 · interview question
Interview: dblink.sql, a one-off remote query
An interviewer asks you to count the old LMS's orders per status with dblink, without foreign tables: connect with host localhost, database lco_old_lms, user app and password app.
Return two columns: status (as text) and orders (the count). Order by status.
Install dblink in the file if it is missing.
What you will learn
- Foreign data wrappers: a server, a user mapping, foreign tables.
- IMPORT FOREIGN SCHEMA: bringing in many table definitions at once.
- Pushdown: what runs on the remote side, read from EXPLAIN VERBOSE.
- dblink: a single remote query, and why you must describe its columns.
More in Extensions and graduation
Build The old LMS yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.