Обсуждение: Moving pg_xlog

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

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

On Thu, Dec 01, 2016 at 05:48:51PM +0200, Achilleas Mantzios wrote:
> 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.

For performance, on-disk write pattern of data in pg_xlog is sequential
writes, while there will be likely random writes on the main data folder.
On top of that, moving them to a different partition gives more flexibility
in the way to tune checkpoint-related parameters using the partition space
as a constraint for retention policy and checkpoint timings.
--
Michael

On Fri, Dec 2, 2016 at 1:04 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
>  Well, while the location of pg_xlog is not currently configurable, on Linux system the way to do it is  to:
>  1. stop PostgreSQL
>  2. move the pg_xlog directory to a separate partition
>  3. create a symbolic link to point to the new partition
>  4. restart PostgreSQL

Similar flow on Windows, just use a junction point for the link.
--
Michael

Thanks, everyone, for your comments.

I think I've got a clearer idea of what's going on now...

Robert.


On 1 December 2016 at 13:55, 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?  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



--
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