Re: [GENERAL] BDR node removal and rejoin

[Craig Ringer]

On 14 July 2017 at 00:09, Zhu, Joshua  wrote:

>
>
> Found these log entries from one of the other node:
>
>
>
> t=2017-07-13 08:35:34 PDT p=27292 a=DEBUG:  0: 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, yet it caused replication slots
>being used up, wouldn’t removal of a node also automatically free up the
>replication slot allocated for the node?
>
>
Yes, it should. Open issue. A patch would be welcomed.



>
>1. Or is there a way to manually free up no longer needed slots? (the
>don’t seem to show up in pg_replication_slots view, I made sure to use
>pg_drop_replication_slot when they do show up there)
>
>
It'll be complaining about replication identifiers ("origins" in 9.6); see
pg_replication_identifier


>1. If there is such a thing, what is the rule of thumb for best value
>of max_replication_slots (are they somehow related to the value
>max_wal_senders as well), with respect to, say, the max number of nodes
>intended to support?
>
>
I think that's covered in the docs, but it's safe to err fairly high. The
cost of extra slots is minimal.


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

Re: [GENERAL] LDAP authentication without OU in ldapbasedn

[Jeff Janes]

On Thu, Jul 13, 2017 at 2:46 AM, Gregory Nicol  wrote:

> Good morning all,
>
>
>
> I can’t seem to get LDAP Authentication working without an OU in the
> ldapbasedn. My users are spread across multiple OUs without a common root
> OU which is why I’m trying to authenticate with just the DC.
>
>

Have you tried using the "simple bind mode" where you specify just the
ldapsuffix and the ldapserver?

Cheers,

Jeff

Re: [GENERAL] Event Trigger question

[ProPAAS DBA]

Oh duh, I'm blind... Thanks!


On 07/13/2017 07:29 PM, David G. Johnston wrote:
On Thursday, July 13, 2017, ProPAAS DBA > wrote:



2) where can I find a complete list of the tg_ variables? I see
this list:
https://www.postgresql.org/docs/9.4/static/plpgsql-trigger.html

which includes TG_NAME. OLD, NEW, etc but tg_tag and tg_event are
not in the list. Are there other variables I can reference?


They are listed at the bottom of the page you linked to.

David J.

Re: [GENERAL] Event Trigger question

[David G. Johnston]

On Thursday, July 13, 2017, ProPAAS DBA  wrote:

>
> 2) where can I find a complete list of the tg_ variables? I see this 
> list:https://www.postgresql.org/docs/9.4/static/plpgsql-trigger.html
>
> which includes TG_NAME. OLD, NEW, etc but tg_tag and tg_event are not in the 
> list. Are there other variables I can reference?
>
>
They are listed at the bottom of the page you linked to.

David J.

[GENERAL] Event Trigger question

[ProPAAS DBA]

Hi All;


we are creating an event trigger on ddl_command_end and I want the 
function to know the TABLE and COMMAND run, for example if the ddl 
command was an "ALTER TABLE ADD COLUMN X" then I want to pull the table 
and the actual alter command. We're running version 9.4 so the 
pg_event_trigger_ddl_commands function is not available.


I see in the 9.4 docs that the variable tg_tag is used, and I see some 
references to tg_event


I assume we can pass ALTER TABLE as the filter value like so:

CREATE OR REPLACE FUNCTION trap_alter_statements() RETURNS event_trigger 
LANGUAGE plpgsql AS $$ BEGIN RAISE EXCEPTION 'command %', tg_tag; END; 
$$; CREATE EVENT TRIGGER abort_ddl ON ddl_command_start WHEN TAG IN 
'ALTER TABLE'  EXECUTE PROCEDURE trap_alter_statements(); Questions: 1) 
is the above "WHEN TAG IN 'ALTER TABLE'" correct? 2) where can I find a 
complete list of the tg_ variables? I see this list: 
https://www.postgresql.org/docs/9.4/static/plpgsql-trigger.html which 
includes TG_NAME. OLD, NEW, etc but tg_tag and tg_event are not in the 
list. Are there other variables I can reference? 3) which specific 
variable will show me (a) the full command run and (b) the table name? 
Thanks in advance

Re: [GENERAL] LDAP authentication without OU in ldapbasedn

[Stephen Frost]

Greetings,

* Gregory Nicol (gregory.ni...@medbank.com.mt) wrote:
> I can't seem to get LDAP Authentication working without an OU in the 
> ldapbasedn. My users are spread across multiple OUs without a common root OU 
> which is why I'm trying to authenticate with just the DC.

As it looks like you're working in a Microsoft Windows AD environment,
I'd strongly suggest you consider using Kerberos/GSS authentication
instead of LDAP.  With LDAP, the user has to constantly re-type their
password and the password is sent to the PostgreSQL server.  Neither of
these are ideal and both are avoided by simply using Kerberos, which is
what AD uses.

Authentication using LDAP really shouldn't ever be done in an
environment which has Active Directory.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Associating a basebackup and it's .backup WAL file

[Stephen Frost]

Greetings,

* cen (imba...@gmail.com) wrote:
> Seems like a lot of manual work to me, to automate it I'd basically
> have to diff both directories and then copy only the newest
> differences over to the recovery. So far I was unable to find a
> supersecret git repo with bash scripts accomplishing this tasks
> which is surprising.

I would hazard to suggest that's because people have realized that bash
scripting backup and recovery of PostgreSQL, and ensuring that all of
the appropriate precautions are taken and checks are done, is next to
impossible; were it to be done the scripts would be more complex and
challenging to use than any of the existing solutions.

I'd strongly suggest you consider one of the maintained backup solutions
that have already been written instead of inventing yet another one.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Associating a basebackup and it's .backup WAL file

[cen]

That is really unfortunate. It seems it would be a nice feature for 
pg_basebackup to simply create a .metadata file in basebackup output 
directory or something along those lines.


Non tarballed/compressed basebackup is fine since I can read the label, 
but most people probably want to always compress. I'll probably try to 
get the WAL file by getting last modified .backup right after basebackup 
invoke.



I also found another "automation" problem looking at PITR recovery 
documentation, specifically:


"If you have unarchived WAL segment files that you saved in step 2, copy 
them into pg_xlog/. (It is best to copy them, not move them, so you 
still have the unmodified files if a problem occurs and you have to 
start over.)"


Seems like a lot of manual work to me, to automate it I'd basically have 
to diff both directories and then copy only the newest differences over 
to the recovery. So far I was unable to find a supersecret git repo with 
bash scripts accomplishing this tasks which is surprising.



On 07/13/2017 11:26 AM, Michael Paquier wrote:

On Thu, Jul 13, 2017 at 10:30 AM, cen  wrote:

Given a basebackup base.tar.gz and an archive of WAL files, is there any way
to find out which .backup WAL file is associated with the basebackup from
command line?

Not from what Postgres ships directly. Without any custom meta data
save with each one of your backups, say something that you write after
calling pg_basebackup, you would need to untar base.tar to look for
the backup_label file.


My use case is for a retention policy bash script which:
-deletes all basebackups older than X days
-runs pg_archivecleanup for the oldest basebackup

I just don't know how to find out which WAL to feed to pg_archivecleanup at
this point.

Recalling something I know about, pg_rman uses its own meta data to do
this decision making with dedicated folder names that use a structure
and names based on timestamps, and this meta data is written and saved
when each backup is taken. This saves future lookups at all tarballs
when doing cleanup of past backups.

I am not sure about the more popular barman and pgBackrest since I
know them less, but I would imagine they handle retention policies
similarly.

Re: [GENERAL] Regarding creating pgpointcloud extension.

[Nandish Jayaram]

Thank you Tom. I have already started running queries from the
corresponding extension. Let's see how far I get! :)

Regards,
Nandish

On Thu, Jul 13, 2017 at 1:03 PM, Tom Lane  wrote:

> Nandish Jayaram  writes:
> > I have been trying to get pgpointcloud running on greenplum 5.0 database.
> > This might seem like a convoluted topic to discuss on this mailing list,
> but
> > the reason I am asking here is that Greenplum 5 is based on postgres 8.4
> > and pgpointcloud requires at least postgres 9.0 it seems.
>
> [ scratches head... ]  The whole concept of extensions came in with PG 9.1.
> So unless Greenplum cherry-picked that feature from later, I don't
> understand how you didn't get something more like this:
>
> =# create extension foo;
> ERROR:  syntax error at or near "extension"
> LINE 1: create extension foo;
>^
>
> > So I am wondering:
> > how does one figure out which postgres 9.0 features in particular does
> > pgpointcloud depend on?
>
> You could try feeding the extension's SQL file directly to psql (in a
> scratch database so you can clean up easily).  If, as is considered
> good practice, it starts with something like this:
>
> -- complain if script is sourced in psql, rather than via CREATE EXTENSION
> \echo Use "CREATE EXTENSION cube" to load this file. \quit
>
> then you'll need to delete that; but the rest of it should be regular
> SQL, and you can see what's failing.
>
> regards, tom lane
>

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

[Alvaro Herrera]

--- Begin Message ---



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-ow...@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 
To: pgsql-general@postgresql.org
Subject: Re: Systemd support (was:Re: Please say it isn't so)
Message-ID: 

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-gene...@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 
To: Vick Khera 
Cc: pgsql-general@postgresql.org
Subject: Re: Systemd support (was:Re: Please say it isn't so)
Message-ID: <17764.1499959...@sss.pgh.pa.us>

Vick Khera  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 
To: dpat 
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 

Re: [GENERAL] Regarding creating pgpointcloud extension.

[Tom Lane]

Nandish Jayaram  writes:
> I have been trying to get pgpointcloud running on greenplum 5.0 database.
> This might seem like a convoluted topic to discuss on this mailing list, but
> the reason I am asking here is that Greenplum 5 is based on postgres 8.4
> and pgpointcloud requires at least postgres 9.0 it seems.

[ scratches head... ]  The whole concept of extensions came in with PG 9.1.
So unless Greenplum cherry-picked that feature from later, I don't
understand how you didn't get something more like this:

=# create extension foo;
ERROR:  syntax error at or near "extension"
LINE 1: create extension foo;
   ^

> So I am wondering:
> how does one figure out which postgres 9.0 features in particular does
> pgpointcloud depend on?

You could try feeding the extension's SQL file directly to psql (in a
scratch database so you can clean up easily).  If, as is considered
good practice, it starts with something like this:

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION cube" to load this file. \quit

then you'll need to delete that; but the rest of it should be regular
SQL, and you can see what's failing.

regards, tom lane


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

Re: [GENERAL] I can't cancel/terminate query.

[Melvin Davidson]

On Thu, Jul 13, 2017 at 2:45 PM, Edmundo Robles 
wrote:

> i  executed the commands   many times like  superuser but  that queries
> still running :(
>
> On Thu, Jul 13, 2017 at 11:10 AM, Melvin Davidson 
> wrote:
>
>>
>>
>> On Thu, Jul 13, 2017 at 11:57 AM, Edmundo Robles 
>> 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(); *  Where  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.
>>
>
>
>
> --
>
>



*If you are running on Linux, then:*

*sudo su*

*kill  *

*Where  is the same as in pg_terminate_backend() *

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] Regarding creating pgpointcloud extension.

[Nandish Jayaram]

Hi,

I have been trying to get pgpointcloud running on greenplum 5.0 database.
This might seem like a convoluted topic to discuss on this mailing list, but
the reason I am asking here is that Greenplum 5 is based on postgres 8.4
and pgpointcloud requires at least postgres 9.0 it seems. So I am wondering:
how does one figure out which postgres 9.0 features in particular does
pgpointcloud depend on? Is there any not-so-difficult way of figuring out
those differences/requirements?

If this is not the correct mailing list for this question, can you kindly
direct me
to the right one?

The error I get on Greenplum 5.0 is:

create extension pointcloud;
*ERROR:  syntax error at or near "NOT"*

Thanks in advance,
Nandish

Re: [GENERAL] Monitoring of a hot standby with a largely idle master

[Michael Paquier]

On Thu, Jul 13, 2017 at 7:23 PM, Jeff Janes  wrote:
> On Thu, Jul 13, 2017 at 1:15 AM, Michael Paquier 
> wrote:
>>
>> On Thu, Jul 13, 2017 at 5:26 AM, Jeff Janes  wrote:
>> >
>> > I think that none of the recovery information functions
>> >
>> > (https://www.postgresql.org/docs/9.6/static/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE)
>> > can distinguish a hot standby which is connected to an idle master,
>> > versus
>> > one which is disconnected.  For example, because the master has crashed,
>> > or
>> > someone has changed the firewall rules.
>> >
>> > Is there a way to monitor from SQL the last time the standby was able to
>> > contact the master and initiate streaming with it?  Other than trying to
>> > write a function that parses it out of pg_log?
>>
>> Not directly I am afraid. One way I can think about is to poll
>> periodically the state of pg_stat_replication on the primary or
>> pg_stat_wal_receiver on the standby and save it in a custom table. The
>> past information is not persistent as any replication-related data in
>> catalogs is based on the shared memory state of the WAL senders and
>> the WAL receiver, and those are wiped out at reconnection.
>
>
> Thanks, that looks like what I want (or will be, once I get the other side
> to upgrade to 9.6).
>
> I think that pg_stat_wal_receiver should be crossreferenced in
> https://www.postgresql.org/docs/9.6/static/hot-standby.html, near the same
> place which it crossreferences table 9-79.  That would make it more
> discoverable.

Hm. Hot standby may not involve streaming replication. What about a
paragraph here instead?
https://www.postgresql.org/docs/devel/static/warm-standby.html#streaming-replication

In the monitoring subsection, we could tell that on a standby the WAL
receiver status can be retrieved from this view when changes are
streamed. What do you think?
-- 
Michael


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

Re: [GENERAL] Monitoring of a hot standby with a largely idle master

[Jeff Janes]

On Thu, Jul 13, 2017 at 1:15 AM, Michael Paquier 
wrote:

> On Thu, Jul 13, 2017 at 5:26 AM, Jeff Janes  wrote:
> >
> > I think that none of the recovery information functions
> > (https://www.postgresql.org/docs/9.6/static/functions-admin.
> html#FUNCTIONS-RECOVERY-INFO-TABLE)
> > can distinguish a hot standby which is connected to an idle master,
> versus
> > one which is disconnected.  For example, because the master has crashed,
> or
> > someone has changed the firewall rules.
> >
> > Is there a way to monitor from SQL the last time the standby was able to
> > contact the master and initiate streaming with it?  Other than trying to
> > write a function that parses it out of pg_log?
>
> Not directly I am afraid. One way I can think about is to poll
> periodically the state of pg_stat_replication on the primary or
> pg_stat_wal_receiver on the standby and save it in a custom table. The
> past information is not persistent as any replication-related data in
> catalogs is based on the shared memory state of the WAL senders and
> the WAL receiver, and those are wiped out at reconnection.
>

Thanks, that looks like what I want (or will be, once I get the other side
to upgrade to 9.6).

I think that pg_stat_wal_receiver should be crossreferenced in
https://www.postgresql.org/docs/9.6/static/hot-standby.html, near the same
place which it crossreferences table 9-79.  That would make it more
discoverable.

Cheers,

Jeff

Re: [GENERAL] I can't cancel/terminate query.

[Jerry Sievers]

Edmundo Robles  writes:

> 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.

Try stracing the catatonic backend.  You will probably find it  hung in
a network send like sendv' or somesuch.

The client side of such a connection is probably still alive but can't
take any more data perhaps due to being  resource starved.  Fully
shutdown such a client if you can locate it.

If this is a no-can-do, there's a way to forge a network packet to
close the blocked socket which you'll have to Google for  if interested.

Else you will have to shutdown -m immediate.  Sorry.

>  
>
>
> --
> [uc]
>
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


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

Re: [GENERAL] I can't cancel/terminate query.

[Melvin Davidson]

On Thu, Jul 13, 2017 at 11:57 AM, Edmundo Robles 
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(); *  Where  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.

Re: [GENERAL] BDR node removal and rejoin

[Zhu, Joshua]

Found these log entries from one of the other node:

t=2017-07-13 08:35:34 PDT p=27292 a=DEBUG:  0: 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, yet it caused replication slots being used 
up, wouldn’t removal of a node also automatically free up the replication slot 
allocated for the node? Or is there a way to manually free up no longer needed 
slots? (the don’t seem to show up in pg_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 related to the value max_wal_senders as 
well), with respect to, say, the max number of nodes intended to support?

Thanks

From: Craig Ringer [mailto:cr...@2ndquadrant.com]
Sent: Wednesday, July 12, 2017 11:59 PM
To: Zhu, Joshua 
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] BDR node removal and rejoin

On 13 July 2017 at 01:56, Zhu, Joshua 
> 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 to perform them) needed to 
remove/rejoin a node, the removal/rejoining exercise was repeated a number of 
times, and stuck again:


  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:  0: received 
replication command: 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 client connection
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:  0: received 
replication command: 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 client connection

Check the logs on the other end.



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

Re: [GENERAL] Get table OID

[Melvin Davidson]

On Thu, Jul 13, 2017 at 11:06 AM, Igor Korot  wrote:

> Hi, Melvin,
>
> On Thu, Jul 13, 2017 at 10:42 AM, Melvin Davidson 
> wrote:
>
>>
>> On Thu, Jul 13, 2017 at 10:36 AM, Igor Korot  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 = ;
>
> 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 oidFROM pg_class  WHERE relname = ;*




*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.

[GENERAL] I can't cancel/terminate query.

[Edmundo Robles]

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.



--

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

[Alvaro Herrera]

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 Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

Re: Systemd support (was:Re: [GENERAL] Please say it isn't so)

[Tom Lane]

Vick Khera  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


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

Re: Systemd support (was:Re: [GENERAL] Please say it isn't so)

[Vick Khera]

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-gene...@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
>

Re: [GENERAL] Long wrapped header lines in psql with expanded mode (9.6.3)

[Simon Ruderich]

On Thu, Jul 13, 2017 at 04:05:43PM +0200, Pavel Stehule wrote:
> what about \pset format wrapped

I prefer the extended format which I find more pleasant to read.

I'm just curious if this expected behavior or a bug / weird
effect of my setup.

If it's considered a bug I'd by willing to try to come up with a
patch (I already have a hacky version running on my local
machine which fixes it for my use case).

Regards
Simon
-- 
+ privacy is necessary
+ using gnupg http://gnupg.org
+ public key id: 0x92FEFDB7E44C32F9


signature.asc
Description: PGP signature

[GENERAL] Get table OID

[Igor Korot]

 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

Re: [GENERAL] Long wrapped header lines in psql with expanded mode (9.6.3)

[Pavel Stehule]

2017-07-13 14:40 GMT+02:00 Simon Ruderich :

> Hello,
>
> I'm using the following minimal ~/.psqlrc:
>
> \pset expanded on
>
> Now when I select rows from a table which are too long to fit the
> screen then I get this output:
>
> simon=> table test;
> -[ RECORD 1 ]---
> 
> 
> -
> test | a
> 
> 
> a
> -[ RECORD 2 ]---
> 
> 
> -
> test | a
> 
> 
> a
>
> Is there any way to remove the superfluous header lines? I'd like
> to get the following output which doesn't waste so much screen
> space:
>
> simon=> table test;
> -[ RECORD 1 ]---
> test | a
> 
> 
> a
> -[ RECORD 2 ]---
> test | a
> 
> 
> a
>
> Regards
> Simon
>

what about \pset format wrapped

Regards

Pavel


> --
> + privacy is necessary
> + using gnupg http://gnupg.org
> + public key id: 0x92FEFDB7E44C32F9
>

Re: [GENERAL] Changing collate & ctype for an existing database

[rihad]

On 07/12/2017 11:25 PM, Tom Lane wrote:

rihad  writes:

What if only English letters are used in the textual indices (ascii
0-127), would they still be impacted after datctype
"C"->"en_US.UTF-8" change?

Yes, as even minimal testing would have told you.  C sort order is
more case-sensitive, for instance.

regards, tom lane
.

Btw, can we wrap the update of datcollate  and rebuilding of 
textual indices inside a transaction with effectively 0 downtime?




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

[GENERAL] Long wrapped header lines in psql with expanded mode (9.6.3)

[Simon Ruderich]

Hello,

I'm using the following minimal ~/.psqlrc:

\pset expanded on

Now when I select rows from a table which are too long to fit the
screen then I get this output:

simon=> table test;
-[ RECORD 1 ]---


-
test | a


a
-[ RECORD 2 ]---


-
test | a


a

Is there any way to remove the superfluous header lines? I'd like
to get the following output which doesn't waste so much screen
space:

simon=> table test;
-[ RECORD 1 ]---
test | a


a
-[ RECORD 2 ]---
test | a


a

Regards
Simon
-- 
+ privacy is necessary
+ using gnupg http://gnupg.org
+ public key id: 0x92FEFDB7E44C32F9


signature.asc
Description: PGP signature

[GENERAL] LDAP authentication without OU in ldapbasedn

[Gregory Nicol]

Good morning all,

I can't seem to get LDAP Authentication working without an OU in the 
ldapbasedn. My users are spread across multiple OUs without a common root OU 
which is why I'm trying to authenticate with just the DC.


With pg_hba.conf like this, I can connect successfully from psql...

local all allldap ldapserver=192.168.xxx.xxx 
ldapbasedn="OU=DB_Accounts,DC=acme,DC=local" ldapbinddn="CN= 
,OU=DB_Accounts,DC=acme,DC=local" ldapsearchattribute=sAMAccountName 
ldapbindpasswd=xxx

But if I remove the OU from the ldapbasedn, like this ...

local all allldap ldapserver=192.168.xxx.xxx 
ldapbasedn="DC=acme,DC=local" ldapbinddn="CN= 
,OU=DB_Accounts,DC=acme,DC=local" ldapsearchattribute=sAMAccountName 
ldapbindpasswd=xxx


Then I get:

psql: FATAL:  LDAP authentication failed for user ""


select VERSION();
 version
--
PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 
(Red Hat 4.8.5-11), 64-bit


psql --version
psql (PostgreSQL) 9.6.2


Kind Regards,
Greg



The information transmitted is intended only for the person or entity to which 
it is addressed and may contain confidential and / or privileged material. Any 
review, retransmission, dissemination or other use of, or taking of any action 
in reliance upon, this information by persons or entities other than the 
intended recipient is prohibited. If you received this in error, please contact 
the sender and delete the material from any computer.

Re: [GENERAL] Associating a basebackup and it's .backup WAL file

[Michael Paquier]

On Thu, Jul 13, 2017 at 10:30 AM, cen  wrote:
> Given a basebackup base.tar.gz and an archive of WAL files, is there any way
> to find out which .backup WAL file is associated with the basebackup from
> command line?

Not from what Postgres ships directly. Without any custom meta data
save with each one of your backups, say something that you write after
calling pg_basebackup, you would need to untar base.tar to look for
the backup_label file.

> My use case is for a retention policy bash script which:
> -deletes all basebackups older than X days
> -runs pg_archivecleanup for the oldest basebackup
>
> I just don't know how to find out which WAL to feed to pg_archivecleanup at
> this point.

Recalling something I know about, pg_rman uses its own meta data to do
this decision making with dedicated folder names that use a structure
and names based on timestamps, and this meta data is written and saved
when each backup is taken. This saves future lookups at all tarballs
when doing cleanup of past backups.

I am not sure about the more popular barman and pgBackrest since I
know them less, but I would imagine they handle retention policies
similarly.
-- 
Michael


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

[GENERAL] Associating a basebackup and it's .backup WAL file

[cen]

Hi

Given a basebackup base.tar.gz and an archive of WAL files, is there any 
way to find out which .backup WAL file is associated with the basebackup 
from command line?


My use case is for a retention policy bash script which:

-deletes all basebackups older than X days

-runs pg_archivecleanup for the oldest basebackup

I just don't know how to find out which WAL to feed to pg_archivecleanup 
at this point.




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

Re: [GENERAL] Monitoring of a hot standby with a largely idle master

[Michael Paquier]

On Thu, Jul 13, 2017 at 5:26 AM, Jeff Janes  wrote:
>
> I think that none of the recovery information functions
> (https://www.postgresql.org/docs/9.6/static/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE)
> can distinguish a hot standby which is connected to an idle master, versus
> one which is disconnected.  For example, because the master has crashed, or
> someone has changed the firewall rules.
>
> Is there a way to monitor from SQL the last time the standby was able to
> contact the master and initiate streaming with it?  Other than trying to
> write a function that parses it out of pg_log?

Not directly I am afraid. One way I can think about is to poll
periodically the state of pg_stat_replication on the primary or
pg_stat_wal_receiver on the standby and save it in a custom table. The
past information is not persistent as any replication-related data in
catalogs is based on the shared memory state of the WAL senders and
the WAL receiver, and those are wiped out at reconnection.
-- 
Michael


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

Re: [GENERAL] BDR node removal and rejoin

[Craig Ringer]

On 13 July 2017 at 01:56, Zhu, Joshua  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 to perform them) needed to
> remove/rejoin a node, the removal/rejoining exercise was repeated a number
> of times, and stuck again:
>
>
>
>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:
> 0: received replication command: 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 client connection
>
> 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:
> 0: received replication command: 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 client connection
>

Check the logs on the other end.



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