Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
    
The trigger file shouldn't exist until the failover, that's its sole purpose. As for what the slave is doing, what's in the postgresql server log? – Daniel Vérité Apr 29 '13 at 19:14
    
Could you check if you have recovery.conf ("/var/lib/postgresql/9.2/main/recovery.conf") and what's in it? – Milen A. Radev Apr 30 '13 at 8:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.