Saving Your Bacon With a PostgreSQL Restore File

Last week our “junior” dba did an unconstrained update (ie. no where clause) on a table in production. Rather than resorting to physical punishment, I figured I ought to fix the thing first. We don’t use PITR on this system, but luckily we do have nightly backups and the data in question rarely changes; in a lot of other scenarios we would be screwed… still I thought the post mortem might be an interesting read, so here it is.
chimp=# update host set root_password = '\243\206E\372O\214LD\'; UPDATE 3369
As soon as you see something like this, you kick yourself for not having wrapped this thing in a transaction. After a few seconds of panic, I went about recovering the data:
-bash-3.00$ createdb xxx CREATE DATABASE -bash-3.00$ pg_restore -Fc chimp.pgr -l -t host > host.pgrl
First thing I did was create a spare database xxx to do the recovery in. I next used pg_restore’s -l option to create a restore file containing the table I needed to fix. The restore file looks like this:
; ; Archive created at Wed Apr 19 23:45:04 2006 ; dbname: chimp ; TOC Entries: 2094 ; Compression: -1 ; Dump Version: 1.10-0 ; Format: CUSTOM ; Integer: 4 bytes ; Offset: 8 bytes ; Dumped from database version: 8.1.3 ; Dumped by pg_dump version: 8.1.3 ; ; ; Selected TOC Entries: ; 1912; 1259 653874882 TABLE public host postgres 3913; 0 653874882 TABLE DATA public host postgres
With that built, I then reloaded the table into the new database I had created.
-bash-3.00$ pg_restore -Fc chimp.pgr -d xxx -t host -L host.pgrl -bash-3.00$ psql xxx Welcome to psql 8.1.3, the PostgreSQL interactive terminal. Type: copyright for distribution terms h for help with SQL commands ? for help with psql commands g or terminate with semicolon to execute query q to quit xxx=# d List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | host | table | postgres (1 row) xxx=# select count(*) from host; count ------- 3369 (1 row)
Initially I thought I might try to generate some type of sql statement from psql on the fly, but had issues with concatenating the bytea field with text. Since I was trying to work quickly, I instead decide I would just load the whole table onto production and do a joined update.
-bash-3.00$ pg_dump xxx > host.sql -bash-3.00$ vi host.sql -- Name: host; Type: TABLE; Schema: public; Owner: postgres; Tablespace: -- CREATE TABLE host ( host_id integer DEFAULT nextval('host_id_seq'::regclass) NOT NULL, site_id integer NOT NULL, platform_id integer NOT NULL, root_password bytea NOT NULL, message_directory text NOT NULL DEFAULT '/var/spool'::text, last_contact timestamp with time zone, software_uptodate boolean DEFAULT false NOT NULL, tolerance_application integer NOT NULL, ); COPY host (host_id, site_id, platform_id, root_password, message_directory, last_contact, software_uptodate, tolerance_application) FROM stdin; 21248 18765 3 \373\273\374\322\022\217\373 /var/spool/chimp 2006-04-14 14:57:13.837271-04 f 1 20324 20323 6 \243\206E\372O\214LD\243\300\225 \Program Files\chimp\ 2006-04-09 18:51:27.764742-05 f 3 ~~~~~~~~ 21264 21263 3 \2112\372O\214L\322\022\217\373 /var/spool/chimp 2006-04-14 15:00:11.032963-04 f 2 21461 15471 4 \373\273\374\322\022\217\373\256 /var/spool/chimp 2006-04-14 11:25:31.234836-04 f 1 .
Here we do a little %s/host/host_recovery/g to rename the table to something we can import into our production system. I also cleaned out a few set declarations and comments and then saved the change. Thinking about it now, I should have just renamed the table in the xxx database, but again, I was just working quickly.
-bash-3.00$ psql -h samus chimp Welcome to psql 8.1.3, the PostgreSQL interactive terminal. Type: copyright for distribution terms h for help with SQL commands ? for help with psql commands g or terminate with semicolon to execute query q to quit chimp=# i host.sql CREATE TABLE chimp=# select count(*) from host_recovery; count ------- 3369 (1 row)
We import the sql script using i, then do a quick check to make sure our records are in tact. Everything looks good, now on to the update, and you can bet I will use a transaction this time!!
chimp=# BEGIN; chimp=# UPDATE host SET root_password = host_recovery.root_password FROM host_recovery WHERE host_recovery.host_id = host.host_id; BEGIN UPDATE 3369 chimp=# COMMIT; COMMIT; chimp=#q
Yay, everything is back to normal. Remember I was very lucky that the specific field that was updated happened to be data that doesn’t change often, otherwise the simple pg_dumps wouldn’t have worked. Still, hopefully this will give you some ideas to think about how to chain together some tools to get yourself out of a sticky situation should the need ever arise (and hopefully it won’t!)