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
toa 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'tgive 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
tobe 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-fashionedway 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
archivingor 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
documentationthan pgsql. 

> Robert.
>


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