Re: PostgresSQL 9.5 and systemd autorestart but without replication.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx>:
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@xxxxxxxxx>:
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@xxxxxxxxx>:

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@xxxxxxxxx>:
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@xxxxxxxxxxx>:
On Fri, Oct 14, 2016 at 7:42 AM, Poul Kristensen <bcc5226@xxxxxxxxx> 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@xxxxxxxxx>:
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.hunley@xxxxxxxxxxx>:

On Thu, Oct 13, 2016 at 6:00 AM, Poul Kristensen <bcc5226@xxxxxxxxx> 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.hunley@xxxxxxxxxxx",
  "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



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

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux