Обсуждение: Deploy postgres - upgrade strategy

Sean Z. wrote:
> Hi,
>
> I need your kind advice on postgres deployment strategy.  My application
> is developed on 8.2.4 and need to be deployed to Windows server 2003.
> Clients may /may not have postgres installed.  How can I deploy postgres
> silently on client machine?  Specifically:
>
> 1. How to determine if postgres is installed on Win2K3, and what's its
> version?

You can probe the port if you want to catch any kind. If you're only
interested in official MSI installs, you can check the registry (there's
a key under HKLM\Software\PostgreSQL IIRC)


> 2. If there is an older version (e.g., 8.1) installed, can I install a
> new version (8.2.4) without touching the existing installation?

Yes.


> 3. If there is an older version of the same version line (e.g., 8.2.3)
> installed, is it safe for me to upgrade it by running upgrade.bat,
> believing that it won't break existing application using the old version?

Normally, yes. You might want to check the release notes if there is any
incompatibility, but we usually do whatever possible to avoid those
between minor releases.


> 4. If there is a newer version installed (e.g. 8.3), can I still install
> 8.2.4 as a separate installation without touching the existing installation?

Yes. You can always have the different major versions on the same
system. You just can't have >1 different *minor* version on the same
system if you use the official MSI - if you install it manually, there
is no restriction at all.


> 5. If there is a newer version of the same version line (e.g. 8.2.5)
> installed, shall I simply use it? (same as 3 I guess)

Probably. Depends on if you want your application to be entirelyi
independent or not.


//Magnus

On Dec 19, 2007 11:12 AM, Sean Z. <sean09182006@yahoo.com> wrote:
>
> Hi,
>
> I need your kind advice on postgres deployment strategy.  My application is
> developed on 8.2.4 and need to be deployed to Windows server 2003. Clients
> may /may not have postgres installed.  How can I deploy postgres silently on
> client machine?  Specifically:
>
> 3. If there is an older version of the same version line (e.g., 8.2.3)
> installed, is it safe for me to upgrade it by running upgrade.bat, believing
> that it won't break existing application using the old version?

Under no circumstances should you simply do it.  You should ask the
user if it's ok to do it.  If I had a machine running a specific
version because that's what I'd tested and was deployed in other
places, and it got updated by some other app being installed I would
not be happy.

> 4. If there is a newer version installed (e.g. 8.3), can I still install
> 8.2.4 as a separate installation without touching the existing installation?

That may be your best bet if you're looking to use postgresql and your
app together as a kind of "bolted together" application.

I'd say that you should ask the user up front if they have another
postgresql server (it might not be on the same machine you're
installing your app on, btw) they want to use, or if they'd like you
to install a postgresql instance just for your application on the
local machine.  Then make all decisions based on that one question.

On Dec 19, 2007 1:55 PM, Sean Z. <sean09182006@yahoo.com> wrote:
>
> Yes, "bolted together" application is something I am thinking about.  It
> will satisfied my application's requirement and it won't affect existing
> applications.
>
> So is it possible to install my postgres server (say 8.2.4) regardless of
> whether there is a postgres server of any version existing on the same
> windows box?

Yes it is.  Take a look at hyperic, they do that.  Note that if you
are gonna include a version of postgresql with your application, you
should make it an option, as sometimes folks would rather use their
one big pgsql server for such things than having yet another odd copy
running around.  Hyperic makes it very easy to switch from using their
pgsql to your own.  They install their copy on port 9432 instead of
5432, and all you have to do it edit the jdbc connect line to point at
your own pg server and create a db and user to match hyperic's and
you're done.

Sean Z. wrote:
> BTW, what's the best way to determine the installation folder, port
> number used by an existing postgres server? From registry
> HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations ?

Yes. From a related doc I once wrote:

When PostgreSQL is installed on Windows it adds a number of entries to
the registry to allow pgAdmin and other applications to discover the
installation and make use of it automatically. This system is being used
further by the application stack builder project that I've been working on.

The following registry keys are added:

HKLM\Software\PostgreSQL\Installations\<Product Code>\Base Directory
HKLM\Software\PostgreSQL\Installations\<Product Code>\Data Directory
HKLM\Software\PostgreSQL\Installations\<Product Code>\Service ID
HKLM\Software\PostgreSQL\Installations\<Product Code>\Version

HKLM\Software\PostgreSQL\Services\<Service ID>\Data Directory
HKLM\Software\PostgreSQL\Services\<Service ID>\Database Superuser
HKLM\Software\PostgreSQL\Services\<Service ID>\Display Name
HKLM\Software\PostgreSQL\Services\<Service ID>\Encoding
HKLM\Software\PostgreSQL\Services\<Service ID>\Locale
HKLM\Software\PostgreSQL\Services\<Service ID>\Port
HKLM\Software\PostgreSQL\Services\<Service ID>\Product Code
HKLM\Software\PostgreSQL\Services\<Service ID>\Service Account

Where:

Product Code is the product code GUID used in the installer package.
This changes between major versions (ie. 8.1 -> 8.2).

Base directory is the base installation directory, eg. C:\PostgreSQL\8.1

Data directory is the data directory, eg. C:\PostgreSQL\8.1\data

Service ID is the identifier of the pg_ctl registration with the service
control manager, eg. pgsql-8.1

Version is the major.minor version, e.g. 8.1

Database superuser is the name of the initial superuser in the cluster,
eg. postgres

Display name is the friendly name of the pg_ctl registration with the
service control manager, eg. PostgreSQL Database Server (8.1)

Encoding is the database encoding set at initdb, eg. LATIN1

Locale is the server locale set at installation, eg. English_United Kingdom

Port is a DWORD value representing the port the server listens on, eg. 5432

Service account is the Windows user account that the pg_ctl service runs
under, eg. MYPC\postgres

The values are split into two sets of keys (with some duplication) to
allow easy cross-referencing from service to installation and vice
versa. Obviously <Product Code> and <Service ID> in the key paths should
be replaced with the actual values.


Regards, Dave.

----- Original Message -----

From: Sean Z.

To: Scott Marlowe

Cc: pgsql-general@postgresql.org

Sent: Thursday, December 20, 2007 3:00 PM

Subject: Re: [GENERAL] Deploy postgres - upgrade strategy

BTW, what's the best way to determine the installation folder, port number used by an existing postgres server? From registry HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations ?

----- Original Message ----
Wrom: TFJMVRESKPNKMBIPBARHDMN href="mailto:scott.marlowe@gmail.com">scott.marlowe@gmail.com>
To: Sean Z. <sean09182006@yahoo.com>
Cc: pgsql-general@postgresql.org
Sent: Wednesday, December 19, 2007 1:04:19 PM
Subject: Re: [GENERAL] Deploy postgres - upgrade strategy

On Dec 19, 2007 11:12 AM, Sean Z. <sean09182006@yahoo.com> wrote:
>
> Hi,
>
> I need your kind advice on postgres deployment strategy.  My application is
> developed on 8.2.4 and need to be deployed to Windows server 2003. Clients
> may /may not have postgres installed.  How can I deploy postgres silently on
> client machine?  Specifically:
>
> 3. If there is an older version of the same version line (e.g., 8.2.3)
> installed, is it safe for me to upgrade it by running upgrade.bat, believing
> that it won't break existing application using the old version?

Under no circumstances should you simply do it.  You should ask the
user if it's ok to do it.  If I had a machine running a specific
version because that's what I'd tested and was deployed in other
places, and it got updated by some other app being installed I would
not be happy.

> 4. If there is a newer version installed (e.g. 8.3), can I still install
> 8.2.4 as a separate installation without touching the existing installation?

That may be your best bet if you're looking to use postgresql and your
app together as a kind of "bolted together" application.

I'd say that you should ask the user up front if they have another
postgresql server (it might not be on the same machine you're
installing your app on, btw) they want to use, or if they'd like you
to install a postgresql instance just for your application on the
local machine.  Then make all decisions based on that one question.

---

Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.

Harald Armin Massa wrote:
>     HKLM\Software\PostgreSQL\Services\<Service ID>\Service Account
>
>     Where:
>
>     [...]
>     Service account is the Windows user account that the pg_ctl service runs
>     under, eg. MYPC\postgres
>
>
> I guess that would be "service account that was assigned to the postgres
> service during installation", and that that key does NOT get updated on
> changes via services.msc, or?

Yes, thats true. The values are those selected at install time. If
you've changed them yourself, that data may be out of date.

/D

On Dec 20, 2007 2:04 PM, Sean Z. <sean09182006@yahoo.com> wrote:
>
> Sorry I am not familiar with hyperic.  Do you mean I can use hyperic to do
> the job, or do you mean I can take the same approach as hyperic?

I mean you can take the same approach as them.