Re: Upgrading from 9.1.2 to 9.1.5

Craig James <cjames@emolecules.com> wrote:
> Sergey Konoplev <gray.ru@gmail.com> wrote:
>> Bruce Momjian <bruce@momjian.us> wrote:
>>> On Thu, Sep  6, 2012 at 05:55:05PM -0500, Antoine Guidi wrote:
>>>> Is it possible to do a pg_upgrade from 9.1.2 to 9.1.5 just
>>>> using pg_upgrade?  For what I could read, the only exception
>>>> would be if I was using a citext column (which I am not).
>>>
>>> You cannot use pg_upgrade for this.

"Cannot" or "don't need to"?

>>> You just need to stop the server, install the binaries, and
>>> restart the server.
>>
>> AFAIU it is not necessary to stop the server when updating
>> binaries if one is not going to create extensions, PLs or
>> anything else that will be dynamically linked after the binaries
>> update and before the server restart.
>>
>> So with the process
>>
>> 1. update binaries
>> 2. postgres restart
>>
>> the downtime will be shorter.
>
> I'm just guessing, but this is probably a bad idea.  This could
> happen...
>
> 1. Postgres master and a bunch of clients are running
>
> 2. You start updating binaries
>
> 3. In the middle of your update, an new client connects and a new
> backend process starts.
>
> 4. The 9.1.2 executable links to the 9.1.5 binaries.  Or a 9.1.5
> executable links to the 9.1.2 libraries.  Or a 9.1.5 executable
> starts with the right binaries, but is talking to a 9.1.2
> postmaster process, which might not have the same shared-memory
> map.  Or ...
>
> ... and so forth.

That's why we put each minor release into a separate location.

1.  PostgreSQL master and a bunch of clients are running against
executables deployed with a prefix of /usr/local/pgsql-9.1.4.  The
prefix is specified in the service script for the server; clients
use a symlink at /usr/local/pgsql.

2.  We make and install a new build with prefix
/usr/local/pgsql-9.1.5.

3.  We change the symlink to point to the new build.

4.  We change the appropriate service script(s) to point to the new
prefix.

5.  We stop and then start the server(s).  (We don't use pg_ctl
restart because that seems to stay on the same prefix.)

6.  Later, when we confirm that nothing is still referencing the old
prefix, we remove its subdirectory.

PostgreSQL is down only for the time it takes for a restart.  We
normally do this during off-hours; but even if this is done during
normal operations, properly coded clients (which retry a database
transaction if it fails with a broken connection, without giving the
client any error) only see a short stutter in response time.

-Kevin