Regarding HA, Failover and Load Balancing

[Arif Hussain]

Hello,

Could anyone please help to implement High availability, replication and
failover for postgresql.

   -

   We are using postgreSQL 14 on VMs (planning to upgrade soon).
   -

   We have a single server and planning to achieve:
   i) High availability (1st priority)
   ii) Automatic Failover (1st priority)
   iii) Data Replication. (1st priority)
   iv) Fault detection (2nd priority)
   v) Monitoring and Alerts (2nd priority)
   vi) GUI (2nd priority)
   -

   As far as I know , postgres does support different replication options
   but it does NOT support automatic failover and load balancing.
   -

   As per my research, I found the below list of tools but all of the
   required features are NOT provided by any one tool. Every tool has some
   pros and cons.

*  pgpool-II:* it provides pooling and load balancing but it does
not provide automatic failover.

*Patroni:* it provides automatic failover but it is not good for
load balancing.

*ClusterControl:* Another candidate with free community addition
but failover is only in paid version. Not sure how efficient it is.

*   EnterpriseDB:* Another one fully paid. Exploring each and every
tool to understand its capabilities and limitations would be a very time
consuming task.

I want to know which tool or set of tools are being used mostly so that I
can narrow down my research to make an efficient decision quickly. Open
source tools are our preference but if some paid tools provide almost all
of the features, we would think of it.



Thanks,


Arif

Re: Regarding HA, Failover and Load Balancing

[Inzamam Shafiq]

Hi,

You can use combination of patroni, pgbouncer, and HAProxy. patroni will work 
for auto failover, pgbouncer is for connection pooling and HAProxy can be used 
for load balancing. These all tools are open source.

Regards,
Inzamam Shafiq

From: Arif Hussain 
Sent: Friday, November 3, 2023 2:04 PM
To: pgsql-general@lists.postgresql.org 
Subject: Regarding HA, Failover and Load Balancing

Hello,


Could anyone please help to implement High availability, replication and 
failover for postgresql.

  *   We are using postgreSQL 14 on VMs (planning to upgrade soon).

  *   We have a single server and planning to achieve:
i) High availability (1st priority)
ii) Automatic Failover (1st priority)
iii) Data Replication. (1st priority)
iv) Fault detection (2nd priority)
v) Monitoring and Alerts (2nd priority)
vi) GUI (2nd priority)

  *   As far as I know , postgres does support different replication options 
but it does NOT support automatic failover and load balancing.

  *   As per my research, I found the below list of tools but all of the 
required features are NOT provided by any one tool. Every tool has some pros 
and cons.

  pgpool-II: it provides pooling and load balancing but it does not 
provide automatic failover.

Patroni: it provides automatic failover but it is not good for load 
balancing.

ClusterControl: Another candidate with free community addition but 
failover is only in paid version. Not sure how efficient it is.

   EnterpriseDB: Another one fully paid. Exploring each and every tool to 
understand its capabilities and limitations would be a very time consuming task.

I want to know which tool or set of tools are being used mostly so that I can 
narrow down my research to make an efficient decision quickly. Open source 
tools are our preference but if some paid tools provide almost all of the 
features, we would think of it.



Thanks,


Arif

Re: pg_dump/pg_restore --jobs practical limit?

[Marc Millas]

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com


Testing pg_restore with different --jobs= values will be easier.   pg_dump
> is what's going to be reading from a constantly varying system.
>
> Hello,
>
each time I do a replatforming of this kind, with DB up to 2 TB, I did
create the target DB, eventually needed users then the appropriate
databases, and finally,  a simple script to pipe pg_dump into psql,
databases one by one.
So.. one thread. Each time, it was limited by the network bandwidth. My
last replatforming with a 10 Gb net and a 1.5 TB DB did show a transfer of
500 Mbytes per second (5Gbs) so.. less than an hour.
which is just fine. Launch it, have lunch, a coffee, and ...done for test.
For Prod, I am used to do it at the quietest night of the week end. and
have a nap ( a short one !)...:-)

Local postgres manual

[Ben Hancock]

Hi all:

Does Postgres come with a local, full version of the manual installed
by default anywhere (i.e. akin to what is available on the website, but
in man, info, or plain-text format)? When I invoke `man postgres`, I do
get a very useful - but limited - manual page, which has references to 
chapters. For example:


OPTIONS
   postgres accepts the following command-line arguments. For a
   detailed discussion of the options consult Chapter 20. ...

Of course, I can pull up a browser and find the manual, or consult a
local PDF if I have a graphical display. But sometimes it may be 
convenient to view the the manual for the version of Postgres that is on 
the system, right there. Does one exist?


--
Ben Hancock

Re: pg_dump/pg_restore --jobs practical limit?

[Ron]

On 11/3/23 05:09, Marc Millas wrote:


Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com 



Testing pg_restore with different --jobs= values will be easier.  
pg_dump is what's going to be reading from a constantly varying system.

Hello,

each time I do a replatforming of this kind, with DB up to 2 TB, I did 
create the target DB, eventually needed users then the appropriate 
databases, and finally,  a simple script to pipe pg_dump into psql, 
databases one by one.
So.. one thread. Each time, it was limited by the network bandwidth. My 
last replatforming with a 10 Gb net and a 1.5 TB DB did show a transfer of 
500 Mbytes per second (5Gbs) so.. less than an hour.


I'm shocked that old-school "SQL" single-threading works that fast.

Thanks for the data point.

which is just fine. Launch it, have lunch, a coffee, and ...done for 
test.  For Prod, I am used to do it at the quietest night of the week end. 
and have a nap ( a short one !)...:-)


--
Born in Arizona, moved to Babylonia.

Re: Local postgres manual

[Bruce Momjian]

On Fri, Nov  3, 2023 at 06:18:19AM -0700, Ben Hancock wrote:
> Hi all:
> 
> Does Postgres come with a local, full version of the manual installed
> by default anywhere (i.e. akin to what is available on the website, but
> in man, info, or plain-text format)? When I invoke `man postgres`, I do
> get a very useful - but limited - manual page, which has references to
> chapters. For example:
> 
> OPTIONS
>postgres accepts the following command-line arguments. For a
>detailed discussion of the options consult Chapter 20. ...
> 
> Of course, I can pull up a browser and find the manual, or consult a
> local PDF if I have a graphical display. But sometimes it may be convenient
> to view the the manual for the version of Postgres that is on the system,
> right there. Does one exist?

Well, that's a good question, and a new question for me.  I poked though
the doc makefile and it turns out that:

make postgres.txt

creates such a file.  Here is the PG 16 version:

https://momjian.us/expire/postgres-16.txt

Let me know if you want a different Postgres version.

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

  Only you can decide what is important to you.

Re: Local postgres manual

[Philip Semanchuk]

> On Nov 3, 2023, at 9:18 AM, Ben Hancock  wrote:
> 
> Hi all:
> 
> Does Postgres come with a local, full version of the manual installed
> by default anywhere (i.e. akin to what is available on the website, but
> in man, info, or plain-text format)? When I invoke `man postgres`, I do
> get a very useful - but limited - manual page, which has references to 
> chapters. For example:
> 
> OPTIONS
>   postgres accepts the following command-line arguments. For a
>   detailed discussion of the options consult Chapter 20. ...
> 
> Of course, I can pull up a browser and find the manual, or consult a
> local PDF if I have a graphical display. But sometimes it may be convenient 
> to view the the manual for the version of Postgres that is on the system, 
> right there. Does one exist?

In addition to Bruce Momjian’s suggestion, I’ll add that you can make an HTML 
version of the manual from the source code. This is what I use and it works 
great for me.

Cheers
Philip

Re: Local postgres manual

[Bruce Momjian]

On Fri, Nov  3, 2023 at 09:39:46AM -0400, Philip Semanchuk wrote:
> 
> 
> > On Nov 3, 2023, at 9:18 AM, Ben Hancock  wrote:
> > 
> > Hi all:
> > 
> > Does Postgres come with a local, full version of the manual installed
> > by default anywhere (i.e. akin to what is available on the website, but
> > in man, info, or plain-text format)? When I invoke `man postgres`, I do
> > get a very useful - but limited - manual page, which has references to 
> > chapters. For example:
> > 
> > OPTIONS
> >   postgres accepts the following command-line arguments. For a
> >   detailed discussion of the options consult Chapter 20. ...
> > 
> > Of course, I can pull up a browser and find the manual, or consult a
> > local PDF if I have a graphical display. But sometimes it may be convenient 
> > to view the the manual for the version of Postgres that is on the system, 
> > right there. Does one exist?
> 
> In addition to Bruce Momjian’s suggestion, I’ll add that you can make an HTML 
> version of the manual from the source code. This is what I use and it works 
> great for me.

Yes, we can do HTML too as a single file, postgres.html.  What I don't
see is man page output for anything but the references pages, and as
separate files.  It might be possible to convert the HTML to man format
using something like html2man.

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

  Only you can decide what is important to you.

Re: Local postgres manual

[jian he]

i think Ben Hancock
want something like https://en.cppreference.com/w/Cppreference:Archives

Re: Local postgres manual

[Philip Semanchuk]

> On Nov 3, 2023, at 9:45 AM, Bruce Momjian  wrote:
> 
> On Fri, Nov  3, 2023 at 09:39:46AM -0400, Philip Semanchuk wrote:
>> 
>> In addition to Bruce Momjian’s suggestion, I’ll add that you can make an 
>> HTML version of the manual from the source code. This is what I use and it 
>> works great for me.
> 
> Yes, we can do HTML too as a single file, postgres.html.  What I don't
> see is man page output for anything but the references pages, and as
> separate files.  It might be possible to convert the HTML to man format
> using something like html2man.

Yes, ISTR there’s an all-in-one-page option, but the HTML that I built is in 
individual pages that mirror the organization on postgresql.org which works 
great for me. Sorry I don’t remember the exact command I used but it was very 
straightforward, nothing tricky at all. Maybe as simple as `make install-docs`?


Cheers
Philip

How to tell which statement is being executed

[Garfield Lewis]

Hi All,

If I create a C function, is there a way from within that function for me to:


  1.  know whether it is being triggered by an INSERT or UPDATE statement
 *   I’ve done some digging and it seems you can get this information if 
you provide a Planner hook, however, I need to know this much later in my 
function not in the Planner
 *   Is there some way for me to get to the PlannedStmt (or anything else 
that may have the command type) from within my function?
  2.  column attribute (specifically the TYPMOD) for the affected column

BTW, is there a more appropriate Postgres list/group/blog that is specifically 
dedicated to these types of programming questions or is it ok to post this here?

Regards,
Garfield

Re: How to tell which statement is being executed

[Tom Lane]

Garfield Lewis  writes:
> If I create a C function, is there a way from within that function for me to:
>   1.  know whether it is being triggered by an INSERT or UPDATE statement

It might not be any of those.  But if what you want is the most closely
nested SQL action, inspecting the ActivePortal might help (see
function_parse_error_transpose, which I think is the only in-core user).

>   2.  column attribute (specifically the TYPMOD) for the affected column

No.  How would you even define "affected column"?

regards, tom lane

Finding execution plan

[shashidhar Reddy]

Hello All,

I have a Postgresql function scheduled to run on the database, some tines
it is taking too long than usual time, is it possible to get the execution
plan of this function or any other way to troubleshoot

Re: Finding execution plan

[Adrian Klaver]

On 11/3/23 10:40 AM, shashidhar Reddy wrote:

Hello All,

I have a Postgresql function scheduled to run on the database, some 
tines it is taking too long than usual time, is it possible to get the 
execution plan of this function or any other way to troubleshoot



You might to take a look at the contrib module:


https://www.postgresql.org/docs/current/pgstatstatements.html


"The |pg_stat_statements| module provides a means for tracking planning 
and execution statistics of all SQL statements executed by a server."

Re: Finding execution plan

[Adrian Klaver]

On 11/3/23 10:44 AM, Adrian Klaver wrote:



On 11/3/23 10:40 AM, shashidhar Reddy wrote:

Hello All,

I have a Postgresql function scheduled to run on the database, some 
tines it is taking too long than usual time, is it possible to get 
the execution plan of this function or any other way to troubleshoot



You might to take a look at the contrib module:


 ^want



https://www.postgresql.org/docs/current/pgstatstatements.html


"The |pg_stat_statements| module provides a means for tracking 
planning and execution statistics of all SQL statements executed by a 
server."

Re: Finding execution plan

[Bruce Momjian]

On Fri, Nov  3, 2023 at 10:44:12AM -0700, Adrian Klaver wrote:
> 
> On 11/3/23 10:40 AM, shashidhar Reddy wrote:
> 
> Hello All,
> 
> I have a Postgresql function scheduled to run on the database, some tines
> it is taking too long than usual time, is it possible to get the execution
> plan of this function or any other way to troubleshoot
> 
> 
> You might to take a look at the contrib module:
> 
> 
> https://www.postgresql.org/docs/current/pgstatstatements.html
> 
> 
> "The pg_stat_statements module provides a means for tracking planning and
> execution statistics of all SQL statements executed by a server."

Since he asked about execution plans, I was thinking auto_explain:

https://www.postgresql.org/docs/current/auto-explain.html

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

  Only you can decide what is important to you.

Re: Finding execution plan

[Adrian Klaver]

On 11/3/23 10:54 AM, Bruce Momjian wrote:

On Fri, Nov  3, 2023 at 10:44:12AM -0700, Adrian Klaver wrote:

On 11/3/23 10:40 AM, shashidhar Reddy wrote:

 Hello All,

 I have a Postgresql function scheduled to run on the database, some tines
 it is taking too long than usual time, is it possible to get the execution
 plan of this function or any other way to troubleshoot


You might to take a look at the contrib module:


https://www.postgresql.org/docs/current/pgstatstatements.html


"The pg_stat_statements module provides a means for tracking planning and
execution statistics of all SQL statements executed by a server."

Since he asked about execution plans, I was thinking auto_explain:

https://www.postgresql.org/docs/current/auto-explain.html



Yeah, that would be the better option.

Re: Finding execution plan

[shashidhar Reddy]

Thank you Adrian fir the reply, I am looking for something like complete
execution plan of the function just like EXPLAIN does.

On Fri, 3 Nov, 2023, 11:15 pm Adrian Klaver, 
wrote:

>
> On 11/3/23 10:44 AM, Adrian Klaver wrote:
>
>
> On 11/3/23 10:40 AM, shashidhar Reddy wrote:
>
> Hello All,
>
> I have a Postgresql function scheduled to run on the database, some tines
> it is taking too long than usual time, is it possible to get the execution
> plan of this function or any other way to troubleshoot
>
>
> You might to take a look at the contrib module:
>
>  ^want
>
>
> https://www.postgresql.org/docs/current/pgstatstatements.html
>
>
> "The pg_stat_statements module provides a means for tracking planning and
> execution statistics of all SQL statements executed by a server."
>
>

Re: [EXT] Re: How to tell which statement is being executed

[Garfield Lewis]

> It might not be any of those.  But if what you want is the most closely
> nested SQL action, inspecting the ActivePortal might help (see
> function_parse_error_transpose, which I think is the only in-core user).

Thx, Tom… will see if this ActivePortal helps…

> No.  How would you even define "affected column"?

Not sure I just assumed that the attributes for column being processed would be 
saved somewhere in some structure somewhere. After all, at some point if for 
instance I created a CHAR(10) column and attempted to insert a CHAR(15) that is 
returned by my function then Postgres will need to generate a truncation 
warning so it must know the TYPMOD of the column to do so.

Regards
Garfield

From: Tom Lane 
Date: Friday, November 3, 2023 at 12:08 PM
To: Garfield Lewis 
Cc: pgsql-generallists.postgresql.org 
Subject: [EXT] Re: How to tell which statement is being executed
Garfield Lewis  writes:
> If I create a C function, is there a way from within that function for me to:
>   1.  know whether it is being triggered by an INSERT or UPDATE statement

It might not be any of those.  But if what you want is the most closely
nested SQL action, inspecting the ActivePortal might help (see
function_parse_error_transpose, which I think is the only in-core user).

>   2.  column attribute (specifically the TYPMOD) for the affected column

No.  How would you even define "affected column"?

regards, tom lane

Re: Local postgres manual

[Ben Hancock]

On 11/3/23 06:33, Bruce Momjian wrote:
But sometimes it may be convenient to view the the manual for the 
version of Postgres that is on the system, right there. Does one 
exist?


Well, that's a good question, and a new question for me.  I poked 
though the doc makefile and it turns out that:


make postgres.txt

creates such a file.  Here is the PG 16 version:

https://momjian.us/expire/postgres-16.txt

Let me know if you want a different Postgres version.


Thanks very much Bruce - this is really helpful! I've never taken a look 
at that makefile but will do so.


I will say that, as the manual is quite large, a local GNU Info version 
would be great to be able to traverse different sections, etc, more 
easily. If that doesn't exist already, I wonder if a tool like `pandoc` 
could be employed to generate that.


As others on the thread have suggested, building or fetching the HTML 
locally may also work. In terminal environment, I suppose one could use 
`lynx` or similar to navigate around.


- Ben

Indexing fragments of a column's value ?

[David Gauthier]

I'm asking about the possibility of indexing portions of a column's value
where the column has a static field format.  Example, a char(8) which
contains all hex values (basically a hex number that's always 8 chars wide,
leading zeros if needed). Someone might want to select all recs where the
first 2 digits are 'ff' or maybe the last 4 hex digits match regexp_match
'00[cdef]{2}' or maybe a match of the entire string... "0dd63a87".

If I know the placement and width of the fields that need to be indexed,
can indices be defined to facilitate queries ?  Example...
- match all 8 chars
- match the 3rd and 4th chars
- match the last 4 chars

I suppose I could fragment the thing into multiple columns for the purposes
of a search.  So add a column called "last4" as a char(4) that matches the
last 4 chars of that column.  Then index that.  etc... But inquiring to see
if there is something more elegant.

Thanks !

Re: Local postgres manual

[Tom Lane]

Ben Hancock  writes:
> I will say that, as the manual is quite large, a local GNU Info version 
> would be great to be able to traverse different sections, etc, more 
> easily. If that doesn't exist already, I wonder if a tool like `pandoc` 
> could be employed to generate that.

That's exactly what the postgres.txt file Bruce mentioned is.

# single-page text
postgres.txt: postgres.html
$(PANDOC) -t plain -o $@ $<

These variant doc forms aren't terribly well (or at all) documented,
but the doc/src/sgml Makefile knows how to create a number of them.

regards, tom lane

Re: Indexing fragments of a column's value ?

[Tom Lane]

David Gauthier  writes:
> I'm asking about the possibility of indexing portions of a column's value
> where the column has a static field format.

GIN indexes are meant for exactly that.  You might have to write your
own opclass to break up the input values in the way you want though.

A less difficult answer would be to write a function that breaks up
the input into (say) an array of text and then use the existing
GIN array support.  But you'd pay for that by needing to write more
complicated queries to use the index.

regards, tom lane

Re: Indexing fragments of a column's value ?

[Thomas Boussekey]

Le ven. 3 nov. 2023 à 21:01, Tom Lane  a écrit :

> David Gauthier  writes:
> > I'm asking about the possibility of indexing portions of a column's value
> > where the column has a static field format.
>
> GIN indexes are meant for exactly that.  You might have to write your
> own opclass to break up the input values in the way you want though.
>
> A less difficult answer would be to write a function that breaks up
> the input into (say) an array of text and then use the existing
> GIN array support.  But you'd pay for that by needing to write more
> complicated queries to use the index.
>
> regards, tom lane
>
>
> Hello David,

Reading your mail, it seems that your data column contains 3 different
kinds of atomic information:

* Characters 1-2
* Characters 3-4
* Characters 5-8

Does it make sense to split this data into 3 separate columns?
Each one could be indexed, and you can rebuild the original thanks to a
generated column:
https://www.postgresql.org/docs/current/ddl-generated-columns.html

HTH, Thomas

Re: Local postgres manual

[Bruce Momjian]

On Fri, Nov  3, 2023 at 03:58:05PM -0400, Tom Lane wrote:
> Ben Hancock  writes:
> > I will say that, as the manual is quite large, a local GNU Info version 
> > would be great to be able to traverse different sections, etc, more 
> > easily. If that doesn't exist already, I wonder if a tool like `pandoc` 
> > could be employed to generate that.
> 
> That's exactly what the postgres.txt file Bruce mentioned is.
> 
> # single-page text
> postgres.txt: postgres.html
>   $(PANDOC) -t plain -o $@ $<
> 
> These variant doc forms aren't terribly well (or at all) documented,
> but the doc/src/sgml Makefile knows how to create a number of them.

I tried:

pandoc -t texinfo -o postgresql.info postgres.html

but couldn't figure out how to access it via 'info'.  I was able to get
the man page looking good with:

make postgres.html &&
pandoc -t man -o postgresql.1 postgres.html &&
sed -n '1,${p;/^\.SH "NAME"/q}' /u/postgres/man/man1/postgres.1 |
sed 's/POSTGRES/POSTGRESQL/' > 
/u/postgres/man/man1/postgresql.1 &&
cat postgresql.1 >> /u/postgres/man/man1/postgresql.1 &&
rm postgresql.1

I pulled the formatting from postgres.1;  without that the formatting
was 80-column and poor.  I think it doesn't define .TH and that makes it
look bad.

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

  Only you can decide what is important to you.