I have set up a PG9.2 database slightly over 110 GB in size. In addition to the master server, I have 2 servers running as active standby nodes. When I run pg_basebackup to set up the standby servers, it completes in about 60 minutes - which is okay for my needs. However, I also need to set up another standby server on the far end of a 100 MB link. As a dry run, I have changed the interface settings on one of the 2 local standby servers so that it runs at 100 MB, full duplex rather than 1 GB. My expectation was that *if* the backup was primarily limited by network speed, that it would take about 10 times as long - 600 minutes, or 10 hours. Instead, the pg_basebackup was completing about 2 percent per hour, and hit 45 percent after running 24 hours. That puts the total backup time to over 50 hours, which is more than 50 times the original time, or 5 times more than I would expect from having slowed the connection down. To validate that the network connection was behaving itself, I ran a "dd" before and after to an NFS mount; the results are reasonably close to the 10 x factor. 512000000 bytes (512 MB) copied, 7.10688 s, 72.0 MB/s 512000000 bytes (512 MB) copied, 87.2156 s, 5.9 MB/s My first thought was that something else had changed, so I flipped the network speed back to 1 GB and the backup once again ran within 60 minutes. Can anyone please shed light on expected behavior for this process? Maybe one or more parameters need to be adjusted in postgresql.conf to accomodate this kind of network where there are both fast and slow replication partners. Below are the lines from the config - minus the lines which are just comments. listen_addresses = '*' port = 5434 max_connections = 300 shared_buffers = 400MB wal_level = hot_standby synchronous_commit = local checkpoint_segments = 200 archive_mode = on archive_command = 'rsync -a %p /mnt/wal/db/%f' max_wal_senders = 3 wal_keep_segments = 10000 # (Set high deliberately for the sake of replication servers that might get behind) synchronous_standby_names = 'partner,rpartner' hot_standby = on log_destination = 'syslog' logging_collector = on log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 0 syslog_facility = 'LOCAL4' syslog_ident = 'postgres' log_timezone = 'US/Pacific' bytea_output = 'escape' datestyle = 'iso, mdy' timezone = 'US/Pacific' lc_messages = 'en_US.UTF-8' lc_monetary = 'en_US.UTF-8' lc_numeric = 'en_US.UTF-8' lc_time = 'en_US.UTF-8' default_text_search_config = 'pg_catalog.english' -- View this message in context: http://postgresql.1045698.n5.nabble.com/pg-basebackup-over-slowed-connection-slowed-out-of-proportion-tp5736998.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com. -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
