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.
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.
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.
Can you tell us a bit more about the machine. In particular RAM size
and disk type/speed and filesystem type.
Given that you are probably not going to be running anything else on
this machine while you are restoring I think you might be able to
tweak a bit more aggressively just for this operation.
You don't mention shared_buffers above? That is a dramatic control
lever. Is that just something you left off your mail?
Bob
···
On 14 July 2017 at 02:54, Edward Robinson <erobinson@projectbalance.com> 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.
I am also trying to understand how a plain sql dump is only 7G while
the custom compressed format is 650G. But given that is true, why
aren't you using the plain text dump?
···
On 14 July 2017 at 13:02, Bob Jolliffe <bobjolliffe@gmail.com> wrote:
Hi Edward
Can you tell us a bit more about the machine. In particular RAM size
and disk type/speed and filesystem type.
Given that you are probably not going to be running anything else on
this machine while you are restoring I think you might be able to
tweak a bit more aggressively just for this operation.
You don't mention shared_buffers above? That is a dramatic control
lever. Is that just something you left off your mail?
Bob
On 14 July 2017 at 02:54, Edward Robinson <erobinson@projectbalance.com> 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.
Apologies, I meant 650Mb on the compressed.
I'll revert with more details shortly, it looks like a huge factor may be autovacuum being on (and possibly logging). I'm tweaking a little more then going to restart it.
Some sort of progress indicator would be nice for restoring large dumps like these.
I'll post my postgresql.conf and other details shortly. Thanks!
···
-----Original Message-----
From: Bob Jolliffe [mailto:bobjolliffe@gmail.com]
Sent: Friday, 14 July 2017 8:07 AM
To: Edward Robinson <erobinson@projectbalance.com>
Cc: dhis2-users@lists.launchpad.net
Subject: Re: [Dhis2-users] Major speed issue with pg_restore
I am also trying to understand how a plain sql dump is only 7G while the custom compressed format is 650G. But given that is true, why aren't you using the plain text dump?
On 14 July 2017 at 13:02, Bob Jolliffe <bobjolliffe@gmail.com> wrote:
Hi Edward
Can you tell us a bit more about the machine. In particular RAM size
and disk type/speed and filesystem type.
Given that you are probably not going to be running anything else on
this machine while you are restoring I think you might be able to
tweak a bit more aggressively just for this operation.
You don't mention shared_buffers above? That is a dramatic control
lever. Is that just something you left off your mail?
Bob
On 14 July 2017 at 02:54, Edward Robinson <erobinson@projectbalance.com> 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.
Yes I saw that note on autovacuum in one of the links you gave.
Certainly can do no harm to turn it off during restoration.
Regarding postgresql.conf, it is sometimes worth taking into account
that later settings in the file override earlier ones. This means
that you can gather all your tweaks together at the bottom of the file
rather than have them scattered throughout. Along the same lines, you
can also just include them in a separate customisation file by having
something like
include 'mytweaks.conf'
at the bottom of the file. This might be useful if you wanted to have
a configuration profile optimized for a particular type of operation.
···
On 14 July 2017 at 13:20, Edward Robinson <erobinson@projectbalance.com> wrote:
Apologies, I meant 650Mb on the compressed.
I'll revert with more details shortly, it looks like a huge factor may be autovacuum being on (and possibly logging). I'm tweaking a little more then going to restart it.
Some sort of progress indicator would be nice for restoring large dumps like these.
I'll post my postgresql.conf and other details shortly. Thanks!
-----Original Message-----
From: Bob Jolliffe [mailto:bobjolliffe@gmail.com]
Sent: Friday, 14 July 2017 8:07 AM
To: Edward Robinson <erobinson@projectbalance.com>
Cc: dhis2-users@lists.launchpad.net
Subject: Re: [Dhis2-users] Major speed issue with pg_restore
I am also trying to understand how a plain sql dump is only 7G while the custom compressed format is 650G. But given that is true, why aren't you using the plain text dump?
On 14 July 2017 at 13:02, Bob Jolliffe <bobjolliffe@gmail.com> wrote:
Hi Edward
Can you tell us a bit more about the machine. In particular RAM size
and disk type/speed and filesystem type.
Given that you are probably not going to be running anything else on
this machine while you are restoring I think you might be able to
tweak a bit more aggressively just for this operation.
You don't mention shared_buffers above? That is a dramatic control
lever. Is that just something you left off your mail?
Bob
On 14 July 2017 at 02:54, Edward Robinson <erobinson@projectbalance.com> 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.
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.
Sorry for this slow response, time has not been on my side
Also, thanks Bob, Knut and Hannan for your responses.
So to fill everyone in, I went through postgres.conf with a fine toothed comb. @ Bob, Shared buffers were set to 3200 though I'd set them to 1600 for the restore as per this article's suggestions:
Overall I'm convinced the PGSQL environment had nothing to do with the issue... what I had suspect is that the combination of Ubuntu 16.04 and our hardware was not a good match. I suspect the SATA channel in particular was the bottleneck.
To prove a point (I have two identical machines), I loaded Win Server 2012 R2 and and installed PGSQL 9.5.5 on the box and, without so much as tweaking a single file, I restored the dump within 50 minutes. We're likely upgrading the platform soon so I'm not going to break my back troubleshooting what is likely an issue that won't be there in 6 months time. Dell didn't include Debian or Ubuntu on their list of supported OS for the hardware combination, though I have no reason to believe it couldn't work with a little effort and a bit of old school compiling, I just don't have the luxury of time to fiddle. It worked well with 12.04 out of the box.
I just thought I'd post this in case anyone else was having similar trouble - if you've double checked the configs, it's quite possibly a lower level issue.
Cheers!
Ed
Sorry for this slow response, time has not been on my side
Also, thanks Bob, Knut and Hannan for your responses.
So to fill everyone in, I went through postgres.conf with a fine toothed comb. @ Bob, Shared buffers were set to 3200 though I’d set them to 1600 for the restore as per this article’s suggestions:
Overall I’m convinced the PGSQL environment had nothing to do with the issue… what I had suspect is that the combination of Ubuntu 16.04 and our hardware was not a good match. I suspect the SATA channel in particular was the bottleneck.
To prove a point (I have two identical machines), I loaded Win Server 2012 R2 and and installed PGSQL 9.5.5 on the box and, without so much as tweaking a single file, I restored the dump within 50 minutes. We’re likely upgrading the platform soon so I’m not going to break my back troubleshooting what is likely an issue that won’t be there in 6 months time. Dell didn’t include Debian or Ubuntu on their list of supported OS for the hardware combination, though I have no reason to believe it couldn’t work with a little effort and a bit of old school compiling, I just don’t have the luxury of time to fiddle. It worked well with 12.04 out of the box.
I just thought I’d post this in case anyone else was having similar trouble - if you’ve double checked the configs, it’s quite possibly a lower level issue.
Sorry for this slow response, time has not been on my side
Also, thanks Bob, Knut and Hannan for your responses.
So to fill everyone in, I went through postgres.conf with a fine toothed comb. @ Bob, Shared buffers were set to 3200 though I’d set them to 1600 for the restore as per this article’s suggestions:
Overall I’m convinced the PGSQL environment had nothing to do with the issue… what I had suspect is that the combination of Ubuntu 16.04 and our hardware was not a good match. I suspect the SATA channel in particular was the bottleneck.
To prove a point (I have two identical machines), I loaded Win Server 2012 R2 and and installed PGSQL 9.5.5 on the box and, without so much as tweaking a single file, I restored the dump within 50 minutes. We’re likely upgrading the platform soon so I’m not going to break my back troubleshooting what is likely an issue that won’t be there in 6 months time. Dell didn’t include Debian or Ubuntu on their list of supported OS for the hardware combination, though I have no reason to believe it couldn’t work with a little effort and a bit of old school compiling, I just don’t have the luxury of time to fiddle. It worked well with 12.04 out of the box.
I just thought I’d post this in case anyone else was having similar trouble - if you’ve double checked the configs, it’s quite possibly a lower level issue.
this is besides the main point but make sure you create pg dumps without the analytics tables, and instead let DHIS 2 re-generate the analytics tables (which is faster as indexing is run in parallel).
Sorry for this slow response, time has not been on my side
Also, thanks Bob, Knut and Hannan for your responses.
So to fill everyone in, I went through postgres.conf with a fine toothed comb. @ Bob, Shared buffers were set to 3200 though I’d set them to 1600 for the restore as per this article’s suggestions:
Overall I’m convinced the PGSQL environment had nothing to do with the issue… what I had suspect is that the combination of Ubuntu 16.04 and our hardware was not a good match. I suspect the SATA channel in particular was the bottleneck.
To prove a point (I have two identical machines), I loaded Win Server 2012 R2 and and installed PGSQL 9.5.5 on the box and, without so much as tweaking a single file, I restored the dump within 50 minutes. We’re likely upgrading the platform soon so I’m not going to break my back troubleshooting what is likely an issue that won’t be there in 6 months time. Dell didn’t include Debian or Ubuntu on their list of supported OS for the hardware combination, though I have no reason to believe it couldn’t work with a little effort and a bit of old school compiling, I just don’t have the luxury of time to fiddle. It worked well with 12.04 out of the box.
I just thought I’d post this in case anyone else was having similar trouble - if you’ve double checked the configs, it’s quite possibly a lower level issue.
That is useful info for the forum for sure and I fully agree, thanks Lars!
Ultimately the server supports Linux – it’s just certified for different distros. Also, I noticed a number of Ubuntu 16.04 users complaining of the same speed issue, so while Ubuntu will probably remain my go-to distro, in the case of these servers, I’m probably better off getting Suse or one of the other supported distros on before I take any more troubleshooting steps. For now it’s running fine and the future direction may be different – including cloud hosting to avoid this kind of hassle.
Thanks everyone for the support,
Cheers
Ed
···
Hi Edward,
this is besides the main point but make sure you create pg dumps without the analytics tables, and instead let DHIS 2 re-generate the analytics tables (which is faster as indexing is run in parallel).
Sorry for this slow response, time has not been on my side
Also, thanks Bob, Knut and Hannan for your responses.
So to fill everyone in, I went through postgres.conf with a fine toothed comb. @ Bob, Shared buffers were set to 3200 though I’d set them to 1600 for the restore as per this article’s suggestions:
Overall I’m convinced the PGSQL environment had nothing to do with the issue… what I had suspect is that the combination of Ubuntu 16.04 and our hardware was not a good match. I suspect the SATA channel in particular was the bottleneck.
To prove a point (I have two identical machines), I loaded Win Server 2012 R2 and and installed PGSQL 9.5.5 on the box and, without so much as tweaking a single file, I restored the dump within 50 minutes. We’re likely upgrading the platform soon so I’m not going to break my back troubleshooting what is likely an issue that won’t be there in 6 months time. Dell didn’t include Debian or Ubuntu on their list of supported OS for the hardware combination, though I have no reason to believe it couldn’t work with a little effort and a bit of old school compiling, I just don’t have the luxury of time to fiddle. It worked well with 12.04 out of the box.
I just thought I’d post this in case anyone else was having similar trouble - if you’ve double checked the configs, it’s quite possibly a lower level issue.
It is unlikely (though not impossible) that the problem is ubuntu specific. It could be your windows for example, is making better use of the disk.
I think I see you had 2 sata disks which would probably work best in a raid 0 setup. Perhaps windows has arranged them thus. Maybe also some other tweaks to get the best out of the filesystem. Good luck.
That is useful info for the forum for sure and I fully agree, thanks Lars!
Ultimately the server supports Linux – it’s just certified for different sistros. Also, I noticed a number of Ubuntu 16.04 users complaining of the same speed issue, so while Ubuntu will probably remain my go-to distro, in the case of these servers, I’m probably better off getting Suse or one of the other supported distros on before I take any more troubleshooting steps. For now it’s running fine and the future direction may be different – including cloud hosting to avoid this kind of hassle.
Subject: Re: [Dhis2-users] Major speed issue with pg_restore
Hi Edward,
this is besides the main point but make sure you create pg dumps without the analytics tables, and instead let DHIS 2 re-generate the analytics tables (which is faster as indexing is run in parallel).
Sorry for this slow response, time has not been on my side
Also, thanks Bob, Knut and Hannan for your responses.
So to fill everyone in, I went through postgres.conf with a fine toothed comb. @ Bob, Shared buffers were set to 3200 though I’d set them to 1600 for the restore as per this article’s suggestions:
Overall I’m convinced the PGSQL environment had nothing to do with the issue… what I had suspect is that the combination of Ubuntu 16.04 and our hardware was not a good match. I suspect the SATA channel in particular was the bottleneck.
To prove a point (I have two identical machines), I loaded Win Server 2012 R2 and and installed PGSQL 9.5.5 on the box and, without so much as tweaking a single file, I restored the dump within 50 minutes. We’re likely upgrading the platform soon so I’m not going to break my back troubleshooting what is likely an issue that won’t be there in 6 months time. Dell didn’t include Debian or Ubuntu on their list of supported OS for the hardware combination, though I have no reason to believe it couldn’t work with a little effort and a bit of old school compiling, I just don’t have the luxury of time to fiddle. It worked well with 12.04 out of the box.
I just thought I’d post this in case anyone else was having similar trouble - if you’ve double checked the configs, it’s quite possibly a lower level issue.
Maybe, but 2 days vs 50 minutes is a pretty big difference…
···
It is unlikely (though not impossible) that the problem is ubuntu specific. It could be your windows for example, is making better use of the disk.
I think I see you had 2 sata disks which would probably work best in a raid 0 setup. Perhaps windows has arranged them thus. Maybe also some other tweaks to get the best out of the filesystem. Good luck.
That is useful info for the forum for sure and I fully agree, thanks Lars!
Ultimately the server supports Linux – it’s just certified for different sistros. Also, I noticed a number of Ubuntu 16.04 users complaining of the same speed issue, so while Ubuntu will probably remain my go-to distro, in the case of these servers, I’m probably better off getting Suse or one of the other supported distros on before I take any more troubleshooting steps. For now it’s running fine and the future direction may be different – including cloud hosting to avoid this kind of hassle.
Subject: Re: [Dhis2-users] Major speed issue with pg_restore
Hi Edward,
this is besides the main point but make sure you create pg dumps without the analytics tables, and instead let DHIS 2 re-generate the analytics tables (which is faster as indexing is run in parallel).
Sorry for this slow response, time has not been on my side
Also, thanks Bob, Knut and Hannan for your responses.
So to fill everyone in, I went through postgres.conf with a fine toothed comb. @ Bob, Shared buffers were set to 3200 though I’d set them to 1600 for the restore as per this article’s suggestions:
Overall I’m convinced the PGSQL environment had nothing to do with the issue… what I had suspect is that the combination of Ubuntu 16.04 and our hardware was not a good match. I suspect the SATA channel in particular was the bottleneck.
To prove a point (I have two identical machines), I loaded Win Server 2012 R2 and and installed PGSQL 9.5.5 on the box and, without so much as tweaking a single file, I restored the dump within 50 minutes. We’re likely upgrading the platform soon so I’m not going to break my back troubleshooting what is likely an issue that won’t be there in 6 months time. Dell didn’t include Debian or Ubuntu on their list of supported OS for the hardware combination, though I have no reason to believe it couldn’t work with a little effort and a bit of old school compiling, I just don’t have the luxury of time to fiddle. It worked well with 12.04 out of the box.
I just thought I’d post this in case anyone else was having similar trouble - if you’ve double checked the configs, it’s quite possibly a lower level issue.
Subject: Re: [Dhis2-users] Major speed issue with pg_restore
It is unlikely (though not impossible) that the problem is ubuntu specific. It could be your windows for example, is making better use of the disk.
I think I see you had 2 sata disks which would probably work best in a raid 0 setup. Perhaps windows has arranged them thus. Maybe also some other tweaks to get the best out of the filesystem. Good luck.
That is useful info for the forum for sure and I fully agree, thanks Lars!
Ultimately the server supports Linux – it’s just certified for different sistros. Also, I noticed a number of Ubuntu 16.04 users complaining of the same speed issue, so while Ubuntu will probably remain my go-to distro, in the case of these servers, I’m probably better off getting Suse or one of the other supported distros on before I take any more troubleshooting steps. For now it’s running fine and the future direction may be different – including cloud hosting to avoid this kind of hassle.
Subject: Re: [Dhis2-users] Major speed issue with pg_restore
Hi Edward,
this is besides the main point but make sure you create pg dumps without the analytics tables, and instead let DHIS 2 re-generate the analytics tables (which is faster as indexing is run in parallel).
Sorry for this slow response, time has not been on my side
Also, thanks Bob, Knut and Hannan for your responses.
So to fill everyone in, I went through postgres.conf with a fine toothed comb. @ Bob, Shared buffers were set to 3200 though I’d set them to 1600 for the restore as per this article’s suggestions:
Overall I’m convinced the PGSQL environment had nothing to do with the issue… what I had suspect is that the combination of Ubuntu 16.04 and our hardware was not a good match. I suspect the SATA channel in particular was the bottleneck.
To prove a point (I have two identical machines), I loaded Win Server 2012 R2 and and installed PGSQL 9.5.5 on the box and, without so much as tweaking a single file, I restored the dump within 50 minutes. We’re likely upgrading the platform soon so I’m not going to break my back troubleshooting what is likely an issue that won’t be there in 6 months time. Dell didn’t include Debian or Ubuntu on their list of supported OS for the hardware combination, though I have no reason to believe it couldn’t work with a little effort and a bit of old school compiling, I just don’t have the luxury of time to fiddle. It worked well with 12.04 out of the box.
I just thought I’d post this in case anyone else was having similar trouble - if you’ve double checked the configs, it’s quite possibly a lower level issue.
Agree, the system has two independent disks, I hadn’t set up any mirroring or striping – I had basically just setup the OS in both cases and performed OS updates. Pretty much a vanilla config on both. My experience is that if the SATA channel drivers aren’t properly loaded, the disks will work in legacy mode which slows things down considerably since no interrupts are used. In the old days with the IDE channel, it was interrupt 14 and 15 for channel 1 and 2 but that’s no longer the case with SATA. To be honest I haven’t come across this before in Linux since most good distros have all the software required to run all but the newest hardware and given that these machines are a few years old, I would assume they are supported (but I imagine this may be something worth troubleshooting). Ultimately I do think online hosting for this project will probably be the best given the difficulty supporting any hardware in this location so we are exploring those options.
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.
“
It’s not this, since I had dumped using the default copy method:
**--attribute-inserts**
Dump data as **INSERT** commands with explicit column names (INSERT
INTO table (column, ...) VALUES ...). This will make restoration
very slow; it is mainly useful for making dumps that can be loaded
into non-PostgreSQL databases. However, since this option generates
a separate command for each row, an error in reloading a row causes
only that row to be lost rather than the entire table contents.
**--inserts**
Dump data as **INSERT** commands (rather than **COPY**). This will make
restoration very slow; it is mainly useful for making dumps that
can be loaded into non-PostgreSQL databases. However, since this
option generates a separate command for each row, an error in
reloading a row causes only that row to be lost rather than the
entire table contents. Note that the restore might fail altogether
if you have rearranged column order. The **--column-inserts** option is
safe against column order changes, though even slower.
