pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Backing Up Partial Databases
3+ messages / 3 participants
[nested] [flat]

* Backing Up Partial Databases
@ 2006-10-26 03:08  Duncan McDonald <duncan@gpats.com.au>
  0 siblings, 1 reply; 3+ messages in thread

From: Duncan McDonald @ 2006-10-26 03:08 UTC (permalink / raw)
  To: pgsql-admin

Hi All,

I was wondering whether there was a way to back up partial sets of data as 
INSERT statements? Pg_dump seems only to handle whole databases or tables.

I have two identical databases (primary and backup) and I need to transfer a 
small portion of missing data from one to the other. Is there an easier way 
to do this rather than SELECT-ing the interval then parsing the ouput to 
look like INSERT statements?

Regards,

       -Duncan 




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

* Re: Backing Up Partial Databases
@ 2006-10-26 06:38  Shane Ambler <pgsql@007Marketing.com>
  parent: Duncan McDonald <duncan@gpats.com.au>
  0 siblings, 1 reply; 3+ messages in thread

From: Shane Ambler @ 2006-10-26 06:38 UTC (permalink / raw)
  To: Duncan McDonald <duncan@gpats.com.au>; +Cc: pgsql-admin

Duncan McDonald wrote:
> Hi All,
> 
> I was wondering whether there was a way to back up partial sets of data 
> as INSERT statements? Pg_dump seems only to handle whole databases or 
> tables.
> 
> I have two identical databases (primary and backup) and I need to 
> transfer a small portion of missing data from one to the other. Is there 
> an easier way to do this rather than SELECT-ing the interval then 
> parsing the ouput to look like INSERT statements?
> 

I can think of a couple of ways -

COPY (SELECT * FROM myPrimaryTable WHERE id<200) TO '/tmp/myData';

you can then

COPY myBackupTable FROM '/tmp/myData';


Another option is to create a table to hold the selected rows then
SELECT * INTO myTempTable FROM myPrimaryTable WHERE id<200;

then use pg_dump --table=myTempTable > /tmp/myData

the --inserts option of pg_dump will export as INSERT's if that is the 
way you want to do it. You will need to do a find and replace on the 
INSERT's to change the table name though.


-- 

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz



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

* Re: Backing Up Partial Databases
@ 2006-10-26 21:40  Jim C. Nasby <jim@nasby.net>
  parent: Shane Ambler <pgsql@007Marketing.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Jim C. Nasby @ 2006-10-26 21:40 UTC (permalink / raw)
  To: Shane Ambler <pgsql@007Marketing.com>; +Cc: Duncan McDonald <duncan@gpats.com.au>; pgsql-admin

On Thu, Oct 26, 2006 at 04:08:05PM +0930, Shane Ambler wrote:
> >I have two identical databases (primary and backup) and I need to 
> >transfer a small portion of missing data from one to the other. Is there 
> >an easier way to do this rather than SELECT-ing the interval then 
> >parsing the ouput to look like INSERT statements?
> >
> 
> I can think of a couple of ways -
> 
> COPY (SELECT * FROM myPrimaryTable WHERE id<200) TO '/tmp/myData';
 
Only in 8.2...

> the --inserts option of pg_dump will export as INSERT's if that is the 
> way you want to do it. You will need to do a find and replace on the 
> INSERT's to change the table name though.

Though, I don't see any reason you'd want to do that; just stick with
the pg_dump defaults.

Another option is to use dbi_link.
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)



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


end of thread, other threads:[~2006-10-26 21:40 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-26 03:08 Backing Up Partial Databases Duncan McDonald <duncan@gpats.com.au>
2006-10-26 06:38 ` Shane Ambler <pgsql@007Marketing.com>
2006-10-26 21:40   ` Jim C. Nasby <jim@nasby.net>

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