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.
Subscribe to:
Post Comments (Atom)
What's in an empty table?
How much storage does an empty table in Postgres take? This is a post about Postgres tables that store ... well basically ... Nothing . The...
-
(Please scroll down to the read the entire-post to know more) Documents required for Christian Marriage Registration (a.k.a. Documents... -
My patch (allowing a non-super-user to dump Globals) just got committed to Postgres 10.0. Besides the use mentioned above, this patch al...
-
pg_tle - A Must-Know for Developers PostgreSQL is a fantastic database, packed with features. But sometimes, you need to add a little somet...
4 comments:
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.
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.
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.
Top Black Friday Deals for 2020
Post a Comment