Thread: pg_basebackup --create-slot-if-not-exists?
Currently, pg_basebackup has
--create-slot option to create slot if not already exists or
--slot to use existing slot
Which means it needs knowledge on if the slot with the given name already exists or not before invoking the command. If pg_basebackup --create-slot <> command fails for some reason after creating the slot. Re-triggering the same command fails with ERROR slot already exists. Either then need to delete the slot and retrigger Or need to add a check before retriggering the command to check if the slot exists and based on the same alter the command to avoid passing --create-slot option. This poses inconvenience while automating on top of pg_basebackup. As checking for slot presence before invoking pg_basebackup unnecessarily involves issuing separate SQL commands. Would be really helpful for such scenarios if similar to CREATE TABLE, pg_basebackup can have IF NOT EXISTS kind of semantic. (Seems the limitation most likely is coming from CREATE REPLICATION SLOT protocol itself), Thoughts?
--
Ashwin Agrawal (VMware)
On Wednesday, September 21, 2022, Ashwin Agrawal <ashwinstar@gmail.com> wrote:
Currently, pg_basebackup has--create-slot option to create slot if not already exists or--slot to use existing slotWhich means it needs knowledge on if the slot with the given name already exists or not before invoking the command. If pg_basebackup --create-slot <> command fails for some reason after creating the slot. Re-triggering the same command fails with ERROR slot already exists. Either then need to delete the slot and retrigger Or need to add a check before retriggering the command to check if the slot exists and based on the same alter the command to avoid passing --create-slot option. This poses inconvenience while automating on top of pg_basebackup. As checking for slot presence before invoking pg_basebackup unnecessarily involves issuing separate SQL commands. Would be really helpful for such scenarios if similar to CREATE TABLE, pg_basebackup can have IF NOT EXISTS kind of semantic. (Seems the limitation most likely is coming from CREATE REPLICATION SLOT protocol itself), Thoughts?
What’s the use case for automating pg_basebackup with a named replication slot created by the pg_basebackup command? Why can you not leverage a temporary replication slot (i.e., omit —slot). ISTM the create option is basically obsolete now.
David J.
On Wed, Sep 21, 2022 at 05:34:20PM -0700, David G. Johnston wrote:
> What’s the use case for automating pg_basebackup with a named replication
> slot created by the pg_basebackup command? Why can you not leverage a
> temporary replication slot (i.e., omit —slot). ISTM the create option is
> basically obsolete now.
+1.
Perhaps the ask would ease some monitoring around pg_replication_slots
with a fixed slot name? One thing that could come into play is the
possibility to enforce the use of a temporary slot with a name given
by -S as pg_basebackup makes it permanent in this case, still the
temporary slot name being pg_basebackup_${PID} makes the slot
searchable with a pattern.
--
Michael
Attachment
Michael Paquier <michael@paquier.xyz> writes:
> On Wed, Sep 21, 2022 at 05:34:20PM -0700, David G. Johnston wrote:
>> What’s the use case for automating pg_basebackup with a named replication
>> slot created by the pg_basebackup command? Why can you not leverage a
>> temporary replication slot (i.e., omit —slot). ISTM the create option is
>> basically obsolete now.
> +1.
ISTM there'd also be some security concerns, ie what if there's a
pre-existing slot (created by a hostile user, perhaps) that has
properties different from what you expect? I realize that slot
creation is a pretty high-privilege operation, but it's not
superuser-only.
In any case I agree with the point that --create-slot seems
rather obsolete. If you are trying to resume in a previous
replication stream (which seems like the point of persistent
slots) then the slot had better already exist. If you are
satisfied with just starting replication from the current
instant, then a temp slot seems like what you want.
regards, tom lane
On Wed, Sep 21, 2022 at 5:34 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, September 21, 2022, Ashwin Agrawal <ashwinstar@gmail.com> wrote:Currently, pg_basebackup has--create-slot option to create slot if not already exists or--slot to use existing slotWhich means it needs knowledge on if the slot with the given name already exists or not before invoking the command. If pg_basebackup --create-slot <> command fails for some reason after creating the slot. Re-triggering the same command fails with ERROR slot already exists. Either then need to delete the slot and retrigger Or need to add a check before retriggering the command to check if the slot exists and based on the same alter the command to avoid passing --create-slot option. This poses inconvenience while automating on top of pg_basebackup. As checking for slot presence before invoking pg_basebackup unnecessarily involves issuing separate SQL commands. Would be really helpful for such scenarios if similar to CREATE TABLE, pg_basebackup can have IF NOT EXISTS kind of semantic. (Seems the limitation most likely is coming from CREATE REPLICATION SLOT protocol itself), Thoughts?What’s the use case for automating pg_basebackup with a named replication slot created by the pg_basebackup command?
Greenplum runs N (some hundred) number of PostgreSQL instances to form a sharded database cluster. Hence, automation/scripts are in place to create replicas, failover failback for these N instances and such. As Michael said for predictable management and monitoring of the slot across these many instances, specific named replication slots are used across all these instances. These named replication slots are used both for pg_basebackup followed by streaming replication.
Why can you not leverage a temporary replication slot (i.e., omit —slot). ISTM the create option is basically obsolete now.
We would be more than happy to use a temporary replication slot if it provided full functionality. It might be a gap in my understanding, but I feel a temporary replication slot only protects WAL deletion for the duration of pg_basebackup. It doesn't protect the window between pg_basebackup completion and streaming replication starting. With --write-recovery-conf option "primary_slot_name" only gets written to postgresql.auto.conf if the named replication slot is provided, which makes sure the same slot will be used for pg_basebackup and streaming replication hence will keep the WAL around till streaming replica connects after pg_basebackup. How to avoid this window with a temp slot?
--
Ashwin Agrawal (VMware)
Hi, On Wed, Sep 21, 2022 at 09:12:04PM -0400, Tom Lane wrote: > In any case I agree with the point that --create-slot seems > rather obsolete. If you are trying to resume in a previous > replication stream (which seems like the point of persistent > slots) then the slot had better already exist. If you are > satisfied with just starting replication from the current > instant, then a temp slot seems like what you want. One advantage of using a permanent slot is that it's getting written into the recovery configuration when you use --write-recovery-conf and you only need to start the standby after intial bootstrap to have it connect using the slot. Not sure that's worth keeping it around, but it makes automating things somewhat simpler I guess. I do somewhat agree with the thread starter, that --create-slot-if-not-exists would make things even easier, but in the light of your concerns regarding security it's probably not the best idea and would make things even more convoluted than they are now. Michael