Thread: Backup of a big DB with a lot of large objects

Backup of a big DB with a lot of large objects

From
"Milen A. Radev"
Date:
We have a big DB that's backuped nightly using pg_dump in the custom
format. Most of its size are several thousand large objects. During
the dump of those LO the machine is heavily loaded so we are looking
for a way to "optimise" that part of backup process. We had an idea to
backup the DB but without the LO and to dump nightly only those LO
that were changed or added since the last backup. Unfortunately in
v8.1 (we use 8.1.5) there is no way to dump a whole database without
the LO.

Any ideas for a less taxing backup process?

--
Milen A. Radev

Re: Backup of a big DB with a lot of large objects

From
Steve Holdoway
Date:
On Thu, 30 Nov 2006 18:12:31 +0200
"Milen A. Radev" <milen@radev.net> wrote:

> We have a big DB that's backuped nightly using pg_dump in the custom
> format. Most of its size are several thousand large objects. During
> the dump of those LO the machine is heavily loaded so we are looking
> for a way to "optimise" that part of backup process. We had an idea to
> backup the DB but without the LO and to dump nightly only those LO
> that were changed or added since the last backup. Unfortunately in
> v8.1 (we use 8.1.5) there is no way to dump a whole database without
> the LO.
>
> Any ideas for a less taxing backup process?
>
> --
> Milen A. Radev
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

Replicate it???

Re: Backup of a big DB with a lot of large objects

From
"Milen A. Radev"
Date:
Steve Holdoway написа:
> On Thu, 30 Nov 2006 18:12:31 +0200
> "Milen A. Radev" <milen@radev.net> wrote:
>
>> We have a big DB that's backuped nightly using pg_dump in the custom
>> format. Most of its size are several thousand large objects. During
>> the dump of those LO the machine is heavily loaded so we are looking
>> for a way to "optimise" that part of backup process. We had an idea to
>> backup the DB but without the LO and to dump nightly only those LO
>> that were changed or added since the last backup. Unfortunately in
>> v8.1 (we use 8.1.5) there is no way to dump a whole database without
>> the LO.
>>
>> Any ideas for a less taxing backup process?
>>
[..]
>
> Replicate it???

I'm hesitant about replacing backup with replication. It seems to me
like trying to shoot a mosquito with a cannon.


--
Milen A. Radev

Re: Backup of a big DB with a lot of large objects

From
Richard Broersma Jr
Date:
 > > Replicate it???
>
> I'm hesitant about replacing backup with replication. It seems to me
> like trying to shoot a mosquito with a cannon.

One some threads that I've read, some posters mention that once their databases reach a certain
size threshold, backups times become so large that replication seems to be the better perhaps only
choice.

I suppose that if replication is used, one of their child read-only servers can be dedicated the
task of backing up the database thereby relieving that load off of the parent read/write server.

But this doesn't solve the problem increasing back-up times.  Of course, equipping yourself with a
cannon now will really help after your mosquito grows in to an albatross. :o)

Regards,
Richard Broersma Jr.

Re: Backup of a big DB with a lot of large objects

From
"Joshua D. Drake"
Date:
On Fri, 2006-12-01 at 10:06 -0800, Richard Broersma Jr wrote:
>  > > Replicate it???
> >
> > I'm hesitant about replacing backup with replication. It seems to me
> > like trying to shoot a mosquito with a cannon.
>
> One some threads that I've read, some posters mention that once their databases reach a certain
> size threshold, backups times become so large that replication seems to be the better perhaps only
> choice.

It can be but see PITR as well.

>
> I suppose that if replication is used, one of their child read-only servers can be dedicated the
> task of backing up the database thereby relieving that load off of the parent read/write server.

Yep, use replication and then backup the slave.

>
> But this doesn't solve the problem increasing back-up times.  Of course, equipping yourself with a
> cannon now will really help after your mosquito grows in to an albatross. :o)

PITR.

Joshua D. Drake


>
> Regards,
> Richard Broersma Jr.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>
--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate