Take the 2-minute tour ×
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.

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
add comment

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.