Re: [GENERAL] pg_dump question (exclude schemas)
>I want to backup a database but exclude certain schemas with a patter. >I have 100 schemas with the pattern: 'sch_000', 'sch_001', and so on. >Will this work? >$pg_dump --exclude-schema='sch_*' >this does not seem to exclude all schemas with this pattern ( 'sch_*' ), >anything wrong here? >thanks It works fine on PostgreSQL 9.2.1. When both -n and -N are given, the behavior is to dump just the schemas that match at least one -n switch but no -N switches. If -N appears without -n, then schemas matching -N are excluded from what is otherwise a normal dump. -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-dump-question-exclude-schemas-tp5768872p5769872.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump question (exclude schemas)
On Wed, Aug 28, 2013 at 9:30 PM, Jay Vee wrote: > $pg_dump --exclude-schema='sch_*' > > this does not seem to exclude all schemas with this pattern ( 'sch_*' ), > anything wrong here? The option is fine and works for me on 9.2.4, I suspect the could do a clash with the -N (exclude-schema). Luca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump question (exclude schemas)
On 08/28/2013 12:30 PM, Jay Vee wrote: I want to backup a database but exclude certain schemas with a patter. I have 100 schemas with the pattern: 'sch_000', 'sch_001', and so on. Will this work? $pg_dump --exclude-schema='sch_*' this does not seem to exclude all schemas with this pattern ( 'sch_*' ), anything wrong here? What version of Postgres? What is the complete command line? Does it exclude any of the schemas? thanks J.V. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump question (exclude schemas)
I want to backup a database but exclude certain schemas with a patter. I have 100 schemas with the pattern: 'sch_000', 'sch_001', and so on. Will this work? $pg_dump --exclude-schema='sch_*' this does not seem to exclude all schemas with this pattern ( 'sch_*' ), anything wrong here? thanks J.V.
Re: [GENERAL] pg_dump question
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_dump question
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_dump question
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/ signature.asc Description: This is a digitally signed message part
Re: [GENERAL] pg_dump question
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] 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 textnot null,
file_ref_id int not null,
file_desc text,
file_name textnot null,
file_file_name textnot null,
file_type textnot null,
file_os textnot null,
file_vertext,
file_active boolean not nulldefault '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 textnot null,
file_ref_id int not null,
file_desc text,
file_name textnot null,
file_file_name textnot null,
file_type textnot null,
file_os textnot null,
file_vertext,
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();
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] pg_dump question
Andreas Kretschmer wrote: > Alvaro Herrera <[EMAIL PROTECTED]> schrieb: > > > > I would like to use pg_dump to backup a list of tables to one file, > > > > but it looks like > > > > the -t option only supports one file. So, pg_dump gives you the > > > > option of backing > > > > up and entire database or one file, but nothing in between. Am I > > > > missing something? > > > > Is there a way to backup multiple tables (but not the whole database)? > > > > > > You can wait for 8.2, this version supports multiple -t - options. > > > Or you can concat several table-dumps to one big dump. > > > > Note that the latter option does not guarantee that you get a consistent > > dump. > > Yes, of course. Ref. integrity, for instance. But i don't know, how 8.2 > solve this problem. Can you explain this? It dumps all tables in a single SERIALIZABLE transaction. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pg_dump question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/02/06 09:56, Alvaro Herrera wrote: > A. Kretschmer wrote: >> am Wed, dem 01.11.2006, um 13:50:58 -0800 mailte Thomas Burns folgendes: >>> Hi, >>> >>> I would like to use pg_dump to backup a list of tables to one file, >>> but it looks like >>> the -t option only supports one file. So, pg_dump gives you the >>> option of backing >>> up and entire database or one file, but nothing in between. Am I >>> missing something? >>> Is there a way to backup multiple tables (but not the whole database)? >> You can wait for 8.2, this version supports multiple -t - options. >> Or you can concat several table-dumps to one big dump. > > Note that the latter option does not guarantee that you get a consistent > dump. What about creating a new schema that "just" has views back to the base tables you want to dump. Then pg_dump the schema. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFSheIS9HxQb37XmcRAh87AKDnrDQj2sL/J8kv1+ZEz/7Ml4mb1QCglkc0 IfDD3/ISk7Jew8hdtpSyWNI= =1pD9 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_dump question
Alvaro Herrera <[EMAIL PROTECTED]> schrieb: > > > I would like to use pg_dump to backup a list of tables to one file, > > > but it looks like > > > the -t option only supports one file. So, pg_dump gives you the > > > option of backing > > > up and entire database or one file, but nothing in between. Am I > > > missing something? > > > Is there a way to backup multiple tables (but not the whole database)? > > > > You can wait for 8.2, this version supports multiple -t - options. > > Or you can concat several table-dumps to one big dump. > > Note that the latter option does not guarantee that you get a consistent > dump. Yes, of course. Ref. integrity, for instance. But i don't know, how 8.2 solve this problem. Can you explain this? Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_dump question
A. Kretschmer wrote: > am Wed, dem 01.11.2006, um 13:50:58 -0800 mailte Thomas Burns folgendes: > > Hi, > > > > I would like to use pg_dump to backup a list of tables to one file, > > but it looks like > > the -t option only supports one file. So, pg_dump gives you the > > option of backing > > up and entire database or one file, but nothing in between. Am I > > missing something? > > Is there a way to backup multiple tables (but not the whole database)? > > You can wait for 8.2, this version supports multiple -t - options. > Or you can concat several table-dumps to one big dump. Note that the latter option does not guarantee that you get a consistent dump. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_dump question
am Wed, dem 01.11.2006, um 13:50:58 -0800 mailte Thomas Burns folgendes: > Hi, > > I would like to use pg_dump to backup a list of tables to one file, > but it looks like > the -t option only supports one file. So, pg_dump gives you the > option of backing > up and entire database or one file, but nothing in between. Am I > missing something? > Is there a way to backup multiple tables (but not the whole database)? You can wait for 8.2, this version supports multiple -t - options. Or you can concat several table-dumps to one big dump. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] pg_dump question
Hi, I would like to use pg_dump to backup a list of tables to one file, but it looks like the -t option only supports one file. So, pg_dump gives you the option of backing up and entire database or one file, but nothing in between. Am I missing something? Is there a way to backup multiple tables (but not the whole database)? Thanks, Tom Burns ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] pg_dump question
[EMAIL PROTECTED] writes: > Can I dump specifc table and still get the actual sequence for it. It works for me in 7.3 and later --- at least when the column was created by calling it a SERIAL column. If you made the sequence separately then I don't think this is really a bug. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PG_DUMP question
On Tue, Dec 02, 2003 at 09:31:43AM +0900, Alex wrote: > is ther a way to pass a password to the pg_dump command to avoid being > prompted for it. > I want to execute the dump from a perl or shell script and dont want to > open accees for the user in the pg_hba.conf Use the ~/.pgpass file. It will work for any libpq program, not just pg_dump. -- Alvaro Herrera () "I call it GNU/Linux. Except the GNU/ is silent." (Ben Reiter) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] PG_DUMP question
Hi, is ther a way to pass a password to the pg_dump command to avoid being prompted for it. I want to execute the dump from a perl or shell script and dont want to open accees for the user in the pg_hba.conf I tried PGUSER=userid PGPASSWD=passwd pg_dump -Fc -f dump.db mydb; Thanks Alex ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
