Re: [GENERAL] Postgres 10.1 fails to start: server did not start in time

[Tom Lane]

Andres Freund  writes:
> we could really do better than just wonder whether our signal to
> shutdown was received or not.  There probably should be a quite short
> timeout for the server to change status, and then a much longer one for
> that shutdown to finish.

While I don't want to just raise the timeout, I could get behind a more
thorough rethinking of the behavior there.

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] Postgres 10.1 fails to start: server did not start in time

[Andres Freund]

On 2017-11-12 14:26:42 -0500, Tom Lane wrote:
> Christoph Berg  writes:
> > The default systemd timeout seems to be 90s. I have already changed
> > the systemd timeout to infinity (start) and 1h (stop), so only the
> > default pg_ctl timeout remains (60s), which I'd rather not override
> > unilaterally.
> 
> > That said, isn't 60s way too small for shutting down larger clusters?
> > And likewise for starting?
> 
> Well, that's tied into the fact that pg_ctl doesn't disturb the server's
> state if it gives up waiting.  If it did, we would certainly use a larger
> timeout or none at all.

Hm. So partially that's also related to the fact that we didn't have a
good way to know whether the server reacted to the shutdown request or
not. With the infrastructure from

commit f13ea95f9e473a43ee4e1baeb94daaf83535d37c
Author: Tom Lane 
Date:   2017-06-28 17:31:24 -0400

Change pg_ctl to detect server-ready by watching status in postmaster.pid.

we could really do better than just wonder whether our signal to
shutdown was received or not.  There probably should be a quite short
timeout for the server to change status, and then a much longer one for
that shutdown to finish.


> I don't feel a big need to change that default,
> but if you have a surrounding script that is going to take adverse action
> after a timeout then you need to use a larger value ...

Didn't we have to fiddle with this a bunch in the regression tests, to
get things to work properly on slow animals? If we had to do that, other
people had to do so as well. Not the friendliest experience...

Greetings,

Andres Freund


-- 
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] Postgres 10.1 fails to start: server did not start in time

[Tom Lane]

Christoph Berg  writes:
> The default systemd timeout seems to be 90s. I have already changed
> the systemd timeout to infinity (start) and 1h (stop), so only the
> default pg_ctl timeout remains (60s), which I'd rather not override
> unilaterally.

> That said, isn't 60s way too small for shutting down larger clusters?
> And likewise for starting?

Well, that's tied into the fact that pg_ctl doesn't disturb the server's
state if it gives up waiting.  If it did, we would certainly use a larger
timeout or none at all.  I don't feel a big need to change that default,
but if you have a surrounding script that is going to take adverse action
after a timeout then you need to use a larger value ...

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] Postgres 10.1 fails to start: server did not start in time

[Christoph Berg]

Re: Tom Lane 2017-11-12 <20802.1510513...@sss.pgh.pa.us>
> Agreed, but I think Peter has a point: why is there a timeout at all,
> let alone one as short as 30 seconds?  Since systemd doesn't serialize
> service starts unnecessarily, there seems little value in giving up
> quickly.  And we know that cases such as crash recovery may take more
> than that.

The default systemd timeout seems to be 90s. I have already changed
the systemd timeout to infinity (start) and 1h (stop), so only the
default pg_ctl timeout remains (60s), which I'd rather not override
unilaterally.

https://anonscm.debian.org/cgit/pkg-postgresql/postgresql-common.git/tree/systemd/postgresql@.service#n18

That said, isn't 60s way too small for shutting down larger clusters?
And likewise for starting?

Christoph


-- 
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] Postgres 10.1 fails to start: server did not start in time

[Tom Lane]

Christoph Berg  writes:
> Re: Peter J. Holzer 2017-11-12 <20171112173559.m6chmbyf4vz6f...@hjp.at>
>> Wouldn't it be better to remove the timeout?

> If you don't want to block, don't depend on the database service. That
> question is independent from the timeout.

Agreed, but I think Peter has a point: why is there a timeout at all,
let alone one as short as 30 seconds?  Since systemd doesn't serialize
service starts unnecessarily, there seems little value in giving up
quickly.  And we know that cases such as crash recovery may take more
than that.

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] Postgres 10.1 fails to start: server did not start in time

[Christoph Berg]

Re: Peter J. Holzer 2017-11-12 <20171112173559.m6chmbyf4vz6f...@hjp.at>
> Wouldn't it be better to remove the timeout? If some other service
> depends on PostgreSQL it probably shouldn't be startet until PostgreSQL
> is really up and services which don't need PostgreSQL (e.g. SSH or X11
> login or a web- or mail server) shouldn't depend on it.
> 
> One of the purported advantages of systemd over SystemV init is that it
> starts up services in parallel, so a service which takes a long (or
> infinite) time to start doesn't block other services.

If you don't want to block, don't depend on the database service. That
question is independent from the timeout.

Christoph


-- 
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] Postgres 10.1 fails to start: server did not start in time

[Peter J. Holzer]

On 2017-11-12 13:26:58 +0100, Christoph Berg wrote:
> Re: To Adam Brusselback 2017-11-11 
> <2017205316.u56lkmkakdmcx...@msg.df7cb.de>
> > I'm investigating if it's a good idea to tell systemd to ignore the
> > exit code of pg_ctl(cluster).
> 
> Telling systemd to ignore ExecStart errors seems to be the correct
> solution. The service will still be active, with the startup error
> being shown:

Wouldn't it be better to remove the timeout? If some other service
depends on PostgreSQL it probably shouldn't be startet until PostgreSQL
is really up and services which don't need PostgreSQL (e.g. SSH or X11
login or a web- or mail server) shouldn't depend on it.

One of the purported advantages of systemd over SystemV init is that it
starts up services in parallel, so a service which takes a long (or
infinite) time to start doesn't block other services.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: [GENERAL] Postgres 10.1 fails to start: server did not start in time

[Christoph Berg]

Re: To Adam Brusselback 2017-11-11 
<2017205316.u56lkmkakdmcx...@msg.df7cb.de>
> I'm investigating if it's a good idea to tell systemd to ignore the
> exit code of pg_ctl(cluster).

Telling systemd to ignore ExecStart errors seems to be the correct
solution. The service will still be active, with the startup error
being shown:

● postgresql@10-main.service - PostgreSQL Cluster 10-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled; vendor 
preset: enabled)
   Active: active (running) since Sun 2017-11-12 13:24:21 CET; 210ms ago
  Process: 31892 ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m 
fast 10-main stop (code=exited, status=0/SUCCESS)
  Process: 31922 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 
10-main start (code=exited, status=1/FAILURE)
 Main PID: 31928 (postgres)
Tasks: 8 (limit: 4915)
   CGroup: /system.slice/system-postgresql.slice/postgresql@10-main.service
   ├─31928 /usr/lib/postgresql/10/bin/postgres -D 
/var/lib/postgresql/10/main -c 
config_file=/etc/postgresql/10/main/postgresql.conf
   ├─31931 postgres: 10/main: checkpointer process
   ├─31932 postgres: 10/main: writer process
   ├─31933 postgres: 10/main: wal writer process
   ├─31934 postgres: 10/main: autovacuum launcher process
   ├─31935 postgres: 10/main: archiver process
   ├─31936 postgres: 10/main: stats collector process
   └─31937 postgres: 10/main: bgworker: logical replication launcher

Nov 12 13:24:20 lehmann systemd[1]: Starting PostgreSQL Cluster 10-main...
Nov 12 13:24:21 lehmann postgresql@10-main[31922]: Error: 
/usr/lib/postgresql/10/bin/pg_ctl /usr/lib/postgresql/10/bin/pg_ctl start -D 
/var/lib/postgresql/10/main -l /var/log/postgresql/postgresql-10-main.log -t 0 
-s -o  -c config_file="/etc/postgresql/10/main/postgresql.conf"  exited with 
status 1:
Nov 12 13:24:21 lehmann postgresql@10-main[31922]: pg_ctl: server did not start 
in time
Nov 12 13:24:21 lehmann systemd[1]: postgresql@10-main.service: PID file 
/var/run/postgresql/10-main.pid not readable (yet?) after start: No such file 
or directory
Nov 12 13:24:21 lehmann systemd[1]: Started PostgreSQL Cluster 10-main.

Fixed in 
https://anonscm.debian.org/cgit/pkg-postgresql/postgresql-common.git/commit/?id=fc57e655c71e8f6bcb3010b054f5adbf32a224d7
 , thanks for the report!

Christoph


-- 
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] Postgres 10.1 fails to start: server did not start in time

[Christoph Berg]

Re: Adam Brusselback 2017-11-11 

> Hey Christoph, I tried starting it with init (service postgresql
> start), and pg_ctlcluster.
> 
> I modified the pg_ctl.conf and set the timeout higher so I could just
> get my cluster back up and running properly, so I can't give you the
> info on what systemctl status says at the moment.

It looks like it's interference from systemd here. The problem is
easily reproduced by putting '-t 0' into pg_ctl.conf:

● postgresql@10-main.service - PostgreSQL Cluster 10-main
   Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled; vendor 
preset: enabled)
   Active: failed (Result: exit-code) since Sat 2017-11-11 21:10:56 CET; 9ms ago
  Process: 17946 ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m 
fast 10-main stop (code=exited, status=1/FAILURE)
  Process: 18000 ExecStart=postgresql@10-main --skip-systemctl-redirect 10-main 
start (code=exited, status=1/FAILURE)
 Main PID: 17878 (code=exited, status=0/SUCCESS)

Nov 11 21:10:55 lehmann systemd[1]: Starting PostgreSQL Cluster 10-main...
Nov 11 21:10:56 lehmann postgresql@10-main[18000]: Error: 
/usr/lib/postgresql/10/bin/pg_ctl /usr/lib/postgresql/10/bin/pg_ctl start -D 
/var/lib/postgresql/10/main -l /var/log/postgresql/postgresql-10-main.log -t 0 
-s -o  -c config_file="/etc/postgresql/10/main/postgresql.conf"  exited with 
status 1:
Nov 11 21:10:56 lehmann postgresql@10-main[18000]: pg_ctl: server did not start 
in time
Nov 11 21:10:56 lehmann systemd[1]: postgresql@10-main.service: Control process 
exited, code=exited status=1
Nov 11 21:10:56 lehmann systemd[1]: Failed to start PostgreSQL Cluster 10-main.
Nov 11 21:10:56 lehmann systemd[1]: postgresql@10-main.service: Unit entered 
failed state.
Nov 11 21:10:56 lehmann systemd[1]: postgresql@10-main.service: Failed with 
result 'exit-code'.

In other words, systemd will by default stop a service that fails to
start.

I'm investigating if it's a good idea to tell systemd to ignore the
exit code of pg_ctl(cluster). Possibly moving to Type=notify is the
best solution, but not all majors support that yet.

Will report back once I have a solution.

Christoph


-- 
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] Postgres 10.1 fails to start: server did not start in time

[Adam Brusselback]

Hey Christoph, I tried starting it with init (service postgresql
start), and pg_ctlcluster.

I modified the pg_ctl.conf and set the timeout higher so I could just
get my cluster back up and running properly, so I can't give you the
info on what systemctl status says at the moment.


On Sat, Nov 11, 2017 at 8:23 AM, Christoph Berg  wrote:
> Re: Tom Lane 2017-11-10 <8027.1510347...@sss.pgh.pa.us>
>> > The recovery succeeds, but when I go to start the cluster on the
>> > standby, it begins to replay the WAL, and does so for about 30
>> > seconds.  Then I get a line in my log saying:
>>
>> >> pg_ctl: server did not start in time
>
> Hi Adam,
>
> how did you start the server? Via pg_ctlcluster, the init system, or
> directly via pg_ctl?
>
>> > Followed by:
>> >> 2017-11-10 20:27:35.907 UTC [7132] LOG:  received smart shutdown request
>> >> ERROR [063]: : terminated on signal [SIGTERM]
>>
>> ... pg_ctl itself wouldn't decide to forcibly shut down the server
>> if the timeout expired.  It merely stops waiting and tells you so.
>> It seems like this must represent misdesign of whatever start script
>> you're using.  I think you need to complain to the Debian packagers
>> about that.
>
> pg_ctlcluster doesn't shut down if startup fails, but to be sure, we'd
> need to see the full log of whatever initiated the startup. If you are
> using systemd, what does `systemctl status postgresql@10-main` report?
> If that doesn't have anything, also check journalctl.
>
> Christoph


-- 
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] Postgres 10.1 fails to start: server did not start in time

[Christoph Berg]

Re: Tom Lane 2017-11-10 <8027.1510347...@sss.pgh.pa.us>
> > The recovery succeeds, but when I go to start the cluster on the
> > standby, it begins to replay the WAL, and does so for about 30
> > seconds.  Then I get a line in my log saying:
> 
> >> pg_ctl: server did not start in time

Hi Adam,

how did you start the server? Via pg_ctlcluster, the init system, or
directly via pg_ctl?

> > Followed by:
> >> 2017-11-10 20:27:35.907 UTC [7132] LOG:  received smart shutdown request
> >> ERROR [063]: : terminated on signal [SIGTERM]
> 
> ... pg_ctl itself wouldn't decide to forcibly shut down the server
> if the timeout expired.  It merely stops waiting and tells you so.
> It seems like this must represent misdesign of whatever start script
> you're using.  I think you need to complain to the Debian packagers
> about that.

pg_ctlcluster doesn't shut down if startup fails, but to be sure, we'd
need to see the full log of whatever initiated the startup. If you are
using systemd, what does `systemctl status postgresql@10-main` report?
If that doesn't have anything, also check journalctl.

Christoph


-- 
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] Postgres 10.1 fails to start: server did not start in time

[Tom Lane]

Adam Brusselback  writes:
>> You might want to increase pg_ctl's wait timeout for this situation,
>> since the default's evidently too little.  However ...

> Got it, thanks.

>> ... pg_ctl itself wouldn't decide to forcibly shut down the server
>> if the timeout expired.  It merely stops waiting and tells you so.
>> It seems like this must represent misdesign of whatever start script
>> you're using.  I think you need to complain to the Debian packagers
>> about that.

> Hmm, interesting.  I installed from this repo:
> http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main
> I was assuming someone in the Postgres project was involved in
> packaging it.  Do you know who I should reach out to in that case?

Christoph's probably a good place to start.

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] Postgres 10.1 fails to start: server did not start in time

[Joe Conway]

On 11/10/2017 01:01 PM, Adam Brusselback wrote:
>> You might want to increase pg_ctl's wait timeout for this situation,
>> since the default's evidently too little.  However ...
> Got it, thanks.
> 
>> ... pg_ctl itself wouldn't decide to forcibly shut down the server
>> if the timeout expired.  It merely stops waiting and tells you so.
>> It seems like this must represent misdesign of whatever start script
>> you're using.  I think you need to complain to the Debian packagers
>> about that.
> 
> Hmm, interesting.  I installed from this repo:
> http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main
> 
> I was assuming someone in the Postgres project was involved in
> packaging it.  Do you know who I should reach out to in that case?

I'm pretty sure Christoph Berg is at least involved. I cc'd him here in
case he misses this thread.

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Postgres 10.1 fails to start: server did not start in time

[Adam Brusselback]

> You might want to increase pg_ctl's wait timeout for this situation,
> since the default's evidently too little.  However ...
Got it, thanks.

> ... pg_ctl itself wouldn't decide to forcibly shut down the server
> if the timeout expired.  It merely stops waiting and tells you so.
> It seems like this must represent misdesign of whatever start script
> you're using.  I think you need to complain to the Debian packagers
> about that.

Hmm, interesting.  I installed from this repo:
http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main

I was assuming someone in the Postgres project was involved in
packaging it.  Do you know who I should reach out to in that case?


-- 
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] Postgres 10.1 fails to start: server did not start in time

[Tom Lane]

Adam Brusselback  writes:
> I am in the process of upgrading to Postgres 10, and am having trouble
> getting my streaming replica working.
> OS: Debian 9.2
> Version: 10.1
> I have my primary backed up using pgbackrest, and I restore that to my
> replica.  It generates a recovery.conf which has a restore command for
> the WAL to pull them from the pgbackrest server.
> The recovery succeeds, but when I go to start the cluster on the
> standby, it begins to replay the WAL, and does so for about 30
> seconds.  Then I get a line in my log saying:

>> pg_ctl: server did not start in time

You might want to increase pg_ctl's wait timeout for this situation,
since the default's evidently too little.  However ...

> Followed by:
>> 2017-11-10 20:27:35.907 UTC [7132] LOG:  received smart shutdown request
>> ERROR [063]: : terminated on signal [SIGTERM]

... pg_ctl itself wouldn't decide to forcibly shut down the server
if the timeout expired.  It merely stops waiting and tells you so.
It seems like this must represent misdesign of whatever start script
you're using.  I think you need to complain to the Debian packagers
about that.

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

[GENERAL] Postgres 10.1 fails to start: server did not start in time

[Adam Brusselback]

Hey all,
I am in the process of upgrading to Postgres 10, and am having trouble
getting my streaming replica working.

OS: Debian 9.2
Version: 10.1

I have my primary backed up using pgbackrest, and I restore that to my
replica.  It generates a recovery.conf which has a restore command for
the WAL to pull them from the pgbackrest server.

The recovery succeeds, but when I go to start the cluster on the
standby, it begins to replay the WAL, and does so for about 30
seconds.  Then I get a line in my log saying:

> pg_ctl: server did not start in time

Followed by:

> 2017-11-10 20:27:35.907 UTC [7132] LOG:  received smart shutdown request
> ERROR [063]: : terminated on signal [SIGTERM]
> 2017-11-10 20:27:35.911 P00   INFO: archive-get command end: terminated on 
> signal [SIGTERM]
ERROR [063]: : terminated on signal [SIGTERM]
> 2017-11-10 20:27:35.912 P00   INFO: archive-get command end: terminated on 
> signal [SIGTERM]
> 2017-11-10 20:27:35.978 UTC [7142] LOG:  shutting down
> 2017-11-10 20:27:36.151 UTC [7132] LOG:  database system is shut down

This happens weather I have the server configured as a standby or not.

Any help would be very appreciated.

Thanks,
-Adam


-- 
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] Postgres 10 manual breaks links with anchors

[Peter Eisentraut]

On 10/16/17 03:19, Thomas Kellerer wrote:
> I don't know if this is intentional, but the Postgres 10 manual started to 
> use lowercase IDs as anchors in the manual.
> 
> So, if I have e.g.: the following URL open in my browser:
> 
>
> https://www.postgresql.org/docs/current/static/sql-createindex.html#sql-createindex-concurrently
> 
> I cannot simply switch to an older version by replacing "current" with e.g. 
> "9.5" because in the 9.5 manual the anchor was all uppercase, and the URL 
> would need to be: 
> 
>
> https://www.postgresql.org/docs/9.5/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

A fix for this has been committed.  Once 10.1 comes out (next week), the
old-style anchors will work again.

-- 
Peter Eisentraut  http://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: [GENERAL] Postgres 9.6 fails to start on VMWare

[Martin Moore]

On 23/10/2017, 17:17, "George Neuner"  wrote:

>Doesn't GCloud provide a way to export drive images?
[He asks naively, never having used it.]


Nope – just the dd method (although this doesn’t work on VMWare, so you need to 
use a VWM utility on a Win pc to get it). I’m pretty sure Google use VMW, so 
it’s pretty poor!

  >  Move/rename the PG data directory, then use initdb to create a new
cluster.  You'll have to reload your databases from backups.
 
Cheers. I’m going off the idea of using VMs very quickly as I can see no real 
advantage and plenty of grief for my use case.

Consider this question resolved (

   







-- 
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] Postgres 9.6 fails to start on VMWare

[George Neuner]

On Mon, 23 Oct 2017 09:14:18 +0100, Martin Moore
 wrote:

>Same server. I tried a few times.
>
>I didn’t move the db separately, but did a ‘dd’ to copy the disk
>to an imagefile which was converted and loaded into VMWare.

If you copied the boot device that way while the system was running,
then you are lucky it even starts in the new environment.

What you did is only [really] safe to do with a data volume ... and
the volume should be mounted R/O while it is being copied.

Doesn't GCloud provide a way to export drive images?
[He asks naively, never having used it.]

>I ‘believed’ that this should keep the low level disk structure the
>same, but if this has corrupted the files I can drop, dump and
>restore, in which case how do I ‘drop’ the DB without postgres
>running?

Move/rename the PG data directory, then use initdb to create a new
cluster.  You'll have to reload your databases from backups.

But I would be concerned that the disk structure is damaged.  I would
run e2fsck on it - and if there are lots of errors found I wouldn't
use it.

George



-- 
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] Postgres 9.6 fails to start on VMWare

[Martin Moore]

OK, cheers.

 

How can I remove the db so I can restore it properly?

 

From: Scott Mead 
Date: Monday, 23 October 2017 at 16:35
To: Martin Moore 
Cc: Michael Nolan , "pgsql-general@postgresql.org" 

Subject: Re: [GENERAL] Postgres 9.6 fails to start on VMWare

 

 

 

On Mon, Oct 23, 2017 at 11:26 AM, Martin Moore  wrote:

It was running – not sure how dd handles this. Maybe badly… ☺

 

it doesn't handle it at all.  This would be the cause of your issue.

 

--Scott

 

 

 

From: Michael Nolan 
Date: Monday, 23 October 2017 at 15:52
To: Martin Moore 
Cc: rob stone , "pgsql-general@postgresql.org" 

Subject: Re: [GENERAL] Postgres 9.6 fails to start on VMWare

 

 

 

On Mon, Oct 23, 2017 at 3:14 AM, Martin Moore  wrote:

Same server. I tried a few times.

I didn’t move the db separately, but did a ‘dd’ to copy the disk to an 
imagefile which was converted and loaded into VMWare.

I ‘believed’ that this should keep the low level disk structure the same, but 
if this has corrupted the files I can drop, dump and restore, in which case how 
do I ‘drop’ the DB without postgres running?

Ta,

Martin.

 

Was the server you were backing up shut down or in backup mode when you did the 
'dd' copy?

--

Mike Nolan



 

-- 

--
Scott Mead
Sr. Architect
OpenSCG

http://openscg.com

Re: [GENERAL] Postgres 9.6 fails to start on VMWare

[Scott Mead]

On Mon, Oct 23, 2017 at 11:26 AM, Martin Moore 
wrote:

> It was running – not sure how dd handles this. Maybe badly… ☺
>

it doesn't handle it at all.  This would be the cause of your issue.

--Scott



>
>
> *From: *Michael Nolan 
> *Date: *Monday, 23 October 2017 at 15:52
> *To: *Martin Moore 
> *Cc: *rob stone , "pgsql-general@postgresql.org" <
> pgsql-general@postgresql.org>
> *Subject: *Re: [GENERAL] Postgres 9.6 fails to start on VMWare
>
>
>
>
>
>
>
> On Mon, Oct 23, 2017 at 3:14 AM, Martin Moore 
> wrote:
>
> Same server. I tried a few times.
>
> I didn’t move the db separately, but did a ‘dd’ to copy the disk to an
> imagefile which was converted and loaded into VMWare.
>
> I ‘believed’ that this should keep the low level disk structure the same,
> but if this has corrupted the files I can drop, dump and restore, in which
> case how do I ‘drop’ the DB without postgres running?
>
> Ta,
>
> Martin.
>
>
>
> Was the server you were backing up shut down or in backup mode when you
> did the 'dd' copy?
>
> --
>
> Mike Nolan
>



-- 
--
Scott Mead
Sr. Architect
*OpenSCG <http://openscg.com>*
http://openscg.com

Re: [GENERAL] Postgres 9.6 fails to start on VMWare

[Martin Moore]

It was running – not sure how dd handles this. Maybe badly… ☺

 

From: Michael Nolan 
Date: Monday, 23 October 2017 at 15:52
To: Martin Moore 
Cc: rob stone , "pgsql-general@postgresql.org" 

Subject: Re: [GENERAL] Postgres 9.6 fails to start on VMWare

 

 

 

On Mon, Oct 23, 2017 at 3:14 AM, Martin Moore  wrote:

Same server. I tried a few times.

I didn’t move the db separately, but did a ‘dd’ to copy the disk to an 
imagefile which was converted and loaded into VMWare.

I ‘believed’ that this should keep the low level disk structure the same, but 
if this has corrupted the files I can drop, dump and restore, in which case how 
do I ‘drop’ the DB without postgres running?

Ta,

Martin.

 

Was the server you were backing up shut down or in backup mode when you did the 
'dd' copy?

--

Mike Nolan

Re: [GENERAL] Postgres 9.6 fails to start on VMWare

[Michael Nolan]

On Mon, Oct 23, 2017 at 3:14 AM, Martin Moore 
wrote:

> Same server. I tried a few times.
>
> I didn’t move the db separately, but did a ‘dd’ to copy the disk to an
> imagefile which was converted and loaded into VMWare.
>
> I ‘believed’ that this should keep the low level disk structure the same,
> but if this has corrupted the files I can drop, dump and restore, in which
> case how do I ‘drop’ the DB without postgres running?
>
> Ta,
>
> Martin.
>

Was the server you were backing up shut down or in backup mode when you did
the 'dd' copy?
--
Mike Nolan

Re: [GENERAL] Postgres 9.6 fails to start on VMWare

[Martin Moore]

Same server. I tried a few times.

I didn’t move the db separately, but did a ‘dd’ to copy the disk to an 
imagefile which was converted and loaded into VMWare.

I ‘believed’ that this should keep the low level disk structure the same, but 
if this has corrupted the files I can drop, dump and restore, in which case how 
do I ‘drop’ the DB without postgres running?

Ta,

Martin.



On 23/10/2017, 00:51, "rob stone"  wrote:



On Sun, 2017-10-22 at 15:13 +0100, Martin Moore wrote:
> 2017-10-22 14:08:28 UTC [2479-1] LOG:  0: database system
> shutdown was interrupted; last known up at 2017-10-22 14:07:20 UTC

There is something missing here. Last shutdown at 2017-10-22 14:07:20
UTC on which server?
Then attempting to start it at 2017-10-22 14:08:28 UTC? One minute and
eight seconds later.
It might also help if you explained exactly how you moved the database
from Google Compute to this VM machine.

Cheers,
robert





-- 
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] Postgres 9.6 fails to start on VMWare

[rob stone]

On Sun, 2017-10-22 at 15:13 +0100, Martin Moore wrote:
> 2017-10-22 14:08:28 UTC [2479-1] LOG:  0: database system
> shutdown was interrupted; last known up at 2017-10-22 14:07:20 UTC

There is something missing here. Last shutdown at 2017-10-22 14:07:20
UTC on which server?
Then attempting to start it at 2017-10-22 14:08:28 UTC? One minute and
eight seconds later.
It might also help if you explained exactly how you moved the database
from Google Compute to this VM machine.

Cheers,
robert


-- 
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] Postgres 9.6 fails to start on VMWare

[Michael Paquier]

On Sun, Oct 22, 2017 at 11:13 PM, Martin Moore  wrote:
> I’ve migrated a running Debian Jessie system from a Google Compute instance 
> to a VMWare ESXi 6.5 system.

How did you actually do this migration? It is really easy to finish
with a corrupted instance if not doing things correctly in this world
(I am referring to quiesced snapshot & co).
-- 
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] Postgres 9.6 fails to start on VMWare

[Martin Moore]

I’ve migrated a running Debian Jessie system from a Google Compute instance to 
a VMWare ESXi 6.5 system.

Postgres won’t start, although returns [ok] :

/etc/init.d/postgresql start 9.6
[ ok ] Starting postgresql (via systemctl): postgresql.service.
 
2017-10-22 14:08:28 UTC [2479-1] LOG:  0: database system shutdown was 
interrupted; last known up at 2017-10-22 14:07:20 UTC
2017-10-22 14:08:28 UTC [2479-2] LOCATION:  StartupXLOG, xlog.c:6009
2017-10-22 14:08:28 UTC [2479-3] LOG:  0: database system was not properly 
shut down; automatic recovery in progress
2017-10-22 14:08:28 UTC [2479-4] LOCATION:  StartupXLOG, xlog.c:6505
2017-10-22 14:08:28 UTC [2479-5] LOG:  0: redo starts at A1/688398C0
2017-10-22 14:08:28 UTC [2479-6] LOCATION:  StartupXLOG, xlog.c:6760
2017-10-22 14:08:28 UTC [2479-7] LOG:  0: invalid record length at 
A1/6AC96408: wanted 24, got 0
2017-10-22 14:08:28 UTC [2479-8] LOCATION:  ReadRecord, xlog.c:4024
2017-10-22 14:08:28 UTC [2479-9] LOG:  0: redo done at A1/6AC963E0
2017-10-22 14:08:28 UTC [2479-10] LOCATION:  StartupXLOG, xlog.c:7023
2017-10-22 14:08:28 UTC [2479-11] LOG:  0: last completed transaction was 
at log time 2017-10-21 16:40:52.629875+00
2017-10-22 14:08:28 UTC [2479-12] LOCATION:  StartupXLOG, xlog.c:7028
2017-10-22 14:08:29 UTC [2479-13] LOG:  0: request to flush past end of 
generated WAL; request A1/72AF47A8, currpos A1/6AC96408
2017-10-22 14:08:29 UTC [2479-14] CONTEXT:  writing block 0 of relation 
base/203725/2840_vm
2017-10-22 14:08:29 UTC [2479-15] LOCATION:  WaitXLogInsertionsToFinish, 
xlog.c:1583
2017-10-22 14:08:29 UTC [2479-16] FATAL:  XX000: xlog flush request A1/72AF47A8 
is not satisfied --- flushed only to A1/6AC96408
2017-10-22 14:08:29 UTC [2479-17] CONTEXT:  writing block 0 of relation 
base/203725/2840_vm
2017-10-22 14:08:29 UTC [2479-18] LOCATION:  XLogFlush, xlog.c:2765
2017-10-22 14:08:29 UTC [2478-1] LOG:  0: startup process (PID 2479) exited 
with exit code 1
2017-10-22 14:08:29 UTC [2478-2] LOCATION:  LogChildExit, postmaster.c:3504
2017-10-22 14:08:29 UTC [2478-3] LOG:  0: aborting startup due to startup 
process failure
2017-10-22 14:08:29 UTC [2478-4] LOCATION:  reaper, postmaster.c:2777
2017-10-22 14:08:29 UTC [2478-5] LOG:  0: database system is shut down
2017-10-22 14:08:29 UTC [2478-6] LOCATION:  UnlinkLockFiles, miscinit.c:755






-- 
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] Postgres Architect

[Joshua D. Drake]

On 10/17/2017 10:39 AM, Dillon Tang wrote:

***Must sit onsite in Cypress,CA or Eden Prairie, MN***


This is the wrong list. Please use pgsql-jobs.

Thank you,

JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.us
* Unless otherwise stated, opinions are my own.   *


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

[GENERAL] Postgres Architect

[Dillon Tang]

***Must sit onsite in Cypress,CA or Eden Prairie, MN***

What is the specific title of the position?
Senior posgreSQL Database Administrator Consultant


What Project/Projects will the candidate be working on while on assignment?
The candidate will be working on the EDSS to Documentum migration project that 
involves postgreSQL as the backend database. The project number associated with 
this is PRJ107346

Is this person a sole contributor or part of a team?
The person is a part of the team but will be mostly be a solely contributor to 
analyzing, designing and standing up over 20 plus Production postgreSQL 
database for one of the largest ever document storage repositories in the world 
that involves billions of documents.

If so, please describe the team? (Name of team, size of team, etc.)
The team is the regular development EDMS team that will be working in 
Documentum, but this person will be the sole contributor for postgreSQL


What are the top 5-10 responsibilities for this position? (Please be detailed 
as to what the candidate is expected to do or complete on a daily basis)

1. postgreSQL Bi Directional Replication
2. postgreSQL Database Analysis and Design
3. postgreSQL Installation
4. postgreSQL Performance Tuning for very high workloads (30k transactions per 
second)
5. postgreSQL Database Partitioning
6. postgreSQL Geo separated High Availability



What software tools/skills are needed to perform these daily responsibilities?
1. postgreSQL
2. Toad OR DB Visualizer or any similar tools
3. Linux
4. Docker Containers


What skills/attributes are a must have?
1. postgreSQL with Certification is a must
2. postgreSQL Development experience
3. High availability hardcore experience
4. Shell Scripting
5. ODBC


What skills/attributes are nice to have?

1. May have written books or white papers on postgreSQL
2. Java Skills
3. Automation skills


Where is the work to be performed? (Please list preferred UHG facility, if 
other please specify i.e. remote work, rural, etc.)

- The entire engagement demands a very hands on Consultant who will be the sole 
contributor to postgreSQL. Some of the functions the contractor will perform 
handson are:

- standing up from scratch, managing and putting into production over 20 plus 
PostgreSQL Databases as Docker Containers

- postgreSQL Bi Directional Replication
- postgreSQL Database Analysis and Design
- postgreSQL Installation
- postgreSQL Performance Tuning for very high workloads (30k transactions per 
second)
- postgreSQL Database Partitioning
- postgreSQL Geo separated High Availability

Dillon Tang | Technical Recruiter | Apex Systems
4100 Newport Place Drive, Suite 410, Newport Beach, CA 92660
Office: 949.623.0003 | Fax: 949-623-0004
dt...@apexsystemsinc.com | 
www.apexsystems.com

This e-mail communication (including any attachments) may contain confidential 
information intended solely for the use of the intended recipient. If you are 
not the intended recipient, you should immediately stop reading this message 
and delete it from your system. Any unauthorized reading, distribution, copying 
or other use of this communication (or its attachments) is strictly prohibited.

[GENERAL] Postgres 10 manual breaks links with anchors

[Thomas Kellerer]

I don't know if this is intentional, but the Postgres 10 manual started to use 
lowercase IDs as anchors in the manual.

So, if I have e.g.: the following URL open in my browser:

   
https://www.postgresql.org/docs/current/static/sql-createindex.html#sql-createindex-concurrently

I cannot simply switch to an older version by replacing "current" with e.g. 
"9.5" because in the 9.5 manual the anchor was all uppercase, and the URL would 
need to be: 

   
https://www.postgresql.org/docs/9.5/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

Is this intentional? 

This also makes "cleaning" up links in e.g. StackOverflow that point to 
outdated versions of the manual a bit more cumbersome. 

Regards
Thomas
 



-- 
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] Postgres 9.6 Logical and Fisical replication

[Jaime Soler]

But what is your problem or error ? github's project doesn't support docker
images that enable and configure logical replication.

2017-09-19 10:33 GMT+02:00 Murtuza Zabuawala <
murtuza.zabuaw...@enterprisedb.com>:

> ++ Forwarding query to appropriate mail group.
>
>
> On Tue, Sep 19, 2017 at 3:00 AM, guedim  wrote:
>
>> Hi guys
>>
>> I am working with Postgres9.6 with a Master/Slave cluster replication
>> using
>> Streaming replication.
>> I would like to add a new Slave server database but this database with
>> logical replication .
>>
>>
>> I tried with some configurations but it was not possible  :(
>>
>> https://github.com/guedim/postgres-streaming-replication
>>
>>
>> Here is the image of what is in my mind:
>> 
>>
>> Thanks for any help!
>>
>>
>>
>> --
>> Sent from: http://www.postgresql-archive.org/PostgreSQL-pgadmin-hackers
>> -f2155306.html
>>
>>
>

[GENERAL] Postgres 9.6 Logical and Fisical replication

[Murtuza Zabuawala]

++ Forwarding query to appropriate mail group.


On Tue, Sep 19, 2017 at 3:00 AM, guedim  wrote:

> Hi guys
>
> I am working with Postgres9.6 with a Master/Slave cluster replication using
> Streaming replication.
> I would like to add a new Slave server database but this database with
> logical replication .
>
>
> I tried with some configurations but it was not possible  :(
>
> https://github.com/guedim/postgres-streaming-replication
>
>
> Here is the image of what is in my mind:
> 
>
> Thanks for any help!
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-pgadmin-hackers
> -f2155306.html
>
>

Re: [GENERAL] Postgres DB is failed due to pg_Xlog is continues full.

[Gavin Flower]

On 14/09/17 16:11, Yogesh Sharma wrote:
>>What you could do is copying its contents to a large disk, and then 
allow it to recover from the crash.
 I will copy the PGDATA into large disk. After that it is require to 
execute some specific command or automatically recovery will start?

If any command is require to execute please let me know.
Regards,
Yogesh

If appropriate, can insert comments directly after the associated text.



On Thursday, September 14, 2017, Michael Paquier 
mailto:michael.paqu...@gmail.com>> wrote:


On Thu, Sep 14, 2017 at 12:44 PM, John R Pierce
> wrote:


[...]

Hi Yogesh,

Please post followups to emails at the end, called 'bottom posting' like 
this.  It is the convention in the Postgres mailing lists, and makes it 
easier to get the context.


If a lot of the the previous text is no longer relevant it can be 
omitted and replaced by '[...]'.



Thanks,
Gavin



--
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] Postgres DB is failed due to pg_Xlog is continues full.

[John R Pierce]

On 9/13/2017 9:11 PM, Yogesh Sharma wrote:
>>What you could do is copying its contents to a large disk, and then 
allow it to recover from the crash.
 I will copy the PGDATA into large disk. After that it is require to 
execute some specific command or automatically recovery will start?

If any command is require to execute please let me know.



you're going to need an experienced postgres admin who understands low 
level disk recovery.    there's a variety of postgres businesses who 
offer such services for hire.



--
john r pierce, recycling bits in santa cruz



--
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] Postgres DB is failed due to pg_Xlog is continues full.

[Yogesh Sharma]

>>What you could do is copying its contents to a large disk, and then allow
it to recover from the crash.
 I will copy the PGDATA into large disk. After that it is require to
execute some specific command or automatically recovery will start?
If any command is require to execute please let me know.
Regards,
Yogesh

On Thursday, September 14, 2017, Michael Paquier 
wrote:

> On Thu, Sep 14, 2017 at 12:44 PM, John R Pierce  > wrote:
> > prior to that error, something else catastrophic must have happened to
> the
> > system, that error is more of a side effect. recovering a database server
> > that far gone which is running such an obsolete version will likely be an
> > expensive proposition. before doing anything, you should make a complete
> > backup of the $PGDATA directory (and other tablespace directories, if you
> > use any).
>
> Definitely take a backup of PGDATA before doing anything! What you
> could do is copying its contents to a large disk, and then allow it to
> recover from the crash. You are going to need more space at the end.
> And yes, upgrade as well. Lagging 7 major releases behind cannot be an
> excuse.
> --
> 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] Postgres DB is failed due to pg_Xlog is continues full.

[Michael Paquier]

On Thu, Sep 14, 2017 at 12:44 PM, John R Pierce  wrote:
> prior to that error, something else catastrophic must have happened to the
> system, that error is more of a side effect. recovering a database server
> that far gone which is running such an obsolete version will likely be an
> expensive proposition. before doing anything, you should make a complete
> backup of the $PGDATA directory (and other tablespace directories, if you
> use any).

Definitely take a backup of PGDATA before doing anything! What you
could do is copying its contents to a large disk, and then allow it to
recover from the crash. You are going to need more space at the end.
And yes, upgrade as well. Lagging 7 major releases behind cannot be an
excuse.
-- 
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] Postgres DB is failed due to pg_Xlog is continues full.

[Yogesh Sharma]

Dear all,

As current situation, i can not upgrade on higher version.

Is there any recovery command available?

Regards,
Yogesh

On Thursday, September 14, 2017, Gavin Flower 
wrote:

> On 14/09/17 15:29, Yogesh Sharma wrote:
>
>> Dear All,
>>
>> Thanks in advance.
>>
>> We are using Postgres 8.1.18 version.
>> In Postgres log, we found below logs.
>> –-
>> CONTEXT:writing block 0 of relation 1664/0/1260
>> ERROR: could not write block 0 of relation 1664/0/1260: Bad address
>>
>> 
>> Due to this pglog_Xlog directory has been continuously increased and
>> directory has been full and Postgres is stopped.
>> Please let me know how to recover this issue.
>>
>> Regards.
>> Yogesh
>>
>
> When you have recovered from that - I strong suggest that you consider
> upgrading to a supported version of Postgres like 9.6.5, as Postgres 8 is
> no longer supported and later versions have many bug fixes and
> enhancements!  Note that Postgres will be released in a few weeks.
>
>
> Cheers,
> Gavin
>
>
>

Re: [GENERAL] Postgres DB is failed due to pg_Xlog is continues full.

[John R Pierce]

On 9/13/2017 8:29 PM, Yogesh Sharma wrote:


We are using Postgres 8.1.18 version.
In Postgres log, we found below logs.
–-
CONTEXT:writing block 0 of relation 1664/0/1260
ERROR: could not write block 0 of relation 1664/0/1260: Bad address


Due to this pglog_Xlog directory has been continuously increased and 
directory has been full and Postgres is stopped.

Please let me know how to recover this issue.



PostgreSQL 8.1 has been unsupported for quite a long time. 8.1.18 was 
released in 2009, 8.1.23 was the last update of 8.1 in late 2010.


the oldest 'supported' postgres is 9.2, and thats at EOL.

prior to that error, something else catastrophic must have happened to 
the system, that error is more of a side effect. recovering a database 
server that far gone which is running such an obsolete version will 
likely be an expensive proposition. before doing anything, you should 
make a complete backup of the $PGDATA directory (and other tablespace 
directories, if you use any).




--
john r pierce, recycling bits in santa cruz



--
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] Postgres DB is failed due to pg_Xlog is continues full.

[Gavin Flower]

On 14/09/17 15:29, Yogesh Sharma wrote:

Dear All,

Thanks in advance.

We are using Postgres 8.1.18 version.
In Postgres log, we found below logs.
–-
CONTEXT:writing block 0 of relation 1664/0/1260
ERROR: could not write block 0 of relation 1664/0/1260: Bad address


Due to this pglog_Xlog directory has been continuously increased and 
directory has been full and Postgres is stopped.

Please let me know how to recover this issue.

Regards.
Yogesh


When you have recovered from that - I strong suggest that you consider 
upgrading to a supported version of Postgres like 9.6.5, as Postgres 8 
is no longer supported and later versions have many bug fixes and 
enhancements!  Note that Postgres will be released in a few weeks.



Cheers,
Gavin




--
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] Postgres DB is failed due to pg_Xlog is continues full.

[Yogesh Sharma]

Dear All,

Thanks in advance.

We are using Postgres 8.1.18 version.
In Postgres log, we found below logs.
–-
CONTEXT:writing block 0 of relation 1664/0/1260
ERROR: could not write block 0 of relation 1664/0/1260: Bad address


Due to this pglog_Xlog directory has been continuously increased and
directory has been full and Postgres is stopped.
Please let me know how to recover this issue.

Regards.
Yogesh

Re: [GENERAL] Postgres logs showing error after replication

[Vick Khera]

On Wed, Sep 6, 2017 at 1:16 AM, Vijay Chaudhery  wrote:

> Ok is it possible to release the lock? or it is just a warning messages
> only?
> thanks
>

The message starts with "ERROR" so it is an error, not a warning. The
command failed. The only way to release the lock is to have the other
connection do it.

Re: [GENERAL] Postgres logs showing error after replication

[Vijay Chaudhery]

Ok is it possible to release the lock? or it is just a warning messages only?
thanks

-Original Message-
From: Tatsuo Ishii [mailto:is...@sraoss.co.jp] 
Sent: 06 September 2017 10:31 AM
To: Vijay Chaudhery 
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Postgres logs showing error after replication

> Ok thank you,
> 
> I have promoted slave server as master in postgres streaming 
> replication , howerver application is working fine after promotion , 
> but getting some error messages in the postgres log (slave which 
> promoted to master)
> 
> what does it mean?
> 
> 
>>ERROR: could not obtain lock on row in relation "main_instance"
> < 2017-09-05 13:08:45.434 UTC >STATEMENT: SELECT "main_instance"."id", 
> "main_instance"."uuid", "main_instance"."hostname", 
> "main_instance"."created", "main_instance"."modified", 
> "main_instance"."version", "main_instance"."capacity" FROM 
> "main_instance" LIMIT 1 FOR UPDATE NOWAIT

Your session (transction) tried to get a row lock on the row but other session 
(transaction) has already gotten the row lock on the same row.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


::DISCLAIMER::


The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
E-mail transmission is not guaranteed to be secure or error-free as information 
could be intercepted, corrupted,
lost, destroyed, arrive late or incomplete, or may contain viruses in 
transmission. The e mail and its contents
(with or without referred errors) shall therefore not attach any liability on 
the originator or HCL or its affiliates.
Views or opinions, if any, presented in this email are solely those of the 
author and may not necessarily reflect the
views or opinions of HCL or its affiliates. Any form of reproduction, 
dissemination, copying, disclosure, modification,
distribution and / or publication of this message without the prior written 
consent of authorized representative of
HCL is strictly prohibited. If you have received this email in error please 
delete it and notify the sender immediately.
Before opening any email and/or attachments, please check them for viruses and 
other defects.





-- 
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] Postgres logs showing error after replication

[Tatsuo Ishii]

> Ok thank you,
> 
> I have promoted slave server as master in postgres streaming replication , 
> howerver application is working fine after promotion , but getting some error 
> messages in the postgres log (slave which promoted to master) 
> 
> what does it mean?
> 
> 
>>ERROR: could not obtain lock on row in relation "main_instance"
> < 2017-09-05 13:08:45.434 UTC >STATEMENT: SELECT "main_instance"."id", 
> "main_instance"."uuid", "main_instance"."hostname", 
> "main_instance"."created", "main_instance"."modified", 
> "main_instance"."version", "main_instance"."capacity" FROM "main_instance" 
> LIMIT 1 FOR UPDATE NOWAIT

Your session (transction) tried to get a row lock on the row but other
session (transaction) has already gotten the row lock on the same row.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
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] Postgres logs showing error after replication

[Vijay Chaudhery]

Ok thank you,

I have promoted slave server as master in postgres streaming replication , 
howerver application is working fine after promotion , but getting some error 
messages in the postgres log (slave which promoted to master) 

what does it mean?


>ERROR: could not obtain lock on row in relation "main_instance"
< 2017-09-05 13:08:45.434 UTC >STATEMENT: SELECT "main_instance"."id", 
"main_instance"."uuid", "main_instance"."hostname", "main_instance"."created", 
"main_instance"."modified", "main_instance"."version", 
"main_instance"."capacity" FROM "main_instance" LIMIT 1 FOR UPDATE NOWAIT



-Original Message-----
From: Tatsuo Ishii [mailto:is...@sraoss.co.jp] 
Sent: 06 September 2017 10:02 AM
To: Vijay Chaudhery 
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Postgres logs showing error after replication

> I am not sure if the sending email is the right way to get the forum help. 
> Could you please let me know how to open forum query to ask postgres 
> questions.

Sending email to this mailing list regarding your questions is the perfect way 
to get help.

Other than this, there's an IRC channel for PostgreSQL.
https://www.postgresql.org/community/irc/

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


::DISCLAIMER::


The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
E-mail transmission is not guaranteed to be secure or error-free as information 
could be intercepted, corrupted,
lost, destroyed, arrive late or incomplete, or may contain viruses in 
transmission. The e mail and its contents
(with or without referred errors) shall therefore not attach any liability on 
the originator or HCL or its affiliates.
Views or opinions, if any, presented in this email are solely those of the 
author and may not necessarily reflect the
views or opinions of HCL or its affiliates. Any form of reproduction, 
dissemination, copying, disclosure, modification,
distribution and / or publication of this message without the prior written 
consent of authorized representative of
HCL is strictly prohibited. If you have received this email in error please 
delete it and notify the sender immediately.
Before opening any email and/or attachments, please check them for viruses and 
other defects.





-- 
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] Postgres logs showing error after replication

[Tatsuo Ishii]

> I am not sure if the sending email is the right way to get the forum help. 
> Could you please let me know how to open forum query to ask postgres 
> questions.

Sending email to this mailing list regarding your questions is the
perfect way to get help.

Other than this, there's an IRC channel for PostgreSQL.
https://www.postgresql.org/community/irc/

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


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

[GENERAL] Postgres logs showing error after replication

[Vijay Chaudhery]

Hi,

I am not sure if the sending email is the right way to get the forum help. 
Could you please let me know how to open forum query to ask postgres questions.

Thanks
vijay


::DISCLAIMER::


The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
E-mail transmission is not guaranteed to be secure or error-free as information 
could be intercepted, corrupted,
lost, destroyed, arrive late or incomplete, or may contain viruses in 
transmission. The e mail and its contents
(with or without referred errors) shall therefore not attach any liability on 
the originator or HCL or its affiliates.
Views or opinions, if any, presented in this email are solely those of the 
author and may not necessarily reflect the
views or opinions of HCL or its affiliates. Any form of reproduction, 
dissemination, copying, disclosure, modification,
distribution and / or publication of this message without the prior written 
consent of authorized representative of
HCL is strictly prohibited. If you have received this email in error please 
delete it and notify the sender immediately.
Before opening any email and/or attachments, please check them for viruses and 
other defects.

Re: [GENERAL] Postgres Log Message Translations

[tirveni yadav]

On Sun, Aug 20, 2017 at 7:19 AM, Enis Inan  wrote:
>
> Hello,
>
> Is there a way I can access the ".po" files containing the English -> Foreign 
> language translations of postgres' log messages? I only need the translations 
> from English to Japanese for a project I'm working on.
>
> Thank you!
> -Enis


In Linux, you can find the translations at /usr/share/locale or /usr/lib/locale.

Take a look at docs:
https://www.postgresql.org/docs/current/static/nls-translator.html


-- 
Regards,

Tirveni Yadav

www.bael.io

What is this Universe ? From what it arises ? Into what does it go?
In freedom it arises, In freedom it rests and into freedom it melts away.
Upanishads.


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

[GENERAL] Postgres Log Message Translations

[Enis Inan]

Hello,

Is there a way I can access the ".po" files containing the English ->
Foreign language translations of postgres' log messages? I only need the
translations from English to Japanese for a project I'm working on.

Thank you!
-Enis

Re: [GENERAL] Postgres csv logging

[Alessandro_feliz]

Hi David, thank you for your reply.

David G Johnston wrote
> As the comment there says your config and your output seem at odds. 

What I showed was the output present in the csv log, the output in the text
file log has the prefix that I indicated in the configration file.

Either way, I managed to "solve" my problem, I started playing with the
logging configurations and managed to get it working. I still have no clue
why a thing so basic isn't correctly implemented.

In a simple way, what I wanted to do was to configure postgres so it would
log all the queries and respective types in a file, like this

"SELECT * FROM test","SELECT"
"INSERT INTO test (id, time) VALUES ('123','1-1-2010')","INSERT"
...

Now, when checking the official documentation regarding postgres logging, I
noticed that the csv format had all the information I needed
(https://www.postgresql.org/docs/9.4/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG)

In theory, logging to a csv file would give a lot of information that would
be usefull to me, return code, query, command tag, etc... In practice, most
of the columns are missing... Not very usefull at all. I started playing
with the logging configuration and managed to have an output that I can use.

By setting the next variables:

log_min_duration_statement = 0
log_statement = 'none'

I managed to create an output with the actual command tag of the query
instead of the word "idle". Funny part, if I change the setting
log_statement to anything else than none, all the command tags are set to
"idle"... As I already said, fields are missing, for instance, I have no
query... I had to use some regex to get it from the message field, but it
should be present in the respective column, but it isn't.

Again, this is something that seems very simple to do... and I thinks its
also very usefull. Worst part is that it is documented, so why is not
possible to do such a trivial task??





--
View this message in context: 
http://www.postgresql-archive.org/Postgres-csv-logging-tp5972017p5972482.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] Postgres csv logging

[David G. Johnston]

It is customary to indicate when you've posted the same question to other
forums - in this case SO.

https://dba.stackexchange.com/questions/180263/postgres-log-query-and-command-tag-to-csv

As the comment there says your config and your output seem at odds.  Though
I think your confusion is still adequately represented.

On Wed, Jul 19, 2017 at 4:41 PM, Alessandro_feliz <
alessandro_fe...@hotmail.com> wrote:

> Why is the first line, that has the query executed, saying that the command
> tag is "idle", shouldn't it say "SELECT"?


"Command tag: type of session's current command" - given your example I
suppose this means "initial state of the session" as opposed to being a
category tag of what the current line is doing.  What you are seeing is
"start at idle and parse" the incoming statement text.  That is one action
- parse.  The subsequent action is "execute" the contents of the SELECT
that is now in the active state of the session.

If the above is correct the docs could be more clear since I'd agree that
your interpretation of the field seems more logical - though knowing
initial state is quite useful in its own right.

David J.

[GENERAL] Postgres csv logging

[Alessandro_feliz]

I am trying to log executed queries into a csv file.

My main objective is to log the command tag and query. For that I enabled
logging in the postgresql.conf, my configs are the following:

#--
# ERROR REPORTING AND LOGGING
#--

# - Where to Log -

log_destination = 'stderr,csvlog'   # Valid values are
combinations of
# stderr, csvlog, syslog, and
eventlog,
# depending on platform.  csvlog
# requires logging_collector to be
on.

# This is used when logging to stderr:
logging_collector = on  # Enable capturing of stderr and
csvlog
# into log files. Required to be on
for
# csvlogs.
# (change requires restart)

# These are only used if logging_collector is on:
log_directory = 'pg_log'# directory where log files are
written,
# can be absolute or relative to
PGDATA
log_filename = 'postgresql-%a-%H-%M.log'# log file name pattern,
# can include strftime() escapes
#log_file_mode = 0600   # creation mode for log files,
# begin with 0 to use octal notation
log_truncate_on_rotation = on   # If on, an existing log file with
the
# same name as the new log file will
be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on
restarts
# or size-driven rotation.  Default
is
# off, meaning append to existing
files
# in all cases.
log_rotation_age = 10   # Automatic rotation of logfiles
will
# happen after that time.  0
disables.
log_rotation_size = 10240   # Automatic rotation of
logfiles will
# happen after that much log output.
# 0 disables.

# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

# This is only relevant when logging to eventlog (win32):
#event_source = 'PostgreSQL'

# - When to Log -

#client_min_messages = log  # values in order of decreasing
detail:
#   debug5
#   debug4
#   debug3
#   debug2
#   debug1
#   log
#   notice
#   warning
#   error

#log_min_messages = info# values in order of decreasing
detail:
#   debug5
#   debug4
#   debug3
#   debug2
#   debug1
#   info
#   notice
#   warning
#   error
#   log
#   fatal
#   panic

#log_min_error_statement = info # values in order of decreasing
detail:
#   debug5
#   debug4
#   debug3
#   debug2
#   debug1
#   info
#   notice
#   warning
#   error
#   log
#   fatal
#   panic (effectively off)

log_min_duration_statement = 0  # -1 is disabled, 0 logs all
statements
# and their durations, > 0 logs only
# statements running at least this
number
# of milliseconds


# - What to Log -

#debug_print_parse = 

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[DrakoRod]

You're right I have forgotten to say, the OS is RHEL 7.

Actually I'm reading about.

Thanks!





-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions 
--
View this message in context: 
http://www.postgresql-archive.org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923p5969564.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[Melvin Davidson]

On Fri, Jun 30, 2017 at 11:36 AM, DrakoRod  wrote:

> > Do you control the app?
>
> Nop Just I know how it's developed.
>
> > The app has a pooling component and you still are having problems, have
> > you looked at what the pooler is actually doing?
>
> As far as I know, the wildfly's jdbc pool. No really I don't know what are
> doing. I suspect that problem is that in DAO's not are closing the sessions
> or not beginning transactions properly. I going to ask them send me the
> logfile or I'll could verify the pool behavior.
>
> > Not sure what the above means. Are you saying the application you refer
> > to above has a history of not correctly closing connections or are you
> > talking in general terms about applications interacting with databases.
>
> Sorry, it's not like that, just was a comment, The problem I have is with a
> specific application.
>
> > I've attached two files that may be helpful to you.
>
> Melvin , Thanks for the scripts! I owe one!
>
> I have another question, I've was reading about the lock_timeout, Somehow
> this parameter will help or could affect all the behaviour?
>
> Thanks!
>
>
>
>
>
>
> -
> Dame un poco de fe, eso me bastará.
> Rozvo Ware Solutions
> --
> View this message in context: http://www.postgresql-archive.
> org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-
> tp5968923p5969552.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

>I've was reading about the lock_timeout, Somehow this parameter will help
or could affect all the behaviour?
This affects all behavior and will abort statements that wait too long to
get locks.

https://www.postgresql.org/docs/9.4/static/runtime-config-client.html

lock_timeout (integer)

Abort any statement that waits longer than the specified number of
milliseconds while attempting to acquire a lock on a table, index, row, or
other database object. The time limit applies separately to each lock
acquisition attempt. The limit applies both to explicit locking requests
(such as LOCK TABLE, or SELECT FOR UPDATE without NOWAIT) and to
implicitly-acquired locks. If log_min_error_statement is set to ERROR or
lower, the statement that timed out will be logged. A value of zero (the
default) turns this off.

Unlike statement_timeout, this timeout can only occur while waiting for
locks. Note that if statement_timeout is nonzero, it is rather pointless to
set lock_timeout to the same or larger value, since the statement timeout
would always trigger first.

*Setting lock_timeout in postgresql.conf is not recommended because it
would affect all sessions.*


*You are probably much better off using tcp_keepalives... , providing your
system supports it. I don't remember if you ever gave us *


*the O/S.*



*Have a look at them. You might possibly be able to use them to force
disconnect after a set amount of inactivity
time.https://www.postgresql.org/docs/current/static/runtime-config-connection.html
*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[DrakoRod]

> Do you control the app?

Nop Just I know how it's developed. 

> The app has a pooling component and you still are having problems, have 
> you looked at what the pooler is actually doing? 

As far as I know, the wildfly's jdbc pool. No really I don't know what are
doing. I suspect that problem is that in DAO's not are closing the sessions
or not beginning transactions properly. I going to ask them send me the
logfile or I'll could verify the pool behavior.

> Not sure what the above means. Are you saying the application you refer 
> to above has a history of not correctly closing connections or are you 
> talking in general terms about applications interacting with databases. 

Sorry, it's not like that, just was a comment, The problem I have is with a
specific application. 

> I've attached two files that may be helpful to you. 

Melvin , Thanks for the scripts! I owe one! 

I have another question, I've was reading about the lock_timeout, Somehow
this parameter will help or could affect all the behaviour?

Thanks! 






-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions 
--
View this message in context: 
http://www.postgresql-archive.org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923p5969552.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[Melvin Davidson]

On Thu, Jun 29, 2017 at 7:30 PM, Adrian Klaver 
wrote:

> On 06/29/2017 10:03 AM, DrakoRod wrote:
>
>> To expand information, the application are written in Grails on wildfly
>> with
>> pool connections.
>>
>
> Do you control the app?
>
> The app has a pooling component and you still are having problems, have
> you looked at what the pooler is actually doing?
>
>
>> I didn't have time to check pg_locks with detail, I'll configure the
>> connections logs to monitoring those.
>>
>> I can't close connections on the application side. How I close connections
>> on the database side? With pg_terminate_backend, pg_cancel_backend or
>> exists
>> other function? I didn't want terminate backends because all connections
>> state was active.
>>
>
> https://www.postgresql.org/docs/9.4/static/functions-admin.
> html#FUNCTIONS-ADMIN-SIGNAL
>
> "pg_cancel_backend(pid int) boolean Cancel a backend's current
> query. You can execute this against another backend that has exactly the
> same role as the user calling the function. In all other cases, you must be
> a superuser.
>
> ...The process ID of an active backend can be found from the pid column of
> the pg_stat_activity view, ..."
>
>
>> I refer only to "idle" because almost in every database that I've saw the
>> application doesn't close correctly the connections. If are "idle in
>> transaction" is not normal.
>>
>
> Not sure what the above means. Are you saying the application you refer to
> above has a history of not correctly closing connections or are you talking
> in general terms about applications interacting with databases.
>
>
>> Your right Adrian, I need to know why the connections are not closing
>> properly.
>>
>> I can't apply idle_in_transation_session_timeout because the version of
>> PostgreSQL is 9.4.4 and the paramater not yet i'ts included. But sounds
>> good
>> the upgrade.
>>
>> Thanks for your help!
>>
>>
>>
>>
>> -
>> Dame un poco de fe, eso me bastará.
>> Rozvo Ware Solutions
>> --
>> View this message in context: http://www.postgresql-archive.
>> org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968
>> 923p5969262.html
>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>>
>>
>>
>
>
>
> *DrakoRod,*
>
> *If you are concerned that the application is leaving connections 'idle in
> transaction', I've attached two files that may be helpful to you. *
>
> *The first is a SQL query -> current_queries.sql  'idle in transaction'
> will appear in the "state" column. *
>
>
> *CAVEAT EMPTOR! in PG 9.6 the "CASE WHEN waiting ..."  needs to be
> commented out..*
> *The second is a Linux script "kill_long_idles.sh" that will kill any
> connection that is *
> *'idle in transaction' for longer than max_time. Currently it is set to 30
> minutes, but you can adjust to*
>
> * your desire. Just run it from a root cron job.*
>
> *Don't forget to   chmod +x  **kill_long_idles.sh*
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.
SELECT backend_start as be_start,
   datname,
   pid as pid,
   client_addr,
   usename as user,
   state,
   query,
   CASE WHEN waiting = TRUE
THEN 'BLOCKED'
ELSE 'no'
END as waiting,
   query_start,
   current_timestamp - query_start as duration 
  FROM pg_stat_activity
 WHERE pg_backend_pid() <> pid
ORDER BY 1, 
 datname,
 query_start;



kill_long_idles.sh
Description: Bourne shell script

-- 
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] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[Adrian Klaver]

On 06/29/2017 10:03 AM, DrakoRod wrote:

To expand information, the application are written in Grails on wildfly with
pool connections.


Do you control the app?

The app has a pooling component and you still are having problems, have 
you looked at what the pooler is actually doing?




I didn't have time to check pg_locks with detail, I'll configure the
connections logs to monitoring those.

I can't close connections on the application side. How I close connections
on the database side? With pg_terminate_backend, pg_cancel_backend or exists
other function? I didn't want terminate backends because all connections
state was active.


https://www.postgresql.org/docs/9.4/static/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL

"pg_cancel_backend(pid int) 	boolean 	Cancel a backend's current query. 
You can execute this against another backend that has exactly the same 
role as the user calling the function. In all other cases, you must be a 
superuser.


...The process ID of an active backend can be found from the pid column 
of the pg_stat_activity view, ..."




I refer only to "idle" because almost in every database that I've saw the
application doesn't close correctly the connections. If are "idle in
transaction" is not normal.


Not sure what the above means. Are you saying the application you refer 
to above has a history of not correctly closing connections or are you 
talking in general terms about applications interacting with databases.




Your right Adrian, I need to know why the connections are not closing
properly.

I can't apply idle_in_transation_session_timeout because the version of
PostgreSQL is 9.4.4 and the paramater not yet i'ts included. But sounds good
the upgrade.

Thanks for your help!




-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions
--
View this message in context: 
http://www.postgresql-archive.org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923p5969262.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





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


--
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] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[David G. Johnston]

On Thu, Jun 29, 2017 at 10:03 AM, DrakoRod  wrote:

> I can't close connections on the application side. How I close connections
> on the database side? With pg_terminate_backend, pg_cancel_backend or
> exists
> other function? I didn't want terminate backends because all connections
> state was active.
>
> I refer only to "idle" because almost in every database that I've saw the
> application doesn't close correctly the connections. If are "idle in
> transaction" is not normal.
>

​If you have lots of permanently maintained client sessions ​and cannot
change the client behavior you either need to try and get transaction
pooling working in a proxy pooling layer and point that clients to that OR
increase the number of simultaneous sessions your server will allow to some
number larger than the maximum possible concurrently connected clients.
And hope the server can handle the extra load.

David J.

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[DrakoRod]

To expand information, the application are written in Grails on wildfly with
pool connections.

I didn't have time to check pg_locks with detail, I'll configure the
connections logs to monitoring those.

I can't close connections on the application side. How I close connections
on the database side? With pg_terminate_backend, pg_cancel_backend or exists
other function? I didn't want terminate backends because all connections
state was active.

I refer only to "idle" because almost in every database that I've saw the
application doesn't close correctly the connections. If are "idle in
transaction" is not normal. 

Your right Adrian, I need to know why the connections are not closing
properly.

I can't apply idle_in_transation_session_timeout because the version of
PostgreSQL is 9.4.4 and the paramater not yet i'ts included. But sounds good
the upgrade. 

Thanks for your help! 




-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions 
--
View this message in context: 
http://www.postgresql-archive.org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923p5969262.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[PT]

On Tue, 27 Jun 2017 16:16:53 -0700 (MST)
DrakoRod  wrote:

> Yep, the real problem was all connections are used up. A ps command showed
> this:
> 
> postgres  1172 23340  1 13:00 ?00:01:23 postgres: dbsomething
> dbsomething 8.8.8.1[34024] PARSE waiting
> postgres  1527 23340  3 13:07 ?00:02:47 postgres: dbsomething
> dbsomething 8.8.8.2[49193] PARSE waiting
> postgres  1869 23340  1 13:13 ?00:01:05 postgres: dbsomething
> dbsomething 8.8.8.1[34209] PARSE waiting
> postgres  1963 23340  0 13:15 ?00:00:23 postgres: dbsomething
> dbsomething 8.8.8.1[34244] PARSE waiting
> postgres  2408 23340  2 13:23 ?00:01:31 postgres: dbsomething
> dbsomething 8.8.8.3[38324] PARSE waiting
> postgres  2442 23340  3 13:23 ?00:02:19 postgres: dbsomething
> dbsomething 8.8.8.3[38359] PARSE waiting
> postgres  2526 23340  2 13:25 ?00:01:39 postgres: dbsomething
> dbsomething 8.8.8.2[49994] PARSE waiting
> postgres  2533 23340  2 13:25 ?00:02:00 postgres: dbsomething
> dbsomething 8.8.8.4[58916] PARSE waiting
> postgres  2616 23340  2 13:26 ?00:01:28 postgres: dbsomething
> dbsomething 8.8.8.3[38496] PARSE waiting
> postgres  2632 23340  3 13:27 ?00:02:09 postgres: dbsomething
> dbsomething 8.8.8.2[50088] idle in transaction
> postgres  2644 23340  0 13:27 ?00:00:25 postgres: dbsomething
> dbsomething 8.8.8.4[58999] PARSE waiting
> postgres  2787 23340  0 13:30 ?00:00:16 postgres: dbsomething
> dbsomething 8.8.8.5[57944] PARSE waiting
> postgres  2815 23340  1 13:31 ?00:00:52 postgres: dbsomething
> dbsomething 8.8.8.2[50263] PARSE waiting
> postgres  2822 23340  0 13:31 ?00:00:29 postgres: dbsomething
> dbsomething 8.8.8.4[59158] PARSE waiting
> postgres  2825 23340  1 13:31 ?00:00:47 postgres: dbsomething
> dbsomething 8.8.8.4[59161] PARSE waiting
> postgres  2826 23340  0 13:31 ?00:00:11 postgres: dbsomething
> dbsomething 8.8.8.4[59163] PARSE waiting
> postgres  2876 23340  0 13:32 ?00:00:26 postgres: dbsomething
> dbsomething 8.8.8.1[34469] PARSE waiting
> postgres  2888 23340  0 13:32 ?00:00:36 postgres: dbsomething
> dbsomething 8.8.8.3[38729] PARSE waiting
> postgres  2911 23340  0 13:33 ?00:00:11 postgres: dbsomething
> dbsomething 8.8.8.2[50352] PARSE waiting
> postgres  2912 23340  0 13:33 ?00:00:36 postgres: dbsomething
> dbsomething 8.8.8.2[50353] PARSE waiting
> postgres  2916 23340  0 13:33 ?00:00:30 postgres: dbsomething
> dbsomething 8.8.8.3[38750] PARSE waiting
> postgres  2922 23340  0 13:33 ?00:00:33 postgres: dbsomething
> dbsomething 8.8.8.4[59238] PARSE waiting
> postgres  2927 23340  1 13:33 ?00:00:38 postgres: dbsomething
> dbsomething 8.8.8.4[59242] PARSE waiting
> postgres  3012 23340  0 13:35 ?00:00:03 postgres: dbsomething
> dbsomething 8.8.8.2[50439] PARSE waiting
> postgres  3017 23340  0 13:35 ?00:00:01 postgres: dbsomething
> dbsomething 8.8.8.3[38833] PARSE waiting
> postgres  3018 23340  0 13:35 ?00:00:27 postgres: dbsomething
> dbsomething 8.8.8.3[38834] PARSE waiting
> postgres  3020 23340  0 13:35 ?00:00:24 postgres: dbsomething
> dbsomething 8.8.8.4[59318] PARSE waiting
> postgres  3026 23340  0 13:35 ?00:00:04 postgres: dbsomething
> dbsomething 8.8.8.4[59323] PARSE waiting
> postgres  3033 23340  0 13:35 ?00:00:15 postgres: dbsomething
> dbsomething 8.8.8.4[59328] PARSE waiting
> 
> 
> When I ran *SELECT * FROM pg_stat_activity*, the state in all queries was
> active and most were SELECTs, then the server did not open new connections.
> I canceled many queries (only SELECTs) and server back to normal.
> 
> I understand that the principal problem probably are the application, of
> that I'm sure, but in the process debug. The best way to avoid or "fix" this
> are with connections pool like pgbouncer? How is the most secure way to
> return connections without restart service?

There are various timeout settings that can be configured:
https://www.postgresql.org/docs/9.6/static/runtime-config-client.html
idle_in_transation_session_timeout is probably the one you want to
enable.

It's likely that your application developers will start to complain about
database "errors" once you enable that, as connections will get killed and
cause errors on the application. You'll need to work to educate your
developers on how to fix their application so the situation stops happening.

-- 
PT 


-- 
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] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[Adrian Klaver]

On 06/27/2017 04:16 PM, DrakoRod wrote:

Yep, the real problem was all connections are used up. A ps command showed
this:

postgres  1172 23340  1 13:00 ?00:01:23 postgres: dbsomething
dbsomething 8.8.8.1[34024] PARSE waiting
postgres  1527 23340  3 13:07 ?00:02:47 postgres: dbsomething
dbsomething 8.8.8.2[49193] PARSE waiting


Hmm, the above is new one to me. Some searching found this:

https://www.postgresql.org/message-id/1282602153-sup-6272%40alvh.no-ip.org

"It means the parse phase is waiting for a lock.  You can see exactly
what it's waiting for by looking at pg_locks "WHERE NOT GRANTED"."

If you have not already, you might want to log 
connections/disconnections for more insight:


https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT



When I ran *SELECT * FROM pg_stat_activity*, the state in all queries was
active and most were SELECTs, then the server did not open new connections.
I canceled many queries (only SELECTs) and server back to normal.

I understand that the principal problem probably are the application, of
that I'm sure, but in the process debug. The best way to avoid or "fix" this
are with connections pool like pgbouncer? How is the most secure way to
return connections without restart service?


Close the connection.



I never had this problem, the idle connections is the normal in almost every
database I managed, but this is new for me.


FYI there is a difference between 'idle' connections and 'idle in 
transaction', not sure which one you are referring to. See below for 
more info:


https://www.postgresql.org/docs/9.6/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

The bottom line is connections are resources that need to be managed. To 
better able to do that is going to require some detective work to 
determine what is generating the connections and for what purpose.




Thanks for your help!




-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions



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


--
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] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[DrakoRod]

Yep, the real problem was all connections are used up. A ps command showed
this:

postgres  1172 23340  1 13:00 ?00:01:23 postgres: dbsomething
dbsomething 8.8.8.1[34024] PARSE waiting
postgres  1527 23340  3 13:07 ?00:02:47 postgres: dbsomething
dbsomething 8.8.8.2[49193] PARSE waiting
postgres  1869 23340  1 13:13 ?00:01:05 postgres: dbsomething
dbsomething 8.8.8.1[34209] PARSE waiting
postgres  1963 23340  0 13:15 ?00:00:23 postgres: dbsomething
dbsomething 8.8.8.1[34244] PARSE waiting
postgres  2408 23340  2 13:23 ?00:01:31 postgres: dbsomething
dbsomething 8.8.8.3[38324] PARSE waiting
postgres  2442 23340  3 13:23 ?00:02:19 postgres: dbsomething
dbsomething 8.8.8.3[38359] PARSE waiting
postgres  2526 23340  2 13:25 ?00:01:39 postgres: dbsomething
dbsomething 8.8.8.2[49994] PARSE waiting
postgres  2533 23340  2 13:25 ?00:02:00 postgres: dbsomething
dbsomething 8.8.8.4[58916] PARSE waiting
postgres  2616 23340  2 13:26 ?00:01:28 postgres: dbsomething
dbsomething 8.8.8.3[38496] PARSE waiting
postgres  2632 23340  3 13:27 ?00:02:09 postgres: dbsomething
dbsomething 8.8.8.2[50088] idle in transaction
postgres  2644 23340  0 13:27 ?00:00:25 postgres: dbsomething
dbsomething 8.8.8.4[58999] PARSE waiting
postgres  2787 23340  0 13:30 ?00:00:16 postgres: dbsomething
dbsomething 8.8.8.5[57944] PARSE waiting
postgres  2815 23340  1 13:31 ?00:00:52 postgres: dbsomething
dbsomething 8.8.8.2[50263] PARSE waiting
postgres  2822 23340  0 13:31 ?00:00:29 postgres: dbsomething
dbsomething 8.8.8.4[59158] PARSE waiting
postgres  2825 23340  1 13:31 ?00:00:47 postgres: dbsomething
dbsomething 8.8.8.4[59161] PARSE waiting
postgres  2826 23340  0 13:31 ?00:00:11 postgres: dbsomething
dbsomething 8.8.8.4[59163] PARSE waiting
postgres  2876 23340  0 13:32 ?00:00:26 postgres: dbsomething
dbsomething 8.8.8.1[34469] PARSE waiting
postgres  2888 23340  0 13:32 ?00:00:36 postgres: dbsomething
dbsomething 8.8.8.3[38729] PARSE waiting
postgres  2911 23340  0 13:33 ?00:00:11 postgres: dbsomething
dbsomething 8.8.8.2[50352] PARSE waiting
postgres  2912 23340  0 13:33 ?00:00:36 postgres: dbsomething
dbsomething 8.8.8.2[50353] PARSE waiting
postgres  2916 23340  0 13:33 ?00:00:30 postgres: dbsomething
dbsomething 8.8.8.3[38750] PARSE waiting
postgres  2922 23340  0 13:33 ?00:00:33 postgres: dbsomething
dbsomething 8.8.8.4[59238] PARSE waiting
postgres  2927 23340  1 13:33 ?00:00:38 postgres: dbsomething
dbsomething 8.8.8.4[59242] PARSE waiting
postgres  3012 23340  0 13:35 ?00:00:03 postgres: dbsomething
dbsomething 8.8.8.2[50439] PARSE waiting
postgres  3017 23340  0 13:35 ?00:00:01 postgres: dbsomething
dbsomething 8.8.8.3[38833] PARSE waiting
postgres  3018 23340  0 13:35 ?00:00:27 postgres: dbsomething
dbsomething 8.8.8.3[38834] PARSE waiting
postgres  3020 23340  0 13:35 ?00:00:24 postgres: dbsomething
dbsomething 8.8.8.4[59318] PARSE waiting
postgres  3026 23340  0 13:35 ?00:00:04 postgres: dbsomething
dbsomething 8.8.8.4[59323] PARSE waiting
postgres  3033 23340  0 13:35 ?00:00:15 postgres: dbsomething
dbsomething 8.8.8.4[59328] PARSE waiting


When I ran *SELECT * FROM pg_stat_activity*, the state in all queries was
active and most were SELECTs, then the server did not open new connections.
I canceled many queries (only SELECTs) and server back to normal.

I understand that the principal problem probably are the application, of
that I'm sure, but in the process debug. The best way to avoid or "fix" this
are with connections pool like pgbouncer? How is the most secure way to
return connections without restart service?

I never had this problem, the idle connections is the normal in almost every
database I managed, but this is new for me.

Thanks for your help! 




-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions 
--
View this message in context: 
http://www.postgresql-archive.org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923p5968960.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[David G. Johnston]

On Tue, Jun 27, 2017 at 3:41 PM, Melvin Davidson 
wrote:

> *His problem is NOT 'idle in transaction' per se. It is all connections
> are used up.*
> *Hence the need for pg_bouncer for connection pooling.*
>
>
Whether pg_bouncer provides a viable solution is just as big an unknown as
whether "idle in transaction" is the biggest contributor to the problem.
If all of them are idle in transaction then pg_bouncer is powerless to
help.  If they are generally just long-lived sessions and only a few stay
in transaction then transaction pooling mode may help.

David J.

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[Adrian Klaver]

On 06/27/2017 03:41 PM, Melvin Davidson wrote:



On Tue, Jun 27, 2017 at 6:32 PM, Adrian Klaver 


*His problem is NOT 'idle in transaction' per se. It is all connections 
are used up.*


Not following. The 'idle in transaction' queries are coming in through a 
connection so having them around is keeping a connection open. Dealing 
with them will help with the connection count. Using pg_bouncer is an 
option, but if you have a process that is not properly closing 
transactions/connections you could get into an arms race between that 
process and the size of your connection pool.



*Hence the need for pg_bouncer for connection pooling.*
--
*Melvin Davidson*




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


--
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] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[PT]

On Tue, 27 Jun 2017 18:41:25 -0400
Melvin Davidson  wrote:

> On Tue, Jun 27, 2017 at 6:32 PM, Adrian Klaver 
> wrote:
> 
> > On 06/27/2017 01:10 PM, DrakoRod wrote:
> >
> >> Hi folks.
> >>
> >> Today I had a problem with production's database PostgreSQL version
> >> 9.4.4.9.
> >> The server have max_connections set to 200, but today I reviewed
> >> pg_stat_activity and saw 199 active connections, obviously the server
> >> rejected any new connection and the production stopped.
> >>
> >> I saw another posts with a similar problems, but this was because the
> >> pg_xlog was full or disk does'nt write, but the directory and disk  had no
> >> problems.
> >>
> >> I just canceled some SELECTs querys and the server returned to normality.
> >> Now a monitoring activity of server and I can see some backends like this:
> >>
> >> postgres  9737 23340  2 14:55 ?00:00:15 postgres: dbname user
> >> 8.8.8.8[37082] idle in transaction
> >> postgres  9741 23340  9 14:55 ?00:00:47 postgres: dbname user
> >> 8.8.8.8[54286] idle in transaction
> >>
> >> Any suggestions?
> >>
> >
> > https://www.postgresql.org/docs/9.4/static/monitoring-stats.
> > html#PG-STAT-ACTIVITY-VIEW
> >
> > SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';
> >
> > To get more detail on what is holding these transactions open.
> >
> 
> *His problem is NOT 'idle in transaction' per se. It is all connections are
> used up.*
> *Hence the need for pg_bouncer for connection pooling.*

That assessment lacks enough information to be substantiated.

One of the things I've frequently seen happen is applications written with
ORMs will create a new connection because the existing connections in the
pool are already in a transaction. If his application is not properly
committing transactions, an additional pooler layer will not improve on
the problem. Hence, what he needs to do first is gather more information and
understand exactly what's going on.

Of course, if usage has just scaled up to the point where he doesn't have
any free connections, then your assessment might be correct. But he hasn't
provided enough information to be sure of that.

Regardless, lots of "idle in transaction" connections that stick around a
long time is a clear sign of application bugs. If they're not the cause
of his immediate problem, they will be the cause of problems at some point,
so he might as well track them down and fix them.

-- 
PT 


-- 
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] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[Melvin Davidson]

On Tue, Jun 27, 2017 at 6:32 PM, Adrian Klaver 
wrote:

> On 06/27/2017 01:10 PM, DrakoRod wrote:
>
>> Hi folks.
>>
>> Today I had a problem with production's database PostgreSQL version
>> 9.4.4.9.
>> The server have max_connections set to 200, but today I reviewed
>> pg_stat_activity and saw 199 active connections, obviously the server
>> rejected any new connection and the production stopped.
>>
>> I saw another posts with a similar problems, but this was because the
>> pg_xlog was full or disk does'nt write, but the directory and disk  had no
>> problems.
>>
>> I just canceled some SELECTs querys and the server returned to normality.
>> Now a monitoring activity of server and I can see some backends like this:
>>
>> postgres  9737 23340  2 14:55 ?00:00:15 postgres: dbname user
>> 8.8.8.8[37082] idle in transaction
>> postgres  9741 23340  9 14:55 ?00:00:47 postgres: dbname user
>> 8.8.8.8[54286] idle in transaction
>>
>> Any suggestions?
>>
>
> https://www.postgresql.org/docs/9.4/static/monitoring-stats.
> html#PG-STAT-ACTIVITY-VIEW
>
> SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';
>
> To get more detail on what is holding these transactions open.
>
>
>>
>>
>> -
>> Dame un poco de fe, eso me bastará.
>> Rozvo Ware Solutions
>> --
>> View this message in context: http://www.postgresql-archive.
>> org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923.html
>> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

*His problem is NOT 'idle in transaction' per se. It is all connections are
used up.*
*Hence the need for pg_bouncer for connection pooling.*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[Adrian Klaver]

On 06/27/2017 01:10 PM, DrakoRod wrote:

Hi folks.

Today I had a problem with production's database PostgreSQL version 9.4.4.9.
The server have max_connections set to 200, but today I reviewed
pg_stat_activity and saw 199 active connections, obviously the server
rejected any new connection and the production stopped.

I saw another posts with a similar problems, but this was because the
pg_xlog was full or disk does'nt write, but the directory and disk  had no
problems.

I just canceled some SELECTs querys and the server returned to normality.
Now a monitoring activity of server and I can see some backends like this:

postgres  9737 23340  2 14:55 ?00:00:15 postgres: dbname user
8.8.8.8[37082] idle in transaction
postgres  9741 23340  9 14:55 ?00:00:47 postgres: dbname user
8.8.8.8[54286] idle in transaction

Any suggestions?


https://www.postgresql.org/docs/9.4/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';

To get more detail on what is holding these transactions open.





-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions
--
View this message in context: 
http://www.postgresql-archive.org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





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


--
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] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[David G. Johnston]

On Tue, Jun 27, 2017 at 1:10 PM, DrakoRod  wrote:

> postgres  9741 23340  9 14:55 ?00:00:47 postgres: dbname user
> 8.8.8.8[54286] idle in transaction
>
> Any suggestions?
>

​There is a serious lack of information provided here but "idle in
transaction" sessions are generally problematic (in particular they
continue to hold locks) and can only be fixed at the source - by fixing
code or user behavior.

David J.

Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[Melvin Davidson]

On Tue, Jun 27, 2017 at 4:10 PM, DrakoRod  wrote:

> Hi folks.
>
> Today I had a problem with production's database PostgreSQL version
> 9.4.4.9.
> The server have max_connections set to 200, but today I reviewed
> pg_stat_activity and saw 199 active connections, obviously the server
> rejected any new connection and the production stopped.
>
> I saw another posts with a similar problems, but this was because the
> pg_xlog was full or disk does'nt write, but the directory and disk  had no
> problems.
>
> I just canceled some SELECTs querys and the server returned to normality.
> Now a monitoring activity of server and I can see some backends like this:
>
> postgres  9737 23340  2 14:55 ?00:00:15 postgres: dbname user
> 8.8.8.8[37082] idle in transaction
> postgres  9741 23340  9 14:55 ?00:00:47 postgres: dbname user
> 8.8.8.8[54286] idle in transaction
>
> Any suggestions?
>
>
>
> -
> Dame un poco de fe, eso me bastará.
> Rozvo Ware Solutions
> --
> View this message in context: http://www.postgresql-archive.
> org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


*Are you asking how to track down the user using up all the connection?
With the information you provided that cannot. be down.*



*If you are asking how to prevent problems in the future, then install
Pg_Bouncer and use that to pool connections.https://pgbouncer.github.io/
*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

[DrakoRod]

Hi folks.

Today I had a problem with production's database PostgreSQL version 9.4.4.9.
The server have max_connections set to 200, but today I reviewed
pg_stat_activity and saw 199 active connections, obviously the server
rejected any new connection and the production stopped.

I saw another posts with a similar problems, but this was because the
pg_xlog was full or disk does'nt write, but the directory and disk  had no
problems.

I just canceled some SELECTs querys and the server returned to normality.
Now a monitoring activity of server and I can see some backends like this: 

postgres  9737 23340  2 14:55 ?00:00:15 postgres: dbname user
8.8.8.8[37082] idle in transaction
postgres  9741 23340  9 14:55 ?00:00:47 postgres: dbname user
8.8.8.8[54286] idle in transaction

Any suggestions? 



-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions 
--
View this message in context: 
http://www.postgresql-archive.org/postgres-dbname-dbuser-9-9-9-9--PARSE-waiting-tp5968923.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] Postgres Data Encryption Using LUKS with dm-crypt ?

[Paul Jungwirth]

On 06/19/2017 12:40 AM, Scott Marlowe wrote:

On Sun, Jun 18, 2017 at 2:20 PM, Condor  wrote:

What I should expect, what is good and bad things that can be happened.


I've run Postgres on a LUKS volume for a few years now and it's all been 
pretty quiet. One challenge is you need to supply the password if the 
server restarts. Automating that in a way that doesn't simply reveal the 
password is tricky.


I'm not using RAID, so I can't speak to combing LUKS + RAID.

If you are on AWS, nowadays they have encrypted EBS volumes which will 
do all this for you automatically. If I were setting up this system 
today that's probably what I would have used.


> I think the only real test here is to build a luks system, initiate
> some pgbench type runs, wait a minute, run checkpoint and then yank
> out the plug. Run a dozen or so times looking for data corruption.

I think this is really the right answer!

Paul




--
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] Postgres Data Encryption Using LUKS with dm-crypt ?

[Scott Marlowe]

On Sun, Jun 18, 2017 at 2:20 PM, Condor  wrote:
> Hello ppl,
>
> a few years ago I asked the same question but did not receive valued answers
> and we use different way to realize the project.
> Today I wanna ask did some one do it and most important for me, can some one
> share his experience ?
> What I should expect, what is good and bad things that can be happened.
>
> Im thinking the problems can be occurred if server is restarted and data is
> not synced, but for that is raid cache battery.
> Also if hard drive need to be checked for bad clusters or broken index /
> files on filesystem what will happened with data?
> Because postgresql does not support data level encryption, Im wanna realize
> with third party tools.

The one and only time I setup a server to us LUKS was for a demo
laptop so that if it was lost our code / data / db etc etc were not
accessible. In that instance we didn't test for fsync reliability
because it was an easily recreateable system.

Generally speaking PostgreSQL expects "perfect" storage that writes
when it says it writes and doesn't present bad sectors to the database
to handle but rather maps such sectors out of the way silently without
data corruption.

I think the only real test here is to build a luks system, initiate
some pgbench type runs, wait a minute, run checkpoint and then yank
out the plug. Run a dozen or so times looking for data corruption.


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

[GENERAL] Postgres Data Encryption Using LUKS with dm-crypt ?

[Condor]

Hello ppl,

a few years ago I asked the same question but did not receive valued 
answers and we use different way to realize the project.
Today I wanna ask did some one do it and most important for me, can some 
one share his experience ?

What I should expect, what is good and bad things that can be happened.

Im thinking the problems can be occurred if server is restarted and data 
is not synced, but for that is raid cache battery.
Also if hard drive need to be checked for bad clusters or broken index / 
files on filesystem what will happened with data?
Because postgresql does not support data level encryption, Im wanna 
realize with third party tools.



Regards,
Hristo S


--
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] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

[Thomas Kellerer]

Adrian Klaver schrieb am 28.05.2017 um 17:51:

After I finally found the EnterpriseDB Beta binaries (they are pretty well 
hidden) I tested with those, and everything works fine there.


For those following along, where would that be?


Here they are:

https://www.enterprisedb.com/products-services-training/pgdevdownload

Note that the "Binaries Version" (the ZIP download) is missing several .exe 
program (most importantly psql.exe and pg_dump.exe but some others as well).

The files in the installer package are complete.





--
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] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

[Tom Lane]

Thomas Kellerer  writes:
> Tom Lane schrieb am 26.05.2017 um 20:18:
>>> The error message reported in the logfile is:
>>> pg_dump: unrecognized collation provider: p

>> Ugh :-( ... seems like a rather obvious typo in dumpCollation().
>> Thanks for finding it!

> When I drop that collation from the source database, pg_upgrade works without 
> problems with the EnterprsieDB binaries.

I committed a fix for that, will be in 10beta2.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=764cb2b596ced6aea4d83fd52ff628bdedb63316

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] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

[Adrian Klaver]

On 05/28/2017 08:41 AM, Thomas Kellerer wrote:

Tom Lane schrieb am 26.05.2017 um 20:18:




Apparently BigSQL forgot to include contrib/xml2 in their distribution;
you should ping them about that one.


I can confirm that it's a BigSQL problem.
After I finally found the EnterpriseDB Beta binaries (they are pretty 
well hidden) I tested with those, and everything works fine there.


For those following along, where would that be?




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


--
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] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

[Thomas Kellerer]

Tom Lane schrieb am 26.05.2017 um 20:18:

I just tried pg_upgrade from the Postgres 10 Beta1 (from the BigSQL 
distribution) to upgrade a 9.6 cluster.
pg_upgrade --check fails with the following messages:



could not load library "$libdir/pgxml":
ERROR:  could not load library 
"d:/etc/postgres-10/pgsql/lib/postgresql/pgxml.dll": unknown error 126


Apparently BigSQL forgot to include contrib/xml2 in their distribution;
you should ping them about that one.


I can confirm that it's a BigSQL problem.
After I finally found the EnterpriseDB Beta binaries (they are pretty well 
hidden) I tested with those, and everything works fine there.
 

When I then run pg_upgrade without the --check option, it fails when taking the 
schema only dump from one database.
The error message reported in the logfile is:
pg_dump: unrecognized collation provider: p


Ugh :-( ... seems like a rather obvious typo in dumpCollation().
Thanks for finding it!


When I drop that collation from the source database, pg_upgrade works without 
problems with the EnterprsieDB binaries.





--
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] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

[Tom Lane]

Thomas Kellerer  writes:
> Interesting enough, the same error message appears when I run "create 
> extension xml2;" on Postgres 10.

Sure, that no doubt is exactly the command getting issued during the
pg_upgrade attempt.

> It somehow looks as if there is a DLL dependency between XML2 and whatever 
> uses pgxml.dll (the new xmltable perhaps?)

pgxml.dll *is* the dll name for contrib/xml2.

I'm suspicious that what you're seeing is some sort of binary-level
incompatibility between BigSQL's build of contrib/xml2 and whatever
version of libxml2 you have installed.  Hard to guess more than that.

If you're not actually using contrib/xml2 in the 9.6 installation,
probably dropping that extension would let you do the upgrade.

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] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

[Thomas Kellerer]

Adrian Klaver schrieb am 26.05.2017 um 23:41:

If that's coming from port/dynloader/win32.c, as I think it must be
because the non-conformant-to-message-style-guidelines phrase "unknown
error" appears nowhere else in our tree, then that's an error code that
FormatMessage doesn't recognize.  Anybody have a clue what it means?



https://msdn.microsoft.com/en-us/library/windows/desktop/ms681382(v=vs.85).aspx

"The System Error Codes are very broad. Each one can occur in one of
many hundreds of locations in the system. Consequently the
descriptions of these codes cannot be very specific. Use of these
codes requires some amount of investigation and analysis. You need to
note both the programmatic and the run-time context in which these
errors occur. Because these codes are defined in WinError.h for
anyone to use, sometimes the codes are returned by non-system
software. Sometimes the code is returned by a function deep in the
stack and far removed from your code that is handling the error."


ERROR_MOD_NOT_FOUND

 126 (0x7E)

 The specified module could not be found.


I thought so as well, but as I said the "trace" from ProcessMonitor didn't show 
any errors.

It didn't even log an attempt to load that dll.



--
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] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

[Thomas Kellerer]

Adrian Klaver schrieb am 26.05.2017 um 23:16:

 could not load library "$libdir/pgxml":
 ERROR:  could not load library 
"d:/etc/postgres-10/pgsql/lib/postgresql/pgxml.dll": unknown error 126


Apparently BigSQL forgot to include contrib/xml2 in their distribution;
you should ping them about that one.


I wasn't sure where the problem is, I will report this to BigSQL as well.

However, the xml2 extension is included. The extension control file as well as 
libxml2-2.dll.


But the error message is looking for pgxml.dll, not libxml2-2.dll.
Those are clearly different files.


Well Thomas is using pg_upgrade from a BigSQL 10beta1 install to
upgrade from a 9.6 instance of unknown provenance. pg_upgrade is
complaining that the 9.6 cluster has pgxml.dll whereas the 10beta1
cluster does not even though xml2 is installed on both clusters. The
question is this a packaging oops on the part of BigSQL or something
else?


Interesting enough, the same error message appears when I run "create extension 
xml2;" on Postgres 10.
It somehow looks as if there is a DLL dependency between XML2 and whatever uses 
pgxml.dll (the new xmltable perhaps?)


 from a 9.6 instance of unknown provenance


The 9.6 installation is a 9.6.2 binary from EnterpriseDB (unfortunately they 
don't supply the beta binaries, otherwise I would have used those)


Thomas



--
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] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

[Adrian Klaver]

On 05/26/2017 02:30 PM, Tom Lane wrote:

Adrian Klaver  writes:

Well Thomas is using pg_upgrade from a BigSQL 10beta1 install to upgrade
from a 9.6 instance of unknown provenance. pg_upgrade is complaining
that the 9.6 cluster has pgxml.dll whereas the 10beta1 cluster does not
even though xml2 is installed on both clusters. The question is this a
packaging oops on the part of BigSQL or something else?


Actually, looking closer, the error message seems not to be "file not
found":


could not load library "$libdir/pgxml":
ERROR:  could not load library 
"d:/etc/postgres-10/pgsql/lib/postgresql/pgxml.dll": unknown error 126


If that's coming from port/dynloader/win32.c, as I think it must be
because the non-conformant-to-message-style-guidelines phrase "unknown
error" appears nowhere else in our tree, then that's an error code that
FormatMessage doesn't recognize.  Anybody have a clue what it means?



?

https://msdn.microsoft.com/en-us/library/windows/desktop/ms681382(v=vs.85).aspx

"The System Error Codes are very broad. Each one can occur in one of 
many hundreds of locations in the system. Consequently the descriptions 
of these codes cannot be very specific. Use of these codes requires some 
amount of investigation and analysis. You need to note both the 
programmatic and the run-time context in which these errors occur. 
Because these codes are defined in WinError.h for anyone to use, 
sometimes the codes are returned by non-system software. Sometimes the 
code is returned by a function deep in the stack and far removed from 
your code that is handling the error."



ERROR_MOD_NOT_FOUND

126 (0x7E)

The specified module could not be found.




regards, tom lane




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


--
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] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

[Tom Lane]

Adrian Klaver  writes:
> Well Thomas is using pg_upgrade from a BigSQL 10beta1 install to upgrade 
> from a 9.6 instance of unknown provenance. pg_upgrade is complaining 
> that the 9.6 cluster has pgxml.dll whereas the 10beta1 cluster does not 
> even though xml2 is installed on both clusters. The question is this a 
> packaging oops on the part of BigSQL or something else?

Actually, looking closer, the error message seems not to be "file not
found":

>>> could not load library "$libdir/pgxml":
>>> ERROR:  could not load library 
>>> "d:/etc/postgres-10/pgsql/lib/postgresql/pgxml.dll": unknown error 126

If that's coming from port/dynloader/win32.c, as I think it must be
because the non-conformant-to-message-style-guidelines phrase "unknown
error" appears nowhere else in our tree, then that's an error code that
FormatMessage doesn't recognize.  Anybody have a clue what it means?

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] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

[Adrian Klaver]

On 05/26/2017 01:58 PM, Alvaro Herrera wrote:

Thomas Kellerer wrote:

Tom Lane schrieb am 26.05.2017 um 20:18:

I just tried pg_upgrade from the Postgres 10 Beta1 (from the BigSQL 
distribution) to upgrade a 9.6 cluster.
pg_upgrade --check fails with the following messages:



 could not load library "$libdir/pgxml":
 ERROR:  could not load library 
"d:/etc/postgres-10/pgsql/lib/postgresql/pgxml.dll": unknown error 126


Apparently BigSQL forgot to include contrib/xml2 in their distribution;
you should ping them about that one.


I wasn't sure where the problem is, I will report this to BigSQL as well.

However, the xml2 extension is included. The extension control file as well as 
libxml2-2.dll.


But the error message is looking for pgxml.dll, not libxml2-2.dll.
Those are clearly different files.



Well Thomas is using pg_upgrade from a BigSQL 10beta1 install to upgrade 
from a 9.6 instance of unknown provenance. pg_upgrade is complaining 
that the 9.6 cluster has pgxml.dll whereas the 10beta1 cluster does not 
even though xml2 is installed on both clusters. The question is this a 
packaging oops on the part of BigSQL or something else?





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


--
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] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

[Alvaro Herrera]

Thomas Kellerer wrote:
> Tom Lane schrieb am 26.05.2017 um 20:18:
> > > I just tried pg_upgrade from the Postgres 10 Beta1 (from the BigSQL 
> > > distribution) to upgrade a 9.6 cluster.
> > > pg_upgrade --check fails with the following messages:
> > 
> > > could not load library "$libdir/pgxml":
> > > ERROR:  could not load library 
> > > "d:/etc/postgres-10/pgsql/lib/postgresql/pgxml.dll": unknown error 126
> > 
> > Apparently BigSQL forgot to include contrib/xml2 in their distribution;
> > you should ping them about that one.
> 
> I wasn't sure where the problem is, I will report this to BigSQL as well.
> 
> However, the xml2 extension is included. The extension control file as well 
> as libxml2-2.dll.

But the error message is looking for pgxml.dll, not libxml2-2.dll.
Those are clearly different files.

-- 
Á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: [GENERAL] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

[Thomas Kellerer]

Tom Lane schrieb am 26.05.2017 um 20:18:

I just tried pg_upgrade from the Postgres 10 Beta1 (from the BigSQL 
distribution) to upgrade a 9.6 cluster.
pg_upgrade --check fails with the following messages:



could not load library "$libdir/pgxml":
ERROR:  could not load library 
"d:/etc/postgres-10/pgsql/lib/postgresql/pgxml.dll": unknown error 126


Apparently BigSQL forgot to include contrib/xml2 in their distribution;
you should ping them about that one.


I wasn't sure where the problem is, I will report this to BigSQL as well.

However, the xml2 extension is included. The extension control file as well as 
libxml2-2.dll.


When I then run pg_upgrade without the --check option, it fails when taking the 
schema only dump from one database.
The error message reported in the logfile is:
pg_dump: unrecognized collation provider: p


Ugh :-( ... seems like a rather obvious typo in dumpCollation().
Thanks for finding it!






--
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] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

[Tom Lane]

Thomas Kellerer  writes:
> I just tried pg_upgrade from the Postgres 10 Beta1 (from the BigSQL 
> distribution) to upgrade a 9.6 cluster.
> pg_upgrade --check fails with the following messages:

>could not load library "$libdir/pgxml":
>ERROR:  could not load library 
> "d:/etc/postgres-10/pgsql/lib/postgresql/pgxml.dll": unknown error 126

Apparently BigSQL forgot to include contrib/xml2 in their distribution;
you should ping them about that one.

> When I then run pg_upgrade without the --check option, it fails when taking 
> the schema only dump from one database.
> The error message reported in the logfile is:
>pg_dump: unrecognized collation provider: p

Ugh :-( ... seems like a rather obvious typo in dumpCollation().
Thanks for finding it!

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

[GENERAL] Postgres 10 Beta1 - pg_upgrade fails on Windows 10

[Thomas Kellerer]

I just tried pg_upgrade from the Postgres 10 Beta1 (from the BigSQL 
distribution) to upgrade a 9.6 cluster.

pg_upgrade --check fails with the following messages:

Performing Consistency Checks
-
Checking cluster versions   ok
Checking database user is the install user  ok
Checking database connection settings   ok
Checking for prepared transactions  ok
Checking for reg* system OID user data typesok
Checking for contrib/isn with bigint-passing mismatch   ok
Checking for invalid "unknown" user columns ok
Checking for presence of required libraries fatal
Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
loadable_libraries.txt

loadable_libraries.txt contains:

  could not load library "$libdir/pgxml":
  ERROR:  could not load library 
"d:/etc/postgres-10/pgsql/lib/postgresql/pgxml.dll": unknown error 126

I ran pg_upgrade as administrator and regular user with the same results. I 
traced the file system calls of pg_upgrade using ProcessMonitor, but I couldn't 
see any obvious problems. I can supply the logfile of that trace if it helps.

Postgres 10 itself runs just fine, and so does e.g. the new XMLTABLE function, 
so I am unsure why pg_upgrade isn't able to load that dll


When I then run pg_upgrade without the --check option, it fails when taking the 
schema only dump from one database.

The error message reported in the logfile is:

  pg_dump: unrecognized collation provider: p

In the database where pg_dump (v10) fails, I have created the following custom 
collation:

  CREATE COLLATION "de" (lc_collate = 'German_Germany.1251', lc_ctype = 
'German_Germany.1251');
  ALTER COLLATION "de" OWNER TO "thomas";

pg_dump (9.6) runs fine with the same arguments on that database

I can supply a (schema only) dump from the database in question if this helps

Regards
Thomas
 




--
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] Postgres intermittent connection errors: psql.bin: could not connect to server: Cannot assign requested address

[Tom Lane]

Vamsi Patchipulusu  writes:
>   Error: psql.bin: could not connect to server: Cannot assign 
> requested address
> Is the server running on host "abchost.corp.xyz.com" 
> (xxx.xxx.xxx.xxx) and accepting
> TCP/IP connections on port 5432?

Googling suggests that this could occur if you're recycling client-side
connections so fast that the old port number assignments haven't timed out
yet.  That's not a Postgres bug, it's inherent in the TCP protocol specs.

> The  shell script does the following:
> a)  Connects to postgres database server using psql.
> b)  Issues a single select statement on table with 200 rows .
> c)   Writes the results to a text file.

There is a large body of evidence to the effect that issuing only one
query per connection attempt is a performance-killer.  Don't do that, at
least not more often than you have to.  This particular symptom is a new
one on me, but there are lots of other reasons not to do it.

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] Postgres intermittent connection errors: psql.bin: could not connect to server: Cannot assign requested address

[David G. Johnston]

On Monday, May 15, 2017, Vamsi Patchipulusu 
wrote:
>
>   Error: psql.bin: could not connect to server: Cannot assign
> requested address
>
> Is the server running on host "abchost.corp.xyz.com"
> (xxx.xxx.xxx.xxx) and accepting
>
> TCP/IP connections on port 5432?
>
> Jmeter is the tool we are using for orchestrating the tests. It  executes
> a shell script on 50 concurrent  threads.
>
The  shell script does the following:
>
> a)  Connects to postgres database server using psql.
>
> b)  Issues a single select statement on table with 200 rows .
>
> c)   Writes the results to a text file.
>
Jmeter client and database host are sitting in the same data center.
>
Database OS: RedHat 6.x,
>
> DB Version:  Postgres 9.6
>
> Database is of decent config. 16 CPU, 64GB RAM, SAN storage.
>
As you noted this is a client-side error so server specs are immaterial
unless the client and server are the same machine.

> While some calls are failing with the error*:   psql.bin: could not
> connect to server: Cannot assign requested address*
>
> Other calls are successful around the same timeframe may be one to two
> seconds later..
>
This points to an O/S problem, possibly configuration.  Or it might just be
a problem with JMeter.  In any case failing to bind to an address
sporadically isn't really something psql can be blamed for.

Googling jmeter for this turns up a variety of programs and scripts that
seem to have this problem when run within JMeter.  Exploring those
results may prove fruitful.

David J.

[GENERAL] Postgres intermittent connection errors: psql.bin: could not connect to server: Cannot assign requested address

[Vamsi Patchipulusu]

Hi All,

We are receiving below intermittent connection error during performance testing 
on postgres.
Thanks in advance for any suggestion or pointers on how we can troubleshoot 
this issue.

  Error: psql.bin: could not connect to server: Cannot assign requested 
address
Is the server running on host "abchost.corp.xyz.com" (xxx.xxx.xxx.xxx) 
and accepting
TCP/IP connections on port 5432?


Our organization is evaluating the feasibility of using postgres for an 
upcoming project.
As part of the evaluation we are performing  a 50 concurrent user read 
performance testing.
Jmeter is the tool we are using for orchestrating the tests. It  executes a 
shell script on 50 concurrent  threads.

The  shell script does the following:

a)  Connects to postgres database server using psql.

b)  Issues a single select statement on table with 200 rows .

c)   Writes the results to a text file.

Jmeter client and database host are sitting in the same data center.
Database OS: RedHat 6.x,
DB Version:  Postgres 9.6
Database is of decent config. 16 CPU, 64GB RAM, SAN storage.

Our tests are run for 10 to 15min.
During the middle of the run, we are noticing connection errors intermittently. 
(Around 20%  calls are failing due to this).

While some calls are failing with the error:   psql.bin: could not connect to 
server: Cannot assign requested address
Other calls are successful around the same timeframe may be one to two seconds 
later..

For the failed calls we are not able to see anything printed in the database 
server logs, (The exception is received on the client side).
For successful  calls we are able to see the details  of the sql statements, 
user, hostname etc... printed in the logs.

We have verified CPU,Memory,No of open files, netstat connection waits, but 
could not identify anything concrete that could be causing this issue.
The database is setup to accept 500 concurrent open connections and we are not 
using anywhere close to it.



Regards,
Vamsi

Re: [GENERAL] Postgres 9.6 Parallel Query

[Rafia Sabih]

On Thu, Apr 27, 2017 at 6:38 PM, basti  wrote:
> Hello,
>
> is there a recomment to set max_worker_processes and
> max_parallel_workers_per_gather per cpu core or thread?
>
This largely depends on what type of processing is done by parallel
workers, e.g. if the task is I/O bound then having workers equal to
h/w threads would be beneficial since less time will spent in CPU,
however if the task is CPU bound then workers more than cpu cores is
not likely to give much performance improvement as they'll be
contending for CPU cycles.

-- 
Regards,
Rafia Sabih
EnterpriseDB: http://www.enterprisedb.com/


-- 
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] Postgres 9.6.2 and pg_log - solved

[Mark Watson]

De : pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] De la part de Mark Watson
Envoyé : Monday, April 24, 2017 3:39 PM
À : David G. Johnston
Cc : (pgsql-general@postgresql.org)
Objet : Re: [GENERAL] Postgres 9.6.2 and pg_log


De : David G. Johnston [mailto:david.g.johns...@gmail.com]
Envoyé : Monday, April 24, 2017 3:15 PM
À : Mark Watson
Cc : (pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>)
Objet : Re: [GENERAL] Postgres 9.6.2 and pg_log

On Mon, Apr 24, 2017 at 11:27 AM, Mark Watson 
mailto:mark.wat...@jurisconcept.ca>> wrote:

log_destination = 'stderr' # Valid values are 
combinations of

# stderr, csvlog, syslog, and eventlog,

# depending on platform.  csvlog

# requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on# Enable capturing of stderr and 
csvlog

# into log files. Required to be on for

# csvlogs.

# (change requires restart)


​I'm out of ideas...

David J.​
Not to worry. This week I’m uninstalling and reinstalling postgres 9.6. I’ll do 
some tests and let this list know.

Mark Watson
Solved (sort of)
After a complete uninstall and fresh install of 9.6.2,  everything is behaving 
as normal. I have a tendency to chalk it off to perhaps an artifact from 
updating 9.6 beta => 9.6 RC => 9.6.0 => 9.6.1 => 9.6.2. Anyway, I’m not keen on 
repeating the updates. By the way, thanks all for the excellent work with 9.6 !

Mark Watson

Re: [GENERAL] Postgres connection Monitor

[Jan de Visser]

On Thu, Apr 27, 2017 at 6:18 AM, basti  wrote:
> Hallo, we have a Postgres database Server and around that there are 8
> clients get data from it.
>
> All servers are the the same datacenter i think (traceroute only 4-5 hops)
>
> Now we plan to move the server to an other hoster/datacenter.
>
> I have done simple test with ping and traceroute.
> Is there a way to monitor "traffic times" in postgres ?
> How long a query would take to be answered ?

You could write a tiny little C program which connects and sends a
PQping(), and measure that.


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

[GENERAL] Postgres 9.6 Parallel Query

[basti]

Hello,

is there a recomment to set max_worker_processes and
max_parallel_workers_per_gather per cpu core or thread?

Best regards


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

[GENERAL] Postgres connection Monitor

[basti]

Hallo, we have a Postgres database Server and around that there are 8
clients get data from it.

All servers are the the same datacenter i think (traceroute only 4-5 hops)

Now we plan to move the server to an other hoster/datacenter.

I have done simple test with ping and traceroute.
Is there a way to monitor "traffic times" in postgres ?
How long a query would take to be answered ?




-- 
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] Postgres 9.6.2 and pg_log

[Mark Watson]

De : David G. Johnston [mailto:david.g.johns...@gmail.com]
Envoyé : Monday, April 24, 2017 3:15 PM
À : Mark Watson
Cc : (pgsql-general@postgresql.org)
Objet : Re: [GENERAL] Postgres 9.6.2 and pg_log

On Mon, Apr 24, 2017 at 11:27 AM, Mark Watson 
mailto:mark.wat...@jurisconcept.ca>> wrote:

log_destination = 'stderr' # Valid values are 
combinations of

# stderr, csvlog, syslog, and eventlog,

# depending on platform.  csvlog

# requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on# Enable capturing of stderr and 
csvlog

# into log files. Required to be on for

# csvlogs.

# (change requires restart)


​I'm out of ideas...

David J.​
Not to worry. This week I’m uninstalling and reinstalling postgres 9.6. I’ll do 
some tests and let this list know.

Mark Watson

Re: [GENERAL] Postgres 9.6.2 and pg_log

[David G. Johnston]

On Mon, Apr 24, 2017 at 11:27 AM, Mark Watson 
wrote:

>
> log_destination = 'stderr' # Valid values are
> combinations of
>
>
> # stderr, csvlog, syslog, and eventlog,
>
>
> # depending on platform.  csvlog
>
>
> # requires logging_collector to be on.
>
>
>
> # This is used when logging to stderr:
>
> logging_collector = on# Enable capturing of stderr and
> csvlog
>
>
> # into log files. Required to be on for
>
>
> # csvlogs.
>
>
> # (change requires restart)
>
>
>
​I'm out of ideas...

David J.​

Re: [GENERAL] Postgres 9.6.2 and pg_log

[Mark Watson]

De : David G. Johnston [mailto:david.g.johns...@gmail.com]
Envoyé : Monday, April 24, 2017 2:18 PM
À : Mark Watson
Cc : (pgsql-general@postgresql.org)
Objet : Re: [GENERAL] Postgres 9.6.2 and pg_log

On Mon, Apr 24, 2017 at 10:58 AM, Mark Watson 
mailto:mark.wat...@jurisconcept.ca>> wrote:

The lines log_rotation_age and log_rotation_size are commented, and currently 
are:
#log_rotation_age = 1d # Automatic rotation of 
logfiles will

# happen after that time.  0 disables.
#log_rotation_size = 10MB  # Automatic rotation of 
logfiles will

# happen after that much log output.

# 0 disables.


I see from your reference article that the log_rotation_age is now in minutes, 
and I will adjust that to 1440 (1 day). I don’t know where the “1d” came from. 
I know it used to be like this in earlier versions.


​Those are fine:  See "Numeric with Unit" @​

​https://www.postgresql.org/docs/9.6/static/config-setting.html

​I think the actual missing, and (just checked) defaulted off, parameter is 
"logging_collector"

David J.
I have (copy/paste) :
log_destination = 'stderr' # Valid values are 
combinations of

# stderr, csvlog, syslog, and eventlog,

# depending on platform.  csvlog

# requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on# Enable capturing of stderr and 
csvlog

# into log files. Required to be on for

# csvlogs.

# (change requires restart)

Mark Watson

Re: [GENERAL] Postgres 9.6.2 and pg_log

[David G. Johnston]

On Mon, Apr 24, 2017 at 10:58 AM, Mark Watson 
wrote:

>
> The lines log_rotation_age and log_rotation_size are commented, and
> currently are:
>
> #log_rotation_age = 1d # Automatic
> rotation of logfiles will
>
>
> # happen after that time.  0 disables.
>
> #log_rotation_size = 10MB  # Automatic rotation of
> logfiles will
>
>
> # happen after that much log output.
>
>
> # 0 disables.
>
>
>
>
>
> I see from your reference article that the log_rotation_age is now in
> minutes, and I will adjust that to 1440 (1 day). I don’t know where the
> “1d” came from. I know it used to be like this in earlier versions.
>
>
>
​Those are fine:  See "Numeric with Unit" @​

​https://www.postgresql.org/docs/9.6/static/config-setting.html

​I think the actual missing, and (just checked) defaulted off, parameter is
"logging_collector"

David J.

Re: [GENERAL] Postgres 9.6.2 and pg_log

[Mark Watson]

De : David G. Johnston [mailto:david.g.johns...@gmail.com]
Envoyé : Monday, April 24, 2017 1:34 PM
À : Mark Watson
Cc : (pgsql-general@postgresql.org)
Objet : Re: [GENERAL] Postgres 9.6.2 and pg_log

On Mon, Apr 24, 2017 at 8:43 AM, Mark Watson 
mailto:mark.wat...@jurisconcept.ca>> wrote:
Good day all,

I just noticed an anomaly regarding the logging. I have my logging set up as 
follows:
log_filename = 'postgresql-%d.log'
log_truncate_on_rotation = on

​I don't see "log_rotation_age" and/or "log_rotation_size" here [1] and at 
least one needs to be set in order to enable actual rotation; the "truncate" 
option simply tells PostgreSQL what to do when encountering a file with the 
same name during the rotation process.​

log_rotation_age apparently has under-documented intelligence since I would 
expect a server that starts up mid-hour and uses a 60 minute rotation to rotate 
mid-hour as well so the log would contain 1 hours worth of data but the leading 
hours would be different.  The examples in log_truncate_on_rotation indicate 
that this isn't the case.  I have not tested reality or read the source.

This is on Windows 10, 64-bit
PostgreSQL 9.2.2, compiled by Visual C++ build 1800, 64-bit
(EnterpriseDB installer)

Note that this is not a major concern on my end; postgres 9.6.2 has otherwise 
been running flawlessly.


​Um...you're reporting a very outdated 9.2 release in the supposed copy-paste 
job above but claiming 9.6.2 ...

[1] 
https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHERE

David J.
Sorry about the version; I queried SELECT version() in pgAdmin4 and did not 
copy/paste the results. It is version 9.6.2.

Re: [GENERAL] Postgres 9.6.2 and pg_log

[Mark Watson]

De : David G. Johnston [mailto:david.g.johns...@gmail.com]
Envoyé : Monday, April 24, 2017 1:34 PM
À : Mark Watson
Cc : (pgsql-general@postgresql.org)
Objet : Re: [GENERAL] Postgres 9.6.2 and pg_log

On Mon, Apr 24, 2017 at 8:43 AM, Mark Watson 
mailto:mark.wat...@jurisconcept.ca>> wrote:
Good day all,

I just noticed an anomaly regarding the logging. I have my logging set up as 
follows:
log_filename = 'postgresql-%d.log'
log_truncate_on_rotation = on

​I don't see "log_rotation_age" and/or "log_rotation_size" here [1] and at 
least one needs to be set in order to enable actual rotation; the "truncate" 
option simply tells PostgreSQL what to do when encountering a file with the 
same name during the rotation process.​

log_rotation_age apparently has under-documented intelligence since I would 
expect a server that starts up mid-hour and uses a 60 minute rotation to rotate 
mid-hour as well so the log would contain 1 hours worth of data but the leading 
hours would be different.  The examples in log_truncate_on_rotation indicate 
that this isn't the case.  I have not tested reality or read the source.

This is on Windows 10, 64-bit
PostgreSQL 9.2.2, compiled by Visual C++ build 1800, 64-bit
(EnterpriseDB installer)

Note that this is not a major concern on my end; postgres 9.6.2 has otherwise 
been running flawlessly.


​Um...you're reporting a very outdated 9.2 release in the supposed copy-paste 
job above but claiming 9.6.2 ...

[1] 
https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHERE

David J.

Thanks, David,
The lines log_rotation_age and log_rotation_size are commented, and currently 
are:
#log_rotation_age = 1d # Automatic rotation of 
logfiles will

# happen after that time.  0 disables.
#log_rotation_size = 10MB  # Automatic rotation of 
logfiles will

# happen after that much log output.

# 0 disables.


I see from your reference article that the log_rotation_age is now in minutes, 
and I will adjust that to 1440 (1 day). I don’t know where the “1d” came from. 
I know it used to be like this in earlier versions.

Mark Watson