Problems rebuilding slave using pg_basebackup

From: Douglas Reed <douglas(at)fsbtech(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Problems rebuilding slave using pg_basebackup
Date: 2017-11-08 10:09:49
Message-ID: CAN7N4m3jAfnWV+1jq7-6S9wowEHU6Kdoomh3Rfe_RdSdt0sDfg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi

Sorry if this email was aready received but I sent it originally from my
own email address
but received no response from the moderator so I assume that it may have
got caught in the
filter.

We are having a number of problems when we attempt to rebuild our slave
from its master

We have made about three attempts without success (using a proven set of
notes)

It's been rebuilt several times over the last few months although the time
between
pg_basebackup being keyed and it actually copying data can be up to six
minutes.
And after completion the time taken from database startup to psql
availability
can also be several minutes while it processes any remaining logs.

Both machines are virtuals and are based with a leading cloud provider

OS Linux Centos6 (6.8 Final)

pg version 9.5.4

pg WAL settings on the master database

max_wal_senders = 5
max_wal_size = 4GB
min_wal_size = 256MB
wal_block_size = 8192
wal_buffers = 1MB
wal_compression = off
wal_keep_segments = 32
wal_level = hot_standby
wal_log_hints = off
wal_receiver_status_interval = 10s
wal_receiver_timeout = 1min
wal_retrieve_retry_interval = 5s
wal_segment_size = 16MB
wal_sender_timeout = 1min
wal_sync_method = fdatasync
wal_writer_delay = 200ms

Message from pg_basebackup

[postgres(at)xxxxxxxxxx]$ pg_basebackup -h -IP_HIDDEN- -D
/var/lib/pgsql/9.5/data -P -U postgres --xlog-method=stream
pg_basebackup: could not receive data from WAL stream: server closed
the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
269061959/269164935 kB (99%), 1/1 tablespace
pg_basebackup: child process exited with error 1

Relevant error messages from master's log

Nov 7 11:52:32 o8-data1 postgres[28558]: [6-1]
user=[unknown],db=[unknown],app=[unknown]client=-IP_HIDDEN- LOG:
connection received: host=-IP_HIDDEN- port=41498
Nov 7 11:52:32 o8-data1 postgres[28558]: [7-1]
user=postgres,db=[unknown],app=[unknown]client=-IP_HIDDEN- LOG:
replication connection authorized: user=postgres
Nov 7 13:51:44 o8-data1 postgres[28558]: [8-1]
user=postgres,db=[unknown],app=pg_basebackupclient=-IP_HIDDEN- LOG: could
not send data to client: Broken pipe
Nov 7 13:51:44 o8-data1 postgres[28558]: [9-1]
user=postgres,db=[unknown],app=pg_basebackupclient=-IP_HIDDEN- ERROR: base
backup could not send data, aborting backup
Nov 7 13:51:44 o8-data1 postgres[28558]: [10-1]
user=postgres,db=[unknown],app=pg_basebackupclient=-IP_HIDDEN- FATAL:
connection to client lost
Nov 7 13:51:44 o8-data1 postgres[28558]: [11-1]
user=postgres,db=[unknown],app=pg_basebackupclient=-IP_HIDDEN- LOG:
disconnection: session time: 1:59:11.943 user=postgres database=
host=-IP_HIDDEN- port=41498

Nov 7 13:54:48 o8-data1 postgres[35445]: [6-1]
user=[unknown],db=[unknown],app=[unknown]client=-IP_HIDDEN- LOG:
connection received: host=-IP_HIDDEN- port=44040
Nov 7 13:54:48 o8-data1 postgres[35445]: [7-1]
user=postgres,db=[unknown],app=[unknown]client=-IP_HIDDEN- LOG:
replication connection authorized: user=postgres
Nov 7 15:09:20 o8-data1 postgres[35445]: [8-1]
user=postgres,db=[unknown],app=pg_basebackupclient=-IP_HIDDEN- LOG: could
not send data to client: Broken pipe
Nov 7 15:09:20 o8-data1 postgres[35445]: [9-1]
user=postgres,db=[unknown],app=pg_basebackupclient=-IP_HIDDEN- ERROR: base
backup could not send data, aborting backup
Nov 7 15:09:20 o8-data1 postgres[35445]: [10-1]
user=postgres,db=[unknown],app=pg_basebackupclient=-IP_HIDDEN- FATAL:
connection to client lost
Nov 7 15:09:20 o8-data1 postgres[35445]: [11-1]
user=postgres,db=[unknown],app=pg_basebackupclient=-IP_HIDDEN- LOG:
disconnection: session time: 1:14:31.925 user=postgres database=
host=-IP_HIDDEN- port=44040

Many thanks in advance

--
Douglas Reed
DBA
FSB Technology

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Payal Singh 2017-11-08 11:57:18 Re: Problems rebuilding slave using pg_basebackup
Previous Message Thomas Kellerer 2017-11-08 06:56:29 Re: Running Windows binaries from non-English folder