I need to have additional instance for our production server.
Is it possible?
Where to begin?
Using Postgresql 9.1 on Windows Server
5
-
Hi @TimBiegeleisen, what i need is to run in windows. I don't know how to apply those instructions. :(fLen– fLen2016-10-04 01:14:44 +00:00Commented Oct 4, 2016 at 1:14
-
Are you sure you need a whole separate instance (on a separate port, with its own server configuration, transaction logs, etc.), and not just an additional database and/or tablespace in your existing instance?Wyzard– Wyzard2016-10-04 01:52:07 +00:00Commented Oct 4, 2016 at 1:52
-
yes, as much as possible separate port but same windows server. I need to have additional instance. Is that possible, @Wyzard?fLen– fLen2016-10-04 01:55:32 +00:00Commented Oct 4, 2016 at 1:55
-
Is Postgres already installed and you want to add another instance? Also 9.1 is no longer maintained (supported). You should really use an up-to-date version (9.5 or 9.6) if you are planning a new installation.user330315– user3303152016-10-04 06:08:39 +00:00Commented Oct 4, 2016 at 6:08
-
@a_horse_with_no_name, Is Postgres already installed and you want to add another instance? YES. Currently downloading version 9.6.fLen– fLen2016-10-04 06:25:57 +00:00Commented Oct 4, 2016 at 6:25
Add a comment
|
3 Answers
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.
2 Comments
NewSheriff
i cahnge conf port, but when i start server i get this msg HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. LOG: could not bind IPv4 socket: Permission denied HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for "localhost" FATAL: could not create any TCP/IP sockets LOG: database system is shut down
Aung
Had same error as above, and realize I need to remove # which is a comment keyword, from #port = 5433 to port = 5433 , solves the issue
Follow the below steps to create another PostgreSQL Instance on same windows server.
Stop the PostgreSQL Service
Point to PostgreSQL installation path (C:\Program Files\PostgreSQL)
Go to File Explorer and copy the version to the same Postgres Folder. Ex: Copy Version 16 to 16-1
Open CMD as admin, navigate to folder 16-1 the BIN folder. EX: C:\Program Files\PostgreSQL\16-1\bin
Run below command pg_ctl register -N postgres16-1 -U "NT AUTHORITY\NetworkService" -D "C:\Program Files\PostgreSQL\16-1\data"
Navigate to data folder in 16-1 and then run below command to change the port number from 5432 to 5433. notepad postgresql.conf
Modify the PostMaster file as below notepad postmaster.opts C:/Program Files/PostgreSQL/16/bin/postgres.exe "-D" "C:\Program Files\PostgreSQL\16\data" to C:/Program Files/PostgreSQL/16-1/bin/postgres.exe "-D" "C:\Program Files\PostgreSQL\16-1\data"
You can find new postgres16-1 in services.msc. Before starting the service provide access to 16-1 folder with "NT Authority\Network Service" with full control.
Start the service 16-1 then connect using PGADMIN with new connection parameter as "LocalHost" and Post Nuber "5433"
That's it. You now successfully installed second postgreSQL Instance.
Happy Learning.
Comments
@NewSheriff
Your start command for your second server needs to use the port you specified in config e.g. if using port 5433 instead of port 5432 then adding: -o "-p 5433" to the end of your start-up command should get past the error message you mentioned
