Re: Documentation for initdb option --waldir - Mailing list pgsql-docs

On Sat Mar 29, 2025 at 7:26 AM PDT, Robert Treat wrote:
> On Thu, Mar 27, 2025 at 5:57 PM David G. Johnston
> <david.g.johnston@gmail.com> wrote:
>> On Thu, Mar 27, 2025 at 2:28 PM Robert Treat <rob@xzilla.net> wrote:
>>> On Thu, Mar 27, 2025 at 12:06 PM David G. Johnston
>>> <david.g.johnston@gmail.com> wrote:
>>> > I expanded upon the material regarding using different file systems and disks.
>>> >
>>> > I would like to add a similar "why" to the mount point recommendation but don't know what that would be.
Suggestionswelcomed. 
>>> >
>>>
>>> I'm not sure I follow what you are asking for... but a non-performance
>>> reason to use a seperate mount point for pg_wal, even if the
>>> underlying storage is the same, would be for something like using
>>> filesystem snapshots to grab contents of the data directory without
>>> grabbing wal (which can be handled separately).
>>>
>>
>> If I mount the filesystem on disk2 to:  /mnt/disk2
>> Why do I need to create "/mnt/disk2/wal_files/" and point there instead of: "/mnt/disk2/"?
>>
>
> My immediate response to this was "because Postgres won't let you"
> which seemed unhelpful, and that I couldn't remember why was pretty
> unsatisfying, so I dug around in the source which was unhelpful but
> eventually came across this from
> https://www.postgresql.org/docs/devel/creating-cluster.html#CREATING-CLUSTER-MOUNT-POINTS
>
> "Best practice is to create a directory within the mount-point
> directory that is owned by the PostgreSQL user, and then create the
> data directory within that. This avoids permissions problems,..."
>
> Which I do remember having tried to do it directly and the OS
> complaining that my mount point wasn't owned by root and/or Postgres
> complaining that the xlog dir wasn't owned by Postgres, so I think
> this advice probably still holds.

I remembered an old advice related to lost+found being placed on mount
points, but couldn't remember the specific, so searched and found Tom's
advice for data directory. I feel that the advice would apply to WAL
directory, as well.

From https://www.postgresql.org/message-id/12168.1312921709%40sss.pgh.pa.us

A couple of reasons for [not placing data diretory at a mount point]
are:

1. Mount-point directories should be owned by root, never by an
unprivileged account such as postgres.  IIRC there are good security
reasons for this practice, though I don't recall all the details right
now.

2. Keeping the data directory one level down ensures a clean failure if
the disk is for some reason not mounted when Postgres starts, or goes
offline later.  Otherwise, particularly if you're using a start script
that will automatically try an initdb, you might end up with some data
files on the / volume underneath where the mount point should have been.
This is sure to lead to serious problems when the disk does come back
online.  There's at least one horror story in our archives from someone
who had an auto-initdb startup script and one day his NFS disk was a few
seconds slow to mount...

Best regards,
Gurjeet
http://Gurje.et