Postgres Interactive Restore
Published on Oct 27, 2023
If you have used git interactive rebase, you know you can specify how rebase is done by picking specific commits, skipping, editing, etc. by merely editing a file in editor (most likely vim). Well pg_restore has similar feature.
git rebase -i HEAD~3 1
pick f7f3f6d Change my name a bit
pick 310154e Update README formatting and add blame
pick a5f4a0d Add cat-file
# Rebase 710f0f8..a5f4a0d onto 710f0f8
#
# Commands:
# p, pick <commit> = use commit
# r, reword <commit> = use commit, but edit the commit message
# e, edit <commit> = use commit, but stop for amending
# s, squash <commit> = use commit, but meld into previous commit
# f, fixup <commit> = like "squash", but discard this commit's log message
# x, exec <command> = run command (the rest of the line) using shell
# b, break = stop here (continue rebase later with 'git rebase --continue')
# d, drop <commit> = remove commit
# l, label <label> = label current HEAD with a name
# t, reset <label> = reset HEAD to a label
# m, merge [-C <commit> | -c <commit>] <label> [# <oneline>]
# . create a merge commit using the original merge commit's
# . message (or the oneline, if no original merge commit was
# . specified). Use -c <commit> to reword the commit message.
#
# These lines can be re-ordered; they are executed from top to bottom.
#
# If you remove a line here THAT COMMIT WILL BE LOST.
#
# However, if you remove everything, the rebase will be aborted.
#
# Note that empty commits are commented out
I had a PG DB dump just the other day and I needed to apply it to my database, but I need to apply only specific changes (tables, constraints, functions, etc). I’ve had that before and I’ve used --table, --schema or --trigger where you can specify by name what you want to restore. I’ve now discovered that pg_restore has better option, an interactive option similar to git interactive rebase.
By specifying --list argument for pg_restore 2, a file gets created instead of restoring database. That file contains a list of actions to be performed during restore. An example file:
;
; Archive created at Mon Sep 14 13:55:39 2009
; dbname: DBDEMOS
; TOC Entries: 81
; Compression: 9
; Dump Version: 1.10-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 8.3.5
; Dumped by pg_dump version: 8.3.8
;
;
; Selected TOC Entries:
;
3; 2615 2200 SCHEMA - public demouser
1861; 0 0 COMMENT - SCHEMA public demouser
41; 2615 2200 SCHEMA - event_store demouser
64; 0 0 COMMENT - SCHEMA event_store demouser
238; 1259 17443 TABLE event_store events demouser
; depends on: 41
286; 1255 17473 FUNCTION event_store event_store_delete() demouser
; depends on: 41
242; 1259 17492 TABLE event_store snapshots demouser
; depends on: 41
...
The file represents just list of internal IDs of object to be restored and everything after ; is a comment. So essentially the file content above is just:
3
1861
41
64
238
286
242
By editing the file above to just:
238; 1259 17443 TABLE event_store events demouser
; depends on: 41
286; 1255 17473 FUNCTION event_store event_store_delete() demouser
; depends on: 41
242; 1259 17492 TABLE event_store snapshots demouser
; depends on: 41
…and by using --use-list with the file path on next pg_restore command, you can restore just the items specified in the file! So in this case it’s just events and snapshots table on event_store schema together with event_store_delete function.
In summary:
# Generate list of entries - entries.txt
pg_restore --list --verbose -h localhost -U postgres -d test_db pg_db_dump.tar.gz > entries.txt
# Edit the entries.txt
# Apply the entries
pg_restore --verbose -h localhost -U postgres -d test_db pg_db_dump.tar.gz --use-list entries.txt