Re: Moving pg_xlog - Mailing list pgsql-general
| From | Jeff Janes |
|---|---|
| Subject | Re: Moving pg_xlog |
| Date | |
| Msg-id | CAMkU=1xA8bFio5yB+m7NAVXNw16Ly4BXVpooZN1zet61Zi_Mjw@mail.gmail.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 Thu, Dec 1, 2016 at 5:55 AM, Robert Inder <robert@interactive.co.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.
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
pgsql-general by date: