Borys Neselovskyi: PostgreSQL in Kubernetes: Operation - Day 2

In this talk, Boris Nessosi discusses the implementation of PostgreSQL operators in Kubernetes, focusing on the cloud-native features of their PostgreSQL operator developed in Go. He highlights the limitations of traditional stateful sets in Kubernetes and illustrates how their operator allows for easier management of PostgreSQL clusters through YAML configurations. The operator supports highly available setups with automated failover and the ability to scale resources on-the-fly. Boris emphasizes the benefits of using their operator, such as backup and recovery processes, rolling upgrades, and integration with Prometheus for monitoring. He also shares insights on security features and storage management for optimal database performance in a Kubernetes environment.

[Music] ladies and Gentlemen please welcome our next speaker Boris nessosi presenting the topic host gray SQL in kubernetes operation day two this really fine uh please raise a hand if you have experiences with postgress dat database nice nice please raise your kubernetes open shift okay we are the right team on the right place in the right time let's go okay my name is Boris nelski I living

in Germany I I was born in Ukraine in very nice city Essa and uh the last almost 30 years I'm working in it with databases I started Oracle uh with Oracle database Oracle middleware I'm really uh love to to to work in the communities and I member from Germany Oracle User Group middleway Community I am leading the community I was Oracle Ace but four years ago I

yeah I changed my career I am choosed the way to open source to postgress dat database I joined EDB and uh I'm helping my customer to adapt the open source strategy and especially in the postgress area and postgress yeah postgress is most popular database in the world yeah by developers uh and uh four years ago in meetings with my customer I I was us it to explain

what is postgress why is postgress good uh why the user should use postgress not Oracle but now all companies have postgress uh strategy and this is really really very very interesting uh development yeah post Cris is the database of the year and uh I'm working for EDB Enterprise DB and we are really very important part of the community yeah almost or more as 30 30% of cotee

and postgress is written by my colleagues and uh from uh seven person in core member uh uh uh coure Team Member free uh from us therefore we can we know postgress we know cuberes and we are really able to build something very nice uh what I will speak about okay post uh operator let's start uh honestly uh we started to build operator late because it was crunchy

it was zando they build it operator four years ago and uh all customer used it loved it but we did it in the different way we did it really cloud native we did it we developed it in go language it means that uh we can interact our operator can can inter interact directly to the kubernetes API we don't need thirdparty tools for high availability for example Petron

we are not using stateful sets you know stateful sets in kubernetes for stateful operation is default uh technology yeah we don't use it because this is not flexible enough yeah we can cannot resize our storage on the Fly and we uh adopted different uh several several techniques which is really very new and uh for example our operator is using by Google gcp yeah Google cloud provider and

they are using our operator and we are working together we have a cloud native PG is open source yeah you can just go to GitHub you can download it you can test it you can use it it's everything fine we developed it and the uh yeah the we made it available for the community as open source but we have uh Fork of the community we have our

property operator based on the cloud native PG if you need some uh Advanced Security Options For example or if you need uh transparent data encryption encryption on the database level open shift compatibility and Z on you can choose our operator but Cloud native PG have the almost the same uh features at our operator and in the last year it was really uh yeah according to time scale

our operator was popularest operator in the world yeah we have 25 Millions downloads uh 3,000 stars and with stars yeah you can see here we are red uh we uh have now almost the same uh number of stars as crunchy and zanda but in very short time and let's speak about what is the difference why do you need to to to use operator why do you need

to use uh kubernetes in the normal in the classic world yeah to create one uh postgress cluster with three not with one master two standbys I need to execute several steps I need to create VMS I need to install pogress I need to configure streaming replication I need to set up backup to test my recovery to create uh configure monitoring security and Zone you can of course

automate this tasks but it's everything installation steps and you will have some problems you need to in uh investigate some troubleshooting and so on you need to maintain your code and adapt to new requirements to new versions to new changing uh settings and on in the declarative way what we have in our operator to create free nodes cluster you need just this yaml file you will not

install it you will just start uh uh deployment and operator will download the image immutable image with postgress and in two or three minutes and you will see it you will have your cluster with security with monitoring with everything backup recovery everything is there and this is really the um unique selling point and added value for you you can make all operations backup recovery monitoring High availability

uh fail over switch over and z just with yaml syntax you don't need to make to execute to know each tool to execute any command it's everything in in in kubernetes automated you and as I already said our operator have SE several features features uh for example we are we rely on kubernetes uh uh selfhealing uh capabilities and uh we build it fail over just to operate

uh honest EDB created for postgress uh streaming replication and logical replication we know how it works and we know what we need to implement to the operator to make fail over and we implemented it to operator I will show you how it works on the uh example you can scale your uh memory and CPUs on the Fly for postgress instan instances and you can scale as well

your re replicas yeah your read workloads you can scale you can start more replicas stop you can make rolling upgrades for example minor upgrades of post from from version for example 153 to 154 you can just change yaml Sy version of postest and it will be running in Rolling uh Manner and make you upgrade you can make backup and uh uh recovery and as well point in

time recovery just with yaml syntax we have uh promeets exporter we have graph dashboards uh we can create J distributed kubernetes clusters and uh make it uh one primary cluster one standby cluster and replicate data and make Jo redundancy and more uh installation of operator is just uh uh yeah you can use Helm chart or you can uh install manifest it's just deployment it's not the big

deal here and to create the cluster with three notes uh one is Master two standby with uh TS authentification for replica it means the replication will be encrypted uh you need just to have one yaml file and it could be yaml file with syntax uh you need to provide your name cluster uh you need to provide some in informations for example uh if you want to have

a synchron synchronous replication with you uh and you uh will not lost the data you can uh set mean Max sync replicas you can set different storage classes for different datab database areas yeah for uh PG data for wall uh storage and for table spaces if you decide to create some table spaces you can set several uh database parameters you can uh as well set pba.com here

you can uh create lb or ad authentification yeah everything is possible and only you need to to to create your yl files and then you can uh yeah just run Cube C and your cluster will be created what uh will be created uh at first it will be created the storage uh units for the note one it will be started postest uh this will be the master

or primary and the r Rite service will be created R Rite service Point always on the primary if my primary is changed the service will re will be reconfigured and show on the new uh primary it means what you need to provide to your clients to access the read uh only uh the IP address or uh server name of the service yeah and will it it it

it will it will be not changed uh then it will be started uh the first replica uh replication will be configured everything automatically uh re only service will be started if you will to provide for your clients re only access yeah for selects for some reports they don't uh need to have access to the master database yeah because you can just isolate work uh R write workloads

from R only and uh give provide the uh connect string to the replica third replica will be created replication will be uh configured and everything is good if your primary fails what's happened operator make fail over completely automatically because they received the message from peratus your replica is not available the standby database will become the role of primary uh operator will start replication for the new primary

to the existing uh uh standby and create from old primary the new replica and start replication and this is what I I would say is Big advantage of kubernetes yeah because you know if you have a classic environment with uh VMS and you have such situations yeah and need third part party tool to uh be sure what your primary if it's fails uh you can start the

uh uh replica you you you you need to to monitor everything yeah here the kubernetes and operator will do it for and of course in this uh in this uh architecture we have uh mostly yeah if thinking uh in in in the cloud vendor wording yeah we have the region with free availability zones with free Orion is City availability zone is uh data center but uh if

we have some problem with our city with our region yeah you can see now in the Ukraine yeah the whole city is uh uh going uh down and uh yeah we need some time Geo redundancy and uh to create the Geo redundancy yeah we can create several clusters two clusters for example and make replica cluster for example I have in in in vus my first cluster kubernetes

cluster with my uh primary to standby I have backup to S3 storage and everything is good I can create from this backup the cluster in City incounters for example yeah I can create it and I can synchronize my cluster into two I can uh replicate the W files from my primary cluster to my replica cluster or I can replicate the Vol files from the backups and then

I can create higher availability encounters for example I can create second and third uh standbys and if my uh primary CL cluster fails I need just uh to change one parameter on the replica cluster to start it as primary and then I can start replication in different way yeah in this way we have in this architecture we have uh two kubernetes cluster two postgress cluster that will

be synchronized everything is in yaml syntax implemented and you don't need to to create to to uh yeah to tip some commments if you need some Advanced security uh options yeah for example extended auditing clock if you need in in in banking sector you need uh to mask some information for example credit card number and uh show only four uh last digits uh encryption yeah we have

it in our property version of of operator and you can use it if you have uh more security uh requirements okay let's talk about main storage uh from my experience we have several customer who said uh okay we have already kubernetes yeah we have applications in kubernetes web server it's running very very nice but we treat to uh to to run databases in cuberes and it it

performance was mess and uh and the first uh think is uh in kubernetes is storage yeah which storage can you attach to your kubernetes because this is very essential question yeah and we are providing for you really the flexibility you can use local storage what is from my uh opinion the best way if you uh attach the locer storage to the workan not and then you have

Mission critical database and you have only one pot on the workan note you can achieve the same storage performance as by Bare Metal or uh virtual machines yeah because you are not sharing storage you are not fighting for several uh from several database several applications for one storage but you can use uh network storage and uh we we support persistent volume claims and storage classes it means

that you can attach your storage you can uh provide it to kubernetes uh as storage classes and you can have different storage classes for different database areas for example SSD for w files yeah or for p data you can choose different uh storage classes uh and uh be flexible it's uh uh very important point I showed you free workan notes free uh postgress instances here uh we

are trying to check how many workan notes you have yeah how many workan notes are available for postgress and we are placing uh postgress instances on different worker notes you can uh with put Affinity anti-affinity rules not selectors you can decide which work or not are available for postgress not uh you can create your databases on different ways with uh any DB you will create the new

cluster yeah completely new cluster in kubernetes but you can as well import your cluster from existing postgress instances you can use uh PG dump and PG restore you don't need to to to try pg dump it will be just a uh section in bootstrap and you can just uh migrate your database from non kubernetes for example into kubernetes and we can create the database from existing backup

uh we can uh try backups about backup I will be uh uh talk a little bit uh later and you can uh restore your database from backup make full restore or point in time recovery and if you know if you need to to to read more my colleague wrote very very good uh blog post about this rolling upgrades it's very important Point uh you need some time

upgrade your uh uh environment and we are supporting uh rolling upgrades the first the standby will be upgraded and then uh the master uh minor release upgrade can be run completely automatically I will show you I will upgrade my uh cluster and uh uh it's upgrade is just replacing of the image yeah we will replace old image with the New Image and on this way you can

uh make uh for example uh and the the the the ports will be restarted yeah you can make security uh fixes if you need to to uh to update your kubernetes platform and if you have uh the the long running upgrades you can uh use uh uh supervised or unsupervised uh uh features with supervised you will decide the time uh where the note can be uh go

on for upgrade beup and Recovery as uh uh the different operator you can make backup and backup to S free storage yeah we are supporting all S3 compatible storage and you can write the VA files uh to S3 storage and make the full backups uh restore you can do it uh uh full and point and time recovery but we uh have some problems with with S3 s3e

storage the first problem is for our customers they have 10 tbte database and the database kubernetes cluster was uh uh uh done and uh lost and they have to uh restore the database 10 terab database from the s3e storage it took uh two days time and the customer breached uh the slas for Recovery uh time object objective and we uh created now we we are supporting the

kubernetes volume snapshots yeah you you can just write in in yaml syntax make me back up of my database with kubernetes volum snapshots and we have some advantages yeah for example we can now make with SNAP shots incremental and differential backups we can uh we can move these snapshots across different cluster and create uh other cluster from this snapshot and this is very very good fits for

very large databases by restore uh here you can see some numbers and if you see the large uh database we started with 4 tbte database and we run and run uh the PG bench and uh yeah we have uh the databases grow to 8 terabyte and we made the backup with snapshot the first backup was done in almost 4 hours second backup in two hours and recovery

time recovery of this database uh took 2 minutes yeah and this is really big difference to uh classic backups yeah and uh this is the feature with our customer need really and now uh use it for very large databases we have native uh uh prils exporter with several metrics uh but I will show you uh this in my demo uh we have grafana dashboard you can just

download from our documentation Json grafana Json file and start it and and use it as a template for example yeah and Implement your own uh logging is very important thing because uh uh yeah all informations of from post press yeah for example uh database lock informations but as well uh auditing clock will be forwarded to standard output and you can collect with your tools the lock informations

from the standard output cnpg plugin it's very very good tool we created extension for cube C or OC if you are using open shift to make uh some things for example to uh maintain your uh TLS certificates to uh start postgress uh stop postgress to start Port stop port to to uh make PG bench uh for example to test my performance and several other things uh yeah

you can start as well PG admin with uh this uh plug-in and we have uh if you have databases with uh oltp for example with uh big amount big uh number of customer yeah and customer will access database log into database make something then and uh uh yeah close the session you need some connection pooling manager and postgress uh is PG bouncer is very well uh connection

pool manager and we created operator for PG bouncer you can just with one yaml file create some instances of PG Bouncer and then uh manage your connection pulling this okay and now I think uh I will show you the demo I have my uh kubernetes clust on my laptop yeah I know it's not oh this one I I can I can maybe I don't know why but

oh this okay can you see or okay okay I have my kubernetes cluster running on my laptop is cadra k3s uh and uh yeah and now I will in the first step create uh install the uh plugin what I talk about this uh installing of plugin is just uh to execute the script from cloud native PG and uh we will be able to to use cube c2l

with extended syntax and uh your plugin will be now uh installed okay the second step is installing of operator as I already said operator is just deployment in in uh kubernetes and you can install operator with Helm charts or with installing of manifest it's really straightforward and now now we are really able to create our first cluster I will check if the operator installation is ready is

not at moment but we can create our first yaml file everything what you need to use the operator is Cube C or OC and yaml syntax uh it to okay I will create now uh the yaml file and as you can see see uh I have the cluster name cluster example I have three instances one master and two standbys I will create it uh with postgress version

153 uh and I will uh just uh uh enable the promets monitoring okay I will check again if the operator is now ready and now just uh yeah we can create our the first our uh our first cluster what has happened let's see the operator will provision uh the storage and then we'll download the image will pull the image and here I will start uh plug-in Cube

C uh cnpg status my see what has happened yeah I'm pulling image from internet from my uh mobile phone my mobile phone that I think we need some seconds uh 10 20 seconds to to to pull the image after pulling the image uh they will start the first instance uh Master instance and uh create the service point my master to Red write Service uh in the second

step we be create the first standby then third standby and everything uh will be good here but now we are pulling image maybe I can ah okay now it's image already there uh the first note uh uh cluster will from cluster will be started started post and here we can see uh what we have the clust example one port uh it's a primary database and uh we

will uh now the operator create the second Port standby you can see we have standby in async mode because I didn't uh create sync uh replication and uh yeah the storage uh will be created for the thir not and this uh uh note will be started and now we have our cluster free notes cluster in two or three minutes with everything what we created already uh we

have free note cluster uh we have Services read right and read only what I already spoke about this we have uh secrets uh we have TLS certificates for replication our replication is encrypted we have uh TS certificate for the client access and we have uh secret for super user access and up access you can create your own user you can import your own certificates everything you can

customize but we will create you for you this all things and the clust is there okay let me show how can we monitor the cluster I will just uh create the promet rules uh this this syntax is everything is described in the documentation yeah we have a promets rules and now I need to just to start port forwarding and show you how it works I'm going to

promet and I just searching for cnpg and you can see here about I think 200 uh metrics with uh alerts and we are monitoring the whole things postgress uh streaming replication High availability everything is there if you need to create your own metrix we described it in the documentation how can you do it and we have here promet EXP Port already configured for you uh now I

will create uh you uh grafana dashboard grafana dashport we have in the documentation uh you can uh download oops something is wrong and I can just go to dashboards and I import uh my uh Json here I need to check the promets data default and I have my dashboard yeah uh we have several sections and some data we need to to have time to to fill out

everything but you can see the cluster level uh version information of Port CPU utilization memory utilization uh uh lack replication and so on you can see uh server Health you can see configuration for example all parameters and you can see CPU usage session State and sever other things storage write a heat lock is very very important if you have some problems performance problems I would like would

always at first see to write a hit lock replication status backup and Z on we have this graph on dashboard it can be good for you maybe you need to adjust something or use uh different but we have promos exporter and graph yeah and everything is uh there but now uh uh I would like to show you some interesting things uh I will create one table uh

I need to go back and create a table test with 1,000 uh uh rows I will check it and I have uh my table test and with 1,000 TRS and the now I will show how can I change the roles I would uh say my clust example two to primary and clust example one to standby uh make switch over uh to make switch over we have the

uh with plug-in Cube C ctlc npg promote cluster example to Cluster example two if I try this command you can see that clust example two will be primary and then uh clust example one will be starting as uh standby and operator configure replication from the new primary to uh standby everything is good yeah and you can do it for example if you need to to upgrade your

uh some uh firmware kubernetes version or something else but now I will uh simulate the failover situation I will start now now uh I will just delete my uh Master Po clust example two is primary and I will just delete it and let's check what has happened kubernetes detects what the poort is not available operator make uh clust example one is primary make fail over and operator

is starting uh failed Master as as the new standby and configured replication uh from the new Master you can see it's happen automatically yeah you don't need to make something if your Port is down operator will take care about this situation I don't know why but sometime they need some time to to to create replication and I can maybe help him to create if I can switch

Vol file see um Cube CTL plugin you can see that the plug-in you can do several uh operations you can make back up your database you can uh maintain your certificates yeah you can destroy class fencing uh flexible IO you can test your uh performance of your storage for hibernate uh you can run PG admin pgbench psql and several other things you can do with plug-in is

just extension to cube C uh and uh yeah it's very good thing uh to do it good okay uh now I would like to show you c c uh status command in the U and the full output to show what is uh the status of our cluster we can see that the cluster is in the health State now uh verion is 153 uh we have uh expiration

date for our certificates yeah we don't have now continuous backup and physical backups and uh all other informations you you can see now I created Five Minutes ah nice nice uh I created now uh the different uh yaml file called uh cluster upgrade and uh and as you can see I will uh I I will do a minor upgrade of POS I will change from 153 to

154 and I will add the backup section I will provide uh S3 uh access uh storage access uh I have my S3 storage here and I don't have any any buckets at the moment and now I will write uh I will run the upgrade as you can see the operator make running upgrade take the first standby and replace the image yeah now they uh make pulling of

the 154 image and if the image is pulled they will replace image on the first standby then they will take the second standby and at the last but not least the master you can choose if your master if you will uh have switch over if you can switch over and then uh upgrade in this default it will be Master just restarted yeah with the new image and

now we can see the example three is now pitched and the second uh uh note will be started with the new image and the last will be our primary replaced image will be replaced okay now we have our clust up upgraded and uh if we are looking now uh for status of the cluster we can see what we have now 154 I changed just the parameter the

instance uh and we have now backup we have backup of w files it means that our uh Min IO storage have now directory CNP CNP and clust ex example and the wall backups will be adjust archived to S free storage uh full backup you can do with syntax uh you need to create yaml file uh with uh kind backup you need to take uh name backup test

which cluster and you can make for example scheduling as well I will I will run a one time backup but you can schedule the backups um and uh backup is completed now and in the S3 storage we can see in cluster example the second base directory with our backup and to restore my backup is always the outof the place restore I will create the new cluster new

yaml file uh cluster restore with one instance and uh it will create the new cluster with the full restore you can make full uh point in time recovery you can provide the line with a time or uh sequence number and make it uh not uh uh with not all Wall Files applying and um yeah now uh my port will be initialized and uh at the end I

will have my restore cluster and you can remember I have the table test with and I I will che check in my cluster restore now if my table is available and you can see what I restored my cluster two minutes okay I am uh almost uh just I would like to show you how easy is to uh to to maintain to run post and kubernetes please use

our operator you have have some questions I am available for discuss discussions in V cdb are available for thank you very much thank you Boris so I don't think we have too much time for all the questions so please do remember to ask questions on slido during the talk or also if anybody in the audience has something to ask right now please raise your hand yes uh

can we have active active cluster pogress yes yes you can uh we by EDB have postgress distributed is multimaster uh based on logical replication with conflict resolution framework we talk it already today uh and we have it on on kubernetes it's not open source it's closed Source but you have you can if you have interest we can uh speak about this in more details of course feel

free to catch up with Boris in pauses and other days to have more in-depth conversations one more yeah in the meantime more questions oh no please do I will repeat do you support any extensions like time scale DB or anything else on this operator yes yes we we are supporting uh almost I I don't know the the uh all cont trip uh extensions time scale DB extension

as well and several other extension PG Vector for example yeah and yeah thank you more questions yes oh there is a microphone coming very quick one um does it uh roll back automatically as well if upgrade it's it's just upgrade uh we need to to talk about different upgrades yeah for example minor upgrade is replacing of uh of image yeah if you make major upgrade we have

different uh uh techniques for example one of techniqu is PG Dum PG restore with FAL file second is to to create uh logical replication what we can also supports with yl file and we have now uh in place uh uh uh major upgrade uh but uh it's dangerous we are not recommended to do it because you need to test if you fail is not automatic or you

need to be care about your fullback scenario okay so but in the situation where which you demoed uh switching to from 153 to 154 if my if uh if one the replicas is terminated and it's not able I mean it doesn't spin up for reasons um does it can it recognize that it's not that the minor upgrade is not going well and just stop it yeah yeah

if you are not in sync if your status is not in sync it will be say you cannot upgrade because this is for this uh unsupervised rolling upgrade the first uh prequest is to have syn status okay thank you