31 May 2017

Patch: Using --no-comments with pg_dump

Recently I submitted a patch for review that allows a non-superuser to practically use a backup taken from pg_dump.

Currently it is a kludge (and well known at that - Ref 1 / Ref 2 / Ref 3 / Ref 4) but since it's (the ideal solution) too big a byte to chew and not in high-demand, it has seen little to no traction in the past decade.

This patch should allow the above. But more importantly, it should also allow regular users of AWS RDS Postgres as well as Google Cloud Postgres databases (who do not get SuperUser access by-design) to reliably use the backups, instead of tinkering with the backup SQLs and remove things like COMMENT ON EXTENSION for it to even run during restoration.

The bad news is that since Postgres 10 has already branched off, I doubt this would see the light of the day (if at all) any time before Postgres 11 (unless there is consensus that it's helpful enough and gets 'back-patched' to Postgres 10 to be released around September 2017).

Update (3rd Oct 2017):
This is now a part of my PsqlForks branch. You can check the related commit here.

Update (26th Jan 2018):
This is now part of the official Postgres v11 branch. You can check the related commit here.

horntail it is :)

I had been planning to contribute to Postgres Buildfarm by running it on one of my VMs and finally got that done... and horntail it is.

A few things to note, before it fades from (my) memory:
  • It (obviously) requires quite a bunch of libraries, especially if you're using the defaults (for e.g. with ssl / krb5 etc..)
    • You may either disable some of those exotic options (not ideally recommended)
    • Or, you may use a silo'ed VM just for this purpose so that it doesn't slow your regular work
    • I did neither (of the above) but didn't care about performance of this VM
      • Probably would enable more Options / Branches down the line and re-submit
  • My distro was Ubuntu and so YMMV, but the packages weren't very obvious. If you see something fail and you check logs, you'd see that 'abc' was missing, but wouldn't have the faintest idea which package satisfied that
    • Some hiccup missing libraries were (These may be obvious for most, but were new to me ):
      • gss_init_sec_context
      • msgfmt
      • tclConfig
    • It'd be good if someone could catalog the packages required
      • For all Unix variants - obviously easier said that done, but
        • It's possible that there may be more people willing (like me) but unable to scale all the walls
      • At least the super-set of what's required when all flags are enabled, from which people could try what's needed for his / her set of enabled flags
  • As it always is, the documentation is relatively clear, just that persistence (especially for exotic Linux variants / issues) is needed in large quantities.
All the best :)

Find Database DNS / Endpoint via SQL

How to get Database identifier using SQL Often there is a need for client programs to find "where am I logged into?". This blog po...