Slow pg_dump affecting pg_upgrade speed - Mailing list pgsql-admin

Hello everyone !

We are trying to upgrade our instances to PG 10 but we have had some
major speed issues.

During the upgrade process pg_upgrade launches a series of pg_dump
that are exceedingly slow. Combined they take more than 3 hour on one
2TB cluster (that I will call "cluster A" in the following
description).

We believe this is related to the size of our pg_catalog, we have
thousands (if not hundred of thousands of views) on most of our
databases. Here is an example on cluster A :

```
Amount of views per database in cluster A
--------------------
database_9999        40812
database_1001        229
database_5030        69142
database_5010        77938
database_8000        291
database_9997        42961
database_5001        137986
database_5000        129315
database_5003        307073
database_5002        183886
database_9998        56911
database_5005        84742
database_5004        401165
database_9996        31862
database_9995        41999
database_5009        31149
database_5008        131806
database_5007        200192
database_5006        311978
database_9994        9883
database_9993        44324
database_9992        34590
database_9991        12735
database_9990        35551
database_9986        4706
database_9985        1420
database_5014        62552
database_9987        20299
database_5012        193093
database_5013        24980
database_5011        360617
database_9988        33474
database_9989        2815
```

We are completely aware that this is a *bad* idea, and our newer
developments are all moving far far away for this type of schema,
however we are currently stuck with this for at least a few more
months if not years.

My question to this mailing list is : Are we missing something that
could speed up the pg_dump ?

This is the pg_upgrade command we have used :
```
/usr/lib/postgresql/10/bin/pg_upgrade -k  -j 8
--old-datadir=/var/lib/postgresql/9.5/main
--new-datadir=/var/lib/postgresql/10/main
--old-bindir=/usr/lib/postgresql/9.5/bin
--new-bindir=/usr/lib/postgresql/10/bin  --old-options '-c
config_file=/etc/postgresql/9.5/main/postgresql.conf'  --new-options
'-c config_file=/etc/postgresql/10/main/postgresql.conf'
```

The pg_dump command generated (one per database) by the pg_upgrade binary :
```
command: "/usr/lib/postgresql/10/bin/pg_dump" --host
/var/lib/postgresql --port 50432 --username postgres --schema-only
--quote-all-identifiers --binary-upgrade --format=custom
--file="pg_upgrade_dump_6667558.custom" 'dbname=database_5002' >>
"pg_upgrade_dump_6667558.log" 2>&1
```

Description of our PostgreSQL infrastructure :

Our cluster have all the same PostgreSQL configuration, they are all
on PostgreSQL 9.4 or 9.5 and they all use the same kind of hardware.
They also host the same kind of data (same schema only the load amount
is different).

Hardware configuration :
All of our PostgreSQL servers are on google-cloud-engine with a
`n1-highmem-8` flavour. That means 50GB of ram and 8 virtual CPUs.
We are using google's `SSD persistent disk` which gives us up to 240
MB/S Read and Write :

```
Operation Type Read Write
Sustained random IOPS limit 15,000.00 15,000.00
Sustained throughput limit (MB/s) 240.00 240.00
```

Here are our PostgreSQL settings :
```
postgres=# select name,setting from pg_settings where source LIKE
'configuration file';
            name             |
                                     setting

-----------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 autovacuum_max_workers      | 3
 DateStyle                   | ISO, MDY
 default_text_search_config  | pg_catalog.english
 effective_cache_size        | 3540224
 external_pid_file           | /var/run/postgresql/9.5-main.pid
 hot_standby                 | on
 lc_messages                 | en_US.UTF-8
 lc_monetary                 | en_US.UTF-8
 lc_numeric                  | en_US.UTF-8
 lc_time                     | en_US.UTF-8
 listen_addresses            | 10.16.13.2
 log_autovacuum_min_duration | 1000
 log_checkpoints             | on
 log_connections             | off
 log_destination             | syslog,csvlog
 log_directory               | /var/log/postgresql
 log_filename                | postgresql.%a
 log_line_prefix             | %t [%p]: [%l-1] appname=%a user=%u, db=%d
 log_lock_waits              | on
 log_min_duration_statement  | 1000
 log_rotation_age            | 1440
 log_rotation_size           | 0
 log_statement               | ddl
 log_temp_files              | 2048
 log_timezone                | UTC
 log_truncate_on_rotation    | on
 logging_collector           | on
 maintenance_work_mem        | 3145728
 max_connections             | 1500
 max_locks_per_transaction   | 258
 max_replication_slots       | 20
 max_wal_senders             | 20
 max_wal_size                | 30
 max_worker_processes        | 50
 random_page_cost            | 2.7
 shared_buffers              | 1416064
 shared_preload_libraries    | pglogical
 ssl                         | on
 ssl_cert_file               | /etc/ssl/certs/ssl-cert-snakeoil.pem
 ssl_key_file                | /etc/ssl/private/ssl-cert-snakeoil.key
 synchronous_commit          | on
 syslog_facility             | local2
 syslog_ident                | postgres
 temp_buffers                | 1024
 temp_file_limit             | 8304640
 TimeZone                    | UTC
 track_commit_timestamp      | on
 wal_keep_segments           | 1000
 wal_level                   | logical
 wal_log_hints               | on
 work_mem                    | 10240
```

Any help or comment is appreciated !

--

Leo