Re: PostgresSQL 9.5 and systemd autorestart but without replication.

I am now running PostgreSQL 9.6 master/replica:

Master - Wal sender process OK!

Standby: Started the server is succesfully but is recovering for several
days and the standby database is not accessible!
Wal receiver streaming is running and is in sync master.

select * from pg_locks p1 left join pg_stat_activity psa on p1.pid=psa.pid;
-[ RECORD 9
]------+----------------------------------------------------------------------------
locktype | relation
database | 0
relation | 2671
page |
tuple |
virtualxid |
transactionid |
classid |
objid |
objsubid |
virtualtransaction | 3/86715
pid | 29001
mode | AccessShareLock
granted | t
fastpath | f
datid | 13322
datname | postgres
pid | 29001
usesysid | 10
usename | postgres
application_name | psql
client_addr |
client_hostname |
client_port | -1
backend_start | 2016-10-24 11:41:20.291612+02
xact_start | 2016-10-24 11:53:57.815105+02
query_start | 2016-10-24 11:53:57.815105+02
state_change | 2016-10-24 11:53:57.815111+02
wait_event_type |
wait_event |
state | active
backend_xid |
backend_xmin | 2312

I can not figure it out!

OS:rhel 7.2

Thanks

Poul

2016-10-17 20:35 GMT+02:00 Poul Kristensen <bcc5226(at)gmail(dot)com>:

> RHEL 7.2, which made the postgresql-9.5.service restart on both virtual
> servers because of some network problem I am almost sure.
>
> The problem now is to make the replication server understand that he is
> not the master now until next failover. If I can not do this by
> reestablishing
> recovery.conf and then restarting the postgresql.-9.5.service then I am
> running out of ideas. I have to make sure to be able to handle the
> failover's and the reestablishment of the "replica" or standby server
> again when going in production on the physical servers.
>
> Thanks
> Poul
>
>
>
> 2016-10-14 14:53 GMT+02:00 Scott Mead <scottm(at)openscg(dot)com>:
>
>> On Fri, Oct 14, 2016 at 7:42 AM, Poul Kristensen <bcc5226(at)gmail(dot)com>
>> wrote:
>>
>>> Hi !
>>>
>>> Hopefully this is a situation in virtuel environments and not on physical
>>> servers with storage on SAN.as replication have to follow up in a
>>> autorestart without a manual new basebackup.
>>>
>>
>> What OS are you running? recovery.conf gets renamed to 'recovery.done'
>> only when a promotion (failover) occurs. This can happen a number of ways (*pg_ctl
>> promote, trigger_file, etc..). *Typically, these commands are not
>> executed automatically for you, you would have had to either install a tool
>> that does auto failover, *or* the startup scripts in your OS did
>> something (highly doubtful).
>>
>> --Scott
>>
>>
>>
>>> Does anyone have experience with replication on physical servers(slaves)?
>>>
>>>
>> Thanks
>>>
>>> Poul
>>>
>>>
>>>
>>>
>>> 2016-10-13 17:45 GMT+02:00 Poul Kristensen <bcc5226(at)gmail(dot)com>:
>>>
>>>> Thank you for fast respons.
>>>>
>>>> No. The recovery.conf has now replace a recovery.done file on the
>>>> slave.
>>>> No errors in the pg_log. I have not earlier experienced this situation.
>>>> The feature of autorestart is really nice.
>>>> BTW: I do not quite understand why the recovery.conf are replaced with
>>>> a recovery.done file.
>>>>
>>>> Thanks
>>>>
>>>> /Poul
>>>>
>>>>
>>>> 2016-10-13 16:30 GMT+02:00 Hunley, Douglas <douglas(dot)hunley(at)openscg(dot)com>
>>>> :
>>>>
>>>>>
>>>>> On Thu, Oct 13, 2016 at 6:00 AM, Poul Kristensen <bcc5226(at)gmail(dot)com>
>>>>> wrote:
>>>>>
>>>>>> This morning I noticed that my 2 PG virtual servers had made an
>>>>>> automatic
>>>>>> restart at exactly the same time 00:00. The 2 servers is running and
>>>>>> accepting connections but no WALL sender and no WALL receicver is running.
>>>>>> Why don't replication follow up!
>>>>>> Do I have to make a new pg_basebackup from primarily to replica?
>>>>>> OS: RHEL 7.2
>>>>>>
>>>>>
>>>>> Is the recovery.conf file still present and valid in $PGDATA on the
>>>>> slave? Any errors in the slave's pg_log directory concerning the
>>>>> replication?
>>>>>
>>>>>
>>>>> --
>>>>> {
>>>>> "name" : "douglas j hunley",
>>>>> "email" : "douglas(dot)hunley(at)openscg(dot)com <doug(dot)hunley(at)gmail(dot)com>",
>>>>> "title" : "database engineer",
>>>>> "phone" : "+1 732 339 3419 x163"
>>>>> }
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Med venlig hilsen / Best regards
>>>> Poul Kristensen
>>>> Linux-OS/Virtualizationexpert and Oracle DBA
>>>>
>>>
>>>
>>>
>>> --
>>> Med venlig hilsen / Best regards
>>> Poul Kristensen
>>> Linux-OS/Virtualizationexpert and Oracle DBA
>>>
>>
>>
>>
>> --
>> --
>> Scott Mead
>> Sr. Architect
>> *OpenSCG <http://openscg.com>*
>> http://openscg.com
>>
>
>
>
> --
> Med venlig hilsen / Best regards
> Poul Kristensen
> Linux-OS/Virtualizationexpert and Oracle DBA
>

--
Med venlig hilsen / Best regards
Poul Kristensen
Linux-OS/Virtualizationexpert and Oracle DBA