pg_dump question

[Madison Kelly <linux@xxxxxxxxxxx>]

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();