pg_basebackup is taking an unusually long time with Postgres 11.3 - Mailing list pgsql-performance

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