pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBacking 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