From: | Madison Kelly <linux(at)alteeve(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | pg_dump question |
Date: | 2007-01-04 16:20:58 |
Message-ID: | 459D296A.3070307@alteeve.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all,
I've created a database (pgsql 8.1 on Debian Etch) that uses
triggers/functions to keep all changes for various tables in a history
schema. This is the first time I've done this (captured and stored
changes in a different schema) so I was hoping for some backup/restore
advice.
As far as I can tell, you can only dump one schema at a time. Is this
true? If so, can I dump 'public' first and then append the dump of
'history' to the same file and be okay? Also, when I restore from this
file, can I prevent the triggers from running just during the reload of
the data?
I hope these aren't too junior questions. :)
Madi
PS - In case it helps, here's an example of a table/function I am using:
CREATE TABLE files (
file_id int default(nextval('id_seq')),
file_for_table text not null,
file_ref_id int not null,
file_desc text,
file_name text not null,
file_file_name text not null,
file_type text not null,
file_os text not null,
file_ver text,
file_active boolean not null default 't',
added_date timestamp without time zone not null default now(),
added_user int not null,
modified_date timestamp without time zone not null default now(),
modified_user int not null
);
ALTER TABLE files OWNER TO digimer;
CREATE TABLE history.files (
file_id int not null,
file_for_table text not null,
file_ref_id int not null,
file_desc text,
file_name text not null,
file_file_name text not null,
file_type text not null,
file_os text not null,
file_ver text,
file_active boolean not null,
added_date timestamp without time zone not null,
added_user int not null,
modified_date timestamp without time zone not null,
modified_user int not null
);
ALTER TABLE history.files OWNER TO digimer;
CREATE FUNCTION history_files() RETURNS "trigger"
AS $$
DECLARE
hist_files RECORD;
BEGIN
SELECT INTO hist_files * FROM public.files WHERE file_id=new.file_id;
INSERT INTO history.files
(file_id, file_for_table, file_ref_id, file_desc, file_name,
file_file_name, file_type, file_os, file_ver, file_active, added_user,
modified_date, modified_user)
VALUES
(hist_files.file_id, hist_files.file_for_table,
hist_files.file_ref_id, hist_files.file_desc, hist_files.file_name,
hist_files.file_file_name, hist_files.file_type, hist_files.file_os,
hist_files.file_ver, hist_files.file_active, hist_files.added_user,
hist_files.modified_date, hist_files.modified_user);
RETURN NULL;
END;$$
LANGUAGE plpgsql;
ALTER FUNCTION history_files() OWNER TO digimer;
CREATE TRIGGER trig_files AFTER INSERT OR UPDATE ON "files" FOR EACH ROW
EXECUTE PROCEDURE history_files();
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-01-04 16:36:35 | Re: self-referential UPDATE problem on 7.4 |
Previous Message | Gunnar Wagenknecht | 2007-01-04 15:57:57 | Dependency conflicts on CentOS 4.4 |