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
