You will take the dump apart into commands and find the one that damaged shop.
A logical dump is a set of commands that build the database again: tables, rows, counters, keys. The database files themselves are not in it. pg_dump writes these commands, and pg_restore runs them. That is why a dump can be brought up on another server and in another database.
The -Fc option of pg_dump picks the custom format, a compressed archive that pg_restore reads. pg_restore prints the archive's table of contents (--list, whose header says which database it came from and when, Archive created at) and turns the archive back into SQL text: -f - prints it to the screen, and -t orders limits it to one table. Without -F, pg_dump writes plain SQL text straight away.
Look inside the dump
In ~/backup, print the table of contents of shop.dump, then its SQL commands that create tables, load rows and set counters.
First, predict
Which commands does the dump use to load table rows?
Now start the exercise, run the command and compare your prediction with the output.
pg_restore ran these very commands in order in the live shop. CREATE TABLE failed: the tables exist, already exists. COPY failed on the first row: id 1 is taken, duplicate key. The keys were there too.
But setval went through. The sequence orders_id_seq is the counter the database takes the id of a new order from. The dump set it back to yesterday's 5, so the next order got id 6, which is taken. pg_restore's errors showed up at once; the damage showed up only on the next insert.
All rows were deleted from orders with DELETE FROM orders, while the table itself stayed. What will pg_restore -d shop shop.dump do with yesterday's dump?
-
CREATE TABLEfails because the table exists.COPYloads the rows into the existing table. -
duplicate keyappears when an id is taken. After theDELETEthe ids in orders are free, soCOPYsucceeds; only theCOPYinto customers fails. -
Without
--exit-on-errorpg_restore carries on after an error: at the start of the lesson it ranCOPYandsetvalafter the failedCREATE TABLE.
pg_restore runs the dump's commands one by one and carries on after an error. CREATE TABLE and the keys fail, COPY into the empty orders succeeds, and setval sets the counter to 5: orders holds yesterday's 5 orders again.