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.


David J. said...

The comments for Ref 3 and 4 are simple inline commentary while the comments for Ref 1 and 2 are actual SQL entities. Mixing them together doesn't seem desirable nor necessary since inline comment should not actually cause problems if the client software is written correctly. All they do is bloat the dump but with compression I cannot see them being that big a deal.

Also, given the history of this particular complaint, it would be counter to project policy to incorporate it into v10 at this point. It is a new feature and feature freeze for 10 is long past. Popularity and support doesn't enter into it.

David J.

Robins Tharakan said...

It's completely understandable for it to not be included in PGv10, which is why I mentioned the point...

Interesting to realize that 'popularity' isn't a metric for a back-patch. And so although this didn't occur to me earlier, when put this way, does make sense. Thanks for clarifying.

David Fetter said...

I've written kludges of this type before. You've now convinced me that the bug isn't in the pg_dump code, but in RDS Postgres and similar for causing pg_restore to fail by default. I'll see about getting these reported and acted on.

Unknown said...

Top Black Friday Deals for 2020

Unlock PostgreSQL Superpowers with pg_tle

pg_tle  - A Must-Know for Developers PostgreSQL is a fantastic database, packed with features. But sometimes, you need to add a little somet...