Обсуждение: Dumping part (not all) of the data in a database...methods?
I'm needing to do a partial dump on a database. All of the entries in the db can be marked as one of two groups, and I've been asked to create a dump of just the second group. It is possible to do a select statement based dump and just grab the one set of records in the output?
Food fight? Enjoy some healthy debate
in the Yahoo! Answers Food & Drink Q&A.
Food fight? Enjoy some healthy debate
in the Yahoo! Answers Food & Drink Q&A.
On 4/11/07, Andrew Edson <cheighlund@yahoo.com> wrote: > I'm needing to do a partial dump on a database. All of the entries in the > db can be marked as one of two groups, and I've been asked to create a dump > of just the second group. It is possible to do a select statement based > dump and just grab the one set of records in the output? you are aware you can dump a table at a time, right? pg_dump -t foo dumps table foo. A partial dumping scheme would probably involve using pg_dump with various flag in combination with a script that makes a list of things to dump. merlin
I am aware of this, yes, but the data in question is all (both sets) contained on a single table. That's why I was looking for a way to do a 'dump where (select foo where bar = 'criteria')' structure.
Merlin Moncure <mmoncure@gmail.com> wrote:
Looking for earth-friendly autos?
Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.
Merlin Moncure <mmoncure@gmail.com> wrote:
On 4/11/07, Andrew Edson wrote:
> I'm needing to do a partial dump on a database. All of the entries in the
> db can be marked as one of two groups, and I've been asked to create a dump
> of just the second group. It is possible to do a select statement based
> dump and just grab the one set of records in the output?
you are aware you can dump a table at a time, right? pg_dump -t foo
dumps table foo. A partial dumping scheme would probably involve
using pg_dump with various flag in combination with a script that
makes a list of things to dump.
merlin
Looking for earth-friendly autos?
Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.
Andrew Edson wrote: > I am aware of this, yes, but the data in question is all (both > sets) contained on a single table. That's why I was looking for a > way to do a 'dump where (select foo where bar = 'criteria')' > structure. What if you do that select into a new table, then pg_dump just that table? - John Burger MITRE
Andrew Edson <cheighlund@yahoo.com> writes: > I am aware of this, yes, but the data in question is all (both sets) contained on a single table. That's why I was lookingfor a way to do a 'dump where (select foo where bar = 'criteria')' structure. pg_dump is not in the business of editorializing on your data. However, as of 8.2 there is COPY (SELECT ...) TO ... which might serve your purpose. If you're on an older release I think it's temporary table time. regards, tom lane
On Wednesday 11. April 2007 19:50, Andrew Edson wrote: >I'm needing to do a partial dump on a database. All of the entries in > the db can be marked as one of two groups, and I've been asked to > create a dump of just the second group. It is possible to do a > select statement based dump and just grab the one set of records in > the output? I had a similar problem with my genealogy database, of which I'm making regular exports to the Web. In order to not publish data on living people, I've got a boolean flag is_public. For me, the easiest way to export a "washed" subset of the data, was writing a custom Python script. It also enabled me to transform the data in other ways, as the script is reading from special views and makes a "flattened" image of the database, more suitable for Web -- or at least for the Swedish dolphin stuff which I'm still using on my Website :-) As I'm no Python guru, the code is rather simplistic. But it does its job well enough for me. If you want a copy of the script, just drop me a mail. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE My Jazz Jukebox: http://www.last.fm/user/leifbk/
What about partitioning the table using bar='criteria' and bar!='criteria' as partitioning condition?
You can then decide to dump only the first subtable, the second or all the data with pg_dump.
I have similar issues and this is the solution I am working on (still not done).
Angelo
--
Bluemetrix Ltd
Northpoint House
Northpoint Business Park
Mallow Road
Cork
Ireland
Ph: +353 21 4212223
Fax: +353 21 4309131
Web: www.bluemetrix.com
The content of this e-mail may be confidential or legally privileged. If you are not the named addressee or the intended recipient please do not copy it or forward it to anyone. If you have received this email in error please destroy it and kindly notify the sender. Email cannot be guaranteed to be secure or error-free, it is your responsibility to ensure that the message (including attachments) is safe and authorised for use in your environment. Bluemetrix Ltd, Directors : Liam English, John Shannon.
Registered in Ireland at Northpoint House, Northpoint Business Park, Mallow Road, Cork
Co Reg No.: 335879 VAT Reg No. IE6355879K
You can then decide to dump only the first subtable, the second or all the data with pg_dump.
I have similar issues and this is the solution I am working on (still not done).
Angelo
On 4/11/07, Andrew Edson < cheighlund@yahoo.com> wrote:
I am aware of this, yes, but the data in question is all (both sets) contained on a single table. That's why I was looking for a way to do a 'dump where (select foo where bar = 'criteria')' structure.
Merlin Moncure <mmoncure@gmail.com> wrote:On 4/11/07, Andrew Edson wrote:
> I'm needing to do a partial dump on a database. All of the entries in the
> db can be marked as one of two groups, and I've been asked to create a dump
> of just the second group. It is possible to do a select statement based
> dump and just grab the one set of records in the output?
you are aware you can dump a table at a time, right? pg_dump -t foo
dumps table foo. A partial dumping scheme would probably involve
using pg_dump with various flag in combination with a script that
makes a list of things to dump.
merlin
Looking for earth-friendly autos?
Browse Top Cars by "Green Rating" at Yahoo! Autos' Green Center.
--
Bluemetrix Ltd
Northpoint House
Northpoint Business Park
Mallow Road
Cork
Ireland
Ph: +353 21 4212223
Fax: +353 21 4309131
Web: www.bluemetrix.com
The content of this e-mail may be confidential or legally privileged. If you are not the named addressee or the intended recipient please do not copy it or forward it to anyone. If you have received this email in error please destroy it and kindly notify the sender. Email cannot be guaranteed to be secure or error-free, it is your responsibility to ensure that the message (including attachments) is safe and authorised for use in your environment. Bluemetrix Ltd, Directors : Liam English, John Shannon.
Registered in Ireland at Northpoint House, Northpoint Business Park, Mallow Road, Cork
Co Reg No.: 335879 VAT Reg No. IE6355879K