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!)