Postgresql – install a PostgreSQL hot standby instance on the same server

postgresqlwindows-server-2008

I'm trying to install a second instance of PostgreSQL on a server (Windows Server 2008) that already has it. I've managed to do this with different versions in the past, but in this case I want the second instance to be a hot-standby (read only replication) server for people to run reports from without affecting the production server with locks, etc. When I run the installer for version 9.2 it tells me that the database is already installed and offers to upgrade it.

Is it as simple as manually creating a duplicate Windows service entry to run that points to a different data directory? Or is this sort of behaviour not supported at all?

Best Answer

if the goal is to have a dedicated box that runs several instances on postgres, on multiple tcp/ip ports, and it sounds like this is a virgin project,.... i would highly recommend installing a quality os on the box. centos or debian are great server linux distros, or you can always for with freebsd if you feel adventerous....

once you have that going, it becomes trivial to fire up multiple postgres instaces, each on its own tcp/ip port, with its own conf file, /tmp and /data dir's.

from my experience, doing this on *nix is very straightfowrward, but attempting on windows will drive one bonkers :P

Related Solutions

PostgreSQL – Default Superuser Username/Password After New Install

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password:

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

PostgreSQL failover cluster on Windows Server

PostgreSQL works fine with MSCS, using the generic service mode. It is recommended that you install the PostgreSQL binaries on the shared drive as well, and point the service there - that way you are sure you won't get a version mismatch.

Lars has a good point in that it's not recommended to run the db and appserver on the same machines. You might want to consider using 2 machines for the db and 2 for the appserver instead - or at least have MSCS take down the appserver on the node that PostgreSQL runs on (i'm not sure if it can do that, but I would imagine it should)

Best Answer

Related Solutions

PostgreSQL – Default Superuser Username/Password After New Install

PostgreSQL failover cluster on Windows Server

Related Topic