Re: Moving pg_xlog

On Thu, Dec 1, 2016 at 5:55 AM, Robert Inder <robert(at)interactive(dot)co(dot)uk>
wrote:

> I'm running Postgres9.4 in master/hot-standby mode on a few pairs of
> servers.
>
> While recovering from A Bit Of Bother last week, I came across a
> posting saying that pg_xlog should be on a separate partition.
>
> I tried to find out more about this, by consulting the PostgresQL
> documentation (i.e.
> https://www.postgresql.org/docs/9.4/static/index.html )
> But all I could find was a mention that "It is advantageous if the log
> is located on a different disk from the main database files".
>
> The questions:
> 1. WHY is this good? Is it (just) to stop pg_xlog filling the
> database disk/partition?

More like the reverse. Running the data partition out of space is bad.
Running the pg_xlog partition out of space is worse. Running both
partitions out of space at the same time is worse yet, which of course you
will do if they are the same partition and that one partition runs out of
space.

> Or are there performance implications?
> SPECIFICALLY: my database is currently in "/", which is on SSD. Is it
> better to move pg_xlog to another partition on the same SSD? Or to a
> physical disk or SAN?
>

If you have something with fast fsyncs (battery backed write cache, maybe
SSD), but that is not big enough to hold your entire database, then you
would want to put your pg_xlog on that, and the rest of the database on the
rest. (if you are doing OLTP, anyway).

On some kernels and some file systems, having a constant stream of fsyncs
(from pg_xlog) interacts poorly with having ordinary non-immediately-synced
writes (from the regular data files) on the same partition.

> 2. What are the implications for doing a base backup? I believe I
> read that putting pg_xlog on a different partition meant it would be
> omitted from a file-system bulk copy (e.g. rsync),

rsync has lots of options to control what happens with symbolic links and
mount points. Or to exclude certain directories, symbolic links and mount
points not withstanding.

> and this was a GOOD
> thing, because the copy operation would be faster -- not copying
> pg_xlog would not prevent the standby server from starting, because
> the information it needed would be in the WAL files that would be
> shipped separately. Have I got that right?
>
> Finally, the suggestion.
>
> I'd really like to read an explicit discussion of this in the official
> documentation, rather than just glean what I can from answers to
> questions.
>

The official documentation cannot have a dissertation on every combination
of hardware, OS, file-system type, version of that file-system, and your
usage pattern. That is inherently the realm of the wiki or the blogs.

Cheers,

Jeff