Picturing Vacuum, Recovery Changes, Cert Authentication, Function Replication | Scaling Postgres 74

In this episode of Scaling Postgres, we discuss vacuum through pictures, upcoming recovery changes, certificate authentication and how functions get replicated.

Content Discussed

• yum upgrade postgresql11 panic!
• PostgreSQL Award
• Webinar: Postgres Vacuuming Through Pictures [Follow up]
• Postgres 12 highlight - Recovery changes
• Certificate Authentication Recipe for PostgreSQL Docker Containers
• PostgreSQL: How are functions and stored procedures replicated?
• Automatic Index Recommendations in PostgreSQL using pg_qualstats and hypopg
• jsquery vs SQL/JSON
• Waiting for PostGIS 3: ST_AsGeoJSON(record)
• Waiting for PostGIS 3: Hilbert Geometry Sorting

YouTube Video

Podcast Audio

Transcript

[00:00:00] In this episode of Scaling Postgres we talk about picturing vacuum recoverychanges, certificate authentication and function replication. I'm Kristen Jameson, and this isscaling postgres episode 74.

[00:00:21] Alright, I hope you're having a great week. Our first piece of content is a PostgresSQL award and this was listed on the second quadrant, Postgresql.com blog. But basically,PostgreSQL was presented the 2019 O'Reilly Open Source Award for lifetime achievement. Andit looks like Mark WAM, Bruce momgean and Hoffpettis accepted the reward on PostgreSQL. Sodefinitely quite a recognition.

[00:00:50] The next post is actually a webinar that you can sign up for called Webinar PostgresVacuuming through Pictures follow up and you can just click the link here. To get access to thewebinar you need to submit some contact information, but it gives a good overview of Vacuumtalking about its purpose, given that Postgres uses MVCC and therefore must be able to seemultiple versions of rows. Once some of these old versions are done, vacuum is used to cleanout those old rows. That's why they call it a vacuum. He talks also about the importance ofconfiguring auto vacuum for being able to handle bloat and some numerous settings that youcan make to configure auto vacuum or Vacuum for your environment. Now there weren't quiteas many pictures as I thought there might be, but it does give a pretty good overview of Vacuumand why and how it works. So definitely a piece of content I encourage you to check out.

[00:01:45] The next post is Postgres Twelve highlight recovery changes. And this is from PakierXYZ and he's talking about the changes coming to how you handle recovery or standbydatabases with Postgres twelve. And basically there will no longer be a recovery.com file, but allthose configuration changes are now taking place within the Postgresql.com file. And some ofthe benefits, we actually mentioned some of these, but what he mentions here is the ability toreload some of these parameters, to be able to change them, to monitor the values which showand to apply changes with Alter system. But when you move to twelve you definitely need to beready for these changes because again, if the recovery.com file is present, PostgreSQL will notstart. So you definitely need to review these additional changes and make changes to yourenvironment when you're planning the move to Postgres twelve. So definitely another piece ofcontent to make sure you're prepared.

[00:02:41] The next post is certificate authentication recipe for PostgreSQL docker containers.And this is from Crunchydata.com. Now actually this isn't only for docker containers. Now they'redescribing how to do it within a docker container, but a lot of the information is beneficial even ifyou're not using docker. Basically, how do you set up certificate authentication for PostgreSQL?Now they have a lot of code here that you can directly use, but here's pretty much the process.So the step one is first you create a root certificate authority. So you need some sort of commoncertificate authority to sign the certificates that are being used by the server side and the clientside. So usually you'll want to create your own certificate authority to do this, but you can chooseto purchase the certificates if you want. Instead, the next step is to generate the PostgreSQLserver key and its certificate, then configure PostgreSQL to enable TLS and certificate basedauthentication. Again, this is within the configuration area as well as changing the Pghba.comfile. And they also mentioned some few other docker commands here, but you can apply thoseto any system environment. Step four is generating the client key and the certificate. So thesewould be used on the clients to be able to connect and then they show you how to connect.Basically they're exporting a couple of different parameters that your client uses to be able toconnect up to a certificate authentication secured server. So if you're interested in usingcertificate authentication, even if you're not using Doctor, this is definitely a great post to checkout. Now, there was also a video linked here called how to set up an enterprise certificate basedauthentication system. Now this link goes to a YouTube video talking about advanced replication.So they talk about using all sorts of other authentication methods, but during the last part they dodescribe setting up certificate authentication. So --this video is another piece of content you can check out to help you set up certificateauthentication.

[00:04:45] The next post is PostgreSQL. How are functions and stored procedures replicated?And this is from CyberTech postgresql.com. And the first thing they cover is well, how arefunctions and procedures stored? Basically in system tables. And there's a System Viewpg Procthat you can take a look at. So he created just a basic function here and you can see it actuallystores in the source when using this procedure. Language 14 is actually SQL. It just stores thecode right in the source field. But if you have procedures stored in other languages, it actuallyrepresents it a bit differently. So if your code relies on libraries, it stores it a bit differently. So forexample, he created the PG Trigram extension and then he looked that up in the PG Proc viewor System table and you see the source is actually a name, but then the library is referencedhere. So now in terms of replication, the System tables of course get replicated over to Replicas.So you don't need to do anything about that. But if you have a library dependency, those need tobe placed on every Replica you're going to use. And he says here, quote, if you create afunction on the master written in C, for example, and your slave does not have the desiredlibrary, the changes in the System tables will be present. There's just no way to call thesefunctions on the slave or the Replica. So basically the System tables get replicated, but anylibraries you need to make sure are on each instance that you're replicating to. So definitelysome great information to be aware of.

[00:06:19] The next post is Yum upgrade PostgreSQL eleven, panic. So this is from Luca Ferrariat F Lucan 1978 GitHub IO. He was wanting to upgrade a database system and he just usedSudo Yum upgrade PostgreSQL. But unfortunately this database server did not start and hecouldn't connect to it. And looking at it when trying to start, it said basically the data directory ismissing or empty. So he was like, what? So he said systemd was trying to launch PostgreSQLwith an empty PG data directory. So he says apparently quote yum upgraded my system Dconfiguration for PostgreSQL to the CentOS default. So basically the service file was overwrittenwithout any advice. So basically what he eventually did is used the command system CTL EditPostgreSQL eleven and added an environment PG Data to state where the data directory is, andthat helped it find it. So, I haven't run into this when doing upgrades, like I tend to use Ubuntu,but this is definitely something to be aware of. So definitely a blog post to check out if you everrun into this particular type of situation.

[00:07:32] The next post is automatic. Index recommendations in PostgreSQL using PG,qualstats and hypopg. So this is from the Procona.com Blog, and they had done a previous postthat we covered on Scaling Postgres, talking about hypopg, which sets up hypothetical indexes.Basically this post walks through a process of using hypopg and PG Qual Stats in order topredict or make a recommendation for what indexes should be used based upon queries thatare running against the system. So ideally you would look at some queries here and it would say,okay, we recommend creating this index and it should give this percentage improvement. Theysay the PG Qualstats is an extension developed by the Powa team to uncover the need forstoring predicates or Quals. And this is important because PG Stats statements doesn't store thepredicates, you only get the variables. So they basically walked through how to set up PGQualstats. And so for setting up then to do these types of index predictions, they needed toinstall these three extensions hypopg, PG Stat statements and PG Qualstats, and add to theshared preload libraries the PG Stat statements as well as PG Qualstats. And then they createdthis quite complex function that they called Find Usable Indexes to be able to consult thedifferent tables to make the recommendations. And at the end they get some output like this,which basically gives them for particular queries what the index recommendation is. So if you'rewanting to use this potentially automated way of determining what some additional indexescould help your application or system, definitely a blog post to check out. The next post isjsquery versus SQL JSON. This is from Tombrown blogspot.com. Also, the name of the blog is T--om's Memory Leak and I wasn't necessarily aware of this one but their JS query is actually anextension that you can install and he says, quote here it was introduced as a non core extensionin PostgreSQL 9.4 and higher now with PostgreSQL twelve coming and provides a native way toquery JSON data. He compares and contrasts both of these so with the changes that arecoming with SQL, JSON and postgres twelve. The reason why I'm highlighting this post isbecause it gives you another opportunity to kind of learn the JSON path syntax. Now hecompares it to Jquery but this is basically another opportunity to learn the JSON path syntax. Soif you're interested in doing that, definitely a blog post to check out.

[00:10:06] The next two posts are related to PostGIS and as I've said I'm not an expert on thatbut I wanted to make them aware. The first post and both of these are from Crunchydata.com.The first post is Waiting for PostGIS three Stasgo JSON record. So apparently this is a newfunction that is available. So if you're potentially interested in that, definitely blog post to checkout as well as a waiting for PostGIS three Hilbert geometry sorting. And they say order geometrycolumns will return rows using a Hilbert curve ordering and do so about twice as fast. So ifyou're interested in that, definitely another blog post to check out.

[00:10:47] That does it. For this episode of Scaling Postgres, you can get links to all the contentmentioned in the show notes. Be sure to head over to Scalingpostgres.com where you can signup to receive weekly notifications of each episode, or you can subscribe via YouTube or itunes.Thanks. --