Split opendrr-boundaries.{sql,dump} into 2GB chunks before uploading as release assets

[anthonyfok]

Task

To split opendrr-boundaries.sql into 2GB chunks (e.g. opendrr-boundaries.sql.00 and opendrr-boundaries.sql.01) before uploading as release assets during GitHub Actions workflow run.

Background

With the addition of hexbin_1km, PostgreSQL database backup file opendrr-boundaries.sql (from Will) and the equivalent opendrr-boundaries.dump (regenerated in GitHub Actions workflow) now both exceed the size limit of GitHub release asset file. The latest Actions run for v1.3 failed with the following error at the softprops/action-gh-release@v1:

gpkg-to-pgdump File size (2490467314) is greater than possible Buffer: 2147483647 bytes

Observations

• According to https://docs.github.com/en/repositories/releasing-projects-on-github/about-releases#storage-and-bandwidth-quotas:

  Each file included in a release must be under 2 GB. There is no limit on the total size of a release, nor bandwidth usage.

• opendrr-boundaries.{sql,dump}, as PostgreSQL database dumps, are already compressed. Compressing them with xz provides hardly any space saving.
• 2490467314 is the file size (in bytes) of opendrr-boundaries.dump that was generated during the run.
• 2147483647 = 2 * 1024³ - 1 = 2 GiB - 1 byte
• When trying to upload an asset file via the GitHub web UI, uploading a 2 GiB (2147483648-byte) file fails silently, while a 2147483647-byte file does work. split -b 2147483647 -d --verbose opendrr-boundaries.sql opendrr-boundaries.sql.
• 2147483647 is the hard limit probably because GitHub engineers uses something like uint32 for the file size?
• Since 2 GiB does not work, let's go with 2 GB (2,000,000,000 bytes) for a nice, even number and some factor of safety:

  $ split -b 2GB -d --verbose opendrr-boundaries.sql opendrr-boundaries.sql.

See also

• Slack direct messages between @wkhchow and @anthonyfok on Wednesday, February 2nd, 2022

[wkhchow]

Thanks for the detailed write up

[anthonyfok]

You are very welcome, Will! And sorry for my delay in getting this implemented: there are a few more bugs I'd like to get fixed in gpkg-to-pgdump.yml; I'll be filing another issue, and hope to get a PR in before tomorrow.

Incidentally, this issue (about splitting up the database dump into 2GB chunks) is not "fortunately" not blocking opendrr-api because add_data.sh is still reading from https://opendrr.eccp.ca/file/OpenDRR/opendrr-boundaries.dump which is up-to-date, 2.5 GB from v1.3.

[anthonyfok]

The splitting of opendrr-boundaries.sql 2GB (2,000,000,000-byte) chunks (named opendrr-boundaries.sql.00, opendrr-boundaries.sql.01, etc.) and uploading as release assets has been implemented in commit cd772c0 in the test_hexbin_unclipped branch, and has been tested in my fork in with a test release tag, see https://github.com/anthonyfok/boundaries/releases/tag/v1.3.1-test_hexbin_unclipped

The support for downloading these 2GB chunks from OpenDRR/boundaries release asset is implemented in commit f724397 to python/add_data.sh in OpenDRR/opendrr-api.