Re: streaming replication not working - Mailing list pgsql-general

On Sep 24, 2013, at 5:48 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:

>> Here is what I have on the standby:
>>
>> postgresql.conf
>>
>> hot_standby = on
>> max_wal_senders = 2
>> wal_level = hot_standby
>
> You should set the same parameters on the primary, else it won't work.

On the primary I have


wal_level = hot_standby
wal_keep_segments = 48
max_wal_senders = 2

archive_mode = on
archive_command = 'rsync --whole-file --ignore-existing --delete-after --timeout=30 -a %p backup:/path/backup/%f'
archive_timeout = 300

I don't have "hot_standby = on". I thought that only applied to the standby to allow queries in recovery mode?


>> Am I missing something obvious? Do I have to back up the primary again to make this change?
>
> No, that shouldn't be necessary.
>
> What are the messages in the secondary's log
> after you start it?


Below is what I have for restart with log level at debug2. In looking at the source, I should have an error or a log
message"streaming replication successfully connected to primary". I never get either one. 


LOG:  restored log file "000000010000010F00000001" from archive
LOG:  restored log file "000000010000010F00000002" from archive
LOG:  received fast shutdown request
LOG:  aborting any active transactions
LOG:  shutting down
LOG:  database system is shut down
LOG:  database system was shut down in recovery at 2013-09-24 08:23:53 CDT
DEBUG:  standby_mode = 'on'
DEBUG:  trigger_file = '/pgsql/9.2/data/failover.trigger'
DEBUG:  primary_conninfo = 'host=localhost port=21333 user=postgres'
DEBUG:  restore_command = 'pg_standby -t /pgsql/9.2/data/failover.trigger /shared/pgbackup %f %p %r'
DEBUG:  archive_cleanup_command = 'pg_archivecleanup /shared/pgbackup %r'
LOG:  entering standby mode
LOG:  restored log file "000000010000010F00000002" from archive
DEBUG:  got WAL segment from archive
DEBUG:  checkpoint record is at 10F/204D9B8
DEBUG:  redo record is at 10F/2035608; shutdown FALSE
DEBUG:  next transaction ID: 0/3837659; next OID: 13124290
DEBUG:  next MultiXactId: 3; next MultiXactOffset: 5
DEBUG:  oldest unfrozen transaction ID: 1798, in database 1
DEBUG:  transaction ID wrap limit is 2147485445, limited by database with OID 1
DEBUG:  resetting unlogged relations: cleanup 1 init 0
DEBUG:  initializing for hot standby
LOG:  redo starts at 10F/2035608
DEBUG:  recovery snapshots are now enabled
CONTEXT:  xlog redo  running xacts: nextXid 3837668 latestCompletedXid 3837667 oldestRunningXid 3837668
DEBUG:  checkpointer updated shared memory configuration values
LOG:  consistent recovery state reached at 10F/3000000
LOG:  database system is ready to accept read only connections
LOG:  restored log file "000000010000010F00000003" from archive
DEBUG:  got WAL segment from archive



Thanks,

John DeSoi, Ph.D.