Slot issues

Hi Team,

I am getting this ERROR while starting my second slave server

PANIC: too many replication slots active before shutdown
HINT: Increase max_replication_slots and try again.

max_replication_slots on my master is 2 and one of them is already active
for another slave,
do i need to increase this parameter for the need of working of another
slave ? if so whats the reason ?

Thanks
Bhargav K

I guess max_replication_slots has to be greater than active replication
slots.
maybe you need to increase max_replication_slots to 3 to have 2 active
slots.

Regards,
Vijay

On Mon, Oct 15, 2018 at 12:54 AM bhargav kamineni <
bhargavpostgres@gmail.com> wrote:

for 9.x. Default was 0. we have set max_replication_slot =2 , one is
already being used , postgres should use second slot for another server
but dono why its throwing error

On Mon, 15 Oct 2018 at 00:32, Vijaykumar Jain <vjain@opentable.com>
wrote:

I guess max_replication_slots has to > current total slots in use. (and
not >= )

https://www.postgresql.org/docs/10/static/runtime-config-replication.html

https://github.com/postgres/postgres/blob/d6e98ebe375e115c29028f9bd090f0f7e07e2527/src/backend/replication/slot.c#L1506

from the doc, it says defaults are 10, any reason why you have set it to
2. You need to set it at a value higher than number of slots active.
also, i guess changing this parameter would require a server restart.

Regards,
Vijay

On Mon, Oct 15, 2018 at 12:16 AM bhargav kamineni <
bhargavpostgres@gmail.com> wrote:

Hi Team,

I am getting this ERROR while starting my second slave server

PANIC: too many replication slots active before shutdown
HINT: Increase max_replication_slots and try again.

max_replication_slots on my master is 2 and one of them is already
active for another slave,
do i need to increase this parameter for the need of working of another
slave ? if so whats the reason ?

Thanks
Bhargav K

ok my bad.

i just set one of the 9.6.9 versions to have max_replication_slots =2 and
still i had to slots active.

ostgres=# table pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | active
| active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn

-------------------------+--------+-----------+--------+----------+--------+------------+------+--------------+-------------+---------------------
a02 | | physical | | | t | 13719 |
| | 2/D30000D0 |
a03 | | physical | | | t | 13720 |
| | 2/D30000D0 |
(2 rows)

postgres=# show max_replication_slots;
max_replication_slots
-----------------------
2
(1 row)

yep it should work with 2.
Regards,
Vijay

On Mon, Oct 15, 2018 at 1:02 AM Vijaykumar Jain <vjain@opentable.com>
wrote:

I guess max_replication_slots has to be greater than active replication
slots.
maybe you need to increase max_replication_slots to 3 to have 2 active
slots.

Regards,
Vijay

On Mon, Oct 15, 2018 at 12:54 AM bhargav kamineni <
bhargavpostgres@gmail.com> wrote:

for 9.x. Default was 0. we have set max_replication_slot =2 , one is
already being used , postgres should use second slot for another server
but dono why its throwing error

On Mon, 15 Oct 2018 at 00:32, Vijaykumar Jain <vjain@opentable.com>
wrote:

I guess max_replication_slots has to > current total slots in use.
(and not >= )

https://www.postgresql.org/docs/10/static/runtime-config-replication.html

https://github.com/postgres/postgres/blob/d6e98ebe375e115c29028f9bd090f0f7e07e2527/src/backend/replication/slot.c#L1506

from the doc, it says defaults are 10, any reason why you have set it
to 2. You need to set it at a value higher than number of slots active.
also, i guess changing this parameter would require a server restart.

Regards,
Vijay

On Mon, Oct 15, 2018 at 12:16 AM bhargav kamineni <
bhargavpostgres@gmail.com> wrote:

Hi Team,

I am getting this ERROR while starting my second slave server

PANIC: too many replication slots active before shutdown
HINT: Increase max_replication_slots and try again.

max_replication_slots on my master is 2 and one of them is already
active for another slave,
do i need to increase this parameter for the need of working of
another slave ? if so whats the reason ?

Thanks
Bhargav K

from your master, can you give us the output of

select * from pg_replication_slots;

maybe some stray slot exists that you may not be aware of ?

Regards,
Vijay

On Mon, Oct 15, 2018 at 1:08 AM Vijaykumar Jain <vjain@opentable.com>
wrote:

ok my bad.

i just set one of the 9.6.9 versions to have max_replication_slots =2 and
still i had to slots active.

ostgres=# table pg_replication_slots;
slot_name | plugin | slot_type | datoid | database |
active | active_pid | xmin | catalog_xmin | restart_lsn |
confirmed_flush_lsn

-------------------------+--------+-----------+--------+----------+--------+------------+------+--------------+-------------+---------------------
a02 | | physical | | | t | 13719 |
| | 2/D30000D0 |
a03 | | physical | | | t | 13720 |
| | 2/D30000D0 |
(2 rows)

postgres=# show max_replication_slots;
max_replication_slots
-----------------------
2
(1 row)

yep it should work with 2.
Regards,
Vijay

On Mon, Oct 15, 2018 at 1:02 AM Vijaykumar Jain <vjain@opentable.com>
wrote:

I guess max_replication_slots has to be greater than active replication
slots.
maybe you need to increase max_replication_slots to 3 to have 2 active
slots.

Regards,
Vijay

On Mon, Oct 15, 2018 at 12:54 AM bhargav kamineni <
bhargavpostgres@gmail.com> wrote:

for 9.x. Default was 0. we have set max_replication_slot =2 , one is
already being used , postgres should use second slot for another server
but dono why its throwing error

On Mon, 15 Oct 2018 at 00:32, Vijaykumar Jain <vjain@opentable.com>
wrote:

I guess max_replication_slots has to > current total slots in use.
(and not >= )

https://www.postgresql.org/docs/10/static/runtime-config-replication.html

https://github.com/postgres/postgres/blob/d6e98ebe375e115c29028f9bd090f0f7e07e2527/src/backend/replication/slot.c#L1506

from the doc, it says defaults are 10, any reason why you have set it
to 2. You need to set it at a value higher than number of slots active.
also, i guess changing this parameter would require a server restart.

Regards,
Vijay

On Mon, Oct 15, 2018 at 12:16 AM bhargav kamineni <
bhargavpostgres@gmail.com> wrote:

Hi Team,

I am getting this ERROR while starting my second slave server

PANIC: too many replication slots active before shutdown
HINT: Increase max_replication_slots and try again.

max_replication_slots on my master is 2 and one of them is already
active for another slave,
do i need to increase this parameter for the need of working of
another slave ? if so whats the reason ?

Thanks
Bhargav K

Hi,

On 2018-10-15 00:15:53 +0530, bhargav kamineni wrote:

Hi Team,

I am getting this ERROR while starting my second slave server

PANIC: too many replication slots active before shutdown
HINT: Increase max_replication_slots and try again.

max_replication_slots on my master is 2 and one of them is already active
for another slave,
do i need to increase this parameter for the need of working of another
slave ? if so whats the reason ?

You're getting that error on the standby, not the primary, right? In
all likelihood the problem is that you copied over replication slots to
your standby server, but that *on the standby* haven't set
max_replication_slots high enough.

Greetings,

Andres Freund

I got his on standby, could you please explain in detail about
--*but that *on the standby* haven't set max_replication_slots high
enough*.

Thanks,
Bhargav

On Mon, 15 Oct 2018 at 01:50, Andres Freund <andres@anarazel.de> wrote:

Hi,

On 2018-10-15 00:15:53 +0530, bhargav kamineni wrote:

Hi Team,

I am getting this ERROR while starting my second slave server

PANIC: too many replication slots active before shutdown
HINT: Increase max_replication_slots and try again.

max_replication_slots on my master is 2 and one of them is already

active

for another slave,
do i need to increase this parameter for the need of working of another
slave ? if so whats the reason ?

You're getting that error on the standby, not the primary, right? In
all likelihood the problem is that you copied over replication slots to
your standby server, but that *on the standby* haven't set
max_replication_slots high enough.

Greetings,

Andres Freund

i guess he is trying to confirm if the value of max_replication_slot is
the same on all the master and the standby nodes.

also,

I am trying to shoot in the dark.

can you give the output of (on master)
ls /var/lib/postgresql/<pg_version>/main/pg_replslot/*

also the value of max_replication_slot on the master and all the standby's.
also, how are you creating the replication slot?

can you share the query?

also pls share the content of the recovery.conf on the standby nodes (both
currently running and currently not running).
you can scribble company secret stuff.

Regards,
Vijay

On Mon, Oct 15, 2018 at 1:58 AM bhargav kamineni <
bhargavpostgres@gmail.com> wrote:

I got his on standby, could you please explain in detail about
--*but that *on the standby* haven't set max_replication_slots high
enough*.

Thanks,
Bhargav

On Mon, 15 Oct 2018 at 01:50, Andres Freund <andres@anarazel.de> wrote:

Hi,

On 2018-10-15 00:15:53 +0530, bhargav kamineni wrote:

Hi Team,

I am getting this ERROR while starting my second slave server

PANIC: too many replication slots active before shutdown
HINT: Increase max_replication_slots and try again.

max_replication_slots on my master is 2 and one of them is already

active

for another slave,
do i need to increase this parameter for the need of working of another
slave ? if so whats the reason ?

You're getting that error on the standby, not the primary, right? In
all likelihood the problem is that you copied over replication slots to
your standby server, but that *on the standby* haven't set
max_replication_slots high enough.

Greetings,

Andres Freund

ok wait, you data directory path may be different :)
i mean wherever your pg data dir is, from there find pg_replslot folder
and list the contents of it.

Regards,
Vijay

On Mon, Oct 15, 2018 at 2:10 AM Vijaykumar Jain <vjain@opentable.com>
wrote:

i guess he is trying to confirm if the value of max_replication_slot is
the same on all the master and the standby nodes.

also,

I am trying to shoot in the dark.

can you give the output of (on master)
ls /var/lib/postgresql/<pg_version>/main/pg_replslot/*

also the value of max_replication_slot on the master and all the
standby's.
also, how are you creating the replication slot?

can you share the query?

also pls share the content of the recovery.conf on the standby nodes
(both currently running and currently not running).
you can scribble company secret stuff.

Regards,
Vijay

On Mon, Oct 15, 2018 at 1:58 AM bhargav kamineni <
bhargavpostgres@gmail.com> wrote:

I got his on standby, could you please explain in detail about
--*but that *on the standby* haven't set max_replication_slots high
enough*.

Thanks,
Bhargav

On Mon, 15 Oct 2018 at 01:50, Andres Freund <andres@anarazel.de> wrote:

Hi,

On 2018-10-15 00:15:53 +0530, bhargav kamineni wrote:

Hi Team,

I am getting this ERROR while starting my second slave server

PANIC: too many replication slots active before shutdown
HINT: Increase max_replication_slots and try again.

max_replication_slots on my master is 2 and one of them is already

active

for another slave,
do i need to increase this parameter for the need of working of

another

slave ? if so whats the reason ?

You're getting that error on the standby, not the primary, right? In
all likelihood the problem is that you copied over replication slots to
your standby server, but that *on the standby* haven't set
max_replication_slots high enough.

Greetings,

Andres Freund

Hi,

Please try to quote properly.

On 2018-10-15 01:57:53 +0530, bhargav kamineni wrote:

I got his on standby, could you please explain in detail about
--*but that *on the standby* haven't set max_replication_slots high

enough*

.

What is max_replication_slots set to on the new standby?

If you created the new basebackup using rsync, and didn't exclude
pg_replication_slot, it'll have copied the slots from the primary. And
thus needs a high enough max_replication_slots to work with them.

- Andres

Hi,

Please try to quote properly.

On 2018-10-15 01:57:53 +0530, bhargav kamineni wrote:

I got his on standby, could you please explain in detail about
--*but that *on the standby* haven't set max_replication_slots high

enough*

.

What is max_replication_slots set to on the new standby?

If you created the new basebackup using rsync, and didn't exclude
pg_replication_slot, it'll have copied the slots from the primary. And
thus needs a high enough max_replication_slots to work with them.

- Andres

Yeah i have used rsync , Got it now will increase the
max_replication_slots to high enough , Thank you Andres Freund :-)

On Mon, 15 Oct 2018 at 02:40, Andres Freund <andres@anarazel.de> wrote:

Hi,

Please try to quote properly.

On 2018-10-15 01:57:53 +0530, bhargav kamineni wrote:

I got his on standby, could you please explain in detail about
--*but that *on the standby* haven't set max_replication_slots high

enough*

.

What is max_replication_slots set to on the new standby?

If you created the new basebackup using rsync, and didn't exclude
pg_replication_slot, it'll have copied the slots from the primary. And
thus needs a high enough max_replication_slots to work with them.

- Andres

Hi,

As I just wrote:

On Mon, 15 Oct 2018 at 02:40, Andres Freund <andres@anarazel.de> wrote:

Please try to quote properly.

On 2018-10-15 02:45:51 +0530, bhargav kamineni wrote:

Yeah i have used rsync , Got it now will increase the

max_replication_slots

to high enough , Thank you Andres Freund :-)

You probably afterwards want to drop those slots from the new standby.
See the following section from the docs:

https://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP-DATA
"It is often a good idea to also omit from the backup the files within the
cluster's pg_replslot/ directory, so that replication slots that exist on
the master do not become part of the backup. Otherwise, the subsequent use
of the backup to create a standby may result in indefinite retention of WAL
files on the standby, and possibly bloat on the master if hot standby
feedback is enabled, because the clients that are using those replication
slots will still be connecting to and updating the slots on the master, not
the standby. Even if the backup is only intended for use in creating a new
master, copying the replication slots isn't expected to be particularly
useful, since the contents of those slots will likely be badly out of date
by the time the new master comes on line."

Greetings,

Andres Freund