Thread: 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.marlowe@ihs.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@infoplease.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.marlowe@ihs.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.