Обсуждение: Backup of a big DB with a lot of large objects
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
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???
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
> > 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.
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