[rt-users] restoring dumps from rt-shredder vs. postgres (+workaround)

[Alexander Talos-Zens]

Hej!

When I tried to restore rt-shredded data from the --sqldump, I got
errors like

ERROR:  relation "CachedGroupMembers" does not exist
ERROR:  relation "GroupMembers" does not exist
ERROR:  relation "CachedGroupMembers" does not exist
ERROR:  relation "Transactions" does not exist
ERROR:  relation "Groups" does not exist
ERROR:  relation "Principals" does not exist

I didn't find any mention of that problem, so I thought I'd post here.

Psql throws these errors because the way rt-shredder builds the "INSERT
INTO" statements clashes with postgres' case sensitivity:

INSERT INTO "CachedGroupMembers" .... --> error
INSERT INTO "cachedgroupmembers" .... --> ok
INSERT INTO CachedGroupMembers .... --> ok as well ;-)

There's an easy workaround with sed:

sed -e 's/^\(INSERT INTO \)"\([^"]\)"/\1\2/' your_dumpfile | psql
your_psql_args

Assuming that you notice this issue only when you need to restore
something, any other solution in the code would come too late (e.g. one
could quote the table names in the CREATE TABLE statements as well).

Tested on RHEL5 with PostgreSQL 8.1.23, rt-3.8.8, RTFM-2.4.2,
RT-IR-2.4.4 but I'd expect both the problem and the fix to be pretty
much version independent.

Cheers,

Alexander

-- 
IT-Security, Universitaet Wien, ACOnet CERT
<URL:http://www.univie.ac.at/ZID/security/>
T: +43-1-4277-14351  M: +43-664-60277-14351