Are Independent instances of Postgresql possible

Question

I want to install postgresql for use as the backend to a Windows application.

This seems to be no problem if postgresql is not already installed on the system.

If postgresql is already installed then unless the command line parameters contain the superpassword etc of the existing installation then the install fails. As I will likely never know the superpassword or other account details of any pre-existing postgresql instances and the machine owners may not either it seems that this will frustrate any attempt to install postgresql in such a situation.

I believe it is possible to install completely independent instances of sql server but is this possible for postgresql?

BTW: If the command line does contain the correct superpassword then the install just seems to overwrite the existing install and ignores parameters like --prefix etc . I used init db to create a new database cluster before doing a second install but this new cluster was ignored?

Answer 1

In general you can have multiple independent instances of PostgreSQL. Strictly speaking it's database cluster with separate:

• data directory
• configuration (e.g. postgresql.conf, pg_hba.conf)
• listening TCP/UDP port (default 5432+)
• owner user and superuser role
• locale and default encoding
• log file
• postmaster server process (on Windows postgres.exe)

Perfect well-done example is Debian with easy to use postgresql-common infrastructure (pg_ctlcluster, pg_lsclusters, pg_createcluster, pg_dropcluster, included SSL, log rotation and so on).

EDIT:

I found it's rather easy to install second, third, etc. instance of same versioned PostgreSQL under Windows with EnterpriseDB's installer, no need to use initdb and pg_ctl (assuming 64-bit installation, probably you need to use Program Files (x86) for 32-bit installation):

1. Open cmd with admin privileges (Run as Administrator)
2. Execute: cd "C:\Program Files\PostgreSQL\9.0\installer\server"
3. Create new database cluster (press Enter on every step): initcluster.vbs postgres postgres 12345 "C:\Program Files\PostgreSQL\9.0" "C:\Program Files\PostgreSQL\9.0\data2" 5433 DEFAULT
4. Register as Windows Service: startupcfg.vbs 9.0 postgres 12345 "C:\Program Files\PostgreSQL\9.0" "C:\Program Files\PostgreSQL\9.0\data2" postgresql-x64-9.0-2
5. Run newly created service postgresql-x64-9.0-2 using services.msc and you have second server

Change 12345 to your password specified during PostgreSQL installation. You don't have to use data2 directory, use whatever you like (but of course not existing data directory).

Answer 2

On Windows 7 I had success following these steps. You'll need the PsExec.exe utility available in the Sysinternals Suite. I assume here that the path to the Sysinternals Suite and the path to the bin folder of your existing PostgreSQL installation are in your PATH environment variable.

1. Open a cmd.exe window and enter the following command to open a prompt as the Network Service account.

   psexec -i -u "nt authority\network service" cmd.exe

2. The Network Service account won't have access to your PATH, so cd 'C:\PostgreSQL\9.3\bin' and then enter the following command to initialize a data directory for your new instance. I've called mine "data2". It doesn't have to be in the postgres directory, but that's where the default data directory goes, so it's a reasonable choice.

   initdb "C:\PostgreSQL\9.3\data2"

3. Edit C:\PostgreSQL\9.3\data2\postgresql.conf so that port = 5433 (the default instance uses 5432, and you shouldn't have two instances on the same port)

4. Leave the Network Service cmd prompt and in your standard prompt enter the following command to register the new service. Here I've named my new instance "pg_test"

   pg_ctl register -N pg_test -U "nt authority\network service" -D "C:\PostgreSQL\9.3\data2"

5. Run the following command to start the service.

   net start pg_test

6. The database owner role will be 'YOURMACHINENAME$'. If you want to change this to the standard 'postgres', you have to first create a new super user role that can rename the owner. From the command prompt, enter the following to create this super user.

   createuser -s -r -l -i -P -h localhost -p 5433 -U YOURMACHINENAME$ mysuperuser

7. Finally, connect to the server with psql (psql -U mysuperuser -h localhost -p 5433 postgres) and enter the following commands to rename your database owner and add a password.

   ALTER USER "YOURMACHINENAME$" RENAME TO postgres;

   ALTER USER postgres WITH PASSWORD 'yourpassword';