Postgres Pro
Downloads
Home   >   mailing lists

Thread: db "hot switch"

db "hot switch"

From
Pablo Varasa
Date:
    Hi all,

    I want to actualize my postgresql version stopping the server
the minimum time possible. I was thinking in turn the database to
read-only state (selects will work normally), make a dump, restore the
dump in the new installation and stop the server only the time needed to
stop old postmaster and load the new one. The problem is that i don't
know how to set readonly state. In mysql for example i left only select
privilege on all users (first i backup users), but in pgsql i can't
revoke or grant privileges to all users on *all databases* easily and i
don't know how to backup only users without a pg_dumpall.

    ¿Any suggestions? Thnx in advance

 ----------------------------
 Pablo Varasa Paredes
 Dept. de sistemas
 Prisacom - Grupo Prisa
 mailto:pvarasa@prisacom.com
 C/ Ribera del Sena s/n 28042 MADRID

Re: db "hot switch"

From
Andreas Rust
Date:
At 12:39 14.02.03, you wrote:
>         Hi all,
>
>         I want to actualize my postgresql version stopping the server
>the minimum time possible. I was thinking in turn the database to
>read-only state (selects will work normally), make a dump, restore the
>dump in the new installation and stop the server only the time needed to
>stop old postmaster and load the new one. The problem is that i don't
>know how to set readonly state. In mysql for example i left only select
>privilege on all users (first i backup users), but in pgsql i can't
>revoke or grant privileges to all users on *all databases* easily and i
>don't know how to backup only users without a pg_dumpall.
>
>         ¿Any suggestions? Thnx in advance

I recommend you compile your new postgres version with a different prefix path,
run the newly compiled postmaster on a port other than 5432 or even only
locally.
(with a different socket).

Whenever you got the new postmaster up and running just restore the dump you
create from your original postgres (do that dump after you saw your new
postmaster is running fine).
As soon as your postgres restore is complete, take down the old postmaster
and run the new one.

l8r


     Andreas Rust     -   webnova GmbH
     rust@webnova.de  -   www.webnova.de
     Tel:  +49 (0)234 - 912 96 10
     Fax:  +49 (0)234 - 912 96 15
+:----------------------------------------------------------:+
       Internet Solutions & Creative Design

Re: db "hot switch"

From
Pablo Varasa
Date:
    Hi! Thnx for answer!

    The problem in your advice is that the time between i make the
dump and i stop the old server, the inserts, updates, etc. will be lost
:(. And if i stop immediatly after make dump i have to wait all the time
while i am restoring.... I have to find a way to set de database in
readonly while after make the dump in old server.

    Thnx!

 ----------------------------
 Pablo Varasa Paredes
 Dept. de sistemas
 Prisacom - Grupo Prisa
 mailto:pvarasa@prisacom.com
 C/ Ribera del Sena s/n 28042 MADRID


On Fri, 14 Feb 2003 13:22:11 +0100
Andreas Rust <rust@webnova.de> wrote:

> At 12:39 14.02.03, you wrote:
> >         Hi all,
> >
> >         I want to actualize my postgresql version stopping the server
> >the minimum time possible. I was thinking in turn the database to
> >read-only state (selects will work normally), make a dump, restore the
> >dump in the new installation and stop the server only the time needed to
> >stop old postmaster and load the new one. The problem is that i don't
> >know how to set readonly state. In mysql for example i left only select
> >privilege on all users (first i backup users), but in pgsql i can't
> >revoke or grant privileges to all users on *all databases* easily and i
> >don't know how to backup only users without a pg_dumpall.
> >
> >         ¿Any suggestions? Thnx in advance
>
> I recommend you compile your new postgres version with a different prefix path,
> run the newly compiled postmaster on a port other than 5432 or even only
> locally.
> (with a different socket).
>
> Whenever you got the new postmaster up and running just restore the dump you
> create from your original postgres (do that dump after you saw your new
> postmaster is running fine).
> As soon as your postgres restore is complete, take down the old postmaster
> and run the new one.
>
> l8r
>
>
>      Andreas Rust     -   webnova GmbH
>      rust@webnova.de  -   www.webnova.de
>      Tel:  +49 (0)234 - 912 96 10
>      Fax:  +49 (0)234 - 912 96 15
> +:----------------------------------------------------------:+
>        Internet Solutions & Creative Design
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster