I'm using PostgreSQL 9.2.3 on both computers, both of which run Win2008R2. I've setup streaming replication by following this guide: http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgresql-streaming-replication-in-10-mins/
My setups are:
Master
postgresql.conf
listen_address = slaveip, localhost #i tried with *(all) as well.
wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 8
wal_keep_segments = 8
pg_hba.conf
host replication replicator slaveip trust
Slave
postgresql.conf
wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 8
wal_keep_segments = 8
hot_standby = on
pg_hba.conf
host replication replicator hostip trust
I've re-worked the script from the guide to a .bat but for now i'm manually doing everything it does (using pg_basebackup etc.).
Both databases start and work without errors but the slave doesn't replicate any changes made to the master.
I don't have a trigger file yet, because i don't quite understand it. Does the trigger file just trigger the failover from Master to Slave or does it have an active role in the streaming replication process ?
EDIT @Daniel: I had forgotten to check the Slave logs and only checked the Master ones, my bad. There is only this error in the logs:
2013-04-25 05:26:46 PDT LOG: could not receive data from client: No connection could be made because the target machine actively refused it.
As this is a test environment, there are no firewalls on, no TMG(praise the lord), no antivirus, no anti-anything. The 2 machines can ping each other, they can open shared folders, etc. I realise that the master is denying access (now that i checked the logs). Could this be a problem with the user Postgres not having the right permissions ?
EDIT @Milen: Yes, I manually created one:
standby_mode = 'on'
primary_conninfo = 'host=masterip port=5432 user=replicator password=thepassword'
#trigger_file = 'postgresql.trigger' #This one is commented, because i haven't made the trigger yet.
EDIT: Hm, now i'm getting this error:
2013-04-30 07:22:03 PDT ERROR: cannot execute LOCK TABLE during recovery
2013-04-30 07:22:03 PDT STATEMENT: LOCK TABLE instances IN EXCLUSIVE MODE
Atleast now I know where to look.
recovery.conf
("/var/lib/postgresql/9.2/main/recovery.conf") and what's in it? – Milen A. Radev Apr 30 at 8:57