Hi Folks,
I am having trouble setting up replication with Postgres 11.3. pg_basebackup is taking an unusually long time for an small Postgres database. Anything wrong in my configuration or anything I could do to speed up pg_basebackup?
I recently upgraded form Postgres 9.2.1. Using a similar postgres configuration, apart from some updates to config for Postgres 11.3. I am using pg_basebackup to replicate from the master. I am using secure ssh tunnel for the replication between master and slave, I.e. there is a ssh tunnel that forwards data from the localhost on port 5433 on the slave to the master server’s port 5432.
pg_basebackup is taking about 30 seconds.
c12-array2-c1:/# du ./path/to/database
249864 ./nimble/var/private/config/versions/group/sodb
pg_basebackup -D $PGSQL_BASEBKUP_PATH -U $DBUSER -c fast -l $backup_name -h localhost -p 5433 --wal-method=stream -Pv -s 10
postgresql.conf:
….
max_connections = 100 # (change requires restart)
# Note: Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
#superuser_reserved_connections = 3 # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # (change requires restart)
…..
# - Security and Authentication -
#authentication_timeout = 1min # 1s-600s
#ssl = off # (change requires restart)
#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers
# (change requires restart)
#ssl_renegotiation_limit = 512MB # amount of data between renegotiations
#ssl_cert_file = 'server.crt' # (change requires restart)
#ssl_key_file = 'server.key' # (change requires restart)
#ssl_ca_file = '' # (change requires restart)
#ssl_crl_file = '' # (change requires restart)
#password_encryption = on
#db_user_namespace = off
# Kerberos and GSSAPI
#krb_server_keyfile = ''
#krb_srvname = 'postgres' # (Kerberos only)
#krb_caseins_users = off
# - TCP Keepalives -
# see "man 7 tcp" for details
#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
# 0 selects the system default
#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
# 0 selects the system default
#tcp_keepalives_count = 0 # TCP_KEEPCNT;
# 0 selects the system default
…..
shared_buffers = 32MB # 32 or 300MB based on model
# (change requires restart)
#temp_buffers = 8MB # min 800kB
….
work_mem = 10MB # min 64kB
#maintenance_work_mem = 16MB # min 1MB
…..
wal_level = replica # minimal, archive, or hot_standby
# (change requires restart)
#fsync = on # turns forced synchronization on or off
#synchronous_commit = on # synchronization level;
# off, local, remote_write, or on
wal_sync_method = open_sync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync (default on Linux)
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
# (change requires restart)
#wal_writer_delay = 200ms # 1-10000 milliseconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 1min # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s # 0 disables
# - Archiving -
archive_mode = on # allows archiving to be done
# (change requires restart)
archive_command = '/bin/true' # command to use to archive a logfile segment
# placeholders: %p = path of file to archive
# %f = file name only
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0 # force a logfile segment switch after this
# number of seconds; 0 disables
…..
max_wal_senders = 10 # max number of walsender processes
# (change requires restart)
wal_keep_segments = 10 # in logfile segments, 16MB each; 0 disables
wal_sender_timeout = 10s # in milliseconds; 0 disables
# - Master Server -
# These settings are ignored on a standby server.
synchronous_standby_names = '' # standby servers that provide sync rep
# comma-separated list of application_name
# from standby(s); '*' = all
#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
# - Standby Servers -
# These settings are ignored on a master server.
hot_standby = on # "on" allows queries during recovery
# (change requires restart)
#max_standby_archive_delay = 30s # max delay before canceling queries
# when reading WAL from archive;
# -1 allows indefinite delay
#max_standby_streaming_delay = 30s # max delay before canceling queries
# when reading streaming WAL;
# -1 allows indefinite delay
wal_receiver_status_interval = 3s # send replies at least this often
# 0 disables
#hot_standby_feedback = off # send info from standby to prevent
# query conflicts
