Re: PostgresSQL 9.5 and systemd autorestart but without replication.

Replica(hot_standby) server:

Is it normal behavior that PostgreSQL 9.6 does a very very slow
recovery restart point at 1/77472E5 in the pg_log
while doing this
postgres: startup process recovering 000000010000000100000077(this
process has run several days)

I expect the database to finish startup when recovering is done.
Is this correct?

At the same time streaming works well

postgres: wal receiver process streaming 1/774731D0

This is the autogenerated recovery.conf except of cause xxxxxxxx and 999
when
used pg_basebackup:

standby_mode = 'on'
primary_conninfo = 'user=xxxxxx host=999.999.999.99 port=5432
sslmode=prefer sslcompression=1 krbsrvname=xxxxxxx'

Thanks!

Poul

2016-10-24 15:14 GMT+02:00 Poul Kristensen <bcc5226(at)gmail(dot)com>:

> Pid 1449 is postgres: startup process recovering
> 00000001000000010000002C
>
> strace -p 1449 -e write
>
> gives
>
> Process 1449 attached
>
> SIGUSR1 {si_signo=SIGUSR1, si_code=SI_USER, si_pid=1488, si_uid=26} ---
> write(12, "\0", 1)
>
> Does this mean that write is denied on the master?
>
> TIA
>
> Poul
>
>
>
>
>
>
>
>
> 2016-10-24 14:26 GMT+02:00 Poul Kristensen <bcc5226(at)gmail(dot)com>:
>
>> This is output from strace -p 1449(pid):
>>
>> The strace keeps repeating this :
>>
>>
>> read(7, 0x7fff0dc197f7, 1) = -1 EAGAIN (Resource temporarily
>> unavailable)
>> epoll_create(3) = 36
>> epoll_ctl(36, EPOLL_CTL_ADD, 11, {EPOLLIN|EPOLLERR|EPOLLHUP,
>> {u32=18051624, u64=18051624}}) = 0
>> epoll_ctl(36, EPOLL_CTL_ADD, 7, {EPOLLIN|EPOLLERR|EPOLLHUP,
>> {u32=18051648, u64=18051648}}) = 0
>> epoll_wait(36, {}, 1, 5000) = 0
>> close(36) = 0
>>
>> Does anyone have an idea off want is going on.
>>
>> TIA.
>>
>> Poul
>>
>>
>> 2016-10-24 11:59 GMT+02:00 Poul Kristensen <bcc5226(at)gmail(dot)com>:
>>
>>>
>>> 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
>>>
>>
>>
>>
>> --
>> 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
>

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