Re: Resync second slave to new master

[Yavuz Selim Sertoğlu]

If not set, could you add recovery.conf file
recovery_target_timeline='latest'
parameter?
https://www.postgresql.org/docs/devel/static/recovery-target-settings.html


2018-03-08 10:41 GMT+03:00 Dylan Luong :

> Hi Michael,
>
> I tested the failover today and the slave 2 failed to resync with the new
> master (old slave1).
>
> After I promoted the slave1 to become master,  I was able to use pg_rewind
> on the old master and bring it back as new slave.
>
> I then stopped slave2 and ran pg_rewind on slave2 against new master, it
> report that no rewind was required:
>
>   $ pg_rewind -D /var/lib/pgsql/9.6/data 
> --source-server="host=x.xxx.
> port=5432 user=postgres"
>   servers diverged at WAL position 1BB/AB98 on timeline 5
>   no rewind required
>
> So I then updated the recovery.conf on slave2 with primary_conninfo equal
> to the new master IP.
> When starting up posgres, it failed with the following error in the logs:
>
> database system was shut down in recovery at 2018-03-08 17:52:10 ACDT
> 2018-03-08 17:56:27 ACDT [23026]: [2-1] db=,user= app=,host= LOG:
> entering standby mode
> cp: cannot stat '/pg_backup/backup/archive /0005.history': No such
> file or directory
> cp: cannot stat '/pg_backup/backup/archive /000501BB00AB': No
> such file or directory
> 2018-03-08 17:56:27 ACDT [23026]: [3-1] db=,user= app=,host= LOG:
> consistent recovery state reached at 1BB/AB98
> 2018-03-08 17:56:27 ACDT [23026]: [4-1] db=,user= app=,host= LOG:  record
> with incorrect prev-link 1B9/7340 at 1BB/AB98
> 2018-03-08 17:56:27 ACDT [23024]: [3-1] db=,user= app=,host= LOG:
> database system is ready to accept read only connections
> 2018-03-08 17:56:27 ACDT [23032]: [1-1] db=,user= app=,host= LOG:  started
> streaming WAL from primary at 1BB/AB00 on timeline 5
> 2018-03-08 17:56:27 ACDT [23032]: [2-1] db=,user= app=,host= LOG:
> replication terminated by primary server
> 2018-03-08 17:56:27 ACDT [23032]: [3-1] db=,user= app=,host= DETAIL:  End
> of WAL reached on timeline 5 at 1BB/AB98.
> cp: cannot stat '/pg_backup/backup/archive_sync/000501BB00AB':
> No such file or directory
> 2018-03-08 17:56:27 ACDT [23032]: [4-1] db=,user= app=,host= LOG:
> restarted WAL streaming at 1BB/AB00 on timeline 5
> 2018-03-08 17:56:27 ACDT [23032]: [5-1] db=,user= app=,host= LOG:
> replication terminated by primary server
> 2018-03-08 17:56:27 ACDT [23032]: [6-1] db=,user= app=,host= DETAIL:  End
> of WAL reached on timeline 5 at 1BB/AB98.
>
>
> On the new master in the /pg_backup/backup/archive folder I can see a file
> 000501BB00AB.partial
> Eg.
> ls -l
> -rw---. 1 postgres postgres 16777216 Mar  8 16:48
> 000501BB00AB.partial
> -rw---. 1 postgres postgres 16777216 Mar  8 16:49
> 000601BB00AB
> -rw---. 1 postgres postgres 16777216 Mar  8 16:49
> 000601BB00AC
> -rw---. 1 postgres postgres 16777216 Mar  8 16:49
> 000601BB00AD
> -rw---. 1 postgres postgres 16777216 Mar  8 16:49
> 000601BB00AE
> -rw---. 1 postgres postgres 16777216 Mar  8 16:49
> 000601BB00AF
> -rw---. 1 postgres postgres 16777216 Mar  8 16:49
> 000601BB00B0
> -rw---. 1 postgres postgres 16777216 Mar  8 16:49
> 000601BB00B1
> -rw---. 1 postgres postgres 16777216 Mar  8 16:49
> 000601BB00B2
> -rw---. 1 postgres postgres 16777216 Mar  8 16:50
> 000601BB00B3
> -rw---. 1 postgres postgres 16777216 Mar  8 17:01
> 000601BB00B4
> -rw---. 1 postgres postgres 16777216 Mar  8 17:14
> 000601BB00B5
> -rw---. 1 postgres postgres  218 Mar  8 16:48 0006.history
>
> Any ideas?
>
> Dylan
>
> -Original Message-
> From: Michael Paquier [mailto:mich...@paquier.xyz]
> Sent: Tuesday, 6 March 2018 5:55 PM
> To: Dylan Luong 
> Cc: pgsql-generallists.postgresql.org 
> Subject: Re: Resync second slave to new master
>
> On Tue, Mar 06, 2018 at 06:00:40AM +, Dylan Luong wrote:
> > So everytime after promoting Slave to become master (either manually
> > or automatic), just stop Slave2 and run pg_rewind on slave2 against
> > the new maser (old slave1). And when old master server is available
> > again, use pg_rewind on that serve as well against new master to
> > return to original configuration.
>
> Yes.  That's exactly the idea.  Running pg_rewind on the old master will
> be necessary anyway because you need to stop it cleanly once, which will
> cause it to generate WAL records at least for the shutdown checkpoint,
> while doing it on slave 2 may be optional, still safer to do.
> --
> Michael
>
>

RE: Resync second slave to new master

[Dylan Luong]

Hi Michael,

I tested the failover today and the slave 2 failed to resync with the new 
master (old slave1).

After I promoted the slave1 to become master,  I was able to use pg_rewind on 
the old master and bring it back as new slave.

I then stopped slave2 and ran pg_rewind on slave2 against new master, it report 
that no rewind was required:

  $ pg_rewind -D /var/lib/pgsql/9.6/data 
--source-server="host=x.xxx. port=5432 user=postgres"
  servers diverged at WAL position 1BB/AB98 on timeline 5
  no rewind required

So I then updated the recovery.conf on slave2 with primary_conninfo equal to 
the new master IP.
When starting up posgres, it failed with the following error in the logs:

database system was shut down in recovery at 2018-03-08 17:52:10 ACDT
2018-03-08 17:56:27 ACDT [23026]: [2-1] db=,user= app=,host= LOG:  entering 
standby mode
cp: cannot stat '/pg_backup/backup/archive /0005.history': No such file or 
directory
cp: cannot stat '/pg_backup/backup/archive /000501BB00AB': No such 
file or directory
2018-03-08 17:56:27 ACDT [23026]: [3-1] db=,user= app=,host= LOG:  consistent 
recovery state reached at 1BB/AB98
2018-03-08 17:56:27 ACDT [23026]: [4-1] db=,user= app=,host= LOG:  record with 
incorrect prev-link 1B9/7340 at 1BB/AB98
2018-03-08 17:56:27 ACDT [23024]: [3-1] db=,user= app=,host= LOG:  database 
system is ready to accept read only connections
2018-03-08 17:56:27 ACDT [23032]: [1-1] db=,user= app=,host= LOG:  started 
streaming WAL from primary at 1BB/AB00 on timeline 5
2018-03-08 17:56:27 ACDT [23032]: [2-1] db=,user= app=,host= LOG:  replication 
terminated by primary server
2018-03-08 17:56:27 ACDT [23032]: [3-1] db=,user= app=,host= DETAIL:  End of 
WAL reached on timeline 5 at 1BB/AB98.
cp: cannot stat '/pg_backup/backup/archive_sync/000501BB00AB': No 
such file or directory
2018-03-08 17:56:27 ACDT [23032]: [4-1] db=,user= app=,host= LOG:  restarted 
WAL streaming at 1BB/AB00 on timeline 5
2018-03-08 17:56:27 ACDT [23032]: [5-1] db=,user= app=,host= LOG:  replication 
terminated by primary server
2018-03-08 17:56:27 ACDT [23032]: [6-1] db=,user= app=,host= DETAIL:  End of 
WAL reached on timeline 5 at 1BB/AB98.


On the new master in the /pg_backup/backup/archive folder I can see a file 
000501BB00AB.partial
Eg.
ls -l
-rw---. 1 postgres postgres 16777216 Mar  8 16:48 
000501BB00AB.partial
-rw---. 1 postgres postgres 16777216 Mar  8 16:49 000601BB00AB
-rw---. 1 postgres postgres 16777216 Mar  8 16:49 000601BB00AC
-rw---. 1 postgres postgres 16777216 Mar  8 16:49 000601BB00AD
-rw---. 1 postgres postgres 16777216 Mar  8 16:49 000601BB00AE
-rw---. 1 postgres postgres 16777216 Mar  8 16:49 000601BB00AF
-rw---. 1 postgres postgres 16777216 Mar  8 16:49 000601BB00B0
-rw---. 1 postgres postgres 16777216 Mar  8 16:49 000601BB00B1
-rw---. 1 postgres postgres 16777216 Mar  8 16:49 000601BB00B2
-rw---. 1 postgres postgres 16777216 Mar  8 16:50 000601BB00B3
-rw---. 1 postgres postgres 16777216 Mar  8 17:01 000601BB00B4
-rw---. 1 postgres postgres 16777216 Mar  8 17:14 000601BB00B5
-rw---. 1 postgres postgres  218 Mar  8 16:48 0006.history

Any ideas?

Dylan

-Original Message-
From: Michael Paquier [mailto:mich...@paquier.xyz] 
Sent: Tuesday, 6 March 2018 5:55 PM
To: Dylan Luong 
Cc: pgsql-generallists.postgresql.org 
Subject: Re: Resync second slave to new master

On Tue, Mar 06, 2018 at 06:00:40AM +, Dylan Luong wrote:
> So everytime after promoting Slave to become master (either manually
> or automatic), just stop Slave2 and run pg_rewind on slave2 against
> the new maser (old slave1). And when old master server is available
> again, use pg_rewind on that serve as well against new master to
> return to original configuration.

Yes.  That's exactly the idea.  Running pg_rewind on the old master will
be necessary anyway because you need to stop it cleanly once, which will
cause it to generate WAL records at least for the shutdown checkpoint,
while doing it on slave 2 may be optional, still safer to do.
--
Michael

Re: Prefixing schema name

[Achilleas Mantzios]

On 08/03/2018 01:13, David G. Johnston wrote:

On Wed, Mar 7, 2018 at 4:05 PM, Tiffany Thang >wrote:

​
The search_path configuration works only for queries.


​Um

​https://www.postgresql.org/docs/10/static/sql-createschema.html

"​A CREATE command specifying an unqualified object name creates the object in the 
current schema (the one at the front of the search path, which can be determined with the 
function current_schema)"



In pg_dump relies heavily on search_path for all CREATE statements.



For example:
Is there a way to run the create/insert statements below without prefixing 
the schema name, user1?​


As the user, user1:
Create table user1.table1 (id int);
Insert into user1.table1 values (1);


​Just omitting "user1" and seeing what happens would be informative.  You should find it 
does exactly what you think - namely because the default search_path will cause "user1" 
to appear first.

Insert is more similar to Select than it is to Create - the object being 
inserted into must already exist

David J.
​​



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Replication push instead of pull

[PegoraroF10]

We have several servers, our masters, installed on several cities. Then we 
need have a centralized database where we can do our reports. 

So, postgres publication/subscription model always opens Master server to
Slave gets its data..

There is a way to push data from master to slave instead of pulling data
from Master to Slave ?





--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Timescale with replication advice

[geoff hoffman]

I was wondering if any list members have experience setting up TimeScale with 
streaming replication. 

http://www.timescale.com/ 

I plan to use a collection of 2-5 EC2 instances in AWS and want to set up 1 
master -> N slaves to allow read-only connection pooling on the slaves, single 
master write.

Any advice, gotchas and recommendations before I begin would be most 
appreciated.

Thanks,
Geoff

Re: cached plan must not change result type

[David G. Johnston]

On Wed, Mar 7, 2018 at 4:06 PM, Jan Bilek  wrote:

> I would like to ask, would you see this solution in general as fine, or is
> there any better way to achieve this? I particularly dislike the part when
> we are trying matching return string on "cached plan must not change result
> type" as error messages might change (e.g. situations using different
> locales), is there any way how to get around this e.g. with enumerator?
>

Source code says that the errcode being returned is "Feature Not Supported"
which seems unreasonably broad​, I'd probably stick with message text
parsing.  If you have locale concerns you could just check for one of the
11 different translations that are presently provided.  Or combine them,
checking for the error code value first (which probably would be rare
enough in production code to be usable) then fall back to the description.

David J.

Re: Prefixing schema name

[David G. Johnston]

On Wed, Mar 7, 2018 at 4:05 PM, Tiffany Thang 
wrote:

> ​
> The search_path configuration works only for queries.
>

​Um

​https://www.postgresql.org/docs/10/static/sql-createschema.html

"​A CREATE command specifying an unqualified object name creates the object
in the current schema (the one at the front of the search path, which can
be determined with the function current_schema)"


> For example:
> Is there a way to run the create/insert statements below without prefixing
> the schema name, user1?​
>

> As the user, user1:
> Create table user1.table1 (id int);
> Insert into user1.table1 values (1);
>

​Just omitting "user1" and seeing what happens would be informative.  You
should find it does exactly what you think - namely because the default
search_path will cause "user1" to appear first.

Insert is more similar to Select than it is to Create - the object being
inserted into must already exist

David J.
​​

cached plan must not change result type

[Jan Bilek]

Hi all,

Our client noticed a problem which occurred so far twice, but might be having 
quite significant impact on our application processing in production: 
ResStatus: PGRES_FATAL_ERROR transaction. ErrorMessage: ERROR: cached plan must 
not change result type.

Reading through the documentation problem is quite understood on our side, 
while most probably caused by our implementation of Virtual Partitioning on 
postgresql, by triggering database schema update through the application 
upgrade procedure or simply by altering one of the tables manually - so far so 
good.

Now, our developers came with a solution where we check return value from 
PQresultStatus(x) for PGRES_FATAL_ERROR and then if the error message from 
PQresultErrorMessage(x) contains "cached plan must not change result type" we 
delete statement and prepare it again.

I would like to ask, would you see this solution in general as fine, or is 
there any better way to achieve this? I particularly dislike the part when we 
are trying matching return string on "cached plan must not change result type" 
as error messages might change (e.g. situations using different locales), is 
there any way how to get around this e.g. with enumerator?

Thank you,
Jan

Prefixing schema name

[Tiffany Thang]

Hi,
Would it be possible to create a table or index in a schema without
prefixing the schema name? I find it a hassle to keep specifying the schema
name when I create an object or performing DMLs in a schema. The
search_path configuration works only for queries.

For example:
Is there a way to run the create/insert statements below without prefixing
the schema name, user1?

As the user, user1:
Create table user1.table1 (id int);
Insert into user1.table1 values (1);

Thanks.

Re: Help troubleshooting SubtransControlLock problems

[Alvaro Herrera]

Scott Frazer wrote:

> Currently this seems to be happening when the server just starts taking
> connections. It doesn't even need time to scale up anymore (previously it
> took about 8 hours for the problem to re-appear)
> 
> It's only happening on the read replicas, though. I've just set my master
> to handle all the traffic, but that's not really sustainable

I failed to notice at start of thread that these were replicas.  I
suppose then you would have to be thinking about the range of XIDs in
the primary server.  Maybe you left some old transaction open, or an
uncommitted prepared transaction there?

If not, I suppose you're up for some "perf" profiling ...

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Help troubleshooting SubtransControlLock problems

[Scott Frazer]

On Wed, Mar 7, 2018 at 10:39 AM, Alvaro Herrera 
wrote:
>
> Laurenz Albe wrote:
> I think you could get in this situation if the range of open
> transactions exceeds what fits in the buffers for subtrans.c pages, and
> the subtransaction cache overflows (64 entries apiece;
> PGPROC_MAX_CACHED_SUBXIDS in src/include/storage/proc.h).  Each page is
> 2048 transactions (4 bytes per xact, 8192 bytes per page;
> SUBTRANS_XACTS_PER_PAGE in src/backend/access/transam/subtrans.c), and
> we keep 32 pages (NUM_SUBTRANS_BUFFERS src/include/access/subtrans.h).
> So if your oldest transaction is over 64k XIDs old, and you have
> transactions with more than 64 subtransactions, you get in trouble.
>
> A simple solution is to increase NUM_SUBTRANS_BUFFERS and recompile.

Currently this seems to be happening when the server just starts taking
connections. It doesn't even need time to scale up anymore (previously it
took about 8 hours for the problem to re-appear)

It's only happening on the read replicas, though. I've just set my master
to handle all the traffic, but that's not really sustainable

Re: Replication pull instead of push

[PegoraroF10]

My question was ... how to pull data from Master into Slave. 
Postgres has a Publication/Subscription model that Slave connects to Master
and gets the data.
I want something that Master server gets its data which needs to be
replicated and send that data to Slave.

Is that way possible ?
If there is no extension or statement which does that, how can I do it
myself ?



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Replication pull instead of push

[Fabrízio de Royes Mello]

2018-03-07 16:01 GMT-03:00 PegoraroF10 :
>
> We have several servers, our masters, installed on several cities. Then we
> need have a centralized database (our standby) where we can do our
reports.
> So, usual way of doing postgres replication is creating a
> replication/subscription process.

Ok.


> But then that master server should be reachable and sometimes it´s not,
just
> because their IP changed or because we need to configure its router to get
> Postgres server properly.
>
> Finally my question, there is a way to pull data which needs to be
> replicated from master to slave.
>

The way is fix your network connection and if necessary change your
subscription side do connect again in your server and continue pulling data.

If you don't drop your publication it will retain the WAL until the
subscription connect again and pull data.

Regards,

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Replication pull instead of push

[PegoraroF10]

We have several servers, our masters, installed on several cities. Then we
need have a centralized database (our standby) where we can do our reports. 
So, usual way of doing postgres replication is creating a
replication/subscription process. 
But then that master server should be reachable and sometimes it´s not, just
because their IP changed or because we need to configure its router to get
Postgres server properly. 

Finally my question, there is a way to pull data which needs to be
replicated from master to slave. 



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: a very primitive question about division

[Martin Mueller]

In Table 9.5  the division of 9 by 4 is indeed described clearly. But there is 
no example of 4/9 and the different ways of formatting it as a decimal fraction 
with different options for rounding or a percentage.  Two or three added 
examples would make life easier for folks who have not progressed much beyond 
8th grade math.

From: "David G. Johnston" 
Date: Wednesday, March 7, 2018 at 11:43 AM
To: Martin Mueller 
Cc: "pgsql-general@lists.postgresql.org" 
Subject: Re: a very primitive question about division

On Wed, Mar 7, 2018 at 10:30 AM, Martin Mueller 
> wrote:
Thanks.  So  round(before1550/colfreq::numeric, 2)  produces the desired result.

The explanations and examples of string functions in the Postgres documentation 
are a model of clarity. The explanations and examples of basic arithmetic 
operations  are not.  There is room for improvement there.


Possibly.  Patches (or even just email suggestions - though the former are 
generally more apt to get applied) detailing specific improvements to make are 
welcome.

The description "division (integer division truncates the result)" seems 
reasonably clear - if you don't want the result truncated, and have two 
integers, you have to make one of the inputs a non-integer.  Of the various 
options I tend to choose numeric though others are possible.

David J.

Re: a very primitive question about division

[David G. Johnston]

On Wed, Mar 7, 2018 at 10:30 AM, Martin Mueller <
martinmuel...@northwestern.edu> wrote:

> Thanks.  So  round(before1550/colfreq::numeric, 2)  produces the desired
> result.
>
>
>
> The explanations and examples of string functions in the Postgres
> documentation are a model of clarity. The explanations and examples of
> basic arithmetic operations  are not.  There is room for improvement there.
>
>
>
Possibly.  Patches (or even just email suggestions - though the former are
generally more apt to get applied) detailing specific improvements to make
are welcome.

The description "division (integer division truncates the result)" seems
reasonably clear - if you don't want the result truncated, and have two
integers, you have to make one of the inputs a non-integer.  Of the various
options I tend to choose numeric though others are possible.

David J.

Re: a very primitive question about division

[Martin Mueller]

Thanks.  So  round(before1550/colfreq::numeric, 2)  produces the desired result.

The explanations and examples of string functions in the Postgres documentation 
are a model of clarity. The explanations and examples of basic arithmetic 
operations  are not.  There is room for improvement there.

From: "David G. Johnston" 
Date: Wednesday, March 7, 2018 at 11:23 AM
To: Martin Mueller 
Cc: "pgsql-general@lists.postgresql.org" 
Subject: Re: a very primitive question about division

On Wed, Mar 7, 2018 at 10:21 AM, Martin Mueller 
> wrote:
Given two values defined as integers, how do I divide one by the other and get 
an answer with two decimals, e.g 3 /4 = 0.75.

​Case one of them to numeric.

​select 3/4::numeric
​
​David J.​

Re: a very primitive question about division

[David G. Johnston]

On Wed, Mar 7, 2018 at 10:21 AM, Martin Mueller <
martinmuel...@northwestern.edu> wrote:

> Given two values defined as integers, how do I divide one by the other and
> get an answer with two decimals, e.g 3 /4 = 0.75.
>

​Case one of them to numeric.

​select 3/4::numeric
​
​David J.​

Re: Help troubleshooting SubtransControlLock problems

[Alvaro Herrera]

Laurenz Albe wrote:
> Scott Frazer wrote:
> > Hi, we have a Postgres 9.6 setup using replication that has recently 
> > started seeing a lot of processes stuck in
> > "SubtransControlLock" as a wait_event on the read-replicas. Like this, only 
> > usually about 300-800 of them:
> > 
> > 
> >  179706 | LWLockNamed | SubtransControlLock

I think you could get in this situation if the range of open
transactions exceeds what fits in the buffers for subtrans.c pages, and
the subtransaction cache overflows (64 entries apiece;
PGPROC_MAX_CACHED_SUBXIDS in src/include/storage/proc.h).  Each page is
2048 transactions (4 bytes per xact, 8192 bytes per page;
SUBTRANS_XACTS_PER_PAGE in src/backend/access/transam/subtrans.c), and
we keep 32 pages (NUM_SUBTRANS_BUFFERS src/include/access/subtrans.h).
So if your oldest transaction is over 64k XIDs old, and you have
transactions with more than 64 subtransactions, you get in trouble.

A simple solution is to increase NUM_SUBTRANS_BUFFERS and recompile.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Help troubleshooting SubtransControlLock problems

[Laurenz Albe]

Scott Frazer wrote:
> 
> On Wed, Mar 7, 2018 at 9:52 AM, Laurenz Albe  wrote:
> > Scott Frazer wrote:
> > > Hi, we have a Postgres 9.6 setup using replication that has recently 
> > > started seeing a lot of processes stuck in
> > > "SubtransControlLock" as a wait_event on the read-replicas. Like this, 
> > > only usually about 300-800 of them:
> > >
> > >
> > >  179706 | LWLockNamed | SubtransControlLock
> > >  186602 | LWLockNamed | SubtransControlLock
> > >  186606 | LWLockNamed | SubtransControlLock
> > >  180947 | LWLockNamed | SubtransControlLock
> > >  186621 | LWLockNamed | SubtransControlLock
> > >
> > > The server then begins to crawl, with some queries just never finishing 
> > > until I finally shut the server down.
> > >
> > > Searching for that particular combo of wait_event_type and wait_event 
> > > only seems to turn up the page
> > > about statistics collection, but no helpful information on 
> > > troubleshooting this lock.
> > >
> > > Restarting the replica server clears the locks and allows us to start 
> > > working again, but it's happened
> > > twice now in 12 hours and I'm worried it will happen again.
> > >
> > > Does anyone have any advice on where to start looking?
> > 
> > I don't think there is any connection to statistics collection.
> > 
> > This lock is used when subtransactions (SAVEPOINTs in SQL or EXCEPTION 
> > blocks
> > in PL/pgSQL) are created, read or removed.
> > 
> > This sounds like a PostgreSQL bug.
> > 
> > What is the exact PostgreSQL version you are running?
> > 
> > It would be cool if you could get a stack trace from the backend that is 
> > holding the lock.
>
> Server version is 9.6.5
> 
> Is there a decent guide to getting a stack trace on Centos7 when using the 
> official Postgres repo?
> trying to follow the Fedora guide put the debug info for 9.2.23 on the box 
> instead of the 9.6.5 version.

You'll have to install debuginfo from the same source where you got PostgreSQL.
It has to be for the same database version.

You should upgrade to 9.6.8 and see if the problem persists.

There is a guid how to get a stack trace at
https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Help troubleshooting SubtransControlLock problems

[Scott Frazer]

Server version is 9.6.5

Is there a decent guide to getting a stack trace on Centos7 when using the
official Postgres repo? trying to follow the Fedora guide put the debug
info for 9.2.23 on the box instead of the 9.6.5 version.

On Wed, Mar 7, 2018 at 9:52 AM, Laurenz Albe 
wrote:

> Scott Frazer wrote:
> > Hi, we have a Postgres 9.6 setup using replication that has recently
> started seeing a lot of processes stuck in
> > "SubtransControlLock" as a wait_event on the read-replicas. Like this,
> only usually about 300-800 of them:
> >
> >
> >  179706 | LWLockNamed | SubtransControlLock
> >  186602 | LWLockNamed | SubtransControlLock
> >  186606 | LWLockNamed | SubtransControlLock
> >  180947 | LWLockNamed | SubtransControlLock
> >  186621 | LWLockNamed | SubtransControlLock
> >
> > The server then begins to crawl, with some queries just never finishing
> until I finally shut the server down.
> >
> > Searching for that particular combo of wait_event_type and wait_event
> only seems to turn up the page
> > about statistics collection, but no helpful information on
> troubleshooting this lock.
> >
> > Restarting the replica server clears the locks and allows us to start
> working again, but it's happened
> > twice now in 12 hours and I'm worried it will happen again.
> >
> > Does anyone have any advice on where to start looking?
>
> I don't think there is any connection to statistics collection.
>
> This lock is used when subtransactions (SAVEPOINTs in SQL or EXCEPTION
> blocks
> in PL/pgSQL) are created, read or removed.
>
> This sounds like a PostgreSQL bug.
>
> What is the exact PostgreSQL version you are running?
>
> It would be cool if you could get a stack trace from the backend that is
> holding the lock.
>
> Yours,
> Laurenz Albe
>

Re: Help troubleshooting SubtransControlLock problems

[Laurenz Albe]

Scott Frazer wrote:
> Hi, we have a Postgres 9.6 setup using replication that has recently started 
> seeing a lot of processes stuck in
> "SubtransControlLock" as a wait_event on the read-replicas. Like this, only 
> usually about 300-800 of them:
> 
> 
>  179706 | LWLockNamed | SubtransControlLock
>  186602 | LWLockNamed | SubtransControlLock
>  186606 | LWLockNamed | SubtransControlLock
>  180947 | LWLockNamed | SubtransControlLock
>  186621 | LWLockNamed | SubtransControlLock
> 
> The server then begins to crawl, with some queries just never finishing until 
> I finally shut the server down.
> 
> Searching for that particular combo of wait_event_type and wait_event only 
> seems to turn up the page
> about statistics collection, but no helpful information on troubleshooting 
> this lock.
> 
> Restarting the replica server clears the locks and allows us to start working 
> again, but it's happened
> twice now in 12 hours and I'm worried it will happen again.
> 
> Does anyone have any advice on where to start looking?

I don't think there is any connection to statistics collection.

This lock is used when subtransactions (SAVEPOINTs in SQL or EXCEPTION blocks
in PL/pgSQL) are created, read or removed.

This sounds like a PostgreSQL bug.

What is the exact PostgreSQL version you are running?

It would be cool if you could get a stack trace from the backend that is 
holding the lock.

Yours,
Laurenz Albe

Re: Authentication?

[Stephen Frost]

Greetings,

* Benedict Holland (benedict.m.holl...@gmail.com) wrote:
> Not to get off topic, can you authenticate database users via Kerberos?

Absolutely.  GSSAPI is the auth method to use for Kerberos.

Thanks!

Stephen

Re: Authentication?

[Benedict Holland]

Not to get off topic, can you authenticate database users via Kerberos?

Thanks,
~Ben

On Wed, Mar 7, 2018 at 10:19 AM, Stephen Frost  wrote:

> Greetings,
>
> * Bjørn T Johansen (b...@havleik.no) wrote:
> > Is it possible to use one authentication method as default, like LDAP,
> and if the user is not found, then try to authenticate using
> > md5/scram-sha-256 ?
>
> Not directly in pg_hba.conf.  You might be able to construct a system
> which works like this using PAM though, but it wouldn't be much fun.
>
> LDAP use really should be discouraged as it involves sending the
> password to the PG server.  If you are operating in an active directory
> environment then you should be using GSSAPI/Kerberos.
>
> SCRAM is a good alternative as it doesn't send the password to the
> server either, though that is only available in PG10, of course.
>
> Thanks!
>
> Stephen
>
>

Re: Help troubleshooting SubtransControlLock problems

[Scott Frazer]

These don't seem like normal locks. Nothing shows up in a  "SELECT
relation::regclass, * FROM pg_locks WHERE NOT GRANTED;"

These processes are all active but the wait_event and wait_event_type
fields indicate they are waiting on (I believe) shared memory locks.

  pid   | usesysid |   usename|  application_name
| state_change  | wait_e

vent_type | wait_event  | state

+--+--+-+---+---

--+-+

 165024 |16392 | content_user | application | nymapp01.nym | 6192  |
2018-03-07 09:19:09.770155-06 | LWLock

Named | SubtransControlLock | active





On Tue, Mar 6, 2018 at 11:43 PM, Rene Romero Benavides <
rene.romer...@gmail.com> wrote:

> For such issues, I find this view very useful (the first one):
> https://wiki.postgresql.org/wiki/Lock_Monitoring
>
> Examine blocking_pid's ,  and tell us what kind of operation is blocking
> the other processes . Also, are there many long running transactions in
> your server?
>
>
> 2018-03-06 21:24 GMT-06:00 Scott Frazer :
>
>> Hi, we have a Postgres 9.6 setup using replication that has recently
>> started seeing a lot of processes stuck in "SubtransControlLock" as a
>> wait_event on the read-replicas. Like this, only usually about 300-800 of
>> them:
>>
>>
>>  179706 | LWLockNamed | SubtransControlLock
>>
>>  186602 | LWLockNamed | SubtransControlLock
>>
>>  186606 | LWLockNamed | SubtransControlLock
>>
>>  180947 | LWLockNamed | SubtransControlLock
>>
>>  186621 | LWLockNamed | SubtransControlLock
>>
>> The server then begins to crawl, with some queries just never finishing
>> until I finally shut the server down.
>>
>> Searching for that particular combo of wait_event_type and wait_event
>> only seems to turn up the page about statistics collection, but no helpful
>> information on troubleshooting this lock.
>>
>> Restarting the replica server clears the locks and allows us to start
>> working again, but it's happened twice now in 12 hours and I'm worried it
>> will happen again.
>>
>> Does anyone have any advice on where to start looking?
>>
>> Thanks,
>> Scott
>>
>>
>
>
> --
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
> http://pglearn.blogspot.mx/
>
>

Re: Authentication?

[Stephen Frost]

Greetings,

* Bjørn T Johansen (b...@havleik.no) wrote:
> Is it possible to use one authentication method as default, like LDAP, and if 
> the user is not found, then try to authenticate using
> md5/scram-sha-256 ?

Not directly in pg_hba.conf.  You might be able to construct a system
which works like this using PAM though, but it wouldn't be much fun.

LDAP use really should be discouraged as it involves sending the
password to the PG server.  If you are operating in an active directory
environment then you should be using GSSAPI/Kerberos.

SCRAM is a good alternative as it doesn't send the password to the
server either, though that is only available in PG10, of course.

Thanks!

Stephen

Re: Authentication?

[David G. Johnston]

On Wed, Mar 7, 2018 at 8:14 AM, Bjørn T Johansen  wrote:

> On Wed, 7 Mar 2018 07:14:55 -0700
> "David G. Johnston"  wrote:
>
> > On Wed, Mar 7, 2018 at 6:13 AM, Bjørn T Johansen  wrote:
> >
> > > Hi.
> > >
> > > Is it possible to use one authentication method as default, like LDAP,
> and
> > > if the user is not found, then try to authenticate using
> > > md5/scram-sha-256 ?
> > >
> >
> > ​In the "Client Authentication" Chapter:​
> >
> > ​https://www.postgresql.org/docs/10/static/auth-pg-hba-conf.html​
> >
> > ​"""
> > ​The first record with a matching connection type, client address,
> > requested database, and user name is used to perform authentication.
> There
> > is no “fall-through” or “backup”: if one record is chosen and the
> > authentication fails, subsequent records are not considered. If no record
> > matches, access is denied.
> > """
> >
>
> I was hoping I had misunderstood but ok.. :)
>

​In the specific case you describe here you could have the server poll the
LDAP server periodically and cache the user names recognized and the
leverage:

"​Multiple user names can be supplied by separating them with commas. A
separate file containing user names can be specified by preceding the file
name with @."

In short, you have to pre-compute which method each user is allowed to
access externally then provide that knowledge to PostgreSQL.

David J.

Re: Authentication?

[Bjørn T Johansen]

On Wed, 7 Mar 2018 07:14:55 -0700
"David G. Johnston"  wrote:

> On Wed, Mar 7, 2018 at 6:13 AM, Bjørn T Johansen  wrote:
> 
> > Hi.
> >
> > Is it possible to use one authentication method as default, like LDAP, and
> > if the user is not found, then try to authenticate using
> > md5/scram-sha-256 ?
> >  
> 
> ​In the "Client Authentication" Chapter:​
> 
> ​https://www.postgresql.org/docs/10/static/auth-pg-hba-conf.html​
> 
> ​"""
> ​The first record with a matching connection type, client address,
> requested database, and user name is used to perform authentication. There
> is no “fall-through” or “backup”: if one record is chosen and the
> authentication fails, subsequent records are not considered. If no record
> matches, access is denied.
> """
> 
> David J.

I was hoping I had misunderstood but ok.. :)

BTJ

Re: Authentication?

[David G. Johnston]

On Wed, Mar 7, 2018 at 6:13 AM, Bjørn T Johansen  wrote:

> Hi.
>
> Is it possible to use one authentication method as default, like LDAP, and
> if the user is not found, then try to authenticate using
> md5/scram-sha-256 ?
>

​In the "Client Authentication" Chapter:​

​https://www.postgresql.org/docs/10/static/auth-pg-hba-conf.html​

​"""
​The first record with a matching connection type, client address,
requested database, and user name is used to perform authentication. There
is no “fall-through” or “backup”: if one record is chosen and the
authentication fails, subsequent records are not considered. If no record
matches, access is denied.
"""

David J.

Re: save query as sql file

[Łukasz Jarych]

thanks you!

Jacek

2018-03-07 15:00 GMT+01:00 Melvin Davidson :

>
>
> On Wed, Mar 7, 2018 at 8:46 AM, Łukasz Jarych  wrote:
>
>> thank you !
>>
>> Jacek
>>
>> 2018-03-07 14:45 GMT+01:00 Adrian Klaver :
>>
>>> On 03/06/2018 11:07 PM, Łukasz Jarych wrote:
>>>
 Hello,

 I ma trying to save query as sql file in pg_admin4 but file --> save as
 not exists like here:

 https://www.youtube.com/watch?v=L4KJ_Kpymh4

 where can i do it ?


>>> Well according to manual:
>>>
>>> https://www.pgadmin.org/docs/pgadmin4/2.x/query_tool.html
>>>
>>> Query tool toolbar
>>>
>>>
>>> Click the Save icon to perform a quick-save of a previously saved query,
>>> or to access the Save menu:
>>>
>>> Select Save to save the selected content of the SQL Editor panel
>>> in a file.
>>> Select Save As to open a new browser dialog and specify a new
>>> location to which to save the selected content of the SQL Editor panel.
>>>
>>>
>>>
>>> Best,
 Jacek

>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>>>
>>
>>
>
> > trying to save query as sql file in pg_admin4 but file --> save as not
> exists like here:
>
> As previously answered, you have to use the icon(s) for all options in the
> query window
>
> FYI,In October 2017, I entered Feature request #2797 in PgAdmin's redmin
> to add a MENU to the query window in addition to the icons.
> That request is still open.
> https://redmine.postgresql.org/issues/2797
> --
> *Melvin Davidson*
> *Maj. Database & Exploration Specialist*
> *Universe Exploration Command – UXC*
> Employment by invitation only!
>

Re: save query as sql file

[Melvin Davidson]

On Wed, Mar 7, 2018 at 8:46 AM, Łukasz Jarych  wrote:

> thank you !
>
> Jacek
>
> 2018-03-07 14:45 GMT+01:00 Adrian Klaver :
>
>> On 03/06/2018 11:07 PM, Łukasz Jarych wrote:
>>
>>> Hello,
>>>
>>> I ma trying to save query as sql file in pg_admin4 but file --> save as
>>> not exists like here:
>>>
>>> https://www.youtube.com/watch?v=L4KJ_Kpymh4
>>>
>>> where can i do it ?
>>>
>>>
>> Well according to manual:
>>
>> https://www.pgadmin.org/docs/pgadmin4/2.x/query_tool.html
>>
>> Query tool toolbar
>>
>>
>> Click the Save icon to perform a quick-save of a previously saved query,
>> or to access the Save menu:
>>
>> Select Save to save the selected content of the SQL Editor panel
>> in a file.
>> Select Save As to open a new browser dialog and specify a new
>> location to which to save the selected content of the SQL Editor panel.
>>
>>
>>
>> Best,
>>> Jacek
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>

> trying to save query as sql file in pg_admin4 but file --> save as not
exists like here:

As previously answered, you have to use the icon(s) for all options in the
query window

FYI,In October 2017, I entered Feature request #2797 in PgAdmin's redmin to
add a MENU to the query window in addition to the icons.
That request is still open.
https://redmine.postgresql.org/issues/2797
-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: save query as sql file

[Łukasz Jarych]

thank you !

Jacek

2018-03-07 14:45 GMT+01:00 Adrian Klaver :

> On 03/06/2018 11:07 PM, Łukasz Jarych wrote:
>
>> Hello,
>>
>> I ma trying to save query as sql file in pg_admin4 but file --> save as
>> not exists like here:
>>
>> https://www.youtube.com/watch?v=L4KJ_Kpymh4
>>
>> where can i do it ?
>>
>>
> Well according to manual:
>
> https://www.pgadmin.org/docs/pgadmin4/2.x/query_tool.html
>
> Query tool toolbar
>
>
> Click the Save icon to perform a quick-save of a previously saved query,
> or to access the Save menu:
>
> Select Save to save the selected content of the SQL Editor panel
> in a file.
> Select Save As to open a new browser dialog and specify a new
> location to which to save the selected content of the SQL Editor panel.
>
>
>
> Best,
>> Jacek
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: save query as sql file

[Adrian Klaver]

On 03/06/2018 11:07 PM, Łukasz Jarych wrote:

Hello,

I ma trying to save query as sql file in pg_admin4 but file --> save as 
not exists like here:


https://www.youtube.com/watch?v=L4KJ_Kpymh4

where can i do it ?



Well according to manual:

https://www.pgadmin.org/docs/pgadmin4/2.x/query_tool.html

Query tool toolbar


Click the Save icon to perform a quick-save of a previously saved query, 
or to access the Save menu:


Select Save to save the selected content of the SQL Editor 
panel in a file.
Select Save As to open a new browser dialog and specify a new 
location to which to save the selected content of the SQL Editor panel.





Best,
Jacek



--
Adrian Klaver
adrian.kla...@aklaver.com

Authentication?

[Bjørn T Johansen]

Hi.

Is it possible to use one authentication method as default, like LDAP, and if 
the user is not found, then try to authenticate using
md5/scram-sha-256 ?


Regards,

BTJ

-- 
---
Bjørn T Johansen

b...@havleik.no
---
Someone wrote:
"I understand that if you play a Windows CD backwards you hear strange Satanic 
messages"
To which someone replied:
"It's even worse than that; play it forwards and it installs Windows"
---

Re: help to query json column

[Arup Rakshit]

Thanks Charles. that worked.


> On Mar 7, 2018, at 12:27 PM, Charles Clavadetscher 
>  wrote:
> 
> Hello
> 
>> -Original Message-
>> From: Arup Rakshit [mailto:aruprakshit1...@outlook.com]
>> Sent: Mittwoch, 7. März 2018 05:41
>> To: pgsql-general@lists.postgresql.org
>> Subject: help to query json column
>> 
>> Hi,
>> 
>> I would like to select only rows where signature has a non null value. My 
>> json looks like :
>> 
>> {
>>  "carInspection": {
>>"damages": [
>>  {
>>"x": 68.670309653916,
>>"y": 44.08014571949,
>>"errors": [
>>  {
>>"code": "BR",
>>"description": "Gebrochen"
>>  },
>>  {
>>"code": "F",
>>"description": "Reifen platt"
>>  }
>>]
>>  },
>>  {
>>"x": 40.8014571949,
>>"y": 50.273224043716,
>>"errors": [
>>  {
>>"code": "BR",
>>"description": "Gebrochen"
>>  },
>>  {
>>"code": "F",
>>"description": "Reifen platt"
>>  }
>>]
>>  },
>>  {
>>"x": 48.269581056466,
>>"y": 37.340619307832,
>>"errors": [
>>  {
>>"code": "F",
>>"description": "Reifen platt"
>>  }
>>]
>>  },
>>  {
>>"x": 49.180327868852,
>>"y": 15.482695810565,
>>"errors": [
>>  {
>>"code": "F",
>>"description": "Reifen platt"
>>  }
>>]
>>  }
>>],
>>"layoutURL": "default",
>>"signature1": "> 0 1000 

Re: Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "merge".

[Alvaro Herrera]

Vinodh NV wrote:
> Sir,
> 
> I am facing the error Caused by: org.postgresql.util.PSQLException: ERROR:
> syntax error at or near "merge". when I execute the below  query in
> postgres database. The same works fine in Oracle.
> 
> merge into net n using dual on (n.id=:id) when matched then update set
> Status=:status, lastStatusDate=:lastStatusDate, errorMessage=:errorMessage,
> errorDetails=:errorDetails when not matched then insert (id,Status,
> lastStatusDate, errorMessage, errorDetails) values
> (:id,:status,:lastStatusDate,:errorMessage,:errorDetails)";
> 
> 
> Can you please let me know how to resolve it?

Easy -- just don't use merge with Postgres, as it doesn't support MERGE
yet.  There's work ongoing to support it, but it will appear in Postgres
11 at the earliest.

There exists a nonstandard command INSERT ON CONFLICT DO UPDATE which
would probably be useful.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "merge".

[Vinodh NV]

Sir,

I am facing the error Caused by: org.postgresql.util.PSQLException: ERROR:
syntax error at or near "merge". when I execute the below  query in
postgres database. The same works fine in Oracle.

merge into net n using dual on (n.id=:id) when matched then update set
Status=:status, lastStatusDate=:lastStatusDate, errorMessage=:errorMessage,
errorDetails=:errorDetails when not matched then insert (id,Status,
lastStatusDate, errorMessage, errorDetails) values
(:id,:status,:lastStatusDate,:errorMessage,:errorDetails)";


Can you please let me know how to resolve it?

Thanks,
Vinodh.