Обсуждение: Putting restrictions on pg_dump?
Is there a way to put a limit on pg_dump, so that it doesn't dump ALL data, but that matching a particular query? Something like: pg_dump -da --attribute-inserts -t "customers" \ --matching-query="select * from customers where id=11"; I'd like to selectively dump information from a query, but using the output format from pg_dump so that it can be used to create a (partial) database. Can this sort of thing be done? -Ben -- "The best way to predict the future is to invent it." - XEROX PARC slogan, circa 1978
On Wed, 4 Jan 2006 21:00:25 -0800, Benjamin Smith <lists@benjamindsmith.com> wrote: > Is there a way to put a limit on pg_dump, so that it doesn't dump ALL data, > but that matching a particular query? > > Something like: > > pg_dump -da --attribute-inserts -t "customers" \ > --matching-query="select * from customers where id=11"; > > I'd like to selectively dump information from a query, but using the output > format from pg_dump so that it can be used to create a (partial) database. > > Can this sort of thing be done? Not directly with pg_dump. You could create a table (create table customers_1 as select * from customers where id=11) and dump that but remember to change the tablename in the dump file or after loading it. You dont get any pk/fk/indexes on the table definition. You could also use copy to stdout/stdin. eg dump psql -d dbname -c "create temp table dump as select * from customers where id=11; copy dump to stdout;" >dumpfile eg restore psql -d newdb -c "copy customers from stdin" <dumpfile You might need to play around with supplying username/password. klint. +---------------------------------------+-----------------+ : Klint Gore : "Non rhyming : : EMail : kg@kgb.une.edu.au : slang - the : : Snail : A.B.R.I. : possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---------------------------------------+-----------------+
Good ideas, all. but, what about keeping things like check constraints, foreign keys, etc? Hmmm... maybe, if I dumped the entire DB schema, with no data, and then looped thru the tables, creating a temp table (as you describe) with a funky name (such as TABLEaBcDeFgH_U) and then pg_dumping that, and then using a regex to rename the table in the output... (eg /TABLE\s+TABLEaBcDeFgH_U/TABLE customers/ Ugh. I was hoping there was a cleaner way... -Ben On Wednesday 04 January 2006 23:35, you wrote: > On Wed, 4 Jan 2006 21:00:25 -0800, Benjamin Smith <lists@benjamindsmith.com> wrote: > > Is there a way to put a limit on pg_dump, so that it doesn't dump ALL data, > > but that matching a particular query? > > > > Something like: > > > > pg_dump -da --attribute-inserts -t "customers" \ > > --matching-query="select * from customers where id=11"; > > > > I'd like to selectively dump information from a query, but using the output > > format from pg_dump so that it can be used to create a (partial) database. > > > > Can this sort of thing be done? > > Not directly with pg_dump. > > You could create a table (create table customers_1 as select * from > customers where id=11) and dump that but remember to change the > tablename in the dump file or after loading it. You dont get any > pk/fk/indexes on the table definition. > > You could also use copy to stdout/stdin. > > eg dump > psql -d dbname -c "create temp table dump as select * from customers > where id=11; copy dump to stdout;" >dumpfile > > eg restore > psql -d newdb -c "copy customers from stdin" <dumpfile > > You might need to play around with supplying username/password. > > klint. > > +---------------------------------------+-----------------+ > : Klint Gore : "Non rhyming : > : EMail : kg@kgb.une.edu.au : slang - the : > : Snail : A.B.R.I. : possibilities : > : Mail University of New England : are useless" : > : Armidale NSW 2351 Australia : L.J.J. : > : Fax : +61 2 6772 5376 : : > +---------------------------------------+-----------------+ > -- "The best way to predict the future is to invent it." - XEROX PARC slogan, circa 1978
On Thu, 5 Jan 2006 09:59:45 -0800, Benjamin Smith <lists@benjamindsmith.com> wrote: > Good ideas, all. but, what about keeping things like check constraints, > foreign keys, etc? how about something like pg_dump -s -t customers dbname >customers.def > Hmmm... maybe, if I dumped the entire DB schema, with no data, and then looped > thru the tables, creating a temp table (as you describe) with a funky name > (such as TABLEaBcDeFgH_U) and then pg_dumping that, and then using a regex to > rename the table in the output... (eg > > /TABLE\s+TABLEaBcDeFgH_U/TABLE customers/ > > Ugh. I was hoping there was a cleaner way... Make a script with all the commands in it. You should be able to manually make a file that is similar to what pg_dump does. pg_dump -s -t customers dbname >customers.def echo "copy customers from stdin;" >>customers.def psql -d dname -c "create temp table dump as select * from customers where id=11; copy dump to stdout;" >>customers.def echo "\." >>customers.def klint. +---------------------------------------+-----------------+ : Klint Gore : "Non rhyming : : EMail : kg@kgb.une.edu.au : slang - the : : Snail : A.B.R.I. : possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---------------------------------------+-----------------+