Re: pgsql: Introduce replication slots.

Introduce replication slots.

Replication slots are a crash-safe data structure which can be created
on either a master or a standby to prevent premature removal of
write-ahead log segments needed by a standby, as well as (with
hot_standby_feedback=on) pruning of tuples whose removal would cause
replication conflicts. Slots have some advantages over existing
techniques, as explained in the documentation.

In a few places, we refer to the type of replication slots introduced
by this patch as "physical" slots, because forthcoming patches for
logical decoding will also have slots, but with somewhat different
properties.

Andres Freund and Robert Haas

Branch
------
master

Details
-------
http://git.postgresql.org/pg/commitdiff/858ec11858a914d4c380971985709b6d6b7dd6fc

Modified Files
--------------
doc/src/sgml/catalogs.sgml | 99 ++
doc/src/sgml/config.sgml | 19 +
doc/src/sgml/func.sgml | 70 ++
doc/src/sgml/high-availability.sgml | 94 +-
doc/src/sgml/protocol.sgml | 64 +-
doc/src/sgml/recovery-config.sgml | 16 +
doc/src/sgml/ref/pg_receivexlog.sgml | 18 +
src/backend/access/transam/xlog.c | 95 +-
src/backend/catalog/system_views.sql | 12 +
src/backend/replication/Makefile | 2 +-
src/backend/replication/README | 5 +-
src/backend/replication/basebackup.c | 4 +
.../libpqwalreceiver/libpqwalreceiver.c | 16 +-
src/backend/replication/repl_gram.y | 54 +-
src/backend/replication/repl_scanner.l | 57 +-
src/backend/replication/slot.c | 1066 ++++++++++++++++++++
src/backend/replication/slotfuncs.c | 193 ++++
src/backend/replication/walreceiver.c | 5 +-
src/backend/replication/walreceiverfuncs.c | 13 +-
src/backend/replication/walsender.c | 197 +++-
src/backend/storage/ipc/ipci.c | 3 +
src/backend/storage/ipc/procarray.c | 42 +
src/backend/storage/lmgr/lwlock.c | 4 +
src/backend/storage/lmgr/proc.c | 5 +
src/backend/utils/misc/guc.c | 12 +
src/backend/utils/misc/postgresql.conf.sample | 3 +
src/bin/initdb/initdb.c | 1 +
src/bin/pg_basebackup/pg_receivexlog.c | 5 +
src/bin/pg_basebackup/receivelog.c | 49 +-
src/bin/pg_basebackup/streamutil.c | 1 +
src/bin/pg_basebackup/streamutil.h | 1 +
src/include/access/xlog.h | 1 +
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_proc.h | 8 +
src/include/nodes/nodes.h | 2 +
src/include/nodes/replnodes.h | 32 +
src/include/replication/slot.h | 120 +++
src/include/replication/walreceiver.h | 11 +-
src/include/storage/lwlock.h | 4 +-
src/include/storage/procarray.h | 2 +
src/test/regress/expected/rules.out | 9 +
src/tools/pgindent/typedefs.list | 2 +
42 files changed, 2356 insertions(+), 62 deletions(-)

On Sat, Feb 1, 2014 at 12:50 PM, Robert Haas <rhaas(at)postgresql(dot)org> wrote:
> Introduce replication slots.
>
> Replication slots are a crash-safe data structure which can be created
> on either a master or a standby to prevent premature removal of
> write-ahead log segments needed by a standby, as well as (with
> hot_standby_feedback=on) pruning of tuples whose removal would cause
> replication conflicts. Slots have some advantages over existing
> techniques, as explained in the documentation.
>
> In a few places, we refer to the type of replication slots introduced
> by this patch as "physical" slots, because forthcoming patches for
> logical decoding will also have slots, but with somewhat different
> properties.

This patch changed basebackup.c so that it skips pg_replslot. It's OK
to skip all files in that directory, but an empty pg_replslot must be
included in the backup. Otherwise we cannot start PostgreSQL from
the backup taken via pg_basebackup. Attached patch fixes this problem.

Regards,

--
Fujii Masao

On 2014-02-01 16:47:47 +0900, Fujii Masao wrote:
> On Sat, Feb 1, 2014 at 12:50 PM, Robert Haas <rhaas(at)postgresql(dot)org> wrote:
> > Introduce replication slots.
> >
> > Replication slots are a crash-safe data structure which can be created
> > on either a master or a standby to prevent premature removal of
> > write-ahead log segments needed by a standby, as well as (with
> > hot_standby_feedback=on) pruning of tuples whose removal would cause
> > replication conflicts. Slots have some advantages over existing
> > techniques, as explained in the documentation.
> >
> > In a few places, we refer to the type of replication slots introduced
> > by this patch as "physical" slots, because forthcoming patches for
> > logical decoding will also have slots, but with somewhat different
> > properties.
>
> This patch changed basebackup.c so that it skips pg_replslot. It's OK
> to skip all files in that directory, but an empty pg_replslot must be
> included in the backup. Otherwise we cannot start PostgreSQL from
> the backup taken via pg_basebackup. Attached patch fixes this problem.

That's a pretty fair point. Not sure how that could escape my
notice. The patch does look sane to me.

I wonder if we additionally should add code to recreate pg_replslot on
startup, similar to pg_xlog?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Sat, Feb 1, 2014 at 6:09 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2014-02-01 16:47:47 +0900, Fujii Masao wrote:
>> On Sat, Feb 1, 2014 at 12:50 PM, Robert Haas <rhaas(at)postgresql(dot)org> wrote:
>> > Introduce replication slots.
>> >
>> > Replication slots are a crash-safe data structure which can be created
>> > on either a master or a standby to prevent premature removal of
>> > write-ahead log segments needed by a standby, as well as (with
>> > hot_standby_feedback=on) pruning of tuples whose removal would cause
>> > replication conflicts. Slots have some advantages over existing
>> > techniques, as explained in the documentation.
>> >
>> > In a few places, we refer to the type of replication slots introduced
>> > by this patch as "physical" slots, because forthcoming patches for
>> > logical decoding will also have slots, but with somewhat different
>> > properties.
>>
>> This patch changed basebackup.c so that it skips pg_replslot. It's OK
>> to skip all files in that directory, but an empty pg_replslot must be
>> included in the backup. Otherwise we cannot start PostgreSQL from
>> the backup taken via pg_basebackup. Attached patch fixes this problem.
>
> That's a pretty fair point. Not sure how that could escape my
> notice. The patch does look sane to me.
>
> I wonder if we additionally should add code to recreate pg_replslot on
> startup, similar to pg_xlog?

Similar to pg_xlog/archive_status, not pg_xlog? That might be an option.
But I'm not inclined to do that for now. The fact that the essential
directory like
pg_replslot doesn't exist indicates the symptom of something strange. And,
changing that way might prevent us from detecting such symptom.

BTW, we should add the setting of primary_slotname into recovery.conf.sample?

Regards,

--
Fujii Masao

On 2014-02-01 18:33:08 +0900, Fujii Masao wrote:
> On Sat, Feb 1, 2014 at 6:09 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > On 2014-02-01 16:47:47 +0900, Fujii Masao wrote:
> >> This patch changed basebackup.c so that it skips pg_replslot. It's OK
> >> to skip all files in that directory, but an empty pg_replslot must be
> >> included in the backup. Otherwise we cannot start PostgreSQL from
> >> the backup taken via pg_basebackup. Attached patch fixes this problem.
> >
> > That's a pretty fair point. Not sure how that could escape my
> > notice. The patch does look sane to me.
> >
> > I wonder if we additionally should add code to recreate pg_replslot on
> > startup, similar to pg_xlog?
>
> Similar to pg_xlog/archive_status, not pg_xlog? That might be an option.
> But I'm not inclined to do that for now. The fact that the essential
> directory like pg_replslot doesn't exist indicates the symptom of
> something strange. And, changing that way might prevent us from
> detecting such symptom.

The reason I am wondering is that it makes a fair bit of sense to
exclude it in open-coded base backups as well, and excluding the
entire directory might be the easiest way there. But I guess people
manage for pg_xlog/, so it's really not something that would reduce pain
measurably.

> BTW, we should add the setting of primary_slotname into
> recovery.conf.sample?

Yes.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 1 February 2014 04:50, Robert Haas <rhaas(at)postgresql(dot)org> wrote:
> doc/src/sgml/high-availability.sgml | 94 +-

s/configuration a replication slot/configuring a replication/

> doc/src/sgml/ref/pg_receivexlog.sgml

s/paramter/parameter/

> src/backend/replication/slot.c | 1066 ++++++++++++++++++++

s/they also useful/they are also useful/

s/name to be uses/name to be used/

s/Find an previously/Find a previously/

s/nobody else wil write/nobody else will write/

s/wouldn't know wether/wouldn't know whether/

> src/backend/replication/walsender.c | 197 +++-

s/slot should saved to/slot should save to|slot should be saved to/

s/we don't need the the/we don't need the/

> src/bin/pg_basebackup/receivelog.c | 49 +-

s/elegible/eligible/

--
Thom

On Sat, Feb 1, 2014 at 6:37 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2014-02-01 18:33:08 +0900, Fujii Masao wrote:
>> On Sat, Feb 1, 2014 at 6:09 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>> > On 2014-02-01 16:47:47 +0900, Fujii Masao wrote:
>> >> This patch changed basebackup.c so that it skips pg_replslot. It's OK
>> >> to skip all files in that directory, but an empty pg_replslot must be
>> >> included in the backup. Otherwise we cannot start PostgreSQL from
>> >> the backup taken via pg_basebackup. Attached patch fixes this problem.
>> >
>> > That's a pretty fair point. Not sure how that could escape my
>> > notice. The patch does look sane to me.
>> >
>> > I wonder if we additionally should add code to recreate pg_replslot on
>> > startup, similar to pg_xlog?
>>
>> Similar to pg_xlog/archive_status, not pg_xlog? That might be an option.
>> But I'm not inclined to do that for now. The fact that the essential
>> directory like pg_replslot doesn't exist indicates the symptom of
>> something strange. And, changing that way might prevent us from
>> detecting such symptom.
>
> The reason I am wondering is that it makes a fair bit of sense to
> exclude it in open-coded base backups as well, and excluding the
> entire directory might be the easiest way there. But I guess people
> manage for pg_xlog/, so it's really not something that would reduce pain
> measurably.

On second thought, we should always delete all files in pg_replslot
when starting recovery from the backup? The users who are
using their own backup script instead of pg_basebackup might
include pg_replslot files in the backup. Currently, in this case,
the replication slots which were created before would be available
even after the recovery. Is this OK? If not, all files in pg_replslot
should be removed at the beginning of the PITR. OTOH, if that's OK,
I think that pg_basebackup should not skip pg_replslot files.
Thought?

Regards,

--
Fujii Masao

On Sun, Feb 2, 2014 at 8:19 AM, Thom Brown <thom(at)linux(dot)com> wrote:
> On 1 February 2014 04:50, Robert Haas <rhaas(at)postgresql(dot)org> wrote:
>> doc/src/sgml/high-availability.sgml | 94 +-
>
> s/configuration a replication slot/configuring a replication/
>
>
>> doc/src/sgml/ref/pg_receivexlog.sgml
>
> s/paramter/parameter/
>
>
>> src/backend/replication/slot.c | 1066 ++++++++++++++++++++
>
> s/they also useful/they are also useful/
>
> s/name to be uses/name to be used/
>
> s/Find an previously/Find a previously/
>
> s/nobody else wil write/nobody else will write/
>
> s/wouldn't know wether/wouldn't know whether/
>
>
>> src/backend/replication/walsender.c | 197 +++-
>
> s/slot should saved to/slot should save to|slot should be saved to/
>
> s/we don't need the the/we don't need the/
>
>
>> src/bin/pg_basebackup/receivelog.c | 49 +-
>
> s/elegible/eligible/

Thanks! Committed.

Regards,

--
Fujii Masao

On 2014-02-02 14:44:17 +0900, Fujii Masao wrote:
> On Sat, Feb 1, 2014 at 6:37 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > On 2014-02-01 18:33:08 +0900, Fujii Masao wrote:
> >> On Sat, Feb 1, 2014 at 6:09 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> >> > On 2014-02-01 16:47:47 +0900, Fujii Masao wrote:
> >> >> This patch changed basebackup.c so that it skips pg_replslot. It's OK
> >> >> to skip all files in that directory, but an empty pg_replslot must be
> >> >> included in the backup. Otherwise we cannot start PostgreSQL from
> >> >> the backup taken via pg_basebackup. Attached patch fixes this problem.
> >> >
> >> > That's a pretty fair point. Not sure how that could escape my
> >> > notice. The patch does look sane to me.
> >> >
> >> > I wonder if we additionally should add code to recreate pg_replslot on
> >> > startup, similar to pg_xlog?
> >>
> >> Similar to pg_xlog/archive_status, not pg_xlog? That might be an option.
> >> But I'm not inclined to do that for now. The fact that the essential
> >> directory like pg_replslot doesn't exist indicates the symptom of
> >> something strange. And, changing that way might prevent us from
> >> detecting such symptom.
> >
> > The reason I am wondering is that it makes a fair bit of sense to
> > exclude it in open-coded base backups as well, and excluding the
> > entire directory might be the easiest way there. But I guess people
> > manage for pg_xlog/, so it's really not something that would reduce pain
> > measurably.
>
> On second thought, we should always delete all files in pg_replslot
> when starting recovery from the backup?

Are you suggesting to always delete them when in standby_mode? If so,
no, that'd be bad, we intentially *do* want to support situations in
which we stream from the standby, i.e. cascading types of setups.

If you mean doing so when initially starting with a backup label,
hm. Maybe. That would make it impossible to keep replication slots when
moving to a new server with a short downtime, which seems a bit
annoying.

> The users who are
> using their own backup script instead of pg_basebackup might
> include pg_replslot files in the backup. Currently, in this case,
> the replication slots which were created before would be available
> even after the recovery. Is this OK? If not, all files in pg_replslot
> should be removed at the beginning of the PITR. OTOH, if that's OK,
> I think that pg_basebackup should not skip pg_replslot files.
> Thought?

Robert raised this previously in
http://archives.postgresql.org/message-id/CA%2BTgmoar6BLb%2B7BQUYEmkmdFSE1f8khCZCDP-aCojOrESiNLBg%40mail.gmail.com :
> - Exclude pg_replslot from base backups. This might need more thought
> and documentation; people who use the filesystem method to perform
> backups might need to be advised to remove this directory in some
> cases also, or people who use pg_basebackup might want to keep it in
> some cases (not sure).

I can see usecases for removing and keeping them. Removing them has the
big advantage that the user won't be surprised by a slot's existance
which prevents resources (WAL, xmin horizon/VACUUM) from being
reclaimed.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Sun, Feb 2, 2014 at 11:06 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2014-02-02 14:44:17 +0900, Fujii Masao wrote:
>> On Sat, Feb 1, 2014 at 6:37 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>> > On 2014-02-01 18:33:08 +0900, Fujii Masao wrote:
>> >> On Sat, Feb 1, 2014 at 6:09 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>> >> > On 2014-02-01 16:47:47 +0900, Fujii Masao wrote:
>> >> >> This patch changed basebackup.c so that it skips pg_replslot. It's OK
>> >> >> to skip all files in that directory, but an empty pg_replslot must be
>> >> >> included in the backup. Otherwise we cannot start PostgreSQL from
>> >> >> the backup taken via pg_basebackup. Attached patch fixes this problem.
>> >> >
>> >> > That's a pretty fair point. Not sure how that could escape my
>> >> > notice. The patch does look sane to me.
>> >> >
>> >> > I wonder if we additionally should add code to recreate pg_replslot on
>> >> > startup, similar to pg_xlog?
>> >>
>> >> Similar to pg_xlog/archive_status, not pg_xlog? That might be an option.
>> >> But I'm not inclined to do that for now. The fact that the essential
>> >> directory like pg_replslot doesn't exist indicates the symptom of
>> >> something strange. And, changing that way might prevent us from
>> >> detecting such symptom.
>> >
>> > The reason I am wondering is that it makes a fair bit of sense to
>> > exclude it in open-coded base backups as well, and excluding the
>> > entire directory might be the easiest way there. But I guess people
>> > manage for pg_xlog/, so it's really not something that would reduce pain
>> > measurably.
>>
>> On second thought, we should always delete all files in pg_replslot
>> when starting recovery from the backup?
>
> Are you suggesting to always delete them when in standby_mode? If so,
> no, that'd be bad, we intentially *do* want to support situations in
> which we stream from the standby, i.e. cascading types of setups.
>
> If you mean doing so when initially starting with a backup label,
> hm. Maybe. That would make it impossible to keep replication slots when
> moving to a new server with a short downtime, which seems a bit
> annoying.

Yeah, I was thinking the latter.

>> The users who are
>> using their own backup script instead of pg_basebackup might
>> include pg_replslot files in the backup. Currently, in this case,
>> the replication slots which were created before would be available
>> even after the recovery. Is this OK? If not, all files in pg_replslot
>> should be removed at the beginning of the PITR. OTOH, if that's OK,
>> I think that pg_basebackup should not skip pg_replslot files.
>> Thought?
>
> Robert raised this previously in
> http://archives.postgresql.org/message-id/CA%2BTgmoar6BLb%2B7BQUYEmkmdFSE1f8khCZCDP-aCojOrESiNLBg%40mail.gmail.com :
>> - Exclude pg_replslot from base backups. This might need more thought
>> and documentation; people who use the filesystem method to perform
>> backups might need to be advised to remove this directory in some
>> cases also, or people who use pg_basebackup might want to keep it in
>> some cases (not sure).
>
> I can see usecases for removing and keeping them. Removing them has the
> big advantage that the user won't be surprised by a slot's existance
> which prevents resources (WAL, xmin horizon/VACUUM) from being
> reclaimed.

Agreed.

Regards,

--
Fujii Masao

On Sat, Feb 1, 2014 at 6:33 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Sat, Feb 1, 2014 at 6:09 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>> On 2014-02-01 16:47:47 +0900, Fujii Masao wrote:
>>> On Sat, Feb 1, 2014 at 12:50 PM, Robert Haas <rhaas(at)postgresql(dot)org> wrote:
>>> > Introduce replication slots.
>>> >
>>> > Replication slots are a crash-safe data structure which can be created
>>> > on either a master or a standby to prevent premature removal of
>>> > write-ahead log segments needed by a standby, as well as (with
>>> > hot_standby_feedback=on) pruning of tuples whose removal would cause
>>> > replication conflicts. Slots have some advantages over existing
>>> > techniques, as explained in the documentation.
>>> >
>>> > In a few places, we refer to the type of replication slots introduced
>>> > by this patch as "physical" slots, because forthcoming patches for
>>> > logical decoding will also have slots, but with somewhat different
>>> > properties.
>>>
>>> This patch changed basebackup.c so that it skips pg_replslot. It's OK
>>> to skip all files in that directory, but an empty pg_replslot must be
>>> included in the backup. Otherwise we cannot start PostgreSQL from
>>> the backup taken via pg_basebackup. Attached patch fixes this problem.
>>
>> That's a pretty fair point. Not sure how that could escape my
>> notice. The patch does look sane to me.
>>
>> I wonder if we additionally should add code to recreate pg_replslot on
>> startup, similar to pg_xlog?
>
> Similar to pg_xlog/archive_status, not pg_xlog? That might be an option.
> But I'm not inclined to do that for now. The fact that the essential
> directory like
> pg_replslot doesn't exist indicates the symptom of something strange. And,
> changing that way might prevent us from detecting such symptom.
To facilitate the user's life, I would be more inclined to do the following:
1) Have pg_replslot created if it does not exist in the base backup
such as users are not surprised that there old scripts do not work
anymore with 9.4
2) Add a new option in BASE_BACKUP (and pg_basebackup as well) to
stream pg_replslot data at will, because I see use cases for both
keeping those files in a base backup as well as removing them.
Regards,
--
Michael

Also, in 25.2.6:

"or by storing the segments in an archive using restore_command".

I think you meant "archive_command", right?

Jeff Davis

On Tue, Feb 4, 2014 at 2:29 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> Also, in 25.2.6:
>
> "or by storing the segments in an archive using restore_command".
>
> I think you meant "archive_command", right?

Yep, thanks. Fixed.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 4 February 2014 05:19, Michael Paquier <michael(dot)paquier(at)gmail(dot)com> wrote:
> On Sat, Feb 1, 2014 at 6:33 PM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> On Sat, Feb 1, 2014 at 6:09 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>>> On 2014-02-01 16:47:47 +0900, Fujii Masao wrote:
>>>> On Sat, Feb 1, 2014 at 12:50 PM, Robert Haas <rhaas(at)postgresql(dot)org> wrote:
>>>> > Introduce replication slots.
>>>> >
>>>> > Replication slots are a crash-safe data structure which can be created
>>>> > on either a master or a standby to prevent premature removal of
>>>> > write-ahead log segments needed by a standby, as well as (with
>>>> > hot_standby_feedback=on) pruning of tuples whose removal would cause
>>>> > replication conflicts. Slots have some advantages over existing
>>>> > techniques, as explained in the documentation.
>>>> >
>>>> > In a few places, we refer to the type of replication slots introduced
>>>> > by this patch as "physical" slots, because forthcoming patches for
>>>> > logical decoding will also have slots, but with somewhat different
>>>> > properties.
>>>>
>>>> This patch changed basebackup.c so that it skips pg_replslot. It's OK
>>>> to skip all files in that directory, but an empty pg_replslot must be
>>>> included in the backup. Otherwise we cannot start PostgreSQL from
>>>> the backup taken via pg_basebackup. Attached patch fixes this problem.
>>>
>>> That's a pretty fair point. Not sure how that could escape my
>>> notice. The patch does look sane to me.
>>>
>>> I wonder if we additionally should add code to recreate pg_replslot on
>>> startup, similar to pg_xlog?
>>
>> Similar to pg_xlog/archive_status, not pg_xlog? That might be an option.
>> But I'm not inclined to do that for now. The fact that the essential
>> directory like
>> pg_replslot doesn't exist indicates the symptom of something strange. And,
>> changing that way might prevent us from detecting such symptom.
> To facilitate the user's life, I would be more inclined to do the following:
> 1) Have pg_replslot created if it does not exist in the base backup
> such as users are not surprised that there old scripts do not work
> anymore with 9.4

Yeah, a script I use to create a replication set-up no longer works.
Using latest Git master, this is causing standbys created with
pg_basebackup to fail to start.

FATAL: could not open directory "pg_replslot": No such file or directory

The pg_basebackup command used was:

pg_basebackup -D standby1 -h localhost -p 5530 -U rep_user

--
Thom

Hi Fujii,

On 2014-02-01 16:47:47 +0900, Fujii Masao wrote:
> This patch changed basebackup.c so that it skips pg_replslot. It's OK
> to skip all files in that directory, but an empty pg_replslot must be
> included in the backup. Otherwise we cannot start PostgreSQL from
> the backup taken via pg_basebackup. Attached patch fixes this problem.

Do you plan to commit this patch? It's clearly an improvement over the
current situation...

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 6 February 2014 10:29, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:

> Hi Fujii,
>
> On 2014-02-01 16:47:47 +0900, Fujii Masao wrote:
> > This patch changed basebackup.c so that it skips pg_replslot. It's OK
> > to skip all files in that directory, but an empty pg_replslot must be
> > included in the backup. Otherwise we cannot start PostgreSQL from
> > the backup taken via pg_basebackup. Attached patch fixes this problem.
>
> Do you plan to commit this patch? It's clearly an improvement over the
> current situation...

Yeah, this is still an annoyance.

--
Thom

On Tue, Feb 25, 2014 at 10:11 AM, Thom Brown <thom(at)linux(dot)com> wrote:
> On 6 February 2014 10:29, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>> Hi Fujii,
>>
>> On 2014-02-01 16:47:47 +0900, Fujii Masao wrote:
>> > This patch changed basebackup.c so that it skips pg_replslot. It's OK
>> > to skip all files in that directory, but an empty pg_replslot must be
>> > included in the backup. Otherwise we cannot start PostgreSQL from
>> > the backup taken via pg_basebackup. Attached patch fixes this problem.
>>
>> Do you plan to commit this patch? It's clearly an improvement over the
>> current situation...
>
> Yeah, this is still an annoyance.

Since Fujii Masao doesn't seem to be around, I'll commit this. But
don't we need to update the documentation as well, for those using the
file-copy method of taking a backup?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi,

Vik Faering noticed a couple of oversights in the replication slot
function reference, and I noticed some more, including using the text
type instead of name, while looking.

Patch attached.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 1 February 2014 03:50, Robert Haas <rhaas(at)postgresql(dot)org> wrote:

> Introduce replication slots.
>
> Replication slots are a crash-safe data structure which can be created
> on either a master or a standby to prevent premature removal of
> write-ahead log segments needed by a standby, as well as (with
> hot_standby_feedback=on) pruning of tuples whose removal would cause
> replication conflicts. Slots have some advantages over existing
> techniques, as explained in the documentation.
>
> In a few places, we refer to the type of replication slots introduced
> by this patch as "physical" slots, because forthcoming patches for
> logical decoding will also have slots, but with somewhat different
> properties.
>

So now that I've actually gone to use physical replication slots, I can't
get them working.

Primary postgresql.conf changes:

shared_buffers = 8MB
logging_collector = on
log_line_prefix = '%m - %u - %d'
max_connections = 8
wal_level = 'hot_standby'
port = 5532
max_wal_senders = 4
max_replication_slots = 4

Standby postgresql.conf changes:

shared_buffers = 8MB
logging_collector = on
log_line_prefix = '%m - %u - %d'
max_connections = 8
wal_level = 'hot_standby'
port = 5533
hot_standby = on

Standby recovery.conf:

standby_mode = 'on'
recovery_target_timeline = 'latest'
primary_conninfo = 'host=127.0.0.1 user=rep_user port=5532
application_name=standby1'
primary_slotname = 'primary_physical_slot'

Primary:

psql://thom(at)[local]:5532/postgres

# SELECT * FROM pg_replication_slots;
slot_name | slot_type | datoid | database | active | xmin |
restart_lsn
-----------------------+-----------+--------+----------+--------+------+-------------
primary_physical_slot | physical | 0 | | f | |
(1 row)

Errors in primary log:

2014-02-28 19:00:40.459 GMT - rep_user - [unknown]ERROR: syntax error
(repeated every 5 seconds)

Errors in standby log:

2014-02-28 19:00:40.459 GMT - - FATAL: could not start WAL streaming:
ERROR: syntax error
(repeated every 5 seconds)

Am I missing something obvious? The error message isn't particularly
helpful.
--
Thom

On 2014-02-28 19:05:40 +0000, Thom Brown wrote:
> On 1 February 2014 03:50, Robert Haas <rhaas(at)postgresql(dot)org> wrote:
>
> > Introduce replication slots.
> >
> > Replication slots are a crash-safe data structure which can be created
> > on either a master or a standby to prevent premature removal of
> > write-ahead log segments needed by a standby, as well as (with
> > hot_standby_feedback=on) pruning of tuples whose removal would cause
> > replication conflicts. Slots have some advantages over existing
> > techniques, as explained in the documentation.
> >
> > In a few places, we refer to the type of replication slots introduced
> > by this patch as "physical" slots, because forthcoming patches for
> > logical decoding will also have slots, but with somewhat different
> > properties.
> >
>
> So now that I've actually gone to use physical replication slots, I can't
> get them working.

Aw yuck. Try a shorter name. libpqrcv_startstreaming is truncating the
identifier if it's too long...

Patch fixing that attached.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 28 February 2014 19:22, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:

> On 2014-02-28 19:05:40 +0000, Thom Brown wrote:
> > On 1 February 2014 03:50, Robert Haas <rhaas(at)postgresql(dot)org> wrote:
> >
> > > Introduce replication slots.
> > >
> > > Replication slots are a crash-safe data structure which can be created
> > > on either a master or a standby to prevent premature removal of
> > > write-ahead log segments needed by a standby, as well as (with
> > > hot_standby_feedback=on) pruning of tuples whose removal would cause
> > > replication conflicts. Slots have some advantages over existing
> > > techniques, as explained in the documentation.
> > >
> > > In a few places, we refer to the type of replication slots introduced
> > > by this patch as "physical" slots, because forthcoming patches for
> > > logical decoding will also have slots, but with somewhat different
> > > properties.
> > >
> >
> > So now that I've actually gone to use physical replication slots, I can't
> > get them working.
>
> Aw yuck. Try a shorter name. libpqrcv_startstreaming is truncating the
> identifier if it's too long...
>
> Patch fixing that attached.

Yes, that fixes the problem. Thanks. I can stop quadruple-checking my
config now. :)

--
Thom

On Fri, Feb 28, 2014 at 2:22 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>> So now that I've actually gone to use physical replication slots, I can't
>> get them working.
>
> Aw yuck. Try a shorter name. libpqrcv_startstreaming is truncating the
> identifier if it's too long...
>
> Patch fixing that attached.

Committed, although I wonder if we shouldn't be doing something more
sophisticated here to avoid the need to guess how long the command
could be. Like, maybe using psprintf()?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 25 February 2014 16:25, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> Since Fujii Masao doesn't seem to be around, I'll commit this. But
> don't we need to update the documentation as well, for those using the
> file-copy method of taking a backup?
>

Yes, that sounds like a sensible idea.

--
Thom