Allow to run the dump in parallel by dumping njobs tables simultaneously

[midestefanis]

It would be nice if the current image allows parallel dump (-j). I tried passing the parameter through POSTGRES_EXTRA_OPTS but it doesn't support current script and folder creation.

[midestefanis]

Something like this.

#Loop all databases
for DB in ${POSTGRES_DBS}; do
  #Initialize filename vers
  DFILE="${BACKUP_DIR}/daily/${DB}-`date +%Y%m%d-%H%M%S`"
  WFILE="${BACKUP_DIR}/weekly/${DB}-`date +%G%V`"
  MFILE="${BACKUP_DIR}/monthly/${DB}-`date +%Y%m`"
  #Create dump
  echo "Creating dump of ${DB} database from ${POSTGRES_HOST}..."
  pg_dump -Fd -f "${DFILE}" -j ${NUMBER_OF_JOBS} ${POSTGRES_HOST_OPTS} ${DB}
  #Compress dump
  tar -zcf "${DFILE}".tar.gz "${DFILE}"
  #Remove backup in folder
  rm -rf "${DFILE}"
  #Copy (hardlink) for each entry
  ln -vf "${DFILE}".tar.gz "${WFILE}".tar.gz
  ln -vf "${DFILE}".tar.gz "${MFILE}".tar.gz
  #Clean old files
  echo "Cleaning older than ${KEEP_DAYS} days for ${DB} database from ${POSTGRES_HOST}..."
  find "${BACKUP_DIR}/daily" -maxdepth 1 -mtime +${KEEP_DAYS} -name "${DB}-*.tar*" -exec rm -rf '{}' ';'
  find "${BACKUP_DIR}/weekly" -maxdepth 1 -mtime +${KEEP_WEEKS} -name "${DB}-*.tar*" -exec rm -rf '{}' ';'
  find "${BACKUP_DIR}/monthly" -maxdepth 1 -mtime +${KEEP_MONTHS} -name "${DB}-*.tar*" -exec rm -rf '{}' ';'
done

[prodrigestivill]

Added support in the latest version.

Use the image with:

BACKUP_SUFFIX="-dump"
POSTGRES_EXTRA_OPTS="-Z9 -Fd -j8"

I would appreciate if you can test that cleaning works acordingly when using the -Fd option.
Feel free to reopen the issue for any related issue.

[midestefanis]

Hello @prodrigestivill , first of all thank you very much for the answer.

I leave you an update of the tests I did:

It worked fine with the new parameters. The only problem I see is that the new dump is not compressed, it remains in directory format. What I do with those dumps of my databases is upload them to the cloud. The dump in this new format increased the size of my backups by 166%.

I would like to know if it is possible to do this fix or do you consider that what I say makes sense. That will let me know if I should take care of this compression or I will wait for you in your next release.

Many thanks in advance!

I stay tuned for news.

[prodrigestivill]

Hello @midestefanis,

Beeing the backup in separated files in a folder doesn't mean the backup is not compressed.
Each file can still be compressed individually.
This behaviour can be enabled by having -Z9 into POSTGRES_EXTRA_OPTS.

I tested myself with my suggested configuration before:

BACKUP_SUFFIX="-dump"
POSTGRES_EXTRA_OPTS="-Z9 -Fd -j8"

Once executed it generates a folder with each file compressed using gzip and with the extension .dat.gz.

[midestefanis]

Hi @prodrigestivill again. I think I was not clear enough. What I mean is the compression of the resulting folder (with the internal .gz).

I leave an example of a small DB:

The last backup is with the new configuration and, as can be seen, it increases the size by 166%.

Then if I compress that directory. The size happens to be similar to the previous ones (which is cheaper for me to store in the cloud, especially when we talk about GBs of storage and multiple DBs).

tar -zcf ****-20200904-173736-dump.tar.gz ***-20200904-173736-dump
2.4M    ***-20200904-041500.sql.gz
6.4M    ***-20200904-173736-dump
2.6M    ***-20200904-173736-dump.tar.gz

Besides being cheaper in my opinion, it is also easier to manipulate a compressed file than a folder, don't you consider the same?

It is only a suggestion and an exchange of opinion. I can solve it by compressing that directory myself, but I was interested in sharing my point of view with you since this project is looking very good. Let me know if you think the same as me!

Thank you very much in return for your answer. We are in touch!