Hi
We have a system with several users. Sometimes one of the users make a mistake with his data and want to restore, like he want to do an undo. Only one user should be restored, not all users.
I work as sysadm so I can not change the system, but has to solve the task at this level.
My approach is to do like pg_dump. I'm looking at the solution backwards. When I has to do the restore, I would have a file which look like pg_dump but only contain data for one id. As there already is data in system for this id I have to delete that data first. Something like:
DELETE FROM person WHERE pid=7;
COPY person (pid, name) FROM stdin;
7 Joe
\.
...and more tables comes here, which is all based on pid=7.
Is there a utility which can do this?
----------------------------------
What I have done so far.
I have made a bash script which generate a SQL-script, which is called crontab.sql.
crontab.sql is static as long as the data structure does not change. crontab.sql runs every day and dumps data which looks like the above example.
The crontab call looks like this:
psql --no-align --tuples-only --set=pid=7 < crontab.sql > daily.sql
crontab.sql looks like:
SELECT 'DELETE FROM person WHERE pid=' || :pid || ';';
SELECT 'COPY person (pid,name) FROM stdin;';
COPY (SELECT pid, name FROM person WHERE pid=:pid) TO stdout;
SELECT '\.';
This will result in a daily.sql like:
DELETE FROM person WHERE pid=7;
COPY person (pid,name) FROM stdin;
7 Joe
\.
...and so on with more tables.
There are more tables and they constrains. So DELETE FROM has to be done in reverse order (I will not use CASCADE). Then data is copied in the forward order as they should.
Here is some data similar to what has to be done:
create table if not exists person (
pid serial PRIMARY KEY,
name text
);
create table if not exists mail (
mid serial PRIMARY KEY,
pid integer references person(pid),
subject text,
body text
);
create table if not exists attachment (
aid serial PRIMARY KEY,
mid integer references mail(mid),
content text
);
INSERT INTO person(name) VALUES('Joe');
INSERT INTO person(name) VALUES('jane');
INSERT INTO mail(pid,subject,body) VALUES( (SELECT pid FROM person WHERE name='Joe') , 'A', 'Hello, world!');
INSERT INTO mail(pid,subject,body) VALUES( (SELECT pid FROM person WHERE name='Joe') , 'B', 'Guten tag welt!');
INSERT INTO attachment(mid,content) VALUES( (SELECT mid FROM mail WHERE subject='A'), 'blah blah');
best regards/hans