PostgreSQL: How to create two instances in same window machine?

Question

I need to have additional instance for our production server.

Is it possible?

Where to begin?

Using Postgresql 9.1 on Windows Server

Answer 1

If you already have the binaries, then adding a second instance ("cluster") is done by running initdb and then registering that new instance as a Windows service.

(I will not prefix the name of the executables with the path they are stored in. You need to either add the bin directory of the Postgres installation to your system wide PATH, use fully qualified names, or simply change into the bin directory to make it the current directory)

To do that, open a command line (cmd.exe) and use initdb to create the instance:

initdb -D c:\Data\PostgresInstance2 -W -A md5

-W makes initdb prompt you for the name and password to be used as the superuser of that instance - make sure you remember the username and passwords you have given. -D specifies where the cluster should be created. Do NOT create that under c:\Program Files.

Once the instance (cluster) is initialized edit c:\Data\PostgresInstance2\postgresql.conf to use a different port, e.g. port = 5433. If the instance should be reachable from the outside you also need to adjust listen_addresses.

You can check if everything works by manually starting the new instance:

pg_ctl start -D c:\Data\PostgresInstance2

Once you have change the port (and adjusted other configuration parameters) you can create a Windows service for the new cluster:

pg_ctl register -N postgres2 -D c:\Data\PostgresInstance2

The service will execute with the "Local Network Account", so you have to make sure the privileges on the data directory are setup properly.