Replication question

I thought I read somewhere that in 9.6, as long as the WAL log is
available on disk or in the archive the replication server will provide
that to the replication client, and my archive NFS mount didn’t have to be
available to all replication clients.

Streaming replication will only read from the WAL files in the
$PGDATA/pg_xlog directory. It will not read from archives. So, yes, you
would need your NFS mount on the replica (or otherwise copy the archive
files to the replica).

Don.

--
Don Seiler
www.seiler.us

Dang, I thought that sounded too good to be true. Oh well.

Thanks for setting me straight.

Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 7349151444 | Email: Scot.Kreienkamp@la-z-boy.com
From: Don Seiler [mailto:don@seiler.us]
Sent: Monday, October 22, 2018 9:58 AM
To: Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com>
Cc: Postgres General <pgsql-general@postgresql.org>
Subject: Re: Replication question

I thought I read somewhere that in 9.6, as long as the WAL log is available on disk or in the archive the replication server will provide that to the replication client, and my archive NFS mount didn’t have to be available to all replication clients.

Streaming replication will only read from the WAL files in the $PGDATA/pg_xlog directory. It will not read from archives. So, yes, you would need your NFS mount on the replica (or otherwise copy the archive files to the replica).

Don.

--
Don Seiler
www.seiler.us<http://www.seiler.us&gt;

This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidential information which is exempt from disclosure under applicable laws. If you are not the intended recipient, you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information. If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.

Am 22.10.2018 um 15:53 schrieb Scot Kreienkamp:

I thought I read somewhere that in 9.6, as long as the WAL log is
available on disk or in the archive the replication server will
provide that to the replication client, and my archive NFS mount
didn�t have to be available to all replication clients.� It doesn�t
seem to be operating that way though.� Did I completely remember that
wrong or did I misunderstand something?

no, but you can define a "restore_command" within your recovery_conf.

https://www.postgresql.org/docs/current/static/continuous-archiving.html

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

On Mon, Oct 22, 2018, 9:54 AM Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com>
wrote:

Hi everyone,

We just moved to PG9.6 from 9.1 (yeah, not my choice to wait this long).
In 9.1 I had to make the archive location (NFS in my case) available to all
the mirrors running PG so that they could catch up whenever they fell
behind. I thought I read somewhere that in 9.6, as long as the WAL log is
available on disk or in the archive the replication server will provide
that to the replication client, and my archive NFS mount didn’t have to be
available to all replication clients. It doesn’t seem to be operating that
way though. Did I completely remember that wrong or did I misunderstand
something?

The master won't read from the archives for you in order to send to an
replica. But using replication slots, you can keep the needed log files
right in pg_xlog/pg_wal until all replicas get what they need (assuming the
disk is large enough). Then you don't need an archive at all for
replication purposes, still might for pitr purposes.

Perhaps this is what you heard about.

Cheers,

Jeff

I remember thinking it was pulling from archive with the restore command if necessary to augment what it had on disk. If that was the case I wanted to configure it. I don’t care for the replication slots due to the possible disk space issue as we don’t run shifts around the clock. So I’ll have to mount the archive via NFS like I had before, not a big deal.

As an alternative to NFS I was thinking about making the archives available via HTTPD and using wget or curl in my script instead of a copy from NFS. That seems like it would work better from the remote sites.

Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 7349151444 | Email: Scot.Kreienkamp@la-z-boy.com
From: Jeff Janes [mailto:jeff.janes@gmail.com]
Sent: Monday, October 22, 2018 11:43 AM
To: Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com>
Cc: Postgres General <pgsql-general@postgresql.org>
Subject: Re: Replication question

On Mon, Oct 22, 2018, 9:54 AM Scot Kreienkamp <Scot.Kreienkamp@la-z-boy.com<mailto:Scot.Kreienkamp@la-z-boy.com>> wrote:
Hi everyone,

We just moved to PG9.6 from 9.1 (yeah, not my choice to wait this long). In 9.1 I had to make the archive location (NFS in my case) available to all the mirrors running PG so that they could catch up whenever they fell behind. I thought I read somewhere that in 9.6, as long as the WAL log is available on disk or in the archive the replication server will provide that to the replication client, and my archive NFS mount didn’t have to be available to all replication clients. It doesn’t seem to be operating that way though. Did I completely remember that wrong or did I misunderstand something?

The master won't read from the archives for you in order to send to an replica. But using replication slots, you can keep the needed log files right in pg_xlog/pg_wal until all replicas get what they need (assuming the disk is large enough). Then you don't need an archive at all for replication purposes, still might for pitr purposes.

Perhaps this is what you heard about.

Cheers,

Jeff

This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidential information which is exempt from disclosure under applicable laws. If you are not the intended recipient, you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information. If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.

Hi,

On 2018-10-22 13:53:40 +0000, Scot Kreienkamp wrote:

We just moved to PG9.6 from 9.1 (yeah, not my choice to wait this long). In 9.1 I had to make the archive location (NFS in my case) available to all the mirrors running PG so that they could catch up whenever they fell behind. I thought I read somewhere that in 9.6, as long as the WAL log is available on disk or in the archive the replication server will provide that to the replication client, and my archive NFS mount didn't have to be available to all replication clients. It doesn't seem to be operating that way though. Did I completely remember that wrong or did I misunderstand something?

You can configure it that way with replication slots. That obviously
requires enough space. It also, as the data is stored on the primary,
can't protect against loosing the entire primary (nor will an nfs served
archive if it's hosted on the primary).

This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidential information which is exempt from disclosure under applicable laws. If you are not the intended recipient, you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information. If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.

GNGNGNG.

Greetings,

Andres Freund

On Mon, Oct 22, 2018 at 1:20 PM Scot Kreienkamp <
Scot.Kreienkamp@la-z-boy.com> wrote:

I remember thinking it was pulling from archive with the restore command
if necessary to augment what it had on disk. If that was the case I wanted
to configure it. I don’t care for the replication slots due to the
possible disk space issue as we don’t run shifts around the clock. So I’ll
have to mount the archive via NFS like I had before, not a big deal.

If the archive partition fills up, then your archive command will start
failing, which means your pg_xlog will also start filling up. If you can
dedicate the same amount of space to the new pg_xlog as you currently have
dedicated to pg_wal + archive partition, then there should be no increased
risk of running out of disk space. Except that you can have
archive_command compress the WAL files upon archival, which can save a lot
of space. Of course there are a variety of reasons that that might not
work, like you use small fast disk for pg_xlog and big slow ones for
archive, or pg_xlog is your problem while archive is some other guy's
problem. But it is something to consider.

As an alternative to NFS I was thinking about making the archives
available via HTTPD and using wget or curl in my script instead of a copy
from NFS. That seems like it would work better from the remote sites.

Yeah, or rsync or scp.

Cheers,

Jeff