>_ IzzyLab

Hands-on task

This task is step 18 of the course “PostgreSQL: from your first database to transactions”

PostgreSQL: dump and restore

medium~10 min

In the shop database there is a customers table with data. Dump only this table to /root/customers.sql, create a new database shop_copy, and restore the table into it so that all rows are in place.

What you'll practise:
  • pg_dump
  • createdb
  • psql
  • backup/restore
  • input redirection

Topic breakdown

A logical dump is an export of a database or its objects into a plain text SQL script (CREATE TABLE, COPY/INSERT) that you can read with your own eyes and run against any other database. The dump utility can limit the export to a single table via the corresponding flag, rather than pulling the whole database. Restoring is not separate magic, but simply running that SQL file against the target database via psql, so the database must first exist (createdb) and be free of the objects being restored. Note the direction of redirection: the file is read as INPUT (<), not overwritten (>).

What's checked

  1. dump of the customers table created
  2. table restored into shop_copy with all rows
  3. the dump has only the customers table

What it looks like