Thread: initdb when data/ folder has mount points

Hi,

v9.6.6

Apparently, initdb assumes that data/ is one big mount point.  However, we have four mount points:
/var/lib/pgsql/9.6/data/backup
/var/lib/pgsql/9.6/data/base
/var/lib/pgsql/9.6/data/pg_log
/var/lib/pgsql/9.6/data/pg_xlog

They are all empty.  How do I convince it to ignore the fact that the directories exist?

Thanks

On Wed, 21 Feb 2018, Ron Johnson wrote:

> Apparently, initdb assumes that data/ is one big mount point. However, we
> have four mount points:
> /var/lib/pgsql/9.6/data/backup
> /var/lib/pgsql/9.6/data/base
> /var/lib/pgsql/9.6/data/pg_log
> /var/lib/pgsql/9.6/data/pg_xlog

Ron,

   What command do you use? Here, on Slackware-14.2, /var/lib/psql/10.2/data/
contains:

PG_VERSION    pg_ident.conf  pg_snapshots  pg_wal
base          pg_logical     pg_stat       pg_xact
global          pg_multixact   pg_stat_tmp   postgresql.auto.conf
pg_commit_ts  pg_notify      pg_subtrans   postgresql.conf
pg_dynshmem   pg_replslot    pg_tblspc       postmaster.opts
pg_hba.conf   pg_serial      pg_twophase   postmaster.pid

   The 9.6 version was the same.

   The command I use (as user postgres) is: 
initdb -D /var/lib/pgsql/10.2/data &

HTH,

Rich

Ron Johnson <ron.l.johnson@cox.net> writes:
> Apparently, initdb assumes that data/ is one big mount point. However, we 
> have four mount points:
> /var/lib/pgsql/9.6/data/backup
> /var/lib/pgsql/9.6/data/base
> /var/lib/pgsql/9.6/data/pg_log
> /var/lib/pgsql/9.6/data/pg_xlog

Don't do that.

There's no reason for backup storage to be under the data directory (and
lots of good reasons for it not to be).  Just put it somewhere else.

The supported way to put pg_xlog on a separate volume is to put that
mount point somewhere else, and make $PGDATA/pg_xlog be a symlink to
it.  IIRC, there's an initdb option to help with that, though you can
also make it so manually after initdb.

For pg_log, just put it somewhere else and set the appropriate
configuration option to say where to write the postmaster log files.
Or you could use a symlink, like the solution for pg_xlog, but
I don't see any advantage there.

I don't see any point in making base/ be its own mount point.  Once
you get rid of those other subdirectories there's not going to be
enough "global" storage left to justify its own volume.

            regards, tom lane

Ron Johnson <ron.l.johnson@cox.net> writes:

Apparently, initdb assumes that data/ is one big mount point. However, we 
have four mount points:
/var/lib/pgsql/9.6/data/backup
/var/lib/pgsql/9.6/data/base
/var/lib/pgsql/9.6/data/pg_log
/var/lib/pgsql/9.6/data/pg_xlog

Don't do that.

There's no reason for backup storage to be under the data directory (and
lots of good reasons for it not to be).  Just put it somewhere else.

The supported way to put pg_xlog on a separate volume is to put that
mount point somewhere else, and make $PGDATA/pg_xlog be a symlink to
it.  IIRC, there's an initdb option to help with that, though you can
also make it so manually after initdb.

For pg_log, just put it somewhere else and set the appropriate
configuration option to say where to write the postmaster log files.
Or you could use a symlink, like the solution for pg_xlog, but
I don't see any advantage there.

I don't see any point in making base/ be its own mount point.  Once
you get rid of those other subdirectories there's not going to be
enough "global" storage left to justify its own volume.

I'm replicating the structure in our existing systems.  Is there an (up to date) Best Practices document for these kinds of issues?

Ron Johnson <ron.l.johnson@cox.net> writes:
> On 02/21/2018 06:01 PM, Tom Lane wrote:
>> Ron Johnson <ron.l.johnson@cox.net> writes:
>>> Apparently, initdb assumes that data/ is one big mount point. However, we
>>> have four mount points:
>>> /var/lib/pgsql/9.6/data/backup
>>> /var/lib/pgsql/9.6/data/base
>>> /var/lib/pgsql/9.6/data/pg_log
>>> /var/lib/pgsql/9.6/data/pg_xlog

>> Don't do that.

> I'm replicating the structure in our existing systems.  Is there an (up to 
> date) Best Practices document for these kinds of issues?

Well, initdb has never allowed a non-empty target (at least not in this
century).  So your existing setup must have been achieved by manual
hacking post-initdb, which you could certainly do again if you're dead
set on that layout.  I remain of the opinion that it's not a good
design, though.

            regards, tom lane

On 2/21/18 7:01 PM, Tom Lane wrote:
> Ron Johnson <ron.l.johnson@cox.net> writes:
>> Apparently, initdb assumes that data/ is one big mount point. However, we
>> have four mount points:
>> /var/lib/pgsql/9.6/data/backup
>> /var/lib/pgsql/9.6/data/base
>> /var/lib/pgsql/9.6/data/pg_log
>> /var/lib/pgsql/9.6/data/pg_xlog
> 
> Don't do that.

Agreed.

> There's no reason for backup storage to be under the data directory (and
> lots of good reasons for it not to be).  Just put it somewhere else.

Yes -- in this configuration your backups would be backed up with every 
backup.  It's pretty obvious where that would go.

> The supported way to put pg_xlog on a separate volume is to put that
> mount point somewhere else, and make $PGDATA/pg_xlog be a symlink to
> it.  IIRC, there's an initdb option to help with that, though you can
> also make it so manually after initdb.

initdb supports linking pg_xlog/pg_wal with the --xlogdir/--waldir option.

> For pg_log, just put it somewhere else and set the appropriate
> configuration option to say where to write the postmaster log files.
> Or you could use a symlink, like the solution for pg_xlog, but
> I don't see any advantage there.

Symlinking pg_log is not ideal because the logs end up in the backup. 
It gets pretty weird when those logs get restored to a standby and 
somebody starts reading them.

> I don't see any point in making base/ be its own mount point.  Once
> you get rid of those other subdirectories there's not going to be
> enough "global" storage left to justify its own volume.

Agreed.

-- 
-David
david@pgmasters.net

On Wed, Feb 21, 2018 at 07:56:38PM -0500, David Steele wrote:
> On 2/21/18 7:01 PM, Tom Lane wrote:
>> For pg_log, just put it somewhere else and set the appropriate
>> configuration option to say where to write the postmaster log files.
>> Or you could use a symlink, like the solution for pg_xlog, but
>> I don't see any advantage there.
>
> Symlinking pg_log is not ideal because the logs end up in the backup. It
> gets pretty weird when those logs get restored to a standby and somebody
> starts reading them.

log_directory in postgresql.conf san be set up with an absolute
directory value.  So there is no actual need for a symlink with pg_log.
This also reduces the amount of data transfered as part of base
backups without actually needing them.
--
Michael

On 2/22/18 1:16 AM, Michael Paquier wrote:
> On Wed, Feb 21, 2018 at 07:56:38PM -0500, David Steele wrote:
>> On 2/21/18 7:01 PM, Tom Lane wrote:
>>> For pg_log, just put it somewhere else and set the appropriate
>>> configuration option to say where to write the postmaster log files.
>>> Or you could use a symlink, like the solution for pg_xlog, but
>>> I don't see any advantage there.
>>
>> Symlinking pg_log is not ideal because the logs end up in the backup. It
>> gets pretty weird when those logs get restored to a standby and somebody
>> starts reading them.
>
> log_directory in postgresql.conf san be set up with an absolute
> directory value.  So there is no actual need for a symlink with pg_log.
> This also reduces the amount of data transfered as part of base
> backups without actually needing them.

Yes, I really should have said having pg_log in the data directory at
all is not ideal, symlinked or no.

--
-David
david@pgmasters.net

On 2/22/18 1:16 AM, Michael Paquier wrote:

On Wed, Feb 21, 2018 at 07:56:38PM -0500, David Steele wrote:

On 2/21/18 7:01 PM, Tom Lane wrote:

For pg_log, just put it somewhere else and set the appropriate
configuration option to say where to write the postmaster log files.
Or you could use a symlink, like the solution for pg_xlog, but
I don't see any advantage there.

Symlinking pg_log is not ideal because the logs end up in the backup. It
gets pretty weird when those logs get restored to a standby and somebody
starts reading them.

log_directory in postgresql.conf san be set up with an absolute
directory value.  So there is no actual need for a symlink with pg_log.
This also reduces the amount of data transfered as part of base
backups without actually needing them. 

Yes, I really should have said having pg_log in the data directory at
all is not ideal, symlinked or no.

Is there a Best Practices wiki?  (https://wiki.postgresql.org/wiki/Detailed_installation_guides is pretty old and thin.)