From: | Robert Inder <robert(at)interactive(dot)co(dot)uk> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Moving pg_xlog |
Date: | 2016-12-01 13:55:39 |
Message-ID: | CAKqjJm-3JOd95WLSH=x-+voQXN=nMX5g61Rk5ZDrwzGfgWV5Mg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
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?
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.
Robert.
--
Robert Inder, 0131 229 1052 / 07808 492 213
Interactive Information Ltd, 3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
Interactions speak louder than words
From | Date | Subject | |
---|---|---|---|
Next Message | Melvin Davidson | 2016-12-01 14:11:03 | Re: Overwrite pg_catalog? |
Previous Message | dharani kumar | 2016-12-01 13:51:13 | issue with host name lookup in PQconnectdb |