PostgreSQL Patroni HAProxy ViP

High Availability and Automatic Failover in PostgreSQL on Kubernetes, On-premise and Cloud

https://www.youtube.com/watch?v=97rwCskemXo&list=PLWhC0zeznqknTPmcSrKmnV75Zr3QLfq7e&index=28

HAProxy can be configured to check via Patroni api who is the new master after a switchover : 
below picture is from above video at minute 26:23

Same ideea of a Load Balancer who uses health checks in order to find the master ==>> 

Design A Highly Available PostgreSQL Cluster With Patroni In GCP — Part 1 : 

https://blog.searce.com/design-a-highly-available-postgresql-cluster-with-patroni-in-gcp-part-1-dfb6ed277806

<<

Do we need HA Proxy:

To use Patroni, we need at least 3 nodes. In case of failover, we need some Virtual(or floating) IP address to make the application continue to access the database. To reduce this dependency, the HA proxy will always talk to the master node. Here is how HA proxy knows who is the master. In HA proxy, we need to give health check port. 8008 is the default Patroni rest API port. HA proxy will send a GET Request to all the nodes in the cluster in 8008 port. Only the Master node will give 200 OK status. Other nodes will return 503 Server Unavailable.

>>

Alternative to HAProxy is  ViP-Manager : 
https://github.com/cybertec-postgresql/vip-manager

UPGRADING POSTGRESQL TO A MAJOR VERSION

 

To do the upgrade , we have various options:

pg_dump & pg_restore 

https://www.enterprisedb.com/postgres-tutorials/how-use-pgdump-and-pgrestore-multi-host-enviorment

pg_upgrade : Copy data on a binary level

pg_upgrade is not destructive. 

If things go wrong, you can always delete the new data directory and start from scratch.

To upgrade a database, two steps are needed:

initdb -D /data/db_new

pg_upgrade -d /data/db_old/ -D /data/db_new -b /path/to/pg_old/bin/ -B /path/to/pg_new/bin/  

pg_upgrade –link : In-place upgrades

To reduce downtime, we can use the << -link >> option:

pg_upgrade -d /data/db_old/ -D /data/db_new -b /path/to/pg_old/bin/ -B /path/to/pg_new/bin/  --link

https://www.cybertec-postgresql.com/en/upgrading-and-updating-postgresql/

How to use table partitioning to scale PostgreSQL

https://www.enterprisedb.com/postgres-tutorials/how-use-table-partitioning-scale-postgresql

Benefits of partitioning 

• PostgreSQL declarative partitioning is highly flexible and provides good control to users. Users can create any level of partitioning based on need and can modify, use constraints, triggers, and indexes on each partition separately as well as on all partitions together.
• Query performance can be increased significantly compared to selecting from a single large table.
• Partition-wise-join and partition-wise-aggregate features increase complex query computation performance as well.
• Bulk loads and data deletion can be much faster, as based on user requirements these operations can be performed on individual partitions.
• Each partition can contain data based on its frequency of use and so can be stored on media that may be cheaper or slower for low-use data.

When to use partitioning 

Most benefits of partitioning can be enjoyed when a single table is not able to provide them. So we can say that if a lot of data is going to be written on a single table at some point, users need partitioning. Apart from data, there may be other factors users should consider, like update frequency of the data, use of data over a time period, how small a range data can be divided, etc. With good planning and taking all factors into consideration, table partitioning can give a great performance boost and scale your PostgreSQL to larger datasets.

How to use partitioning 

As of PostgreSQL12 release List, Range, Hash and combinations of these partition methods at different levels are supported.

PostgreSQL 9.1 + synchronous replication

PostgreSQL 9.1 synchronous replication

PostgreSQL 9.1 is now available in beta, introducing a number of new features. Among the new features is support for synchronous replication for the database.
"The database administrator can decide on a per-transaction basis which ones need to be replicated and which ones don't. The advantage of that is that there are a lot of mixed load applications which might have a small amount of essential data and a large amount of data that is not as important".