Re: [GENERAL] Manage slot in logical/pglogical replication

Hi List,
   I'm m extremely please to  see the logical replication aka:
transaction replication feature been implemented in Pg 10,  very nice
work done by the contrib of this module/feature!
   Since  here is mentioned the "replication slots" are located on
master replication host, if the master goes offline unexpected what
will  be state of slave server :
-will accept  transactions( writes connection)  ?
  will rollback the uncommitted transactions? and move to read-write
state/promote as new  master? ( re cgf slots)
-should the DBA consider cfg replication slots on different  host than
master ( or at least mirror to a 3 side) ?


Thank you
Isabella
-
On 07/13/2017 09:11 AM, pgsql-general-owner@postgresql.org wrote:
> Message Digest
> Volume 1 : Issue 15352 : "text" Format
>
> Messages in this Issue:
>    Re: Systemd support (was:Re: Please say it isn't so)
>    Re: Systemd support (was:Re: Please say it isn't so)
>    Re: Manage slot in logical/pglogical replication
>    I can't cancel/terminate query.
>    Re: Get table OID
>    Re: BDR node removal and rejoin
>    Re: I can't cancel/terminate query.
>
> ----------------------------------------------------------------------
>
> Date: Thu, 13 Jul 2017 10:49:01 -0400
> From: Vick Khera <vivek@khera.org>
> To: pgsql-general@postgresql.org
> Subject: Re: Systemd support (was:Re: Please say it isn't so)
> Message-ID: <CALd+dcf+FxqsSUrkPZvzhLNBwzxS8opTRSFKeLA8XTn62c9phA@mail.gmail.com>
>
> What exactly does the configure flag to enable systemd support do? It seems
> to me that building software to the systemd platform is just the same as
> building it for windows vs unix or any other platform. One can only hope it
> doesn't cause the others to wither away.
>
> On Wed, Jul 12, 2017 at 3:20 AM, Mark Morgan Lloyd <
> markMLl.pgsql-general@telemetry.co.uk> wrote:
>
>> On 12/07/17 05:00, Steve Litt wrote:
>>
>>> Hi all,
>>>
>>> Please tell me this is a mistake:
>>>
>>> https://wiki.postgresql.org/wiki/Systemd
>>>
>>> Why a database system should care about how processes get started is
>>> beyond me. Systemd is an entangled mess that every year subsumes more
>>> and more of the operating system, in a very non-cooperative way.
>>>
>>> There are almost ten init systems. In every one of those init systems,
>>> one can run a process supervisor, such as runit or s6 or
>>> daemontools-encore, completely capable of starting the postgres server.
>>>
>>> Every year, systemd further hinders interoperability, further erodes
>>> interchangeability of parts, and continues to address problems with
>>> WONTFIX. In the long run, you do your users no favor by including
>>> init-system specific code in Postgres or its makefiles. If systemd
>>> can't correctly start Postgres, I guarantee you that s6 or runit,
>>> running on top of systemd, can.
>>>
>>> Postgres doesn't care which language makes a query to it. Why
>>> should Postgres care which init system started it? I hope you can free
>>> Postgres of init-specific code, and if for some reason you can't do
>>> that, at least don't recommend init-specific code.
>>>
>> OTOH since systemd is what's being supported by a significant number of
>> distributions it makes sense to at least try to work robustly with it.
>>
>> While my preference would have been to have made such a change at a major
>> version transition, the reality is that database systems are competitive,
>> and not keeping up with the underlying platform would have been very much
>> to PostgreSQL's disadvantage,
>>
>> OP: Please note that you do yourself no favours at all by posting a
>> subject line which could very easily be misinterpreted as spam.
>>
>> --
>> Mark Morgan Lloyd
>> markMLl .AT. telemetry.co .DOT. uk
>>
>> [Opinions above are the author's, not those of his employers or colleagues]
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
> [Attachment of type text/html removed.]
>
> ------------------------------
>
> Date: Thu, 13 Jul 2017 11:24:10 -0400
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Vick Khera <vivek@khera.org>
> Cc: pgsql-general@postgresql.org
> Subject: Re: Systemd support (was:Re: Please say it isn't so)
> Message-ID: <17764.1499959450@sss.pgh.pa.us>
>
> Vick Khera <vivek@khera.org> writes:
>> What exactly does the configure flag to enable systemd support do?
> Not a lot.  A quick grep for USE_SYSTEMD says it does nothing except
> add code in the postmaster to report ready/not-ready state transitions
> by calling sd_notify().  We have significantly more lines of
> documentation concerning systemd than we do code.
>
>                          regards, tom lane
>
>
> ------------------------------
>
> Date: Thu, 13 Jul 2017 11:55:31 -0400
> From: Alvaro Herrera <alvherre@2ndquadrant.com>
> To: dpat <denni.pat@gmail.com>
> Cc: pgsql-general@postgresql.org
> ubject: Re: Manage slot in logicaS
> l/pglogical replication
> Message-ID: <20170713155531.hfzfxdvibw46bn6x@alvherre.pgsql>
>
> dpat wrote:
>
>> i have configure a master-replica replication with new pglogical 2.0.
>> I have to replicate data over MPLS/VPN, so there is a possibility that the
>> link temporarily interrupts.
>> I know that you have to be accurately estimated pg_xlog folder.
>> How can I handle the prolonged interruption of the link?
>> Can I just extend the folder? Or drop the slot?
> Yeah, data accumulates in the origin side ("master"), so you need to
> make sure you have sufficient room in pg_xlog to hold all the data
> generated during the network interruption.
>
> Dropping the slot would of course release the disk space, but it would
> mean that you'd have to re-create the replica afterwards.  I suppose you
> could have pg_xlog large enough to hold as much data as possible for a
> mid-sized network interruption, and drop the slot as a last resource if
> a connection interrupts for long enough that you risk causing
> reliability problems in the origin.
>
>> I can create a consumer who temporarily saves the data in an external file?
> I don't think so.
>
> --
> Álvaro Herrera                https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
> ------------------------------
>
> Date: Thu, 13 Jul 2017 10:57:46 -0500
> From: Edmundo Robles <edmundo@sw-argos.com>
> To: pgsql-general@postgresql.org
> Subject: I can't cancel/terminate query.
> Message-ID: <CAOXzpYC-nQ+_B8jUfe5-MZS4cuSH5hrQZwu_QQjLLq-EyKGc3g@mail.gmail.com>
>
> Hi!  i have  many  too long time queries,  the oldest  is  almost 16 days,
> so i  tried to cancel and terminate with pg_cancel_backend and
> pg_terminate_backend  but queries is still running.
>
> STIME     ELAPSED ELAPSED %CPU   PID COMMAND
> jun27 15-23:05:46 1379146  0.3 29660 postgres: argos_admin bdkairos
> 127.0.0.1(55605) SELECT
> jun27 15-23:05:46 1379146  0.3 29659 postgres: argos_admin bdkairos
> 127.0.0.1(55604) SELECT
> jun27 15-23:05:46 1379146  0.3 29658 postgres: argos_admin bdkairos
> 127.0.0.1(55603) SELECT
> jun27 15-23:05:16 1379116  0.3 29909 postgres: argos_admin bdkairos
> 127.0.0.1(55767) SELECT
> jun27 15-23:04:51 1379091  0.3 30097 postgres: argos_admin bdkairos
> 127.0.0.1(55909) SELECT
> jun27 15-23:03:49 1379029  0.3 30661 postgres: argos_admin bdkairos
> 127.0.0.1(56303) SELECT
> ...
> What should  i do to safely close that  queries, before to use kill command
> in linux?
>
> I have Postgres 9.4  running on debian jessie.
>
>
>
> --
>
>
> [Attachment of type text/html removed.]
>
> ------------------------------
>
> Date: Thu, 13 Jul 2017 12:00:14 -0400
> From: Melvin Davidson <melvin6925@gmail.com>
> To: Igor Korot <ikorot01@gmail.com>
> Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
> Subject: Re: Get table OID
> Message-ID: <CANu8FixnW+_ph0MEdgCOUrAyzo68sKavU=zKfWX==PdJoFRyLQ@mail.gmail.com>
>
> On Thu, Jul 13, 2017 at 11:06 AM, Igor Korot <ikorot01@gmail.com> wrote:
>
>> Hi, Melvin,
>>
>> On Thu, Jul 13, 2017 at 10:42 AM, Melvin Davidson <melvin6925@gmail.com>
>> wrote:
>>
>>> On Thu, Jul 13, 2017 at 10:36 AM, Igor Korot <ikorot01@gmail.com> wrote:
>>>
>>>>   Hi, ALL,
>>>> Is it possible to get the table ID (or OID) from information_schema
>>>> somewhere?
>>>>
>>>> Thank you.
>>>>
>>>>
>>>> --
>>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-general
>>>>
>>> It 's in pg_class!
>>>
>>> https://www.postgresql.org/docs/9.4/static/catalog-pg-class.html
>>>
>> But pg_class is in postgres database.
>> Can I do something like this:
>>
>> SELECT oid FROM postgres.pg_class WHERE relname = <my_table_name>;
>>
>> Or I will have to connect to postgres database?
>>
>> Thank you.
>>
>>
>>> IOW:
>>> SELECT relname, oid  FROM pg_class WHERE relkind = 'r' ORDER BY 1;
>>> --
>>> *Melvin Davidson*
>>> I reserve the right to fantasize.  Whether or not you
>>> wish to share my fantasy is entirely up to you.
>>>
>>
>
> *Igor,*
>
> *You do not need to specify "postgres" schema (postgres.pg_class). That is
> wrong anyway.*
>
> *FYI, the correct schema is pg_catalog, but  All postgres CATALOGS are
> always available regardless of which database you are connected to.*
>
> *and it is in the default search path, so you do not have to specify it.*
>
>
>
>
>
> *Just do:SELECT oid    FROM pg_class  WHERE relname = <my_table_name>;*
>
>
>
>
> *It will work just fine!  I highly encourage you to RTFM.*
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>
>
> [Attachment of type text/html removed.]
>
> ------------------------------
>
> Date: Thu, 13 Jul 2017 16:09:22 +0000
> From: "Zhu, Joshua" <jzhu@vormetric.com>
> To: Craig Ringer <craig@2ndquadrant.com>
> Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
> Subject: Re: BDR node removal and rejoin
> Message-ID: <f5ac30a5684548e5ba672810e528c80b@EXUSDAGORL01.INTERNAL.ROOT.TES>
>
>
>
> Found these log entries from one of the other node:
>
>
>
> t=2017-07-13 08:35:34 PDT p=27292 a=DEBUG:  00000: found valid replication identifier 15
>
> t=2017-07-13 08:35:34 PDT p=27292 a=LOCATION:  bdr_establish_connection_and_slot, bdr.c:604
>
> t=2017-07-13 08:35:34 PDT p=27292 a=ERROR:  53400: no free replication state could be found for 15, increase
max_replication_slots
>
>
>
> Increased max_replication_slots, things are looking good now, thanks.
>
>
>
> This does bring up a couple of questions:
>
>
>
>
>
>    1.  Given the fact there is no real increase in the number of nodes in this repeated removal/rejoining exercise,
yetit caused replication slots being used up, wouldn’t removal of a node also automatically free up the replication
slotallocated for the node? Or is there a way to manually free up no longer needed slots? (the don’t seem to show up
inpg_replication_slots view, I made sure to use pg_drop_replication_slot when they do show up there) 
>
>    2.  If there is such a thing, what is the rule of thumb for best value of max_replication_slots (are they somehow
relatedto the value max_wal_senders as well), with respect to, say, the max number of nodes intended to support? 
>
>
>
> Thanks
>
>
>
> From: Craig Ringer [mailto:craig@2ndquadrant.com]
>
> Sent: Wednesday, July 12, 2017 11:59 PM
>
> To: Zhu, Joshua <jzhu@thalesesec.net>
>
> Cc: pgsql-general@postgresql.org
>
> Subject: Re: [GENERAL] BDR node removal and rejoin
>
>
>
> On 13 July 2017 at 01:56, Zhu, Joshua <jzhu@vormetric.com<mailto:jzhu@vormetric.com>> wrote:
>
> Thanks for the clarification.
>
>
>
> Looks like I am running into a different issue: while trying to pin down precisely the steps (and the order in which
toperform them) needed to remove/rejoin a node, the removal/rejoining exercise was repeated a number of times, and
stuckagain: 
>
>
>
>
>
>    1.  The status of the re-joining node (node4) on other nodes is “I�
>
>    2.  The status of the re-joining node on the node4 itself started at “I�, changed to “o�, then stuck there
>
>    3.  From the log file for node4, the following entries are constantly being generated:
>
>
>
> 2017-07-12 10:37:46 PDT [24943:bdr (6334686800251932108,1,43865,):receive:::1(33883)]DEBUG:  00000: received
replicationcommand: IDENTIFY_SYSTEM 
>
> 2017-07-12 10:37:46 PDT [24943:bdr (6334686800251932108,1,43865,):receive:::1(33883)]LOCATION:
exec_replication_command,walsender.c:1309 
>
> 2017-07-12 10:37:46 PDT [24943:bdr (6334686800251932108,1,43865,):receive:::1(33883)]DEBUG:  08003: unexpected EOF on
clientconnection 
>
> 2017-07-12 10:37:46 PDT [24943:bdr (6334686800251932108,1,43865,):receive:::1(33883)]LOCATION:  SocketBackend,
postgres.c:355
>
> 2017-07-12 10:37:46 PDT [24944:bdr (6408408103171110238,1,24713,):receive:::1(33884)]DEBUG:  00000: received
replicationcommand: IDENTIFY_SYSTEM 
>
> 2017-07-12 10:37:46 PDT [24944:bdr (6408408103171110238,1,24713,):receive:::1(33884)]LOCATION:
exec_replication_command,walsender.c:1309 
>
> 2017-07-12 10:37:46 PDT [24944:bdr (6408408103171110238,1,24713,):receive:::1(33884)]DEBUG:  08003: unexpected EOF on
clientconnection 
>
>
>
> Check the logs on the other end.
>
>
>
>
>
>
>
> --
>
>   Craig Ringer                   http://www.2ndQuadrant.com/
>
>   PostgreSQL Development, 24x7 Support, Training & Services
>
>
>
> [Attachment of type text/html removed.]
>
> ------------------------------
>
> Date: Thu, 13 Jul 2017 12:10:20 -0400
> From: Melvin Davidson <melvin6925@gmail.com>
> To: Edmundo Robles <edmundo@sw-argos.com>
> Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
> Subject: Re: I can't cancel/terminate query.
> Message-ID: <CANu8FiyH_+Uu09G2ZcC2FaE+52nh3fB5fy7DiY0-nbKdvtEbUg@mail.gmail.com>
>
> On Thu, Jul 13, 2017 at 11:57 AM, Edmundo Robles <edmundo@sw-argos.com>
> wrote:
>
>> Hi!  i have  many  too long time queries,  the oldest  is  almost 16 days,
>> so i  tried to cancel and terminate with pg_cancel_backend and
>> pg_terminate_backend  but queries is still running.
>>
>> STIME     ELAPSED ELAPSED %CPU   PID COMMAND
>> jun27 15-23:05:46 1379146  0.3 29660 postgres: argos_admin bdkairos
>> 127.0.0.1(55605) SELECT
>> jun27 15-23:05:46 1379146  0.3 29659 postgres: argos_admin bdkairos
>> 127.0.0.1(55604) SELECT
>> jun27 15-23:05:46 1379146  0.3 29658 postgres: argos_admin bdkairos
>> 127.0.0.1(55603) SELECT
>> jun27 15-23:05:16 1379116  0.3 29909 postgres: argos_admin bdkairos
>> 127.0.0.1(55767) SELECT
>> jun27 15-23:04:51 1379091  0.3 30097 postgres: argos_admin bdkairos
>> 127.0.0.1(55909) SELECT
>> jun27 15-23:03:49 1379029  0.3 30661 postgres: argos_admin bdkairos
>> 127.0.0.1(56303) SELECT
>> ...
>> What should  i do to safely close that  queries, before to use kill
>> command in linux?
>>
>> I have Postgres 9.4  running on debian jessie.
>>
>>
>>
>> --
>>
>>
> T
>
> *o cancel a process with pg_terminate_backend, you need to be a superuser.*
> *So first;*
>
> *SET ROLE postgres;*
>
> Then you should be able to
> *SELECT **pg_terminate_backend(<pid>); *  Where <pid> is the pid of process
> you want to termnate.
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>
>
> [Attachment of type text/html removed.]
>
> ------------------------------
>
> End of [pgsql-general] Daily digest v1.15352 (7 messages)
> **********