Re: Moving pg_xlog - Mailing list pgsql-general
From | Achilleas Mantzios |
---|---|
Subject | Re: Moving pg_xlog |
Date | |
Msg-id | 58404663.9090907@matrix.gatewaynet.com Whole thread Raw |
In response to | Moving pg_xlog (Robert Inder <robert@interactive.co.uk>) |
Responses |
Re: Moving pg_xlog
|
List | pgsql-general |
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
pgsql-general by date: