Thread: pg_dump question
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();
Madison Kelly wrote: > 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? No, pg_dump dumps a whole database by default. You can dump just a single schema or table though. > If so, can I dump 'public' first and then append the dump of > 'history' to the same file and be okay? No, someone might have updated public in-between. > Also, when I restore from this > file, can I prevent the triggers from running just during the reload of > the data? Yes, there's a command-line setting when doing a data-only restore. When doing a full restore (schema+data) this is done for you. Try the page below or "man pg_dump"/"man pg_restore" for full details: http://www.postgresql.org/docs/8.1/static/reference-client.html -- Richard Huxton Archonet Ltd
Hi, On Thu, 2007-01-04 at 11:20 -0500, Madison Kelly wrote: > As far as I can tell, you can only dump one schema at a time. Is > this true? You can dump multiple schemas and multiple tables at a time with 8.2. Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
Attachment
Richard Huxton wrote: >> As far as I can tell, you can only dump one schema at a time. Is >> this true? > > No, pg_dump dumps a whole database by default. You can dump just a > single schema or table though. Hmm, I wonder why I thought this... Was this true in older versions or did I just imagine this? :) > > If so, can I dump 'public' first and then append the dump of >> 'history' to the same file and be okay? > > No, someone might have updated public in-between. Ah, of course. > > Also, when I restore from this >> file, can I prevent the triggers from running just during the reload >> of the data? > > Yes, there's a command-line setting when doing a data-only restore. When > doing a full restore (schema+data) this is done for you. > > Try the page below or "man pg_dump"/"man pg_restore" for full details: > http://www.postgresql.org/docs/8.1/static/reference-client.html I had read the man pages, but I re-read them and I apparently went on a mental vacation and missed a fair chunk of it. *sigh* Thanks kindly for your reply! Madi
Madison Kelly wrote: > Richard Huxton wrote: >>> As far as I can tell, you can only dump one schema at a time. Is >>> this true? >> >> No, pg_dump dumps a whole database by default. You can dump just a >> single schema or table though. > > Hmm, I wonder why I thought this... Was this true in older versions or > did I just imagine this? :) Be comforted, imagination is a trait shared by all highly intelligent people :-) >> Try the page below or "man pg_dump"/"man pg_restore" for full details: >> http://www.postgresql.org/docs/8.1/static/reference-client.html > > I had read the man pages, but I re-read them and I apparently went on a > mental vacation and missed a fair chunk of it. *sigh* You'll almost certainly want the "custom" format for your dumps. You might find the --list and --use-list options useful for restoring sets of tables from a full dump. -- Richard Huxton Archonet Ltd