postgresql-setup.sh Enhancements

[rdlrt]

Versions The db-sync version (eg cardano-db-sync --version): 13.2.0.1 PostgreSQL version: 16.2

Build/Install Method The method you use to build or install cardano-db-sync: Release binaries

Run method The method you used to run cardano-db-sync (eg Nix/Docker/systemd/none): systemd

Additional context The file scripts/postgresql-setup.sh is currently lagging in compatibility:

• [x] As raised in #1659, when using exit-on-error flag against pg_restore, we now need to specify --no-owner and --role=<role> which wasnt required before, this role may need to be devised from PGPASSFILE (for socket, that would mean current user) OR it could be accepted as command line argument. Other alternative is adapt pg_dump to use no-owner too.
• [ ] When using disable-ledger flag, we do not expect to have lstate files, but absence of lstate file breaks the script. Allow this flag to be optional.
• [ ] Use binary option instead of SQL which paves way for ensuring only public schema is filtered to be added to snapshot. Thus, dbsync users can make use of different schemas to store their addendum on the top of dbsync. This would also help leverage internal compression options from postgres
• [ ] The snapshots should also contain the config to be able to easily identify compatibility for that snapshot with the flags that can be used (the logic to verify if snapshot is usable against dbsync instance isnt required, it's just to qualify snapshots usability).
• [ ] L112 assumes psql listing of particular database can be counted by filter "${PGDATABASE} " , tho it's common to add prefix to the datbase name, would be safer to have space on either side " ${PGDATABASE} ".

  count=$(psql -l "${PGDATABASE}" | grep " ${PGDATABASE} " | cut -d \| -f 3 | grep -c UTF8)

[Scitz0]

In addition to the above mentioned items, separate to that but also connected to dump/restore. I just wanted to add that I have found that from a performance perspective, its better to compress outside of pg_dump (-Z0). Additionally, we can leverage our multi-core cpu (-j and pigz -p) to speed up the process. pigz being an external application might be a bit controversial as it needs to be added to nix flow but parallel gzip compression does help greatly when the horsepower exists to utilize it.

Some example code for how I have dumped and restored with great performance gains:

# dump
db="explorer";
tmp_dir="/tmp/dump_${db}";
threads=$(( nproc - 1 ));
pg_dump -d ${db} -Fd -j ${threads} -Z0 -f "${tmp_dir}";
tar -cf - "${tmp_dir}/" | pigz -p ${threads} > "${tmp_dir}.tar.gz";

# restore
db="explorer";
tmp_dir="/tmp/dump_${db}";
threads=$(( nproc - 1 ));
pigz -p ${threads} -dc /tmp/dump_cexplorer.tar.gz | tar -C "${tmp_dir}" --strip-components 1 -xf -;
pg_restore -j ${threads} -Fd -O -d ${db} "${tmp_dir}";

Take it for what it is... I wanted to put it out there if found useful to possibly integrate (or parts of it) in the flow for dbsync dump/restore.