Re: [HACKERS] list of credits for release notes
On Mon, Oct 2, 2017 at 02:12:50PM -0400, Stephen Frost wrote: > > How should this be handled for the Postgres 11 release notes? > > Ideally, we would let the individuals choose how to be recognized in > release notes, and anywhere else we recognize them. We have the start > of that in https://postgresql.org/account/profile but that isn't very > easily tied to things in the commit history or elsewhere, yet. I'd > suggest that we try to improve on that by: My smaller question is how will this list be generated in PG 11? From the commit log when the release notes are created, or some other method? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] list of credits for release notes
On Fri, Sep 29, 2017 at 12:00:05PM -0400, Peter Eisentraut wrote: > On 9/29/17 11:35, Robert Haas wrote: > > On Wed, Sep 27, 2017 at 8:29 PM, Michael Paquier > > <michael.paqu...@gmail.com> wrote: > >> Looking at this list, the first name is followed by the family name, > >> so there are inconsistencies with some Japanese names: > >> - Fujii Masao should be Masao Fujii. > >> - KaiGai Kohei should be Kohei Kaigai. > > > > But his emails say Fujii Masao, not Masao Fujii. > > > > KaiGai's case is a bit trickier, as his email name has changed over time. > > Yes, I used the form that the person used in their emails. How should this be handled for the Postgres 11 release notes? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #14825: enum type: unsafe use?
On Tue, Sep 26, 2017 at 05:32:15PM -0400, Tom Lane wrote: > Bruce Momjian <br...@momjian.us> writes: > > On Tue, Sep 26, 2017 at 04:07:02PM -0400, Tom Lane wrote: > >> Any other votes out there? > > > Well, I was concerned yesterday that we had a broken build farm so close > > to release. (I got consistent regression failures.) I think PG 11 would > > be better for this feature change, so I support reverting this. > > I'll take the blame for (most of) yesterday's failures in the v10 > branch, but they were unrelated to this patch --- they were because > of that SIGBUS patch I messed up. So that doesn't seem like a very > applicable argument. Still, it's true that this seems like the most > consequential patch that's gone into v10 post-RC1, certainly so if > you discount stuff that was back-patched further than v10. Oh, I couldn't untangle that the regression failures were unrelated to enums, so please ignore my opinion. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #14825: enum type: unsafe use?
On Tue, Sep 26, 2017 at 04:07:02PM -0400, Tom Lane wrote: > Andrew Dunstan <andrew.duns...@2ndquadrant.com> writes: > > On 09/26/2017 02:37 PM, Tom Lane wrote: > >> ... and the buildfarm's not too happy. It looks like force_parallel_mode > >> breaks all the regression test cases around unsafe enums; which on > >> reflection is unsurprising, because parallel workers will not have access > >> to the parent's blacklist hash, so they will think unsafe values are safe. > > > I think I would mark enum_in and friends as parallel-restricted. Yes I > > know it would involve a cat version bump, so I'll understand if that's > > not acceptable, but it seems to me the best of a bad bunch of choices. > > Second choice might be turning off parallel mode if the hash exists, but > > I'm unclear how that would work. > > Meh. I'm starting to slide back to my original opinion that we should > revert back to 9.6 behavior. Even if a post-RC1 catversion bump is OK, > making these sorts of changes a week before GA is not comfort inducing. > I'm losing faith that we've thought through the issue thoroughly, and > there's no longer time to catch any remaining oversights through testing. > > Any other votes out there? Well, I was concerned yesterday that we had a broken build farm so close to release. (I got consistent regression failures.) I think PG 11 would be better for this feature change, so I support reverting this. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] md5 still listed as an option in pg_hba.conf.sample
On Tue, Sep 26, 2017 at 10:23:55AM -0700, Mark Dilger wrote: > The comment that I think needs updating is: > > # METHOD can be "trust", "reject", "md5", "password", "scram-sha-256", > # "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert". > > The "md5" option no longer works, as discussed in other threads. Uh, I think that "md5" still works just fine. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [GENERAL] [HACKERS] USER Profiles for PostgreSQL
On Tue, Sep 19, 2017 at 01:28:11PM -0400, Stephen Frost wrote: > Tom, > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > > chiru r <chir...@gmail.com> writes: > > > We are looking for User profiles in ope source PostgreSQL. > > > For example, If a user password failed n+ times while login ,the user > > > access has to be blocked few seconds. > > > Please let us know, is there any plan to implement user profiles in > > > feature > > > releases?. > > > > Not particularly. You can do that sort of thing already via PAM, > > for example. > > Ugh, hardly and it's hokey and a huge pain to do, and only works on > platforms that have PAM. > > Better is to use an external authentication system (Kerberos, for > example) which can deal with this, but I do think this is also something > we should be considering for core, especially now that we've got a > reasonable password-based authentication method with SCRAM. Does LDAP do this too? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PG 10 release notes
On Tue, Sep 19, 2017 at 12:30:01PM -0400, Tom Lane wrote: > "'Bruce Momjian'" <br...@momjian.us> writes: > > On Tue, Sep 19, 2017 at 12:22:39PM -0400, Tom Lane wrote: > >> We don't normally release-note documentation changes. If this > >> wasn't purely a documentation change, then I was probably in error > >> to decide it didn't need to be in the notes. > > > It was purely a documentation change, but it was a documented change in a > > long-standing and popular practice of not using too many shared buffers > > on Windows, so I thought it wise to add it. > > Well, if the intent of the note was to encourage people to raise > shared_buffers, it didn't do a very good job of that as written, > because I sure didn't understand it that way. Do you have any suggestions since it is not a code change that I can point to? My guess is that the limitation was removed years ago, but we only found out recently. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PG 10 release notes
On Tue, Sep 19, 2017 at 12:22:39PM -0400, Tom Lane wrote: > "'Bruce Momjian'" <br...@momjian.us> writes: > > I am sure Tom can explain his reasoning. > > We don't normally release-note documentation changes. If this > wasn't purely a documentation change, then I was probably in error > to decide it didn't need to be in the notes. It was purely a documentation change, but it was a documented change in a long-standing and popular practice of not using too many shared buffers on Windows, so I thought it wise to add it. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PG 10 release notes
On Thu, Sep 14, 2017 at 03:13:50AM +, Tsunakawa, Takayuki wrote: > It's embarrassing to ask about such a trivial thing, but I noticed > the following line was missing in the latest release note, which was > originally in Bruce's website: > > Remove documented restriction about using large shared buffers on > Windows (Takayuki Tsunakawa) > > Is this intended? I don't know. The original text was: Remove documented restriction about using large shared buffers on Windows (Takayuki Tsunakawa) and was removed in this commit: commit 749eceff4a1f9740391b126c81af9fd4bf3b1eaa Author: Tom Lane <t...@sss.pgh.pa.us> Date: Sun Jul 9 20:11:21 2017 -0400 Doc: desultory copy-editing for v10 release notes. Improve many item descriptions, improve markup, relocate some items that seemed to be in the wrong section. I am sure Tom can explain his reasoning. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)
On Sat, Sep 16, 2017 at 11:36:40PM +0200, Andreas Joseph Krogh wrote: > På lørdag 16. september 2017 kl. 18:34:51, skrev Bruce Momjian < > br...@momjian.us>: > No. If you ran initdb with --waldir on the new primary, you will create > a symbolic link in the PGDATA directory, and a directory outside of > PGDATA for storing the WAL. When you run rsync on the new primary > PGDATA directory, you will copy the symlink in the PGDATA directory, but > it will point to probably nothing on the standby. > > > > The misunderstanding here comes from the fact that I used pg_upgradecluster > like this: > pg_upgradecluster --method=upgrade --link 9.6 main > > and it didn't issue initdb with --waldir on the new cluster (because > pg_upgradecluster issues initdb for you), so pg_wal ended up in $PGDIR because > pg_upgradecluster didn't figure out the old cluster was initialized with > --xlogdir. This is why I thought i made sense mentioning that one had to move > pg_wal manually. > > I know it's debian-stuff, but maybe it's worth mentioning pg_upgradecluster > somewhere and recommend not using it? It seems to start the new cluster > automatically and when upgrading standbys section 10 tells you not to do that. So you didn't really follow the instructions, but instead are trying to use the standby part of the instructions and found a problem with the way pg_upgradecluster handled it. We really can't document this. It would be good to report the bug to pg_upgradecluster developers though. Yes, I can see rsync not working that case. > > should be clearly pointed out that copying pg_wal is only needed in > those > > cases, and that it can be done with whatever network-copying procedure > you're > > familiar with, ie. scp/rsync. This step is not similar to the steps > required > > for copying tablespaces outside $PGDATA, so it's worth documenting > explicitly. > > Maybe also telling users to ensure the synlink (in $PGDATA) to pg_wal on > > standby points to pg_wal. > > Why tell them new instructions when the rsync instructions work fine? > What is the value? > > > The rsync instructions mentioned in 10.F all address the --link scenario and > use "--delete --hard-links --size-only", and "merge 2 source-dirs into one", > which isn't relevant when copying pg_wal. > > This sentence: > "If you have relocated pg_wal outside the data directories, rsync must be run > on those directories too." > implies one must follow the rsync pattern elsewhere in 10.F, which isn't > really > true. Maybe re-wording it to: > "If you have relocated pg_wal outside the data directories you must copy it > over to the new standby, and ensure the symlink from $PGDATA points to it" > helps? We can't document every possible configuration, especially if a secondary tool is used in the middle. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PoC plpgsql - possibility to force custom or generic plan
On Wed, Sep 6, 2017 at 10:43:39AM -0400, Robert Haas wrote:
> helps. I don't think we can just indefinitely continue to resist
> providing manual control over this behavior on the theory that some
> day we'll fix it. It's been six years and we haven't made any
> significant progress. In some cases, a long delay without any
> progress might just point to a lack of effort that should have been
> applied, but in this case I think it's because the problem is
> incredibly hard.
Add to that, we didn't even document the behavior until last year:
commit fab9d1da4a213fab08fe2d263eedf2408bc4a27a
Author: Bruce Momjian <br...@momjian.us>
Date: Tue Jun 14 16:11:46 2016 -0400
document when PREPARE uses generic plans
Also explain how generic plans are created.
Link to PREPARE docs from wire-protocol prepare docs.
Reported-by: Jonathan Rogers
Discussion:
https://www.postgresql.org/message-id/flat/561E749D.4090301%40socialserve.com
--
Bruce Momjian <br...@momjian.us>http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)
On Sat, Sep 16, 2017 at 06:11:17PM +0200, Andreas Joseph Krogh wrote: > I'm a little unsure what scenario we're trying to describe here. Copying the > pg_wal separately (for which there's no need optimizing for) is only needed if > you've moved it out of $PGDATA _after_ running pg_upgrade, IIUC. So, I think > it No. If you ran initdb with --waldir on the new primary, you will create a symbolic link in the PGDATA directory, and a directory outside of PGDATA for storing the WAL. When you run rsync on the new primary PGDATA directory, you will copy the symlink in the PGDATA directory, but it will point to probably nothing on the standby. > should be clearly pointed out that copying pg_wal is only needed in those > cases, and that it can be done with whatever network-copying procedure you're > familiar with, ie. scp/rsync. This step is not similar to the steps required > for copying tablespaces outside $PGDATA, so it's worth documenting explicitly. > Maybe also telling users to ensure the synlink (in $PGDATA) to pg_wal on > standby points to pg_wal. Why tell them new instructions when the rsync instructions work fine? What is the value? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)
On Thu, Sep 14, 2017 at 03:12:50PM -0400, Bruce Momjian wrote: > I talked with Stephen about this on IM. The issue is that if you don't > do --delete, and there are files in the primary that are not in the > standby, they are copied, but files in the standby and not in the > primary are kept. This could lead to mixed primary/standby log files, > or worse. Using --delete means the new standby exactly matches the new > primary and all the steps you need to adjust after a base backup are the > same. > > I added a mention of rsync --dry-run per Stephen's suggestion. > > I have also added a paragraph from Magnus that I developed via IM that > explains that you can use rsync to upgrade one standby from another > standby, if the standby has not been started. Patch applied through 9.5. Updated docs at: http://momjian.us/pgsql_docs/pgupgrade.html -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)
On Fri, Sep 15, 2017 at 01:23:45AM +0200, Andreas Joseph Krogh wrote: > I tested upgrading from 9.6 to 10 now, using pg_upgrade, and pg_upgrade > creates > the new data-dir with pg_wal "in it" (just like regular initdb), so pg_upgrade > seems not to care about where the old version's pg_xlog was. You have to move > (by symlinking) pg_wal to a separate location manually *after* running > pg_upgrade on the master. No special handling is needed when rsync'ing it over > to the standby, so it doesn't need any --hard-links or --size-only, correct? > > Given the path, on the upgraded primary, to pg_wal is /custom/path/to/pg_wal, > the rsync command will be: > > rsync --archive --delete /custom/path/to/pg_wal standby.example.com:/custom/ > path/to/pg_wal > > I think it's useful to mention this to eliminate any doubt. > > I also think it's worth mentioning that you have to manually move pg_wal to a > custom location after running pg_upgrade as it will not preserve/use the old > path. Thinking some more, you are right that there is no need to rsync the _old_ primary WAL directory since it is the same on the standby old WAL directory, and there are no links between the old and new WAL directories, so you could just do the new one, or just copy it and not even use rsync. However, I think it adds complexity to try to optimize the copy of the WAL files and we are better just requiring them to use the same steps for WAL copy that they _must_ use for the data directory and tablespaces because of the links between old and new files there. Agreed? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)
On Thu, Sep 14, 2017 at 09:21:25PM -0400, Stephen Frost wrote: > Michael, all, > > * Michael Paquier (michael.paqu...@gmail.com) wrote: > > On Fri, Sep 15, 2017 at 8:23 AM, Andreas Joseph Krogh > > <andr...@visena.com> wrote: > > > I tested upgrading from 9.6 to 10 now, using pg_upgrade, and pg_upgrade > > > creates the new data-dir with pg_wal "in it" (just like regular initdb), > > > so > > > pg_upgrade seems not to care about where the old version's pg_xlog was. > > > You > > > have to move (by symlinking) pg_wal to a separate location manually > > > *after* > > > running pg_upgrade on the master. > > > > That's true, this should definitely be mentioned in the documentation. > > Uh, this seems like something that should be *fixed*, not documented. > That initdb accepts an alternative location for pg_xlog/pg_wal now > raises that to a first-class feature, in my view, and other tools should > recognize the case and deal with it correctly. > > Of course, that having been said, there's some technical challenges > there. One being that we don't really want to mess with the old > cluster's WAL during pg_upgrade. Frustratingly, we have a way to deal > with that case today for tablespaces, it was just never applied to WAL > when we started allowing WAL to be stored elsewhere (formally). That > seems like it was a mistake to me. > > Then again, the more I think about this, the more I wonder about putting > more-or-less everything in PGDATA into per-catalog-version directories > and making everything self-contained. Part of the ugly bit with the > rsync is exactly that we have to go up an extra level for the data > directories themselves, and users can actually put them anywhere so > there might not even *be* a common parent directory to use. I am going to need to outline where I think we are before I can suggest a solution. What we did with the tablespace directory is to use the catalog version _inside_ the tablespace directory, e.g.: /vol1/pg_tblsp/PG_9.5_201510051 /vol1/pg_tblsp/PG_9.6_201608131 We did not do this for the WAL directory because by _default_ it is in PGDATA, which is major-version specific. Where this breaks is when the initdb --waldir option is used. We could have created a major version subdirectory inside the directory specified by --waldir, but that would have made the PGDATA contents and the --waldir differ, and I think it would have been confusing. What we have now is the problem that perhaps people are not creating major-version-specific names for --waldir. I am not sure how anyone is doing an upgrade except for dumping the data, deleteing the old cluster and the old WAL directory, recreating the new cluster and new WAL directory, and then loading the data. Because pg_upgrade needs to have the old and new servers running, having different external WAL directories for each cluster is a requirement. We have not had any complaints about this so I am confused why it is now an issue just because of rsync. If they created separate WAL directories on the primary, they will need separate ones on the standby, the symlinks will be copied, and they need to use rsync to copy stuff. There are no hard links in there, but using the link option should be harmless. > > An improvement could be done as well here for pg_upgrade: when using > > --link, the new PGDATA created could consider as well the source > > pg_wal and create a link to it, and then clean up its contents. I am > > not completely sure if this would be worth doing as people are likely > > used to the current flow though. The documentation needs to outline > > the matter at least. > > No, one of the baseline requirements of pg_upgrade is to *not* screw > with the existing cluster. Removing its WAL or "cleaning it up" > definitely seems like it's violating that principle. > > I tend to agree that it'd be good for the documentation to address this, > but this is all really getting to be a bit much for a manpage to be able > to handle, I think.. Yes, I am struggling with this too. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)
On Fri, Sep 15, 2017 at 01:23:45AM +0200, Andreas Joseph Krogh wrote: > I tested upgrading from 9.6 to 10 now, using pg_upgrade, and pg_upgrade > creates > the new data-dir with pg_wal "in it" (just like regular initdb), so pg_upgrade > seems not to care about where the old version's pg_xlog was. You have to move > (by symlinking) pg_wal to a separate location manually *after* running > pg_upgrade on the master. No special handling is needed when rsync'ing it over > to the standby, so it doesn't need any --hard-links or --size-only, correct? What rsync is going to do is to reproduce the directory structure of the old cluster _in_ the standby's old cluster, and the structure of the new cluster on the standby's new cluster. If you had the WAL directory relocated in the new cluster, the relocation symbolic link will be reproduced by rsync, but the directory it points _to_ will not be copied, so it will point to nothing. Of course, of both old and new clusters share the same WAL directory, which I think is impossible, things would get very confusing quickly. I will reply to this now in a later email. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)
On Wed, Sep 13, 2017 at 07:39:31PM +0200, Michael Banck wrote: > On Tue, Sep 12, 2017 at 07:38:40PM -0400, Stephen Frost wrote: > > Further, really, I think we should provide a utility to do all of the > > above instead of using rsync- and that utility should do some additional > > things, such as: > > > > - Check that the control file on the primary and replica show that they > > reached the same point prior to the pg_upgrade. If they didn't, then > > things could go badly as there's unplayed WAL that the primary got > > through and the replica didn't. > > > > - Not copy over unlogged data, or any other information that shouldn't > > be copied across. > > > > - Allow the directory structures to be more different between the > > primary and the replica than rsync allows (wouldn't have to have a > > common subdirectory on the replica). > > > > - Perhaps other validation checks or similar. > > > > Unfortunately, this is a bit annoying as it necessairly involves running > > things on both the primary and the replica from the same tool, without > > access to PG, meaning we'd have to work through something else (such as > > SSH, like rsync does, but then what would we do for Windows...?). > > Maybe pg_rewind's mechanism could be partially reused for this as it > seems to accomplish something vaguely similar AIUI? pg_rewind works at the WAL level while this is at the file system level. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)
On Thu, Sep 14, 2017 at 08:49:24PM +0200, Andreas Joseph Krogh wrote: > I think the tablespace example is clear enough to modify for WAL and we > instruct them right below that example to do WAL. > > > Well, it's not following the exact same structure as there's no > "version-directory" in pg_xlog, so the "rsync the version-dirs into it's > parent > on the target" isn't what's happening. > > That's why I think this makes sense to mention for the sake of a complete > example: > > rsync --archive --delete --hard-links --size-only /vol1/postgres/9.6/pg_xlog \ > /vol1/postgres/10/pg_wal standby.example.com:/vol1/postgres/10/pg_wal Well, there is technically no need for version directories in pgdata either --- installers just create them. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)
On Wed, Sep 13, 2017 at 12:16:33PM -0400, Stephen Frost wrote: > Bruce, > > * Bruce Momjian (br...@momjian.us) wrote: > > I have applied the attached patch to show examples of using rsync on > > PGDATA and tablespaces, documented that rsync is only useful when in > > link mode, and explained more clearly how rsync handles links. You can > > see the results here: > > > > http://momjian.us/pgsql_docs/pgupgrade.html > > > > Any more improvements? > > First off, I'd strongly suggest that we make "Step 1" in the pg_upgrade > process be "take a full backup and verify that you're able to restore it > successfully and without corruption." I am hesitant to add pg_upgrade-specific nanny language but if we want to review all upgrade methods and make recommendations, we can do that. If we need to add more --link-specific warnings, please suggest that. Thanks. > I don't particularly care for how this seems to imply that the Rsync > method is "the" method to use when --link mode is used with pg_upgrade. Agreed. I have added new text in the attached patch to make it clear that non-rsync is an option and is easier. > I'd reword the section title to be along these lines: > > If you have streaming replicas or log-shipping standby servers then they > will also need to be updated. The simplest way to accomplish this is to > simply rebuild the replicas from scratch once the primary is back > online. Unfortunately, that can take a long time for larger systems as > the data has to be copied from the primary to each replica in the > environment. If --link mode was used with pg_upgrade, the Latest > checkpoint location matches between the primary and the replica(s) (as > discussed in Step 8), the rsync utility is available, and the existing > data directory and new data directory on the replica are able to be in a > common directory on the same filesystem (as is required on the primary > for --link mode to be used), then an alternative method may be used to > update the replicas using rsync which will generally require much less > time. > > Note that this method will end up needlessly copying across temporary > files and unlogged tables. If these make up a large portion of your > database, then rebuilding the replicas from scratch may be a better > option. > > With this method, you will not be running pg_upgrade on the standby > servers, but rather rsync on the primary to sync the replicas to match > the results of the pg_upgrade on the primary. Do not start any servers > yet. If you did not use link mode, skip the instructions in this > section and simply recreate the standby servers. > > This method requires that the *old* data directory on the replica be in > place as rsync will be creating a hard-link tree between the old data > files on the replica and the new data directory on the replica (as was > done by pg_upgrade on the primary). Sorry, I didn't use any of the above text. It seems to be a step backward in clarity. > a. Install the new PostgreSQL binaries on standby servers. > > ... > > b. Make sure the new standby data directories do not exist > > If initdb was run on the replica to create a new data directory, remove > that new data directory (the rsync will recreate it). Do *not* remove > the existing old data directory. I clarified "new data directory" in the patch. > c. Install custom shared object files > > ** I would probably move this up to be step 'b' instead, and make step > 'b' be step 'c' instead. Why move it? The current ordering seems more logical. > d. Stop standby servers > > ... > > *new* > e. Verify/re-verify that Latest checkpoint location in pg_controldata >on the replica matches that of the primary (from before the primary >was upgraded with pg_upgrade). I added text in the pg_controldata paragraph to mention which standby upgrade method is references. Repeating the pg_controldata check seems pointless here. > f. Save configuration files > > ** this should have a caveat that it's only necessary if the config > files are in the data directory. I clarified "data directory" in the patch. > g. Run rsync > > ** I am having a hard time figuring out why --delete makes sense here. > There shouldn't be anything in the new data directory, and we don't > actually need to delete anything in the old data directory on the > replica, so what are we doing suggesting --delete be used? Strikes me > as unnecessairly adding risk, should someone end up doing the wrong > command. Also, again, if I was doing this, I'd absolutely run rsync > with --dry-run for starters and review what it is going to do and make > sure that's cons
Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)
On Wed, Sep 13, 2017 at 04:31:09PM +0200, Andreas Joseph Krogh wrote: > På onsdag 13. september 2017 kl. 15:26:27, skrev Bruce Momjian < > br...@momjian.us>: > > On Wed, Sep 13, 2017 at 01:35:17AM +0200, Andreas Joseph Krogh wrote: > [snip] > > I know I'm being a little nitty-gritty here, but if it helps me > understand it > > might help others. > > I have applied the attached patch to show examples of using rsync on > PGDATA and tablespaces, documented that rsync is only useful when in > link mode, and explained more clearly how rsync handles links. You can > see the results here: > > http://momjian.us/pgsql_docs/pgupgrade.html > > Any more improvements? > > > Very nice! > > For sake of completeness I think an example of running rsync when having > pg_wal > located outside the data directories would be helpful. Especially an example > upgrading from 9.6 to 10 because of the name-change of pg_xlog -> pg_wal. I think the tablespace example is clear enough to modify for WAL and we instruct them right below that example to do WAL. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)
On Wed, Sep 13, 2017 at 01:35:17AM +0200, Andreas Joseph Krogh wrote: > På onsdag 13. september 2017 kl. 01:00:20, skrev Bruce Momjian < > br...@momjian.us>: > (I know this isn't exactly -hackers food, but it seems natural to end this > thread here) > > Ok, thanks. > It is clearer what happens now that you've explained that there's a clever > "rsync-trick" involving 2 directories and making rsync preserving > hard-links that way on the destination-server. Maybe it's because I'm not a > native English speaker but it wasn't obvious to me... > > I have my tablespaces laid out like this: > /storage/fast_ssd/9.6/tablespaces/ > which you correctly say that in practice means that 9.6 files are (I see now > that I don't need the pg-version in my directory-structure): > /storage/fast_ssd/9.6/tablespaces//PG_9.6_201608131 > > I understand, I hope, that without link-mode rsyncing tablespaces would be > like > this: > rsync --archive /path/to/tablespace_basedir > standby:/path/to/tablespace_basedir > > What would the equivalent be in link-mode, for transferring most efficiently? > The reason I ask is that it's not immediately obvious to me what "old_datadir" > and "new_datadir" when rsync'ing tablespaces and pg_wal dirs outside the > "pg-dirs". > > Speaking of pg_wal, how should this be rsynced now that it's changed its name > (from pg_xlog), just rsync pg_xlog and rename it? > > I know I'm being a little nitty-gritty here, but if it helps me understand it > might help others. I have applied the attached patch to show examples of using rsync on PGDATA and tablespaces, documented that rsync is only useful when in link mode, and explained more clearly how rsync handles links. You can see the results here: http://momjian.us/pgsql_docs/pgupgrade.html Any more improvements? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml new file mode 100644 index f8d9630..60011d8 *** a/doc/src/sgml/ref/pgupgrade.sgml --- b/doc/src/sgml/ref/pgupgrade.sgml *** pg_upgrade.exe *** 421,432 Upgrade Streaming Replication and Log-Shipping standby servers ! If you have Streaming Replication (see ) or Log-Shipping (see ) standby servers, follow these steps to ! upgrade them. You will not be running pg_upgrade ! on the standby servers, but rather rsync on the ! primary. Do not start any servers yet. --- 421,434 Upgrade Streaming Replication and Log-Shipping standby servers ! If you used link mode and have Streaming Replication (see ) or Log-Shipping (see ) standby servers, follow these steps to ! upgrade them. You will not be running pg_upgrade on ! the standby servers, but rather rsync on the primary. ! Do not start any servers yet. If you did not use link ! mode, skip the instructions in this section and simply recreate the ! standby servers. *** pg_upgrade.exe *** 482,490 Run rsync !From a directory on the primary server that is above the old and !new database cluster directories, run this on the !primary for each standby server: rsync --archive --delete --hard-links --size-only old_pgdata new_pgdata remote_dir --- 484,494 Run rsync !When using link mode, standby servers can be quickly upgraded using !rsync. To accomplish this, from a directory on !the primary server that is above the old and new database cluster !directories, run this on the primary for each standby !server: rsync --archive --delete --hard-links --size-only old_pgdata new_pgdata remote_dir *** rsync --archive --delete --hard-links -- *** 492,521 where old_pgdata and new_pgdata are relative to the current directory on the primary, and remote_dir !is above the old and new cluster directories on !the standby. The old and new relative cluster paths !must match on the primary and standby server. Consult the rsync manual page for details on specifying the !remote directory, e.g. standbyhost:/opt/PostgreSQL/. !What rsync does is to copy files from the !primary to the standby, and, if pg_upgrade's !--link mode was used, link files from the old to !new clusters on the standby. It links the same files that !pg_upgrade linked in the primary old and new !clusters. (Of course, linking speeds up rsync.) !
Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)
On Tue, Sep 12, 2017 at 07:54:15PM -0400, Stephen Frost wrote: > Andreas, > > * Andreas Joseph Krogh (andr...@visena.com) wrote: > > I have to ask; Why not run pg_upgrade on standby, after verifying that it's > > in > > sync with primary and promoting it to primary if necessary and then making > > it > > standby again after pg_upgrade is finished? > > I don't think that we could be guaranteed that the catalog tables would > be the same on the replica as on the primary if they were actually > created by pg_upgrade. FYI, the other problem is that standby can't go into write mode or it would diverge from the primary. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)
On Wed, Sep 13, 2017 at 12:40:32AM +0200, Andreas Joseph Krogh wrote: > På tirsdag 12. september 2017 kl. 23:52:02, skrev Bruce Momjian < > br...@momjian.us>: > > On Tue, Sep 12, 2017 at 08:59:05PM +0200, Andreas Joseph Krogh wrote: > > Improvements? > > > > Thanks, that certainly improves things. > > But; I still find the rsync-command in f) confusing; > > 1. Why --size-only? From rsync manual: "skip files that match in size", > is this > > safe?? > > > > 2. Why is old_pgdata in the rsync-command, why is it needed to sync it? > > If the file exists under the same name, it doesn't need to be checked at > all --- it is the same. We don't want to check the file modification > time because it will probably be different because of replay delay or > clock drift. We could use checksums, but there is no need since there is > no way the file contents could be different. > > > > So you're saying that if the file exists (has the same name) on the standby > (in > old_pgdata), and has the same size, then you're safe that it contains the same > data, hence --size-only? > Does this apply when not using --link mode for pg_upgrade? Well, it is really true in every case. For link mode, we have to use an rsync command that lists both the old and new clusters on the command line (since we need rsync to see those hard links to reproduce them). If we don't use --size-only, we are going to checksum check the _old_ data cluster. The new cluster will be empty so we will copy all of that (no need for a checksum there since there are no files). I think you need size-only even without link since that old cluster is going to be listed for rsync. Now, what you could do, if you are _not_ using link mode, is to rsync only the new cluster, but the instructions we give work the same for link and non-link mode and produce the same results in the same time even if we had a non-link-mode example, so it seems we might as well just give one set of instructions. > > There are many ways to do/configure things it seems, resulting in many > ifs and > > buts which makes section 10 rather confusing. I really think a complete > > example, with absolute paths, would be clarifying. > > You mean a full rsync command, e.g.: > > rsync --archive --delete --hard-links --size-only \ > /opt/PostgreSQL/9.5 /opt/PostgreSQL/9.6 standby:/opt/PostgreSQL > > Does that help? > > > > It seems some non-obvious assumptions (to me at least) are made here. > This example seems only valid when using pg_upgrade --link, correct? If so it > would be clearer to the reader if explicitly stated. Well, as I stated above, --hard-links is only going to recreate hard links on the standby that exist on the primary, and if you didn't use pg_upgrade's --link mode, there will be none, so it is harmless if pg_upgrade --link mode was not used. > 1. Why do you have to rsync both /opt/PostgreSQL/9.5 AND /opt/PostgreSQL/9.6, > wouldn't /opt/PostgreSQL/9.6 suffice? Or does this assume "pg_upgrade --link" > AND "rsync --hard-links" and therefore it somewhat needs to transfer less > data? As I stated above, rsync has to see _both_ hard links on the primary to recreate them on the standby. I thought the doc patch was clear on that, but obviously not. :-( Suggestions? (Yes, I admit that using rsync in this way is super-crafty, and I would _love_ to take credit for the idea, but I think the award goes to Stephen Frost.) > 2. What would the rsync command look like if pg_upgrade wasn't issued with > --link? It would look like: rsync --archive /opt/PostgreSQL/9.6 standby:/opt/PostgreSQL/9.6 but effectively there isn't anything _in_ standby:/opt/PostgreSQL/9.6, so you are really just using rsync as cp, and frankly I have found 'cp' is faster than rsync when nothing exists on the other side so it really becomes "just copy the cluster when the server is down", but I don't think people even need instructions for that. Maybe we should recommend rsync only for pg_upgrade --link mode? > 3. What if the directory-layout isn't the same on primary and standby, ie. > tablespaces are located differently? The way we reconfigured the location of tablespaces in PG 9.0 is that each major version of Postgres places its tablespace in a subdirectory of the tablespace directory, so there is tbldir/9.5 and tbldir/9.6. If your tbldir is different on the primary and standby, rsync will still work. Everything _under_ the standby dir must be laid out the same, but the directories above it can be different. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterp
Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)
On Tue, Sep 12, 2017 at 08:59:05PM +0200, Andreas Joseph Krogh wrote: > Improvements? > > Thanks, that certainly improves things. > But; I still find the rsync-command in f) confusing; > 1. Why --size-only? From rsync manual: "skip files that match in size", is > this > safe?? > 2. Why is old_pgdata in the rsync-command, why is it needed to sync it? If the file exists under the same name, it doesn't need to be checked at all --- it is the same. We don't want to check the file modification time because it will probably be different because of replay delay or clock drift. We could use checksums, but there is no need since there is no way the file contents could be different. > There are many ways to do/configure things it seems, resulting in many ifs and > buts which makes section 10 rather confusing. I really think a complete > example, with absolute paths, would be clarifying. You mean a full rsync command, e.g.: rsync --archive --delete --hard-links --size-only \ /opt/PostgreSQL/9.5 /opt/PostgreSQL/9.6 standby:/opt/PostgreSQL Does that help? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Clarification in pg10's pgupgrade.html step 10 (upgrading standby servers)
On Thu, Aug 3, 2017 at 11:37:32AM +0200, Michael Paquier wrote: > On Mon, Jul 31, 2017 at 6:13 PM, Robert Haas <robertmh...@gmail.com> wrote: > > On Fri, Jul 28, 2017 at 10:35 AM, Andreas Joseph Krogh > > <andr...@visena.com> wrote: > >> I'm reading https://www.postgresql.org/docs/10/static/pgupgrade.html to try > >> to understand how to upgrade standby-servers using pg_upgrade with pg10. > >> > >> The text in step 10 sais: > >> "You will not be running pg_upgrade on the standby servers, but rather > >> rsync", which to me sounds like rsync, in step 10-f, should be issued on > >> the > >> standy servers. Is this the case? If so I don't understand how the > >> standby's > >> data is upgraded and what "remote_dir" is. If rsync is supposed to be > >> issued > >> on the primary then I think it should be explicitly mentioned, and step > >> 10-f > >> should provide a clarer example with more detailed values for the > >> directory-structures involved. > >> > >> I really think section 10 needs improvement as I'm certainly not > >> comfortable > >> upgrading standbys following the existing procedure. > > > > Yeah, I don't understand it either, and I have never been convinced > > that there's any safe way to do it other than recloning the standbys > > from the upgraded master. > > Here are my 2c on the matter. 10-f means that the upgraded node may > have generated WAL with wal_level = minimal, which, at least it seems > to me, that we have a risk of having inconsistent data pages if only a > rsync is used on the old standbys. Like Robert, the flow we used in > the products I work on is to re-create standbys from scratch after the > upgrade using a fresh backup, with a VM cloning. An upgrade here is an > in-place process not only linked to Postgres, so standby VMs are made > of many services, some are being linked to Postgres. So this choice is > mainly decided by those dependencies, still it feels safer anyway. I have applied the attached doc patch back to 9.5 to clarify pg_upgrade's rsync instructions and explain how it works. Improvements? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml new file mode 100644 index d444318..f8d9630 *** a/doc/src/sgml/ref/pgupgrade.sgml --- b/doc/src/sgml/ref/pgupgrade.sgml *** NET STOP postgresql- *** 332,338 Also, if upgrading standby servers, change wal_level to replica in the postgresql.conf file on ! the new master cluster. --- 332,338 Also, if upgrading standby servers, change wal_level to replica in the postgresql.conf file on ! the new primary cluster. *** pg_upgrade.exe *** 425,432 linkend="streaming-replication">) or Log-Shipping (see ) standby servers, follow these steps to upgrade them. You will not be running pg_upgrade ! on the standby servers, but rather rsync. Do not ! start any servers yet. --- 425,432 linkend="streaming-replication">) or Log-Shipping (see ) standby servers, follow these steps to upgrade them. You will not be running pg_upgrade ! on the standby servers, but rather rsync on the ! primary. Do not start any servers yet. *** pg_upgrade.exe *** 455,461 Install the same custom shared object files on the new standbys !that you installed in the new master cluster. --- 455,461 Install the same custom shared object files on the new standbys !that you installed in the new primary cluster. *** pg_upgrade.exe *** 482,506 Run rsync !From a directory that is above the old and new database cluster !directories, run this for each standby: rsync --archive --delete --hard-links --size-only old_pgdata new_pgdata remote_dir where old_pgdata and new_pgdata are relative !to the current directory, and remote_dir is !above the old and new cluster directories on !the standby server. The old and new relative cluster paths !must match on the master and standby server. Consult the rsync manual page for details on specifying the remote directory, e.g. standbyhost:/opt/PostgreSQL/. !rsync will be fast when pg_upgr
Re: [HACKERS] PG 10 release notes
On Fri, Sep 1, 2017 at 05:39:31PM +0900, Masahiko Sawada wrote: > Hi all, > > On Tue, Aug 1, 2017 at 5:53 AM, Thomas Munro > <thomas.mu...@enterprisedb.com> wrote: > > On Tue, Apr 25, 2017 at 1:31 PM, Bruce Momjian <br...@momjian.us> wrote: > >> I have committed the first draft of the Postgres 10 release notes. They > >> are current as of two days ago, and I will keep them current. Please > >> give me any feedback you have. > > > > Hi Bruce, > > > > "Add AFTER trigger transition tables to record changed rows (Kevin > > Grittner)" > > > > Any chance I could ask for a secondary author credit here? While I > > started out as a reviewer and I understand that we don't list those, I > > finished up writing quite a lot of lines of committed code for this > > (see commits 1add0b15, 8c55244a, c46c0e52, 501ed02c, f32d57fd, > > 9e6104c6, 29fd3d9d, 304007d9, 5ebeb579) and was already listed as a > > co-author by Kevin in the original commits (59702716, 18ce3a4a). Of > > course I wish I'd identified and fixed all of those things *before* > > the original commits, but that's how it played out... > > > > It might be too late but I found that the following entry is > categorized in Monitoring. But in PostgreSQL 9.6 release note, the > feature related to default role is categorized in Permissions > Management. I think the adding new default roles can be categorized in > the same category to not confuse users and personally it's more > suitable. > > "Add default monitoring roles (Dave Page)" We don't have a "Permissions Management" category in PG 10 and unless we have at least three items to add in there, I don't think it is worth adding it. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PG 10 release notes
On Tue, Aug 1, 2017 at 08:53:51AM +1200, Thomas Munro wrote: > On Tue, Apr 25, 2017 at 1:31 PM, Bruce Momjian <br...@momjian.us> wrote: > > I have committed the first draft of the Postgres 10 release notes. They > > are current as of two days ago, and I will keep them current. Please > > give me any feedback you have. > > Hi Bruce, > > "Add AFTER trigger transition tables to record changed rows (Kevin Grittner)" > > Any chance I could ask for a secondary author credit here? While I > started out as a reviewer and I understand that we don't list those, I > finished up writing quite a lot of lines of committed code for this > (see commits 1add0b15, 8c55244a, c46c0e52, 501ed02c, f32d57fd, > 9e6104c6, 29fd3d9d, 304007d9, 5ebeb579) and was already listed as a > co-author by Kevin in the original commits (59702716, 18ce3a4a). Of > course I wish I'd identified and fixed all of those things *before* > the original commits, but that's how it played out... Sure, done. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PG 10 release notes
On Sat, Sep 9, 2017 at 12:39:43PM +0200, Adrien Nayrat wrote: > On 07/13/2017 04:36 PM, Adrien Nayrat wrote: > > Hello hackers, > > > > From: Peter Geoghegan <p...@bowt.ie> > >> Date: Wed, 5 Jul 2017 15:19:57 -0700 > >> Subject: Re: [BUGS] BUG #14722: Segfault in tuplesort_heap_siftup, 32 bit > >> overflow > >> On pgsql-b...@postgresql.org > > > > On 07/06/2017 12:19 AM, Peter Geoghegan wrote: > >> In Postgres 10, tuplesort external sort run merging became much faster > >> following commit 24598337c8d. It might be noticeable if such a machine > >> were using Postgres 10 [...] > > > > Should-we mention this improvement in release notes? > > > > Regards, > > > > Hello, > > After seeing theses slides (especially 52) : > https://speakerdeck.com/peterg/sort-hash-pgconfus-2017 ... > > > > Should we mention it ? I don't know, but I suggest you read this email thread from April to get an idea of how performance items are handled: https://www.postgresql.org/message-id/flat/20170425013144.GA7513%40momjian.us#20170425013144.ga7...@momjian.us -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PG 10 release notes
On Mon, Sep 11, 2017 at 06:30:58PM -0400, Tom Lane wrote: > Bruce Momjian <br...@momjian.us> writes: > > On Fri, Jun 2, 2017 at 04:05:44PM -0500, Jim Nasby wrote: > >> Can you change the attribution on > >> Allow PL/Tcl functions to return composite types and sets > >> to Karl Lehenbauer? > > > Done and backpatched. Sorry for the delay. > > I don't see this pushed to the repo? Sorry, pushed now. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PG 10 release notes
On Fri, Jun 2, 2017 at 04:05:44PM -0500, Jim Nasby wrote: > On 4/24/17 8:31 PM, Bruce Momjian wrote: > >I have committed the first draft of the Postgres 10 release notes. They > >are current as of two days ago, and I will keep them current. Please > >give me any feedback you have. > > > >The only unusual thing is that this release has ~180 items while most > >recent release have had ~220. The pattern I see that there are more > >large features in this release than previous ones. > > Can you change the attribution on > > Allow PL/Tcl functions to return composite types and sets > > to Karl Lehenbauer? He actually wrote the original patch; I just helped to > get it through the community (something that FlightAware paid for). I didn't > realize at the time that you could change the listed Author in the > commitfest. Done and backpatched. Sorry for the delay. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GnuTLS support
On Fri, Sep 1, 2017 at 12:09:35PM -0400, Robert Haas wrote: > I think that what this shows is that the current set of GUCs is overly > OpenSSL-centric. We created a set of GUCs that are actually specific > to one particular implementation but named them as if they were > generic. My idea about this would be to actually rename the existing > GUCs to start with "openssl" rather than "ssl", and then add new GUCs > as needed for other SSL implementations. > > Depending on what we think is best, GUCs for an SSL implementation > other than the one against which we compiled can either not exist at > all, or can exist but be limited to a single value (e.g. "none", as we > currently do when the compile has no SSL support at all). Also, we > could add a read-only GUC with a name like ssl_library that exposes > the name of the underlying SSL implementation - none, openssl, gnutls, > or whatever. > > I think if we go the route of insisting that every SSL implementation > has to use the existing GUCs, we're just trying to shove a square peg > into a round hole, and there's no real benefit for users. If the > string that has to be stuffed into ssl_ciphers differs based on which > library was chosen at compile time, then you can't have a uniform > default configuration for all libraries anyway. I think it'll be > easier to explain and document this if there's separate documentation > for openssl_ciphers, gnutls_ciphers, etc. rather than one giant > documentation section that tries to explain every implementation > separately. I am worried about having 3x version of TLS controls in postgresql.conf, and only one set being active. Perhaps we need to break out the TLS config to separate files or something. Anyway, this needs more thought. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] obsolete code in pg_upgrade
On Tue, Aug 22, 2017 at 08:28:15PM -0400, Peter Eisentraut wrote:
> It seems to me that this code in pg_upgrade/check.c has been useless
> since at least version 9.1:
>
> /* Is it 9.0 but without tablespace directories? */
> if (GET_MAJOR_VERSION(new_cluster.major_version) == 900 &&
> new_cluster.controldata.cat_ver < TABLE_SPACE_SUBDIRS_CAT_VER)
> pg_fatal("This utility can only upgrade to PostgreSQL version
> 9.0 after 2010-01-11\n"
> "because of backend API changes made during
> development.\n");
Coming in late, but agreed.
--
Bruce Momjian <br...@momjian.us>http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tupconvert.c API change in v10 release notes
On Wed, Jul 19, 2017 at 12:39:07PM -0400, Tom Lane wrote: > Justin Pryzby <pry...@telsasoft.com> writes: > > FYI, I happened across this commit comment: > > 3f902354b08ac788600f0ae54fcbfc1d4e3ea765 > > | So, let's accept the removal of the guarantee about > > | the output tuple's rowtype marking, recognizing that this is a API > > change > > | that could conceivably break third-party callers of tupconvert.c. (So, > > | let's remember to mention it in the v10 release notes.) > > > ..but couldn't see that the commit or change is so referenced. > > Yeah, I see nothing about 3f902354b in release-10.sgml either. > We've had varying policies over the years about whether to mention > internal API changes in the release notes or not, but this one > I think does belong there, since it's a silent API break rather > than one that would easily be caught due to compiler errors. > Bruce, did you have any specific reasoning for leaving it out? I doubt I saw that sentence in the paragraph. For long text like that, I am usually looking for "BACKWARDS INCOMPATIBLE CHANGE" or something like that. Sorry I missed it. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Authentication mechanisms categorization
On Thu, Jul 20, 2017 at 01:00:50AM +0300, Álvaro Hernández Tortosa wrote: > I'm mostly convinced by the power of all the parameters that already > exist, given that you added both saslname and saslchannelbinding to the > already existing sslmode. That's great, and allows for very fine choosing of > the auth method. So it would be great if (non-libpq) driver implementations > would expose the same parameter names to the users. I will study this for > JDBC. Coming in late here, but the way TLS prevents authentication downgrade attacks is for the sender to send a list of supported authentication methods, and a hash of the supported authentication methods with a random number and a secret shared with the other end, and send that. If the list doesn't match the hash, it means the list is invalid. The secret prevents attackers from faking connections. I think the problem is that we don't have a consistent secret shared between the client and the server. We have md5 and SCRAM, but that doesn't help because the secret it tied to the authentication methods. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Revisiting NAMEDATALEN
On Mon, Jul 3, 2017 at 11:31:01AM -0700, Emrul wrote: > Hi hackers, > > This question came up again on Reddit: > https://www.reddit.com/r/PostgreSQL/comments/6kyyev/i_have_hit_the_table_name_length_limit_a_number/ > and I thought I'd echo it here. > > I totally am on board with short, descriptive names and a good convention. > However, there are just so many cases where 63 characters can't > descriptively describe a column name. I've been on projects where we have I am coming in late on this, but just to clarify, the NAMEDATALEN is in _bytes_, meaning multi-byte names are often less than 63 characters. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Challenges preventing us moving to 64 bit transaction id (XID)?
On Thu, Jul 6, 2017 at 07:29:07AM -0700, Jim Finnerty wrote: > re: "The problem is if you want to delete from such a page. Then you need to > update the tuple's xmax and stick the new xid epoch somewhere." I am coming to this very late, but wouldn't such a row be marked using our frozen-commited fixed xid so it doesn't matter what the xid epoch is? I realize with 64-bit xids we don't need to freeze tuples, but we could still use a frozen-commited fixed xid, see: #define FrozenTransactionId ((TransactionId) 2) -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
On Wed, Jun 28, 2017 at 10:11:35PM -0400, Bruce Momjian wrote:
> On Fri, Jun 23, 2017 at 06:17:47PM +0300, Sergey Burladyan wrote:
> > PS:
> > I successfully upgraded last night from 9.2 to 9.4 and find other issue :-)
> >
> > It is about hash index and promote:
> > 1. create master
> > 2. create standby from it
> > 3. create unlogged table and hash index like:
> > create unlogged table test (id int primary key, v text);
> > create index on test using hash (id);
> > 3. stop master
> > 4. promote standby
> >
> > now, if you try to upgrade this new promoted master pg_upgrade will stop
> > on this hash index:
> > error while creating link for relation "public.test_id_idx"
> > ("s/9.2/base/16384/16393" to "m/9.4/base/16422/16393"): No such file or
> > directory
> > Failure, exiting
> >
> > I touch this file (s/9.2/base/16384/16393) and rerun pg_upgrade from
> > scratch and it complete successfully.
>
> Sergey, can you please test if the table "test" is not unlogged, does
> pg_upgrade still fail on the hash index file?
I was able to reproduce this failure on my server. :-)
What I found is that the problem is larger than I thought. Sergey is
correct that pg_upgrade fails because there is no hash file associated
with the unlogged table, but in fact a simple access of the unlogged
table with a hash index generates an error:
test=> SELECT * FROM t_u_hash;
ERROR: could not open file "base/16384/16392": No such file or
directory
What is interesting is that this is the only combination that generates
an error. A unlogged able with a btree index or a logged table with a
hash index are fine, e.g.:
List of relations
Schema | Name| Type | Owner
+---+---+--
public | t_btree | table | postgres
public | t_hash| table | postgres
public | t_u_btree | table | postgres
fail--> public | t_u_hash | table | postgres
This doesn't fail on PG 10 since we WAL-log hash indexes.
I think we have two questions:
1. do we fix this in the server
2. if not, do we fix pg_upgrade
--
Bruce Momjian <br...@momjian.us>http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
On Fri, Jun 23, 2017 at 06:17:47PM +0300, Sergey Burladyan wrote:
> PS:
> I successfully upgraded last night from 9.2 to 9.4 and find other issue :-)
>
> It is about hash index and promote:
> 1. create master
> 2. create standby from it
> 3. create unlogged table and hash index like:
> create unlogged table test (id int primary key, v text);
> create index on test using hash (id);
> 3. stop master
> 4. promote standby
>
> now, if you try to upgrade this new promoted master pg_upgrade will stop
> on this hash index:
> error while creating link for relation "public.test_id_idx"
> ("s/9.2/base/16384/16393" to "m/9.4/base/16422/16393"): No such file or
> directory
> Failure, exiting
>
> I touch this file (s/9.2/base/16384/16393) and rerun pg_upgrade from
> scratch and it complete successfully.
Sergey, can you please test if the table "test" is not unlogged, does
pg_upgrade still fail on the hash index file?
--
Bruce Momjian <br...@momjian.us>http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
On Sat, Jun 24, 2017 at 09:24:21AM +0530, Amit Kapila wrote: > > I was not clear. I was not saying there can be only one extra WAL file. > > I am saying the "Latest checkpoint location" should be one WAL file > > farther on the master. I think the big problem is that we need a full > > replay of that WAL file, not just having it one less than the master. > > > > If the user has properly shutdown, then that last file should only > have checkpoint record, is it safe to proceed with upgrade without > actually copying that file? Yes, but how do we know they processed all the records in the second-to-last WAL file (in WAL shipping mode). -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
On Sat, Jun 24, 2017 at 09:19:10AM +0530, Amit Kapila wrote:
> > I successfully upgraded last night from 9.2 to 9.4 and find other issue :-)
> >
> > It is about hash index and promote:
> > 1. create master
> > 2. create standby from it
> > 3. create unlogged table and hash index like:
> > create unlogged table test (id int primary key, v text);
> > create index on test using hash (id);
> > 3. stop master
> > 4. promote standby
> >
> > now, if you try to upgrade this new promoted master pg_upgrade will stop
> > on this hash index:
> > error while creating link for relation "public.test_id_idx"
> > ("s/9.2/base/16384/16393" to "m/9.4/base/16422/16393"): No such file or
> > directory
> > Failure, exiting
> >
>
> I am not sure if this is a problem because in the version you are
> trying hash indexes are not WAL-logged and the creation of same will
> not be replicated on standby, so the error seems to be expected.
Well, it certainly should not error out like this. I have not seen such
a failure report before.
I think the fundamental problem is that unlogged objects
(pg_class.relpersistence='u') creates a file on the master, but doesn't
create anything on the standby since it is never transmitted over the
WAL (assuming the object is created after the base backup).
I assume the standby creates them as empty when it is promoted to
primary and someone tries to access the object. I wonder if I need to
add a boolean to each object to record if it is unlogged, and allow
copy/link to silently fail in such cases. Does that make sense?
--
Bruce Momjian <br...@momjian.us>http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
On Fri, Jun 23, 2017 at 08:10:17AM +0530, Amit Kapila wrote: > On Wed, Jun 21, 2017 at 10:03 PM, Bruce Momjian <br...@momjian.us> wrote: > > On Wed, Jun 21, 2017 at 07:49:21PM +0530, Amit Kapila wrote: > >> On Tue, Jun 20, 2017 at 7:24 PM, Amit Kapila <amit.kapil...@gmail.com> > >> wrote: > >> > Hmm. I think we need something that works with lesser effort because > >> > not all users will be as knowledgeable as you are, so if they make any > >> > mistakes in copying the file manually, it can lead to problems. How > >> > about issuing a notification (XLogArchiveNotifySeg) in shutdown > >> > checkpoint if archiving is enabled? > >> > > >> > >> I have thought more about the above solution and it seems risky to > >> notify archiver for incomplete WAL segments (which will be possible in > >> this case as there is no guarantee that Checkpoint record will fill > >> the segment). So, it seems to me we should update the document unless > >> you or someone has some solution to this problem. > > > > The over-arching question is how do we tell users to verify that the WAL > > has been replayed on the standby? I am thinking we would say that for > > streaming replication, the "Latest checkpoint location" should match on > > the primary and standby, while for log shipping, the standbys should be > > exactly one WAL file less than the primary. > > > > I am not sure if we can say "standbys should be exactly one WAL file > less than the primary" because checkpoint can create few more WAL > segments for future use. I think to make this work user needs to > carefully just copy the next WAL segment (next to the last file in > standby) which will contain checkpoint record. Ideally, there should > be some way either in form of a tool or a functionality in the > database server with which this last file can be copied but I think in > the absence of that we can at least document this fact. I was not clear. I was not saying there can be only one extra WAL file. I am saying the "Latest checkpoint location" should be one WAL file farther on the master. I think the big problem is that we need a full replay of that WAL file, not just having it one less than the master. I have no idea how do explain that. It is easy for streaming replication since the "Latest checkpoint location" should match, which is simple. Also, we need something that can be backpatched. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-indent HEAD tomorrow?
On Thu, Jun 22, 2017 at 10:38:41AM -0400, Robert Haas wrote: > On Wed, Jun 21, 2017 at 5:28 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > > Right now we're really just speculating about how much pain there will > > be, on either end of this. So it'd be interesting for somebody who's > > carrying large out-of-tree patches (EDB? Citus?) to try the new > > pgindent version on a back branch and see how much of their patches no > > longer apply afterwards. > > EDB is not continuously reapplying patches; we have branches into > which the upstream reindents would have to be merged. As a broad > statement, reindenting all of the back branches is surely going to > create some extra work for whoever has to do those merges, but if > that's what the community thinks is best, we will of course manage. > It's not *that* bad. > > It would be slightly less annoying for us, I think, if the reindent > were done immediately after a minor-release rather than at some other > random point in time. Also keep in mind that if we don't reindent all active branches then even forks of Postgres will have merge conflicts in backporting of their own patches, meaning the community and forks will have backbranch patch difficulties. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re-indent HEAD tomorrow?
On Wed, Jun 21, 2017 at 04:07:30PM -0400, Tom Lane wrote: > I wrote: > > Barring objections, I'd like to reindent HEAD with the new version > > of pg_bsd_indent (and correspondingly updated pgindent script) > > tomorrow, say around 1800 UTC. > > ... and it's done. You are eventually doing all active branches, right? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
On Wed, Jun 21, 2017 at 07:49:21PM +0530, Amit Kapila wrote: > On Tue, Jun 20, 2017 at 7:24 PM, Amit Kapila <amit.kapil...@gmail.com> wrote: > > Hmm. I think we need something that works with lesser effort because > > not all users will be as knowledgeable as you are, so if they make any > > mistakes in copying the file manually, it can lead to problems. How > > about issuing a notification (XLogArchiveNotifySeg) in shutdown > > checkpoint if archiving is enabled? > > > > I have thought more about the above solution and it seems risky to > notify archiver for incomplete WAL segments (which will be possible in > this case as there is no guarantee that Checkpoint record will fill > the segment). So, it seems to me we should update the document unless > you or someone has some solution to this problem. The over-arching question is how do we tell users to verify that the WAL has been replayed on the standby? I am thinking we would say that for streaming replication, the "Latest checkpoint location" should match on the primary and standby, while for log shipping, the standbys should be exactly one WAL file less than the primary. As far as I know this is the only remaining open issue. Sergey, please verify. I appreciate the work everyone has done to improve this, and all the existing fixes have been pushed to all supported branches. :-) -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Shortened URLs for commit messages
On Wed, Jun 21, 2017 at 11:11:57AM -0400, Alvaro Herrera wrote: > Bruce Momjian wrote: > > > Oh, here is a fixed version that requires an @ sign, which all message > > id's have: > > > > sed '/http/!s;^\(Discussion: *\)\(.*@.*\)$;\1https://postgr.es/m/\2;' > > So how do you actually use this? My commit script is here: https://momjian.us/main/writings/pgsql/src/pgcommit It basically runs some checks and then creates a temp file with lines labeled by their purpose. It edits the temp file, then runs the temp file through a number of filters, and one of those does the URL shortening via pgurl at: https://momjian.us/main/writings/pgsql/src/pgurl It also changes bare message-ids on the 'Discussion' line to shorted URLs too. It also removes empty labeled lines, and exits if nothing has been changed in the editor. It then does the commit (potentially to multiple branches), and then the push. The script calls many of other custom scripts but you can get an idea how it works. I am happy to supply more tools as desired. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
Sorry, this email from June 16 didn't make it to the lists for some odd
reason so I am reposting it now. I will apply a patch based on this
email shortly.
What is really odd is that I replied to this email already but the
original wasn't posted. I think it was something about my email reader.
---
On Fri, Jun 16, 2017 at 10:57:33PM +0300, Sergey Burladyan wrote:
> Bruce Momjian <br...@momjian.us> writes:
> > On Fri, Jun 16, 2017 at 04:33:16AM +0300, Sergey Burladyan wrote:
> > > Bruce Momjian <br...@momjian.us> writes:
> > The way pg_upgrade uses rsync, the standby never needs to replay the WAL
> > when it starts up because we already copied the changed system tables
> > and hard linked the user data files.
>
> Oh, it is my fail, I was not run test script completely for current git
> master. In git master it work as expected. But not in previous versions.
> I used this test script and got this result:
> 9.2 -> master: wal_level setting:replica
> 9.2 -> 9.6: wal_level setting:minimal
> 9.2 -> 9.5: wal_level setting:minimal
> 9.2 -> 9.4: Current wal_level setting:minimal
Wow, thank you again for your excellent research.
> >From git master pg_upgrade is restart new master again after
> pg_resetwal -o, as you said.
>
> It is from src/bin/pg_upgrade/check.c:176
> void
> issue_warnings(void)
> {
> /* Create dummy large object permissions for old < PG 9.0? */
> if (GET_MAJOR_VERSION(old_cluster.major_version) <= 804)
> {
> start_postmaster(_cluster, true);
> new_9_0_populate_pg_largeobject_metadata(_cluster, false);
> stop_postmaster(false);
> }
>
> /* Reindex hash indexes for old < 10.0 */
> if (GET_MAJOR_VERSION(old_cluster.major_version) <= 906)
> {
> start_postmaster(_cluster, true);
> old_9_6_invalidate_hash_indexes(_cluster, false);
> stop_postmaster(false);
> }
> }
Yes, that is _exactly_ the right place to look. Only in PG 10 do we
restart the new cluster to invalidate hash indexes. In previous
releases we didn't do the restart.
That didn't matter with the old rsync instructions, but now that we have
removed the start/stop before rsync step, the final WAL status of
pg_upgrade matters.
I suggest applying the attached patch
--
Bruce Momjian <br...@momjian.us>http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
--2oS5YaxWCcQjTEyO
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="wal.diff"
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
new file mode 100644
index 8b9e81e..b79e54a
*** a/src/bin/pg_upgrade/check.c
--- b/src/bin/pg_upgrade/check.c
*** report_clusters_compatible(void)
*** 174,196
void
! issue_warnings(void)
{
/* Create dummy large object permissions for old < PG 9.0? */
if (GET_MAJOR_VERSION(old_cluster.major_version) <= 804)
- {
- start_postmaster(_cluster, true);
new_9_0_populate_pg_largeobject_metadata(_cluster, false);
- stop_postmaster(false);
- }
/* Reindex hash indexes for old < 10.0 */
if (GET_MAJOR_VERSION(old_cluster.major_version) <= 906)
- {
- start_postmaster(_cluster, true);
old_9_6_invalidate_hash_indexes(_cluster, false);
! stop_postmaster(false);
! }
}
--- 174,198
void
! issue_warnings_and_set_wal_level(void)
{
+ /*
+* We unconditionally start/stop the new server because pg_resetwal -o
+* set wal_level to 'minimum'. If the user is upgrading standby
+* servers using the rsync instructions, they will need pg_upgrade
+* to write its final WAL record showing wal_level as 'replica'.
+*/
+ start_postmaster(_cluster, true);
+
/* Create dummy large object permissions for old < PG 9.0? */
if (GET_MAJOR_VERSION(old_cluster.major_version) <= 804)
new_9_0_populate_pg_largeobject_metadata(_cluster, false);
/* Reindex hash indexes for old < 10.0 */
if (GET_MAJOR_VERSION(old_cluster.major_version) <= 906)
old_9_6_invalidate_hash_indexes(_cluster, false);
!
! stop_postmaster(false);
}
diff --git a/src/bin/pg_upgrade/pg_upgrade.c b/src/bin/pg_upgrade/pg_upgrade.c
new file mode 100644
index ca1aa5c..2a9c397
*** a/src/bin/pg_upgrade/pg_upgrade.c
--- b/src/bin/pg_upgrade/pg_upgrade.c
*** main(int arg
Re: [HACKERS] Broken hint bits (freeze)
On Tue, Jun 20, 2017 at 06:42:58PM +0300, Sergey Burladyan wrote: > Bruce Momjian <br...@momjian.us> writes: > > > On Tue, Jun 20, 2017 at 01:10:26PM +0300, Sergey Burladyan wrote: > > > Only if missing/changed files changed in size, because rsync run with > > > --size-only it does not copy changed files with same size. > > > > I am sorry but I am not understanding. Step 10.b says: > > > > 10.b Make sure the new standby data directories do not exist > > > > Make sure the new standby data directories do not exist or are empty. If > > initdb was run, delete the standby server data directories. > > > > so the _entire_ new data directory is empty before rsync is run, meaning > > that it is an exact copy of the new master. > > Yes, new data directory at standby is empty, but you missed old data > directory at standby which is hardlink'ed by rsync into new as at master. OK, I think I am getting closer to understanding. Only some files are hard-linked from the old master to the new master, specifically the user data files (table and indexes). > rsync run with _three_ arguments and with --hard-links option: > rsync --archive --delete --hard-links --size-only old_pgdata new_pgdata > remote_dir > (remote_dir is parent directory for old and new data at standby) > > In this mode rsync compare not only new_pgdata with new empty data > directory at standby, but also compare it with old data directory from > standby and with --size-only it doing this compare only by the file > existence or file size. but it only going to create hard links for hard links that already exist between the old and new masters. If I am wrong, we are in big trouble because rsync would not work. > If file at standby in old data directory is different from same file at > master, but it have same size, it will be hardlinked into new data > directory at standby and does not copied from master. Only if pg_upgrade created the hardlinks, right? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
On Tue, Jun 20, 2017 at 01:10:26PM +0300, Sergey Burladyan wrote: > Bruce Momjian <br...@momjian.us> writes: > > > Uh, as I understand it the rsync is going to copy the missing WAL file > > > from the new master to the standby, right, and I think pg_controldata > > > too, so it should be fine. Have you tested to see if it fails? > > It need old WAL files from old version for correct restore heap > files. New WAL files from new version does not have this information. > > > The point is that we are checking the "Latest checkpoint location" to > > make sure all the WAL was replayed. We are never going to start the > > old standby server. Rsync is going to copy the missing/changed files. > > Only if missing/changed files changed in size, because rsync run with > --size-only it does not copy changed files with same size. I am sorry but I am not understanding. Step 10.b says: 10.b Make sure the new standby data directories do not exist Make sure the new standby data directories do not exist or are empty. If initdb was run, delete the standby server data directories. so the _entire_ new data directory is empty before rsync is run, meaning that it is an exact copy of the new master. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
On Mon, Jun 19, 2017 at 10:59:19PM -0400, Bruce Momjian wrote: > On Tue, Jun 20, 2017 at 03:50:29AM +0300, Sergey Burladyan wrote: > > 20 июн. 2017 г. 1:21 пользователь "Bruce Momjian" <br...@momjian.us> > > написал: > > > > > > We are saying that Log-Shipping should match "Latest checkpoint > > location", but the WAL for that will not be sent to the standby, so it > > will not match, but that is OK since the only thing in the non-shipped > > WAL file is the checkpoint record. How should we modify the wording on > > this? > > > > > > I am afraid that without this checkpoint record standby cannot make > > restartpoint > > and without restartpoint it does not sync shared buffers into disk at > > shutdown. > > Uh, as I understand it the rsync is going to copy the missing WAL file > from the new master to the standby, right, and I think pg_controldata > too, so it should be fine. Have you tested to see if it fails? The point is that we are checking the "Latest checkpoint location" to make sure all the WAL was replayed. We are never going to start the old standby server. Rsync is going to copy the missing/changed files. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
On Tue, Jun 20, 2017 at 03:50:29AM +0300, Sergey Burladyan wrote: > 20 июн. 2017 г. 1:21 пользователь "Bruce Momjian" <br...@momjian.us> написал: > > > We are saying that Log-Shipping should match "Latest checkpoint > location", but the WAL for that will not be sent to the standby, so it > will not match, but that is OK since the only thing in the non-shipped > WAL file is the checkpoint record. How should we modify the wording on > this? > > > I am afraid that without this checkpoint record standby cannot make > restartpoint > and without restartpoint it does not sync shared buffers into disk at > shutdown. Uh, as I understand it the rsync is going to copy the missing WAL file from the new master to the standby, right, and I think pg_controldata too, so it should be fine. Have you tested to see if it fails? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
On Sat, Jun 17, 2017 at 08:34:47AM +0530, Amit Kapila wrote: > On Fri, Jun 16, 2017 at 11:03 PM, Sergey Burladyan <eshkin...@gmail.com> > wrote: > >> > Yeah, we have ensured that all the transactions before shutdown > >> > checkpoint got archived. It is done in commit > >> > 2e6107cb621d003dcab0df53ac8673ea67c4e467. However, it is not clear to > >> > me neither it is mentioned in comments why we have done it that way. > >> > >> Yes, I am confused why Sergey doesn't see that behavior. > > > > The behavior reported by Sergey is what is expected i.e the last file > in which shutdown checkpoint record is written won't be archived and > there is a reason behind that. We always perform shutdown checkpoint > (which will write shutdown checkpoint record) after requesting a xlog > switch. Any record written after xlog switch won't be archived unless > it is so big that it consumes complete xlog segment. > > > I think this last new switched WAL with shutdown checkpoint record is > > incomplete and it does not marked as *.ready in pg_xlog/archive_status/ > > and not archived. > > > > Yes, that's true and is expected behavior. OK, so our pg_upgrade documentation is currently incorrect: https://www.postgresql.org/docs/10/static/pgupgrade.html 8. Verify standby servers If you are upgrading Streaming Replication and Log-Shipping standby servers, verify that the old standby servers are caught up by running pg_controldata against the old primary and standby clusters. Verify that the "Latest checkpoint location" values match in all clusters. (There will be a mismatch if old standby servers were shut down before the old primary.) We are saying that Log-Shipping should match "Latest checkpoint location", but the WAL for that will not be sent to the standby, so it will not match, but that is OK since the only thing in the non-shipped WAL file is the checkpoint record. How should we modify the wording on this? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
On Fri, Jun 16, 2017 at 04:44:46PM -0400, Bruce Momjian wrote: > Yes, that is _exactly_ the right place to look. Only in PG 10 do we > restart the new cluster to invalidate hash indexes. In previous > releases we didn't do the restart. > > That didn't matter with the old rsync instructions, but now that we have > removed the start/stop before rsync step, the final WAL status of > pg_upgrade matters. > > I suggest applying the attached patch Sorry, I meant to say, I suggest applying the attached patch to all Postgres versions, of course modified. While the rsync instructions only appear in PG 9.5+, the instructions work for any supported version of Postgres, so we should allow it to continue working, even if the updated instructions are used. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Preliminary results for proposed new pgindent implementation
On Fri, Jun 16, 2017 at 03:56:47PM -0400, Tom Lane wrote: > can be. I managed to tweak bsdindent so that its output matches > what entab would do, by dint of the attached patch, which implements > the rule "use a space instead of a tab if the tab would only move > one column and we don't need another tab after it". (I think entab > is being weird with the second half of that rule, but if I remove it, > I get circa a thousand lines of invisible whitespace changes; probably > better not to deal with those. With no patch at all, just letting > bsdindent do what it does now, there's circa ten thousand changed lines.) Yeah, entab was designed to do that, via this C comment: /* * Is the next character going to be a tab? We do tab * replacement in the current spot if the next char is * going to be a tab and ignore min_spaces. */ -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Preliminary results for proposed new pgindent implementation
On Fri, Jun 16, 2017 at 11:54:06AM -0700, Andres Freund wrote: > On 2017-06-16 14:42:38 -0400, Bruce Momjian wrote: > > On Fri, Jun 16, 2017 at 02:23:00PM -0400, Tom Lane wrote: > > > Well, that's something we need to discuss. I originally argued for > > > back-patching the new rules, whatever they are (ie, run the new > > > pgindent on the back branches whenever we've agreed that the dust > > > has settled). But I'm starting to realize that that's likely to > > > be horrid for anyone who's carrying out-of-tree patches, as I know > > > a lot of packagers do for instance. We have to trade off our own > > > inconvenience in making back-patches against inconvenience to > > > people who are maintaining private patchsets. > > > > Can't they sync up to just before our pgindent commit and run pgindent > > on their own code base? > > That doesn't really help that much if you have a series of patches that > you want to keep independent, e.g. because you might want to submit to > postgres. And you'll also get a bunch of annoying to resolve merge > conflicts, even if they're easier to resolve with that methodology. I think we have to ask how much we want to make things easier for people with modified but continually-updated Postgres trees vs. our community-tree developers. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Preliminary results for proposed new pgindent implementation
On Fri, Jun 16, 2017 at 02:23:00PM -0400, Tom Lane wrote: > Well, that's something we need to discuss. I originally argued for > back-patching the new rules, whatever they are (ie, run the new > pgindent on the back branches whenever we've agreed that the dust > has settled). But I'm starting to realize that that's likely to > be horrid for anyone who's carrying out-of-tree patches, as I know > a lot of packagers do for instance. We have to trade off our own > inconvenience in making back-patches against inconvenience to > people who are maintaining private patchsets. Can't they sync up to just before our pgindent commit and run pgindent on their own code base? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Preliminary results for proposed new pgindent implementation
On Fri, Jun 16, 2017 at 01:34:01PM -0400, Tom Lane wrote: > > I could live with both of these proposed > > changes, the selection of the changes you posted looks like it could be > > improved by code changes, but that's obviously a large amount of work. > > In the end, the only thing that fixes this sort of stuff is to be more > rigid about making the code fit into 80 columns to begin with. I get > the impression though that a lot of people work in editor windows that > are wider than that, so the code looks fine to them when it slops over > a bit. Yes, it is all about <80 column output. The current pgindent does everything possible to accomplish that --- the question is whether we want uglier code to do it. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
On Fri, Jun 16, 2017 at 08:10:13PM +0530, Amit Kapila wrote: > On Fri, Jun 16, 2017 at 7:03 AM, Sergey Burladyan <eshkin...@gmail.com> wrote: > > Bruce Momjian <br...@momjian.us> writes: > > > >> ! against the old primary and standby clusters. Verify that the > >> ! Latest checkpoint location values match in all clusters. > > > > For "Log-Shipping only" standby server this cannot be satisfied, because > > last WAL from master (with shutdown checkpoint) never archived. > > > > Yeah, we have ensured that all the transactions before shutdown > checkpoint got archived. It is done in commit > 2e6107cb621d003dcab0df53ac8673ea67c4e467. However, it is not clear to > me neither it is mentioned in comments why we have done it that way. Yes, I am confused why Sergey doesn't see that behavior. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
On Fri, Jun 16, 2017 at 04:33:16AM +0300, Sergey Burladyan wrote: > Bruce Momjian <br...@momjian.us> writes: > > ! > > ! Also, if upgrading standby servers, change wal_level > > ! to replica in the postgresql.conf file on > > ! the new cluster. > > > > > > I am not sure how this help. > > wal_level is reset by pg_resetxlog during pg_upgrade, so it does not > depend on postgresql.conf. After pg_upgrade wal_level always is > 'minimal', that is why you must start and stop new master before rsync: > > output > $ "$bin"/pg_controldata "$ver" | grep wal_level > wal_level setting:replica > > $ "$bin"/pg_resetwal "$ver" > Write-ahead log reset > > $ "$bin"/pg_controldata "$ver" | grep wal_level > wal_level setting:minimal > Yes, I see that, but pg_resetxlog is run _before_ the _new_ cluster is started for the last time, so in my testing the wal_level at the end of pg_upgrade matches the value in postgresql.conf, e.g. "replica". For example: Upgrade Complete Optimizer statistics are not transferred by pg_upgrade so, once you start the new server, consider running: ./analyze_new_cluster.sh Running this script will delete the old cluster's data files: ./delete_old_cluster.sh $ pg_controldata /u/pg/data/ | grep wal_level wal_level setting:replica The way pg_upgrade uses rsync, the standby never needs to replay the WAL when it starts up because we already copied the changed system tables and hard linked the user data files. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Fri, Jun 16, 2017 at 11:06:39AM +0300, Konstantin Knizhnik wrote: > Encryption is much easier to implement than compression, because it is not > changing page size. So I do not see any "complexity and flexibility > challenges" here. > Just for reference I attached to this mail our own encryption patch. I do I didn't see you using CPU AES instructions, which can improve performance by 3-10x. Is there a reason? > Postgres buffer manager interface significantly simplifies integration of > encryption and compression. There is actually single path through which data > is fetched/stored to the disk. > It is most obvious and natural solution to decompress/decrypt data when it > is read from the disk to page pool and compress/encrypt it when it is > written back. Taken in account that memory is cheap now and many databases > can completely fit in memory, storing pages in the buffer cache in plain > (decompressed/decrypted) format allows to minimize overhead of > compression/encryption and its influence on performance. For read only > queries working with cached data performance will be exactly the same as > without encryption/compression. > Write speed for encrypted pages will be certainly slightly worse, but still > encryption speed is much higher than disk IO speed. Good point. > I do not think that pluggable storage API is right approach to integrate > compression and especially encryption. It is better to plugin encryption > between buffer manager and storage device, > allowing to use it with any storage implementation. Also it is not clear to > me whether encryption of WAL can be provided using pluggable storage API. Yes, you are completely correct. I withdraw my suggestion of doing it as plugin storage. > The last discussed question is whether it is necessary to encrypt temporary > data (BufFile). In our solution we encrypt only main fork of non-system > relations and do no encrypt temporary relations. It may cause that some > secrete data will be stored at this disk in non-encrypted format. But > accessing this data is not trivial. You can not just copy/stole disk, open > database and do "select * from SecreteTable": you will have to extract data > from raw file yourself. So looks like it is better to allow user to make > choice whether to encrypt temporary data or not. If we go forward with in-db encryption, I think we are going to have to have a discussion about what parts of PGDATA need to be encrypted, i.e., I don't think pg_clog needs encryption. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Thu, Jun 15, 2017 at 08:08:05PM -0400, Bruce Momjian wrote: > On Thu, Jun 15, 2017 at 04:56:36PM -0700, Andres Freund wrote: > > how few concerns about this feature's complexity / maintainability > > impact have been raised. > > Yeah, I guess we will just have to wait to see it since other people are > excited about it. My concern is code complexity and usability > challenges, vs punting the problem to the operating system, though > admittedly there are some cases where that is not possible. I know some OS's can create file systems inside files. Can you encrypt such file storage as non-root? I assume that is just too odd. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Shortened URLs for commit messages
On Thu, Jun 15, 2017 at 01:05:19PM -0400, Bruce Momjian wrote: > On Tue, May 23, 2017 at 11:25:07PM -0400, Bruce Momjian wrote: > > I have written the following sed script to convert regular Postgres > > email message URLs to their shorter form for commit messages: > > > > sed > > 's;http\(s\?\)://www\.postgresql\.org/message-id/;http\1://postgr.es/m/;gi' > > > > in case this is helpful to anyone. > > Oh, here's another one. I use an optional "Discussion:" tag in my > commit messages. This sed script converts a message-id into a proper > URL: > > sed '/http/!s;^\(Discussion: *\)\(.*\)$;\1https://postgr.es/m/\2;' Oh, here is a fixed version that requires an @ sign, which all message id's have: sed '/http/!s;^\(Discussion: *\)\(.*@.*\)$;\1https://postgr.es/m/\2;' -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Thu, Jun 15, 2017 at 04:56:36PM -0700, Andres Freund wrote: > On 2017-06-15 19:44:43 -0400, Bruce Momjian wrote: > > Understood, but now you are promoting a feature with an admittedly-poor > > API, duplication of an OS feature, and perhaps an invasive change to the > > code. > > *Perhaps* an invasive change to the code? To me it's pretty evident > that this'll be a pretty costly feature from that angle. We've quite a > few places that manipulate on-disk files, and they'll all have to be > manipulated. Several of those are essentially critical sections, adding > memory allocations to them wouldn't be good, so we'll need > pre-allocation APIs. > > I've only skimmed the discussion, but based on that I'm very surprised > how few concerns about this feature's complexity / maintainability > impact have been raised. Yeah, I guess we will just have to wait to see it since other people are excited about it. My concern is code complexity and usability challenges, vs punting the problem to the operating system, though admittedly there are some cases where that is not possible. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Thu, Jun 15, 2017 at 07:51:36PM -0400, Alvaro Herrera wrote: > Bruce Momjian wrote: > > On Thu, Jun 15, 2017 at 07:27:55PM -0400, Stephen Frost wrote: > > > I expect the same would happen with the shell-command approach suggested > > > up-thread and the prompt-on-stdin approach too, they aren't great but I > > > expect users would still use the feature. As Robert and I have > > > mentioned, there is a good bit of value to having this feature simply > > > because it avoids the need to get someone with root privileges to set up > > > an encrypted volume and I don't think having to use a shell command or > > > providing the password on stdin at startup really changes that very > > > much. > > > > Understood, but now you are promoting a feature with an admittedly-poor > > API, duplication of an OS feature, and perhaps an invasive change to the > > code. Those are high hurdles. > > I thought we called it "incremental development". From the opposite > point of view, would you say we should ban use of passphrase-protected > SSL key files because the current user interface for them is bad? > > I have no use for data-at-rest encryption myself, but I wouldn't stop > development just because the initial design proposal doesn't include > top-notch key management. Yes, but we have to have a plan on how to improve it. Why add a feature that is hard to maintain, and hard to use. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Thu, Jun 15, 2017 at 07:27:55PM -0400, Stephen Frost wrote: > I expect the same would happen with the shell-command approach suggested > up-thread and the prompt-on-stdin approach too, they aren't great but I > expect users would still use the feature. As Robert and I have > mentioned, there is a good bit of value to having this feature simply > because it avoids the need to get someone with root privileges to set up > an encrypted volume and I don't think having to use a shell command or > providing the password on stdin at startup really changes that very > much. Understood, but now you are promoting a feature with an admittedly-poor API, duplication of an OS feature, and perhaps an invasive change to the code. Those are high hurdles. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Thu, Jun 15, 2017 at 06:41:08PM -0400, Stephen Frost wrote: > > > > One serious difference between in-database-encryption and SSH keys is > > > > that the use of passwords for SSH is well understood and reasonable to > > > > use, while I think we all admit that use of passwords for database > > > > objects like SSL keys is murky. Use of keys for OS-level encryption is > > > > a little better handled, but not as clean as SSH keys. > > > > > > Peter pointed out upthread that our handling of SSL passphrases leaves > > > a lot to be desired, and that maybe we should fix that problem first; > > > I agree. But I don't think this is any kind of intrinsic limitation > > > of PostgreSQL vs. encrypted filesystems vs. SSH; it's just a > > > quality-of-implementation issue. > > I'm not thrilled with asking Ants to implement a solution to SSL > passphrases, and generalizing it to work for this, to get this feature > accepted. I assume that the reason for asking for that work to be done > now is because we decided that the current approach for SSL sucks but we > couldn't actually drop support for it, but we don't want to add other > features which work in a similar way because, well, it sucks. My point is that if our support for db-level encryption is as bad as SSL key passwords, then it will be nearly useless, so we might as well not have it. Isn't that obvious? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Thu, Jun 15, 2017 at 05:04:17PM -0400, Robert Haas wrote: > > Also, there is the sense that security requires > > trust of the root user, while using Postgres doesn't require the root > > user to also use Postgres. > > I don't understand this. It is certainly true that you're running > binaries owned by root, the root user could Trojan the binaries and > break any security you think you have. But that problem is no better > or worse for PostgreSQL than anything else. I couldn't find a cleaner way to see it --- it is that database use doesn't involve the root user using it, while database security requires the root user to also be security-conscious. > > One serious difference between in-database-encryption and SSH keys is > > that the use of passwords for SSH is well understood and reasonable to > > use, while I think we all admit that use of passwords for database > > objects like SSL keys is murky. Use of keys for OS-level encryption is > > a little better handled, but not as clean as SSH keys. > > Peter pointed out upthread that our handling of SSL passphrases leaves > a lot to be desired, and that maybe we should fix that problem first; > I agree. But I don't think this is any kind of intrinsic limitation > of PostgreSQL vs. encrypted filesystems vs. SSH; it's just a > quality-of-implementation issue. I think there are environmental issues that make password use on SSH easier than the other cases --- it isn't just code quality. However, it would be good to research how SSH handles it to see if we can get any ideas. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Thu, Jun 15, 2017 at 03:09:32PM -0400, Robert Haas wrote: > To be honest, I find the hostility toward this feature a bit baffling. > The argument seems to be essentially that we shouldn't have this > feature because we'd have to maintain the code and many of the same > goals could be accomplished by using facilities that already exist > outside the database server. But that's also true for parallel query > (cf. Stado), logical replication (cf. Slony, Bucardo, Londiste), > physical replication (cf. DRBD), partitioning (cf. pg_partman), RLS > (cf. veil), and anything that could be written as application logic > (eg. psql's \if ... \endif, every procedural language we have, > user-defined functions themselves, database-enforced constraints, > FDWs). Yet, in every one of those cases, we find it worthwhile to > have the feature because it works better and is easier to use when > it's built in. I don't think that a patch for this feature is likely > to be bigger than (or even as large as) the patches for logical > replication or parallel query, and it will probably be less work to > maintain going forward than either. I think the big win for having OS features in the database is selectivity --- the ability to selectively apply a feature to part of the database. This is what you are doing by putting a password on your SSH key, and my idea about row encryption. It is also a question of convenience. If SSH told users they have to create an encrypted volume to store their SSH keys with a password, it would be silly, since the files are so small compared to a file system. I think the assumption is that any security-concerned deployment of Postgres will already have Postgres on its own partition and have the root administrator involved. I think it is this assumption that drives the idea that requiring root to run Postgres doesn't make sense, but it does to do encryption. Also, there is the sense that security requires trust of the root user, while using Postgres doesn't require the root user to also use Postgres. One serious difference between in-database-encryption and SSH keys is that the use of passwords for SSH is well understood and reasonable to use, while I think we all admit that use of passwords for database objects like SSL keys is murky. Use of keys for OS-level encryption is a little better handled, but not as clean as SSH keys. I admit there is no hard line here, so I guess we will have to see what the final patch looks like. I am basing my statements on what I guess the complexity will be. Complexity has a cost so we will have to weigh it when we see it. When SSH added password access, it was probably an easy decision because the use-case was high and the complexity was low. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
On Thu, Jun 15, 2017 at 03:00:18PM +0530, Amit Kapila wrote: > On Wed, Jun 14, 2017 at 8:44 PM, Bruce Momjian <br...@momjian.us> wrote: > > On Wed, Jun 14, 2017 at 07:45:17PM +0530, Amit Kapila wrote: > >> > Now, it seems we later added a doc section early on that talks about > >> > "Verify standby servers" so I have moved the wal_level section into that > >> > block, which should be safe. There is now no need to start/stop the new > >> > server since pg_upgrade will do that safely already. > >> > > >> > >> ! > >> ! Also, if upgrading standby servers, change wal_level > >> ! to replica in the postgresql.conf file on > >> ! the new cluster. > >> > >> I think it is better to indicate that this is required for the master > >> cluster (probably it is clear for users) /"on the new cluster."/"on > >> the new master cluster.". Do we need something different for v10 where > >> default wal_level is 'replica' > > > > You know, I thought about that and was afraid saying "new master > > cluster" would be confusing because it isn't a master _yet_, but if you > > feel it will help, and I considered it, let's add it. The problem is > > that in the old instructions, at the point we were mentioning this, it > > was the new master, which is why I evaluated removing it in the first > > place. (Yeah, I am amazed I considered all these cases.) > > > > Updated patch attached. Thanks. > > > > Looks good to me. Patch applied back to 9.5, where these instructions first appeared. A mention of this will appear in the minor release notes. Thanks for everyone's work on this. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Shortened URLs for commit messages
On Tue, May 23, 2017 at 11:25:07PM -0400, Bruce Momjian wrote: > I have written the following sed script to convert regular Postgres > email message URLs to their shorter form for commit messages: > > sed > 's;http\(s\?\)://www\.postgresql\.org/message-id/;http\1://postgr.es/m/;gi' > > in case this is helpful to anyone. Oh, here's another one. I use an optional "Discussion:" tag in my commit messages. This sed script converts a message-id into a proper URL: sed '/http/!s;^\(Discussion: *\)\(.*\)$;\1https://postgr.es/m/\2;' For example: -Discussion: 87wp8o506b.fsf@seb.koffice.internal +Discussion: https://postgr.es/m/87wp8o506b.fsf@seb.koffice.internal Yeah! -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Wed, Jun 14, 2017 at 06:41:43PM +0300, Ants Aasma wrote: > On Wed, Jun 14, 2017 at 6:26 PM, Bruce Momjian <br...@momjian.us> wrote: > > Are you checking the CPU type or if AES instructions are enabled on the > > CPU? I ask this because I just realized in researching my new TLS talk > > that my BIOS defaults to AES instructions disabled, and I had to > > manually enable it. > > There is zero code for that now, but the plan was to check the CPUID > instruction. My understanding is that it should report what is > currently enabled on the CPU. Will double check when actually writing > the code for the check. Just for specifics, I have two Intel Xeon CPU E5620, but the AES instructions were disabled for this CPU since 2012 when I bought it. :-( The good news is that only recently have I forced https in some pages so this is the first time I heavily need it. I now have a boot test, which returns 16: grep -c '\<aes\>' /proc/cpuinfo > >> > I anticipate that one of the trickier problems here will be handling > >> > encryption of the write-ahead log. Suppose you encrypt WAL a block at > >> > a time. In the current system, once you've written and flushed a > >> > block, you can consider it durably committed, but if that block is > >> > encrypted, this is no longer true. A crash might tear the block, > >> > making it impossible to decrypt. Replay will therefore stop at the > >> > end of the previous block, not at the last record actually flushed as > >> > would happen today. > >> > >> My patch is currently doing a block at a time for WAL. The XTS mode > > > > Uh, how are you writing partial writes to the WAL. I assume you are > > doing a streaming cipher so you can write in increments, right? > > We were doing 8kB page aligned writes to WAL anyway. I just encrypt > the block before it gets written out. Oh, we do. The beauty of streaming ciphers built on block ciphers is that you can pre-compute the cipher to be XOR'ed with the data because the block cipher output doesn't depend on the user data. This is used for SSH, for example. > >> I think we need to require wal_log_hints=on when encryption is > >> enabled. Currently I have not considered tearing on CLOG bits. Other > >> SLRUs probably have similar issues. I need to think a bit about how to > >> solve that. > > > > I am not sure if clog even needs to be encrypted. > > Me neither, but it currently is, and it looks like that's broken in a > "silently corrupts your data" way in face of torn writes. Using OFB > mode (xor plaintext with pseudorandom stream for cipher) looks like it > might help here, if other approaches fail. I would just document the limitation and move on. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Wed, Jun 14, 2017 at 06:10:32PM +0300, Ants Aasma wrote: > On Tue, Jun 13, 2017 at 6:35 PM, Robert Haas <robertmh...@gmail.com> wrote: > > Performance is likely to be poor on large databases, > > because every time a page transits between shared_buffers and the > > buffer cache we've got to en/decrypt, but as long as it's only poor > > for the people who opt into the feature I don't see a big problem with > > that. > > It would make sense to tune the database with large shared buffers if > encryption is enabled. That should make sure that most shared buffers > traffic is going to disk anyway. As for performance, I have a > prototype assembly implementation of AES that does 3GB/s/core on my > laptop. If we add that behind a CPUID check the overhead should be > quite reasonable. Are you checking the CPU type or if AES instructions are enabled on the CPU? I ask this because I just realized in researching my new TLS talk that my BIOS defaults to AES instructions disabled, and I had to manually enable it. > > I anticipate that one of the trickier problems here will be handling > > encryption of the write-ahead log. Suppose you encrypt WAL a block at > > a time. In the current system, once you've written and flushed a > > block, you can consider it durably committed, but if that block is > > encrypted, this is no longer true. A crash might tear the block, > > making it impossible to decrypt. Replay will therefore stop at the > > end of the previous block, not at the last record actually flushed as > > would happen today. > > My patch is currenly doing a block at a time for WAL. The XTS mode Uh, how are you writing partial writes to the WAL. I assume you are doing a streaming cipher so you can write in increments, right? > used to encrypt has the useful property that blocks that share > identical prefix unencrypted also have identical prefix when > encrypted. It requires that the tearing is 16B aligned, but I think > that is true for pretty much all storage systems. That property of > course has security downsides, but for table/index storage we have a > nonce in the form of LSN in the page header eliminating the issue. > > > So, your synchronous_commit suddenly isn't. A > > similar problem will occur any other page where we choose not to > > protect against torn pages using full page writes. For instance, > > unless checksums are enabled or wal_log_hints=on, we'll write a data > > page where a single bit has been flipped and assume that the bit will > > either make it to disk or not; the page can't really be torn in any > > way that hurts us. But with encryption that's no longer true, because > > the hint bit will turn into much more than a single bit flip, and > > rereading that page with half old and half new contents will be the > > end of the world (TM). I don't know off-hand whether we're > > protecting, say, CLOG page writes with FPWs.: because setting a couple > > of bits is idempotent and doesn't depend on the existing page > > contents, we might not need it currently, but with encryption, every > > bit in the page depends on every other bit in the page, so we > > certainly would. I don't know how many places we've got assumptions > > like this baked into the system, but I'm guessing there are a bunch. > > I think we need to require wal_log_hints=on when encryption is > enabled. Currently I have not considered tearing on CLOG bits. Other > SLRUs probably have similar issues. I need to think a bit about how to > solve that. I am not sure if clog even needs to be encrypted. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
On Wed, Jun 14, 2017 at 07:45:17PM +0530, Amit Kapila wrote: > > Now, it seems we later added a doc section early on that talks about > > "Verify standby servers" so I have moved the wal_level section into that > > block, which should be safe. There is now no need to start/stop the new > > server since pg_upgrade will do that safely already. > > > > ! > ! Also, if upgrading standby servers, change wal_level > ! to replica in the postgresql.conf file on > ! the new cluster. > > I think it is better to indicate that this is required for the master > cluster (probably it is clear for users) /"on the new cluster."/"on > the new master cluster.". Do we need something different for v10 where > default wal_level is 'replica' You know, I thought about that and was afraid saying "new master cluster" would be confusing because it isn't a master _yet_, but if you feel it will help, and I considered it, let's add it. The problem is that in the old instructions, at the point we were mentioning this, it was the new master, which is why I evaluated removing it in the first place. (Yeah, I am amazed I considered all these cases.) Updated patch attached. Thanks. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml new file mode 100644 index bf58a0a..05fa053 *** a/doc/src/sgml/ref/pgupgrade.sgml --- b/doc/src/sgml/ref/pgupgrade.sgml *** NET STOP postgresql-9.0 *** 317,331 ! Verify standby servers ! If you are upgrading Streaming Replication and Log-Shipping standby ! servers, verify that the old standby servers are caught up by running ! pg_controldata against the old primary and standby ! clusters. Verify that the Latest checkpoint location ! values match in all clusters. (There will be a mismatch if old ! standby servers were shut down before the old primary.) --- 317,338 ! Prepare for standby server upgrades ! If you are upgrading standby servers (as outlined in section ), verify that the old standby ! servers are caught up by running pg_controldata ! against the old primary and standby clusters. Verify that the ! Latest checkpoint location values match in all clusters. ! (There will be a mismatch if old standby servers were shut down ! before the old primary.) ! ! ! ! Also, if upgrading standby servers, change wal_level ! to replica in the postgresql.conf file on ! the new master cluster. *** pg_upgrade.exe *** 410,416 ! Upgrade Streaming Replication and Log-Shipping standby servers --- 417,423 ! Upgrade Streaming Replication and Log-Shipping standby servers *** pg_upgrade.exe *** 471,486 - - Start and stop the new master cluster - - -In the new master cluster, change wal_level to -replica in the postgresql.conf file -and then start and stop the cluster. - - - Run rsync --- 478,483 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: pgindent (was Re: [HACKERS] [COMMITTERS] pgsql: Preventive maintenance in advance of pgindent run.)
On Wed, Jun 14, 2017 at 10:38:40AM -0400, Tom Lane wrote: > btw, I was slightly amused to notice that this version still calls itself > > $ indent -V > pg_bsd_indent 1.3 > > Don't know what you plan to do with that program name, but we certainly > need a version number bump so that pgindent can tell that it's got an > up-to-date copy. 1.4? 2.0? For Piotr's reference, we will update src/tools/pgindent/pgindent to match whatever new version number you use. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
ission link? Is that used a lot? > > > > Is having the db encrypt every write a reasonable solution to that? > > > > > > There's multiple use-cases here. Making it easier to copy the database > > > is just one of them and it isn't the biggest one. The biggest benefit > > > is that there's cases where filesystem-level encryption isn't really an > > > option or, even if it is, it's not desirable for other reasons. > > > > I am thinking NAS storage you don't trust, though there is the leakage > > there. > > Yes, NAS or SAN storage where you don't want the NAS/SAN administrators > to have access to the data is a good example of where encryption would > be useful. Of course, either filesystem-level or PG-level encryption > would address that, but with the trade-off that PG-level encryption > would allow the NAS/SAN administrators to see the file metadata, as > discussed above. Uh, as far as I understand it, SAN could technically use encryption because you are sending blocks to the network storage and you could encrypt the blocks before transfer. However, I don't think that would work for NAS/NFS. > > Also, has anyone asked users if they would find db-encryption better > > than file system encryption? > > I've been asked for this capability multiple times from our users and > have generally pushed back and encouraged filesystem-level encryption. > That hasn't always been an acceptable solution, unfortunately. Yes, it would be good to know how often that happens, and whether we should be adjusting Postgres to address it. The idea posted of using plugin storage for encryption seems like a cool idea, and compression and stuff could be added. (However, I realize encryption and compression doesn't work well because of information leakage.) -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Wed, Jun 14, 2017 at 04:13:57PM +0300, Aleksander Alekseev wrote: > > > While I agree that configuring full disk encryption is not technically > > > difficult, it requires much more privileged access to the system and > > > basically requires the support of a system administrator. In addition, > > > if a volume is not available for encryption, PostgreSQL support for > > > encryption would still allow for its data to be encrypted and as others > > > have mentioned can be enabled by the DBA alone. > > > > Frankly I'm having difficulties imagining when it could be a real > > problem. It doesn't seem to be such a burden to ask a colleague for > > assistance in case you don't have sufficient permissions to do > > something. And I got a strong feeling that solving bureaucracy issues of > > specific organizations by changing PostgreSQL core in very invasive way > > (keeping in mind testing, maintaining, etc) is misguided. > > In the same time implementing a plugable storage API and then implementing > encrypted / compressed / whatever storage in a standalone extension using > this API seems to be a reasonable thing to do. Agreed, good point. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: pgindent (was Re: [HACKERS] [COMMITTERS] pgsql: Preventive maintenance in advance of pgindent run.)
On Tue, Jun 13, 2017 at 05:00:31PM -0400, Tom Lane wrote: > Anyway, it is now time to fish or cut bait. I don't think we can wait > much longer to decide whether we're going to adopt this new indent > version for PG 10. I think we should. The floor is open for votes. Works for me. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Tue, Jun 13, 2017 at 04:08:29PM -0400, Peter Eisentraut wrote: > On 6/13/17 15:51, Bruce Momjian wrote: > > Isn't the leakage controlled by OS permissions, so is it really leakage, > > i.e., if you can see the leakage, you probably have bypassed the OS > > permissions and see the key and data anyway. > > One scenario (among many) is when you're done with the disk. If the > content was fully encrypted, then you can just throw it into the trash > or have your provider dispose of it or reuse it. If not, then, > depending on policy, you will have to physically obtain it and burn it. Oh, I see your point --- db-level encryption stores the file system as mountable on the device, while it is not with storage-level encryption --- got it. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Tue, Jun 13, 2017 at 03:20:12PM -0400, Stephen Frost wrote: > Bruce, > > * Bruce Momjian (br...@momjian.us) wrote: > > On Tue, Jun 13, 2017 at 02:38:58PM -0400, Stephen Frost wrote: > > > It's good to discuss what the feature would bring and what cases it > > > doesn't cover, as well as discussing how it can be designed to make sure > > > that later improvements are able to be done without having to change it > > > around. I do think it's a good idea for us to consider taking an > > > incremental approach where we're adding pieces and building things up as > > > we go. I'm concerned that if we try to do too much in the initial > > > implementation that we'll end up not having anything. > > > > > > As it relates to the different attack vectors that this would address, > > > it's primairly the same ones which filesystem-level encryption also > > > addresses, but it's an improvement when it comes to ease of use. > > > Unfortunately, it won't address cases where the OS is compromised. > > > > OK, so let's go back. You are saying there are no security benefits to > > this vs. file system encryption. > > I'm not sure that I can see any, myself.. Perhaps I'm wrong there, but > it seems unlikely that this would be an improvement over filesystem > level encryption in the general sense. I'm not sure that I see it as > really worse than filesystem-level encryption either, to be clear. > There's a bit of increased information leakage, as Peter mentioned and I > agreed with, but it's not a lot and I expect in most cases that > information leak would be acceptable. That seems like something which > would need to be considered on a case-by-case basis. Isn't the leakage controlled by OS permissions, so is it really leakage, i.e., if you can see the leakage, you probably have bypassed the OS permissions and see the key and data anyway. > > The benefit is allowing configuration > > in the database rather than the OS? > > No, the benefit is that the database administrator can configure it and > set it up and not have to get an OS-level administrator involved. There > may also be other reasons why filesystem-level encryption is difficult > to set up or use in a certain environment, but this wouldn't depend on > anything OS-related and therefore could be done. OK, my only point here is that we are going down a slippery slope of implementing OS things in the database. There is nothing wrong with that but it has often been something we have avoided, because of the added complexity required in the db server. As a counter-example, we only added an external collation library to Postgres when we clearly saw a benefit, e.g. detecting collation changes. > > You stated you can transfer > > db-level encrypted files between servers, but can't you do that anyway? > > If the filesystem is encrypted and you wanted to transfer the entire > cluster from one system to another, keeping it encrypted with the same > key, you'd have to transfer the entire filesystem at a block level. > That's not typically very easy to do (ZFS, specifically, has this > capability where you can export a filesystem and send it from one > machine to another, but I don't know of any other filesystems which do > and ZFS isn't always an option..). > > You could go through a process of re-encrypting the files prior to > transferring them, or deciding that simply having the transport > mechanism encrypted is sufficient (eg: SSH), but if what you really want > to do is keep the existing encryption of the database and transfer it to > another system, this allows that pretty easily. > > For example, you could simply do: > > cp -a /path/to/PG /mnt/usb > > and you're done. If you're using filesystem level encryption then you'd > have to re-encrypt the data, using something like: > > tar -cf - /path/to/PG | openssl -key private.key > > /mnt/usb/encrypted_cluster.tar > > And then you would need openssl on the other system to decrypt it. > > Of course, either way you'd have to provide for a way to get the key > from one system to the other. Uh, doesn't scp do this? I have trouble seeing how avoiding calling openssl justifies changes to our database server. > Also, tools like pg_basebackup could be used, with nearly zero changes, > I think, to get an encrypted copy of the database for backup purposes, > removing the need to work out a way to handle encrypting backups. I do think we need much more documentation on how to encrypt things, though that is a separate issue. It might help to document how you _should_ do things now to see the limitations we have. > > Is the problem that you have to encrypt before sending and decrypt on &
Re: [HACKERS] Broken hint bits (freeze)
On Mon, Jun 12, 2017 at 06:31:11PM +0300, Vladimir Borodin wrote: > What about the following sequence? > > 1. Run pg_upgrade on master, > 2. Start it in single-user mode and stop (to get right wal_level in > pg_control), > 3. Copy pg_control somewhere, > 4. Start master, run analyze and stop. > 5. Put the control file from step 3 to replicas and rsync them according to > the > documentation. > > And I think that step 10.f in the documentation [1] should be fixed to mention > starting in single-user mode or with disabled autovacuum. > > [1] https://www.postgresql.org/docs/devel/static/pgupgrade.html First, I want to apologize for not getting involved in this thread earlier, and I want to thank everyone for the huge amount of detective work in finding the cause of this bug. Let me see if I can replay how the standby server upgrade instructions evolved over time. Initially we knew that we had to set wal_level to replica+ so that when you reconnect to the standby servers, the WAL would have the right contents. (We are basically simulating pg_start/stop backup with rsync.) There was a desire to have those instructions inside a documentation block dedicated to standby server upgrades, so the wal_level adjustment and new server start/stop was added to that block. I assumed a start/stop could not modify the WAL, or at least nothing important would happen, but obviously I was wrong. (pg_upgrade takes steps to ensure that nothing happens.) Adding ANALYZE in there just made it worse, but the problem always existed. I sure hope others haven't had a problem with this. Now, it seems we later added a doc section early on that talks about "Verify standby servers" so I have moved the wal_level section into that block, which should be safe. There is now no need to start/stop the new server since pg_upgrade will do that safely already. I plan to patch this back to 9.5 where these instructions were added. I will mention that this should be in the minor release notes. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml new file mode 100644 index bf58a0a..18e6af3 *** a/doc/src/sgml/ref/pgupgrade.sgml --- b/doc/src/sgml/ref/pgupgrade.sgml *** NET STOP postgresql-9.0 *** 317,331 ! Verify standby servers ! If you are upgrading Streaming Replication and Log-Shipping standby ! servers, verify that the old standby servers are caught up by running ! pg_controldata against the old primary and standby ! clusters. Verify that the Latest checkpoint location ! values match in all clusters. (There will be a mismatch if old ! standby servers were shut down before the old primary.) --- 317,338 ! Prepare for standby server upgrades ! If you are upgrading standby servers (as outlined in section ), verify that the old standby ! servers are caught up by running pg_controldata ! against the old primary and standby clusters. Verify that the ! Latest checkpoint location values match in all clusters. ! (There will be a mismatch if old standby servers were shut down ! before the old primary.) ! ! ! ! Also, if upgrading standby servers, change wal_level ! to replica in the postgresql.conf file on ! the new cluster. *** pg_upgrade.exe *** 410,416 ! Upgrade Streaming Replication and Log-Shipping standby servers --- 417,423 ! Upgrade Streaming Replication and Log-Shipping standby servers *** pg_upgrade.exe *** 471,486 - - Start and stop the new master cluster - - -In the new master cluster, change wal_level to -replica in the postgresql.conf file -and then start and stop the cluster. - - - Run rsync --- 478,483 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Tue, Jun 13, 2017 at 02:38:58PM -0400, Stephen Frost wrote: > It's good to discuss what the feature would bring and what cases it > doesn't cover, as well as discussing how it can be designed to make sure > that later improvements are able to be done without having to change it > around. I do think it's a good idea for us to consider taking an > incremental approach where we're adding pieces and building things up as > we go. I'm concerned that if we try to do too much in the initial > implementation that we'll end up not having anything. > > As it relates to the different attack vectors that this would address, > it's primairly the same ones which filesystem-level encryption also > addresses, but it's an improvement when it comes to ease of use. > Unfortunately, it won't address cases where the OS is compromised. OK, so let's go back. You are saying there are no security benefits to this vs. file system encryption. The benefit is allowing configuration in the database rather than the OS? You stated you can transfer db-level encrypted files between servers, but can't you do that anyway? Is the problem that you have to encrypt before sending and decrypt on arrival, if you don't trust the transmission link? Is that used a lot? Is having the db encrypt every write a reasonable solution to that? As far as future features, we don't have to add the all features at this time, but if someone has a good idea for an API and we can make it work easily while adding this feature, why wouldn't we do that? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Tue, Jun 13, 2017 at 02:23:39PM -0400, Stephen Frost wrote: > I'm not trying to shut down discussion, I'm simply pointing out where > this feature will be helpful and where it won't be. If there's a way to > make it better and able to address an attack where the OS permission > system is bypassed, that'd be great, but I certainly don't know of any > way to do that and we don't want to claim that this feature will protect > against an attack vector that it won't. > > If the lack of that means you don't support the feature, that's > unfortunate as it seems to imply, to me at least, that we'll never have > any kind of encryption because there's no way for it to prevent attacks > where the OS permission system is able to be bypassed. It means if we can't discuss the actual benefits that this feature brings, and doesn't bring, and how it will deal with future feature additions, then you are right we will never have it. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Tue, Jun 13, 2017 at 01:44:51PM -0400, Stephen Frost wrote: > Just to be clear, I don't have any issue with discussing the idea that > we want to get to a point where we can work with multiple keys and > encrypt different tables with different keys (or not encrypt certain > tables, et al) with the goal of implementing the single-key approach in > a way that allows us to expand on it down the road easily, I just don't > think we need to have it all done in the very first patch which adds the > ability to encrypt the data files. Maybe you're not saying that it has > to be included in the first implementation, in which case we seem to > just be talking past each other, but that isn't the impression I got.. We don't want to implement all-cluster encryption with a simple user API and then realize we need another API for later encryption features, do we? And we are not going to know that if we don't talk about it, but hey, this is just an email thread and I can marshal opposition to the feature later when it appears, and point this all out again. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Tue, Jun 13, 2017 at 01:25:00PM -0400, Stephen Frost wrote: > > I think the big win of Postgres doing the encryption is that the > > user-visible file system is no longer a target (assuming OS permissions > > are bypassed), while for file system encryption it is the storage device > > that is encrypted. > > If OS permissions are bypassed then the encryption isn't going to help > because the attacker can just access shared memory. > > The big wins for doing the encryption in PostgreSQL are, as Robert and I > have both mentioned on this thread already, that it provides > data-at-rest encryption in an easier to deploy fashion which will work > the same across different systems and allows the encrypted cluster to be > transferred more easily between systems. There are almsot certainly > other wins from having PG do the encryption, but the above strikes me as > the big ones, and those are certainly valuable enough on their own for > us to seriously consider adding this capability. Since you seem to be trying to shut down discussion, I will simply say I am unimpressed that this use-case is sufficient justification to add the feature. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] v10beta pg_catalog diagrams
On Mon, Jun 12, 2017 at 04:07:35PM -0400, Peter Eisentraut wrote: > On 6/12/17 11:28, Neil Anderson wrote: > > I'm cross posting from general. I did some work to diagram the > > relationships in pg_catalog for v10. I would like to add it to the > > developer FAQ here > > https://wiki.postgresql.org/wiki/Developer_FAQ#Is_there_a_diagram_of_the_system_catalogs_available.3F > > > > but I thought I should check if people thought it appropriate and useful > > before I do? > > > > https://www.postgrescompare.com/2017/06/11/pg_catalog_constraints.html > > Go for it. Yeah, great. We have been talking about adding diagrams to our official docs but needed an updated toolchain, which I think we now have, so there is a lot of opportunity for growth here. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Tue, Jun 13, 2017 at 01:01:32PM -0400, Stephen Frost wrote: > > Well, usually the symetric key is stored using RSA and a symetric > > cipher is used to encrypt/decrypt the data. I was thinking of a case > > where you encrypt a row using a symetric key, then store RSA-encrypted > > versions of the symetric key encrypted that only specific users could > > decrypt and get the key to decrypt the data. > > This goes back to key management and I agree that it often makes sense > to use RSA or similar to encrypt the symmetric key, and this approach > would allow the user to do so. That doesn't actually give you a > "write-only" encryption option though, since any user who can decrypt > the symmetric key is able to use the symmetric key for both encryption > and decryption, and someone who only has access to the RSA encryption > key can't actually encrypt the data since they can't access the > symmetric key. I think the big win of Postgres doing the encryption is that the user-visible file system is no longer a target (assuming OS permissions are bypassed), while for file system encryption it is the storage device that is encrypted. My big question is how many times are the OS permissions bypassed in a way that would also not expose the db clusters key or db data? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Transactional sequence stuff breaks pg_upgrade
On Tue, Jun 13, 2017 at 11:14:02AM -0400, Robert Haas wrote: > Also, I think that if we did it that way, it would be significantly > harder to debug. Right now, if something goes boom, you can look at > the old and new clusters and figure out what doesn't match, but if > pg_upgrade renumbered everything, you would no longer be able to do > that, or at least not easily. FYI, pg_upgrade is designed to go boom if something doesn't look right because it can't anticipate what changes might be made to Postgres in the future. boom == feature! -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Tue, Jun 13, 2017 at 09:55:10AM -0700, Joe Conway wrote: > > That way, if the user wants to store the key in an unencrypted text > > file, they can set the encryption_key_command = 'cat /not/very/secure' > > and call it a day. If they want to prompt the user on the console or > > request the key from an HSM or get it in any other way, they just have > > to write the appropriate shell script. We just provide mechanism, not > > policy, and the user can adopt any policy they like, from an extremely > > insecure policy to one suitable for Fort Knox. > > Agreed, but as Bruce alluded to, we want this to be a master key, which > is in turn used to encrypt the actual key, or keys, that are used to > encrypt the data. The actual data encryption keys could be very long > randomly generated binary, and there could be more than one of them > (e.g. one per tablespace) in a file which is encrypted with the master > key. This is more secure and allows, for example the master key to be > changed without having to decrypt/re-encrypt the entire database. Yes, thank you. Also, you can make multiple RSA-encrypted copies of the symetric key, one for each role you want to view the data. And good point on the ability to change the RSA key/password without having to reencrypt the data. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Tue, Jun 13, 2017 at 12:23:01PM -0400, Stephen Frost wrote: > > As I understand it, having encryption in the database means the key is > > stored in the database, while having encryption in the file system means > > the key is stored in the operating system somewhere. > > Key management is an entirely independent discussion from this and the > proposal from Ants, as I understand it, is that the key would *not* be > in the database but could be anywhere that a shell command could get it > from, including possibly a HSM (hardware device). > > Having the data encrypted by PostgreSQL does not mean the key is stored > in the database. Yes, I was just simplifying. > > Of course, if the > > key stored in the database is visible to someone using the operating > > system, we really haven't added much/any security --- I guess my point > > is that the OS easily can hide the key from the database, but the > > database can't easily hide the key from the operating system. > > This is correct- the key must be available to the PostgreSQL process > and therefore someone with privileged access to the OS would be able to > retrieve the key, but that's also true of filesystem encryption. > > Basically, if the server is doing the encryption and you have the > ability to read all memory on the server then you can get the key. Of > course, if you can read all memory then you can just look at shared > buffers and you don't really need to bother yourself with the key or > the encryption, and it doesn't make any difference if you're encrypting > in the database or in the filesystem. That attack vector is not one > which this is intending to address. My point is that if you have the key accessible to the database server, both the database server and OS have access to it. If you store it in the OS, only the OS has access to it. > > I have to admit we tend to avoid heavy-API solutions that are designed > > just to work around deployment challenges. Commercial databases are > > fine in doing that, but it leads to very complex products. > > I'm not following what you mean here. By adding all-cluster encryption, we are re-implementing something the OS does just fine, in most cases. We are going to have API overhead to do it in the database, and historically we have avoided that. > > One cool idea I have is using public encryption to store the encryption > > key by users who don't know the decryption key, e.g. RSA. It would be a > > write-only encryption option. Not sure how useful that is, but it > > easily possible, and doesn't require us to keep the _encryption_ key > > secret, just the decryption one. > > The downside here is that asymmetric encryption is much more expensive > than symmetric encryption and that probably makes it a non-starter. I > do think we'll want to support multiple encryption methods and perhaps > we can have an option where asymmetric encryption is used, but that's > not what I expect will be typically used. Well, usually the symetric key is stored using RSA and a symetric cipher is used to encrypt/decrypt the data. I was thinking of a case where you encrypt a row using a symetric key, then store RSA-encrypted versions of the symetric key encrypted that only specific users could decrypt and get the key to decrypt the data. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Tue, Jun 13, 2017 at 11:04:21AM -0400, Stephen Frost wrote: > > Also, in the use case you describe, if you use pg_basebackup to make a > > direct encrypted copy of a data directory, I think that would mean you'd > > have to keep using the same key for all copies. > > That's true, but that might be acceptable and possibly even desirable in > certain cases. On the other hand, it would certainly be a useful > feature to have a way to migrate from one key to another. Perhaps that > would start out as an off-line tool, but maybe we'd be able to work out > a way to support having it done on-line in the future (certainly > non-trivial, but if we supported multiple keys concurrently with a > preference for which key is used to write data back out, and required > that checksums be in place to allow us to test if decrypting with a > specific key worked ... lots more hand-waving here... ). As I understand it, having encryption in the database means the key is stored in the database, while having encryption in the file system means the key is stored in the operating system somewhere. Of course, if the key stored in the database is visible to someone using the operating system, we really haven't added much/any security --- I guess my point is that the OS easily can hide the key from the database, but the database can't easily hide the key from the operating system. Of course, if the storage is split from the database server then having the key on the database server seems like a win. However, I think a db server could easily encrypt blocks before sending them to the SAN server. This would not work for NAS, of course, since it is file-based. I have to admit we tend to avoid heavy-API solutions that are designed just to work around deployment challenges. Commercial databases are fine in doing that, but it leads to very complex products. I think the larger issue is where to store the key. I would love for us to come up with a unified solution to that and then build encryption on that, including all-cluster encryption. One cool idea I have is using public encryption to store the encryption key by users who don't know the decryption key, e.g. RSA. It would be a write-only encryption option. Not sure how useful that is, but it easily possible, and doesn't require us to keep the _encryption_ key secret, just the decryption one. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Data at rest encryption
On Tue, Jun 13, 2017 at 11:35:03AM -0400, Robert Haas wrote: > I anticipate that one of the trickier problems here will be handling > encryption of the write-ahead log. Suppose you encrypt WAL a block at > a time. In the current system, once you've written and flushed a > block, you can consider it durably committed, but if that block is > encrypted, this is no longer true. A crash might tear the block, > making it impossible to decrypt. Replay will therefore stop at the > end of the previous block, not at the last record actually flushed as > would happen today. So, your synchronous_commit suddenly isn't. A > similar problem will occur any other page where we choose not to > protect against torn pages using full page writes. For instance, > unless checksums are enabled or wal_log_hints=on, we'll write a data > page where a single bit has been flipped and assume that the bit will > either make it to disk or not; the page can't really be torn in any > way that hurts us. But with encryption that's no longer true, because > the hint bit will turn into much more than a single bit flip, and > rereading that page with half old and half new contents will be the > end of the world (TM). I don't know off-hand whether we're > protecting, say, CLOG page writes with FPWs.: because setting a couple > of bits is idempotent and doesn't depend on the existing page > contents, we might not need it currently, but with encryption, every > bit in the page depends on every other bit in the page, so we > certainly would. I don't know how many places we've got assumptions > like this baked into the system, but I'm guessing there are a bunch. That is not necessary true. You are describing a cipher mode where the user data goes through the cipher, e.g. AES in CBC mode. However, if you are using a stream cipher based on a block cipher, e.g. CTR, GCM, you XOR the user data with a random bit stream, and in that case one bit change in user data would be one bit change in the cipher output. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: pgindent (was Re: [HACKERS] [COMMITTERS] pgsql: Preventive maintenance in advance of pgindent run.)
On Sun, Jun 11, 2017 at 09:14:36PM +, Piotr Stefaniak wrote: > I've never been too excited to improve indent and it's increasingly > challenging for me to force myself to work on it now, after I've > invested so much of my spare time into it. So please bear with me if > there are any errors. Understood. You would think that with the number of open-source programs written in C that there would be more interest in C formatting tools. Is the Postgres community the only ones with specific requirements, or is it just that we settled on an older tool and can't easily change? I have reviewed the C formatting options a few times over the years and every time the other options were worse than what we had. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PG10 transition tables, wCTEs and multiple operations on the same table
On Thu, Jun 8, 2017 at 11:05:43AM -0700, Peter Geoghegan wrote: > On Thu, Jun 8, 2017 at 10:59 AM, Robert Haas <robertmh...@gmail.com> wrote: > > More generally, I don't think there's ever a > > time when it's OK to commit a patch that you're not willing to put at > > least some effort into fixing up afterwards. > > Kevin said "It has become clear that the scope of problems being found > now exceed what I can be sure of being able to fix in time to make for > a stable release, in spite of the heroic efforts Thomas has been > putting in". I think it's clear that Kevin is willing to put in some > work. The issue is that he is unable to *guarantee* that he'll be able > to put in *sufficient* time, and in light of that concedes that it > might be best to revert and revisit for Postgres 11. He is being > cautious, and does not want to *risk* unduly holding up the release. > > That was my understanding, at least. I think we can all agree that Kevin should have communicated this earlier, rather than requiring Robert to push him on the issue. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PG 10 release notes
On Wed, Jun 7, 2017 at 03:18:49PM +1000, Neha Khatri wrote: > > On Mon, May 15, 2017 at 12:45 PM, Bruce Momjian <br...@momjian.us> wrote: > > On Thu, May 11, 2017 at 11:50:03PM -0400, Tom Lane wrote: > > Michael Paquier <michael.paqu...@gmail.com> writes: > > > Bruce, the release notes do not mention yet that support for cleartext > > > passwords is removed. This has been done recently by Heikki in > > > eb61136d. This has as consequence that CREATE ROLE PASSWORD > > > UNENCRYPTED returns an error, and that password_encryption loses its > > > value 'off' compared to last releases. > > > > The release notes only say that they're current through 4-22. The > > usual process is to update them in batches every so often. It'd be > > great if Bruce has time to do another round before Monday, but the > > current situation is not really broken. > > Done. Applied patch attached. > > > > The patch introduced one release note item twice in > https://www.postgresql.org/ > docs/10/static/release-10.html : > > > • Rename WAL-related functions and views to use lsn instead of location > (David Rowley) > > • RenameWAL-related functions and views to use lsn instead of location > (David > Rowley) > > Perhaps just one entry is good. Yes, this has been fixed already, see: https://www.postgresql.org/docs/devel/static/release-10.html -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extra Vietnamese unaccent rules
On Tue, Jun 6, 2017 at 12:15:13PM -0400, Tom Lane wrote: > Bruce Momjian <br...@momjian.us> writes: > > There seems to be a problem. I can't see a patch dated 2017-06-07 on > > the commitfest page: > > https://commitfest.postgresql.org/14/1161/ > > It looks to me like the patch is buried inside a multipart/alternative > MIME section. That's evidently causing our mail archives to miss its > presence. The latest message does show as having an attachment in the > archives, but I think there's some delay before the CF app will notice. OK, I see had picked up my email as the lastest, not the latest patch. I see now the second patch email appears properly on the webpage, so we are good: https://commitfest.postgresql.org/14/1161/ -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extra Vietnamese unaccent rules
On Wed, Jun 7, 2017 at 01:06:22AM +0900, Man Trieu wrote: > 2017-06-07 0:31 GMT+09:00 Bruce Momjian <br...@momjian.us>: > I added the thread but there was no change. (I think the thread was > already present.) It appears it is not seeing this patch as the latest > patch. > > Does anyone know why this is happening? > > > > May be due to my Mac's mailer? Sorry but I try one more time to attach the > patch by webmail. It is getting weirder. It has picked up my email report of a commitfest problem as the latest patch (even though there was no patch), and your second posting is not listed: https://commitfest.postgresql.org/14/1161/ I think we need someone who knows the rules of how the commitfest finds patches. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extra Vietnamese unaccent rules
On Wed, Jun 7, 2017 at 12:10:25AM +0900, Dang Minh Huong wrote: > > On Jun 4, 29 Heisei, at 00:48, Bruce Momjian <br...@momjian.us> wrote: > >>>> Shouldn't you use "or is_letter_with_marks()", instead of "or len(...) > >>>>> 1"? Your test might catch something that isn't based on a 'letter' > >>>> (according to is_plain_letter). Otherwise this looks pretty good to > >>>> me. Please add it to the next commitfest. > >>> > >>> Thanks for confirm, sir. > >>> I will add it to the next CF soon. > >> > >> Sorry for lately response. I attach the update patch. > > > > Uh, there is no patch attached. > > > > Sorry sir, reattach the patch. > I also added it to the next CF and set reviewers to Thomas Munro. Could you > confirm for me. There seems to be a problem. I can't see a patch dated 2017-06-07 on the commitfest page: https://commitfest.postgresql.org/14/1161/ I added the thread but there was no change. (I think the thread was already present.) It appears it is not seeing this patch as the latest patch. Does anyone know why this is happening? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Challenges preventing us moving to 64 bit transaction id (XID)?
On Tue, Jun 6, 2017 at 09:05:03AM -0400, Peter Eisentraut wrote: > On 6/6/17 08:29, Bruce Momjian wrote: > > On Tue, Jun 6, 2017 at 06:00:54PM +0800, Craig Ringer wrote: > >> Tom's point is, I think, that we'll want to stay pg_upgrade > >> compatible. So when we see a pg10 tuple and want to add a new page > >> with a new page header that has an epoch, but the whole page is full > >> so there isn't 32 bits left to move tuples "down" the page, what do we > >> do? > > > > I guess I am missing something. If you see an old page version number, > > you know none of the tuples are from running transactions so you can > > just freeze them all, after consulting the pg_clog. What am I missing? > > If the page is full, why are you trying to add to the page? > > The problem is if you want to delete from such a page. Then you need to > update the tuple's xmax and stick the new xid epoch somewhere. > > We had an unconference session at PGCon about this. These issues were > all discussed and some ideas were thrown around. We can expect a patch > to appear soon, I think. Sorry I missed the unconference session. OK, crazy idea. Since we know the creation is frozen can we put the epoch in the xmin and set some tuple bit that only has meaning on old page versions? Yeah, I said crazy. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces
On Tue, Jun 6, 2017 at 04:39:50AM -0300, Alvaro Herrera wrote: > Andres Freund wrote: > > > FWIW, allowing UNLOGGED tables, rather than just TEMPORARY ones, > > increases the complexity of that project noticeably. For TEMPORARY you > > basically don't need to do much but to recreate the structure inside the > > tablespace at start - fairly simple. But for UNLOGGED you need to find > > a way to recreate the relevant file and init forks - otherwise we might > > not notice what needs to be reset at a crash restart, and we might error > > out when executing selects etc. and then the table's not there. > > Presumably recreating files & init forks that at first table access is > > doable, but it's not entirely trivial to do locking wise. > > I was thinking that you could create the init fork for each unlogged > table in a permanent tablespace (probably the default one for the > database). > > FWIW I don't think calling these tablespaces "temporary" is the right > word. It's not the tablespaces that are temporary. Maybe "evanescent". I was thinking "transient". Amazon uses "ephemeral". -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Challenges preventing us moving to 64 bit transaction id (XID)?
On Tue, Jun 6, 2017 at 06:00:54PM +0800, Craig Ringer wrote: > On 6 June 2017 at 12:38, Ashutosh Bapat <ashutosh.ba...@enterprisedb.com> > wrote: > > On Tue, Jun 6, 2017 at 10:00 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > >> In my mind the harder problem is where to find another 32 bits for the > >> new page header field. You could convert the header format on-the-fly > >> if there's free space in the page, but what if there isn't? > > > > I guess, we will have to reserve 32 bits in the header. That's much > > better than increasing tuple header by 32 bits. > > Tom's point is, I think, that we'll want to stay pg_upgrade > compatible. So when we see a pg10 tuple and want to add a new page > with a new page header that has an epoch, but the whole page is full > so there isn't 32 bits left to move tuples "down" the page, what do we > do? I guess I am missing something. If you see an old page version number, you know none of the tuples are from running transactions so you can just freeze them all, after consulting the pg_clog. What am I missing? If the page is full, why are you trying to add to the page? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Use of non-restart-safe storage by temp_tablespaces
On Mon, Jun 5, 2017 at 07:38:43PM -0700, Andres Freund wrote: > On 2017-06-05 22:34:17 -0400, Bruce Momjian wrote: > > On Mon, Jun 5, 2017 at 04:38:32PM -0500, Jerry Sievers wrote: > > > The SAN snaps capture the entire pgdata and WAL pg_xlog area but there > > > is no attempt to copy the NVME device when the snaps are made. > > > > > > There's an event trigger plus batch job now running tou avoid this risk. > > > > > > We realize too that there are implications here if a backup is > > > instantiated and PITR is done. > > > > > > Just FYI that there could be others running like this ignorant of the > > > potential gotchas. > > > > Yes, if we implement the TODO you will create a TEMPORARY tablespace > > that can't contain non-temporary and/or non-unlogged tables. > > FWIW, allowing UNLOGGED tables, rather than just TEMPORARY ones, > increases the complexity of that project noticeably. For TEMPORARY you > basically don't need to do much but to recreate the structure inside the > tablespace at start - fairly simple. But for UNLOGGED you need to find > a way to recreate the relevant file and init forks - otherwise we might > not notice what needs to be reset at a crash restart, and we might error > out when executing selects etc. and then the table's not there. > Presumably recreating files & init forks that at first table access is > doable, but it's not entirely trivial to do locking wise. Agreed, that is why they are separate adjacent items on the TODO list. :-) -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
