pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Dumping  partial database content
6+ messages / 4 participants
[nested] [flat]

* Dumping  partial database content
@ 2004-06-30 12:07 h_harush@hotmail.com
  2004-07-02 04:10 ` Re: Dumping  partial database content mike g <mike@thegodshalls.com>
  0 siblings, 1 reply; 6+ messages in thread

From: h_harush@hotmail.com @ 2004-06-30 12:07 UTC (permalink / raw)
  To: pgsql-admin

Hi,

I need to dump only partial content of my database, for example i want
to dump only records that has been insterted to the database from a
specific date.
I tried to use pg_dump/pg_export but did not find anything usefull.

I'm using postgres version 7.3 on Linux RH 7.3

any idea/direction ? 

Regards,
Hanan



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Dumping  partial database content
  2004-06-30 12:07 Dumping  partial database content h_harush@hotmail.com
@ 2004-07-02 04:10 ` mike g <mike@thegodshalls.com>
  0 siblings, 0 replies; 6+ messages in thread

From: mike g @ 2004-07-02 04:10 UTC (permalink / raw)
  To: h_harush@hotmail.com; +Cc: pgsql-admin

I don't believe there is an option in pg_dump that current handles
that.  It is the whole table / object or nothing at all.  Depending on
the volume of transactions and frequency that you need to dump the data
will determine which solution works best for you.

Heavy volume and/or dumps multiple times per hour:  Replication would
probably be the best solution-perhaps Slony.

Occasionaly or small volume:  A Perl script / cron job that executes a
SELECT * FROM x WHERE date > $passed parameter when calling the script. 

One time:  Use pgadminIII or psql to execute a SELECT query and direct
the results into a file.

Mike
On Wed, 2004-06-30 at 07:07, h_harush@hotmail.com wrote:
> Hi,
> 
> I need to dump only partial content of my database, for example i want
> to dump only records that has been insterted to the database from a
> specific date.
> I tried to use pg_dump/pg_export but did not find anything usefull.
> 
> I'm using postgres version 7.3 on Linux RH 7.3
> 
> any idea/direction ? 
> 
> Regards,
> Hanan
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Dumping  partial database content
@ 2004-07-02 16:48 Bender, Cheryl <cbender@mriresearch.org>
  2004-07-02 19:15 ` Re: Dumping  partial database content Christopher Browne <cbbrowne@acm.org>
  0 siblings, 1 reply; 6+ messages in thread

From: Bender, Cheryl @ 2004-07-02 16:48 UTC (permalink / raw)
  To: mike@thegodshalls.com; h_harush@hotmail.com; +Cc: pgsql-admin

Just wondering--is it possible to dump on a temporary table?

Cheryl Bender


-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of mike g
Sent: Thursday, July 01, 2004 11:11 PM
To: h_harush@hotmail.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Dumping partial database content


I don't believe there is an option in pg_dump that current handles that.
It is the whole table / object or nothing at all.  Depending on the
volume of transactions and frequency that you need to dump the data will
determine which solution works best for you.

Heavy volume and/or dumps multiple times per hour:  Replication would
probably be the best solution-perhaps Slony.

Occasionaly or small volume:  A Perl script / cron job that executes a
SELECT * FROM x WHERE date > $passed parameter when calling the script. 

One time:  Use pgadminIII or psql to execute a SELECT query and direct
the results into a file.

Mike
On Wed, 2004-06-30 at 07:07, h_harush@hotmail.com wrote:
> Hi,
> 
> I need to dump only partial content of my database, for example i want

> to dump only records that has been insterted to the database from a 
> specific date. I tried to use pg_dump/pg_export but did not find 
> anything usefull.
> 
> I'm using postgres version 7.3 on Linux RH 7.3
> 
> any idea/direction ?
> 
> Regards,
> Hanan
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Dumping  partial database content
  2004-07-02 16:48 Re: Dumping  partial database content Bender, Cheryl <cbender@mriresearch.org>
@ 2004-07-02 19:15 ` Christopher Browne <cbbrowne@acm.org>
  0 siblings, 0 replies; 6+ messages in thread

From: Christopher Browne @ 2004-07-02 19:15 UTC (permalink / raw)
  To: pgsql-admin

Martha Stewart called it a Good Thing when cbender@mriresearch.org ("Bender, Cheryl") wrote:
> Just wondering--is it possible to dump on a temporary table?

The temp table is only visible inside the context of the transaction
under which it was created.  

A pg_dump session will create an independent transaction context,
where the table won't be visible, alas.

So you can't use pg_dump to dump the data out.

You may, however, use COPY to dump it out yourself.

  select * into temp table foo from bar;
  copy foo to '/tmp/foo_contents.txt';
-- 
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne; rate me
http://www.ntlug.org/~cbbrowne/postgresql.html
Rules of the Evil Overlord #50. "My main computers will have their own
special  operating system  that will  be completely  incompatible with
standard IBM and Macintosh powerbooks."
<http://www.eviloverlord.com/;



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Dumping  partial database content
@ 2004-07-02 18:56 Bender, Cheryl <cbender@mriresearch.org>
  0 siblings, 0 replies; 6+ messages in thread

From: Bender, Cheryl @ 2004-07-02 18:56 UTC (permalink / raw)
  To: Bender, Cheryl <cbender@mriresearch.org>; mike@thegodshalls.com; h_harush@hotmail.com; +Cc: pgsql-admin

Never mind--answered my own question.  

You can dump from a temporary table, which provides Hanan another way to
dump a subset of data.  

Use CREATE TEMPORARY TABLE as select ....


Cheryl Bender

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Bender, Cheryl
Sent: Friday, July 02, 2004 11:48 AM
To: mike@thegodshalls.com; h_harush@hotmail.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Dumping partial database content


Just wondering--is it possible to dump on a temporary table?

Cheryl Bender


-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of mike g
Sent: Thursday, July 01, 2004 11:11 PM
To: h_harush@hotmail.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Dumping partial database content


I don't believe there is an option in pg_dump that current handles that.
It is the whole table / object or nothing at all.  Depending on the
volume of transactions and frequency that you need to dump the data will
determine which solution works best for you.

Heavy volume and/or dumps multiple times per hour:  Replication would
probably be the best solution-perhaps Slony.

Occasionaly or small volume:  A Perl script / cron job that executes a
SELECT * FROM x WHERE date > $passed parameter when calling the script. 

One time:  Use pgadminIII or psql to execute a SELECT query and direct
the results into a file.

Mike
On Wed, 2004-06-30 at 07:07, h_harush@hotmail.com wrote:
> Hi,
> 
> I need to dump only partial content of my database, for example i want

> to dump only records that has been insterted to the database from a
> specific date. I tried to use pg_dump/pg_export but did not find 
> anything usefull.
> 
> I'm using postgres version 7.3 on Linux RH 7.3
> 
> any idea/direction ?
> 
> Regards,
> Hanan
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Dumping  partial database content
@ 2004-07-02 21:03 Bender, Cheryl <cbender@mriresearch.org>
  0 siblings, 0 replies; 6+ messages in thread

From: Bender, Cheryl @ 2004-07-02 21:03 UTC (permalink / raw)
  To: Christopher Browne <cbbrowne@acm.org>; pgsql-admin

I see what you're saying.  I thought it had worked, but when I looked at
the dump file there was no data.  Thanks.

Cheryl Bender


-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Christopher
Browne
Sent: Friday, July 02, 2004 2:15 PM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Dumping partial database content


Martha Stewart called it a Good Thing when cbender@mriresearch.org
("Bender, Cheryl") wrote:
> Just wondering--is it possible to dump on a temporary table?

The temp table is only visible inside the context of the transaction
under which it was created.  

A pg_dump session will create an independent transaction context, where
the table won't be visible, alas.

So you can't use pg_dump to dump the data out.

You may, however, use COPY to dump it out yourself.

  select * into temp table foo from bar;
  copy foo to '/tmp/foo_contents.txt';
-- 
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne; rate me
http://www.ntlug.org/~cbbrowne/postgresql.html
Rules of the Evil Overlord #50. "My main computers will have their own
special  operating system  that will  be completely  incompatible with
standard IBM and Macintosh powerbooks." <http://www.eviloverlord.com/;

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2004-07-02 21:03 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-30 12:07 Dumping  partial database content h_harush@hotmail.com
2004-07-02 04:10 ` mike g <mike@thegodshalls.com>
2004-07-02 16:48 Re: Dumping  partial database content Bender, Cheryl <cbender@mriresearch.org>
2004-07-02 19:15 ` Christopher Browne <cbbrowne@acm.org>
2004-07-02 18:56 Re: Dumping  partial database content Bender, Cheryl <cbender@mriresearch.org>
2004-07-02 21:03 Re: Dumping  partial database content Bender, Cheryl <cbender@mriresearch.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox