pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
reduce downtime when upgrading 7.3 -> 7.4
6+ messages / 5 participants
[nested] [flat]

* reduce downtime when upgrading 7.3 -> 7.4
@ 2004-02-08 02:35  Palle Girgensohn <girgen@pingpong.net>
  0 siblings, 3 replies; 6+ messages in thread

From: Palle Girgensohn @ 2004-02-08 02:35 UTC (permalink / raw)
  To: pgsql-admin

Hi,

We use postgresql for rather large databases. For a typical installation, a 
pg_restore takes a couple of hours, at least (the dumpfiles are usually 2-4 
gigabytes or so, including BLOBs). The machines are expected to be up 24/7, 
so this dump/restore procedure makes upgrading unpopular. Is there any 
(safe) way to speed this process up?

The most obvious question is, can we use pg_upgrade from contrib? It seems 
not to have been updated since 7.3, and is generally documented as 
untested. What kind of problems can we get, can they be tested for in a 
testbed in advance?

If pg_upgrade is not a good idea, how can I speed up pg_restore? Best way 
to set things like fsync etc in postgresql.conf? Will it make a big 
difference?

We use FreeBSD-4.9 and want to upgrade from 7.3.4 -> 7.4.1.

Thanks,
Palle




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

* Re: reduce downtime when upgrading 7.3 -> 7.4
@ 2004-02-08 04:30  Christopher Browne <cbbrowne@acm.org>
  parent: Palle Girgensohn <girgen@pingpong.net>
  2 siblings, 1 reply; 6+ messages in thread

From: Christopher Browne @ 2004-02-08 04:30 UTC (permalink / raw)
  To: pgsql-admin

Martha Stewart called it a Good Thing when girgen@pingpong.net (Palle Girgensohn) wrote:
> We use postgresql for rather large databases. For a typical
> installation, a pg_restore takes a couple of hours, at least (the
> dumpfiles are usually 2-4
> gigabytes or so, including BLOBs). The machines are expected to be up
> 24/7, so this dump/restore procedure makes upgrading unpopular. Is
> there any (safe) way to speed this process up?
>
> The most obvious question is, can we use pg_upgrade from contrib? It
> seems not to have been updated since 7.3, and is generally documented
> as untested. What kind of problems can we get, can they be tested for
> in a testbed in advance?
>
> If pg_upgrade is not a good idea, how can I speed up pg_restore? Best
> way to set things like fsync etc in postgresql.conf? Will it make a
> big difference?
>
> We use FreeBSD-4.9 and want to upgrade from 7.3.4 -> 7.4.1.

A "faster" method would be to use one of the replication systems, such
as ERserv.

You have your existing database, running 7.3.4, and set up another DB
instance (perhaps on the same box) running 7.4.1.

You replicate the 7.3 DB over to the 7.4 one.  It may take a
substantial period of time to get them in near sync, but once you get
them close, you can disconnect the application that is injecting
updates to the 7.3 DB, and it should take mere minutes to get those
updates into the 7.4 system.

You then shut down the 7.3 system, shift the 7.4 one to the ports your
application expects to use, and voila!  You did it all with a mere 10
minute outage.  You may need a few minutes to add back integrity
constraints that the replication system required you to drop (because
it may not order inserts in the exact same order that they went into
place in the origin system).

All that being said, ERserv may not cope perfectly with BLOBs, so you
may need to do something special about that.

But the above approach, while it has aspects that are fragile, can
certainly cut down "down time" REALLY substantially.

The other major approach that would provide something equivalent is
the "Holy Grail" of PITR, which some people are thinking of working on
now.  That would be a more universal scheme which would be logically
quite similar:

 -> You start a pg_dump to send to the 7.5 system, and start
    archiving WAL files.

 -> You load the pg_dump into the 7.5 system.

 -> You then move over the WAL files, and replay them into the 7.5
    system.  (Big magic occurs here!)

 -> You shut down the 7.3 system, copy the last WAL files over,
    and and load them into 7.5.

And voila!  You have a 7.5 database that started with the contents of
the pg_dump, and then had all of the subsequent transactions applied
to it, bringing it up to date.

The Big Magic part is of the need to load 7.3 WAL data into a 7.5
system.  If anything about data format changes, something fancy has to
happen read it in properly.  I wrote 7.5 rather than 7.4 because this
is certainly not something that will be ready for a 7.4 release.  

If you need something sooner, then you'll need to look into the
existing replication solutions.
-- 
output = ("cbbrowne" "@" "ntlug.org")
http://cbbrowne.com/info/rdbms.html
If con is the opposite of pro, is Congress the opposite of progress?



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

* Re: reduce downtime when upgrading 7.3 -> 7.4
@ 2004-02-08 05:01  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Palle Girgensohn <girgen@pingpong.net>
  2 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2004-02-08 05:01 UTC (permalink / raw)
  To: Palle Girgensohn <girgen@pingpong.net>; +Cc: pgsql-admin

Palle Girgensohn <girgen@pingpong.net> writes:
> The most obvious question is, can we use pg_upgrade from contrib? It seems 
> not to have been updated since 7.3, and is generally documented as 
> untested.

"Guaranteed not to work" is more like it.  I have some ambitions of
rewriting it for future releases, but don't even think of going there
at the moment.

			regards, tom lane



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

* Re: reduce downtime when upgrading 7.3 -> 7.4
@ 2004-02-10 14:33  Robert Treat <xzilla@users.sourceforge.net>
  parent: Palle Girgensohn <girgen@pingpong.net>
  2 siblings, 1 reply; 6+ messages in thread

From: Robert Treat @ 2004-02-10 14:33 UTC (permalink / raw)
  To: Palle Girgensohn <girgen@pingpong.net>; +Cc: pgsql-admin

On Sat, 2004-02-07 at 21:35, Palle Girgensohn wrote:
> Hi,
> 
> We use postgresql for rather large databases. For a typical installation, a 
> pg_restore takes a couple of hours, at least (the dumpfiles are usually 2-4 
> gigabytes or so, including BLOBs). The machines are expected to be up 24/7, 
> so this dump/restore procedure makes upgrading unpopular. Is there any 
> (safe) way to speed this process up?
> 
> If pg_upgrade is not a good idea, how can I speed up pg_restore? Best way 
> to set things like fsync etc in postgresql.conf? Will it make a big 
> difference?
> 

yes, setting fsync off should make a significant difference. I usually
recommend it cause if there is a machine failure during restore I will
want to start the process again anyway.  Other items you should probably
change are cranking up sort_mem significantly (if your restore is the
only process running, let it take up most of the ram on the box) and you
can increase check_point segments as well.  HTH

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL




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

* Re: reduce downtime when upgrading 7.3 -> 7.4
@ 2004-02-10 16:24  Palle Girgensohn <girgen@pingpong.net>
  parent: Robert Treat <xzilla@users.sourceforge.net>
  0 siblings, 0 replies; 6+ messages in thread

From: Palle Girgensohn @ 2004-02-10 16:24 UTC (permalink / raw)
  To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-admin

--On Tuesday, February 10, 2004 09:33:20 -0500 Robert Treat 
<xzilla@users.sourceforge.net> wrote:

> On Sat, 2004-02-07 at 21:35, Palle Girgensohn wrote:
>> Hi,
>>
>> We use postgresql for rather large databases. For a typical
>> installation, a  pg_restore takes a couple of hours, at least (the
>> dumpfiles are usually 2-4  gigabytes or so, including BLOBs). The
>> machines are expected to be up 24/7,  so this dump/restore procedure
>> makes upgrading unpopular. Is there any  (safe) way to speed this
>> process up?
>>
>> If pg_upgrade is not a good idea, how can I speed up pg_restore? Best
>> way  to set things like fsync etc in postgresql.conf? Will it make a big
>> difference?
>>
>
> yes, setting fsync off should make a significant difference. I usually
> recommend it cause if there is a machine failure during restore I will
> want to start the process again anyway.  Other items you should probably
> change are cranking up sort_mem significantly (if your restore is the
> only process running, let it take up most of the ram on the box) and you
> can increase check_point segments as well.  HTH

Thanks, I'll try this.

Regards,
Palle




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

* Re: reduce downtime when upgrading 7.3 -> 7.4
@ 2004-02-12 22:06  Andrew Sullivan <ajs@crankycanuck.ca>
  parent: Christopher Browne <cbbrowne@acm.org>
  0 siblings, 0 replies; 6+ messages in thread

From: Andrew Sullivan @ 2004-02-12 22:06 UTC (permalink / raw)
  To: pgsql-admin

On Sat, Feb 07, 2004 at 11:30:59PM -0500, Christopher Browne wrote:
> 
> A "faster" method would be to use one of the replication systems, such
> as ERserv.
> 
> You have your existing database, running 7.3.4, and set up another DB
> instance (perhaps on the same box) running 7.4.1.

Note that adding replication to an existing, non-replicated database
is some amount of work and imposes some overhead on your system.

> All that being said, ERserv may not cope perfectly with BLOBs, so you
> may need to do something special about that.

It copes very badly with them, in fact, as its memory is limited by
the JVM limits.

The strategy is otherwise sound, though, and has even been used by,
uh, some of us.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The plural of anecdote is not data.
		--Roger Brinner



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


end of thread, other threads:[~2004-02-12 22:06 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-08 02:35 reduce downtime when upgrading 7.3 -> 7.4 Palle Girgensohn <girgen@pingpong.net>
2004-02-08 04:30 ` Christopher Browne <cbbrowne@acm.org>
2004-02-12 22:06   ` Andrew Sullivan <ajs@crankycanuck.ca>
2004-02-08 05:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-10 14:33 ` Robert Treat <xzilla@users.sourceforge.net>
2004-02-10 16:24   ` Palle Girgensohn <girgen@pingpong.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