dhis2-users team mailing list archive

[Edward Robinson <erobinson@xxxxxxxxxxxxxxxxxx>]

Agree, I’m sure it’s a PostgreSQL / Ubuntu 16.04 issue.  I’m posting in as many locations as possible.  Seems I’m not alone
https://serverfault.com/questions/790723/postgres-9-5-restoring-dumps-is-very-very-slow
https://www.questarter.com/q/restoring-postgres-dump-is-very-very-slow-using-on-ubuntu-16-04-7_171095.html

Ed

From: Knut Staring [mailto:knutst@xxxxxxxxx]
Sent: Thursday, 13 July 2017 9:58 PM
To: Edward Robinson <erobinson@xxxxxxxxxxxxxxxxxx>
Cc: dhis2-users@xxxxxxxxxxxxxxxxxxx
Subject: Re: [Dhis2-users] Major speed issue with pg_restore

Perhaps useful to also send this question to a postgres forum?

On Jul 13, 2017 9:55 PM, "Edward Robinson" <erobinson@xxxxxxxxxxxxxxxxxx<mailto:erobinson@xxxxxxxxxxxxxxxxxx>> wrote:
I’ve setup a new Ubuntu 16.04 box with PostgreSQL 9.5.5 and I’m restoring a fairly large DHIS2 backup but having speed issues.  It’s a full pg_dump in custom format and about 650Gb compressed (a plain text dump produces a 7Gb file).  I made sure inserts were turned off, so that’s not the issue, but so far it’s been running for 33 hours – CPU at 100% - with no end in sight.  This is a backup file that took 30 minutes to generate.

If it is running synchronously, I calculated that it’s on around 20 million DB rows of around 170 million after 33 hours!  Surely that’s not normal.
I’ve tweaked PostgreSQL with the following settings:

maintenance_work_mem=2GB
max_wal_size = 1Gb
checkpoint_timeout = 3600
checkpoint_completion_target = 0.9

Anyway, if anyone has insight or has had a similar experience, or suggestions, please let me know!
I’m testing it on another (Windows) instance to see if there’s something amiss.

Cheers,
Ed

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-users
Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx<mailto:dhis2-users@xxxxxxxxxxxxxxxxxxx>
Unsubscribe : https://launchpad.net/~dhis2-users
More help   : https://help.launchpad.net/ListHelp