Postgresql – pg_basebackup giving “could not receive data from client: Connection reset by peer”

backuppg-basebackuppostgresql

I'm trying to get data to a new slave box from my new master (previously the slave).

I've tried the steps already and it worked previously. Now when I run the following command:

pg_basebackup -h masterhost -D /var/lib/postgresql/9.3/main -U rep -v -P -X stream

Sometimes when I run this command, nothing is logged. Other times I receive: "could not receive data from client: Connection reset by peer". I can see this process running on the master:

postgres: wal sender process rep slavehost(60899) sending backup "pg_basebackup base backup"  
  • I'm able to scp from one box to another at 100MB/s
  • I've set up the snakeoil certs on both boxes
  • It rejects invalid passwords
  • When I kill the process on the slave, I get "pg_basebackup: could not initiate base backup: FATAL: terminating connection due to administrator command"
  • There is no additional load on either box when I run this command
  • The /var/lib/postgresql/9.3/main dir on the slave is owned by postgres and is empty
  • The DB is ~60GB
  • Eventually the "wal sender process" on the master ends but nothing seems to happen
  • I can psql to the master box from the slave using a non-replication user
  • Both boxes are Ubuntu 14.04 running PG 9.3.6

Slave config:

listen_addresses = '*'
hot_standby = on

default_statistics_target = 50
maintenance_work_mem = 1GB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 22GB
work_mem = 192MB
checkpoint_segments = 16
shared_buffers = 7680MB
max_connections = 160
log_min_duration_statement = 80
log_temp_files = 0

Master has the same but also:

wal_buffers = 8MB
checkpoint_segments = 16
wal_keep_segments = 128
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 2
vacuum_defer_cleanup_age = 100000
ssl_renegotiation_limit = 0

Best Answer

If you have errors in your log along the lines of SSL Renegotiation Failure or SSL Error, then you likely ran into an issue with a version of OpenSSL and SSL renegotiation during the streaming basebackup.

Setting ssl_renegotiation_limit = 0 should help address your problem.

There are and have been many discussions now on disabling ssl renegotiation by default on the PostgreSQL lists. Example here: http://www.postgresql.org/message-id/E1ZKB9u-00050W-7V@gemulon.postgresql.org

Hope that helps. =)