Re: Run 4 postgresql session on ONE server?

Hello,
I only know the traditional way of using postgreSQL:
In serverprocess with an "unlimited" number of databases.
But:
Is is possible to have 4 processes, for each database one, running on one
linux server?

The reason is the following:
I only have one server on which a productive database is working and a
second database, which will be set upped at the moment.
On my productive database I can't test with the parameters so I am searching
for a way, that I can do the following:
- let running for each database one serverprocess on the same machine
- having for each database one pg_hba.conf and postgresql.conf (on testdb's,
I want to log every SQL-statement, which is on a productive database
impossible to log)

I know, that you shouldn't test on a productive server, but at the moment
this is the only way I can do but I hope to get an test server or a new
productive server as soon as the second database is ready.

Thank you in advance for your answers

Daniel Seichter
--
postgreSQL on Netware - the red elephant
http://postgresql.dseichter.org
Last update: 26th May 2003

On Thu, 12 Jun 2003, Daniel Seichter wrote:

> Hello,
> I only know the traditional way of using postgreSQL:
> In serverprocess with an "unlimited" number of databases.
> But:
> Is is possible to have 4 processes, for each database one, running on one
> linux server?
>
> The reason is the following:
> I only have one server on which a productive database is working and a
> second database, which will be set upped at the moment.
> On my productive database I can't test with the parameters so I am searching
> for a way, that I can do the following:
> - let running for each database one serverprocess on the same machine
> - having for each database one pg_hba.conf and postgresql.conf (on testdb's,
> I want to log every SQL-statement, which is on a productive database
> impossible to log)
>
> I know, that you shouldn't test on a productive server, but at the moment
> this is the only way I can do but I hope to get an test server or a new
> productive server as soon as the second database is ready.

Yes, you can do it. All you have to do is create four seperate accounts
for it to run under (pgsql1, pgsql2, pgsql3, pgsql4) and then in each of
those accounts, set up a different PGDATA value and initdb as that user.
Then edit each account's postgresql.conf to have a different port number
(I just incremented from 5432 to 5433 etc...) and start them up.

Then when you connect just specify the port of the database instance you
need. Since each one is running as a different user with different
postgresql.conf and pg_hba.conf files you can lock the production instance
down tight to prevent brain farts (oops, I just dropped a table in the
production database) and you're gold.

"scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> writes:
> Yes, you can do it. All you have to do is create four seperate accounts
> for it to run under (pgsql1, pgsql2, pgsql3, pgsql4) and then in each of
> those accounts, set up a different PGDATA value and initdb as that user.
> Then edit each account's postgresql.conf to have a different port number
> (I just incremented from 5432 to 5433 etc...) and start them up.

You don't really need N users, unless you have more protection concerns
than you mentioned (like you want each database to have its own DBA with
no access to the other ones). A single "postgres" Unix userid can serve
for all the postmasters in typical cases. All you really need are a
separate data directory and a separate port number for each postmaster.

Pay attention though to the amount of machine resources you are
committing to each postmaster. You'd probably not want to push
shared_buffers up real far, for example.

regards, tom lane

Good morning,

> Yes, you can do it. All you have to do is create four seperate accounts
> for it to run under (pgsql1, pgsql2, pgsql3, pgsql4) and then in each of
> those accounts, set up a different PGDATA value and initdb as that user.
> Then edit each account's postgresql.conf to have a different port number
> (I just incremented from 5432 to 5433 etc...) and start them up.
Well, it looks easy *g*
I will do this today and hope, it work.

> Then when you connect just specify the port of the database instance you
> need. Since each one is running as a different user with different
> postgresql.conf and pg_hba.conf files you can lock the production instance
> down tight to prevent brain farts (oops, I just dropped a table in the
> production database) and you're gold.
Ok, at the moment , I only can say: thank you, and I had to test it during
this weekend to start at the next week with the configuration.

Daniel

On Sat, 2003-06-14 at 03:20, Daniel Seichter wrote:
> Good morning,
>
> > Yes, you can do it. All you have to do is create four seperate accounts
> > for it to run under (pgsql1, pgsql2, pgsql3, pgsql4) and then in each of
> > those accounts, set up a different PGDATA value and initdb as that user.
> > Then edit each account's postgresql.conf to have a different port number
> > (I just incremented from 5432 to 5433 etc...) and start them up.
> Well, it looks easy *g*
> I will do this today and hope, it work.
>
> > Then when you connect just specify the port of the database instance you
> > need. Since each one is running as a different user with different
> > postgresql.conf and pg_hba.conf files you can lock the production instance
> > down tight to prevent brain farts (oops, I just dropped a table in the
> > production database) and you're gold.
> Ok, at the moment , I only can say: thank you, and I had to test it during
> this weekend to start at the next week with the configuration.

BTW -- this is pretty easy with the RPMs (I think you said you were on
RedHat). Just make a symlink to the init script. Call it 'pg2'

Then put the port and pgdata definitions in /etc/sysconfig/pgsql/pg2

--
Karl DeBisschop <kdebisschop(at)infoplease(dot)com>
Director of Software Development, Infoplease/Pearson Education

Hello,

> BTW -- this is pretty easy with the RPMs (I think you said you were on
> RedHat). Just make a symlink to the init script. Call it 'pg2'
>
> Then put the port and pgdata definitions in /etc/sysconfig/pgsql/pg2
No, I am on SuSE but don't using the RPMs (maybe on RedHat it will be
better) because after ONE DAY of asking and configuring I was not able to
make the initdb to the /db directory where the database should stored in,
no, the SuSE RPMs are all linked to /var/...... and I don't want to use
symlinks to a database. To an config-file it is possible, but not to a
directory.
So I sit on my server and wait 15 minuten until postgreSQL is
compiled......*g*

Daniel Seichter
--
postgreSQL on Netware - the red elephant
http://postgresql.dseichter.org
Last update: 26th May 2003

Hello,

> BTW -- this is pretty easy with the RPMs (I think you said you were on

> RedHat). Just make a symlink to the init script. Call it 'pg2'

>

> Then put the port and pgdata definitions in /etc/sysconfig/pgsql/pg2

No, I am on SuSE but don't using the RPMs (maybe on RedHat it will be

better) because after ONE DAY of asking and configuring I was not able to

make the initdb to the /db directory where the database should stored in,

no, the SuSE RPMs are all linked to /var/...... and I don't want to use

symlinks to a database. To an config-file it is possible, but not to a

directory. So I sit on my server and wait 15 minuten until postgreSQL is

compiled......*g*

Daniel Seichter

--

postgreSQL on Netware - the red elephant

http://postgresql.dseichter.org

Last update: 26th May 2003

Hello,
> BTW -- this is pretty easy with the RPMs (I think you said you were on
> RedHat). Just make a symlink to the init script. Call it 'pg2'
>
> Then put the port and pgdata definitions in /etc/sysconfig/pgsql/pg2
No, I am on SuSE but don't using the RPMs (maybe on RedHat it will be
better) because after ONE DAY of asking and configuring I was not able to
make the initdb to the /db directory where the database should stored in,
no, the SuSE RPMs are all linked to /var/...... and I don't want to use
symlinks to a database. To an config-file it is possible, but not to a
directory. So I sit on my server and wait 15 minuten until postgreSQL is
compiled......*g*
Daniel Seichter
--
postgreSQL on Netware - the red elephant
http://postgresql.dseichter.org
Last update: 26th May 2003

On Fri, 13 Jun 2003, Tom Lane wrote:

> "scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> writes:
> > Yes, you can do it. All you have to do is create four seperate accounts
> > for it to run under (pgsql1, pgsql2, pgsql3, pgsql4) and then in each of
> > those accounts, set up a different PGDATA value and initdb as that user.
> > Then edit each account's postgresql.conf to have a different port number
> > (I just incremented from 5432 to 5433 etc...) and start them up.
>
> You don't really need N users, unless you have more protection concerns
> than you mentioned (like you want each database to have its own DBA with
> no access to the other ones). A single "postgres" Unix userid can serve
> for all the postmasters in typical cases. All you really need are a
> separate data directory and a separate port number for each postmaster.

The reason I always setup multiple users is so things like pg_ctl and all
still work the same, plus it lets you pawn off maintenance of the
"secondary" databases to other folks while keeping the main user account
for the real DBA.