Обсуждение: why not using a mountpoint as PGDATA?
I apparently cannot find an answer in the documentation, however initdb states: initdb: directory "/mnt/data1" exists but is not empty It contains a dot-prefixed/invisible file, perhaps due to it being a mount point. Using a mount point directly as the data directory is not recommended. Create a subdirectory under the mount point. and effectively /mnt/data1 is a mountpoint, therefore but belongs to the user of the cluster, therefore the only difference I can see is having '..' belonging to root at the mountpoint or the cluster user within a subdirectory. What's wrong with using a mountpoint? Thanks, Luca
On Wed, Feb 27, 2019 at 12:22 PM Luca Ferrari <fluca1978@gmail.com> wrote: > > What's wrong with using a mountpoint? You can see most obvious reasons at https://bugzilla.redhat.com/show_bug.cgi?id=1247477
On Wed, Feb 27, 2019 at 12:33 PM Julien Rouhaud <rjuju123@gmail.com> wrote: > You can see most obvious reasons at > https://bugzilla.redhat.com/show_bug.cgi?id=1247477 Thanks, I didn't see the lost+found problem because I'm on UFS (I'm wondering if this applies also to ZFS datasetes with mount point). Same story for the accidentally umount, since FreeBSD seems enough friendly to avoid umount while the database is running, but it could definetely happen with some brute force or on another operating system. I see the upgrade problem being a lot more general in this sense. However, all are good points. Luca
On 2019-02-27 12:33:02 +0100, Julien Rouhaud wrote: > On Wed, Feb 27, 2019 at 12:22 PM Luca Ferrari <fluca1978@gmail.com> wrote: > > > > What's wrong with using a mountpoint? > > You can see most obvious reasons at > https://bugzilla.redhat.com/show_bug.cgi?id=1247477 I see only one good reason there: The fact that pg_upgrade needs write access to the parent directory. Of course that alone might suffice. The other reasons aren't good IMHO. The first one (initdb checks for an empty directory) is more "We disallow it, therefore it is a bad idea" than a reason for disallowing it. The second is just wrong: You can have a non-root owned mount-point on any Unixoid system I've worked with. (And I don't see why that would be a security problem) The third is wrong at least on Debian: All server processes have /var/lib/postgresql/$version/$cluster as their working directory, so it cannot be unmounted while the database is up. Even if you could, the server would either immediately lose access to all files (in which case you could recover) or it would keep access to all files (so, not a problem). Plus being in a subdirectory wouldn't change that. Maybe it's a potential problem with other layouts. hp -- _ | Peter J. Holzer | we build much bigger, better disasters now |_|_) | | because we have much more sophisticated | | | hjp@hjp.at | management tools. __/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
Вложения
Luca Ferrari <fluca1978@gmail.com> writes: > On Wed, Feb 27, 2019 at 12:33 PM Julien Rouhaud <rjuju123@gmail.com> wrote: >> You can see most obvious reasons at >> https://bugzilla.redhat.com/show_bug.cgi?id=1247477 > Thanks, I didn't see the lost+found problem because I'm on UFS (I'm > wondering if this applies also to ZFS datasetes with mount point). > Same story for the accidentally umount, since FreeBSD seems enough > friendly to avoid umount while the database is running, but it could > definetely happen with some brute force or on another operating > system. The case that I can recall most clearly was actually in the other direction: during system bootup, some NFS volume that was being abused this way (mount point == data dir) was slow to mount. Compounding the problem, postgres was being started through some init script that would helpfully run initdb if it saw the specified data directory was empty. So, rather than failing like a properly paranoid DBA would wish, it ran initdb and then started the postmaster. A bit later, the NFS volume came online, replacing the mount-point directory, and now the postmaster had a devil's brew of open files on the local volume containing the mount point and open files on the NFS volume. It didn't take long for that to translate into hopeless catalog corruption. I don't think they ever got their data back. The true value of data-dir-at-least-one-level-down is so that it will fail to be there at all if the file system isn't mounted. You need that, and you do *not* want automated attempts to "repair" that. regards, tom lane
On 2019-02-27 10:42:12 -0500, Tom Lane wrote: > Luca Ferrari <fluca1978@gmail.com> writes: > > On Wed, Feb 27, 2019 at 12:33 PM Julien Rouhaud <rjuju123@gmail.com> wrote: > >> You can see most obvious reasons at > >> https://bugzilla.redhat.com/show_bug.cgi?id=1247477 [...] > The case that I can recall most clearly was actually in the other > direction: during system bootup, some NFS volume that was being abused > this way (mount point == data dir) was slow to mount. Compounding the > problem, postgres was being started through some init script that would > helpfully run initdb if it saw the specified data directory was empty. > So, rather than failing like a properly paranoid DBA would wish, it > ran initdb and then started the postmaster. Ouch. I wonder though why that directory was writable by the postgres user. But maybe the helpful start script chown'ed it to fix the "wrong" permissions. hp -- _ | Peter J. Holzer | we build much bigger, better disasters now |_|_) | | because we have much more sophisticated | | | hjp@hjp.at | management tools. __/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>
Вложения
On 2/27/19 11:49 AM, Peter J. Holzer wrote: > On 2019-02-27 10:42:12 -0500, Tom Lane wrote: >> Luca Ferrari <fluca1978@gmail.com> writes: >> > On Wed, Feb 27, 2019 at 12:33 PM Julien Rouhaud <rjuju123@gmail.com> wrote: >> >> You can see most obvious reasons at >> >> https://bugzilla.redhat.com/show_bug.cgi?id=1247477 > [...] >> The case that I can recall most clearly was actually in the other >> direction: during system bootup, some NFS volume that was being abused >> this way (mount point == data dir) was slow to mount. Compounding the >> problem, postgres was being started through some init script that would >> helpfully run initdb if it saw the specified data directory was empty. >> So, rather than failing like a properly paranoid DBA would wish, it >> ran initdb and then started the postmaster. > > Ouch. > > I wonder though why that directory was writable by the postgres user. > But maybe the helpful start script chown'ed it to fix the "wrong" > permissions. FWIW, if you want to read the whole gory details of that incident, here it is: https://www.postgresql.org/message-id/flat/41D04FA4.7010402%40joeconway.com#dfc38927745e238d49569ffd5b33beba Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development
Вложения
On 2/27/19 12:43 PM, Joe Conway wrote: > On 2/27/19 11:49 AM, Peter J. Holzer wrote: >> On 2019-02-27 10:42:12 -0500, Tom Lane wrote: >>> Luca Ferrari <fluca1978@gmail.com> writes: >>>> On Wed, Feb 27, 2019 at 12:33 PM Julien Rouhaud <rjuju123@gmail.com> wrote: >>>>> You can see most obvious reasons at >>>>> https://bugzilla.redhat.com/show_bug.cgi?id=1247477 >> [...] >>> The case that I can recall most clearly was actually in the other >>> direction: during system bootup, some NFS volume that was being abused >>> this way (mount point == data dir) was slow to mount. Compounding the >>> problem, postgres was being started through some init script that would >>> helpfully run initdb if it saw the specified data directory was empty. >>> So, rather than failing like a properly paranoid DBA would wish, it >>> ran initdb and then started the postmaster. >> Ouch. >> >> I wonder though why that directory was writable by the postgres user. >> But maybe the helpful start script chown'ed it to fix the "wrong" >> permissions. > FWIW, if you want to read the whole gory details of that incident, here > it is: > > https://www.postgresql.org/message-id/flat/41D04FA4.7010402%40joeconway.com#dfc38927745e238d49569ffd5b33beba What in the world was that SuSE maintainer -- and the people who should have been looking over his shoulder -- thinking??? -- Angular momentum makes the world go 'round.
Ron <ronljohnsonjr@gmail.com> writes: > On 2/27/19 12:43 PM, Joe Conway wrote: >> FWIW, if you want to read the whole gory details of that incident, here >> it is: >> https://www.postgresql.org/message-id/flat/41D04FA4.7010402%40joeconway.com#dfc38927745e238d49569ffd5b33beba > What in the world was that SuSE maintainer -- and the people who should have > been looking over his shoulder -- thinking??? Well, I wouldn't blame them *that* much. Up till that time, I don't think anyone fully understood the hazards of auto-initdb in a startup script (certainly none of the PG developers did). But this has been in our folklore long enough now that anybody who's still doing that definitely should get yelled at ... regards, tom lane
Peter J. Holzer wrote: > On 2019-02-27 12:33:02 +0100, Julien Rouhaud wrote: > > On Wed, Feb 27, 2019 at 12:22 PM Luca Ferrari <fluca1978@gmail.com> wrote: > > > > > > What's wrong with using a mountpoint? > > > > You can see most obvious reasons at > > https://bugzilla.redhat.com/show_bug.cgi?id=1247477 > > I see only one good reason there: The fact that pg_upgrade needs write > access to the parent directory. Of course that alone might suffice. > > The other reasons aren't good IMHO. > > The first one (initdb checks for an empty directory) is more "We > disallow it, therefore it is a bad idea" than a reason for disallowing > it. > > The second is just wrong: You can have a non-root owned mount-point on > any Unixoid system I've worked with. (And I don't see why that would be > a security problem) > > The third is wrong at least on Debian: All server processes have > /var/lib/postgresql/$version/$cluster as their working directory, so it > cannot be unmounted while the database is up. Even if you could, the > server would either immediately lose access to all files (in which case > you could recover) or it would keep access to all files (so, not a > problem). Plus being in a subdirectory wouldn't change that. Maybe it's > a potential problem with other layouts. > > hp > > -- > _ | Peter J. Holzer | we build much bigger, better disasters now > |_|_) | | because we have much more sophisticated > | | | hjp@hjp.at | management tools. > __/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/> I didn't know you weren't supposed to do that. I've been using ecryptfs mountpoints for my data directories for ages without problem. Ecryptfs doesn't create lost+found and the underlying directory is owned by the postgres user and so the mountpoint is as well (even though it is mounted by root). However, the parent directory is root-owned so I guess pg_upgrade wouldn't work. But I've never used pg_upgrade so I've never encountered that problem. Also, if it's not mounted, the underlying directory isn't empty but postgresql can tell something's not right and it won't start if told to do so prematurely. But the next time I upgrade, I might put the data directory in a sub-directory of the mountpoint in case I ever want to start using pg_upgrade. It would also mean I only need a single ecryptfs mountpoint. At the moment, I create a new one for each major upgrade which seems silly. cheers, raf