1

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

SwipeZ
  • 57
  • 1
  • 6
  • You mention INSERTs, did you do the `pg_dump` with either `--inserts` or `--column-inserts`? If that is the case it will definitely slow down the restore. – Adrian Klaver Apr 15 '22 at 15:32
  • I didn't create this backup, but I can ask its creator if necessary. But it is not now, the answer below seems to increase performance dramatically. – SwipeZ Apr 17 '22 at 17:24

1 Answers1

3

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263