Re: a new standby server promotes itself to primary without cause

Yep, they're both in different directories and on different ports. Allow
me to note, that I have both handbuilt this file from the sample provided
in /usr/pgsql-9.4/share and used pg_basebackup's -R option to automatically
generate it. Doesn't seem to make a difference. Here's the recovery.conf
file I'm using on the standby.

restore_command = 'cp /var/lib/pgsql/9.4/share/%f %p' # e.g. 'cp
/mnt/server/archivedir/%f %p'
archive_cleanup_command = '/usr/pgsql-9.4/bin/pg_archivecleanup
/var/lib/pgsql/9.4/share %r'
#recovery_end_command = ''
#recovery_target_name = '' # e.g. 'daily backup 2011-01-26'
#recovery_target_time = '' # e.g. '2004-07-14 22:39:00 EST'
#recovery_target_xid = ''
#recovery_target_inclusive = true
recovery_target = 'immediate'
pause_at_recovery_target = false
standby_mode = on
primary_conninfo = 'host=10.32.10.175 port=5432 user=replicator'
# e.g. 'host=localhost port=5432'
primary_slot_name = 'replicant'
trigger_file = '/var/lib/pgsql/9.4/share/trigger_file'
#recovery_min_apply_delay = 0

I have also gone through the master's postgresql.conf file several times
looking for problems, and I've verified the replication slot exists.

Thanks for your assistance,
Jay

On Thu, Oct 8, 2015 at 9:51 AM, Keith Fiske <keith(at)omniti(dot)com> wrote:

>
>
>
>
> On Thu, Oct 8, 2015 at 9:24 AM, John Scalia <jayknowsunix(at)gmail(dot)com>
> wrote:
>
>> Hi all,
>>
>> We have an ongoing discussion here about retiring slony and implementing
>> streaming synchronous replication. As part of this effort, I've been trying
>> to build a small cluster with one primary and one standby. Yes, I know
>> about the potential problems with a single standby, but this is only to
>> provide configuration parameters to my sysadmins.
>>
>> What I've tried is building the two servers on a single sandbox server.
>> The primary has been running correctly for some time. The standby starts,
>> but quickly turns itself into a primary without any trigger file being
>> present. My process is:
>>
>> 1) build the new standby in a different directory with pg_basebackup
>> 2) edit the standby's postgresql.conf so the standby has a different port.
>> 3) start the standby.
>> 4) immediately after starting the standby, I see a recovery.done file in
>> that PGDATA dir.
>>
>> Here's part of the debug2 level log file from that standby, but I'm not
>> seeing why this standby is becoming a primary. Only that it is:
>>
>> [2015-10-08 07:18:19.448 CDT] @: LOG: 00000: database system is ready to
>> accept read only connections
>> [2015-10-08 07:18:19.448 CDT] @: LOCATION: sigusr1_handler,
>> postmaster.c:4857
>> cp: cannot stat `/var/lib/pgsql/9.4/share/00000002.history': No such file
>> or directory
>> [2015-10-08 07:18:19.477 CDT] @: DEBUG: 00000: could not restore file
>> "00000002.history" from archive: child process exited with exit code 1
>> [2015-10-08 07:18:19.477 CDT] @: LOCATION: RestoreArchivedFile,
>> xlogarchive.c:304
>> [2015-10-08 07:18:19.477 CDT] @: LOG: 00000: selected new timeline ID: 2
>> [2015-10-08 07:18:19.477 CDT] @: LOCATION: StartupXLOG, xlog.c:7107
>> cp: cannot stat `/var/lib/pgsql/9.4/share/00000001.history': No such file
>> or directory
>> [2015-10-08 07:18:19.485 CDT] @: DEBUG: 00000: could not restore file
>> "00000001.history" from archive: child process exited with exit code 1
>> [2015-10-08 07:18:19.485 CDT] @: LOCATION: RestoreArchivedFile,
>> xlogarchive.c:304
>> [2015-10-08 07:18:19.792 CDT] @: LOG: 00000: archive recovery complete
>> [2015-10-08 07:18:19.792 CDT] @: LOCATION: exitArchiveRecovery,
>> xlog.c:5417
>> [2015-10-08 07:18:19.798 CDT] @: DEBUG: 00000: performing replication
>> slot checkpoint
>> [2015-10-08 07:18:19.798 CDT] @: LOCATION: CheckPointReplicationSlots,
>> slot.c:794
>> [2015-10-08 07:18:19.814 CDT] @: DEBUG: 00000: attempting to remove WAL
>> segments older than log file 0000000000000006000000D8
>> [2015-10-08 07:18:19.814 CDT] @: LOCATION: RemoveOldXlogFiles,
>> xlog.c:3775
>> [2015-10-08 07:18:19.814 CDT] @: DEBUG: 00000: SlruScanDirectory
>> invoking callback on pg_multixact/offsets/0000
>> [2015-10-08 07:18:19.814 CDT] @: LOCATION: SlruScanDirectory, slru.c:1307
>> [2015-10-08 07:18:19.815 CDT] @: DEBUG: 00000: SlruScanDirectory
>> invoking callback on pg_multixact/members/0000
>> [2015-10-08 07:18:19.815 CDT] @: LOCATION: SlruScanDirectory, slru.c:1307
>> [2015-10-08 07:18:19.815 CDT] @: DEBUG: 00000: SlruScanDirectory
>> invoking callback on pg_multixact/offsets/0000
>> [2015-10-08 07:18:19.815 CDT] @: LOCATION: SlruScanDirectory, slru.c:1307
>> [2015-10-08 07:18:19.893 CDT] @: DEBUG: 00000: attempting to remove WAL
>> segments newer than log file 0000000200000006000000E1
>> [2015-10-08 07:18:19.893 CDT] @: LOCATION: RemoveNonParentXlogFiles,
>> xlog.c:5458
>> [2015-10-08 07:18:19.899 CDT] @: DEBUG: 00000: oldest MultiXactId member
>> is at offset 1
>> [2015-10-08 07:18:19.899 CDT] @: LOCATION: SetOffsetVacuumLimit,
>> multixact.c:2677
>> [2015-10-08 07:18:19.899 CDT] @: LOG: 00000: MultiXact member wraparound
>> protections are now enabled
>> [2015-10-08 07:18:19.899 CDT] @: LOCATION: DetermineSafeOldestOffset,
>> multixact.c:2587
>> [2015-10-08 07:18:19.899 CDT] @: DEBUG: 00000: MultiXact member stop
>> limit is now 4294914944 based on MultiXact 1
>> [2015-10-08 07:18:19.899 CDT] @: LOCATION: DetermineSafeOldestOffset,
>> multixact.c:2590
>> [2015-10-08 07:18:19.899 CDT] @: DEBUG: 00000: release all standby locks
>> [2015-10-08 07:18:19.899 CDT] @: LOCATION: StandbyReleaseAllLocks,
>> standby.c:666
>> [2015-10-08 07:18:19.903 CDT] @: LOG: 00000: starting background worker
>> process "powa"
>> [2015-10-08 07:18:19.904 CDT] @: LOCATION: do_start_bgworker,
>> postmaster.c:5412
>> [2015-10-08 07:18:19.905 CDT] @: LOG: 00000: database system is ready to
>> accept connections
>>
>> The one thing that's a little weird on this configuration is that both
>> the primary and the standby are on the same system, and I've never done a
>> configuration this way before. Usually, these are on different systems, but
>> this was done just to test some configuration parameters. Can a standby and
>> a primary not exist together on the same system? In any event, there is no
>> trigger file as specified in the recovery.conf on this system. So, why is
>> promotion occuring?
>> --
>> Jay
>>
>
>
> Long as they're on different ports and using different data directories,
> you should be fine.
> Can you share the contents of your recovery.conf file?
>
> --
> Keith Fiske
> Database Administrator
> OmniTI Computer Consulting, Inc.
> http://www.keithf4.com
>