Re: Moving pg_xlog

On 01/12/2016 15:55, Robert Inder 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? 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?

Performance is the reason. You would benefit from moving pg_xlog to a different controller with its own write cache or to a different SSD with a write cache which is capacitor-backed. So in
enterprise/server-class setups the above would boost the performance. Using the same SSD with a different partition won't give you much.

>
> 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), 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?
Rsync does cross fs boundaries unless you give it the -x option. It is true that the files in pg_xlog won't be useful to be taken in the backup. However the wal files to be shipped separately is not
smth done by itself, you need to enable/implement WAL archiving. What you describe seems to be the "legacy" old-fashioned way circa 9.0. pg_basebackup (9.1) is more convenient, can create complete
standalone copies (without the need of any additional wals), can use wal streaming so that you don't depend on wal archiving or wal_keep_segment, supports repl slots, can create a ready to go hot
standby, etc.

> 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 possibility of moving pg_xlog to another disk is mentioned in the
> documentation, but I almost missed it because it is in "the wrong
> place". It is in Section 29.5 -- "Reliability and the Write Ahead
> Log" / "WAL Internals". But I wasn't interested in anything INTERNAL:
> I wanted to know where I should try to locate it/them. So I'd looked
> in "the obvious places" -- Section 18 (Server configuration), and in
> particular 18.2 "File Locations". Could I suggest that the motivation
> for doing this, and the consequences for backups, should be discussed
> in "the right place" -- in or near the section that talks about file
> locations in the context of server configuration.

All I can tell you is I haven't found one single piece of free (or not so free) software with more complete documentation than pgsql.

> Robert.
>

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt