>_ IzzyLab

Hands-on task

This task is step 10 of the course “Databases in an evening: SQLite, PostgreSQL, Redis”

Dump a PostgreSQL database

medium~15 min

PostgreSQL is already installed, but the server isn't running. Start it, create a database shopdb with any table (at least one row), and make a logical dump of it into the file /root/shopdb.sql — a plain SQL dump the database can be restored from.

What you'll practise:
  • postgresql
  • pg_dump
  • sudo -u postgres
  • logical dump
  • apt

Topic breakdown

PostgreSQL is a server database: it runs as a daemon, and administrative access goes through the system user postgres, which is created when the package is installed. A logical dump is an export of the database as plain SQL commands (CREATE TABLE, INSERT) from which it can be restored anywhere; pg_dump makes it. The flow is: bring up the server, create and populate the database (commands to the database are convenient to run via sudo -u postgres), and redirect the dump output into a file — the redirection runs as your own user, so the file lands in /root.

What's checked

  1. dump /root/shopdb.sql is non-empty
  2. dump contains table definitions
  3. the dump was made by pg_dump and contains data rows
  4. the shopdb database is created

What it looks like