PostgreSQL Administration - Backing Up Partial Databases

Author

Backing Up Partial Databases

Duncan McDonald

2006-10-26, 12:16 am

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Shane Ambler

2006-10-26, 5:18 am

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Jim C. Nasby

2006-10-26, 7:25 pm

On Thu, Oct 26, 2006 at 04:08:05PM +0930, Shane Ambler wrote:
>
> 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)

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sponsored Links