Query planner riddle (array-related?)

[Markus]

Hi,

I'm running Postgresql 9.6.7 on Debian stretch -- and I'm trying to
understand a query plan, with any hint where to gain further insight
welcome.

Situation: Two tables, both with a bigint source_id as primary key:

gaia.dr2light -- roughly 1.6 billion rows, about 30 columns, all of them
reals, double precisions, bigints.

gaia.dr2epochflux -- roughly 0.5 million rows, about 15 columns, 10 of
which are arrays, typically real[] or double precision[] with 10 .. 30
array elements each.

The source_ids in dr2epochflux are a small subset of the ones in
dr2light.  Also, dr2light has an index on a column called parallax, and 

  select count(*) from gaia.dr2light where parallax>50;

gives 5400 rows in no time.  The distribution is such that this will
actually touch 5400 disk blocks on gaia.dr2light.  Both tables are read
only (i.e., have been ingested and analyzed once and remained unchanged
ever since).

The story:

SELECT * 
FROM gaia.dr2epochflux 
JOIN gaia.dr2light
USING (source_id)
WHERE parallax>50

(query 1) is horribly slow (like, many minutes).  Forcing the planner to
do the right thing, like this:

SELECT * 
FROM gaia.dr2epochflux 
JOIN (SELECT * FROM gaia.dr2light
  WHERE parallax>50 OFFSET 0) AS q
USING (source_id)

(query 2) makes the query execute in about a second (and yields 18 rows).

Why would Postgres choose a dramatically inferior plan?  Here's what
EXPLAIN gives for query 2 (the good plan):


 Hash Join  (cost=472743.04..26313612.35 rows=551038 width=1647)
   Hash Cond: (q.source_id = dr2epochflux.source_id)
   ->  Subquery Scan on q  (cost=243173.69..25417932.01 rows=12991627 width=132)
 ->  Bitmap Heap Scan on dr2light  (cost=243173.69..25288015.74 
rows=12991627 width=132)
   Recheck Cond: (parallax > '50'::double precision)
   ->  Bitmap Index Scan on dr2light_parallax  
(cost=0.00..239925.78 rows=12991627 width=0)
 Index Cond: (parallax > '50'::double precision)
   ->  Hash  (cost=118285.38..118285.38 rows=551038 width=1523)
 ->  Seq Scan on dr2epochflux  (cost=0.00..118285.38 rows=551038 
width=1523)


And here's the bad plan (for query 1):

--
 Nested Loop  (cost=0.58..4801856.96 rows=4229 width=1647)
   ->  Seq Scan on dr2epochflux  (cost=0.00..118285.38 rows=551038 width=1523)
   ->  Index Scan using dr2light_pkey on dr2light  (cost=0.58..8.49 rows=1 
width=132)
 Index Cond: (source_id = dr2epochflux.source_id)
 Filter: (parallax > '50'::double precision)

If I enable_seqscan=0, it comes up with this for query 1:

-
 Nested Loop  (cost=1.00..4810726.18 rows=4229 width=1647)
   ->  Index Scan using dr2epochflux_pkey on dr2epochflux  
(cost=0.42..127154.60 rows=551038 width=1523)
   ->  Index Scan using dr2light_pkey on dr2light  (cost=0.58..8.49 rows=1 
width=132)
 Index Cond: (source_id = dr2epochflux.source_id)
 Filter: (parallax > '50'::double precision)

-- which in reality appears to be a good deal faster than the "bad"
plan, though still much, much slower than the "good plan".

Both tables are ANALYZE-d, and they should be reasonably VACUUMED.

Is there anything I can do to make it easier for the planner to see the
light?

   -- Markus

Re: Query planner riddle (array-related?)

[Markus]

Hi Tom,

On Fri, May 04, 2018 at 09:32:08AM -0400, Tom Lane wrote:
> Markus  writes:
> > I'm running Postgresql 9.6.7 on Debian stretch -- and I'm trying to
> > understand a query plan, with any hint where to gain further insight
> > welcome.
> 
> Well, you say
> 
> >   select count(*) from gaia.dr2light where parallax>50;
> > gives 5400 rows in no time.
> 
> but the planner thinks there are 12991627 such rows:

Ah... yeah, the parallax distribution is fairly sharply peaked around
0, so >50 might be severely off.

So, I've run

  alter table gaia.dr2light alter parallax set statistics 1000;
  analyze gaia.dr2light;

and lo and behold, the both queries become a good deal faster (a
couple of seconds).  That's essentially enough to make me happy --
thanks!

> Also, this sort of thing is usually much easier to diagnose from
> EXPLAIN ANALYZE output.  All we can see from these queries is that
> the planner picked what it thought was the lowest-cost plan.  Without
> actual rowcounts it's very hard to guess why the estimates were wrong.
> You happened to provide one actual-rowcount number that maybe was
> enough to diagnose the issue; but if the above doesn't do the trick,
> we're going to need to see EXPLAIN ANALYZE to guess what else is up.

With this, the query plans converge to trivial variations of

 Hash Join  (cost=253856.23..4775113.84 rows=422 width=1647) (actual 
time=1967.095..2733.109 rows=18 loops=1)
   Hash Cond: (dr2light.source_id = dr2epochflux.source_id)
   ->  Bitmap Heap Scan on dr2light  (cost=24286.88..4385601.28 rows=1297329 
width=132) (actual time=3.113..19.346 rows=5400 loops=1)
 Recheck Cond: (parallax > '50'::double precision)
 Heap Blocks: exact=5184
 ->  Bitmap Index Scan on dr2light_parallax  (cost=0.00..23962.54 
rows=1297329 width=0) (actual time=1.721..1.721 rows=5400 loops=1)
   Index Cond: (parallax > '50'::double precision)
   ->  Hash  (cost=118285.38..118285.38 rows=551038 width=1523) (actual 
time=1885.177..1885.177 rows=550737 loops=1)
 Buckets: 65536  Batches: 16  Memory Usage: 53292kB
 ->  Seq Scan on dr2epochflux  (cost=0.00..118285.38 rows=551038 
width=1523) (actual time=0.008..430.692 rows=550737 loops=1)
 Planning time: 6.504 ms
 Execution time: 2733.653 ms

(with 10% or so jitter in the actual times, obviously); this one is
for

  SELECT *
  FROM gaia.dr2epochflux
  JOIN gaia.dr2light
  USING (source_id)
  WHERE parallax>50


While that's a reasonable plan and fast enough, I'd still like to
keep the box from seqscanning dr2epochflux with its large arrays and
use that table's index on source_id.  If I read the query plan right,
part of the problem is that it still massively overestimates the
result of parallax>50 (1297329 rather than 5400).  Is there anything
I can do to improve that estimate?

But even with that suboptimal estimate, postgres, under the
assumption of something not too far from a uniform distribution on
source_id, should end up estimating the cardinality of the end result
as something like

(selectivity on dr2light)*(cardinality of dr2epochflux),

and hence roughly (1297329/1.6e9*5e5)=405 rows to be drawn from
dr2epochflux.  It would seem a lot smarter to just pull these few 1e2
rows using the source_id index on dr2epochflux than seqscanning that
table, no?

Btw., I've raised the statistics target on dr2light to 1000 for
source_id; so, postgres should know source_id isn't uniformly
distributed, but it should also see it's not *that* far away from it.


   -- Markus

How to make runtime partition pruning work?

[Markus Heiden]

I partitioned a table "data_table" by the key "import_id" to reduce the 
number of partitions to be loaded in my queries.
I used list partitions, each containing usually just one "import_id". I 
used a primary key (id, import_id)
But PostgreSQL does not consider partition keys to avoid loading not 
needed partitions.


My query:
SELECT SUM(something) FROM data_table WHERE import_id IN (SELECT id FROM 
import_table WHERE ...)

My problem:
The query takes too long, because PostgreSQL uses a hash join over all 
partitions of "data_table" with the "import_table", instead of pruning 
the "data_table" partitions by the import_ids at runtime.

Static pruning (when using ... IN (1, 2, 3, 4)) works fine though.

What am I doing wrong that runtime partition pruning with PostgreSQL 
11.5 does not work in my case?


Thanks,
Markus

Re: How to make runtime partition pruning work?

[Markus Heiden]

Notice that only subqueries and parameterized nested loop joins are
mentioned.  The above text does not really go into the detail of which
types of subqueries can be used, but I can confirm that they must be
subqueries that can only return a scalar value. e.g WHERE x = (SELECT
y FROM ...).  The executor would raise an error if that subquery
returned more than 1 row. The IN() clause you have is not eligible.
This will be converted into a semi-join during planning, and even if
it wasn't, the executor wouldn't be raising an error if it returned
multiple rows.

Unfortunately, to do what you mention with a hash join, we'd need to
scan through the entire hash table and incrementally build the set of
partitions which could match each value in the table.  I'm sure there
are times where that would be well worth the trouble, but I imagine in
the average case we might find that the cost of scanning the entire
table this way would be more than just executing the query without any
partition pruning. I don't see any good way to know in advance if it
would be worthwhile or not.


First thanks for the detailed answer. Now at least I known that I can't 
make it work this way.


What I do not understand is, why for the IN() clause a hash join will be 
used in this case (instead of a semi join too)?


In this case (x is the partition key) using a semi join would be optimal 
for IN() clauses too IMHO.
This way only the query providing the y's has to executed first to 
provide the partition keys to filter.
In other cases where x is not the partition key, I agree with your 
argumentation.

Re: install postgres

[Thomas Markus]

Hi,

there's a section "Direct RPM download" ;)
Or https://download.postgresql.org/pub/repos/yum/12/redhat/

regards
Thomas

Am 20.12.19 um 10:24 schrieb Marc Millas:

Hi,

I may overlook something obvious..
I am just looking, on the download pages of postgresql.org 


for a way to download rpm.(for  redhat 6 and  redhat 7 x64 machines)
NOT the noarch, but the full distro.
Reason is I have to install postgres on a network with NO internet access.

Thanks,

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com 

Re: Practical usage of large objects.

[Thomas Markus]

Am 14.05.20 um 15:36 schrieb Laurenz Albe:

Interesting; only recently I played with that a little and found that
that is not necessarily true:

https://www.cybertec-postgresql.com/en/binary-data-performance-in-postgresql/

Yours,
Laurenz Albe
We used lo a lot in a project for large uploads (>4GB files). Really 
useful in a cloud environment.


I was interested in speed camparison myself and made a similar test with 
network connection and without pg specific code.

https://github.com/5UtJAjiRWj1q/psql-lob-performance

File access is really fast and lo access is much slower than bytea (as 
expected). But content size limitation and memory consumption for bytea 
is problematic.


regards
Thomas

Re: Parallelism on Partitioning .

[Thomas Markus]

Hi


Hi ,

We are trying to assign the parallel worker or execute the query in 
parallel manner on partitioned Tables and partitioned master table but 
not able to do that ,could u plz suggest .


Current Postgresql version :- 9.6

Fyi, We have tried with all parameters which can allow the parallel 
query execution.


Thanks..


Your PG version is too old. 9.6 is not able to this (as I remember)

Thomas

Binding Postgres to port 0 for testing

[Markus Pilman]

Hi all,

I am building a simple integration test framework for an application that I
am building. For this project I am planning to use PostgreSQL.

For testing purposes I'd like to always start with an empty database,
populate data, and, if the test was successful, delete everything. These
tests are single process tests.

I'd like to run many processes in parallel and have one postgres server
process running for each. I realize that I could use one and use multiple
databases but I don't want to do this for a variety of reasons (one being
that I also want to test the control plane logic, the application is
multi-tenant and uses a database per tenant, having separate databases
simplifies debugging etc).

Now the problem is that I need to find a TCP port for each running postgres
instance. There's multiple ways to do this, but by far the easiest one I
know is to bind to port 0. So my plan was to start postgres with "-p 0" and
then parse stdout to figure out which port it actually uses. But that
doesn't seem to work:

 >postgres -D data/ -p 0
 2023-03-25 16:39:54.271 GMT [13924] FATAL:  0 is outside the valid range
for parameter "port" (1 .. 65535)

Is there a reason this is not allowed? What would be the recommended way of
addressing my issue?

Best Markus

Re: Binding Postgres to port 0 for testing

[Markus Pilman]

Thanks for the suggestions. I didn't know about pg_virtualenv, that's
interesting. Though it seems to achieve something similar as to
containerize the test (potentially in a more platform independent way).
Though it seems pg_virtualenv is mostly doing what my test driver is
currently doing. Trying out the ports is obviously possible, but it seems a
bit hacky to me (though if there's no better way I don't think that's a
good show-stopper).

But I am still wondering: Is there a reason PostgreSQL doesn't allow me to
bind against port 0? I understand that in a production environment this is
almost never the thing you want to do, but I wouldn't consider this option
very dangerous.

Best Markus


On Sat, Mar 25, 2023 at 12:10 PM Erik Wienhold  wrote:

> > On 25/03/2023 18:01 CET Markus Pilman  wrote:
> >
> > I am building a simple integration test framework for an application
> that I
> > am building. For this project I am planning to use PostgreSQL.
> >
> > For testing purposes I'd like to always start with an empty database,
> > populate data, and, if the test was successful, delete everything. These
> > tests are single process tests.
> >
> > I'd like to run many processes in parallel and have one postgres server
> > process running for each. I realize that I could use one and use multiple
> > databases but I don't want to do this for a variety of reasons (one being
> > that I also want to test the control plane logic, the application is
> > multi-tenant and uses a database per tenant, having separate databases
> > simplifies debugging etc).
> >
> > Now the problem is that I need to find a TCP port for each running
> postgres
> > instance. There's multiple ways to do this, but by far the easiest one I
> > know is to bind to port 0. So my plan was to start postgres with "-p 0"
> and
> > then parse stdout to figure out which port it actually uses. But that
> > doesn't seem to work:
> >
> > >postgres -D data/ -p 0
> >  2023-03-25 16:39:54.271 GMT [13924] FATAL: 0 is outside the valid range
> for parameter "port" (1 .. 65535)
> >
> > What would be the recommended way of addressing my issue?
>
> I would try to start Postgres with every port number in a for loop starting
> with port number 1024.  The first one that works is your port number.  And
> you
> may not even have to parse stdout if you can pass that port number to your
> tests.
>
> Maybe you can also use pg_virtualenv[0] from Debian's postgresql-common.
> It
> tries every port number starting from 5432.
>
> [0]
> https://manpages.debian.org/testing/postgresql-common/pg_virtualenv.1.en.html
>
> --
> Erik
>

Re: Binding Postgres to port 0 for testing

[Markus Pilman]

Thanks Michael and Erik for the help, I really appreciate it!

Thanks for explaining the context why PostgreSQL doesn't allow binding
against port 0.

I somehow didn't consider looking at the postgres tests, though it makes
sense that they need to solve this problem. If I read the perl code
correctly though it seems that this could, in theory, cause a race? The
script checks first whether the port has been assigned to a test, then
binds a socket to check whether it is used by someone else, closes this
test socker, and then starts a server process. I guess it's unlikely
enough, but isn't there a risk that some other process (that isn't
controlled by this perl script) binds to the found port right after this
test bind but right before postgres calls bind? I guess it should be rare
enough so that it wouldn't cause flaky tests.

I decided to implement the following (this strategy works, though it might
be a bit brittle if PostgreSQL changes the error output format in the
future):
1. Loop, starting from port 5432, incrementing each iteration
2. Start postgres with the given port
3. Parse the output to check whether postgres either writes a line that
ends with "could not create any TCP/IP sockets" (in which case I continue)
or with "database system is ready to accept connections" (in which case I
break).

This is definitely not the most elegant solution, but it should do for now.
At the moment I want to be able to set up everything in one process. In my
experience this makes debugging problems a bit easier but comes at the cost
of a more complex test driver (I recognize that it is a bit weird that the
application layer initializes the runtime environment in this case).

Also, this is a hobby-project and I am more interested in fun learning than
reducing work :) Generally I would agree that reusing existing and testing
code to run this would be better unless there's a really good reason not to
do that.

On Sun, Mar 26, 2023 at 7:27 PM Michael Paquier  wrote:

> On Sat, Mar 25, 2023 at 11:01:33AM -0600, Markus Pilman wrote:
> > Now the problem is that I need to find a TCP port for each running
> postgres
> > instance. There's multiple ways to do this, but by far the easiest one I
> > know is to bind to port 0. So my plan was to start postgres with "-p 0"
> and
> > then parse stdout to figure out which port it actually uses. But that
> > doesn't seem to work:
>
> Note that you can find some inspiration about that in the code tree
> within src/test/perl/PostgreSQL/Test/Cluster.pm, particularly
> get_free_port(), where we have now accumulated a couple of years of
> experience in designing something that's rather safe, even if it comes
> with its own limits.  It is in perl so perhaps you could just reuse it
> rather than reinvent the wheel?  Of course, still it should not be
> complicated to translate that in a different language, but there may
> be no need to reinvent the wheel.  And seeing your first message with
> the requirements you list, this does what you are looking for:
> - Create an empty cluster.
> - Freely create databases, tablespaces, queries, etc.
> - Wipe out the whole.
>
> The test cases around src/test/recovery/t/ could be a good starting
> point, as well.
> --
> Michael
>

Re: sha512sum (program) gives different result than sha512 in PG11

[Thomas Markus]

Hi,

echo contains a trailing carriage return. Try

echo -n "A" | sha512sum

regards
Thomas


Am 14.01.19 um 16:16 schrieb Andreas Joseph Krogh:

Hi.
Anyone can explain why these two don't give the same result?
1.
$ echo "A" | sha512sum
7a296fab5364b34ce3e0476d55bf291bd41aa085e5ecf2a96883e593aa1836fed22f7242af48d54af18f55c8d1def13ec9314c92a0ba63f7663500090565 
 -

2.
$ psql -A -t -c "select encode(sha512('A'), 'hex')"
21b4f4bd9e64ed355c3eb676a28ebedaf6d8f17bdc365995b319097153044080516bd083bfcce66121a3072646994c8430cc382b8dc543e84880183bf856cff5
Thanks!
--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 

Re: Question: Multiple pg clusters on one server can be reached with the standard port.

[Thomas Markus]

Hi

Am 16.06.23 um 11:40 schrieb Brainmue:

Hello all,

I am currently looking for a solution similar to Oracle Listener.

Current situation:
I have a system with multiple PostgreSQL clusters for different databases.
Each cluster is running on the same physical machine and is accessed through 
its own DNS alias and corresponding port.
I only have one single IP address available, and there is no possibility of 
obtaining additional addresses.

Here's an example:

DNS ALIAS   Host
─> pgs1.server.net:5432 ─> PG_Cluster1@192.168.0.1:5432
─> pgs2.server.net:5433 ─> PG_Cluster2@192.168.0.1:5433
─> pgs3.server.net:5434 ─> PG_Cluster3@192.168.0.1:5434
─> pgs4.server.net:5435 ─> PG_Cluster4@192.168.0.1:5435
─> pgs5.server.net:5436 ─> PG_Cluster5@192.168.0.1:5436


Desired solution:
I still want to use the same system with different PostgreSQL clusters for the 
various databases.
These clusters are internally accessible through different ports.
However, I would like a service on the server to receive all external requests 
on port 5432 and forward them to the corresponding internal cluster based on 
the DNS alias.
It would also be desirable if this service could provide additional features 
like connection pooling or other functionalities.
Similar to a reverse proxy.

Here's an example:

DNS ALIAS HOST
─> pgs1.server.net:5432 ─┐   ┌──┬──> PG_Cluster1@localhost:5433
─> pgs2.server.net:5432 ─┤   │  ├──> PG_Cluster2@localhost:5434
─> pgs3.server.net:5432 ─┼─>─┤ 192.168.0.1:5432 ├──> PG_Cluster3@localhost:5435
─> pgs4.server.net:5432 ─┤   │  ├──> PG_Cluster4@localhost:5436
─> pgs5.server.net:5432 ─┘   └──┴──> PG_Cluster5@localhost:5437


Is there a solution for this, and what are the advantages or limitations that 
arise from it?

Thank you in advance for your suggestions and help.

Regards,
Michael




possible solutions:
* set up a firewall rule to forward connection
* use a tcp proxy (nginx can do that)
* check pg_bouncer

best regards
Thomas




OpenPGP_0x9794716335E9B5AF.asc
Description: OpenPGP public key


OpenPGP_signature
Description: OpenPGP digital signature

Re: Question: Multiple pg clusters on one server can be reached with the standard port.

[Thomas Markus]

Hi,

Am 16.06.23 um 13:53 schrieb Brainmue:



possible solutions:
* set up a firewall rule to forward connection
* use a tcp proxy (nginx can do that)
* check pg_bouncer

best regards
Thomas

Hello Thomas,

Thank you for your quick reply.

With firewall you mean an additional software, right?
Because with iptables or netfilter I can't forward TCP packets based on the DNS 
alias name. Or is
that possible?

I have the same problem with nginx. I just looked in the documentation again 
but I can't find a way
to distinguish which cluster to forward to based on the DNS alias.
Do you have an example for me?

We have already looked at pgbouncer and it works with that but unfortunately 
you have to do the
authentication in pgbouncer. Which we don't like so much.

Regards,
Michael


You cant setup firewall rules basedon dns names. firewall rules are 
based on ip adresses and dns resolution happens on rule creation.
I dont have an example for nginx. As I remember nginx resolves dns names 
only for variables. So setup a variable with your hostname and use this 
variable in your server definition.


best regards
Thomas



OpenPGP_0x9794716335E9B5AF.asc
Description: OpenPGP public key


OpenPGP_signature
Description: OpenPGP digital signature

Re: Question: Multiple pg clusters on one server can be reached with the standard port.

[Thomas Markus]

Hi,

Am 19.06.23 um 12:33 schrieb Peter J. Holzer:

As Francisco already pointed out, this can't work with nginx either. The
client resolves the alias and the TCP packets only contain the IP
address, not the alias which was used to get that address. So nginx
simply doesn't have that information and therefore can't act on it.

For HTTP this works because the HTTP protocol contains a Host field
which the client fills with the name it used. But the Postgres protocol
has no such information (and in any case nginx probably doesn't
understand that protocol anyway).

So (again, as Francisco already wrote) the best way is probably to write
a simple proxy which uses the database (not DNS) name for routing. I
seem to remember that nginx has a plugin architecture for protocols so
it might make sense to write that as an nginx plugin instead of a
standalone server, but that's really a judgement call the programmer has
to make. Another possibility would of course be to extend pgbouncer to
do what the OP needs.

 hp

yeah I know
I looked at his setup definition only and missed the point that he wants 
a single entry point for all.

The image doesnt match this. Looks like a simple forward proxy definition.

anyway, fw or nginx cant look into tcp streams nor does it makes sense 
to me. Maybe without tls




OpenPGP_0x9794716335E9B5AF.asc
Description: OpenPGP public key


OpenPGP_signature
Description: OpenPGP digital signature

Postgres > 12 with Windows Server 2012

[Markus Oley]

Hello everyone,
I need to run Postgres on Windows Server 2012. Postgres 12 is EndOfLive in
< 12 months, so I'd like to use newer versions (I've got Postgres 15
sorted) in this scenario. However, I have concerns because Postgres 13
onwards is no longer specified for Windows Server 2012.
Is this just due to the fact that Windows Server 2012 is already EndOfLive
or are there really hard reasons (such as reliability degradation or
expected technical problems) why this combination should be avoided?

Would be nice if I could hear a well-founded opinion on this.
Thanks in advance
Regards
Markus

Re: Match 2 words and more

[Thomas Markus]

Am 28.11.21 um 01:27 schrieb Shaozhong SHI:

this is supposed to find those to have 2 words and more.

select name FROM a_table where "STREET_NAME" ~ '^[[:alpha:]+ 
]+[:alpha:]+$';


But, it finds only one word as well.

It appears that regex is not robust.

Can anyone shed light on this?

Regards,

David

Hi,

It's robust, but syntax is sometimes weired

for words I would use something like (contains numbers too)
"STREET_NAME" ~ '(\w+\s+)+\w+';

or alpha only
"STREET_NAME" ~ '([[:alpha:]]+\s+)+[[:alpha:]]+'

regards
Thomas

SELECT DISTINCT scans the table?

[Markus Demleitner]

Dear list,

This feels like a FAQ, but neither the postgres docs nor web searches
got me a single step towards some sort of insight.

Maximally stripped down, my problem is that

  select distinct 300 from 

seqscans  (at least in PostgreSQL 11.14).  To me, it seems
obvious that this ought be be just one row containing 300 once
Postgres has established that  is nonempty.

Why do things not work like this?  Am I missing something major?

The reason I'm interested in this is of course a bit more involved.
I have a view that looks somewhat like this:

  CREATE VIEW a_view AS (
SELECT 'abc' as coll, ...
FROM table1
  UNION
SELECT 'def' as coll, ...
FROM table2
  UNION
SELECT coll, ...
FROM table3
  ...)

and so on for perhaps 50 tables; where, as for table3 in this
example, the coll column is not simply a constant, there is an index
on the source column (these then are actually fast).

I'd now like to be able to say

  SELECT DISTINCT coll FROM a_view

-- which takes forever once some of the tables involved are
sufficiently large.

I'd assume in an ideal world the query would be essentally
instantaneous -- merging the constants and doing a few index scans
for the tables that have non-constant coll.  In reality, it's not.
The tables with constant coll are all completely scanned, either
sequentially or index-only.

Assuming this is the expected behaviour: Is there perhaps some trick
I could use to make postgres use the information that there are
constants in the select clauses?

Thanks,

 Markus

Re: SELECT DISTINCT scans the table?

[Markus Demleitner]

Dear Tom,

On Thu, Dec 16, 2021 at 03:47:57PM -0500, Tom Lane wrote:
> Markus Demleitner  writes:
> > Maximally stripped down, my problem is that
> 
> >   select distinct 300 from 
> 
> > seqscans  (at least in PostgreSQL 11.14).  To me, it seems
> > obvious that this ought be be just one row containing 300 once
> > Postgres has established that  is nonempty.
> 
> That seems like the sort of optimization that we absolutely should
> not spend cycles looking for.  If it were a trivial change consuming
> no detectable number of planning cycles, maybe it would be worth the
> development and maintenance effort; though I'd be dubious about the
> value.  But the fact that it'd have to be transformed into something
> testing whether the table is nonempty makes it fairly nontrivial.
> I doubt it's worth the development cost plus the cycles imposed
> on every other query.

I certainly understand that reasoning for this particular example.
However, in my actual use case, the one with the view consisting of a
large union containing constants from the original mail, 

  CREATE VIEW a_view AS (
SELECT 'abc' as coll, ...
FROM table1   -- with perhaps 1e6 rows
  UNION
SELECT 'def' as coll, ...
FROM table2   -- with perhaps another 1e7 rows
  UNION
SELECT coll, ... -- with an index on table3.coll
FROM table3
  ...)

being able to factor out constants would make a difference of
milliseconds versus a long time (~ a minute in my case, with about
1e8 total rows) when running SELECT DISTINCT coll FROM a_view.

Is there, perhaps, a non-obvious way to give the planner a nudge to
exploit the constant-ness of coll in table1 and table2?

Thanks,

   Markus

Re: Getting json-value as varchar

[Thomas Markus]

Hi,

Am 06.01.22 um 13:28 schrieb Andreas Joseph Krogh:

Hi, in PG-14 this query returns "value" (with double-quotes):
SELECT ('{"key":"value"}'::jsonb)['key'];
┌─┐
│  jsonb  │
├─┤
│ "value" │
└─┘
(1 row)

and this returns 'value' (without the quotes):
SELECT ('{"key":"value"}'::jsonb)->> 'key';
┌──┐
│ ?column? │
├──┤
│ value    │
└──┘
(1 row)

How to I use the subscript syntax and get the result as varchar 
instead of JSONB, assuming I /know/ the JSON-field is a String?

simply cast your value
SELECT (('{"key":"value"}'::jsonb)->> 'key')::text;

best regards
Thomas

--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com
www.visena.com 

Re: Getting json-value as varchar

[Thomas Markus]

Hi,

Am 06.01.22 um 13:36 schrieb Andreas Joseph Krogh:
På torsdag 06. januar 2022 kl. 13:31:19, skrev Thomas Markus 
:


Hi,
Am 06.01.22 um 13:28 schrieb Andreas Joseph Krogh:

Hi, in PG-14 this query returns "value" (with double-quotes):
SELECT ('{"key":"value"}'::jsonb)['key'];
┌─┐
│  jsonb  │
├─┤
│ "value" │
└─┘
(1 row)

and this returns 'value' (without the quotes):
SELECT ('{"key":"value"}'::jsonb)->> 'key';
┌──┐
│ ?column? │
├──┤
│ value    │
└──┘
(1 row)

How to I use the subscript syntax and get the result as varchar
instead of JSONB, assuming I /know/ the JSON-field is a String?

simply cast your value
SELECT (('{"key":"value"}'::jsonb)->> 'key')::text;

best regards
Thoma

I think you misread my message. What I want is for the subscript-version:

('{"key":"value"}'::jsonb)['key']

to return:
┌──┐
│ ?column? │
├──┤
│ value    │
└──┘

instead of
┌─┐
│  jsonb  │
├─┤
│ "value" │
└─┘

yeah right :(

complicated but this should do:
SELECT  jsonb_build_array( ('{"key":"value"}'::jsonb)['key'] ) ->> 0;




--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com
www.visena.com <https://www.visena.com>
<https://www.visena.com>

Re: duplicate primary key

[Thomas Markus]

Am 22.11.17 um 12:05 schrieb Alexander Pyhalov:

Hello.

I'm a bit shocked. During import/export of our database we've found a 
duplicate primary key.


# \d player

Table "public.player"
   Column   |    Type | 
    Modifiers
+-+ 

 id | integer | not null default 
nextval('player_id_seq'::regclass)

...
Indexes:
    "pk_id" PRIMARY KEY, btree (id)
...

# select * from pg_indexes where indexname='pk_id';
 schemaname | tablename | indexname | tablespace |  indexdef
+---+---++-- 

 public | player    | pk_id |    | CREATE UNIQUE INDEX 
pk_id ON player USING btree (id)


# select * from pg_constraint where conname='pk_id';
-[ RECORD 1 ]-+--
conname   | pk_id
connamespace  | 2200
contype   | p
condeferrable | f
condeferred   | f
convalidated  | t
conrelid  | 18319
contypid  | 0
conindid  | 18640
confrelid | 0
confupdtype   |
confdeltype   |
confmatchtype |
conislocal    | t
coninhcount   | 0
connoinherit  | t
conkey    | {1}
confkey   |
conpfeqop |
conppeqop |
conffeqop |
conexclop |
conbin    |
consrc    |

# select count(*) from player where id=14875;
-[ RECORD 1 ]
count | 2

The records are identical, besides ctid,xmin,xmax

# select tableoid,ctid,id,xmin,xmax from player where id=14875;
 tableoid | ctid |    id |    xmin    |    xmax
--+--+---++
    18319 | (9982129,2)  | 14875 | 3149449600 | 3152681810
    18319 | (9976870,49) | 14875 | 3149448769 | 3152328995



I don't understand how this could have happened


Hi Alex,

we got this with a broken index. Fix data and rebuild them. And check 
your system/storage


Thomas

Index not used in certain nested views but not in others

[Markus Demleitner]

.479 rows=0 loops=1)
 ->  Seq Scan on main  (cost=0.00..52.61 rows=561 width=48) (actual 
time=0.011..0.317 rows=561 loops=1)
 ->  Index Scan using raw_spectra_pub_did on raw_spectra  
(cost=0.56..8.58 rows=1 width=66) (actual time=0.003..0.003 rows=0 loops=561)
   Index Cond: (pub_did = main.obs_publisher_did)
 Planning Time: 5.386 ms
 Execution Time: 2.750 ms

-- exactly as it should.

So, when the SELECT statement on dfbsspec.ssa stands along in the view
definition, Postgres does the right thing; when the exact same query
stands in a UNION ALL with other tables, Postgres doesn't use the
index.  Hu?

Is there anything that would explain that behaviour given I've switched
off the genetic optimiser and postgres has hopefully exhaustively
searched the space of plans in both cases?

Thanks a lot!

 -- Markus

broken backup trail in case of quickly patroni switchback and forth

[Zwettler Markus (OIZ)]

we are using Patroni for management of our Postgres standby databases.

we take our (wal) backups on the primary side based on intervals and thresholds.
our archived wal's are written to a local wal directory first and moved to tape 
afterwards.

we got a case where Patroni switched back and forth sides quickly, e.g.:
12:00h: primary - standby
12:05h: standby - primary
12:10h: primary - standby

we realised that we will not have a wal backup of those wal's generated between 
12:05h and 12:10h in this scenario.

how can we make sure that the whole wal sequence trail will be backuped? any 
idea?

- Markus

AW: broken backup trail in case of quickly patroni switchback and forth

[Zwettler Markus (OIZ)]

I already asked the Patroni folks. They told me this is not related to Patroni 
but Postgresql. ;-)

- Markus



On 11/7/19 5:52 AM, Zwettler Markus (OIZ) wrote:
> we are using Patroni for management of our Postgres standby databases.
> 
> we take our (wal) backups on the primary side based on intervals and 
> thresholds.
> our archived wal's are written to a local wal directory first and moved to 
> tape afterwards.
> 
> we got a case where Patroni switched back and forth sides quickly, e.g.:
> 12:00h: primary - standby
> 12:05h: standby - primary
> 12:10h: primary - standby
> 
> we realised that we will not have a wal backup of those wal's generated 
> between 12:05h and 12:10h in this scenario.
> 
> how can we make sure that the whole wal sequence trail will be backuped? any 
> idea?

Probably best to ask the Patroni folks:

https://github.com/zalando/patroni#community

> 
> - Markus
> 
> 


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

AW: AW: broken backup trail in case of quickly patroni switchback and forth

[Zwettler Markus (OIZ)]

1) 9.6



2) 
$ cat postgresql.conf
# Do not edit this file manually!
# It will be overwritten by Patroni!
include 'postgresql.base.conf'

cluster_name = 'pcl_l702'
hot_standby = 'on'
hot_standby_feedback = 'True'
listen_addresses = 
'localhost,tstm49003.tstglobal.tst.loc,pcl_l702.tstglobal.tst.loc'
max_connections = '100'
max_locks_per_transaction = '64'
max_prepared_transactions = '0'
max_replication_slots = '10'
max_wal_senders = '10'
max_worker_processes = '8'
port = '5436'
track_commit_timestamp = 'off'
wal_keep_segments = '8'
wal_level = 'replica'
wal_log_hints = 'on'
hba_file = '/pgdata/pcl_l702/pg_hba.conf'
ident_file = '/pgdata/pcl_l702/pg_ident.conf'
$
$
$
$ cat postgresql.base.conf
datestyle = 'iso, mdy'
default_text_search_config = 'pg_catalog.english'
dynamic_shared_memory_type = posix
lc_messages = 'en_US.UTF-8'
lc_monetary = 'de_CH.UTF-8'
lc_numeric = 'de_CH.UTF-8'
lc_time = 'de_CH.UTF-8'
logging_collector = on
log_directory = 'pg_log'
log_rotation_age = 1d
log_rotation_size = 0
log_timezone = 'Europe/Vaduz'
log_truncate_on_rotation = on
max_connections = 100
timezone = 'Europe/Vaduz'
archive_command = 'test ! -f /tmp/pg_archive_backup_running_on_pcl_l702* && 
rsync --checksum %p /pgxlog_archive/pcl_l702/%f'
archive_mode = on
archive_timeout = 1800
cluster_name = pcl_l702
cron.database_name = 'pdb_l72_oiz'
# effective_cache_size
listen_addresses = '*'
log_connections = on
log_destination = 'stderr, csvlog'
log_disconnections = on
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_line_prefix = '%t : %h=>%u@%d : %p-%c-%v : %e '
log_statement = 'ddl'
max_wal_senders = 5
port = 5436
shared_buffers = 512MB
shared_preload_libraries = 'auto_explain, pg_stat_statements, pg_cron, 
pg_statsinfo'
wal_buffers = 16MB
wal_compression = on
wal_level = replica
# work_mem



3)
12:00h: primary - standby
=> Some clients commited some transactions; Failover
12:05h: standby - primary
=> Some clients connected + commited some transactions; Failover
12:10h: primary - standby





On 11/7/19 7:18 AM, Zwettler Markus (OIZ) wrote:
> I already asked the Patroni folks. They told me this is not related to 
> Patroni but Postgresql. ;-)

Hard to say without more information:

1) Postgres version

2) Setup/config info

3) Detail if what happened between 12:00 and 12:10

> 
> - Markus
> 
> 
> 
> On 11/7/19 5:52 AM, Zwettler Markus (OIZ) wrote:
>> we are using Patroni for management of our Postgres standby databases.
>>
>> we take our (wal) backups on the primary side based on intervals and 
>> thresholds.
>> our archived wal's are written to a local wal directory first and moved to 
>> tape afterwards.
>>
>> we got a case where Patroni switched back and forth sides quickly, e.g.:
>> 12:00h: primary - standby
>> 12:05h: standby - primary
>> 12:10h: primary - standby
>>
>> we realised that we will not have a wal backup of those wal's generated 
>> between 12:05h and 12:10h in this scenario.
>>
>> how can we make sure that the whole wal sequence trail will be backuped? any 
>> idea?
> 
> Probably best to ask the Patroni folks:
> 
> https://github.com/zalando/patroni#community
> 
>>
>> - Markus
>>
>>
> 
> 


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

AW: AW: AW: broken backup trail in case of quickly patroni switchback and forth

[Zwettler Markus (OIZ)]

3)
Patroni does only failovers. Also in case of regular shutdown of the primary. A 
failover is a promote of the standby + automatic reinstate (pg_rewind or 
pg_basebackup) of the former primary.

Time: role site 1 - role site 2

12:00h: primary - standby
=> Some clients commited some transactions; Primary stopped => Failover to 
standby
12:05h: standby - primary
=> Some clients connected + commited some transactions; Primary stopped => 
Failover to standby
12:10h: primary - standby



Patroni.yml)
$ cat pcl_l702.yml
scope: pcl_l702
name: pcl_l702@tstm49003
namespace: /patroni/

log:
  level: DEBUG
  dir: /opt/app/patroni/etc/log/
  file_num: 10
  file_size: 104857600

restapi:
  listen: tstm49003.tstglobal.tst.loc:8010
  connect_address: tstm49003.tstglobal.tst.loc:8010

etcd:
  hosts: 
etcdlab01.tstglobal.tst.loc:2379,etcdlab02.tstglobal.tst.loc:2379,etcdlab03.tstglobal.tst.loc:2379,etcdlab04.tstglobal.tst.loc:2379,etcdlab05.tstglobal.tst.loc:2379
  username: patroni
  password: censored

bootstrap:
  dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
master_start_timeout: 300
synchronous_mode: true
postgresql:
  use_pg_rewind: true
  use_slots: true

  # NO BOOTSTRAPPING USED
  method: do_not_bootstrap
  do_not_bootstrap:
command: /bin/false

postgresql:
  authentication:
replication:
  username: repadmin
  password: censored
superuser:
  username: patroni
  password: censored
  callbacks:
on_reload: /opt/app/patroni/etc/callback_patroni.sh
on_restart: /opt/app/patroni/etc/callback_patroni.sh
on_role_change: /opt/app/patroni/etc/callback_patroni.sh
on_start: /opt/app/patroni/etc/callback_patroni.sh
on_stop: /opt/app/patroni/etc/callback_patroni.sh
  connect_address: tstm49003.tstglobal.tst.loc:5436
  database: pcl_l702
  data_dir: /pgdata/pcl_l702
  bin_dir: /usr/pgsql-9.6/bin
  listen: localhost,tstm49003.tstglobal.tst.loc,pcl_l702.tstglobal.tst.loc:5436
  pgpass: /home/postgres/.pgpass_patroni
  recovery_conf:
restore_command: cp /pgxlog_archive/pcl_l702/%f %p
  parameters:
hot_standby_feedback: on
wal_keep_segments: 64
  use_pg_rewind: true

watchdog:
  mode: automatic
  device: /dev/watchdog
  safety_margin: 5

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false




-Ursprüngliche Nachricht-
Von: Adrian Klaver  
Gesendet: Donnerstag, 7. November 2019 17:06
An: Zwettler Markus (OIZ) ; 
pgsql-general@lists.postgresql.org
Betreff: Re: AW: AW: broken backup trail in case of quickly patroni switchback 
and forth

On 11/7/19 7:47 AM, Zwettler Markus (OIZ) wrote:

I am heading out the door so I will not have time to look at below until later. 
For those that get a chance before then, it would be nice to have the Patroni 
conf file information also. The Patroni information may answer the question, 
but it case it does not what actually is failover in 3) below?

> 1) 9.6
> 
> 
> 
> 2)
> $ cat postgresql.conf
> # Do not edit this file manually!
> # It will be overwritten by Patroni!
> include 'postgresql.base.conf'
> 
> cluster_name = 'pcl_l702'
> hot_standby = 'on'
> hot_standby_feedback = 'True'
> listen_addresses = 
> 'localhost,tstm49003.tstglobal.tst.loc,pcl_l702.tstglobal.tst.loc'
> max_connections = '100'
> max_locks_per_transaction = '64'
> max_prepared_transactions = '0'
> max_replication_slots = '10'
> max_wal_senders = '10'
> max_worker_processes = '8'
> port = '5436'
> track_commit_timestamp = 'off'
> wal_keep_segments = '8'
> wal_level = 'replica'
> wal_log_hints = 'on'
> hba_file = '/pgdata/pcl_l702/pg_hba.conf'
> ident_file = '/pgdata/pcl_l702/pg_ident.conf'
> $
> $
> $
> $ cat postgresql.base.conf
> datestyle = 'iso, mdy'
> default_text_search_config = 'pg_catalog.english'
> dynamic_shared_memory_type = posix
> lc_messages = 'en_US.UTF-8'
> lc_monetary = 'de_CH.UTF-8'
> lc_numeric = 'de_CH.UTF-8'
> lc_time = 'de_CH.UTF-8'
> logging_collector = on
> log_directory = 'pg_log'
> log_rotation_age = 1d
> log_rotation_size = 0
> log_timezone = 'Europe/Vaduz'
> log_truncate_on_rotation = on
> max_connections = 100
> timezone = 'Europe/Vaduz'
> archive_command = 'test ! -f /tmp/pg_archive_backup_running_on_pcl_l702* && 
> rsync --checksum %p /pgxlog_archive/pcl_l702/%f'
> archive_mode = on
> archive_timeout = 1800
> cluster_name = pcl_l702
> cron.database_name = 'pdb_l72_oiz'
> # effective_cache_size
> listen_addresses = '*'
> log_connectio

AW: broken backup trail in case of quickly patroni switchback and forth

[Zwettler Markus (OIZ)]

1) If I got you right this means enabling archiving on both machines 
(archive_mode=on, archive_command=cp...). Yes?

2) Will the latest transactions on the actual primary be archived (copied from 
pg_xlog to the local archive_directory) before this primary is reinstated as 
new standby?

Thanks,
Markus



On Thu, 2019-11-07 at 13:52 +, Zwettler Markus (OIZ) wrote:
> we are using Patroni for management of our Postgres standby databases.
> 
> we take our (wal) backups on the primary side based on intervals and 
> thresholds.
> our archived wal's are written to a local wal directory first and moved to 
> tape afterwards.
> 
> we got a case where Patroni switched back and forth sides quickly, e.g.:
> 12:00h: primary - standby
> 12:05h: standby - primary
> 12:10h: primary - standby
> 
> we realised that we will not have a wal backup of those wal's generated 
> between 12:05h and 12:10h in this scenario.
> 
> how can we make sure that the whole wal sequence trail will be backuped? any 
> idea?

You'll have to archive WAL from both machines.  Then you have everything you 
should need.

Make sure "recovery_target_timeline = 'latest'" so that recovery will follow 
the timeline jumps.

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

AW: AW: AW: AW: broken backup trail in case of quickly patroni switchback and forth

[Zwettler Markus (OIZ)]

It depends. It is a switchover if Patroni could to a clean shutdown. But, it 
might start killing processes after a certain period if a normal shutdown after 
SIGTERM didn't happen. This would not be a switchover anymore. In other words 
there is no guarantee for a "clean" switchover. This might be the reason why 
the Patroni guys are always talking about failover only.

It's not a Patroni issue but it's triggered by Patroni as it will do "some kind 
of switchover" on a regular shutdown.



"Zwettler Markus (OIZ)" 
mailto:markus.zwett...@zuerich.ch>> wrote on 
2019/11/07 11:32:42 AM:

> From: "Zwettler Markus (OIZ)" 
> mailto:markus.zwett...@zuerich.ch>>
> To: Adrian Klaver 
> mailto:adrian.kla...@aklaver.com>>, "pgsql-
> gene...@lists.postgresql.org<mailto:gene...@lists.postgresql.org>" 
> mailto:pgsql-general@lists.postgresql.org>>
> Date: 2019/11/07 11:33 AM
> Subject: [EXTERNAL] AW: AW: AW: broken backup trail in case of
> quickly patroni switchback and forth
>
> 3)
> Patroni does only failovers. Also in case of regular shutdown of the
> primary. A failover is a promote of the standby + automatic
> reinstate (pg_rewind or pg_basebackup) of the former primary.


This is not accurate.  Patroni does controlled switchovers as well as 
failovers.  Controlled switchover issues a fast shutdown to Postgres, hard ones 
issue an immediate shutdown.  From this point, it's how Postgres responds to 
those that matter.

Fast shutdown will attempt to ensure the wal stream is transmitted to the 
replica and the wal files are archived.  Immediate shutdown will not do any of 
this.  This issue explains more about when Patroni may choose an immediate 
shutdown (it might not be totally accurate anymore as it's a year old).

https://github.com/zalando/patroni/issues/837#issuecomment-433686687


I agree with the Patroni folks that this is not a Patroni issue, but simply how 
Postgres responds to the required shutdown types.

AW: AW: AW: AW: AW: broken backup trail in case of quickly patroni switchback and forth

[Zwettler Markus (OIZ)]

Let me clarify: "But, it might start killing processes after a certain period 
if a _fast_ shutdown after SIGTERM didn't happen".

I am talking about stopping the Patroni master process with a systemd scipt.



Von: Brad Nicholson 
Gesendet: Freitag, 8. November 2019 15:58
An: Zwettler Markus (OIZ) 
Cc: Adrian Klaver ; 
pgsql-general@lists.postgresql.org
Betreff: Re: AW: AW: AW: AW: broken backup trail in case of quickly patroni 
switchback and forth


"Zwettler Markus (OIZ)" 
mailto:markus.zwett...@zuerich.ch>> wrote on 
2019/11/08 07:51:33 AM:

> From: "Zwettler Markus (OIZ)" 
> mailto:markus.zwett...@zuerich.ch>>
> To: Brad Nicholson mailto:br...@ca.ibm.com>>
> Cc: Adrian Klaver 
> mailto:adrian.kla...@aklaver.com>>, "pgsql-
> gene...@lists.postgresql.org<mailto:gene...@lists.postgresql.org>" 
> mailto:pgsql-general@lists.postgresql.org>>
> Date: 2019/11/08 07:51 AM
> Subject: [EXTERNAL] AW:  AW: AW: AW: broken backup trail in case of
> quickly patroni switchback and forth
>
> It depends. It is a switchover if Patroni could to a clean shutdown.
> But, it might start killing processes after a certain period if a
> normal shutdown after SIGTERM didn't happen. This would not be a
> switchover anymore. In other words there is no guarantee for a
> "clean" switchover. This might be the reason why the Patroni guys
> are always talking about failover only.


If it can't do a clean shutdown, that points to something wrong with Postgres 
itself.  Why doesn't a fast shutdown work for you in those cases?

> It's not a Patroni issue but it's triggered by Patroni as it will do
> "some kind of switchover" on a regular shutdown.

Sure, but you should be looking at why Postgres can't cleanly shutdown.

How are you telling Patroni to switchover? Are you using the Patroni switchover 
command via patronictl or the API, or sending a signal to the Patroni process?  
I think the explicit switchover
command will not behave this way.  It will return you a 503 if it can't 
switchover and not change the primary (that is something you can confirm with 
the Patroni developers).

Brad.

AW: AW: AW: AW: AW: AW: broken backup trail in case of quickly patroni switchback and forth

[Zwettler Markus (OIZ)]

How exactly? Please clarify.



"Zwettler Markus (OIZ)" 
mailto:markus.zwett...@zuerich.ch>> wrote on 
2019/11/08 11:02:49 AM:

> From: "Zwettler Markus (OIZ)" 
> mailto:markus.zwett...@zuerich.ch>>
> To: Brad Nicholson mailto:br...@ca.ibm.com>>
> Cc: Adrian Klaver 
> mailto:adrian.kla...@aklaver.com>>, "pgsql-
> gene...@lists.postgresql.org<mailto:gene...@lists.postgresql.org>" 
> mailto:pgsql-general@lists.postgresql.org>>
> Date: 2019/11/08 11:02 AM
> Subject: [EXTERNAL] AW:  AW:  AW: AW: AW: broken backup trail in
> case of quickly patroni switchback and forth
>
> Let me clarify: "But, it might start killing processes after a
> certain period if a _fast_ shutdown after SIGTERM didn't happen".
>
> I am talking about stopping the Patroni master process with a systemd scipt.


Use the switchover functionality in Patroni first, and gate youur shutdown via 
systemd on the success of that operation.

Brad.

AW: AW: AW: AW: AW: AW: AW: broken backup trail in case of quickly patroni switchback and forth

[Zwettler Markus (OIZ)]

?  "Zwettler Markus (OIZ)" 
mailto:markus.zwett...@zuerich.ch>> wrote on 
2019/11/08 11:27:00 AM:

>> From: "Zwettler Markus (OIZ)" 
>> mailto:markus.zwett...@zuerich.ch>>
>> To: Brad Nicholson mailto:br...@ca.ibm.com>>
>> Cc: Adrian Klaver 
>> mailto:adrian.kla...@aklaver.com>>, "pgsql-
>> gene...@lists.postgresql.org<mailto:gene...@lists.postgresql.org>" 
>> mailto:pgsql-general@lists.postgresql.org>>
>> Date: 2019/11/08 11:27 AM
>>> Subject: [EXTERNAL] AW:  AW:  AW:  AW: AW: AW: broken backup trail
>> in case of quickly patroni switchback and forth
>>
>> How exactly? Please clarify.

> (please don't top post, makes the replies hard to follow)

> patronictl switchover 

> follow the prompts

> there is also a /switchover API endpoint you can use.

> Brad



I wondered about your "patronictl switchover + systemd" hint. How would you do 
("gate") this combination?

Markus

sql query for postgres replication check

[Zwettler Markus (OIZ)]

We would like to check the Postgres SYNC streaming replication status with 
Nagios using the same query on all servers (master + standby) and versions 
(9.6, 10, 12) for simplicity.


I came up with the following query which should return any apply lag in seconds.


select coalesce(replay_delay, 0) replication_delay_in_sec
from (
   select datname,
  (
select case
 when received_lsn = latest_end_lsn then 0
 else extract(epoch
from now() - latest_end_time)
   end
from pg_stat_wal_receiver
  ) replay_delay
   from pg_database
   where datname = current_database()
 ) xview;


I would expect delays >0 in case SYNC or ASYNC replication is somehow behind. 
We will do a warning at 120 secs and critical at 300 secs.


Would this do the job or am I missing something here?


Thanks, Markus

AW: sql query for postgres replication check

[Zwettler Markus (OIZ)]

> On Fri, Nov 22, 2019 at 01:20:59PM +, Zwettler Markus (OIZ) wrote:
> > I came up with the following query which should return any apply lag in 
> > seconds.
> >
> > select coalesce(replay_delay, 0) replication_delay_in_sec from (
> >select datname,
> >   (
> > select case
> >  when received_lsn = latest_end_lsn then 0
> >  else extract(epoch
> > from now() - latest_end_time)
> >end
> > from pg_stat_wal_receiver
> >   ) replay_delay
> >from pg_database
> >where datname = current_database()
> >  ) xview;
> >
> >
> > I would expect delays >0 in case SYNC or ASYNC replication is somehow
> > behind. We will do a warning at 120 secs and critical at 300 secs.
> 
> pg_stat_wal_receiver is available only on the receiver, aka the standby so it 
> would
> not really be helpful on a primary.  On top of that streaming replication is 
> system-
> wide, so there is no actual point to look at databases either.
> 
> > Would this do the job or am I missing something here?
> 
> Here is a suggestion for Nagios: hot_standby_delay, as told in
> https://github.com/bucardo/check_postgres/blob/master/check_postgres.pl
> --
> Michael


I don't want to use check_hot_standby_delay as I would have to configure every 
streaming replication configuration separately with nagios.

I want a generic routine which I can load on any postgres server regardless of 
streaming replication or database role.

The query would return >0 if streaming replication falls behind and 0 in all 
other cases (replication or not).

Checking streaming replication per database doesn't make any sense to me.

Markus

secure deletion of archived logs

[Zwettler Markus (OIZ)]

with Oracle we use "backup archivelog all delete all input".
this is a kind of atomic transaction.
everything backuped for sure is deleted.

with Postgres we archive to a local host directory
we do a Networker backup of this directory afterwards and delete the archived 
logs
but this is not an atomic transaction
so there is a small risk that something gets deleted which is not backuped

how to you prevent this?

Is there any backup tool which can do backups analogous Oracle?

archiving question

[Zwettler Markus (OIZ)]

When there is a Postgres archiver stuck because of filled pg_xlog and archive 
directories...

... and the pg_xlog directory had been filled with dozens of GBs of xlogs...

...it takes ages until the archive_command had moved all xlogs from the pg_xlog 
directory to the archive directory afterwards...

... and you get crazy if you have a 8GB archive directory while the pg_xlog 
directory had been pumped up to 100GB :(


Any idea on this one?

AW: secure deletion of archived logs

[Zwettler Markus (OIZ)]

> Greetings,
> 
> * Zwettler Markus (OIZ) (markus.zwett...@zuerich.ch) wrote:
> > with Oracle we use "backup archivelog all delete all input".
> > this is a kind of atomic transaction.
> > everything backuped for sure is deleted.
> >
> > with Postgres we archive to a local host directory
> 
> ... how?  Do you actually sync the files after you copy them with an fsync to 
> be
> sure that they're durably stored there?  If not, then there's a pretty good 
> chance
> that you'll lose some WAL if a crash happens because if your archive command
> returns successful, PG will removed its copy of the WAL file.
> 
> Hint: using 'cp' as an archive command is a very bad idea.
> 
> > we do a Networker backup of this directory afterwards and delete the
> > archived logs but this is not an atomic transaction so there is a
> > small risk that something gets deleted which is not backuped
> 
> That would definitely be quite bad, particularly if a WAL file that was 
> needed for a
> backup to be consistent was removed or missed, as that backup would no longer
> be valid then.
> 
> > how to you prevent this?
> 
> I would strongly recommend that you use a tool that's actually built for the 
> purpose
> of backing up PG systems, like pgbackrest or similar.
> Writing your own custom code for managing WAL archives and backup sets is
> likely to result in issues.
> 
> > Is there any backup tool which can do backups analogous Oracle?
> 
> There's quite a few different tools available for backing up PG systems, with
> various features and performance- from simple things like pg_basebackup (which
> you can set up to include all the WAL for the backup to be consistent, though 
> that
> doesn't do anything to help you with managing WAL for PITR), to much more
> sophisticated tools like pgbackrest, wal-g, and others that help with managing
> WAL and dealing with expiring out backups and such.  The biggest thing is- 
> don't
> try to roll your own.
> 
> Thanks,
> 
> Stephen




We use "rsync" on XFS with "wsync" mount mode. I think this should do the job?


The tools mentioned will all do backup to disk.
We are required to do backup to tape.


Markus

AW: archiving question

[Zwettler Markus (OIZ)]

> 
> Greetings,
> 
> * Zwettler Markus (OIZ) (markus.zwett...@zuerich.ch) wrote:
> > When there is a Postgres archiver stuck because of filled pg_xlog and 
> > archive
> directories...
> >
> > ... and the pg_xlog directory had been filled with dozens of GBs of xlogs...
> >
> > ...it takes ages until the archive_command had moved all xlogs from the
> pg_xlog directory to the archive directory afterwards...
> >
> > ... and you get crazy if you have a 8GB archive directory while the
> > pg_xlog directory had been pumped up to 100GB :(
> >
> >
> > Any idea on this one?
> 
> Parallelizing the archive-push operation can be quite helpful to address this.
> 
> Thanks,
> 
> Stephen


What do you mean hear?

Afaik, Postgres runs the archive_command per log, means log by log by log.

How should we parallelize this?

AW: archiving question

[Zwettler Markus (OIZ)]

> -Ursprüngliche Nachricht-
> Von: Michael Paquier 
> Gesendet: Freitag, 6. Dezember 2019 02:43
> An: Zwettler Markus (OIZ) 
> Cc: Stephen Frost ; pgsql-general@lists.postgresql.org
> Betreff: Re: archiving question
> 
> On Thu, Dec 05, 2019 at 03:04:55PM +, Zwettler Markus (OIZ) wrote:
> > What do you mean hear?
> >
> > Afaik, Postgres runs the archive_command per log, means log by log by log.
> >
> > How should we parallelize this?
> 
> You can, in theory, skip the archiving for a couple of segments and then do 
> the
> operation at once without the need to patch Postgres.
> --
> Michael


Sorry, I am still confused.

Do you mean I should move (mv * /backup_dir) the whole pg_xlog directory away 
and move it back (mv /backup_dir/* /pg_xlog) in case of recovery?

Markus

AW: archiving question

[Zwettler Markus (OIZ)]

> On Fri, Dec 6, 2019 at 10:50 AM Zwettler Markus (OIZ) 
> <mailto:markus.zwett...@zuerich.ch> wrote:
>> -Ursprüngliche Nachricht-
>> Von: Michael Paquier <mailto:mich...@paquier.xyz>
>> Gesendet: Freitag, 6. Dezember 2019 02:43
>> An: Zwettler Markus (OIZ) <mailto:markus.zwett...@zuerich.ch>
>> Cc: Stephen Frost <mailto:sfr...@snowman.net>; 
>> mailto:pgsql-general@lists.postgresql.org
>> Betreff: Re: archiving question
>> 
>> On Thu, Dec 05, 2019 at 03:04:55PM +, Zwettler Markus (OIZ) wrote:
>> > What do you mean hear?
>> >
>> > Afaik, Postgres runs the archive_command per log, means log by log by log.
>> >
>> > How should we parallelize this?
>> 
>> You can, in theory, skip the archiving for a couple of segments and then do 
>> the
>> operation at once without the need to patch Postgres.
>> --
>> Michael
>
>
>Sorry, I am still confused.
>
>Do you mean I should move (mv * /backup_dir) the whole pg_xlog directory away 
>and move it back (mv /backup_dir/* /pg_xlog) in case of recovery?
>
>No, *absolutely* not.
>
>What you can do is have archive_command copy things one by one to a local 
>directory (still sequentially), and then you can have a separate process that 
>sends these to the archive -- and *this* process can be parallelized. 
>
>//Magnus
 


That has been my initial question.

Is there a way to tune this sequential archive_command log by log copy in case 
I have tons of logs within the pg_xlog directory?

Markus

AW: PostgreSQL HA FO question

[Zwettler Markus (OIZ)]

In my opinion, the best open source product is Patroni.



Von: Dor Ben Dov 
Gesendet: Montag, 9. Dezember 2019 11:24
An: pgsql-general@lists.postgresql.org
Betreff: PostgreSQL HA FO question

Hi everyone,


What is the best / mostly common / production used open source solution for HA 
/ FO/ backup and restore.
Open source community / Commercial

Best Regards,
Dor Ben Dov
This email and the information contained herein is proprietary and confidential 
and subject to the Amdocs Email Terms of Service, which you may review at 
https://www.amdocs.com/about/email-terms-of-service

AW: PostgreSQL HA FO question

[Zwettler Markus (OIZ)]

production experience.



Von: Dor Ben Dov 
Gesendet: Montag, 9. Dezember 2019 13:24
An: Zwettler Markus (OIZ) ; 
pgsql-general@lists.postgresql.org
Betreff: RE: PostgreSQL HA FO question

Hi Zwettler,

Based on what comparison, production experience ?

Regards,
Dor

From: Zwettler Markus (OIZ) 
mailto:markus.zwett...@zuerich.ch>>
Sent: Monday, December 9, 2019 1:13 PM
To: Dor Ben Dov mailto:dor.ben-...@amdocs.com>>; 
pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org>
Subject: AW: PostgreSQL HA FO question

In my opinion, the best open source product is Patroni.



Von: Dor Ben Dov mailto:dor.ben-...@amdocs.com>>
Gesendet: Montag, 9. Dezember 2019 11:24
An: 
pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org>
Betreff: PostgreSQL HA FO question

Hi everyone,


What is the best / mostly common / production used open source solution for HA 
/ FO/ backup and restore.
Open source community / Commercial

Best Regards,
Dor Ben Dov
This email and the information contained herein is proprietary and confidential 
and subject to the Amdocs Email Terms of Service, which you may review at 
https://www.amdocs.com/about/email-terms-of-service<https://eur01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.amdocs.com%2Fabout%2Femail-terms-of-service&data=02%7C01%7Cdor.ben-dov%40amdocs.com%7C847b5c8a4732aa4208d77c98ebc1%7Cc8eca3ca127646d59d9da0f2a028920f%7C0%7C0%7C637114868535818367&sdata=Tp37b8BL9Tc10RzjuYtcDkZtmpA6jyiGCliVEQhk1nM%3D&reserved=0>
This email and the information contained herein is proprietary and confidential 
and subject to the Amdocs Email Terms of Service, which you may review at 
https://www.amdocs.com/about/email-terms-of-service

AW: AW: secure deletion of archived logs

[Zwettler Markus (OIZ)]

> 
> Greetings,
> 
> * Zwettler Markus (OIZ) (markus.zwett...@zuerich.ch) wrote:
> > We use "rsync" on XFS with "wsync" mount mode. I think this should do the 
> > job?
> 
> No, that just makes sure that namespace operations are executed synchronously,
> that doesn't provide any guarantee that the data has actually been written 
> out and
> sync'd.
> 

What else? "rsync" on XFS mounted wsync + execute "sync" afterwards?


> > The tools mentioned will all do backup to disk.
> > We are required to do backup to tape.
> 
> Back up to disk first and then tar to tape.
> 
> Thanks,
> 
> Stephen


Markus

How to log pg_terminate_backend() calls

[Zwettler Markus (OIZ)]

We see some "FATAL:  terminating connection due to administrator command" error 
messages in the Postgres logfile.

We assume someone did pg_terminate_backend() calls.

How can we log such calls, especially who did the call?

periodic refresh of pre-production stages

[Zwettler Markus (OIZ)]

hi,

we have to refresh our DEV and UAT stages periodically with PRD data.

we will to do this by cloning the whole cluster using pg_basebackup or 
restoring our filesystem backup (data + archive).

I saw that the database system identifier stays the same. is there any 
complication on this?

thx

gdal version for Postgis 2.4?

[Zwettler Markus (OIZ)]

I am in the same situation:

I found that Postgis 24 depends on gdal30-libs.x86_64 0:3.0.4-2.
But this installation creates an error :
dbname=# create extension postgis;
ERROR:  could not load library "/usr/pgsql-11/lib/rtpostgis-2.4.so":
/usr/gdal30/lib/libgdal.so.26: undefined symbol: GEOSMakeValid_r
And I found that postgis 24 actually depends on gdal23.

https://www.postgresql.org/message-id/CAAMgDXks6XSX7NxVhggEQvtPQ%2BE1niZsKyK1%2Bwzki8jwuW1wAQ%40mail.gmail.com



My Postgis 2.4 rpm also seems to depend on gdal30-libs-3.0.4:

BuildArch: x86_64
Name:  postgis24_96
Version:   2.4.8
Release:   7.rhel7
...
Requires:  gdal30-libs >= 3.0.4




QUESTIONS:
Does Postgis 2.4 depend on gdal23? 
Is there a bug with the rpm dependency?
How to fix this?



$ uname -a
Linux szhm49251 3.10.0-1062.12.1.el7.x86_64 #1 SMP Thu Dec 12 06:44:49 EST 2019 
x86_64 x86_64 x86_64 GNU/Linux
$ rpm -aq | grep postgres
postgresql96-contrib-9.6.17-1PGDG.rhel7.x86_64
postgresql96-libs-9.6.17-1PGDG.rhel7.x86_64
postgresql96-server-9.6.17-1PGDG.rhel7.x86_64
postgresql96-devel-9.6.17-1PGDG.rhel7.x86_64
postgresql96-9.6.17-1PGDG.rhel7.x86_64
postgresql96-plpython-9.6.17-1PGDG.rhel7.x86_64
$ rpm -aq | grep postgis
postgis24_96-2.4.8-7.rhel7.x86_64
postgis24_96-client-2.4.8-7.rhel7.x86_64
$ rpm -qa | grep gda
gdal-libs-1.11.4-12.rhel7.x86_64
gdal30-libs-3.0.4-1.rhel7.x86_64
gdal23-libs-2.3.2-8.rhel7.x86_64

vacuum full doubled database size

[Zwettler Markus (OIZ)]

We did a "vacuum full" on a database which had been interrupted by a network 
outage.

We found the database size doubled afterwards.

Autovacuum also found a lot of orphaned tables afterwards.

The ophan temp objects went away after a cluster restart while the db size 
remained doubled.

Any idea?

Postgres 9.6.17

pg_upgrade 9.6 to 12 without 9.6 binaries

[Zwettler Markus (OIZ)]

We are running Postgres 9.6 on server A.

We want to upgrade it to Postgres 12 on server B.

pg_upgrade requires the old (-b) and new (-B) binary set on the same server.

We don't want to install both binary sets on the same server as we had some 
library conflicts in the past.

Is there still a way to use pg_upgrade without the old (-b) binary set (I am 
aware of pg_dump/pg_restore)?

Markus

AW: vacuum full doubled database size

[Zwettler Markus (OIZ)]

Thanks. How to get rid of it. New vacuum full?



Von: Michael Loftis 
Gesendet: Freitag, 13. März 2020 14:48
An: Zwettler Markus (OIZ) 
Cc: pgsql-general 
Betreff: Re: vacuum full doubled database size

A vacuum full rebuilds the tables, so yeah if it didn’t successfully complete I 
would expect a lot of dead data.

On Fri, Mar 13, 2020 at 07:41 Zwettler Markus (OIZ) 
mailto:markus.zwett...@zuerich.ch>> wrote:
We did a "vacuum full" on a database which had been interrupted by a network 
outage.

We found the database size doubled afterwards.

Autovacuum also found a lot of orphaned tables afterwards.

The ophan temp objects went away after a cluster restart while the db size 
remained doubled.

Any idea?

Postgres 9.6.17


--

"Genius might be described as a supreme capacity for getting its possessors
into trouble of all kinds."
-- Samuel Butler

AW: Which commands are guaranteed to drop role

[Zwettler Markus (OIZ)]

> -Ursprüngliche Nachricht-
> Von: Laurenz Albe 
> Gesendet: Sonntag, 12. April 2020 22:09
> An: Andrus ; pgsql-general 
> Betreff: Re: Which commands are guaranteed to drop role
> 
> On Sun, 2020-04-12 at 00:25 +0300, Andrus wrote:
> > User "currentuser"  tries to delete role "roletodelete" from this
> > database using
> >
> > But got error
> >
> > ERROR: role "roletodelete" cannot be dropped because some objects
> > depend on it
> > DETAIL: privileges for schema public;
> >
> > How to create script which  is guaranteed to delete role ?
> 
> You cannot write such a script, but you will have to REVOKE and change
> ownership and ALTER DEFAULT PRIVILEGES until no more dependencies on the
> role exist.
> 
> That is why you are well advised not to grant permissions to a role that you 
> plan to
> drop.
> 
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
> 
> 



Is there a query to get all missing privileges not revoked yet?

Markus

Linux Update Experience

[Zwettler Markus (OIZ)]

We are running PGDG Postgres 9.6 and 12 on RHEL7.

Our Linux team does global Linux updates on a quarterly basis (yum update).

We are hitting more and more update problems.

Some troubles this time:

+ Postgis24 has been updated to Postgis30
+ Postgres 12.2 has been updated to Postgres 12.3 claiming missing requirements:

Error: Package: postgresql12-devel-12.3-1PGDG.rhel7.x86_64 
(imx_product_3rd_party_postgresql_repository_postgresql12_rhel7_x86_64)
   Requires: llvm-toolset-7-clang >= 4.0.1

Question: How to you handle your Linux update cycles? Not updating anymore?

Thanks,
Markus

AW: Linux Update Experience

[Zwettler Markus (OIZ)]

Hi Marco,

How do you handle these conflicts? No longer updating that regularly or not at 
all anymore?

Thanks,
Markus




Von: Marco Lechner 
Gesendet: Donnerstag, 28. Mai 2020 11:01
An: Zwettler Markus (OIZ) ; PostgreSQL General 

Betreff: AW: Linux Update Experience

Hi Markus,

at the moment we are facing similar conflicts on Oracle LInux 7 (wich is 
derived from RHEL) - we manage our machines using Spacewalk. The conflicts 
occur (as expected) on Spacewalk as well as on manually using yum:

Fehler: Paket: postgresql11-devel-11.8-1PGDG.rhel7.x86_64 
(oraclelinux7postgresql11)
Benötigt: llvm-toolset-7-clang >= 4.0.1
Fehler: Paket: postgis30_12-3.0.1-5.rhel7.x86_64 (oraclelinux7postgresql12)
Benötigt: proj70 >= 7.0.1
Installiert: proj70-7.0.0-2.rhel7.x86_64 (@oraclelinux7postgresql11)
proj70 = 7.0.0-2.rhel7
Verfügbar: proj70-7.0.0-1.rhel7.x86_64 (oraclelinux7postgresql11)
proj70 = 7.0.0-1.rhel7
Fehler: Paket: postgresql12-devel-12.3-1PGDG.rhel7.x86_64 
(oraclelinux7postgresql12)
Benötigt: llvm-toolset-7-clang >= 4.0.1

Marco


--
Dr. Marco Lechner
Bundesamt fuer Strahlenschutz / Federal Office for Radiation Protection
RN Radiologischer Notfallschutz / Radiological Emergency Preparedness and 
Response
RN1 Koordination Notfallschutzsysteme / Coordination Emergency Systems
Rosastrasse 9 | D-79098 Freiburg | Germany
mlech...@bfs.de<mailto:mlech...@bfs.de> | +49 (0)3018 333 6724 | 
www.bfs.de<http://www.bfs.de>

Hinweis zu Anhängen die auf .p7m/.p7c/.p7s oder .asc/.asc.sig enden:
Die .p7?- und .asc-Dateien sind ungefährliche Signaturdateien (digitale 
Unterschriften). In E-Mail-Clients mit S/MIME Konfiguration (.p7?) oder 
PGP-Erweiterung (.asc) dienen sie zur:
- Überprüfung des Absenders
- Überprüfung einer evtl. Veränderung des Inhalts während der Übermittlung über 
das Internet
Die Signaturdateien können ebenso dazu verwendet werden dem Absender dieser 
Signatur eine E-Mail mit verschlüsseltem Inhalt zu senden. In E-Mail-Clients 
ohne S/MIME Konfiguration oder PGP-Erweiterung erscheinen die Dateien als 
Anhang und können ignoriert werden.



Von: Zwettler Markus (OIZ) [mailto:markus.zwett...@zuerich.ch]
Gesendet: Donnerstag, 28. Mai 2020 09:59
An: PostgreSQL General 
mailto:pgsql-general@lists.postgresql.org>>
Betreff: Linux Update Experience

We are running PGDG Postgres 9.6 and 12 on RHEL7.

Our Linux team does global Linux updates on a quarterly basis (yum update).

We are hitting more and more update problems.

Some troubles this time:

+ Postgis24 has been updated to Postgis30
+ Postgres 12.2 has been updated to Postgres 12.3 claiming missing requirements:

Error: Package: postgresql12-devel-12.3-1PGDG.rhel7.x86_64 
(imx_product_3rd_party_postgresql_repository_postgresql12_rhel7_x86_64)
   Requires: llvm-toolset-7-clang >= 4.0.1

Question: How to you handle your Linux update cycles? Not updating anymore?

Thanks,
Markus

AW: Linux Update Experience

[Zwettler Markus (OIZ)]

> -Ursprüngliche Nachricht-
> Von: Adrian Klaver 
> Gesendet: Donnerstag, 28. Mai 2020 16:15
> An: Zwettler Markus (OIZ) ; PostgreSQL General
> 
> Betreff: Re: Linux Update Experience
> 
> On 5/28/20 12:59 AM, Zwettler Markus (OIZ) wrote:
> > We are running PGDG Postgres 9.6 and 12 on RHEL7.
> >
> > Our Linux team does global Linux updates on a quarterly basis (yum update).
> >
> > We are hitting more and more update problems.
> >
> > Some troubles this time:
> >
> > + Postgis24 has been updated to Postgis30
> >
> > + Postgres 12.2 has been updated to Postgres 12.3 claiming missing
> > requirements:
> >
> > Error: Package: postgresql12-devel-12.3-1PGDG.rhel7.x86_64
> > (imx_product_3rd_party_postgresql_repository_postgresql12_rhel7_x86_64)
> >
> >     Requires: llvm-toolset-7-clang >= 4.0.1
> >
> > Question: How to you handle your Linux update cycles? Not updating anymore?
> 
> See here:
> 
> https://yum.postgresql.org/news-newreporpmsreleased.php
> 
> And if you have community account:
> 
> https://redmine.postgresql.org/issues/5483
> 
> To contact the RPM packagers directly:
> 
> https://yum.postgresql.org/contact.php
> 
> >
> > Thanks,
> >
> > Markus
> >
> 
> 
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
[Zwettler Markus (OIZ)] 



Hi Adrian,

I'm not talking about this specific bug or its resolution.

I want to talk about the Linux update problem in general.

Anyone updating Linux might get such nerving dependency troubles.

How do you handle this situation? Updating more frequently? Updating less 
frequently? Not updating anymore?

Cheers,
Markus

pg_dump / pg_restore version confusion

[Zwettler Markus (OIZ)]

Hi,

An external supplier had a postgres v9.5 database which he dumped with a 
pg_dump v12 client in custom format using PgAdmin4.

Would a pg_restore with a v12 client into a postgres v9.6 database work and be 
officially supported?

Thanks, Markus

AW: pg_dump / pg_restore version confusion

[Zwettler Markus (OIZ)]

And I can also do this restore:


Would a pg_restore with a v12 client into a postgres v9.6 database work and be 
officially supported?


-Markus



> -Ursprüngliche Nachricht-
> Von: Bruce Momjian 
> Gesendet: Mittwoch, 29. Juli 2020 13:49
> An: Zwettler Markus (OIZ) 
> Cc: pgsql-general 
> Betreff: Re: pg_dump / pg_restore version confusion
> 
> On Wed, Jul 29, 2020 at 10:53:34AM +, Zwettler Markus (OIZ) wrote:
> > Hi,
> >
> >
> >
> > An external supplier had a postgres v9.5 database which he dumped with
> > a pg_dump v12 client in custom format using PgAdmin4.
> >
> >
> >
> > Would a pg_restore with a v12 client into a postgres v9.6 database
> > work and be officially supported?
> 
> Yes, you can always use a newer pg_dump on an older database, though the
> reverse is not recommended.  In fact, if you are upgrading to PG 12, it is
> recommended to use pg_dump v12 to dump a Postgres database from an earlier
> version.
> 
> --
>   Bruce Momjian  https://momjian.us
>   EnterpriseDB https://enterprisedb.com
> 
>   The usefulness of a cup is in its emptiness, Bruce Lee

AW: pg_dump / pg_restore version confusion

[Zwettler Markus (OIZ)]

I cannot use pg_restore v9.6 on a pg_dump v12 because otherwise: pg_restore: 
[archiver] unsupported version (1.14) in file header

The external supplier did PG v9.5 database + pg_dump v12. 
I would have to do pg_restore v12 (as of pg_dump v12) into my PG v9.6. 
The version chain would be PG v9.5 => pg_dump v12 => pg_restore v12 => PG v9.6.

That's why my question has been: would the whole chain work and is it supported?



> -Ursprüngliche Nachricht-
> Von: Bruce Momjian 
> Gesendet: Mittwoch, 29. Juli 2020 14:55
> An: Zwettler Markus (OIZ) 
> Cc: pgsql-general 
> Betreff: Re: pg_dump / pg_restore version confusion
> 
> On Wed, Jul 29, 2020 at 12:33:56PM +, Zwettler Markus (OIZ) wrote:
> > And I can also do this restore:
> >
> > 
> > Would a pg_restore with a v12 client into a postgres v9.6 database work and 
> > be
> officially supported?
> > 
> 
> Uh, good question.  You should still use the version of pg_restore that you 
> are
> loading _into_, not what you dumped from.
> 
> --
>   Bruce Momjian  https://momjian.us
>   EnterpriseDB https://enterprisedb.com
> 
>   The usefulness of a cup is in its emptiness, Bruce Lee

How to find out why user processes are using so much memory?

[Zwettler Markus (OIZ)]

Hi,

Having an application on Postgres. 
Most parameters are on default.
Memory related parameters are slightly increased:
effective_cache_size = 512MB
max_connections = 300
maintenance_work_mem = 64MB
shared_buffers = 512MB
wal_buffers = 16MB
work_mem = 10MB

92 user processes are using around 30G RAM + 20G Swap at the moment.
pg_top shows an even distribution of RAM per process (see below).

It seems the application is eating up more and more memory.
Any idea how to find out why the user processes are using that much memory?

Is there a statistic memory usage per statement or something like that?
pg_stat_statements is only showing shared_buffers per statement.

-Markus



last pid: 92572;  load avg:  0.22,  0.32,  0.22;   up 258+21:20:34  
11:22:16
96 processes: 96 sleeping
CPU states:  0.2% user,  0.0% nice,  0.2% system, 99.6% idle,  0.0% iowait
Memory: 45G used, 2091M free, 624K buffers, 13G cached
DB activity:  33 tps,  0 rollbs/s,  81 buffer r/s, 98 hit%,479 row r/s,
7 row w/s s
DB I/O: 5 reads/s,   171 KB/s, 2 writes/s,19 KB/s
DB disk: 190.0 GB total, 28.2 GB free (85% used)
Swap: 19G used, 3407M free, 90M cached

  PID USERNAME PRI NICE  SIZE   RES STATE   TIME   WCPUCPU COMMAND
48064 postgres  200  788M  146M sleep   0:03  0.02%  0.20% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p01_oiz 10.9.132.61(54140) idle
77908 postgres  200 2266M 1960M sleep  32:13  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(44496) idle
60355 postgres  200 2266M 1864M sleep  29:00  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36638) idle
92184 postgres  200 2187M 1893M sleep  28:20  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(33216) idle
60356 postgres  200 2236M 1852M sleep  27:56  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36642) idle
60359 postgres  200 2283M 1906M sleep  24:03  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36644) idle
77909 postgres  200 2219M 1947M sleep  23:59  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(44498) idle
77910 postgres  200 2191M 1918M sleep  22:20  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(44504) idle
60357 postgres  200 2203M 1935M sleep  20:48  0.52%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36635) idle
96423 postgres  200 2242M 1945M sleep  18:29  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(43288) idle
60328 postgres  200 M 1947M sleep  18:23  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36558) idle
99094 postgres  200 2349M 1989M sleep  16:58  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(58596) idle
60339 postgres  200 1774M 1361M sleep  16:10  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36578) idle
60354 postgres  200 2282M 1940M sleep  14:56  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36634) idle
77703 postgres  200 2035M 1714M sleep  12:29  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(45088) idle
60358 postgres  200 1788M  608M sleep  11:43  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.62(36640) idle
110412 postgres  200 2100M  813M sleep  11:26  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(48528) idle
60317 postgres  200 1986M 1634M sleep  11:24  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(40160) idle
58878 postgres  200 2003M 1694M sleep   9:50  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54224) idle
60315 postgres  200 2060M 1805M sleep   9:50  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(40156) idle
51969 postgres  200 2018M  829M sleep   8:54  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54512) idle
58875 postgres  200 1957M 1553M sleep   8:53  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54218) idle
77704 postgres  200 2108M  563M sleep   8:31  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(45090) idle
58876 postgres  200 1925M  831M sleep   8:29  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54220) idle
58880 postgres  200 2236M  807M sleep   8:24  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54228) idle
58862 postgres  200 1684M  559M sleep   7:58  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(54158) idle
77702 postgres  200 1027M  712M sleep   7:06  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz 10.9.132.60(45086) idle
110414 postgres  200 1294M  710M sleep   6:54  0.00%  0.00% postgres: 
pcl_p011: pdb_ppp_oiz pdb_p02_oiz

pgpool-II native replication

[Zwettler Markus (OIZ)]

Hi,

pgpool-II also offers native replication maintaining several real time backups.

The docu also states: "There is no guarantee that any data provided using a 
context-dependent mechanism (e.g. random number, transaction ID, OID, SERIAL, 
sequence), will be replicated correctly on multiple backends."

Has anyone ever used pgpool-II native replication to build an HA environment?

Thanks, Markus

Linux package upgrade without dependency conflicts

[Zwettler Markus (OIZ)]

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

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

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

AW: How to debug authentication issues in Postgres

[Zwettler Markus (OIZ)]

Did you correctly upgrade your whole environment to scram-sha-256?



To upgrade an existing installation from md5 to scram-sha-256, after having 
ensured that all client libraries in use are new enough to support SCRAM, set 
password_encryption = 'scram-sha-256' in postgresql.conf, make all users set 
new passwords, and change the authentication method specifications in 
pg_hba.conf to scram-sha-256.



-Markus



Von: Hemil Ruparel 
Gesendet: Freitag, 27. November 2020 09:38
An: Laurenz Albe 
Cc: pgsql-generallists.postgresql.org 
Betreff: Re: How to debug authentication issues in Postgres

The log says:
> FATAL:  password authentication failed for user "centos"
> DETAIL:  Connection matched pg_hba.conf line 88: "hostuser   password 
>  0.0.0.0/0<http://0.0.0.0/0>   scram-sha-256"

I can't understand where is the problem as both psql and pgadmin connect 
without problems using the same password

On Fri, Nov 27, 2020 at 1:46 PM Hemil Ruparel 
mailto:hemilruparel2...@gmail.com>> wrote:
Sorry. This was the replication section:
local   replication all peer
hostreplication all 127.0.0.1/32<http://127.0.0.1/32>   
 scram-sha-256
hostreplication all ::1/128 scram-sha-256

On Fri, Nov 27, 2020 at 1:41 PM Laurenz Albe 
mailto:laurenz.a...@cybertec.at>> wrote:
On Fri, 2020-11-27 at 13:34 +0530, Hemil Ruparel wrote:
> I have restarted postgres quite a few times to try making configuration 
> changes and it
>  is always back up. I don't know how. Feels weird to me. I didn't add the line
>  "local replication all". It was there by default

I don't believe that.

This is how it looks by default:

# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication all trust
hostreplication all 127.0.0.1/32<http://127.0.0.1/32>   
 trust
hostreplication all ::1/128 trust

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

FDW using remote ODBC driver

[Zwettler Markus (OIZ)]

Hi,

Can I create a FDW connection using an ODBC driver which is installed on a 
remote host, e.g.

Postgres@Linux ==> FDW ==> ODBC@Windows ==> SQL-Server

In other words, must the ODBC driver be installed on the local Linux system?

Thanks,
Markus

AW: FDW using remote ODBC driver

[Zwettler Markus (OIZ)]

Client means Postgres Server / FDW in that case?



Von: Hemil Ruparel 
Gesendet: Dienstag, 1. Dezember 2020 16:08
An: Zwettler Markus (OIZ) 
Cc: pgsql-general@lists.postgresql.org
Betreff: Re: FDW using remote ODBC driver

Yes. The driver needs to be on the client

On Tue, Dec 1, 2020 at 8:34 PM Zwettler Markus (OIZ) 
mailto:markus.zwett...@zuerich.ch>> wrote:
Hi,

Can I create a FDW connection using an ODBC driver which is installed on a 
remote host, e.g.

Postgres@Linux ==> FDW ==> ODBC@Windows ==> SQL-Server

In other words, must the ODBC driver be installed on the local Linux system?

Thanks,
Markus

AW: FDW using remote ODBC driver

[Zwettler Markus (OIZ)]

Wrong direction.

I want to do a Connection FROM Postgres@Linux using fdw + odbc TO 
SQL-Server@Windows.

Question has been if the SQL-Server ODBC driver can also be installed on the 
remote Windows Server in that case.

-Markus



Von: Hemil Ruparel 
Gesendet: Dienstag, 1. Dezember 2020 16:17
An: Zwettler Markus (OIZ) 
Cc: pgsql-general@lists.postgresql.org
Betreff: Re: FDW using remote ODBC driver

The machine you are trying to connect the postgres server from is the client in 
this case. May be a laptop or a production VM or container.

On Tue, Dec 1, 2020 at 8:43 PM Zwettler Markus (OIZ) 
mailto:markus.zwett...@zuerich.ch>> wrote:
Client means Postgres Server / FDW in that case?



Von: Hemil Ruparel 
mailto:hemilruparel2...@gmail.com>>
Gesendet: Dienstag, 1. Dezember 2020 16:08
An: Zwettler Markus (OIZ) 
mailto:markus.zwett...@zuerich.ch>>
Cc: 
pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org>
Betreff: Re: FDW using remote ODBC driver

Yes. The driver needs to be on the client

On Tue, Dec 1, 2020 at 8:34 PM Zwettler Markus (OIZ) 
mailto:markus.zwett...@zuerich.ch>> wrote:
Hi,

Can I create a FDW connection using an ODBC driver which is installed on a 
remote host, e.g.

Postgres@Linux ==> FDW ==> ODBC@Windows ==> SQL-Server

In other words, must the ODBC driver be installed on the local Linux system?

Thanks,
Markus

AW: AW: FDW using remote ODBC driver

[Zwettler Markus (OIZ)]

Question has been for pure interest. I could use a remote ODBC driver with 
Oracle ;-)


> -Ursprüngliche Nachricht-
> Von: Laurenz Albe 
> Gesendet: Dienstag, 1. Dezember 2020 17:14
> An: Zwettler Markus (OIZ) ; Hemil Ruparel
> 
> Cc: pgsql-general@lists.postgresql.org
> Betreff: Re: AW: FDW using remote ODBC driver
> 
> On Tue, 2020-12-01 at 15:23 +, Zwettler Markus (OIZ) wrote:
> > I want to do a Connection FROM Postgres@Linux using fdw + odbc TO SQL-
> Server@Windows.
> 
> Is there any reason for not using tds_fdw?
> 
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com

ldap connection parameter lookup

[Zwettler Markus (OIZ)]

Hi,


I want to use ldap to lookup the connection parameters:
https://www.postgresql.org/docs/12/libpq-ldap.html


Do I have to create one static entry per database within pg_service.conf like:

[mydatabase]
ldap://ldap.mycompany.com/dc=mycompany,dc=com?description?one?(cn=mydatabase)


or is there also some kind of generic variant like this (meaning lookup 
connection parameters for the database name I tell you somehow):

[${PGDATABASE}]
ldap://ldap.mycompany.com/dc=mycompany,dc=com?description?one?(cn=${PGDATABASE})


Thanks, Markus

AW: ldap connection parameter lookup

[Zwettler Markus (OIZ)]

> -Ursprüngliche Nachricht-
> Von: Laurenz Albe 
> Gesendet: Freitag, 15. Januar 2021 17:21
> An: Zwettler Markus (OIZ) ; pgsql-
> gene...@postgresql.org
> Betreff: Re: ldap connection parameter lookup
> 
> On Fri, 2021-01-15 at 14:09 +, Zwettler Markus (OIZ) wrote:
> > I want to use ldap to lookup the connection parameters:
> > https://www.postgresql.org/docs/12/libpq-ldap.html
> >
> >
> > Do I have to create one static entry per database within pg_service.conf 
> > like:
> >
> > [mydatabase]
> >
> ldap://ldap.mycompany.com/dc=mycompany,dc=com?description?one?(cn=mydat
> abase)
> >
> >
> > or is there also some kind of generic variant like this (meaning lookup 
> > connection
> parameters for the database name I tell you somehow):
> >
> > [${PGDATABASE}]
> >
> ldap://ldap.mycompany.com/dc=mycompany,dc=com?description?one?(cn=${PGD
> ATABASE})
> 
> I proposed something like that a while ago:
> https://postgr.es/m/D960CB61B694CF459DCFB4B0128514C2F3442B%40exadv11
> .host.magwien.gv.at
> but it was rejected.
> 
> Perhaps you could come up with a better version.
> 
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com



I'm afraid not. My proposal is as close to yours.

Anyway. PostgreSQL needs some kind of generic central name resolution service.

It is not feasible to do static entries per database in a large environment 
with hundreds of clients.

It's also not feasible to have ordinary endusers have to handle this static 
entry by themselves.

So Tom, lets give it a try?

Regards,
Markus

open service broker api for local PGDG Postgres

[Zwettler Markus (OIZ)]

Does anyone know if there is an open service broker api for a local PGDG 
Postgres installation?

I can only find an osb api for Crunchy Postgres pgo: 
https://github.com/CrunchyData/pgo-osb

-Markus

running vacuumlo periodically?

[Zwettler Markus (OIZ)]

Short question. Is it recommended  - or even best practice - to run vacuumlo 
periodically as a routine maintenance task?

We don't do it. I think if this would be recommended it would have been 
implemented as an autotask like autovacuum. No?

Thanks, Markus

AW: running vacuumlo periodically?

[Zwettler Markus (OIZ)]

> -Ursprüngliche Nachricht-
> Von: Laurenz Albe 
> Gesendet: Donnerstag, 28. Januar 2021 17:39
> An: Zwettler Markus (OIZ) ; pgsql-
> gene...@postgresql.org
> Betreff: Re: running vacuumlo periodically?
> 
> On Thu, 2021-01-28 at 13:18 +, Zwettler Markus (OIZ) wrote:
> > Short question. Is it recommended  - or even best practice – to run vacuumlo
> periodically as a routine maintenance task?
> >
> > We don't do it. I think if this would be recommended it would have been
> implemented as an autotask like autovacuum. No?
> 
> It is recommended to run it regularly if
> - you are using large objects
> - you don't have a trigger in place that deletes large objects that you don't
>   need any more
> 
> Only a small minority of people do that, so it wouldn't make sense to 
> automatically
> run that on all databases.
> 
> Avoid large objects if you can.
> 
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
[Zwettler Markus (OIZ)] 



We didn't recognize that an application is using large objects and didn't 
delete them.
Now we found >100G dead large objects within the database. :-(

Is there any _GENERIC_ query which enables monitoring for orphaned objects 
(dead LO)?

select oid from pg_largeobject_metadata m where not exists
 (select 1 from ANY_EXISTING_TABLE_WITHIN_THE_DATABASE  where m.oid = 
ANY_COLUMN_CONTAINING_OIDs);

check_postgres.pl doesn't have any generic check for it. :-(


Thanks, Markus

AW: running vacuumlo periodically

[Zwettler Markus (OIZ)]

I run "vacuumlo" in batches (-l) which worked well.

I found table "pg_catalog.pg_largeobjects" to be massively bloated afterwards.

I tried "vacuum full pg_catalog.pg_largeobjects" but run out of diskspace 
(although having 250G diskspace free; database size = 400G).


Question: 
Will "vacuum full pg_catalog.pg_largeobjects" need less diskspace when 
"maintenance_work_mem" is increased?




> -----Ursprüngliche Nachricht-
> Von: Zwettler Markus (OIZ) 
> Gesendet: Donnerstag, 28. Januar 2021 18:04
> An: Laurenz Albe ; pgsql-gene...@postgresql.org
> Betreff: AW: running vacuumlo periodically?
> 
> > -Ursprüngliche Nachricht-
> > Von: Laurenz Albe 
> > Gesendet: Donnerstag, 28. Januar 2021 17:39
> > An: Zwettler Markus (OIZ) ; pgsql-
> > gene...@postgresql.org
> > Betreff: Re: running vacuumlo periodically?
> >
> > On Thu, 2021-01-28 at 13:18 +, Zwettler Markus (OIZ) wrote:
> > > Short question. Is it recommended  - or even best practice – to run
> > > vacuumlo
> > periodically as a routine maintenance task?
> > >
> > > We don't do it. I think if this would be recommended it would have
> > > been
> > implemented as an autotask like autovacuum. No?
> >
> > It is recommended to run it regularly if
> > - you are using large objects
> > - you don't have a trigger in place that deletes large objects that you 
> > don't
> >   need any more
> >
> > Only a small minority of people do that, so it wouldn't make sense to
> > automatically run that on all databases.
> >
> > Avoid large objects if you can.
> >
> > Yours,
> > Laurenz Albe
> > --
> > Cybertec | https://www.cybertec-postgresql.com
> [Zwettler Markus (OIZ)]
> 
> 
> 
> We didn't recognize that an application is using large objects and didn't 
> delete
> them.
> Now we found >100G dead large objects within the database. :-(
> 
> Is there any _GENERIC_ query which enables monitoring for orphaned objects
> (dead LO)?
> 
> select oid from pg_largeobject_metadata m where not exists  (select 1 from
> ANY_EXISTING_TABLE_WITHIN_THE_DATABASE  where m.oid =
> ANY_COLUMN_CONTAINING_OIDs);
> 
> check_postgres.pl doesn't have any generic check for it. :-(
> 
> 
> Thanks, Markus
> 
> 

curious vacuum full behavior

[Zwettler Markus (OIZ)]

I have 2 identical systems A + B.
B being a clone of A.


The table pg_catalog.pg_largeobject was identical on both systems: 300GB in 
total size; 100GB bloated.


I did following on A:

?  vacuum full pg_catalog.pg_largeobject;
(using the default maintenance_work_mem of 64MB)
It took around 45 minutes and increased the diskspace by around 125% until the 
vacuum had been finished.


I did following on B:

?  set maintenance_work_mem = '256MB';

?  vacuum full pg_catalog.pg_largeobject;
This took around 5 minutes. I don't know if the diskspace ever increased.


I was really surprised.
Is there any explanation on this behavior?
Is vacuum full heavily using on-disk sort areas if maintenance_work_mem is too 
low?


Postgres Version 9.6


Thanks, Markus

consistent postgresql snapshot

[Zwettler Markus (OIZ)]

PostgreSQL12 running on CentOS7 + ext4.

Would it be save to do a "fsfreeze -freeze" + disk snapshot + "fsfreeze 
-unfreeze" of the PGDATA filesystem to get a consistent snapshot?

I am wondering as PostgreSQL default blocksize = 8k while ext4 default 
blocksize = 4k, resulting in 2 fs blocks per db block.

Thanks, Markus

AW: [Extern] Re: consistent postgresql snapshot

[Zwettler Markus (OIZ)]

I don't want to do use the normal backup algorithm where pg_start_backup + 
pg_stop_backup will fix any fractured block and I am required to have all 
archived logfiles, therefore.

I want to produce an atomic consistent disk snapshot.

I am not sure if freeze/unfreeze will be enough with default values (8k db 
blocksize vs. 4k fs blocksize).

I am quite sure it should be safe when I also change the fs blocksize to 8k 
because 1 IO = 1 db block = 1 fs block than.

Any thoughts?



Von: Ron 
Gesendet: Donnerstag, 12. Mai 2022 01:51
An: pgsql-general@lists.postgresql.org
Betreff: [Extern] Re: consistent postgresql snapshot

On 5/11/22 10:41, Zwettler Markus (OIZ) wrote:

PostgreSQL12 running on CentOS7 + ext4.

Would it be save to do a "fsfreeze –freeze" + disk snapshot + "fsfreeze 
–unfreeze" of the PGDATA filesystem to get a consistent snapshot?

I am wondering as PostgreSQL default blocksize = 8k while ext4 default 
blocksize = 4k, resulting in 2 fs blocks per db block.

If data/base and pg_xlog are on the same filesystem, then it might work.

Be sure to select pg_start_backup('something informative'); beforehand, and 
select pg_stop_backup(); afterwards.

For example:
psql -c "select pg_start_backup('something informative');"
fsfreeze –freeze
disk snapshot
fsfreeze –unfreeze
psql -c "select pg_stop_backup();"
--
Angular momentum makes the world go 'round.

Achtung: Diese E-Mail wurde von einer externen Adresse verschickt. Klicken Sie 
auf keine Links und öffnen Sie keine angehängten Dateien, wenn Sie den Absender 
bzw. die Absenderin nicht kennen. Sind Sie sich unsicher, kontaktieren Sie den 
Service Desk der Stadt Zürich.

Patroni question

[Zwettler Markus (OIZ)]

We had a failover.
I would read the Patroni logs below as following.

2022-09-21 11:13:56,384 secondary did a HTTP GET request to primary. This 
failed with a read timeout.
2022-09-21 11:13:56,792 secondary promoted itself to primary
2022-09-21 11:13:57,279 primary did a HTTP GET request to secondary. An 
exception happend. Probably also due to read timeout.
2022-09-21 11:13:57,983 primary demoted itself

So, the failover has been caused by a network timeout between primary and 
secondary.
QUESTION 1 : Do you agree?

I thought that the Patroni nodes do not communicate directly with each other 
but only by DCS?
QUESTION 2: Is this not correct anymore?



===


patroni version: 2.1.3


===


Patroni Logfile of Host szhm49346 (IP 10.9.132.13) => Primary until Failover
...
...
2022-09-21 11:13:57,279 DEBUG: API thread: 10.9.132.16 - - "GET /patroni 
HTTP/1.1" 200 - latency: 2245.090 ms
2022-09-21 11:13:57,378 ERROR:
Traceback (most recent call last):
  File "/usr/lib/python3.6/site-packages/patroni/dcs/etcd.py", line 566, in 
wrapper
retval = func(self, *args, **kwargs) is not None
  File "/usr/lib/python3.6/site-packages/patroni/dcs/etcd.py", line 696, in 
_update_leader
return self.retry(self._client.write, self.leader_path, self._name, 
prevValue=self._name, ttl=self._ttl)
  File "/usr/lib/python3.6/site-packages/patroni/dcs/etcd.py", line 447, in 
retry
return retry(*args, **kwargs)
  File "/usr/lib/python3.6/site-packages/patroni/utils.py", line 334, in 
__call__
return func(*args, **kwargs)
  File "/usr/lib/python3.6/site-packages/etcd/client.py", line 500, in write
response = self.api_execute(path, method, params=params)
  File "/usr/lib/python3.6/site-packages/patroni/dcs/etcd.py", line 257, in 
api_execute
return self._handle_server_response(response)
  File "/usr/lib/python3.6/site-packages/etcd/client.py", line 987, in 
_handle_server_response
etcd.EtcdError.handle(r)
  File "/usr/lib/python3.6/site-packages/etcd/__init__.py", line 306, in handle
raise exc(msg, payload)
etcd.EtcdCompareFailed: Compare failed : [pcl_p011@szhm49346 != 
pcl_p011@szhm49345]
2022-09-21 11:13:57,558 WARNING: Exception happened during processing of 
request from 10.9.132.16:49080
2022-09-21 11:13:57,965 ERROR: failed to update leader lock
2022-09-21 11:13:57,983 INFO: Demoting self (immediate-nolock)
2022-09-21 11:13:58,214 WARNING: Traceback (most recent call last):
  File "/usr/lib64/python3.6/socketserver.py", line 654, in 
process_request_thread
self.finish_request(request, client_address)
  File "/usr/lib64/python3.6/socketserver.py", line 364, in finish_request
self.RequestHandlerClass(request, client_address, self)
  File "/usr/lib64/python3.6/socketserver.py", line 724, in __init__
self.handle()
  File "/usr/lib64/python3.6/http/server.py", line 418, in handle
self.handle_one_request()
  File "/usr/lib/python3.6/site-packages/patroni/api.py", line 652, in 
handle_one_request
BaseHTTPRequestHandler.handle_one_request(self)
  File "/usr/lib64/python3.6/http/server.py", line 406, in handle_one_request
method()
  File "/usr/lib/python3.6/site-packages/patroni/api.py", line 198, in 
do_GET_patroni
self._write_status_response(200, response)
  File "/usr/lib/python3.6/site-packages/patroni/api.py", line 94, in 
_write_status_response
self._write_json_response(status_code, response)
  File "/usr/lib/python3.6/site-packages/patroni/api.py", line 53, in 
_write_json_response
self._write_response(status_code, json.dumps(response, default=str), 
content_type='application/json')
  File "/usr/lib/python3.6/site-packages/patroni/api.py", line 50, in 
_write_response
self.wfile.write(body.encode('utf-8'))
  File "/usr/lib64/python3.6/socketserver.py", line 803, in write
self._sock.sendall(b)
BrokenPipeError: [Errno 32] Broken pipe
...
...


===


Patroni Logfile of Host szhm49345 (IP 10.9.132.16) => Standby until Failover
...
...
2022-09-21 11:13:54,381 DEBUG: Starting new HTTP connection (1): 
szhm49346.global.szh.loc:8009
2022-09-21 11:13:56,384 WARNING: Request failed to pcl_p011@szhm49346: GET 
http://szhm49346.global.szh.loc:8009/patroni 
(HTTPConnectionPool(host='szhm49346.global.szh.loc', port=8009): Max retries 
exceeded with url: /patroni (Caused by 
ReadTimeoutError("HTTPConnectionPool(host='szhm49346.global.szh.loc', 
port=8009): Read timed out. (read timeout=2)",)))
2022-09-21 11:13:56,484 DEBUG: Writing pcl_p011@szhm49345 to key 
/patroni/pcl_p011/leader ttl=30 dir=False append=False
2022-09-21 11:13:56,485 DEBUG: Converted retries value: 0 -> Retry(total=0, 
connect=None, read=None, redirect=0, status=None)
2022-09-21 11:13:56,562 DEBUG: http://10.7.211.13:2379 "PUT 
/v2/keys/patroni/pcl_p011/leader HTTP/1.1" 201 197
2022-09-21 11:13:56,562 DEBUG: Issuing read for key /patroni/pcl_p011/ with 
args {'recursive': True, 'retry': }
2022-09-21 11:13:56,563 DEBUG: Converted ret

is there still a memory leak with hash joins in PG 12.11 ?

[Zwettler Markus (OIZ)]

Hi,

We are running on PG 12.11.
We recognized an evergrowing memory usage.

work_mem = 50M
there are some regularly hash joins.

I found this blog post talking about a memory leak having hash joins due to a 
larger work_mem.
https://gist.github.com/luhn/2b35a9b31255e3a6a2e6a06d1213dfc9

Does anyone know if this problem is still there in PG 12.11 ?

Thanks,
Markus

pg_isready mandatory parameters?

[Zwettler Markus (OIZ)]

I found this in PG14 ==>


bash-4.4$ ./pg_isready --version
pg_isready (PostgreSQL) 14.2
bash-4.4$ ./pg_isready
/var/run/postgresql:5432 - no attempt
bash-4.4$ ./pg_isready -h localhost
localhost:5432 - no attempt
bash-4.4$ ./pg_isready -h localhost -p 5432
localhost:5432 - no attempt
bash-4.4$ ./pg_isready -h localhost -p 5432 -d postgres
localhost:5432 - no attempt
bash-4.4$ ./pg_isready -h localhost -p 5432 -d postgres -U postgres
localhost:5432 - accepting connections


I always thought all parameters are optional? No?


Thanks,
Markus

postgres restore & needed history files

[Zwettler Markus (OIZ)]

We are using a DIY Postgres backup:
---
psql -c "select pg_start_backup ('Full');"
save -s "${NSR_SERVER}" -g "${NSR_POOL}" "${PGDATA}"
psql -c "select pg_stop_backup();"
---
The pg_wal directory is not saved with it because it is a linked directory.


After some time, we had to perform a point-in-time recovery of 1 of the 5 
databases to a point in time 7 days in the past.
We tried to perform a point-in-time restore on another host, which did not work 
until we copied the contents of the current pg_wal directory.
The current pg_wal directory included 8 history files: 0002.history to 
0009.history.
The point-in-time restore worked smoodly after it had all these history files.


Afaik, all necessary history files should also be restored by the 
restore_command.
I had a look at our archived wal backups and found that 0002.history to 
0008.history files already had been deleted due to our NSR backup retention 
of 30 days.


Question: Is it necessary to retain all history files?

curious postgres (crash) recovery behavior

[Zwettler Markus (OIZ)]

We are using Crunchy PGO which uses "pgbackrest".
Over Christmas we had a disk full error on the "pgbackrest" repo followed by a 
disk full error on the PostgreSQL instance pod.
Unfortunately, a colleague then deleted the current "pg_wal" directory on the 
instance pod.
So we had to do a point-in-time recovery to overcome this situation.



we started a PITR to 2022-12-23 01:34 based on the following "pgbackrest" 
backup:

sh-4.4$ pgbackrest info
stanza: db
status: ok
cipher: none

db (current)
wal archive min/max (13): 
000B010B00C1/000C0111000A

full backup: 20221222-230004F
timestamp start/stop: 2022-12-22 23:00:04 / 2022-12-23 01:32:17
wal start/stop: 000B010B00C1 / 000B010C005C
database size: 46.3GB, database backup size: 46.3GB
repo1: backup set size: 17.6GB, backup size: 17.6GB



During the point-in-time recovery, the "pgbackrest" pod terminated incorrectly 
with an error "pg_ctl: server did not start in time".
There is a known PGO bug on this (pg_ctl default timeout of 60 secs cannot be 
changed).


PGO started the instance pod anyway in following, which leads to a regular 
PostgreSQL crash recovery ending with "last completed transaction was at log 
time 2022-12-23 20:52:29.584555+01":
...
2023-01-04 15:26:35 CET : =>@ : 94-4=>63b58c9b.5e : 0 LOG:  starting 
PostgreSQL 13.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 
(Red Hat 8.5.0-10), 64-bit
2023-01-04 15:26:35 CET : =>@ : 94-5=>63b58c9b.5e : 0 LOG:  listening on 
IPv4 address "0.0.0.0", port 5432
2023-01-04 15:26:35 CET : =>@ : 94-6=>63b58c9b.5e : 0 LOG:  listening on 
IPv6 address "::", port 5432
2023-01-04 15:26:35 CET : =>@ : 94-7=>63b58c9b.5e : 0 LOG:  listening on 
Unix socket "/tmp/postgres/.s.PGSQL.5432"
2023-01-04 15:26:35 CET : =>@ : 108-1=>63b58c9b.6c : 0 LOG:  database 
system was interrupted while in recovery at log time 2022-12-23 01:06:58 CET
2023-01-04 15:26:35 CET : =>@ : 108-2=>63b58c9b.6c : 0 HINT:  If this has 
occurred more than once some data might be corrupted and you might need to 
choose an earlier recovery target.
2023-01-04 15:26:35 CET : =>@ : 108-3=>63b58c9b.6c : 0 LOG:  entering 
standby mode
2023-01-04 15:26:35 CET : =>@ : 108-4=>63b58c9b.6c : 0 LOG:  restored log 
file "000B.history" from archive
2023-01-04 15:26:35 CET : =>@ : 108-5=>63b58c9b.6c : 0 LOG:  restored log 
file "000B010C000C" from archive
...
2023-01-04 15:30:06 CET : =>@ : 108-1198=>63b58c9b.6c : 0 LOG:  restored 
log file "000B011000AD" from archive
2023-01-04 15:30:06 CET : =>@ : 108-1199=>63b58c9b.6c : 0 LOG:  received 
promote request
2023-01-04 15:30:06 CET : =>@ : 108-1200=>63b58c9b.6c : 0 LOG:  redo done 
at 110/AD0005B8
2023-01-04 15:30:06 CET : =>@ : 108-1201=>63b58c9b.6c : 0 LOG:  last 
completed transaction was at log time 2022-12-23 20:52:29.584555+01
2023-01-04 15:30:07 CET : =>@ : 108-1202=>63b58c9b.6c : 0 LOG:  restored 
log file "000B011000AD" from archive
2023-01-04 15:30:07 CET : =>@ : 108-1203=>63b58c9b.6c : 0 LOG:  selected 
new timeline ID: 12
2023-01-04 15:30:07 CET : =>@ : 108-1204=>63b58c9b.6c : 0 LOG:  archive 
recovery complete
2023-01-04 15:30:07 CET : =>@ : 108-1205=>63b58c9b.6c : 0 LOG:  restored 
log file "000B.history" from archive
2023-01-04 15:30:07 CET : =>@ : 94-9=>63b58c9b.5e : 0 LOG:  database system 
is ready to accept connections
...



QUESTION:
=
I wondered how the crash recovery can run to 2022-12-23 20:52:29.584555 and 
simply open the database?

When I got it right the full backup itself would be consistent with "wal stop 
000B010C005C".
PostgreSQL additionally added some wal files until 000B011000AD 
(2022-12-23 20:52:29.584555) because it didn't find any more before the 
recovery target of 2022-12-23 01:34.

Am I right or did I miss something?

AW: [Extern] Re: postgres restore & needed history files

[Zwettler Markus (OIZ)]

> -Ursprüngliche Nachricht-
> Von: Laurenz Albe 
> Gesendet: Freitag, 6. Januar 2023 06:28
> An: Zwettler Markus (OIZ) ; pgsql-
> gene...@lists.postgresql.org
> Betreff: [Extern] Re: postgres restore & needed history files
> 
> On Tue, 2023-01-03 at 16:03 +, Zwettler Markus (OIZ) wrote:
> > We are using a DIY Postgres backup:
> > ---
> > psql -c "select pg_start_backup ('Full');"
> > save -s "${NSR_SERVER}" -g "${NSR_POOL}" "${PGDATA}"
> > psql -c "select pg_stop_backup();"
> > ---
> > The pg_wal directory is not saved with it because it is a linked directory.
> >
> >
> > After some time, we had to perform a point-in-time recovery of 1 of the 5
> databases to a point in time 7 days in the past.
> > We tried to perform a point-in-time restore on another host, which did not 
> > work
> until we copied the contents of the current pg_wal directory.
> > The current pg_wal directory included 8 history files: 0002.history to
> 0009.history.
> > The point-in-time restore worked smoodly after it had all these history 
> > files.
> >
> >
> > Afaik, all necessary history files should also be restored by the
> restore_command.
> > I had a look at our archived wal backups and found that 0002.history to
> 0008.history files already had been deleted due to our NSR backup 
> retention
> of 30 days.
> >
> > Question: Is it necessary to retain all history files?
> 
> Yes, the history files are an integral part of the database.
> You must not delete them from your WAL archive.
> 
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
> 



What would you do in case of a disaster when all history files in pg_wal are 
gone and also deleted in the backup due to the backup retention?

Thanks, Markus

AW: AW: [Extern] Re: postgres restore & needed history files

[Zwettler Markus (OIZ)]

> -Ursprüngliche Nachricht-
> Von: Laurenz Albe 
> Gesendet: Freitag, 13. Januar 2023 11:25
> An: Zwettler Markus (OIZ) ; pgsql-
> gene...@lists.postgresql.org
> Betreff: Re: AW: [Extern] Re: postgres restore & needed history files
> 
> On Thu, 2023-01-12 at 16:50 +, Zwettler Markus (OIZ) wrote:
> > What would you do in case of a disaster when all history files in
> > pg_wal are gone and also deleted in the backup due to the backup retention?
> 
> Easy.  Take a new base backup immediately and don't try to restore a backup 
> that
> was taken on an earlier timeline.
> 
> Yours,
> Laurenz Albe


Hope I got you right.

You meant the latest backup within the latest timeline is always working even 
if the latest history files are gone.

Thanks, Markus

Is a logical replication crash recovery safe?

[Zwettler Markus (OIZ)]

Is a logical replication crash recovery safe?

Thanks, Markus

AW: [Extern] Re: Is a logical replication crash recovery safe?

[Zwettler Markus (OIZ)]

> -Ursprüngliche Nachricht-
> Von: Christophe Pettus 
> Gesendet: Dienstag, 24. Januar 2023 16:48
> An: Zwettler Markus (OIZ) 
> Cc: pgsql-general@lists.postgresql.org
> Betreff: [Extern] Re: Is a logical replication crash recovery safe?
> 
> 
> 
> > On Jan 24, 2023, at 07:37, Zwettler Markus (OIZ) 
> > 
> wrote:
> >
> > Is a logical replication crash recovery safe?
> 
> You might want to give more detail about the scenario you are concerned about.
> Changes that are received by a subscriber as part of logical replication are 
> WAL-
> logged, observe transaction semantics, and will be replayed if the subscriber
> crashes and restarts.
> 


Sorry. Let me refine.

If I create a logical replication slot and use the replication slot to publish 
some tables.

Will the information in the replication slot be safe for any subscription even 
if a crash recovery happened on the publisher node?

How to install vacuumlo on a client?

[Zwettler Markus (OIZ)]

I want to install vacuumlo on a client.

I would install client + contrib + libs package:

yum install postgresql15 postgresql15-contrib postgresql15-libs

Is this enough or are there some other rpm / libs needed?

Postgres Enhancement Request

[Zwettler Markus (OIZ)]

CREATEROLE allows users to create new roles also having the CREATEDB privilege 
(at least in version 9.6).

We want special users to be able to CREATEROLE without being able to CREATEDB 
(eg. when usermanagement is done by the application itself).

Please prevent users with CREATEROLE to create roles having CREATEDB (analogous 
SUPERUSER and REPLICATION).

Thanks

AW: Postgres Enhancement Request

[Zwettler Markus (OIZ)]

We already did and use this at the moment. Unfortunately.

Some out-of-the-box applications can't use functions for user management.
Some users don't want "special" functions for user management.
...

Markus



-Ursprüngliche Nachricht-
Von: Thomas Kellerer  
Gesendet: Mittwoch, 20. März 2019 11:45
An: pgsql-general@lists.postgresql.org
Betreff: Re: Postgres Enhancement Request

Zwettler Markus (OIZ) schrieb am 20.03.2019 um 11:10:
> CREATEROLE allows users to create new roles also having the CREATEDB 
> privilege (at least in version 9.6).
> 
> We want special users to be able to CREATEROLE without being able to CREATEDB 
> (eg. when usermanagement is done by the application itself).
> 
> Please prevent users with CREATEROLE to create roles having CREATEDB 
> (analogous SUPERUSER and REPLICATION).

I agree that would be a welcome enhancement. 

As a workaround, you can create a function owned by a superuser (or any other 
user with the "createrole" privilege) using "security definer" that provides a 
simple "create user" capability and makes sure that the created user does not 
have the createdb privilege. 

The user/role that should be able to create new roles doesn't need the 
createrole privilege at all then. 
All it needs is the execute privilege on the function.

Thomas

AW: Postgres Enhancement Request

[Zwettler Markus (OIZ)]

Yes, that would be totally ok. Like the "with [grant|admin] option" privilege 
model in SQL. It should be done with all these predefined top-level database 
roles like CREATEROLE.

It's doesn't only seem bogus but also a security hole when users can get 
privileges they have never been granted.

Markus




-Ursprüngliche Nachricht-
Von: Tom Lane  
Gesendet: Mittwoch, 20. März 2019 15:30
An: Thomas Kellerer 
Cc: pgsql-general@lists.postgresql.org
Betreff: Re: Postgres Enhancement Request

Thomas Kellerer  writes:
> Tom Lane schrieb am 20.03.2019 um 14:59:
>> No, it wouldn't.  The point of CREATEROLE is to allow user creation 
>> and deletion to be done by a role that's less than full superuser.
>> If we changed it like that, then you'd be right back at needing 
>> superuser for very routine role creations.  That's *not* an 
>> improvement, even if it somehow fit better into the OP's desired 
>> security model (which he hasn't explained).

> I didn't take this to be a request to remove the createdb privilege in 
> general, but a request to have finer grained control what kind of privileges 
> the role with createrole can grant to newly created roles (or what it can do 
> in general).

Hmm.  Thinking about it a bit more carefully, it does seem bogus that a role 
that has CREATEROLE but not CREATEDB can make a role that has the latter 
privilege.  It would be more sensible to have a uniform rule that "you can't 
grant a privilege you don't have yourself", which would mean that the OP's 
problem could perhaps be solved by making a role that has CREATEROLE but not 
CREATEDB.

You could imagine going further and applying the full SQL privilege model to 
these things, which would make it possible to have a role that has CREATEDB (so 
can make DBs itself) but can't pass that privilege on to others for lack of 
grant options on CREATEDB.
But that would be a very much bigger chunk of work, and I'm not sure I see the 
payback.

regards, tom lane

Cast INTEGER to BIT confusion

[[Quipsy] Markus Karg]

Hello PostgreSQL Community,

I have 25+ years of experience with some other RDBMS, but I am a PostgreSQL 
starter, so I assume the following is rather a simple beginner's question...:

I like to store just a single bit but that can be either 1 or 0, so I tried to 
do this:

CREATE TABLE T (c BIT);
INSERT INTO T VALUES (1);
-- I MUST NOT change both lines in any way as these are part of a third-party 
application!

Unfortunately this tells me:

column "c" is of type bit but expression is of type integer

So I logged in as a cluster admin and I tried this:

CREATE CAST (integer AS bit) WITH INOUT AS IMPLICIT;

Unfortunately that tells me:

cast from type integer to type bit already exists

This is confusing! Apparently PostgreSQL 15.3 comes with the needed cast 
out-of-the-box but it does not apply it? This is confusing!

What is my fault?

Thanks!
-Markus

AW: Cast INTEGER to BIT confusion

[[Quipsy] Markus Karg]

Thank you, Erik! Prefixing the search path in fact looks very interesting, and 
I think in this particular application it is a safe (and the only) solution.

Is setting the search path something that has to be done for each new 
connection / each user, or is this something static and global for the database?

Thanks a lot!
-Markus


-Ursprüngliche Nachricht-
Von: Erik Wienhold  
Gesendet: Dienstag, 15. August 2023 13:48
An: [Quipsy] Markus Karg ; pgsql-general@lists.postgresql.org
Betreff: Re: Cast INTEGER to BIT confusion

> On 15/08/2023 10:49 CEST [Quipsy] Markus Karg  wrote:
>
> Hello PostgreSQL Community,
>
> I like to store just a single bit but that can be either 1 or 0, so I 
> tried to do this:
>
> CREATE TABLE T (c BIT);
> INSERT INTO T VALUES (1);
> -- I MUST NOT change both lines in any way as these are part of a third-party 
> application!
>
> Unfortunately this tells me:
>
> column "c" is of type bit but expression is of type integer
>
> So I logged in as a cluster admin and I tried this:
>
> CREATE CAST (integer AS bit) WITH INOUT AS IMPLICIT;
>
> Unfortunately that tells me:
>
> cast from type integer to type bit already exists
>
> This is confusing! Apparently PostgreSQL 15.3 comes with the needed 
> cast out-of-the-box but it does not apply it? This is confusing!
>
> What is my fault?

The built-in cast is explicit (castcontext = 'e'):

=# SELECT * FROM pg_cast WHERE castsource = 'int'::regtype AND 
casttarget = 'bit'::regtype;
  oid  | castsource | casttarget | castfunc | castcontext | castmethod
---+++--+-+
 10186 | 23 |   1560 | 1683 | e   | f
(1 row)

It's not possible to drop that cast and replace it with a custom one:

=# DROP CAST (int AS bit);
ERROR:  cannot drop cast from integer to bit because it is required by 
the database system

You could create a custom domain if you're only interested in values 0 and 1 
and don't use bit string functions.  The search path must be changed so that 
domain bit overrides pg_catalog.bit:

=# CREATE SCHEMA xxx;
=# CREATE DOMAIN xxx.bit AS int;
=# SET search_path = xxx, pg_catalog;
=# CREATE TABLE t (c bit);
=# INSERT INTO t VALUES (1);
INSERT 0 1

But I would do that only if the third-party code cannot be tweaked because the 
custom domain could be confusing.  It's also prone to errors as it relies on a 
specific search path order.  Also make sure that regular users cannot create 
objects in schema xxx that would override objects in pg_catalog.

--
Erik

AW: Cast INTEGER to BIT confusion

[[Quipsy] Markus Karg]

Erik,

I just tried out your proposal on PostgreSQL 15.3 and this is the result:

ERROR:  column "c" is of type bit but expression is of type integer
LINE 5:   INSERT INTO t VALUES (1);
^
HINT:  You will need to rewrite or cast the expression.

Apparently the search path is ignored?!

-Markus

-Ursprüngliche Nachricht-
Von: Erik Wienhold  
Gesendet: Dienstag, 15. August 2023 13:48
An: [Quipsy] Markus Karg ; pgsql-general@lists.postgresql.org
Betreff: Re: Cast INTEGER to BIT confusion

> On 15/08/2023 10:49 CEST [Quipsy] Markus Karg  wrote:
>
> Hello PostgreSQL Community,
>
> I like to store just a single bit but that can be either 1 or 0, so I 
> tried to do this:
>
> CREATE TABLE T (c BIT);
> INSERT INTO T VALUES (1);
> -- I MUST NOT change both lines in any way as these are part of a third-party 
> application!
>
> Unfortunately this tells me:
>
> column "c" is of type bit but expression is of type integer
>
> So I logged in as a cluster admin and I tried this:
>
> CREATE CAST (integer AS bit) WITH INOUT AS IMPLICIT;
>
> Unfortunately that tells me:
>
> cast from type integer to type bit already exists
>
> This is confusing! Apparently PostgreSQL 15.3 comes with the needed 
> cast out-of-the-box but it does not apply it? This is confusing!
>
> What is my fault?

The built-in cast is explicit (castcontext = 'e'):

=# SELECT * FROM pg_cast WHERE castsource = 'int'::regtype AND 
casttarget = 'bit'::regtype;
  oid  | castsource | casttarget | castfunc | castcontext | castmethod
---+++--+-+
 10186 | 23 |   1560 | 1683 | e   | f
(1 row)

It's not possible to drop that cast and replace it with a custom one:

=# DROP CAST (int AS bit);
ERROR:  cannot drop cast from integer to bit because it is required by 
the database system

You could create a custom domain if you're only interested in values 0 and 1 
and don't use bit string functions.  The search path must be changed so that 
domain bit overrides pg_catalog.bit:

=# CREATE SCHEMA xxx;
=# CREATE DOMAIN xxx.bit AS int;
=# SET search_path = xxx, pg_catalog;
=# CREATE TABLE t (c bit);
=# INSERT INTO t VALUES (1);
INSERT 0 1

But I would do that only if the third-party code cannot be tweaked because the 
custom domain could be confusing.  It's also prone to errors as it relies on a 
specific search path order.  Also make sure that regular users cannot create 
objects in schema xxx that would override objects in pg_catalog.

--
Erik

AW: PostgreSQL and GUI management

[[Quipsy] Markus Karg]

I am using pg_admin in the browser, and it works rather fine for me.
-Markus

Von: Jason Long 
Gesendet: Dienstag, 15. August 2023 17:09
An: pgsql-general@lists.postgresql.org
Betreff: PostgreSQL and GUI management

Hello,
Does PostgreSQL have a graphical environment for management or is it only 
managed through CLI?


Thank you.

AW: AW: Cast INTEGER to BIT confusion

[[Quipsy] Markus Karg]

I am giving up. While even `SELECT current_schemas(true)` correctly prints 
`xxx, pg_catalog` it still uses the original bit type. This is completely NOT 
as described in the documentation, where it is clearly told that pg_catalog 
only is searched immediately if NOT found in the search path. It seems it is 
simply impossible to run this application on PostgreSQL, and we have to stick 
with a different RDBMS. Very sad.
-Markus


-Ursprüngliche Nachricht-
Von: Erik Wienhold  
Gesendet: Dienstag, 15. August 2023 16:28
An: [Quipsy] Markus Karg ; pgsql-general@lists.postgresql.org
Betreff: Re: AW: Cast INTEGER to BIT confusion

> On 15/08/2023 14:02 CEST [Quipsy] Markus Karg  wrote:
>
> I just tried out your proposal on PostgreSQL 15.3 and this is the result:
>
> ERROR:  column "c" is of type bit but expression is of type integer
> LINE 5:   INSERT INTO t VALUES (1);
> ^
> HINT:  You will need to rewrite or cast the expression.
>
> Apparently the search path is ignored?!
>
> -Ursprüngliche Nachricht-
> Von: Erik Wienhold 
> Gesendet: Dienstag, 15. August 2023 13:48
> An: [Quipsy] Markus Karg ; 
> pgsql-general@lists.postgresql.org
> Betreff: Re: Cast INTEGER to BIT confusion
>
> You could create a custom domain if you're only interested in values 0 
> and 1 and don't use bit string functions.  The search path must be 
> changed so that domain bit overrides pg_catalog.bit:
> 
>   =# CREATE SCHEMA xxx;
>   =# CREATE DOMAIN xxx.bit AS int;
>   =# SET search_path = xxx, pg_catalog;
>   =# CREATE TABLE t (c bit);
>   =# INSERT INTO t VALUES (1);
>   INSERT 0 1
>
> But I would do that only if the third-party code cannot be tweaked 
> because the custom domain could be confusing.  It's also prone to 
> errors as it relies on a specific search path order.  Also make sure 
> that regular users cannot create objects in schema xxx that would override 
> objects in pg_catalog.

Hmm, I thought that Postgres resolves all types through the search path, but 
apparently that is not the case for built-in types.  I never used this to 
override built-in types so this is a surprise to me.  (And obviously I haven't 
tested the search path feature before posting.)

Neither [1] or [2] mention that special (?) case or if there's a distinction 
between built-in types and user-defined types.  The USAGE privilege is required 
according to [2] but I was testing as superuser anyway.

[1] https://www.postgresql.org/docs/15/ddl-schemas.html
[2] 
https://www.postgresql.org/docs/15/runtime-config-client.html#GUC-SEARCH-PATH

--
Erik

moving data with pg_dump/pg_restore between database with different locale

[Zwettler Markus (OIZ)]

hi,

if you want to retrieve data by means of pg_dump / pg_restore from a database 
with locale de_CH.UTF8 into a database with locale en_US.UTF8 are there any 
other things to consider than the behavior implications mentioned in the 
documentation:

https://www.postgresql.org/docs/current/locale.html#LOCALE-BEHAVIOR

Thanks,
Markus

best migration solution

[Zwettler Markus (OIZ)]

we have to migrate from hosted PG12 to containerized PG16 on private cloud.

some of the installed PG12 extensions are not offered on the containerized 
PG16, eg. PostGIS related extensions like pg_routing and ogr_fdw.
some of these extensions are not needed anymore. some of these extensions were 
installed in their own schema.

we also need to change the database names and most role names due to external 
requirements.


I came up with this solution.

dump all roles with pg_dumpall.
edit this dumpfile and

  *   exclude roles not needed
  *   change required role names

dump all required databases with pg_dump

  *   in plain text
  *   exclude all schemas not needed
edit this dump file and

  *   exclude any "create extension" command for not existing extensions
  *   change all required role names on permissions and ownerships


any missings?
any better solutions?


I wonder whether a plain text dump could lead to conversion problems or 
something similar?

how to completely turn off statement error logging

[Zwettler Markus (OIZ)]

I don't want to log statement errors in the server logfile - whether the 
statement string nor the error message.


I set "log_min_error_statement = panic" according to the docs:


To effectively turn off logging of failing statements, set this parameter to 
PANIC.



But error messages are still logged:

$ psql
psql (12.17)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 
256, compression: off)
Type "help" for help.

pcl_l300=# show log_min_error_statement;
log_min_error_statement
-
panic
(1 row)

pcl_l300=# select error_817 / 0;
ERROR:  column "error_817" does not exist
LINE 1: select error_817 / 0;
   ^
pcl_l300=# exit
$ fgrep error_817 pg_statsinfo.log
2024-05-13 16:01:14 CEST 36421 ERROR:  column "error_817" does not exist


Question: How can I turn off logging of this statement based error message?

AW: [Extern] Re: how to completely turn off statement error logging

[Zwettler Markus (OIZ)]

> Von: Tom Lane 
> Gesendet: Montag, 13. Mai 2024 16:26
> An: Zwettler Markus (OIZ) 
> Cc: pgsql-general@lists.postgresql.org
> Betreff: [Extern] Re: how to completely turn off statement error logging
> 
> "Zwettler Markus (OIZ)"  writes:
> > I don't want to log statement errors in the server logfile - whether the 
> > statement
> string nor the error message.
> 
> You need to set log_min_messages higher than ERROR.  You might consider
> using LOG or FATAL rather than PANIC, though.
> 
> > I set "log_min_error_statement = panic" according to the docs:
> > To effectively turn off logging of failing statements, set this parameter to
> PANIC.
> 
> This setting controls whether the STATEMENT: detail is appended to a message,
> but not the basic choice of whether to emit the message.
> 
> regards, tom lane
> --- Externe Email: Vorsicht mit Anhängen, Links oder dem Preisgeben von
> Informationen ---



please let me refine.

I would like to suppress all errors in the server logfile coming from client 
applications, i.e. statement level errors such as "duplicate key violates..."

but I do not want to suppress errors that are related to infrastructure 
problems, i.e. "could not open file..."

if I set log_min_messages higher than ERROR, errors concerning the 
infrastructure would also be suppressed, wouldn't they?

thanks, markus

PG16.1 security breach?

[Zwettler Markus (OIZ)]

I am running the following on Postgres 16.1 in database "postgres" as a 
superuser:

revoke create on schema public from public;
revoke create on database postgres from public;
create schema if not exists oiz;
revoke create on schema oiz from public;
grant usage on schema oiz to public;

create or replace function oiz.f_set_dbowner (p_dbowner text, p_dbname text)
returns void
language plpgsql
security definer
as $$
...



when I create a new role in following:

create role testuser with password 'testuser' login;

postgres=# \du testuser
 List of roles
Role name | Attributes
---+
testuser  |



than this new role is able to execute the function oiz.f_set_dbowner 
immediately even I did not grant execute on this function to this role!

postgres=> \conninfo
You are connected to database "postgres" as user "testuser" on host 
"cmpgdb-pg-eng900.eng.cmp.szh.loc" (address "10.199.112.56") at port "5017".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: 
off)
postgres=> select oiz.f_set_dbowner ('testuser','database1');
f_set_dbowner
---

(1 row)



The role is also able to execute the function even I revoke any execute 
privilege explicitly:

revoke execute on function oiz.f_set_dbowner (p_dbowner text, p_dbname text) 
from testuser;



There are also no default privileges on the schema:

postgres=# \ddp
 Default access privileges
Owner | Schema | Type | Access privileges
---++--+---
(0 rows)


postgres=> \df+ oiz.f_set_dbowner

   List of functions
Schema | Name  | Result data type |  Argument data types  | 
Type | Volatility | Parallel |  Owner   | Security |  Access privileges  | 
Language | Internal name | Description
+---+--+---+--++--+--+--+-+--+---+-
oiz| f_set_dbowner | void | p_dbowner text, p_dbname text | 
func | volatile   | unsafe   | postgres | definer  | =X/postgres+| 
plpgsql  |   |
|   |  |   |
  ||  |  |  | postgres=X/postgres | 
 |   |
(1 row)


postgres=> \l postgres
   List of databases
   Name   |  Owner   | Encoding | Locale Provider |   Collate   |Ctype| 
ICU Locale | ICU Rules |   Access privileges
--+--+--+-+-+-++---+---
postgres | postgres | UTF8 | libc| de_CH.utf-8 | de_CH.utf-8 |  
  |   | =Tc/postgres +
  |  |  | | | | 
   |   | postgres=CTc/postgres
(1 row)



What I am missing? Is there something new with PG 16? Is it a bug?



Cheers, Markus

AW: [Extern] Re: PG16.1 security breach?

[Zwettler Markus (OIZ)]

> -Ursprüngliche Nachricht-
> Von: Joe Conway 
> Gesendet: Freitag, 7. Juni 2024 15:22
> An: Zwettler Markus (OIZ) ; pgsql-
> gene...@lists.postgresql.org
> Betreff: [Extern] Re: PG16.1 security breach?
> 
> On 6/7/24 07:04, Zwettler Markus (OIZ) wrote:
> > I am running the following on Postgres 16.1 in database "postgres" as
> > a
> > superuser:
> 
> 
> 
> > create or replace function oiz.f_set_dbowner (p_dbowner text, p_dbname
> > text)
> 
> 
> 
> > create role testuser with password 'testuser' login;
> 
> 
> 
> > than this new role is able to execute the function oiz.f_set_dbowner
> > immediately even I did not grant execute on this function to this role!
> 
> See:
> https://www.postgresql.org/docs/current/sql-createfunction.html
> 
> In particular, this part:
> 8<
> Another point to keep in mind is that by default, execute privilege is 
> granted to
> PUBLIC for newly created functions (see Section 5.7 for more information).
> Frequently you will wish to restrict use of a security definer function to 
> only some
> users. To do that, you must revoke the default PUBLIC privileges and then 
> grant
> execute privilege selectively.
> To avoid having a window where the new function is accessible to all, create 
> it and
> set the privileges within a single transaction. For example:
> 8<
> 
> HTH,
> 
> --
> Joe Conway
> PostgreSQL Contributors Team
> RDS Open Source Databases
> Amazon Web Services: https://aws.amazon.com
> 
> --- Externe Email: Vorsicht mit Anhängen, Links oder dem Preisgeben von
> Informationen ---


Argh. No! What a bad habit!

Might be good idea for an enhancement request to create a global parameter to 
disable this habit.

Thanks Markus

question on plain pg_dump file usage

[Zwettler Markus (OIZ)]

I have to do an out-of-place Postgres migration from PG12 to PG16 using:

pg_dump -F p -f dump.sql ...
sed -i "s/old_name/new_name/g"
psql -f dump.sql ...

Both databases are on UTF-8.

I wonder if there could be character set conversion errors here, as the data is 
temporarily written to a plain text file.

Thanks, Markus