Обсуждение: Upgrade to new version

Hello all!

I would like to ask  what would be the best procedure to upgrade to a
new version of PostgreSQL in a production system when there is a 'initdb'
necessary?

Situation:
We are running PostgreSQL in a production system. There are about 1000
selects and about 100 inserts per minute.


To upgrade to PostgreSQL 7.2.3 there is an 'initdb' necessary.

The straight forward approach is to shut down the PostgreSQL server
and restore the saved databases.
But, this approach is not possible for us.

We need a "smooth" switch over with a downtime of maximum 5 minutes.

It is possible to set up an other filesystem and another PostgreSQL server
with another port number to initdb the database and restore the databases
and tables backuped at a given time.
But meanwhile some new inserts (well a lot of them) did happen in the existing
databases.
How should the missed inserts be transferred to the new PostgreSQL server?

What would you recommend for this switch?

Thank you for your discussion!
--
--
Mit freundlichen Gruessen / With best regards
    Reiner Dassing

I don't have any good news for you.  Yes, you can set up a server on a
different port and perform a dump and reload while both systems are up,
but as you say, you are going to miss inserts during the dump because
pg_dump takes a snapshot when it starts.

My only suggestion is to keep the old database SELECT-only during the
dump, or somehow gather the changes that happen after the dump and
manually move them to the new database.

---------------------------------------------------------------------------

Reiner Dassing wrote:
> Hello all!
>
> I would like to ask  what would be the best procedure to upgrade to a
> new version of PostgreSQL in a production system when there is a 'initdb'
> necessary?
>
> Situation:
> We are running PostgreSQL in a production system. There are about 1000
> selects and about 100 inserts per minute.
>
>
> To upgrade to PostgreSQL 7.2.3 there is an 'initdb' necessary.
>
> The straight forward approach is to shut down the PostgreSQL server
> and restore the saved databases.
> But, this approach is not possible for us.
>
> We need a "smooth" switch over with a downtime of maximum 5 minutes.
>
> It is possible to set up an other filesystem and another PostgreSQL server
> with another port number to initdb the database and restore the databases
> and tables backuped at a given time.
> But meanwhile some new inserts (well a lot of them) did happen in the existing
> databases.
> How should the missed inserts be transferred to the new PostgreSQL server?
>
> What would you recommend for this switch?
>
> Thank you for your discussion!
> --
> --
> Mit freundlichen Gruessen / With best regards
>     Reiner Dassing
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On 10 Oct 2002 at 9:46, Reiner Dassing wrote:

> To upgrade to PostgreSQL 7.2.3 there is an 'initdb' necessary.

This caused me to ask around off-list.  An initdb is required only if
you are upgrading from from pre-7.2.X.
--
Dan Langille
I'm looking for a computer job:
http://www.freebsddiary.org/dan_langille.php

On Thu, Oct 10, 2002 at 09:46:14AM +0200, Reiner Dassing wrote:
> Hello all!
>
> I would like to ask  what would be the best procedure to upgrade to a
> new version of PostgreSQL in a production system when there is a 'initdb'
> necessary?
>
> Situation:
> We are running PostgreSQL in a production system. There are about 1000
> selects and about 100 inserts per minute.

If you're upgrading to 7.2.3 from 7.2.2, no initdb is required.  But
anyway, here's a general strategy:

If you are using replication (and if not, this might be a good time
to start looking at it), you can use it to minimise downtime.  Here's
how:

1.    Add an additional replication target.  Put up the new back
end on another port, and start replicating into it.

2.    When you are totally caught up (i.e. replication is merely
sending in its most recent changes in each cycle), shut off the
applications.

3.    Reconfigure your client applications to use the new back end.

4.    Check that replication has really caught up.  Shut off the
old back end (for safety).

5.    Re-start your applications.

I've done this.  It works.  You still have to shut off for a brief
period, but it's minutes, and not hours.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110