PostgreSQL 9.5 pg_dump slow and no start/stop/autostart...?

Hello

I upgraded our DB server this weekend and ran into a few issues.

1. In the 9.5 installation there's no Start and Stop server icons. And it also doesn't autostart on reboot. How does people deal with this? Found a few launchd option on internet for it but curious to here if there's an official approach from BMD..?

2. pg_dump takes more than twice as long with 9.5 than 8.4. Not sure why and can't find anything about it when googling. I use the same script for auto-backups with 9.5 as I had on 8.4. This is the line that does the dump:

Code: Select all

${BINARY_PATH}/pg_dump --host=localhost --user=postgres --no-password --blobs --format=custom --verbose --file=${pg_dump_filename}_${database}.backup ${database}

In 9.5 there's a parallell flag for pg_dump but you have to dump it into another format than which I assume isn't compatible with a db-restore from within Resolve, or?

To put some actual numbers on how long it takes, in 8.4 we did a full dump in 210 minutes. After upgrade of server it takes. 644 minutes. So it is a real problem and can't help to wonder if it only affects pg_dump or other DB transactions as well.

pg_dump still uses 100% cpu which in apple language is one full core. Was the same in 8.4.

The upgrade procedure was the BMD one using their tools to back up form 8.4, upgrade to 9.5, restore from backup.

So a quick update on this since this might be of interest to others that does the 8.4 => 9.5 server upgrade on macOS.

After some help from the guys at the pgres-performance mailing list I used Instruments, from xcode, to analyze the pg_dump process when it was running. In a vm I compared the 9.5 pg_dump with the 8.4 one to see if anything was different. And my amateur conclusion is that there's something with the bundled zlib in the compiled binaries that BM ships that's suboptimal for our server.

9.5 use an included zlib, 1.2.8 (/Library/PostgreSQL//9.5/include/zlib.h). 8.4 seems to use and link to a system library instead, libz.1.dylib.

With this in mind I tried without compression, --compression=0, and ran a bunch of performance tests. Have in mind that no compression flag and the default is a compression level of 6. And yes with no compression the, extreme, slowness is gone and dump time went from 730 minutes to 84 minutes but at the same time the total dump size went from 46GB to 243GB.

I've played some more with it and done a bunch of test and now I've landed in a solution where i dump uncompressed but I pipe it to 'pigz' which is capable of doing a threaded compression. With that solution I'm now down to a 56 minute dump and a size of 47 GB. Somewhat of an improvement! And no matter if you have issues with the dump or not I would recommend skipping pg_dump's compression and do it with pigz or some other threaded compressor. This is the current line in my script.

${BINARY_PATH}/pg_dump --host=localhost --user=postgres --no-password --blobs --format=custom --compress=0 --verbose "${database}" | ${pigz_bin} > ${pg_dump_filename}_${database}_pigz.backup.gz

A few variables there but you get the point in broad strokes. =)