Re: Postgresql HA cluster

[Jehan-Guillaume de Rorthais]

Hi Jason,

On Tue, 17 Oct 2023 19:59:00 +
Jason Grammenos  wrote:

[...]
> If you have 2 PostgreSQL nodes hooked up to a Load balancer (haproxy), and
> you move take node1 out of load balancing, you now have connections on node1
> and connections on node2, as the Load balancer drains the connections off
> node1 and over to node2. How does PostgreSQL handle this scenario when there
> are writes happening on both nodes?

As you write about "writes happening on both nodes", you describe a
Multi-primary architecture that PostgreSQL does not support.

A standby is in "recovery mode", receiving and applying WALs directly from
the primary (or archives).

A standby will only accept read-only (RO) queries (providing you set
"hot_standby=on") as it can not produce WAL itself. Any write attempt raises
an ERROR to the client. This is a really strict state, not an advisory one. You
just can't write on a standby, even as a superuser.

So, in the situation you describe, as long as the standby is not promoted to
primary, any write (RW) query on it will just fails hard.

Obviously, you should definitely NOT promote a standby while another primary is
still alive in the cluster. You would end up with a bad split brain scenario.

About haproxy, you should use tcp-check (eg. on a Systemd socket) or http-check
(eg. on Patroni API) to decide on which server the primary is hosted. It
avoids this transition period where both nodes have RW connections.

> If instead you have 2 PostgreSQL nodes behind pacemaker (controlling a
> floating ip), what happens when you initiate a failover and move the floating
> ip? You want the connections to drain off node1 and move to node2. Again in
> this scenario, both nodes would be sustaining writes at some point in time.

No, both nodes would not be sustaining writes in the same time. Two primaries
can not leave in the same cluster in the same time, this would be a split brain
scenario. If this happen, you have to pick one and rewind or restore it from
PITR, losing the transactions it wrote in the meantime. Of course, you can also
compare both nodes and tries to recover missing transaction before restoring
one of them, but it takes a lot of time...That's why clusters (not just for
PostgreSQL) tries hard to avoid split brain scenario using fencing, quorum,
watchdog, etc.

Before moving the IP address, you have to demote your primary as standby.
Unfortunately, this can not be achieved without restarting the primary
PostgreSQL as a standby one on its node. But because demote == restart as
standby, you effectively end all clients sessions. At this point, all your
nodes will be in standby mode, without primary to feed them.

So, during a switchover Pacemaker will:

* demote your current primary (ending all sessions)
* promote the other standby as primary
* move the IP address to the new primary (colocation rule in Pacemaker between
  the IP and the promoted role).

As soon as a primary appears in the cluster and the IP address (or haproxy)
points on it, all other standbies nodes will start replicating from it.

> How does the write only replica get changed out of write mode during this
> failover?

"write only"? Do you speak about the primary in RW mode or about a standby in
RO mode? 

For a primary: a PostgreSQL restart.
For a standby: promotion happen live, all existing sessions are kept and can
then issue write queries.

> How does the primary node get switched to read only after the
> connection drain is complete?

A PostgreSQL restart.

> Overall, I am trying to understand what it looks like operationally to run a
> 2 node postgresql "cluster" and how patching both nodes would work (and
> mentally comparing it to what I currently do with mysql).
> 
> You recommended that primary-standby could be sufficient and is much simpler,
> so I am simply trying to wrap my head around what exactly running it would
> look like. If primary standby is simple enough to failover, patch , reboot,
> maintain, etc. Then you could be correct that master->master may not be
> needed.

And if you have a strong, safe and high available storage, a shared storage
cluster for the primary is even simpler IMHO (because you move all the
complexity off your hands to the storage guys ones).

For details, I recommend reading this chapter in documentation:
https://www.postgresql.org/docs/current/high-availability.html

Regards,

Re: Index based search have issues with 11.20

[Hafeez Rahim]

Thanks for the providing the references

we will go through the wiki link and identify the glibc version updates
during the OS patching.


On Tue, Oct 17, 2023 at 10:18 PM Tom Lane  wrote:

> Hafeez Rahim  writes:
> > Issue :
>
> > One of the table query not woks when string values are filtered with =
> > clause
> > where as the query works with like clause using '%' flag  or using
> > trim(column)
> > below are query examples describes the problem
>
> You've not really provided enough detail, but I'm going to guess
> that your OS update included a change in collation definitions
> that left any indexes on text columns essentially corrupt.
> The LIKE queries probably weren't affected because they didn't
> rely on indexes.
>
> Reindexing the indexes should have been enough to fix it.
> See
>
> https://wiki.postgresql.org/wiki/Locale_data_changes
>
> regards, tom lane
>


-- 



*Regards,Hafeez Database Technical Lead*

Mindcurv TSPL
#6F, T-1, World Trade Centre,
Infopark CSEZ, P-1,
Kakkanad, Cochin - 682042

t: +91 (0)484 2100164
m: +91 9605445282
*www.mindcurv.com *

* CONFIDENTIALITY NOTICE: This e-mail and any attachments hereto are
intended only for use by the addressee(s) named herein and may contain
legally privileged and/or confidential information. If you are not the
intended recipient of this e-mail, you are hereby notified that any
dissemination, distribution or copying of this e-mail, and any attachments
thereto, is strictly prohibited. If you have received this e-mail in error,
please notify the sender by replying to this message and permanently delete
the original and any copy of this e-mail and any printout thereof.*

-- 
--
CONFIDENTIALITY NOTICE: This e-mail and any attachments hereto are 
intended only for use by the addressee(s) named herein and may contain 
legally privileged and/or confidential information. If you are not the 
intended recipient of this e-mail, you are hereby notified that any 
dissemination, distribution or copying of this e-mail, and any attachments 
thereto, is strictly prohibited. If you have received this e-mail in error, 
please notify the sender by replying to this message and permanently delete 
the original and any copy of this e-mail and any printout thereof.

Re: Index based search have issues with 11.20

[Hafeez Rahim]

Thanks for the details

We will further check on the references given

On Tue, Oct 17, 2023 at 10:20 PM Adrian Klaver 
wrote:

> On 10/17/23 11:24, Hafeez Rahim wrote:
> >
> > Hi
> >
> > This is to check regarding an issue we came across on postgreSQL
> > community version 11 with minor patch 11.20 along with few other changes
> > change details mention on end part of this mail
> >
> > The issue is notice after the minor patching from postgreSQL 11.16 to
> 11.20
> > The particular database runs on a dockerized platform
> >
> > Issue :
> >
> > One of the table query not woks when string values are filtered with =
> > clause
> > where as the query works with like clause using '%' flag  or using
> > trim(column)
> > below are query examples describes the problem
> >
> > not working
> >
> > select * from atlas_order.gdp_ship_logistic_food cgslf where
> > shipping_group_id = '2VxFRCOG9tc7SjQ2Ogg0bveve96';
> >
> > working
> >
> > select * from atlas_order.gdp_ship_logistic_food cgslf where
> > shipping_group_id like '%2VxFRCOG9tc7SjQ2Ogg0bveve96%';
> > select * from atlas_order.gdp_ship_logistic_food cgslf where
> > shipping_group_id like '2VxFR%COG9tc7SjQ2Ogg0bveve96';
> > select * from atlas_order.gdp_ship_logistic_food cgslf where
> > trim(shipping_group_id) = '2VxFR%COG9tc7SjQ2Ogg0bveve96';
> >
> > Later as per the feedback from dev we have dropped and recreated the
> > constraints and rebuild the indexes of the affected tables
> > and the data is accessibleafter on
>
> I would say the above is due to the distro change below.
>
> >  2. Change docker container base image from Debian to Alpine  (Debian
> > Bookworm - alpine3.18)
>
> My guess something along lines of:
>
> https://wiki.postgresql.org/wiki/Locale_data_changes
>
> Changing distro's mid stream increases the likely hood there will be
> issues.
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

-- 



*Regards,Hafeez Database Technical Lead*

Mindcurv TSPL
#6F, T-1, World Trade Centre,
Infopark CSEZ, P-1,
Kakkanad, Cochin - 682042

t: +91 (0)484 2100164
m: +91 9605445282
*www.mindcurv.com *

* CONFIDENTIALITY NOTICE: This e-mail and any attachments hereto are
intended only for use by the addressee(s) named herein and may contain
legally privileged and/or confidential information. If you are not the
intended recipient of this e-mail, you are hereby notified that any
dissemination, distribution or copying of this e-mail, and any attachments
thereto, is strictly prohibited. If you have received this e-mail in error,
please notify the sender by replying to this message and permanently delete
the original and any copy of this e-mail and any printout thereof.*

-- 
--
CONFIDENTIALITY NOTICE: This e-mail and any attachments hereto are 
intended only for use by the addressee(s) named herein and may contain 
legally privileged and/or confidential information. If you are not the 
intended recipient of this e-mail, you are hereby notified that any 
dissemination, distribution or copying of this e-mail, and any attachments 
thereto, is strictly prohibited. If you have received this e-mail in error, 
please notify the sender by replying to this message and permanently delete 
the original and any copy of this e-mail and any printout thereof.

A few questions about foreign tables

[Marcin Borkowski]

Hello,

I want to set up a few foreign tables (to access tables from one
PostgreSQL database in another one).  However, I want to do it
"properly", using a database migration tool, so I need to have a "down"
migration to revert to the previous state.

How to revert the effect of "import foreign schema"?  Is just dropping
the imported tables enough?

Also, how can I check the definitions (i.e., what tables on the server
they correspond to) of existing foreign tables?

(May I ask to be cc'd since I'm not subscribed to the list?)

TIA,

-- 
Marcin Borkowski
http://mbork.pl

postgres keeps having blocks

[Shaozhong SHI]

My postgres is playing up.

I terminated session that is causing blocks many time.

New block appears.

Endless.

What should I do?

Regards,

David

Re: Inheritance in PostgreSQL

[Luis Bruno]

> Thank you all for your responses. I appreciate the input on the use of
> table inheritance in PostgreSQL, and I will take your recommendations into
> consideration. The provided link is also quite useful, and I'm grateful for
> the solution provided by Ron regarding normalization. Thanks!
>

Re: postgres keeps having blocks

[David G. Johnston]

On Wednesday, October 18, 2023, Shaozhong SHI 
wrote:

> My postgres is playing up.
>
> I terminated session that is causing blocks many time.
>
> New block appears.
>
> Endless.
>
> What should I do?
>

PostgreSQL is probably just doing what you told it.  Not possible to give
advice without knowing what you told it to do. You should put more effort
into showing others what your app is doing and how you are debugging it and
the database.

Killing sessions without changing anything else is unlikely to result in a
change of behavior.

David J.

Re: Inheritance in PostgreSQL

[Thomas Kellerer]

Merlin Moncure schrieb am 18.10.2023 um 03:20:
> The only thing you can't really do in SQL easily without writing
> nasty triggers are things like, 'this table must be linked from one
> and only one of these candidate tables'.  I think the language
> probably ought to support this, but I don't think postgres would
> unless the standard did.

Isn't that what assertions are intended to solve in the SQL standard?

Re: postgres keeps having blocks

[Ron]

On 10/18/23 07:40, David G. Johnston wrote:

On Wednesday, October 18, 2023, Shaozhong SHI  wrote:

My postgres is playing up.

I terminated session that is causing blocks many time.

New block appears.

Endless.

What should I do?


PostgreSQL is probably just doing what you told it.  Not possible to give 
advice without knowing what you told it to do. You should put more effort 
into showing others what your app is doing and how you are debugging it 
and the database.


Killing sessions without changing anything else is unlikely to result in a 
change of behavior.


That's the definition of insanity...

--
Born in Arizona, moved to Babylonia.

Re: A few questions about foreign tables

[Laurenz Albe]

On Wed, 2023-10-18 at 10:24 +0200, Marcin Borkowski wrote:
> How to revert the effect of "import foreign schema"?  Is just dropping
> the imported tables enough?

Running DROP FOREIGN TABLE ... for all imported tables is enough.

> Also, how can I check the definitions (i.e., what tables on the server
> they correspond to) of existing foreign tables?

\d+ 

> (May I ask to be cc'd since I'm not subscribed to the list?)

But please reply to the list.

Yours,
Laurenz Albe

Re: A few questions about foreign tables

[Ron]

And SELECT relnamespace::regnamespace::text, relname FROM pg_class WHERE 
relkind='f'; tells you all of the foreign tables.


Thus, this (untested) query generate all of the DROP FOREIGN TABLE statements:
SELECT format('DROP FOREIGN TABLE IF EXISTS %I.%I RESTRICT;'
  , relnamespace::regnamespace::text, relname)
FROM pg_class WHERE relkind='f'
ORDER BY relkind;

On 10/18/23 10:56, Laurenz Albe wrote:

On Wed, 2023-10-18 at 10:24 +0200, Marcin Borkowski wrote:

How to revert the effect of "import foreign schema"?  Is just dropping
the imported tables enough?

Running DROP FOREIGN TABLE ... for all imported tables is enough.


Also, how can I check the definitions (i.e., what tables on the server
they correspond to) of existing foreign tables?

\d+ 


(May I ask to be cc'd since I'm not subscribed to the list?)

But please reply to the list.

Yours,
Laurenz Albe




--
Born in Arizona, moved to Babylonia.

Re: postgres keeps having blocks

[Adrian Klaver]

On 10/18/23 04:27, Shaozhong SHI wrote:

My postgres is playing up.

I terminated session that is causing blocks many time.

New block appears.

Endless.

What should I do?


Provide more information.

1) Postgres version.

2) Define what blocks means.

3) Show us the statements/code that is causing the issue.

4) Post relevant sections from the Postgres log.




Regards,

David


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

Re: postgres keeps having blocks

[Rob Sargent]

On 10/18/23 10:15, Adrian Klaver wrote:

On 10/18/23 04:27, Shaozhong SHI wrote:

My postgres is playing up.

I terminated session that is causing blocks many time.

New block appears.

Endless.

What should I do?


Provide more information.

1) Postgres version.

2) Define what blocks means.

3) Show us the statements/code that is causing the issue.

4) Post relevant sections from the Postgres log.




Commit your transactions?

Re: psql \du no more showing "member of" column

[Bruce Momjian]

On Fri, Oct 13, 2023 at 05:45:35PM +0200, Lele Gaifax wrote:
> Ian Lawrence Barwick  writes:
> 
> > and also noted in the release notes:
> >
> >   https://www.postgresql.org/docs/16/release-16.html#RELEASE-16-PSQL
> 
> FWIW, I noticed a small typo in the relevant entry: s/informaion/information/
> 
> ciao, lele.

Thanks, fix applied.

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

  Only you can decide what is important to you.

Re: Inheritance in PostgreSQL

[Merlin Moncure]

On Wed, Oct 18, 2023 at 7:43 AM Thomas Kellerer  wrote:

> Merlin Moncure schrieb am 18.10.2023 um 03:20:
> > The only thing you can't really do in SQL easily without writing
> > nasty triggers are things like, 'this table must be linked from one
> > and only one of these candidate tables'.  I think the language
> > probably ought to support this, but I don't think postgres would
> > unless the standard did.
>
> Isn't that what assertions are intended to solve in the SQL standard?
>

Hm, interesting,  ...maybe, maybe not.   SQL '92 -- wow.  guessing
why not:  Are they deferrable, always deferred? Doesn't seem like it,
simple inheritance cases would seem problematic otherwise, chicken/egg
conditions. This might explain why they are not in common use despite being
innovated 30 years ago.  Here is an Oracle discussion on the topic:

https://forums.oracle.com/ords/apexds/post/sql-assertions-declarative-multi-row-constraints-8418

...with the discussion implying that interaction with the transaction state
may be an unsolved problem, at least within oracle.  I suspect there may
also be challenges relating to performant implementation.  Sadly, I think
the correct approach remains to utilize complex triggers, or nothing.

merlin

How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type?

[毛毛]

Hi,


Do we have a rule by follow which one can accurately info the output of a 
SELECT statment FROM a table with floating-point data type?


I am working with PostgreSQL 16 and pgAdmin 7.6.


Here is my SQL script:


```

CREATE TABLE TEST (REAL_COLUMN real);

INSERT INTO TEST
VALUES (12345.123456789);

SELECT * FROM TEST;
```



I consulted the following doc and found that the range of real type is 6 
decimal digits precision.
https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-NUMERIC



So I thought the output of the SELECT statement should be like: 12345.1 with 6 
digits in total.
But it turns out to be 12345.123 with 8 digits in total.

May I know why would this happen?
Do we have a rule I can use to infer the correct output of a floating-point 
number without running the script?


Thank you for your time and have a great day!

Re: How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type?

[Tom Lane]

=?GBK?B?w6vDqw==?=  writes:
> Do we have a rule by follow which one can accurately info the output of a 
> SELECT statment FROM a table with floating-point data type?

The goal of our floating-point output functions is to produce
the shortest representation from which the stored value could
be recovered exactly.  It's not unusual for that to require
two or three decimal digits more than the nominal precision.
Keep in mind that the nominal precision, such as 6 digits for
float4, is a *lower bound* on the number of decimal digits
that will be stored accurately, whereas supporting exact
round-trip I/O requires an *upper bound* number of digits.

In short, no, there is not a trivial way to predict the
number of digits emitted.  If you have a problem with that,
maybe you should be using type numeric instead.

You can find more info by looking around for info about the
Ryū float output algorithm, eg here:

https://github.com/ulfjack/ryu

regards, tom lane

Re: How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type?

[David G. Johnston]

On Wednesday, October 18, 2023, 毛毛  wrote:
>
>
> I consulted the following doc and found that the range of real type is 6
> decimal digits precision.
> https://www.postgresql.org/docs/current/datatype-numeric.
> html#DATATYPE-NUMERIC
>

You also need to consult:

https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-FLOAT


But in short, no, the result is input specific and you the user are not
expected to know or care about such details.  The docs say you will get
between 6 and 9 digits depending on the value, under default settings.  The
table gives the minimum as clarified in the prose.  There is room in the
table to include more detail and it probably should.

David J.