Postgres 9.5: Restoring dumps is very very slowIndefinite hang when restoring SQL 2005 database on a SQL 2008 server in EC2What's the default superuser username/password for postgres after a new install?Postgres suddenly slower and higher CPU usagepg_restore taking much longer than pg_dumppostgres restore from data files - no data showing upUnable to restore postgresql data dump for Django app hosted on Azure VMHowto Byte-level “offline” backup of postgresql databaseUpdate Postgres on Production Server without downtimePostgres autovacuum errors while restoring a dumpHow to diagnose memory usage issues in PostgreSQL 9.5 on a 8GB Windows 2008 Server?
Short story with brother-sister conjoined twins as protagonists?
What do you call the action of someone tackling a stronger person?
Impossible darts scores
How can Charles Proxy change settings without admin rights after first time?
Content builder HTTPS
What is the line crossing the Pacific Ocean that is shown on maps?
Firefox Arm64 available but RapsPi 3B+ still 32 bit
How risky is real estate?
Why is Madam Hooch not a professor?
Inverse-quotes-quine
Can a US President have someone sent to prison?
Should I include salary information on my CV?
Intuitively, why does putting capacitors in series decrease the equivalent capacitance?
Does the Distant Spell metamagic apply to the Sword Burst cantrip?
Declining an offer to present a poster instead of a paper
Do I recheck baggage at stopovers MCI-SEA-ICN-SGN? Delta and Korean Air
Is it okay to visually align the elements in a logo?
Does ultrasonic bath cleaning damage laboratory volumetric glassware calibration?
How often can a PC check with passive perception during a combat turn?
Singing along to guitar chords (harmony)
Do French speakers not use the subjunctive informally?
How could mana leakage be dangerous to a elf?
How many satellites can stay in a Lagrange point?
Pull-up sequence accumulator counter
Postgres 9.5: Restoring dumps is very very slow
Indefinite hang when restoring SQL 2005 database on a SQL 2008 server in EC2What's the default superuser username/password for postgres after a new install?Postgres suddenly slower and higher CPU usagepg_restore taking much longer than pg_dumppostgres restore from data files - no data showing upUnable to restore postgresql data dump for Django app hosted on Azure VMHowto Byte-level “offline” backup of postgresql databaseUpdate Postgres on Production Server without downtimePostgres autovacuum errors while restoring a dumpHow to diagnose memory usage issues in PostgreSQL 9.5 on a 8GB Windows 2008 Server?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom: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.
postgresql restore dump
asked Jul 19 '16 at 16:01
BarmiBarmi
1192 silver badges12 bronze badges
add a comment |
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.
postgresql restore dump
asked Jul 19 '16 at 16:01
BarmiBarmi
1192 silver badges12 bronze badges
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
add a comment |
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.
postgresql restore dump
asked Jul 19 '16 at 16:01
BarmiBarmi
1192 silver badges12 bronze badges
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.
postgresql restore dump
postgresql restore dump
asked Jul 19 '16 at 16:01
BarmiBarmi
1192 silver badges12 bronze badges
asked Jul 19 '16 at 16:01
BarmiBarmi
1192 silver badges12 bronze badges
asked Jul 19 '16 at 16:01
BarmiBarmi
1192 silver badges12 bronze badges
asked Jul 19 '16 at 16:01
BarmiBarmi
1192 silver badges12 bronze badges
asked Jul 19 '16 at 16:01
BarmiBarmi
1192 silver badges12 bronze badges
1192 silver badges12 bronze badges
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
add a comment |
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
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
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
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
add a comment |
1 Answer
1
active
oldest
votes
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
answered Feb 13 '18 at 2:30
BarmiBarmi
1192 silver badges12 bronze badges
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "2"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fserverfault.com%2fquestions%2f790723%2fpostgres-9-5-restoring-dumps-is-very-very-slow%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Feb 13 '18 at 2:30
BarmiBarmi
1192 silver badges12 bronze badges
add a comment |
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
answered Feb 13 '18 at 2:30
BarmiBarmi
1192 silver badges12 bronze badges
add a comment |
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
answered Feb 13 '18 at 2:30
BarmiBarmi
1192 silver badges12 bronze badges
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
answered Feb 13 '18 at 2:30
BarmiBarmi
1192 silver badges12 bronze badges
answered Feb 13 '18 at 2:30
BarmiBarmi
1192 silver badges12 bronze badges
answered Feb 13 '18 at 2:30
BarmiBarmi
1192 silver badges12 bronze badges
answered Feb 13 '18 at 2:30
BarmiBarmi
1192 silver badges12 bronze badges
1192 silver badges12 bronze badges
add a comment |
add a comment |
Thanks for contributing an answer to Server Fault!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fserverfault.com%2fquestions%2f790723%2fpostgres-9-5-restoring-dumps-is-very-very-slow%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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