Re: [ADMIN] Problems rebuilding slave using pg_basebackup

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