aa8y/postgres-dataset repository overview

⁠PostgreSQL images — aa8y/postgres-dataset

The original images: each aa8y/postgres-dataset⁠ image carries exactly one dataset, loaded into a database named after the dataset, and is built through an Extract -> Transform -> Load Dockerfile⁠ driven by manifest.yml⁠.

The available tags are the PostgreSQL column of the dataset support matrix⁠, which also lists each dataset's upstream source.

⁠Base image

PostgreSQL⁠ as aa8y/postgres-dataset⁠. We use the alpine version of the official image as the base image to keep our image slim.

⁠Usage

Start the world image, wait for it to initialize, and query it with the in-container psql client:

docker run -d --name pg-ds-world aa8y/postgres-dataset:world
# The first start loads the dataset; give it a moment to initialize (watch
# `docker logs pg-ds-world` for a *second* "ready to accept connections").
docker exec -it pg-ds-world psql -d world -c 'SELECT count(*) FROM city'

To run a different dataset, swap world for any tag in the PostgreSQL column of the matrix⁠; the database inside is the tag name minus any stackexchange- prefix (e.g. stackexchange-beer → beer).

⁠Connecting from the host or another container

The image keeps the base image's default superuser postgres with password postgres on the standard port 5432. Publish the port and connect with any client:

docker run -d --name pg-ds-world -p 5432:5432 aa8y/postgres-dataset:world
PGPASSWORD=postgres psql -h localhost -U postgres -d world -c 'SELECT count(*) FROM city'

These are throwaway practice and test images with trivial credentials — don't put them anywhere that matters. They also compose: see this example⁠.

⁠PostgreSQL datasets

Sources are in the matrix⁠; the notes below are PostgreSQL-specific.

• chinook: 11 tables in the public schema, quoted CamelCase identifiers (e.g. "Track", "InvoiceLine").
• pgexercises: 3 tables in a dedicated cd schema (not public).
• sportsdb: all 107 tables are created, but only the generic infrastructure tables plus American football, baseball, basketball, and ice hockey carry data — motor racing, soccer, tennis, wagering, and weather are schema-only.
• pagila: the payment table is range-partitioned by month (payment_p2022_NN), so row counts split across the parent and its partitions; upstream periodically shifts the sample dates to the current year, so absolute dates change between rebuilds.
• omdb: CSVs are fetched at build time and shipped in the image so \copy resolves at start; the init script creates the tsm_system_rows extension the upstream views rely on. Heavy (~150 MB of CSV + indexes).
• adventureworks: the upstream port pulls Microsoft's CSV bundle and runs a Python reformat before loading (68 tables across 5 schemas). Heavy (~90 MB of CSV).
• airlines: 9 tables in a bookings schema (search_path defaults to it). Upstream ships a single gzipped pg_dump of its own demo database, so the build decompresses it and strips the DROP/CREATE DATABASE / \connect directives so it loads into the airlines database. The heaviest dataset (several million inlined rows); the snapshot URL is date-stamped and may need bumping if postgrespro retires the pinned file.
• employees: datacharmer's canonical large sample (6 tables + 2 views; 300,024 employees and 2,844,047 salary rows — the one to reach for when you want a dataset big enough to make query plans matter). Built from the repo's own PostgreSQL port, which upstream splits into a schema file plus eight data dumps that a shell script feeds to psql one at a time; the postgres/scripts/employees transform hook does that assembly at build time (backticks stripped, upstream's DROP/CREATE DATABASE + \connect header removed so it loads into the employees database). All PK/FK/CHECK constraints and both views (dept_emp_latest_date, current_dept_emp) are upstream's. Row counts are exact (a fixed dataset) and match the MySQL tag; first boot takes about 30 s.
• moma: MoMA ships only CSV/JSON, so the schema is authored in-repo (postgres/scripts/moma/schema.sql, every column text) and the CSVs ship alongside the init script; counts drift as MoMA refreshes its exports (recorded as floors).
• geonames: GeoNames' cities15000 export (one cities table, ~34k places with a population above 15,000). It ships only as a zipped tab-separated file with no header and no quoting, so the schema is authored in-repo (postgres/scripts/geonames/schema.sql) and the export ships alongside the init script for \copy to read at start. GeoNames rebuilds the dump daily, so counts drift and are recorded as floors.
• openflights: the OpenFlights airport/airline/route tables (3 tables, ~82k rows). Published only as three comma-separated data files with no header row and \N for a missing value, so the schema is authored in-repo (postgres/scripts/openflights/schema.sql) and the files ship alongside the init script for \copy to read at start. There are no foreign keys: routes is compiled from published timetables and deliberately keeps rows whose airport/airline references dangle. Upstream refreshes the files in place, so counts drift and are recorded as floors.
• stackexchange-<site> (db = bare site name): the dump ships only per-table XML, so a shared build hook (postgres/scripts/stackexchange/transform) converts it to CREATE TABLE + inline COPY + indexes at build time (8 tables in public). Every site shares one schema and builds through that one hook, so adding a site is just another tag; counts are recorded as floors. cooking is the largest (~500k votes, ~230k post-history rows).

⁠Tag naming

The database inside each image is the bare dataset name — the tag minus any stackexchange- prefix.

⁠Custom images

Each image carries one dataset, selected with the DATASET build arg along with that dataset's sources (declared per tag in manifest.yml⁠). The simplest way to build a tag is through dave:

dave build -c postgres -t dellstore

To add or change a PostgreSQL dataset, declare its extractUrl, sqlFiles and any extras (extraPrereqs, dbExtension, cdDir) under a new tag in manifest.yml — the ETL Dockerfile⁠ reads them as build args. See docs/building.md⁠ for the full build instructions, the equivalent raw docker build invocation, and how the build cache works.

⁠History

There is no multi-dataset all image anymore — each image is one dataset; for several at once, run one container per dataset (e.g. via docker-compose). pagila was removed in 2019⁠ over an upstream breakage and is back as a regular tag, since the fork⁠ loads cleanly on modern Postgres and one dataset can no longer break the others.