0

After updating server (and also the pc for additional tests) to Postgres 9.5 I tried to import PG 9.4 dumps and/or to restore 9.5 dumps:

pg_dump testdb > db.sql
psql -d testdb -f db.sql

Restoring these dumps in PG 9.4 takes less than 30 minutes, restoring them in PG 9.5 takes several hours (even if I make a PG 9.5 dump and try to restore this one)!!! The test dumps have a size of 8 GB.

Does anybody know what the reason could be? Tried it with the original PG 9.4 and 9.5 configs as well as with different options like increasing max_wal_size in 9.5 e.g.

Do I miss a specific default option in 9.5 which may be different to 9.4?

Example Log:

LOG:  duration: 278349.128 ms  statement: COPY test (id, ...)
LOG:  duration: 646487.952 ms  statement: ALTER TABLE ONLY test ...

The same with creating index... It takes hours with PG 9.5.

  • Hmm... checked the disk with badblocks -sv /dev/sda. But no errors. The update is still slow - but just on the server. Installed PG 9.4, same result. Any idea what could cause the super long durations? Thanks in advance. – Barmi Jul 19 '16 at 22:32
  • It does not seem to be a PG problem. More likely it's a language or locale problem with PG on Ubuntu 16.04, because installing huge indexed tables with foreign languages and therefore with many utf-8 chars cause the problem. Do we miss a language package? On another server with Ubuntu 14.04 everything works fine. – Barmi Jul 20 '16 at 9:26
0

Interesting that nobody found the answer...

It's a glibc bug: After upgrading from Ubuntu 14.10 (glibc 2.19) to 15.04 (glibc 2.21), we are experiencing a big performance regression in one particular PostgreSQL index creation. The index creation now takes more than 10 minutes, while before it was done in about 30 seconds.

https://sourceware.org/bugzilla/show_bug.cgi?id=18441

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.