Обсуждение: Installing multiple instances of Postgred on one FreeBSD server

On Wed, May 01, 2013 at 01:58:21PM -0600, Benjamin Krajmalnik wrote:
> I need to migrate a production database from Postgres 9.0 to Postgres
> 9.2.
>
> Running FreeBSD 8.1/amd64, and presently running Postgres 9.0.4.
>
> Due to the nature of the application, I cannot have any considerable
> downtime, so the pg_dump and install new version route is not an option.
>
> I would like to install PG 9.2 on the same server and use Slony to
> replicate the databases, and once everything is up take the 9.0 cluster
> down.
>
> Ideally, I would like to do this from the ports.
>
> Any suggestions will be deeply appreciated.
>

Hi Benjamin,

Using Slony will definitely work. We have used it many times for this
type of upgrade. Since you are on the same box, have you considered using
pg_upgrade?

Regards,
Ken

This box is one which has a cluster as a hot standby via streaming
replication, so I need to keep it alive because it is used as a
read-only cluster.
The intent is to replicate it to 9.2 via Slony and then make it the
master while we upgrade the other server, which will then become the
slave.
I need to run the process in a lab environment first, of course, but the
problem right now is how to get the 2 clusters running on the same
server, preferably using the ports system.


-----Original Message-----
From: ktm@rice.edu [mailto:ktm@rice.edu]
Sent: Wednesday, May 01, 2013 2:09 PM
To: Benjamin Krajmalnik
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Installing multiple instances of Postgred on one
FreeBSD server

On Wed, May 01, 2013 at 01:58:21PM -0600, Benjamin Krajmalnik wrote:
> I need to migrate a production database from Postgres 9.0 to Postgres
> 9.2.
>
> Running FreeBSD 8.1/amd64, and presently running Postgres 9.0.4.
>
> Due to the nature of the application, I cannot have any considerable
> downtime, so the pg_dump and install new version route is not an
option.
>
> I would like to install PG 9.2 on the same server and use Slony to
> replicate the databases, and once everything is up take the 9.0
> cluster down.
>
> Ideally, I would like to do this from the ports.
>
> Any suggestions will be deeply appreciated.
>

Hi Benjamin,

Using Slony will definitely work. We have used it many times for this
type of upgrade. Since you are on the same box, have you considered
using pg_upgrade?

Regards,
Ken

On Wed, May 01, 2013 at 01:58:21PM -0600, Benjamin Krajmalnik wrote:
> I would like to install PG 9.2 on the same server and use Slony to
> replicate the databases, and once everything is up take the 9.0 cluster
> down.

allocate a new ip to the server.
reconfigure/restart 9.0 to listen on the main IP (not "all" as default)

use ezjail to create a freebsd jail on thenew IP

install 9.2 in the jail

treat it them as two servers.

point clients at new ip.

once migrated, delete all 9.0 from the main server.

if you need to upgrade again, add another jail.

stop and delete the old jail whe the next upgrade is complete.

repeat as often as you want to upgrade.

i'm pretty sure this can be adapted to linux, but i forget what their jail equivilent is.

the overhead is minimal



--
Jim Mercer     Reptilian Research      jim@reptiles.org    +1 416 410-5633
"He who dies with the most toys is nonetheless dead"

So essentially we would have to run jails on this - does that affect
performance?


-----Original Message-----
From: Jim Mercer [mailto:jim@reptiles.org]
Sent: Wednesday, May 01, 2013 2:20 PM
To: Benjamin Krajmalnik
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Installing multiple instances of Postgred on one
FreeBSD server

On Wed, May 01, 2013 at 01:58:21PM -0600, Benjamin Krajmalnik wrote:
> I would like to install PG 9.2 on the same server and use Slony to
> replicate the databases, and once everything is up take the 9.0
> cluster down.

allocate a new ip to the server.
reconfigure/restart 9.0 to listen on the main IP (not "all" as default)

use ezjail to create a freebsd jail on thenew IP

install 9.2 in the jail

treat it them as two servers.

point clients at new ip.

once migrated, delete all 9.0 from the main server.

if you need to upgrade again, add another jail.

stop and delete the old jail whe the next upgrade is complete.

repeat as often as you want to upgrade.

i'm pretty sure this can be adapted to linux, but i forget what their
jail equivilent is.

the overhead is minimal



--
Jim Mercer     Reptilian Research      jim@reptiles.org    +1 416
410-5633
"He who dies with the most toys is nonetheless dead"

On Wed, May 01, 2013 at 02:21:44PM -0600, Benjamin Krajmalnik wrote:
> So essentially we would have to run jails on this - does that affect
> performance?

i would say minimal impact.

>
>
> -----Original Message-----
> From: Jim Mercer [mailto:jim@reptiles.org]
> Sent: Wednesday, May 01, 2013 2:20 PM
> To: Benjamin Krajmalnik
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Installing multiple instances of Postgred on one
> FreeBSD server
>
> On Wed, May 01, 2013 at 01:58:21PM -0600, Benjamin Krajmalnik wrote:
> > I would like to install PG 9.2 on the same server and use Slony to
> > replicate the databases, and once everything is up take the 9.0
> > cluster down.
>
> allocate a new ip to the server.
> reconfigure/restart 9.0 to listen on the main IP (not "all" as default)
>
> use ezjail to create a freebsd jail on thenew IP
>
> install 9.2 in the jail
>
> treat it them as two servers.
>
> point clients at new ip.
>
> once migrated, delete all 9.0 from the main server.
>
> if you need to upgrade again, add another jail.
>
> stop and delete the old jail whe the next upgrade is complete.
>
> repeat as often as you want to upgrade.
>
> i'm pretty sure this can be adapted to linux, but i forget what their
> jail equivilent is.
>
> the overhead is minimal
>
>
>
> --
> Jim Mercer     Reptilian Research      jim@reptiles.org    +1 416
> 410-5633
> "He who dies with the most toys is nonetheless dead"

--
Jim Mercer     Reptilian Research      jim@reptiles.org    +1 416 410-5633
"He who dies with the most toys is nonetheless dead"

On Wed, May 01, 2013 at 03:40:31PM -0600, Benjamin Krajmalnik wrote:
> The problem, as I see it without using jails, is that the binaries are going to be installed in the same location as
the9.0 - unless I am not seeing something properly. 

you can probably set destdir or something in ports to point at /usr/local2 or something, but you will need to be
carefulthat the configs done conflict for listen ip, port and sockets. 

the jail solution is quite clean, and allows for a straight up ports install/config.

sorry for the lack of formatting, using tiny keyboard.

--jim

>
>
>
>
>
> From: Gezeala M. Bacu?o II [mailto:gezeala@gmail.com]
> Sent: Wednesday, May 01, 2013 3:38 PM
> To: Jim Mercer
> Cc: Benjamin Krajmalnik; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Installing multiple instances of Postgred on one FreeBSD server
>
>
>
> I used to do the ezjail route before but for ease, you can modify the setting "unix_socket_directory" on your
postgresql.confand have it point to another folder (default is /tmp) to have more than 1 instance listen on port 5432.
Ofcourse you need to add an alias IP for each instance and add that IP on your postgresql.conf "listen_addresses". 
>
>
>
>
> --
>
> regards
>
> gezeala bacu?o II
>
>
>
> On Wed, May 1, 2013 at 1:24 PM, Jim Mercer <jim@reptiles.org> wrote:
>
> On Wed, May 01, 2013 at 02:21:44PM -0600, Benjamin Krajmalnik wrote:
> > So essentially we would have to run jails on this - does that affect
> > performance?
>
> i would say minimal impact.
>
>
> >
> >
> > -----Original Message-----
> > From: Jim Mercer [mailto:jim@reptiles.org]
> > Sent: Wednesday, May 01, 2013 2:20 PM
> > To: Benjamin Krajmalnik
> > Cc: pgsql-admin@postgresql.org
> > Subject: Re: [ADMIN] Installing multiple instances of Postgred on one
> > FreeBSD server
> >
> > On Wed, May 01, 2013 at 01:58:21PM -0600, Benjamin Krajmalnik wrote:
> > > I would like to install PG 9.2 on the same server and use Slony to
> > > replicate the databases, and once everything is up take the 9.0
> > > cluster down.
> >
> > allocate a new ip to the server.
> > reconfigure/restart 9.0 to listen on the main IP (not "all" as default)
> >
> > use ezjail to create a freebsd jail on thenew IP
> >
> > install 9.2 in the jail
> >
> > treat it them as two servers.
> >
> > point clients at new ip.
> >
> > once migrated, delete all 9.0 from the main server.
> >
> > if you need to upgrade again, add another jail.
> >
> > stop and delete the old jail whe the next upgrade is complete.
> >
> > repeat as often as you want to upgrade.
> >
> > i'm pretty sure this can be adapted to linux, but i forget what their
> > jail equivilent is.
> >
> > the overhead is minimal
> >
> >
> >
> > --
> > Jim Mercer     Reptilian Research      jim@reptiles.org    +1 416
> > 410-5633
> > "He who dies with the most toys is nonetheless dead"
>
> --
> Jim Mercer     Reptilian Research      jim@reptiles.org    +1 416 410-5633 <tel:%2B1%20416%20410-5633>
> "He who dies with the most toys is nonetheless dead"
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
>

--
Jim Mercer     Reptilian Research      jim@reptiles.org    +1 416 410-5633
"He who dies with the most toys is nonetheless dead"

On Thu, May 02, 2013 at 10:10:51AM +0300, Achilleas Mantzios wrote:
> Hi, i was just lurking around,
> being previously interested in running pgsql in jail environment (must have been some years back)
> i noticed that jails had issues with running many instances of postmaster listening on the same port
> and with the same user (say e.g. postgres). So basically having cloned jails running pgsql was not
> out of the box possible without tweaking either user or port.
> Being many years out of freebsd admin, has this been remedied by now?

yeah, those issues are no longer

the key thing is to have things running on the base machine set to listen
on a specific IP, rather than all interfaces, as many applications do by
default.

so, for instance, for ssh on the base system, you would do:

sshd_enable="YES"
sshd_flags="-o ListenAddress=120.121.122.123"

this will prevent it from listening for ssh on the ip you intend to use for
the jail.

and in the jail, you can leave it listen on all interfaces, since, in the
jail, it only has its own interface.

in the case of pgsql, the best way to do it is to not have pgsql installed
on the base server, and only run it in a jail (or a couple jails, if you are
looking to smooth the upgrade from one to the next).

if you want to run pgsql on the base server, then set:
listen_addresses = '120.121.122.123'
in postgresql.conf, and there will be no conflict of listeners.

it issue of users/uids is moot, since the jail is independent of the main system.

assuming you configure the base system to listen only on its own IP, installing
on the jail, is no different than installing on a normal system.

--jim

>
> On ?????? 01 ?????? 2013 16:24:01 Jim Mercer wrote:
> > On Wed, May 01, 2013 at 02:21:44PM -0600, Benjamin Krajmalnik wrote:
> > > So essentially we would have to run jails on this - does that affect
> > > performance?
> >
> > i would say minimal impact.
> >
> > >
> > >
> > > -----Original Message-----
> > > From: Jim Mercer [mailto:jim@reptiles.org]
> > > Sent: Wednesday, May 01, 2013 2:20 PM
> > > To: Benjamin Krajmalnik
> > > Cc: pgsql-admin@postgresql.org
> > > Subject: Re: [ADMIN] Installing multiple instances of Postgred on one
> > > FreeBSD server
> > >
> > > On Wed, May 01, 2013 at 01:58:21PM -0600, Benjamin Krajmalnik wrote:
> > > > I would like to install PG 9.2 on the same server and use Slony to
> > > > replicate the databases, and once everything is up take the 9.0
> > > > cluster down.
> > >
> > > allocate a new ip to the server.
> > > reconfigure/restart 9.0 to listen on the main IP (not "all" as default)
> > >
> > > use ezjail to create a freebsd jail on thenew IP
> > >
> > > install 9.2 in the jail
> > >
> > > treat it them as two servers.
> > >
> > > point clients at new ip.
> > >
> > > once migrated, delete all 9.0 from the main server.
> > >
> > > if you need to upgrade again, add another jail.
> > >
> > > stop and delete the old jail whe the next upgrade is complete.
> > >
> > > repeat as often as you want to upgrade.
> > >
> > > i'm pretty sure this can be adapted to linux, but i forget what their
> > > jail equivilent is.
> > >
> > > the overhead is minimal
> > >
> > >
> > >
> >
> >
> -
> Achilleas Mantzios
> IT DEV
> IT DEPT

--
Jim Mercer     Reptilian Research      jim@reptiles.org    +1 416 410-5633
"He who dies with the most toys is nonetheless dead"

Pretty informative Jim, thanx.

Do you remember which version, when, by who, were these shortcomings (different user, different port) resolved?
At the time it seemed like a very hard thing to implement in the current status of jails.
So, to rephrase, did it take a major jail re-write to achieve the above? or was it more on the hack-side?

On Ðåì 02 ÌáÀ 2013 08:58:49 Jim Mercer wrote:
> On Thu, May 02, 2013 at 10:10:51AM +0300, Achilleas Mantzios wrote:
> > Hi, i was just lurking around,
> > being previously interested in running pgsql in jail environment (must have been some years back)
> > i noticed that jails had issues with running many instances of postmaster listening on the same port
> > and with the same user (say e.g. postgres). So basically having cloned jails running pgsql was not
> > out of the box possible without tweaking either user or port.
> > Being many years out of freebsd admin, has this been remedied by now?
>
> yeah, those issues are no longer
>
> the key thing is to have things running on the base machine set to listen
> on a specific IP, rather than all interfaces, as many applications do by
> default.
>
> so, for instance, for ssh on the base system, you would do:
>
> sshd_enable="YES"
> sshd_flags="-o ListenAddress=120.121.122.123"
>
> this will prevent it from listening for ssh on the ip you intend to use for
> the jail.
>
> and in the jail, you can leave it listen on all interfaces, since, in the
> jail, it only has its own interface.
>
> in the case of pgsql, the best way to do it is to not have pgsql installed
> on the base server, and only run it in a jail (or a couple jails, if you are
> looking to smooth the upgrade from one to the next).
>
> if you want to run pgsql on the base server, then set:
> listen_addresses = '120.121.122.123'
> in postgresql.conf, and there will be no conflict of listeners.
>
> it issue of users/uids is moot, since the jail is independent of the main system.
>
> assuming you configure the base system to listen only on its own IP, installing
> on the jail, is no different than installing on a normal system.
>
> --jim
>
> >
> > On ?????? 01 ?????? 2013 16:24:01 Jim Mercer wrote:
> > > On Wed, May 01, 2013 at 02:21:44PM -0600, Benjamin Krajmalnik wrote:
> > > > So essentially we would have to run jails on this - does that affect
> > > > performance?
> > >
> > > i would say minimal impact.
> > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: Jim Mercer [mailto:jim@reptiles.org]
> > > > Sent: Wednesday, May 01, 2013 2:20 PM
> > > > To: Benjamin Krajmalnik
> > > > Cc: pgsql-admin@postgresql.org
> > > > Subject: Re: [ADMIN] Installing multiple instances of Postgred on one
> > > > FreeBSD server
> > > >
> > > > On Wed, May 01, 2013 at 01:58:21PM -0600, Benjamin Krajmalnik wrote:
> > > > > I would like to install PG 9.2 on the same server and use Slony to
> > > > > replicate the databases, and once everything is up take the 9.0
> > > > > cluster down.
> > > >
> > > > allocate a new ip to the server.
> > > > reconfigure/restart 9.0 to listen on the main IP (not "all" as default)
> > > >
> > > > use ezjail to create a freebsd jail on thenew IP
> > > >
> > > > install 9.2 in the jail
> > > >
> > > > treat it them as two servers.
> > > >
> > > > point clients at new ip.
> > > >
> > > > once migrated, delete all 9.0 from the main server.
> > > >
> > > > if you need to upgrade again, add another jail.
> > > >
> > > > stop and delete the old jail whe the next upgrade is complete.
> > > >
> > > > repeat as often as you want to upgrade.
> > > >
> > > > i'm pretty sure this can be adapted to linux, but i forget what their
> > > > jail equivilent is.
> > > >
> > > > the overhead is minimal
> > > >
> > > >
> > > >
> > >
> > >
> > -
> > Achilleas Mantzios
> > IT DEV
> > IT DEPT
>
>
-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

On Wed, May 08, 2013 at 10:03:30AM +0300, Achilleas Mantzios wrote:
> Pretty informative Jim, thanx.
>
> Do you remember which version, when, by who, were these shortcomings
> (different user, different port) resolved?
> At the time it seemed like a very hard thing to implement in the current
> status of jails.
> So, to rephrase, did it take a major jail re-write to achieve the above?
> or was it more on the hack-side?

i didn't keep up with the release notes, but i think the first few rollouts
of jails in FreeBSD were on the experimental side.

it certainly took a few tweaks and hacks to make it robust.

however, that was years ago.

jails have evolved into a well thought out virtual kernel environment.

ah, here you go, some stuff about setting up pgsql in a FreeBSD jail:
http://forums.freebsd.org/showthread.php?t=23114

--
Jim Mercer     Reptilian Research      jim@reptiles.org    +1 416 410-5633
"He who dies with the most toys is nonetheless dead"

On Ôåô 08 ÌáÀ 2013 08:28:31 Jim Mercer wrote:
> On Wed, May 08, 2013 at 10:03:30AM +0300, Achilleas Mantzios wrote:
> > Pretty informative Jim, thanx.
> >
> > Do you remember which version, when, by who, were these shortcomings
> > (different user, different port) resolved?
> > At the time it seemed like a very hard thing to implement in the current
> > status of jails.
> > So, to rephrase, did it take a major jail re-write to achieve the above?
> > or was it more on the hack-side?
>
> i didn't keep up with the release notes, but i think the first few rollouts
> of jails in FreeBSD were on the experimental side.
>
> it certainly took a few tweaks and hacks to make it robust.
>
> however, that was years ago.
>
> jails have evolved into a well thought out virtual kernel environment.
>
> ah, here you go, some stuff about setting up pgsql in a FreeBSD jail:
> http://forums.freebsd.org/showthread.php?t=23114

Thanx,
1st the above links do not reveal anything new.
2nd, judging by the wiki : https://wiki.freebsd.org/Jails ,
jailified SysV IPC (or SysV IPC-ified jails) (the specific territory where pgsql comes in contact with jails)
are in stalled state.

Latest work i recall was for this docs.freevps.com/doku.php?id=freebsd:changelog project
and i remember that getting this through was something not easy to do.

That's why i asked about any milestones in FreeBSD jail implementation.
It just does not seem normal, all of a sudden for pgsql to work that nice in FreeBSD jails,
there have been also some rather "warm" discussions here in these very lists.

>
>
-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt

On Wed, May 08, 2013 at 05:29:53PM +0300, Achilleas Mantzios wrote:
> 1st the above links do not reveal anything new.
> 2nd, judging by the wiki : https://wiki.freebsd.org/Jails ,
> jailified SysV IPC (or SysV IPC-ified jails) (the specific territory where pgsql comes in contact with jails)
> are in stalled state.

ah, hrm.

to be honest, i was making some assumptions, and now that i think of it, yeah
the shared memory stuff is the likely cause of problems.

i was pretty sure that i was running pgsql in jails, and i looked at the
servers i set up that way, only to find that i had build the server on
the main system, and ran pgsql-client inside the jails, without issues.

i suppose i should have fired up pgsql server in one of my jails to be sure.

apologies to anyone i sent off on a goose chase.

> Latest work i recall was for this docs.freevps.com/doku.php?id=freebsd:changelog project
> and i remember that getting this through was something not easy to do.
>
> That's why i asked about any milestones in FreeBSD jail implementation.
> It just does not seem normal, all of a sudden for pgsql to work that nice in FreeBSD jails,
> there have been also some rather "warm" discussions here in these very lists.

i'm relatively new to the pgsql lists (although i've been using pgsql since
at least the mid-90's).

--jim

--
Jim Mercer     Reptilian Research      jim@reptiles.org    +1 416 410-5633
"He who dies with the most toys is nonetheless dead"

On May 8, 2013, at 11:32 AM, Jim Mercer wrote:

> On Wed, May 08, 2013 at 05:29:53PM +0300, Achilleas Mantzios wrote:
>> 1st the above links do not reveal anything new.
>> 2nd, judging by the wiki : https://wiki.freebsd.org/Jails ,
>> jailified SysV IPC (or SysV IPC-ified jails) (the specific territory where pgsql comes in contact with jails)
>> are in stalled state.
>
> ah, hrm.
>
> to be honest, i was making some assumptions, and now that i think of it, yeah
> the shared memory stuff is the likely cause of problems.

Use a different UID for the pgsql user in each jail if you run more than one.

> i was pretty sure that i was running pgsql in jails, and i looked at the
> servers i set up that way, only to find that i had build the server on
> the main system, and ran pgsql-client inside the jails, without issues.

We've been running multiple jails each with PG inside the jail for years, I
don't recall anything terribly special there aside from:

-ensure that PG in each jail runs under a unique UID
-ensure the sysctls for shared mem stuff are high enough to handle multiple
 running PG instances
-ensure that you have set jail_sysvipc_allow="YES" in rc.conf

Trimming the rest after that top-posting discussion. :)

Charles