Create replication slot in pg_basebackup if requested and not yet present

On Sun, Mar 19, 2017 at 11:21 AM, Michael Banck <michael.banck@credativ.de>
wrote:

Hi,

with the default configuration improvements so far for 10, it seems to
be very easy to setup streaming replication (at least locally):

$ initdb --pgdata=data1
$ pg_ctl --pgdata=data1 start
$ pg_basebackup --pgdata=data2 --write-recovery-conf
$ sed -i -e 's/^#port.=.5432/port = 5433/' \

-e 's/^#hot_standby.=.off/hot_standby = on/' \
data2/postgresql.conf

$ pg_ctl --pgdata=data2 start

(there might be a case for having hot_standby=on by default, but I think
that got discussed elsewhere and is anyway a different thread).

However, the above does not use replication slots, and if you want to do
so, you get:

$ pg_basebackup --pgdata=data2 --write-recovery-conf --slot=pg2
2017-03-19 11:04:37.978 CET [25362] ERROR: replication slot "pg2" does
not exist
pg_basebackup: could not send replication command "START_REPLICATION":
ERROR: replication slot "pg2" does not exist
pg_basebackup: child process exited with error 1
pg_basebackup: removing data directory "data2"

The error message is clear enough, but I wonder whether people will
start writing streaming replication tutorials just glossing over this
because it's one more step to run CREATE_REPLICATION_SLOT manually.

So I propose the attached tiny patch to just create the slot (if it does
not exist already) in pg_basebackup, somewhat similar to what
pg_receivewal does, albeit unconditionally, if the user explicitly
requested a slot:

$ pg_basebackup --pgdata=data2 --write-recovery-conf --slot=pg2
$ echo $?
0
$ psql -c "DROP_REPLICATION_SLOT pg2" "host=127.0.0.1 port=5432
replication=database user=mba dbname=postgres"
SELECT
$

This would get us somewhat closer to near zero-config replication, in my
opinion. Pardon me if that was discussed already and shot down

Comments?

I think this is a good idea, since it makes replication slots easier to
use. The change to use temporary slots was good for backups, but didn't
help people setting up replicas.

I've been annoyed for a while we didn't have a "create slot" mode in
pg_basebackup, but doing it integrated like this is definitely a step even
better than that.

I think maybe we should output a message when the slot is created, at least
in verbose mode, to make sure people realize that happened. Does that seem
reasonable?

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Hi,

On Sun, Mar 19, 2017 at 05:01:23PM +0100, Magnus Hagander wrote:

On Sun, Mar 19, 2017 at 11:21 AM, Michael Banck <michael.banck@credativ.de>
wrote:

So I propose the attached tiny patch to just create the slot (if it does
not exist already) in pg_basebackup, somewhat similar to what
pg_receivewal does, albeit unconditionally, if the user explicitly
requested a slot:

$ pg_basebackup --pgdata=data2 --write-recovery-conf --slot=pg2
$ echo $?
0
$ psql -c "DROP_REPLICATION_SLOT pg2" "host=127.0.0.1 port=5432
replication=database user=mba dbname=postgres"
SELECT
$

This would get us somewhat closer to near zero-config replication, in my
opinion. Pardon me if that was discussed already and shot down

Comments?

I think this is a good idea, since it makes replication slots easier to
use. The change to use temporary slots was good for backups, but didn't
help people setting up replicas.

I've been annoyed for a while we didn't have a "create slot" mode in
pg_basebackup, but doing it integrated like this is definitely a step even
better than that.

Great!

I think maybe we should output a message when the slot is created, at least
in verbose mode, to make sure people realize that happened. Does that seem
reasonable?

So the patch I sent earlier creates the slot in ReceiveXlogStream() in
receivewal.c, as that's where the temp slot gets created as well, but
now I wonder whether that is maybe not the best place, as pg_receivewal
also calls that function. The other problem with receivewal.c is that
`verbose' isn't around in it so I don't how I'd print out a message
there.

So probably it is better to create the slot in pg_basebackup.c's
StartLogStreamer(), see the attached first patch, that one also adds
a verbose message.

I also now realized I didn't ran the TAP tests and they need updating,
this has been done in the first attached patch as well. Independently of
this patch series I think those two hunks from the third patch are
applicable to current master as well:

 $node->command_fails(
-       [ 'pg_basebackup', '-D', "$tempdir/fail", '-S', 'slot1' ],
+       [ 'pg_basebackup', '-D', "$tempdir/fail", '-X', 'none', '-S', 'slot0' ],
        'pg_basebackup with replication slot fails without -X stream');

as '-X stream' is now the default, and (more cosmetically)

-like($lsn, qr!^0/[0-9A-Z]{7,8}$!, 'restart LSN of slot has advanced');
+like($lsn, qr!^0/[0-9A-F]{7,8}$!, 'restart LSN of slot has advanced');

as we are looking for hex values.

However, the other thing to ponder is that we don't really know whether
the user maybe setup a replication slot on the primary in preparation
already as there seems to be no way to query the list of current slots
via the replication protocal, and setting up another normal connection
just to query pg_replication_slots seems to be overkill. So maybe the
user would be confused then why the slot is created, but IDK.

If there are other uses for querying the available replication slots,
maybe the protocol could be extended to that end for 11.

Finally, it is a bit inconsitent that we'd report the creation of the
permanent slot, but not the temporary one.

I took a look and it seems the main reason why ReceiveXlogStream() does
not call streamutil,c's CreateReplicationSlot() seems to be that the
latter has no notion of temporary slots yet. I'm attaching a second
patch which refactors things a bit more, adding a `is_temporary'
argument to CreateReplicationSlot() and moving the creation of the
temporary slot to pg_basebackup.c's StartLogStreamer() as well (as
pg_recvlogical and pg_receivewal do not deal with temp slots anyway).
This way, the creation of the temporary slot can be mention on --verbose
as well.

Personally, I think it'd be good to be able to have the --slot option
just work in 10, so if the first patch is still acceptable (pending
review) for 10 but not the second, that'd be entirely fine.

Michael

--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.de

credativ GmbH, HRB M�nchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 M�nchengladbach
Gesch�ftsf�hrung: Dr. Michael Meskes, J�rg Folz, Sascha Heuer

Hello,

On 19.03.2017 21:45, Michael Banck wrote:

So the patch I sent earlier creates the slot in ReceiveXlogStream() in
receivewal.c, as that's where the temp slot gets created as well, but
now I wonder whether that is maybe not the best place, as pg_receivewal
also calls that function. The other problem with receivewal.c is that
`verbose' isn't around in it so I don't how I'd print out a message
there.

So probably it is better to create the slot in pg_basebackup.c's
StartLogStreamer(), see the attached first patch, that one also adds
a verbose message.

I think such too. I suppose it is more clearly. StartLogStreamer() is
better place for creating permanent and temporary slots.

Also maybe it would be good if pg_basebackup had a way to drop created
slot. Although "drop slot" is not related with concept of automatically
created slots, it will good if user will have a way to drop slots.

--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

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

Hi,

On Mon, Mar 20, 2017 at 02:42:32PM +0300, Arthur Zakirov wrote:

Also maybe it would be good if pg_basebackup had a way to drop created slot.
Although "drop slot" is not related with concept of automatically created
slots, it will good if user will have a way to drop slots.

If you want to drop the slot after basebackup finishes you'd just use a
temporary slot (i.e. the default), or am I understanding your use-case
incorrectly?

I assumed the primary use-case for creating a non-temporary slot is to
keep it around while the standby is active.

Michael

--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.de

credativ GmbH, HRB M�nchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 M�nchengladbach
Gesch�ftsf�hrung: Dr. Michael Meskes, J�rg Folz, Sascha Heuer

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

On Tue, Mar 21, 2017 at 1:32 AM, Michael Banck
<michael.banck@credativ.de> wrote:

On Mon, Mar 20, 2017 at 02:42:32PM +0300, Arthur Zakirov wrote:

Also maybe it would be good if pg_basebackup had a way to drop created slot.
Although "drop slot" is not related with concept of automatically created
slots, it will good if user will have a way to drop slots.

If you want to drop the slot after basebackup finishes you'd just use a
temporary slot (i.e. the default), or am I understanding your use-case
incorrectly?

Yup, agreed.

I assumed the primary use-case for creating a non-temporary slot is to
keep it around while the standby is active.

Indeed.

     /*
+     * Try to create a permanent replication slot if one is specified. Do
+     * not error out if the slot already exists, other errors are already
+     * reported by CreateReplicationSlot().
+     */
+    if (!stream->temp_slot && stream->replication_slot)
+    {
+        if (!CreateReplicationSlot(conn, stream->replication_slot,
NULL, true, true))
+            return false;
+    }
This could be part of an else taken from the previous if where
temp_slot is checked.

There should be some TAP tests included. And +1 for sharing the same
behavior as pg_receivewal.
--
Michael

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

Hi,

Am Dienstag, den 21.03.2017, 11:03 +0900 schrieb Michael Paquier:

On Tue, Mar 21, 2017 at 1:32 AM, Michael Banck
<michael.banck@credativ.de> wrote:
/*
+     * Try to create a permanent replication slot if one is specified. Do
+     * not error out if the slot already exists, other errors are already
+     * reported by CreateReplicationSlot().
+     */
+    if (!stream->temp_slot && stream->replication_slot)
+    {
+        if (!CreateReplicationSlot(conn, stream->replication_slot,
NULL, true, true))
+            return false;
+    }
This could be part of an else taken from the previous if where
temp_slot is checked.

Not sure how this would work, as ISTM the if clause above only sets the
name for param->temp_slot (if supported), but doesn't distinguish which
kind of slot (if any) will actually be created.

I've done it for the second (refactoring) patch though, but I had to
make `no_slot' a global variable to not have the logic be too
complicated.

There should be some TAP tests included. And +1 for sharing the same
behavior as pg_receivewal.

Well, I've adjusted the TAP tests in so far as it's now checking that
the physical slot got created, previously it checked for the opposite:

|-$node->command_fails(
|+$node->command_ok(
| [ 'pg_basebackup', '-D',
| "$tempdir/backupxs_sl_fail", '-X',
| 'stream', '-S',
|- 'slot1' ],
|- 'pg_basebackup fails with nonexistent replication slot');
|+ 'slot0' ],
|+ 'pg_basebackup runs with nonexistent replication slot');
|+
|+my $lsn = $node->safe_psql('postgres',
|+ q{SELECT restart_lsn FROM pg_replication_slots WHERE slot_name
|= 'slot0'}
|+);
|+like($lsn, qr!^0/[0-9A-F]{7,8}$!, 'slot is present');

I have now made the message a bit clearer by saying "pg_basebackup -S
creates previously nonexistent replication slot".

Probably there could be additional TAP tests, but off the top of my head
I can't think of any?

New patches attached.

Michael

--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.de

credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer

Am Dienstag, den 21.03.2017, 12:52 +0100 schrieb Michael Banck:

New patches attached.

On second though, there was a superfluous whitespace change in
t/010_pg_basebackup.pl, and I've moved the check-for-hex regex fix to
the second patch as it's cosmetic and not related to changing the --slot
creation behaviour.

Michael

--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.de

credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer

On Sun, Mar 19, 2017 at 12:01 PM, Magnus Hagander <magnus@hagander.net> wrote:

I think maybe we should output a message when the slot is created, at least
in verbose mode, to make sure people realize that happened. Does that seem
reasonable?

Slots are great until you leave one lying around by accident. I'm
afraid that no matter what we do, we're going to start getting
complaints from people who mess that up. For example, somebody
creates a replica using the new super-easy method, and then blows it
away without dropping the slot from the master, and then days or weeks
later pg_wal fills up and takes the server down. The user says, oh,
these old write-ahead log files should have gotten removed, and
removes them all. Oops.

So I tend to think that there should always be some explicit user
action to cause the creation of a slot, like --create-slot-if-needed
or --create-slot=name. That still won't prevent careless use of that
option but it's less dangerous than assuming that a user who refers to
a nonexistent slot intended to create it when, perhaps, they just
typo'd it.

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

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

Am Dienstag, den 21.03.2017, 15:34 -0400 schrieb Robert Haas:

On Sun, Mar 19, 2017 at 12:01 PM, Magnus Hagander <magnus@hagander.net> wrote:

I think maybe we should output a message when the slot is created, at least
in verbose mode, to make sure people realize that happened. Does that seem
reasonable?

Slots are great until you leave one lying around by accident. I'm
afraid that no matter what we do, we're going to start getting
complaints from people who mess that up. For example, somebody
creates a replica using the new super-easy method, and then blows it
away without dropping the slot from the master, and then days or weeks
later pg_wal fills up and takes the server down. The user says, oh,
these old write-ahead log files should have gotten removed, and
removes them all. Oops.

Hrm.

Maybe it would be useful to log a warning like "slot XY has not been
active for X minutes", based on some threshold, though possibly the
people not keeping an eye on their replication slots won't spot that in
the logfiles, either.

So I tend to think that there should always be some explicit user
action to cause the creation of a slot, like --create-slot-if-needed
or --create-slot=name. That still won't prevent careless use of that
option but it's less dangerous than assuming that a user who refers to
a nonexistent slot intended to create it when, perhaps, they just
typo'd it.

I guess if we decide (physical) slots should not be created implicitly,
then using the same UI as pg_receivewal makes sense for the sake of
consistency, i.e. "--slot=name --create-slot [--if-not-exists]". That is
rather verbose though and I don't think the --if-not-exists is great UX,
but maybe there is some use-case for erroring out if a slot already
exists that I haven't thought of.

Michael

--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.de

credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer

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

Hi,

Am Mittwoch, den 22.03.2017, 00:40 +0100 schrieb Michael Banck:

I guess if we decide (physical) slots should not be created implicitly,
then using the same UI as pg_receivewal makes sense for the sake of
consistency, i.e. "--slot=name --create-slot [--if-not-exists]". That is
rather verbose though and I don't think the --if-not-exists is great UX,
but maybe there is some use-case for erroring out if a slot already
exists that I haven't thought of.

Thinking about this some more, there's the case of somebody accidentally
using an already existing slot that was meant for another standby which
happens to be down just at that moment.

From some quick testing, that makes replication fail once the other
standby is started up again, but soon after the new standby is taken
down, replication picks up without apparent problems for the other
standby.

Michael

--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.de

credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer

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

On 3/21/17 15:34, Robert Haas wrote:

So I tend to think that there should always be some explicit user
action to cause the creation of a slot, like --create-slot-if-needed
or --create-slot=name. That still won't prevent careless use of that
option but it's less dangerous than assuming that a user who refers to
a nonexistent slot intended to create it when, perhaps, they just
typo'd it.

I have the same concern.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

Hello, I favor this feature.

At Wed, 22 Mar 2017 00:18:19 -0400, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote in <1f5daba9-773d-9281-5608-37f049025bb3@2ndquadrant.com>

On 3/21/17 15:34, Robert Haas wrote:

So I tend to think that there should always be some explicit user
action to cause the creation of a slot, like --create-slot-if-needed
or --create-slot=name. That still won't prevent careless use of that
option but it's less dangerous than assuming that a user who refers to
a nonexistent slot intended to create it when, perhaps, they just
typo'd it.

I have the same concern.

A slot created as !immeediately_reserve (even though currently
CREATE_REPLICATION_SLOT command doesn't seem to have the option)
won't do such a trick but I agree to the point. I think that any
explicit action is required unless any anticipated catastrophic
end caused by remainig slots is evaded implicitly.

Do ephemeral or temporary slots help this case?

Otherwise, I'm proposing a patch to ignore restart-lsn of slots
that let too many WALs to stay on.

/messages/by-id/20170228.122736.123383594.horiguchi.kyotaro@lab.ntt.co.jp

Instaed just ignoring restart-lsn, like Andres' suggestion,
removing (or just disabling) a slot that is marked as
'auto-removable' with the same kind (including disconnection
timeout) of trigger also will work.

I had a similar annoyance with CREATE SUBSCRIPTION. It implicitly
creates a slot on publisher and subscriber fails to have the same
subscription after re-initialization. Of couse DROP SUBSCRIPTION
doesn't help the case. Users don't have a clue to solution, I
suppose. But this would be another topic.

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center

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

On Tue, Mar 21, 2017 at 8:34 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Sun, Mar 19, 2017 at 12:01 PM, Magnus Hagander <magnus@hagander.net>
wrote:

I think maybe we should output a message when the slot is created, at

least

in verbose mode, to make sure people realize that happened. Does that

seem

reasonable?

Slots are great until you leave one lying around by accident. I'm
afraid that no matter what we do, we're going to start getting
complaints from people who mess that up. For example, somebody
creates a replica using the new super-easy method, and then blows it
away without dropping the slot from the master, and then days or weeks
later pg_wal fills up and takes the server down. The user says, oh,
these old write-ahead log files should have gotten removed, and
removes them all. Oops.

So I tend to think that there should always be some explicit user
action to cause the creation of a slot, like --create-slot-if-needed
or --create-slot=name. That still won't prevent careless use of that
option but it's less dangerous than assuming that a user who refers to
a nonexistent slot intended to create it when, perhaps, they just
typo'd it.

Well, the explicit user action would be "--slot". But sure, I can
definitely live with a --create-if-not-exists. The important thing, to me,
is that you can run it as a single command, which makes it a lot easier to
work with. (And not like we currently have for pg_receivewal which requires
a separate command to create the slot)

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

Hi,

On Thu, Mar 23, 2017 at 12:41:54PM +0100, Magnus Hagander wrote:

On Tue, Mar 21, 2017 at 8:34 PM, Robert Haas <robertmhaas@gmail.com> wrote:

So I tend to think that there should always be some explicit user
action to cause the creation of a slot, like --create-slot-if-needed
or --create-slot=name. That still won't prevent careless use of that
option but it's less dangerous than assuming that a user who refers to
a nonexistent slot intended to create it when, perhaps, they just
typo'd it.

Well, the explicit user action would be "--slot". But sure, I can
definitely live with a --create-if-not-exists.

Can we just make that option create slot and don't worry if one exists
already? CreateReplicationSlot() can be told to not mind about already
existing slots, and I don't see a huge point in erroring out if the slot
exists already, unless somebody can show that this leads to data loss
somehow.

The important thing, to me, is that you can run it as a single
command, which makes it a lot easier to work with. (And not like we
currently have for pg_receivewal which requires a separate command to
create the slot)

Oh, that is how it works with pg_receivewal, I have to admit I've never
used it so was a bit confused about this when I read its code.

So in that case I think we don't necessarily need to have the same user
interface at all. I first thought about just adding "-C, --create" (as
in "--create --slot=foo"), but this on second thought this looked a bit
shortsighted - who knows what flashy thing pg_basebackup might create in
5 years... So I settled on --create-slot, which is only slightly more to
type (albeit repetive, "--create-slot --slot=foo"), but adding a short
option "-C" would be fine I thinkg "-C -S foo".

So attached is a patch with adds that option. If people really think it
should be --create-slot-if-not-exists instead I can update the patch, of
course.

I again added a second patch with some further refactoring which makes
it print a message on temporary slot creation in verbose mode.

Michael

--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.de

credativ GmbH, HRB M�nchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 M�nchengladbach
Gesch�ftsf�hrung: Dr. Michael Meskes, J�rg Folz, Sascha Heuer

Hi,

Am Freitag, den 24.03.2017, 19:32 +0100 schrieb Michael Banck:

On Thu, Mar 23, 2017 at 12:41:54PM +0100, Magnus Hagander wrote:

On Tue, Mar 21, 2017 at 8:34 PM, Robert Haas <robertmhaas@gmail.com> wrote:

So I tend to think that there should always be some explicit user
action to cause the creation of a slot, like --create-slot-if-needed
or --create-slot=name. That still won't prevent careless use of that
option but it's less dangerous than assuming that a user who refers to
a nonexistent slot intended to create it when, perhaps, they just
typo'd it.

Well, the explicit user action would be "--slot". But sure, I can
definitely live with a --create-if-not-exists.

Can we just make that option create slot and don't worry if one exists
already? CreateReplicationSlot() can be told to not mind about already
existing slots, and I don't see a huge point in erroring out if the slot
exists already, unless somebody can show that this leads to data loss
somehow.

The important thing, to me, is that you can run it as a single
command, which makes it a lot easier to work with. (And not like we
currently have for pg_receivewal which requires a separate command to
create the slot)

Oh, that is how it works with pg_receivewal, I have to admit I've never
used it so was a bit confused about this when I read its code.

So in that case I think we don't necessarily need to have the same user
interface at all. I first thought about just adding "-C, --create" (as
in "--create --slot=foo"), but this on second thought this looked a bit
shortsighted - who knows what flashy thing pg_basebackup might create in
5 years... So I settled on --create-slot, which is only slightly more to
type (albeit repetive, "--create-slot --slot=foo"), but adding a short
option "-C" would be fine I thinkg "-C -S foo".

So attached is a patch with adds that option. If people really think it
should be --create-slot-if-not-exists instead I can update the patch, of
course.

I again added a second patch with some further refactoring which makes
it print a message on temporary slot creation in verbose mode.

Rebased, squashed and slighly edited version attached. I've added this
to the 2017-07 commitfest now as well:

https://commitfest.postgresql.org/14/1112/

Michael

--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.de

credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer

On Sun, Apr 9, 2017 at 4:22 AM, Michael Banck <michael.banck@credativ.de>
wrote:

Hi,

Am Freitag, den 24.03.2017, 19:32 +0100 schrieb Michael Banck:

On Thu, Mar 23, 2017 at 12:41:54PM +0100, Magnus Hagander wrote:

On Tue, Mar 21, 2017 at 8:34 PM, Robert Haas <robertmhaas@gmail.com>

wrote:

So I tend to think that there should always be some explicit user
action to cause the creation of a slot, like --create-slot-if-needed
or --create-slot=name. That still won't prevent careless use of that
option but it's less dangerous than assuming that a user who refers

to

a nonexistent slot intended to create it when, perhaps, they just
typo'd it.

Well, the explicit user action would be "--slot". But sure, I can
definitely live with a --create-if-not-exists.

Can we just make that option create slot and don't worry if one exists
already? CreateReplicationSlot() can be told to not mind about already
existing slots, and I don't see a huge point in erroring out if the slot
exists already, unless somebody can show that this leads to data loss
somehow.

The important thing, to me, is that you can run it as a single
command, which makes it a lot easier to work with. (And not like we
currently have for pg_receivewal which requires a separate command to
create the slot)

Oh, that is how it works with pg_receivewal, I have to admit I've never
used it so was a bit confused about this when I read its code.

So in that case I think we don't necessarily need to have the same user
interface at all. I first thought about just adding "-C, --create" (as
in "--create --slot=foo"), but this on second thought this looked a bit
shortsighted - who knows what flashy thing pg_basebackup might create in
5 years... So I settled on --create-slot, which is only slightly more to
type (albeit repetive, "--create-slot --slot=foo"), but adding a short
option "-C" would be fine I thinkg "-C -S foo".

So attached is a patch with adds that option. If people really think it
should be --create-slot-if-not-exists instead I can update the patch, of
course.

I again added a second patch with some further refactoring which makes
it print a message on temporary slot creation in verbose mode.

Rebased, squashed and slighly edited version attached. I've added this
to the 2017-07 commitfest now as well:

https://commitfest.postgresql.org/14/1112/

Can you rebase this past some conflicting changes?

Thanks,

Jeff

Hi,

On Tue, Aug 15, 2017 at 02:14:58PM -0700, Jeff Janes wrote:

On Sun, Apr 9, 2017 at 4:22 AM, Michael Banck <michael.banck@credativ.de>
wrote:

Rebased, squashed and slighly edited version attached. I've added this
to the 2017-07 commitfest now as well:

https://commitfest.postgresql.org/14/1112/

Can you rebase this past some conflicting changes?

Thanks for letting me know, PFA a rebased version.

I also adressed the following message which appeared when starting the
TAP tests:

't/010_pg_basebackup.pl ... "my" variable $lsn masks earlier declaration
in same scope at t/010_pg_basebackup.pl line 287.'

Michael

--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.de

credativ GmbH, HRB M�nchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 M�nchengladbach
Gesch�ftsf�hrung: Dr. Michael Meskes, J�rg Folz, Sascha Heuer

On 8/18/17 05:28, Michael Banck wrote:

Rebased, squashed and slighly edited version attached. I've added this
to the 2017-07 commitfest now as well:

https://commitfest.postgresql.org/14/1112/

Can you rebase this past some conflicting changes?

Thanks for letting me know, PFA a rebased version.

I have reviewed the thread so far. I think there is general agreement
that something like this would be good to have.

I have not found any explanation, however, why the "if not exists"
behavior is desirable, let alone as the default. I can only think of
two workflows here: Either you have scripts for previous PG versions
that create the slot externally, in which can you omit --create, or you
use the new functionality to have pg_basebackup create the slot. I
don't see any use for pg_basebackup to opportunistically use a slot if
it happens to exist. Even if there is one, it should not be the
default. So please change that.

A minor point, I suggest to print the message about the replication slot
being created *after* the slot has been created. This aligns with how
logical replication subscriptions work. I don't see the need for
printing a message about temporary slots. Since they are temporary,
they will go away automatically, so there is nothing the user needs to
know there.

With these two changes, I think I'd be content with this patch.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

Hi,

not directly related to the topic, but:

On Tue, Mar 21, 2017 at 03:34:00PM -0400, Robert Haas wrote:

For example, somebody creates a replica using the new super-easy
method, and then blows it away without dropping the slot from the
master,

Just a thought, but maybe there should be some pg_dropstandby command or
similar, which cleans everything (what exactly?) up? That might go some
way to ensure this does not happen.

Michael

--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael.banck@credativ.de

credativ GmbH, HRB M�nchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 M�nchengladbach
Gesch�ftsf�hrung: Dr. Michael Meskes, J�rg Folz, Sascha Heuer

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