postgresql: Restoring database backup is very slow and used resources are underutilized significantl-CodePudding

I am trying to restore backup of postgresql database on my personal computer. My OS is Ubuntu 18 and I use postgresql of 14 version. Volume of .sql file is 52G, but after waiting for 6 hours the volume of target database increased only to 120 MB. Then I changed some parameters of my default postgres.conf:

work_mem = 32MB
shared_buffers = 4GB
maintenance_work_mem = 2GB
full_page_writes = off
autovacuum = off
wal_buffers = -1

But the speed remained mostly the same. The resource utilization during INSERTs is following:

CPU < 7% (ps -aux)
Memory < 0.5 % (ps -aux)
Disk write speed < 1000 K/S (iotop, dd reached more than 80 MB/s)

Thank you in advance. Command used here is sudo -u postgres psql lom < psql_long_small_dump

CodePudding user response：

psql will restore the dump in a single thread (mostly, CREATE INDEX can get parallelized), so you won't exhaust your resources that way. You'd have more luck with a custom format dump (pg_dump -F c) which you can restore in several threads with pg_restore -j 4 or so.

The parameters you change seem random. To speed up your current restore process as much as you can, set the following:

wal_level = minimal  # requires a restart
maintenance_work_mem = 2GB
max_wal_size = 20GB  # as big as you want

and restore using psql -1 so that everything runs in a single transaction.