pgbackrest when data/base is symlinked to another volume

Hi Ron,

On 9/6/18 11:21 PM, Ron wrote:

Will pgbackrest properly backup and restore the cluster if data/base,
data/pg_xlog and data/pg_log are symlinks?

PGDATA=/var/lib/pgsql/9.6/data
$PGDATA/base -> /Database/9.6/base
$PGDATA/pg_log -> /Database/9.6/pg_log
$PGDATA/pg_xlog -> /Database/9.6/pg_xlog

Yes, this will work. Note that restore does not recreate symlinks by
default so you'll need to specify --link-all to enable symlink creation.

See
https://pgbackrest.org/configuration.html#section-restore/option-link-all
for details.

Using symlinks in this way will make management of your clusters more
difficult, mostly because systems need more provisioning before restores
can be performed. In general I'd recommend against it unless there are
performance considerations.

Also, you might consider using log_directory to relocate log files
rather than a symlink. This will exclude log files from your backup
which is usually preferable -- primary logs restored to a standby are
out of context and can cause confusion.

Regards,
--
-David
david@pgmasters.net

On 09/07/2018 05:22 PM, David Steele wrote:

Hi Ron,

On 9/6/18 11:21 PM, Ron wrote:

Will pgbackrest properly backup and restore the cluster if data/base,
data/pg_xlog and data/pg_log are symlinks?

PGDATA=/var/lib/pgsql/9.6/data
$PGDATA/base -> /Database/9.6/base
$PGDATA/pg_log -> /Database/9.6/pg_log
$PGDATA/pg_xlog -> /Database/9.6/pg_xlog

Yes, this will work.  Note that restore does not recreate symlinks by
default so you'll need to specify --link-all to enable symlink creation.

See
https://pgbackrest.org/configuration.html#section-restore/option-link-all
for details.

Using symlinks in this way will make management of your clusters more
difficult, mostly because systems need more provisioning before restores
can be performed.  In general I'd recommend against it unless there are
performance considerations.

Also, you might consider using log_directory to relocate log files rather
than a symlink.  This will exclude log files from your backup which is
usually preferable -- primary logs restored to a standby are out of
context and can cause confusion.

Thanks for the tips.  I'll probably implement that on our new systems.

--
Angular momentum makes the world go 'round.

On 09/07/2018 05:22 PM, David Steele wrote:

Hi Ron,

On 9/6/18 11:21 PM, Ron wrote:

Will pgbackrest properly backup and restore the cluster if data/base,
data/pg_xlog and data/pg_log are symlinks?

PGDATA=/var/lib/pgsql/9.6/data
$PGDATA/base -> /Database/9.6/base
$PGDATA/pg_log -> /Database/9.6/pg_log
$PGDATA/pg_xlog -> /Database/9.6/pg_xlog

Yes, this will work.  Note that restore does not recreate symlinks by
default so you'll need to specify --link-all to enable symlink creation.

See
https://pgbackrest.org/configuration.html#section-restore/option-link-all
for details.

Using symlinks in this way will make management of your clusters more
difficult, mostly because systems need more provisioning before restores
can be performed.  In general I'd recommend against it unless there are
performance considerations.

Now that I'm thinking more about what you wrote... "data" isn't on it's own
partition.  data/*base* has it's own partition.

What's the recommended method for putting *base**/* on a partition different
from data/?  Or is that not recommended?

--
Angular momentum makes the world go 'round.

On 9/7/18 8:47 PM, Ron wrote:

On 09/07/2018 05:22 PM, David Steele wrote:

On 9/6/18 11:21 PM, Ron wrote:

Will pgbackrest properly backup and restore the cluster if data/base,
data/pg_xlog and data/pg_log are symlinks?

PGDATA=/var/lib/pgsql/9.6/data
$PGDATA/base -> /Database/9.6/base
$PGDATA/pg_log -> /Database/9.6/pg_log
$PGDATA/pg_xlog -> /Database/9.6/pg_xlog

Yes, this will work.  Note that restore does not recreate symlinks by
default so you'll need to specify --link-all to enable symlink creation.

See
https://pgbackrest.org/configuration.html#section-restore/option-link-all
for details.

Using symlinks in this way will make management of your clusters more
difficult, mostly because systems need more provisioning before
restores can be performed.  In general I'd recommend against it unless
there are performance considerations.

Now that I'm thinking more about what you wrote... "data" isn't on it's
own partition.  data/*base* has it's own partition.

What's the recommended method for putting *base**/* on a partition
different from data/?  Or is that not recommended?

All the user data goes in base so there's really no need to separate it
out of data. Typically pg_wal and tablespaces are relocated onto
different devices for performance (or to get more space). If the
partitions are on the same device then there's no performance benefit,
just admin hassle.

--
-David
david@pgmasters.net

On 09/08/2018 03:07 PM, David Steele wrote:

On 9/7/18 8:47 PM, Ron wrote:

On 09/07/2018 05:22 PM, David Steele wrote:

On 9/6/18 11:21 PM, Ron wrote:

Will pgbackrest properly backup and restore the cluster if data/base,
data/pg_xlog and data/pg_log are symlinks?

PGDATA=/var/lib/pgsql/9.6/data
$PGDATA/base -> /Database/9.6/base
$PGDATA/pg_log -> /Database/9.6/pg_log
$PGDATA/pg_xlog -> /Database/9.6/pg_xlog

Yes, this will work.  Note that restore does not recreate symlinks by
default so you'll need to specify --link-all to enable symlink creation.

See
https://pgbackrest.org/configuration.html#section-restore/option-link-all
for details.

Using symlinks in this way will make management of your clusters more
difficult, mostly because systems need more provisioning before restores
can be performed.  In general I'd recommend against it unless there are
performance considerations.

Now that I'm thinking more about what you wrote... "data" isn't on it's
own partition.  data/*base* has it's own partition.

What's the recommended method for putting *base**/* on a partition
different from data/?  Or is that not recommended?

All the user data goes in base so there's really no need to separate it
out of data.  Typically pg_wal and tablespaces are relocated onto
different devices for performance (or to get more space).  If the
partitions are on the same device then there's no performance benefit,
just admin hassle.

Googled "postgresql disk partitioning" and "postgresql volume partitioning"
without much success.

Is the best practice volume partitioning:
/Database/9.6/data
/Database/9.6/data/pg_log
/Database/9.6/data/pg_xlog

where /var/lib/pgsql/9.6 (on RHEL6) is a symlink to /Database/9.6/data and
PGDATA=/Database/9.6/data

*or *

/Database/9.6/data/base
/Database/9.6/data/pg_log
/Database/9.6/data/pg_xlog

where PGDATA=/var/lib/pgsql/9.6/data and base, pg_log and px_xlog are
symlinks to the partitions?

Thanks

--
Angular momentum makes the world go 'round.

Hi Ron

On 9/8/18 4:26 PM, Ron wrote:

Googled "postgresql disk partitioning" and "postgresql volume
partitioning" without much success.

Is the best practice volume partitioning:
/Database/9.6/data
/Database/9.6/data/pg_log
/Database/9.6/data/pg_xlog

where /var/lib/pgsql/9.6 (on RHEL6) is a symlink to /Database/9.6/data
and PGDATA=/Database/9.6/data

*or *

/Database/9.6/data/base
/Database/9.6/data/pg_log
/Database/9.6/data/pg_xlog

where PGDATA=/var/lib/pgsql/9.6/data and base, pg_log and px_xlog are
symlinks to the partitions?

This is really a matter of preference, but simpler is better, so the
first one looks better to me.

In general I prefer to keep everything in /var/lib/pgsql/9.6:

/var/lib/pgsql/9.6/data
/var/lib/pgsql/9.6/data/pg_xlog -> /var/lib/pgsql/9.6/wal

Then use the log_directory setting to put logs in:

/var/log/pgsql or /var/lib/pgsql/9.6/log

pgBackRest will expect to be pointed at a real directory (pg-path) and
expect the data_directory in PostgreSQL to match.

Regards,
--
-David
david@pgmasters.net