Re: Meaning of below statement

[David G. Johnston]

On Friday, November 20, 2020, Srinivasa T N  wrote:

> Hi,
>I have the following in my log files:
>
> 2020-11-20 11:20:46.216 IST [38207] LOG: execute S_1/C_2: SELECT
> "gid",encode(ST_AsBinary(ST_Simplify(ST_Force2D("shape"),
> 14.929338247701526, true)),'base64') as "shape" FROM
> "ami_smart_new"."aoi_boundary" WHERE ("sectioncode" IN ('4683', '4587') AND
> "sectioncode" IS NOT NULL AND "shape" && ST_GeomFromText('POLYGON
> ((683696.123647752 989199.9990667417, 683696.123647752 1000723.135701899,
> 708574.8226023088 1000723.135701899, 708574.8226023088 989199.9990667417,
> 683696.123647752 989199.9990667417))', 32643))
> 2020-11-20 11:20:46.218 IST [38207] LOG: execute S_3: ROLLBACK
>
> Does it mean that there was an error in "SELECT ..." and hence internally
> postgres executed ROLLBACK??
>
>
If there was an error you would see an error message.  Plus, PostgreSQL
doesn’t just internally issue a rollback on its own.  That said I’m not
sure what is being shown, or rather where the “execute” stuff comes from.

David J.

Re: Meaning of below statement

[Tim Clarke]

On 20/11/2020 08:29, David G. Johnston wrote:

On Friday, November 20, 2020, Srinivasa T N 
mailto:seen...@gmail.com>> wrote:
Hi,
   I have the following in my log files:

2020-11-20 11:20:46.216 IST [38207] LOG: execute S_1/C_2: SELECT 
"gid",encode(ST_AsBinary(ST_Simplify(ST_Force2D("shape"), 14.929338247701526, 
true)),'base64') as "shape" FROM "ami_smart_new"."aoi_boundary" WHERE 
("sectioncode" IN ('4683', '4587') AND "sectioncode" IS NOT NULL AND "shape" && 
ST_GeomFromText('POLYGON ((683696.123647752 989199.9990667417, 683696.123647752 
1000723.135701899, 708574.8226023088 1000723.135701899, 708574.8226023088 
989199.9990667417, 683696.123647752 989199.9990667417))', 32643))
2020-11-20 11:20:46.218 IST [38207] LOG: execute S_3: ROLLBACK

Does it mean that there was an error in "SELECT ..." and hence internally 
postgres executed ROLLBACK??


If there was an error you would see an error message.  Plus, PostgreSQL doesn’t 
just internally issue a rollback on its own.  That said I’m not sure what is 
being shown, or rather where the “execute” stuff comes from.

David J.



Given that PostgreSQL isn't throwing the error, I'd suggest the rollback is 
from a decision in the front-end application layer.


Tim Clarke MBCS
IT Director
Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420



Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: 
+49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848
Web: https://www.manifest.co.uk/



Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom



Copyright: This e-mail may contain confidential or legally privileged 
information. If you are not the named addressee you must not use or disclose 
such information, instead please report it to 
ad...@minerva.info
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: 
Registered in England Number 11260966 & The Manifest Voting Agency Ltd: 
Registered in England Number 2920820 Registered Office at above address. Please 
Click Here https://www.manifest.co.uk/legal/ for further information.

Determine if postgresql cluster running is primary or not

[Raul Kaubi]

Hi

CentOS 7
Postgres 9 to 12

For monitoring purpose, I would like that certain scripts are only run in
primary server.
So I am looking ways to universally discover if postgresql cluster that is
running is primary or not.

What would be the best way to achieve this?

Regards
Raul

Re: Determine if postgresql cluster running is primary or not

[Thomas Kellerer]

Raul Kaubi schrieb am 20.11.2020 um 09:53:
> CentOS 7
> Postgres 9 to 12
>
> For monitoring purpose, I would like that certain scripts are only run in 
> primary server.
> So I am looking ways to universally discover if postgresql cluster that is 
> running is primary or not.

As the standby will be in constant recovery, you can use

   select pg_is_in_recovery();

Re: Determine if postgresql cluster running is primary or not

[Paul Förster]

Hi Thomas,

> On 20. Nov, 2020, at 10:03, Thomas Kellerer  wrote:
> 
> Raul Kaubi schrieb am 20.11.2020 um 09:53:
>> CentOS 7
>> Postgres 9 to 12
>> 
>> For monitoring purpose, I would like that certain scripts are only run in 
>> primary server.
>> So I am looking ways to universally discover if postgresql cluster that is 
>> running is primary or not.
> 
> As the standby will be in constant recovery, you can use
> 
>   select pg_is_in_recovery();

I usually don't recommend using pg_is_in_recovery() only because a database 
cluster can be in recovery for other reasons. This is why I always do the 
following:

select distinct
case
when b.sender=0 and c.receiver=0 then
'Standalone'
when b.sender>0 and c.receiver=0 then
'Primary'   
when b.sender=0 and c.receiver>0 then
'Replica'   
when b.sender>0 and c.receiver>0 then
'Primary+Replica'   
end as pgrole
from
pg_database a,
(
select count(*) as sender
from pg_stat_replication 
) b,
(
select count(*) as receiver
from pg_stat_wal_receiver
) c
where
not a.datistemplate;

Cheers,
Paul

Re: Determine if postgresql cluster running is primary or not

[David G. Johnston]

On Friday, November 20, 2020, Paul Förster  wrote:
>
>
> > On 20. Nov, 2020, at 10:03, Thomas Kellerer  wrote:
>
> >
> >   select pg_is_in_recovery();
>
> I usually don't recommend using pg_is_in_recovery() only because a
> database cluster can be in recovery for other reasons. This is why I always
> do the following:
>

Do any of those other reasons allow connections that could execute that
function to exist?

David J.

AW: Linux package upgrade without dependency conflicts

[Zwettler Markus (OIZ)]

Any hints on this one?

Thanks, Markus



Von: Zwettler Markus (OIZ) 
Gesendet: Donnerstag, 19. November 2020 14:50
An: pgsql-general@lists.postgresql.org
Betreff: Linux package upgrade without dependency conflicts

We run Postgres 9.6 + 12 Community Edition on RHEL7 which we install directly 
out of the PGDG channels using RPMs. We also run Patroni installed with RPMs 
provided by Github.

Currently we have major dependency conflicts with each quarterly Linux package 
upgrade (yum upgrade), especially on PostGIS and Patroni.

I was told that there will be no dependency conflicts anymore when we install 
Postgres from sourcecode and Patroni with pip.

Is that correct? Because all Linux packages required by Postgres will continue 
to be updated.

-Markus

Re: Determine if postgresql cluster running is primary or not

[Raul Kaubi]

Hi

Thanks.
Seems like 9.5 does not work.

ERROR:  relation "pg_stat_wal_receiver" does not exist
> LINE 20: from pg_stat_wal_receiver


Any ide how to achieve this in 9.5 ?

Raul

Kontakt Paul Förster () kirjutas kuupäeval R, 20.
november 2020 kell 11:29:

> Hi Thomas,
>
> > On 20. Nov, 2020, at 10:03, Thomas Kellerer  wrote:
> >
> > Raul Kaubi schrieb am 20.11.2020 um 09:53:
> >> CentOS 7
> >> Postgres 9 to 12
> >>
> >> For monitoring purpose, I would like that certain scripts are only run
> in primary server.
> >> So I am looking ways to universally discover if postgresql cluster that
> is running is primary or not.
> >
> > As the standby will be in constant recovery, you can use
> >
> >   select pg_is_in_recovery();
>
> I usually don't recommend using pg_is_in_recovery() only because a
> database cluster can be in recovery for other reasons. This is why I always
> do the following:
>
> select distinct
> case
> when b.sender=0 and c.receiver=0 then
> 'Standalone'
> when b.sender>0 and c.receiver=0 then
> 'Primary'
> when b.sender=0 and c.receiver>0 then
> 'Replica'
> when b.sender>0 and c.receiver>0 then
> 'Primary+Replica'
> end as pgrole
> from
> pg_database a,
> (
> select count(*) as sender
> from pg_stat_replication
> ) b,
> (
> select count(*) as receiver
> from pg_stat_wal_receiver
> ) c
> where
> not a.datistemplate;
>
> Cheers,
> Paul
>
>

Re: received immediate shutdown request caused cluster failover

[Yi Sun]

Hello,

Thank you for your reply

Patroni replied this:

"It seems your system is under so much stress that there was no resources
for Patroni to execute HA loop for 35 seconds.
This interval exceeds ttl=30s, therefore the leader key expired, Patroni
noticed it and demoted Postgres.

You need to figure out what is going on with your system, and what is the
reason for cpu/memory pressure. Ideally fix these issues."

As company hundreds of clusters use ansible deployments use same
parameters, change parameters for 1 cluster is difficult

I just think maybe can get top sql from pg_stat_statements as below then
analyse and tuning

Is it correct direction? Any suggestions please, thanks

1 time IO SQL TOP 5
select userid::regrole, dbid, query from pg_stat_statements order by
(blk_read_time+blk_write_time)/calls desc limit 5;

total IO SQL TOP 5
select userid::regrole, dbid, query from pg_stat_statements order by
(blk_read_time+blk_write_time) desc limit 5;

1 time long SQL TOP 5
select userid::regrole, dbid, query from pg_stat_statements order by
mean_time desc limit 5;

total time long SQL TOP 5
select userid::regrole, dbid, query from pg_stat_statements order by
total_time desc limit 5;

average time long SQL TOP 5
select calls, total_time/calls AS avg_time, left(query,80) from
pg_stat_statements order by 2 desc limit 5;

stddev time SQL
select userid::regrole, dbid, query from pg_stat_statements order by
stddev_time desc limit 5;

share block SQL
select userid::regrole, dbid, query from pg_stat_statements order by
(shared_blks_hit+shared_blks_dirtied) desc limit 5;

temp blk SQL
select userid::regrole, dbid, query from pg_stat_statements order by
temp_blks_written desc limit 5;


Tom Lane  于2020年11月20日周五 下午2:17写道：

> Yi Sun  writes:
> > Besides command run(like pg_ctl) can cause "received immediate shutdown
> > request"  any other reason can cause this please?
>
> That message indicates that something sent the postmaster process a
> SIGQUIT signal (which is all that "pg_ctl stop -m immediate" does).
> There's no speculation to that: a look at postmaster.c will convince
> you that there is no other way to reach that message.  So you need
> to be looking for things that would be sending SIGQUIT unexpectedly.
>
> I don't know much about Patroni, but maybe something in that
> environment thinks that SIGQUIT'ing random processes is a good
> thing to do.
>
> regards, tom lane
>

Re: Determine if postgresql cluster running is primary or not

[Paul Förster]

Hi David,

> On 20. Nov, 2020, at 10:34, David G. Johnston  
> wrote:
> 
> 
> On Friday, November 20, 2020, Paul Förster  wrote:
> 
> > On 20. Nov, 2020, at 10:03, Thomas Kellerer  wrote:
> 
> > 
> >   select pg_is_in_recovery();
> 
> I usually don't recommend using pg_is_in_recovery() only because a database 
> cluster can be in recovery for other reasons. This is why I always do the 
> following:
> 
> Do any of those other reasons allow connections that could execute that 
> function to exist?

that always depends on what your application does. An application could still 
select a lot of things, maybe even wrongly so, even if the cluster is in 
recovery mode.

That was my idea when writing this query and it's been working fine for years 
now.

Cheers,
Paul

Re: Determine if postgresql cluster running is primary or not

[Paul Förster]

Hi Raul,

> On 20. Nov, 2020, at 10:41, Raul Kaubi  wrote:
> 
> Hi
> 
> Thanks.
> Seems like 9.5 does not work.
> 
> ERROR:  relation "pg_stat_wal_receiver" does not exist
> LINE 20: from pg_stat_wal_receiver
> 
> Any ide how to achieve this in 9.5 ?
> 
> Raul

this query is tested to work on 10.x and newer, not 9.x. I don't have 9.x, so I 
can't say, sorry.

Cheers,
Paul

Recovering old installation.

[Roger Wolff]

Hi, 

I have a little problem here... 

I had a hardware failure. Replaced the power supply and then the
system wouldn't boot. I eventually bought a new computer, new SSD for
a root/boot disk. Did a fresh install then moved over the storage
drives and went on my merry way 

Now... it seems after an earlier migration I had fogotten to move the
postgresql backup to the new postgresql installation. 

So now I have my database in version 9.5 in /oldroot/ somewhere
and a new instance of psql (Version 12) is running in the freshly
installed instance.

My plan-of-attack is to simply chroot to /oldroot, start the old 9.5
server run a psqldump and un-dump into the new server (have to figure
out how, but should be documented)

But the problem is: how do I start the server in the old root?

All searches for "chroot postgresql" turn up people who, for security
reasons, want to run postgresql inside a chroot environment.

All searches for "start postgresql" tell me to simply type "service
postgresql start". Great on a normal system, but not here:

abra2:/etc# service postgresql start
 * Starting PostgreSQL 9.5 database server  
 * The PostgreSQL server failed 
to start. Please check the log output:
2020-11-20 10:54:59 CET [2080825-1] FATAL:  could not open shared memory 
segment "/PostgreSQL.1552904327": Function not implemented

 [fail]
abra2:/etc# journalctl
No journal files were found.
-- No entries --

OK. One step further. Monted (bind) /dev onto /oldroot/dev and now the
message chages to "permission denied" as opposed to "function not
implemented".


One of the things I'd like to do is to start the server manually.

In /lib/systemd/system I find postgresql@.service that says: 

# systemd service template for PostgreSQL clusters. The actual instances will
# be called "postgresql@version-cluster", e.g. "postgresql@9.3-main". The
# variable %i expands to "version-cluster", %I expands to "version/cluster".
# (%I breaks for cluster names containing dashes.)

and: 

ExecStart=@/usr/bin/pg_ctlcluster postgresql@%i --skip-systemctl-redirect %i 
start

So, I figured out that my cluster is called "main" and my version
is 9.5. So now I expect that commandline to expand to: 

/usr/bin/pg_ctlcluster postgresql@9.5-main --skip-systemctl-redirect 9.5-main 
start

And when I try that I get: 
  Error: specified cluster does not exist

Now that error message is less useful than it could be. I don't know
if the postgresql@9.5-main or the 9.5-main is what it considers the
"specified cluster". Anyway, I tried a few variations but can't
get it to recognize the "specified cluster". 


abra2:/lib/systemd/system# pg_lsclusters
Ver Cluster Port Status OwnerData directory   Log file
9.5 main5432 down   postgres /var/lib/postgresql/9.5/main 
/var/log/postgresql/postgresql-9.5-main.log
abra2:/lib/systemd/system# 

Simplifying the commandline to: 

  /usr/bin/pg_ctlcluster 9.5-main start

it seems to do something... but then again: 
  FATAL:  could not open shared memory segment "/PostgreSQL.964036999": 
Permission denied

So then running strace -f to see what failed... No EPERM returns from
any system call. Sigh.

So... Any ideas for helping me get my server up-and-running for five
minutes to run a pgdump ?

Roger. 

-- 
** r.e.wo...@bitwizard.nl ** https://www.BitWizard.nl/ ** +31-15-2049110 **
**Delftechpark 11 2628 XJ  Delft, The Netherlands.  KVK: 27239233**
f equals m times a. When your f is steady, and your m is going down
your a is going up.  -- Chris Hadfield about flying up the space shuttle.

Re: Recovering old installation.

[Roger Wolff]

Update! (progress!)

It seems that:

  mount --bind /dev/shm /oldroot/dev/shm

did the trick. I now have a running server and I can connect
with the commandline tool. Now figure out the dump/restore
process. 

Leading up to this was starting postgres in my new system and looking
at the commandline of the running server and replicating that in the
chroot (changing version). Then strace showed EACCESS on
/dev/shm/PostgreSQL. which allowed further debugging, googling.

Roger.



On Fri, Nov 20, 2020 at 11:20:43AM +0100, Roger Wolff wrote:
> Hi, 
> 
> I have a little problem here... 
> 
> I had a hardware failure. Replaced the power supply and then the
> system wouldn't boot. I eventually bought a new computer, new SSD for
> a root/boot disk. Did a fresh install then moved over the storage
> drives and went on my merry way 
> 
> Now... it seems after an earlier migration I had fogotten to move the
> postgresql backup to the new postgresql installation. 
> 
> So now I have my database in version 9.5 in /oldroot/ somewhere
> and a new instance of psql (Version 12) is running in the freshly
> installed instance.
> 
> My plan-of-attack is to simply chroot to /oldroot, start the old 9.5
> server run a psqldump and un-dump into the new server (have to figure
> out how, but should be documented)
> 
> But the problem is: how do I start the server in the old root?
> 
> All searches for "chroot postgresql" turn up people who, for security
> reasons, want to run postgresql inside a chroot environment.
> 
> All searches for "start postgresql" tell me to simply type "service
> postgresql start". Great on a normal system, but not here:
> 
> abra2:/etc# service postgresql start
>  * Starting PostgreSQL 9.5 database server
>* The PostgreSQL server 
> failed to start. Please check the log output:
> 2020-11-20 10:54:59 CET [2080825-1] FATAL:  could not open shared memory 
> segment "/PostgreSQL.1552904327": Function not implemented
>   
>[fail]
> abra2:/etc# journalctl
> No journal files were found.
> -- No entries --
> 
> OK. One step further. Monted (bind) /dev onto /oldroot/dev and now the
> message chages to "permission denied" as opposed to "function not
> implemented".
> 
> 
> One of the things I'd like to do is to start the server manually.
> 
> In /lib/systemd/system I find postgresql@.service that says: 
> 
> # systemd service template for PostgreSQL clusters. The actual instances will
> # be called "postgresql@version-cluster", e.g. "postgresql@9.3-main". The
> # variable %i expands to "version-cluster", %I expands to "version/cluster".
> # (%I breaks for cluster names containing dashes.)
> 
> and: 
> 
> ExecStart=@/usr/bin/pg_ctlcluster postgresql@%i --skip-systemctl-redirect %i 
> start
> 
> So, I figured out that my cluster is called "main" and my version
> is 9.5. So now I expect that commandline to expand to: 
> 
> /usr/bin/pg_ctlcluster postgresql@9.5-main --skip-systemctl-redirect 9.5-main 
> start
> 
> And when I try that I get: 
>   Error: specified cluster does not exist
> 
> Now that error message is less useful than it could be. I don't know
> if the postgresql@9.5-main or the 9.5-main is what it considers the
> "specified cluster". Anyway, I tried a few variations but can't
> get it to recognize the "specified cluster". 
> 
> 
> abra2:/lib/systemd/system# pg_lsclusters
> Ver Cluster Port Status OwnerData directory   Log file
> 9.5 main5432 down   postgres /var/lib/postgresql/9.5/main 
> /var/log/postgresql/postgresql-9.5-main.log
> abra2:/lib/systemd/system# 
> 
> Simplifying the commandline to: 
> 
>   /usr/bin/pg_ctlcluster 9.5-main start
> 
> it seems to do something... but then again: 
>   FATAL:  could not open shared memory segment "/PostgreSQL.964036999": 
> Permission denied
> 
> So then running strace -f to see what failed... No EPERM returns from
> any system call. Sigh.
> 
> So... Any ideas for helping me get my server up-and-running for five
> minutes to run a pgdump ?
> 
>   Roger. 
> 
> -- 
> ** r.e.wo...@bitwizard.nl ** https://www.BitWizard.nl/ ** +31-15-2049110 **
> **Delftechpark 11 2628 XJ  Delft, The Netherlands.  KVK: 27239233**
> f equals m times a. When your f is steady, and your m is going down
> your a is going up.  -- Chris Hadfield about flying up the space shuttle.
> 
> 

-- 
** r.e.wo...@bitwizard.nl ** https://www.BitWizard.nl/ ** +31-15-2049110 **
**Delftechpark 11 2628 XJ  Delft, The Netherlands.  KVK: 27239233**
f equals m times a. When your f is steady, and your m is going down
your a is going up.  -- Chris Hadfield about flying up the space shuttle.

Re: Determine if postgresql cluster running is primary or not

[Raul Kaubi]

Hmm, ok.

But how is this possible..?

 when b.sender>0 and c.receiver>0 then
> 'Primary+Replica'


Raul

Kontakt Paul Förster () kirjutas kuupäeval R, 20.
november 2020 kell 12:04:

> Hi Raul,
>
> > On 20. Nov, 2020, at 10:41, Raul Kaubi  wrote:
> >
> > Hi
> >
> > Thanks.
> > Seems like 9.5 does not work.
> >
> > ERROR:  relation "pg_stat_wal_receiver" does not exist
> > LINE 20: from pg_stat_wal_receiver
> >
> > Any ide how to achieve this in 9.5 ?
> >
> > Raul
>
> this query is tested to work on 10.x and newer, not 9.x. I don't have 9.x,
> so I can't say, sorry.
>
> Cheers,
> Paul

Re: Determine if postgresql cluster running is primary or not

[Paul Förster]

Hi Raul,

> On 20. Nov, 2020, at 11:45, Raul Kaubi  wrote:
> 
> Hmm, ok.
> 
> But how is this possible..?
> 
>  when b.sender>0 and c.receiver>0 then
> 'Primary+Replica'
> 
> Raul

this happens for example if you have a primary a and replica b running as a 
normal cluster (we use Patroni for automatic failover) and then add another 
replica c to the existing replica b, effectively replicating: a => b => c In 
this case, b would be the replica of a, but also be the primary for c.

It's called cascading replication.

Cheers,
Paul

Re: Determine if postgresql cluster running is primary or not

[Raul Kaubi]

Ok, Thanks!

Raul

Kontakt Paul Förster () kirjutas kuupäeval R, 20.
november 2020 kell 12:54:

> Hi Raul,
>
> > On 20. Nov, 2020, at 11:45, Raul Kaubi  wrote:
> >
> > Hmm, ok.
> >
> > But how is this possible..?
> >
> >  when b.sender>0 and c.receiver>0 then
> > 'Primary+Replica'
> >
> > Raul
>
> this happens for example if you have a primary a and replica b running as
> a normal cluster (we use Patroni for automatic failover) and then add
> another replica c to the existing replica b, effectively replicating: a =>
> b => c In this case, b would be the replica of a, but also be the primary
> for c.
>
> It's called cascading replication.
>
> Cheers,
> Paul

Re: Set COLLATE on a session level

[Laurenz Albe]

On Fri, 2020-11-20 at 07:13 +, Dirk Mika wrote:
> we come from the Oracle world and we have an application that, depending on a 
> setting,
>  sends the command ALTER SESSION SET NLS_SORT=... when connecting to the 
> database.
> 
> Is there a similar way to set a COLLATE for a session in PostgreSQL?
> 
> I know that I can specify a COLLATE for a SELECT statement in the ORDER BY 
> Clause,
>  but then I would have to adjust the statements in the client and statements 
> that are
>  automatically generated by the database components used, would not be 
> affected.

There is no way to do that in PostgreSQL.

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

Re: Set COLLATE on a session level

[Dirk Mika]

Thank you for the quick reply. But how is this usually solved?

Let's assume there is an app that accesses the same database from different 
countries. And in this app data should be displayed ordered. And the sort order 
is not identical in all countries.

Does the app have to send different SQL commands depending on the country? Not 
nice.
Do the data have to be sorted in the app? Not nice either.

Regards
Dirk


--
Dirk Mika
Software Developer

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk.m...@mikatiming.de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika

Re: Set COLLATE on a session level

[Pavel Stehule]

Hi

pá 20. 11. 2020 v 15:28 odesílatel Dirk Mika 
napsal:

> Thank you for the quick reply. But how is this usually solved?
>
> Let's assume there is an app that accesses the same database from
> different countries. And in this app data should be displayed ordered. And
> the sort order is not identical in all countries.
>
> Does the app have to send different SQL commands depending on the country?
> Not nice.
> Do the data have to be sorted in the app? Not nice either.
>

the query is the same - you just use a different COLLATE clause. For
Postgres there is not any other way.

Regards

Pavel


> Regards
> Dirk
>
>
> --
> Dirk Mika
> Software Developer
>
> mika:timing GmbH
> Strundepark - Kürtener Str. 11b
> 51465 Bergisch Gladbach
> Germany
>
> fon +49 2202 2401-1197
> dirk.m...@mikatiming.de
> www.mikatiming.de
>
> AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
> Geschäftsführer: Harald Mika, Jörg Mika
>
>
>

Re: Set COLLATE on a session level

[Karsten Hilbert]

On Fri, Nov 20, 2020 at 03:32:48PM +0100, Pavel Stehule wrote:

> pá 20. 11. 2020 v 15:28 odesílatel Dirk Mika 
> napsal:
>
> > Let's assume there is an app that accesses the same database from
> > different countries. And in this app data should be displayed ordered. And
> > the sort order is not identical in all countries.
> >
> > Does the app have to send different SQL commands depending on the country?
> > Not nice.
> > Do the data have to be sorted in the app? Not nice either.
> >
>
> the query is the same - you just use a different COLLATE clause. For
> Postgres there is not any other way.

One might use a function producing a SELECT taking the locale as a parameter.

Or views in schemas per locale. Selecting the search path
per locale pulls in the right view.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Re: AW: Linux package upgrade without dependency conflicts

[Adrian Klaver]

On 11/20/20 1:38 AM, Zwettler Markus (OIZ) wrote:

Any hints on this one?


Are you sure the Postgres packages are coming from the PGDG repos and 
not the RH repos?


What are the dependency conflicts?




Thanks, Markus

*Von:*Zwettler Markus (OIZ) 
*Gesendet:* Donnerstag, 19. November 2020 14:50
*An:* pgsql-general@lists.postgresql.org
*Betreff:* Linux package upgrade without dependency conflicts

We run Postgres 9.6 + 12 Community Edition on RHEL7 which we install 
directly out of the PGDG channels using RPMs. We also run Patroni 
installed with RPMs provided by Github.


Currently we have major dependency conflicts with each quarterly Linux 
package upgrade (yum upgrade), especially on PostGIS and Patroni.


I was told that there will be no dependency conflicts anymore when we 
install Postgres from sourcecode and Patroni with pip.


Is that correct? Because all Linux packages required by Postgres will 
continue to be updated.


-Markus




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

Re: Linux package upgrade without dependency conflicts

[Magnus Hagander]

On Thu, Nov 19, 2020 at 2:50 PM Zwettler Markus (OIZ)
 wrote:
>
> We run Postgres 9.6 + 12 Community Edition on RHEL7 which we install directly 
> out of the PGDG channels using RPMs. We also run Patroni installed with RPMs 
> provided by Github.
>
>
>
> Currently we have major dependency conflicts with each quarterly Linux 
> package upgrade (yum upgrade), especially on PostGIS and Patroni.
>
>
>
> I was told that there will be no dependency conflicts anymore when we install 
> Postgres from sourcecode and Patroni with pip.
>
>
>
> Is that correct? Because all Linux packages required by Postgres will 
> continue to be updated.

This is not really a PostgreSQL question, it's more of a RedHat
question I'd say.

In general, no. If you install from source you will have a different
kind of dependency management, and you need to handle all of that
manually. As long as you do, there shouldn't be issues.

That said, what are your dependency conflicts? As long as you're using
the PGDG repositories on RHEL7 it should work without that. There have
been some issues with PostGIS on RHEL8, but I think they are mostly
fine on RHEL7. But if you don't actually show us what your dependency
problems are, we can't tell you how to fix it...

(And why not use Patroni from the PDGD repositories?)


-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

Re: Meaning of below statement

[Tom Lane]

"David G. Johnston"  writes:
> On Friday, November 20, 2020, Srinivasa T N  wrote:
>> 2020-11-20 11:20:46.218 IST [38207] LOG: execute S_3: ROLLBACK

> If there was an error you would see an error message.  Plus, PostgreSQL
> doesn’t just internally issue a rollback on its own.  That said I’m not
> sure what is being shown, or rather where the “execute” stuff comes from.

The "execute" says this is an execution of a previously prepared query,
ie the client is using extended query protocol rather than simple.
It's got nothing much to do with the semantics though.  It seems clear
that the client chose to roll back the transaction; the server did not
do that.

regards, tom lane

AW: AW: Linux package upgrade without dependency conflicts

[Zwettler Markus (OIZ)]

Yes. Pretty sure. PGDG repos.

The last problems I remember had been related to Patroni + python-psycopg and 
PostGIS + pgrouting_96.

Regards, Markus



> -Ursprüngliche Nachricht-
> Von: Adrian Klaver 
> Gesendet: Freitag, 20. November 2020 16:26
> An: Zwettler Markus (OIZ) ; pgsql-
> gene...@lists.postgresql.org
> Betreff: Re: AW: Linux package upgrade without dependency conflicts
> 
> On 11/20/20 1:38 AM, Zwettler Markus (OIZ) wrote:
> > Any hints on this one?
> 
> Are you sure the Postgres packages are coming from the PGDG repos and not the
> RH repos?
> 
> What are the dependency conflicts?
> 
> 
> >
> > Thanks, Markus
> >
> > *Von:*Zwettler Markus (OIZ) 
> > *Gesendet:* Donnerstag, 19. November 2020 14:50
> > *An:* pgsql-general@lists.postgresql.org
> > *Betreff:* Linux package upgrade without dependency conflicts
> >
> > We run Postgres 9.6 + 12 Community Edition on RHEL7 which we install
> > directly out of the PGDG channels using RPMs. We also run Patroni
> > installed with RPMs provided by Github.
> >
> > Currently we have major dependency conflicts with each quarterly Linux
> > package upgrade (yum upgrade), especially on PostGIS and Patroni.
> >
> > I was told that there will be no dependency conflicts anymore when we
> > install Postgres from sourcecode and Patroni with pip.
> >
> > Is that correct? Because all Linux packages required by Postgres will
> > continue to be updated.
> >
> > -Markus
> >
> 
> 
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

AW: Linux package upgrade without dependency conflicts

[Zwettler Markus (OIZ)]

> -Ursprüngliche Nachricht-
> Von: Magnus Hagander 
> Gesendet: Freitag, 20. November 2020 16:29
> An: Zwettler Markus (OIZ) 
> Cc: pgsql-general@lists.postgresql.org
> Betreff: Re: Linux package upgrade without dependency conflicts
> 
> On Thu, Nov 19, 2020 at 2:50 PM Zwettler Markus (OIZ)
>  wrote:
> >
> > We run Postgres 9.6 + 12 Community Edition on RHEL7 which we install 
> > directly
> out of the PGDG channels using RPMs. We also run Patroni installed with RPMs
> provided by Github.
> >
> >
> >
> > Currently we have major dependency conflicts with each quarterly Linux 
> > package
> upgrade (yum upgrade), especially on PostGIS and Patroni.
> >
> >
> >
> > I was told that there will be no dependency conflicts anymore when we 
> > install
> Postgres from sourcecode and Patroni with pip.
> >
> >
> >
> > Is that correct? Because all Linux packages required by Postgres will 
> > continue to
> be updated.
> 
> This is not really a PostgreSQL question, it's more of a RedHat question I'd 
> say.
> 
> In general, no. If you install from source you will have a different kind of
> dependency management, and you need to handle all of that manually. As long as
> you do, there shouldn't be issues.
> 
> That said, what are your dependency conflicts? As long as you're using the 
> PGDG
> repositories on RHEL7 it should work without that. There have been some issues
> with PostGIS on RHEL8, but I think they are mostly fine on RHEL7. But if you 
> don't
> actually show us what your dependency problems are, we can't tell you how to 
> fix
> it...
> 
> (And why not use Patroni from the PDGD repositories?)
> 
> 
> --
>  Magnus Hagander
>  Me: https://www.hagander.net/
>  Work: https://www.redpill-linpro.com/


The last problems I remember had been related to Patroni + python-psycopg and 
PostGIS + pgrouting_96.

We used Patroni before it had been provided by the PGDG repositories. I am 
actually planning to use the RPMs from the PGDG repo in the future.

I will post our actual problems the next time.

Thanks, Markus

Re: Determine if postgresql cluster running is primary or not

[David G. Johnston]

On Friday, November 20, 2020, Paul Förster  wrote:

> Hi David,
>
> > On 20. Nov, 2020, at 10:34, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
> >
> >
> > On Friday, November 20, 2020, Paul Förster 
> wrote:
> >
> > > On 20. Nov, 2020, at 10:03, Thomas Kellerer  wrote:
> >
> > >
> > >   select pg_is_in_recovery();
> >
> > I usually don't recommend using pg_is_in_recovery() only because a
> database cluster can be in recovery for other reasons. This is why I always
> do the following:
> >
> > Do any of those other reasons allow connections that could execute that
> function to exist?
>
> that always depends on what your application does. An application could
> still select a lot of things, maybe even wrongly so, even if the cluster is
> in recovery mode.


I don’t follow - i posit that if psql successfully connects to a server
that reports it is is recovery that server is a secondary to some other
server, period.  Can you provide a counter-example for when that isn’t true
(given the whole psql connects successfully bit).

David J.

pg_dump - how to force to show timestamps in client log

[Durumdara]

Hello!

We need to log the pg_dump's state.
What objects are in copy, and what are the starting and ending times.

But when I try to redirect the output, the result doesn't have timestamps.

PG 11, on Windows.

As I see the -v option isn't enough to see the starting times.

For example:

2020-11-19 12:00:01.084 Dump table content table1
2020-11-19 12:03:12.932 Dump table content table2
...
etc.


Thank you for any information you can provide!

dd

Re: pg_dump - how to force to show timestamps in client log

[Joshua Drake]

Howdy,

pg_dump is just issuing SQL commands, you could turn on timestamps in your
postgresql log.

JD

On Fri, Nov 20, 2020 at 10:02 AM Durumdara  wrote:

> Hello!
>
> We need to log the pg_dump's state.
> What objects are in copy, and what are the starting and ending times.
>
> But when I try to redirect the output, the result doesn't have timestamps.
>
> PG 11, on Windows.
>
> As I see the -v option isn't enough to see the starting times.
>
> For example:
>
> 2020-11-19 12:00:01.084 Dump table content table1
> 2020-11-19 12:03:12.932 Dump table content table2
> ...
> etc.
>
>
> Thank you for any information you can provide!
>
> dd
>
>
>

Re: pg_dump - how to force to show timestamps in client log

[Adrian Klaver]

On 11/20/20 10:01 AM, Durumdara wrote:

Hello!

We need to log the pg_dump's state.
What objects are in copy, and what are the starting and ending times.

But when I try to redirect the output, the result doesn't have timestamps.

PG 11, on Windows.

As I see the -v option isn't enough to see the starting times.

For example:

2020-11-19 12:00:01.084 Dump table content table1
2020-11-19 12:03:12.932 Dump table content table2
...
etc.



If you are redirecting to a file it have the creation time that you can 
use. Internally times don't really matter for the objects as the dump is 
based on a snapshot. Said snapshot is based on visible transactions not 
time. So for practical purposes they all occur at the same 'time'.





Thank you for any information you can provide!

dd





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

Re: pg_dump - how to force to show timestamps in client log

[Ron]

On 11/20/20 2:56 PM, Adrian Klaver wrote:

On 11/20/20 10:01 AM, Durumdara wrote:

Hello!

We need to log the pg_dump's state.
What objects are in copy, and what are the starting and ending times.

But when I try to redirect the output, the result doesn't have timestamps.

PG 11, on Windows.

As I see the -v option isn't enough to see the starting times.

For example:

2020-11-19 12:00:01.084 Dump table content table1
2020-11-19 12:03:12.932 Dump table content table2
...
etc.



If you are redirecting to a file it have the creation time that you can 
use. Internally times don't really matter for the objects as the dump is 
based on a snapshot. Said snapshot is based on visible transactions not 
time. So for practical purposes they all occur at the same 'time'.


It makes all the difference when monitoring the progress of a backup.

--
Angular momentum makes the world go 'round.

Re: pg_dump - how to force to show timestamps in client log

[Adrian Klaver]

On 11/20/20 1:00 PM, Ron wrote:

On 11/20/20 2:56 PM, Adrian Klaver wrote:

On 11/20/20 10:01 AM, Durumdara wrote:

Hello!

We need to log the pg_dump's state.
What objects are in copy, and what are the starting and ending times.

But when I try to redirect the output, the result doesn't have 
timestamps.


PG 11, on Windows.

As I see the -v option isn't enough to see the starting times.

For example:

2020-11-19 12:00:01.084 Dump table content table1
2020-11-19 12:03:12.932 Dump table content table2
...
etc.



If you are redirecting to a file it have the creation time that you 
can use. Internally times don't really matter for the objects as the 
dump is based on a snapshot. Said snapshot is based on visible 
transactions not time. So for practical purposes they all occur at the 
same 'time'.


It makes all the difference when monitoring the progress of a backup.



With -v you will get running list of objects dumped, just not the time. 
The time is only of value relative to the following. Progress will only 
be measurable by determining what is left to run and the time for each 
object. Not sure that is feasible as you would have to pre-run the dump 
to get information about the number of objects and an estimate of the 
data quantity involved and the effect of each on the other. I could see 
that estimate getting worse the bigger the data set(and hence the more 
you cared) got. Because at some point the load on the machine would 
affect the output speed of the dump.



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

Re: pg_dump - how to force to show timestamps in client log

[Ron]

On 11/20/20 3:39 PM, Adrian Klaver wrote:

On 11/20/20 1:00 PM, Ron wrote:

On 11/20/20 2:56 PM, Adrian Klaver wrote:

On 11/20/20 10:01 AM, Durumdara wrote:

Hello!

We need to log the pg_dump's state.
What objects are in copy, and what are the starting and ending times.

But when I try to redirect the output, the result doesn't have timestamps.

PG 11, on Windows.

As I see the -v option isn't enough to see the starting times.

For example:

2020-11-19 12:00:01.084 Dump table content table1
2020-11-19 12:03:12.932 Dump table content table2
...
etc.



If you are redirecting to a file it have the creation time that you can 
use. Internally times don't really matter for the objects as the dump is 
based on a snapshot. Said snapshot is based on visible transactions not 
time. So for practical purposes they all occur at the same 'time'.


It makes all the difference when monitoring the progress of a backup.



With -v you will get running list of objects dumped, just not the time. 
The time is only of value relative to the following. Progress will only be 
measurable by determining what is left to run and the time for each 
object. Not sure that is feasible as you would have to pre-run the dump to 
get information about the number of objects and an estimate of the data 
quantity involved and the effect of each on the other. I could see that 
estimate getting worse the bigger the data set(and hence the more you 
cared) got. Because at some point the load on the machine would affect the 
output speed of the dump.


By knowing the sizes of the tables, and how long it takes to takes the first 
"some" tables, then one can forecast how long it takes to backup the whole 
database.


--
Angular momentum makes the world go 'round.

Re: pg_dump - how to force to show timestamps in client log

[Mark Johnson]

This all sounds like a previous discussion on pg hackers about a progress
meter for pg_dump.  Search the archives for that discussion.  Also, search
the web for something like "pg_dump progress meter" and you'll get a few
suggestions like pipe to pv, although that does not appear to work with all
of the file formats supported by pg_dump.

What do you see in pg_stat_activity?  It's been a while since I tried to
monitor a running pg_dump.

 Also, if you redirect the output to a file then doesn't the file's
timestamp get updated each time something happens.  (That's what I used to
do with Oracle before they added timestamps).

On Fri, Nov 20, 2020 at 5:00 PM Ron  wrote:

> On 11/20/20 3:39 PM, Adrian Klaver wrote:
> > On 11/20/20 1:00 PM, Ron wrote:
> >> On 11/20/20 2:56 PM, Adrian Klaver wrote:
> >>> On 11/20/20 10:01 AM, Durumdara wrote:
>  Hello!
> 
>  We need to log the pg_dump's state.
>  What objects are in copy, and what are the starting and ending times.
> 
>  But when I try to redirect the output, the result doesn't have
> timestamps.
> 
>  PG 11, on Windows.
> 
>  As I see the -v option isn't enough to see the starting times.
> 
>  For example:
> 
>  2020-11-19 12:00:01.084 Dump table content table1
>  2020-11-19 12:03:12.932 Dump table content table2
>  ...
>  etc.
> >>>
> >>>
> >>> If you are redirecting to a file it have the creation time that you
> can
> >>> use. Internally times don't really matter for the objects as the dump
> is
> >>> based on a snapshot. Said snapshot is based on visible transactions
> not
> >>> time. So for practical purposes they all occur at the same 'time'.
> >>
> >> It makes all the difference when monitoring the progress of a backup.
> >>
> >
> > With -v you will get running list of objects dumped, just not the time.
> > The time is only of value relative to the following. Progress will only
> be
> > measurable by determining what is left to run and the time for each
> > object. Not sure that is feasible as you would have to pre-run the dump
> to
> > get information about the number of objects and an estimate of the data
> > quantity involved and the effect of each on the other. I could see that
> > estimate getting worse the bigger the data set(and hence the more you
> > cared) got. Because at some point the load on the machine would affect
> the
> > output speed of the dump.
>
> By knowing the sizes of the tables, and how long it takes to takes the
> first
> "some" tables, then one can forecast how long it takes to backup the whole
> database.
>
> --
> Angular momentum makes the world go 'round.
>
>
>

Restoring Database on Version 11 does not restore database comment

[George Weaver]

Good afternoon,

If I backup a PostgreSQL 10.14 database using pg_dump from Version 
11.10, and restore to Version 11, the comment for the database is lost.


I haven't encountered this before when upgrading between major versions.

Test case:

C:\Program Files\PostgreSQL\10\bin>psql
psql (10.14)

postgres=# CREATE DATABASE test_comment TEMPLATE = template0;
CREATE DATABASE
postgres=# COMMENT ON DATABASE test_comment IS 'Test Comment Database';
COMMENT
postgres=# SELECT pg_catalog.shobj_description(d.oid, 'pg_database') as 
"Version"

postgres-# FROM pg_catalog.pg_database d
postgres-#   where d.datname = 'test_comment';
    Version
---
 Test Comment Database
(1 row)

C:\Program Files\PostgreSQL\11\bin>pg_dump -h localhost -p 5433 -U 
postgres -f "G:\test_comment.bak" -Fc -O test_comment


C:\Program Files\PostgreSQL\11\bin>psql -p 5435
psql (11.10)

postgres=# CREATE DATABASE test_comment TEMPLATE = template0;
CREATE DATABASE
postgres=# \q

C:\Program Files\PostgreSQL\11\bin>pg_Restore -p 5435  -U postgres -d 
test_comment "G:\test_comment.bak"


C:\Program Files\PostgreSQL\11\bin>psql -p 5435
psql (11.10)

postgres=# SELECT pg_catalog.shobj_description(d.oid, 'pg_database') as 
"Version"

postgres-# FROM pg_catalog.pg_database d
postgres-#   where d.datname = 'test_comment';
 Version
-

(1 row)

What am I missing?

Thanks,
George

Re: Restoring Database on Version 11 does not restore database comment

[David G. Johnston]

On Fri, Nov 20, 2020 at 3:58 PM George Weaver  wrote:

> What am I missing?
>
>
Release notes.

https://www.postgresql.org/docs/11/release-11.html

"pg_dump and pg_restore, without --create, no longer dump/restore
database-level comments and security labels; those are now treated as
properties of the database."

David J.

Re: Restoring Database on Version 11 does not restore database comment

[Bruce Momjian]

On Fri, Nov 20, 2020 at 04:01:26PM -0700, David G. Johnston wrote:
> On Fri, Nov 20, 2020 at 3:58 PM George Weaver  wrote:
> 
> What am I missing?
> 
> 
> 
> Release notes.
> 
> https://www.postgresql.org/docs/11/release-11.html
> 
> "pg_dump and pg_restore, without --create, no longer dump/restore
> database-level comments and security labels; those are now treated as
> properties of the database."

Yeah, I realize this new behavior is kind of odd, but logically, it
makes sense.

-- 
  Bruce Momjian  https://momjian.us
  EnterpriseDB https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

Re: Restoring Database on Version 11 does not restore database comment

[George Weaver]

Thanks David!

Missed that...

On 20/11/2020 5:01 p.m., David G. Johnston wrote:
On Fri, Nov 20, 2020 at 3:58 PM George Weaver > wrote:


What am I missing?


Release notes.

https://www.postgresql.org/docs/11/release-11.html 



"pg_dump and pg_restore, without --create, no longer dump/restore 
database-level comments and security labels; those are now treated as 
properties of the database."


David J.


--
Cleartag Software, Inc.
972 McMillan Avenue
Winnipeg, MB
R3M 0V7
(204) 284-9839 phone/cell
(204) 284-9838 fax
gwea...@cleartagsoftware.com

Fast. Accurate. Easy.