After upgrade pg12 -> pg14 import time increased

From: Hu Bert <revirii(at)googlemail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: After upgrade pg12 -> pg14 import time increased
Date: 2022-02-25 08:25:29
Message-ID: CAAV-989+T6QYJRJV_O4YC7wdOgsatT02rETVtTXJ96HaHvN8Jw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

starting with an explanation first... i have some sort of 'cold
backup' system: it rsyncs a postgres dump (size: ~1.5TB) from the main
system (created with 'pg_dump $dbname > db.sql) to the backup system
(10TB hdds in a raid10), and then automatically imports that dump
(psql $dbname < $db.sql) into postgres. So the backup system
stores/provides the database of each day (cleanup of older stuff done
manually). copy+import normally finished within 16-18 hours.

About 4 weeks ago both systems were running with postgres12 on debian
buster; then the switch to postgres14 was done, first on main system,
later on the backup system (both with pg_upgrade, no problems here).
But after that switch the import time on the backup system increased,
now taking more than 24 hours. A dist-upgrade to debian bullseye was
done, but no improvement.

I was checking release notes (releases 13+14), but, somehow, nothing
catched my eye.

hardware of backup system:
128G ram, raid10 (ext4, 4 disks) for postgresql, raid10 (ext4, 6
disks) for sql backup files
shared_buffers = 32768MB
work_mem = 16384MB
maintenance_work_mem = 16384MB
effective_cache_size = 16GB

no read/write operations on the backup system. any idea what could've
changed? more information needed?

thx,
Hubert

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2022-02-25 09:33:12 Re: \set \e and newline
Previous Message Wim Bertels 2022-02-24 17:16:48 \set \e and newline