Re: [GENERAL] Moving pg_xlog

[Robert Inder]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Moving pg_xlog

[Jeff Janes]

On Thu, Dec 1, 2016 at 6:17 PM, Michael Paquier 
wrote:

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

This is only the case if you have a write cache, or are doing bulk loads.
With small transactions and without a write cache, the need for constant
syncs totally destroys the benefits of sequential writes.

Cheers,

Jeff

Re: [GENERAL] Moving pg_xlog

[Steven Winfield]

2016-12-02 17:10 GMT+13:00 Michael Paquier 
>:
On Fri, Dec 2, 2016 at 1:04 PM, Melvin Davidson 
> 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

It’s also worth mentioning that the xlog directory can be specified at cluster 
creation time using ‘initdb -X ’, which (AFAICT) just creates the 
symlink for you.

Steve.


Dr. Steven Winfield
Scientist
D: +44 (0)1223 755 776

[Cantab email sig]

Re: [GENERAL] Moving pg_xlog

[Lucas Possamai]

2016-12-02 17:10 GMT+13:00 Michael Paquier :

> On Fri, Dec 2, 2016 at 1:04 PM, Melvin Davidson 
> 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
>

I've done this on my Postgres 9.2 DB server running CentOS 6.7...

And it's pretty much what the guys told you already:


>  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

In my case, it significantly improved I/O performance.

Lucas

Re: [GENERAL] Moving pg_xlog

[Michael Paquier]

On Fri, Dec 2, 2016 at 1:04 PM, Melvin Davidson  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Moving pg_xlog

[Melvin Davidson]

On Thu, Dec 1, 2016 at 10:17 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, Dec 1, 2016 at 7:59 PM, Jeff Janes  wrote:
>
>> On Thu, Dec 1, 2016 at 5:55 AM, Robert Inder 
>> wrote:
>>
>>>
>>> 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.
>>
>>
> ​The documentation has enough information at this level of detail that I
> wouldn't object to adding commentary addressing the above should someone
> take the time to write it.​
>
> Given that the location of pg_xlog is not "configurable" placing such
> commentary in Server Configuration would be a no-go, however.  At a quick
> glance a new section under "Server Setup and Operation - Creating a
> Database Cluster" would probably be a better home.  It already discusses
> Secondary File Systems and in many ways this is just an extension of that
> discussion.
>
> David J.
>
>
>Given that the location of pg_xlog is not "configurable"

 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

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Moving pg_xlog

[David G. Johnston]

On Thu, Dec 1, 2016 at 7:59 PM, Jeff Janes  wrote:

> On Thu, Dec 1, 2016 at 5:55 AM, Robert Inder 
> wrote:
>
>>
>> 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.
>
>
​The documentation has enough information at this level of detail that I
wouldn't object to adding commentary addressing the above should someone
take the time to write it.​

Given that the location of pg_xlog is not "configurable" placing such
commentary in Server Configuration would be a no-go, however.  At a quick
glance a new section under "Server Setup and Operation - Creating a
Database Cluster" would probably be a better home.  It already discusses
Secondary File Systems and in many ways this is just an extension of that
discussion.

David J.

Re: [GENERAL] Moving pg_xlog

[Jeff Janes]

On Thu, Dec 1, 2016 at 5:55 AM, 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?


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.



> 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

Re: [GENERAL] Moving pg_xlog

[Michael Paquier]

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


signature.asc
Description: PGP signature

Re: [GENERAL] Moving pg_xlog

[Achilleas Mantzios]

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.




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 to be 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-fashioned way 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 archiving or 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 documentation than pgsql.


Robert.




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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Moving pg_xlog

[Robert Inder]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Moving pg_xlog to another drive

[Ben]

I'm trying to move my WAL to another drive, but am having difficulties 
with this seemingly simple process. Every time I start up with pg_xlog 
symlinked to my other drive, I get this:


FATAL:  could not open file pg_xlog/0001.history: Permission denied

If I move pg_xlog back into its normal place then things work fine. The 
postgres user can certainly create files when using the symlink, so I 
really don't think it's a permission issue... but I'm at a loss as to what 
else it might be.


This is on CentOS 5 with the latest RPMs. SELinux is not enforcing (nor 
logging any events when I do this stuff).


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Moving pg_xlog to another drive

[Joshua D. Drake]

Ben wrote:
I'm trying to move my WAL to another drive, but am having difficulties 
with this seemingly simple process. Every time I start up with pg_xlog 
symlinked to my other drive, I get this:


FATAL:  could not open file pg_xlog/0001.history: Permission denied

If I move pg_xlog back into its normal place then things work fine. The 
postgres user can certainly create files when using the symlink, so I 
really don't think it's a permission issue... but I'm at a loss as to 
what else it might be.


Is the parent directory of the place you are moving pg_xlog to a place 
that the postgres user can enter?




This is on CentOS 5 with the latest RPMs. SELinux is not enforcing (nor 
logging any events when I do this stuff).


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq




--

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Moving pg_xlog to another drive

[Alvaro Herrera]

Ben wrote:
 I'm trying to move my WAL to another drive, but am having difficulties 
 with this seemingly simple process. Every time I start up with pg_xlog 
 symlinked to my other drive, I get this:
 
 FATAL:  could not open file pg_xlog/0001.history: Permission denied
 
 If I move pg_xlog back into its normal place then things work fine. The 
 postgres user can certainly create files when using the symlink, so I 
 really don't think it's a permission issue... but I'm at a loss as to what 
 else it might be.

Maybe the postgres user does not have all permissions in dirs leading to
the new directory?  You must give it at least x permissions on all
levels up to the parent.

-- 
Alvaro Herrera http://www.flickr.com/photos/alvherre/
Criptografía: Poderosa técnica algorítmica de codificación que es
empleada en la creación de manuales de computadores.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Moving pg_xlog to another drive

[Ben]

On Mon, 4 Jun 2007, Joshua D. Drake wrote:


Ben wrote:
I'm trying to move my WAL to another drive, but am having difficulties with 
this seemingly simple process. Every time I start up with pg_xlog symlinked 
to my other drive, I get this:


FATAL:  could not open file pg_xlog/0001.history: Permission denied

If I move pg_xlog back into its normal place then things work fine. The 
postgres user can certainly create files when using the symlink, so I 
really don't think it's a permission issue... but I'm at a loss as to what 
else it might be.


Is the parent directory of the place you are moving pg_xlog to a place that 
the postgres user can enter?


Sorry, yes, I neglected to mention that the postgres user can enter every 
directory along the path to the new pg_xlog directory. In addition, 
pg_xlog remains owned by postgres.postgres, as does its parent directory, 
and the new pg_xlog directory has permissions of 0700.


So I really can't see why it would be a permission issue.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Moving pg_xlog to another drive

[Steve Atkins]

On Jun 4, 2007, at 11:15 AM, Ben wrote:


On Mon, 4 Jun 2007, Joshua D. Drake wrote:


Ben wrote:
I'm trying to move my WAL to another drive, but am having  
difficulties with this seemingly simple process. Every time I  
start up with pg_xlog symlinked to my other drive, I get this:
FATAL:  could not open file pg_xlog/0001.history:  
Permission denied
If I move pg_xlog back into its normal place then things work  
fine. The postgres user can certainly create files when using the  
symlink, so I really don't think it's a permission issue... but  
I'm at a loss as to what else it might be.


Is the parent directory of the place you are moving pg_xlog to a  
place that the postgres user can enter?


Sorry, yes, I neglected to mention that the postgres user can enter  
every directory along the path to the new pg_xlog directory. In  
addition, pg_xlog remains owned by postgres.postgres, as does its  
parent directory, and the new pg_xlog directory has permissions of  
0700.


So I really can't see why it would be a permission issue.


Are you running SELinux? It's main goal in life is to break disk  
access by denying permission to files anywhere other than where it  
thinks an application should be allowed to access.


Cheers,
  Steve


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Moving pg_xlog to another drive

[Ben]

On Mon, 4 Jun 2007, Steve Atkins wrote:

Are you running SELinux? It's main goal in life is to break disk access by 
denying permission to files anywhere other than where it thinks an 
application should be allowed to access.


Bleh. I am, but I *thought* it was not enforcing. Seems I was wrong. 
Thanks for pointing out the one area I hadn't double-checked. :)


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/