Episode Transcript
[00:00:00] In this episode of Scaling Postgres we talk about logical replication monitoring queries, SQL beautifier and incomplete startup packet. I'm Kristen Jameson and this is Scaling Postgres episode 93.
[00:00:22] Alright, I hope you're having a great week. A first piece of content is actually an advisory that was released on the Postgresql.org website titled Additional Advisory to 2019 November 14 Cumulative Update Release for Debian and Ubuntu users and there is a disclosed vulnerability and they suggest updating your PostgreSQL common packages as soon as possible. And the vulnerability is that the PostgreSQL super user could escalate to root using a deficiency in the Pgctl cluster command. So if you run PostgreSQL on Debian or Ubuntu, go ahead and update that PostgreSQL common package as soon as you're able.
[00:01:06] The next post is Interesting Aspects of Logical Replication at a glance and this is from Rafiasabee blogspot.com and basically he's talking about a few things to be aware of when you're using logical replication versus physical streaming replication. One of the first things to remember is you can do writes at the secondary. So not only can you replicate to it, unlike a readonly database doing streaming replication, with logical replication you can have rights going to there you could only be copying over one table or subset of tables, or even copy over data from one database and also accept rights to that same database you're replicating to. The second thing to keep in mind is with regard to the schema in that it is not automatically all copied over once you start the replication. So basically you have to create the tables you want at the secondary before you start the subscription. In addition, any schema changes aren't going to be replicated over either, so you need to handle those separately. So for example create them on the destination database and then make those changes to the primary database. Another thing mentioned is attribute comparison. So basically the attributes of a table are matched by name, so the column order does not have to be the same, it can be different. In addition, they mentioned here the data type can also be different as long as the text representation of the type is the same as the secondary. Plus the destination target can also have more columns than exist in the source target. So just something to keep in mind as you're thinking through how you're using logical replication sequences. So sequences do not get copied over to the destination database. So this is something to be aware of if you're doing writes to the destination database separate from what's being replicated because you could run into an ID collision if you're using sequences. So it's just something to keep in mind. Next thing mentioned is privileges. So he says the permissions for the tables are checked only at the time of creston of the subscription and never afterwards. So something to keep in mind. And lastly partition tables. So partition tables, the root or the parent table is not going to be able to be replicated over, but you can create a publication for each child table now hopefully in future versions of PostgreSQL they will be able to handle that as well. But overall a great list of things to be aware of if you're starting to use logical replication. The next post is Understanding PostgreSQL Query Performance and this is from PG IO blog and this covers a great review of things you can do to monitor your queries. The first one is you can use PGSTAT Activity to see what all the current queries are running against your system, particularly if you're trying to find some slow queries. And you can even see if there's certain weights that are happening or what's being locked or delayed by looking at the weight event type and the weight event in PG Stat Activity. Now that gives you a current view of what's going on in the database. If you want to look at more historical activities, there's two different things you can do. The first one is using the extension PG Stats statements and they go through the process of how you would be able to install this extension and getting working for your database. But after you do that, it does create statistics of queries against the system. And you can see how many times a particular query was called, what the query was, the total time to execute, the minimum amount of time. The maximum amount of time, the mean time, and also gives you statistics with regard to how memory and disk was potentially used for those queries. So you can see how resource intensive they were. And this blog post goes through and does a good job of explaining some of these different metrics that are collected on PGSTAT statements. Now another option for capturing historical queries is turning on the slow query log by setting the configuration parameter log min duration statement and putting an amount greater than negative one. So in this case if you set it to 1000 because it's in milliseconds, any query longer than a second will be logged to the log file. And the last thing they mentioned if you want to look at execution planes, you can enable the auto explain extension, because then if something is triggered longer than a particular period of times in their example here again 1000. Seconds, it will automatically do an auto explain in the query log so you can look at the explain plan for why a particular query was slow. So overall, very good blog post explaining how you can monitor your queries.
[00:05:41] The next post is Abusing PostgreSQL as an SQL beautifier and this is from CyberTech Postgresql.com. And they're talking about formatting a query that looks like this to something more friendly and how you can do this in PostgreSQL by designing a function that creates a temporary view and then uses the built in function PG get view definition to get a friendly printout of that query. So for example, they created this function and what they do is they create a temporary view and then they print out the view and then they drop that temporary view. So for example, when they input this type of SQL, it will output it in this friendly format and it prints out all of the column names from what the Asterisk is represented here. So if you're interested in potentially doing that, definitely a blog post to check out. The next post is PostgreSQL twelve. Improvement benign Log Entries incomplete Startup Packet so what they say is that there are some monitoring tools that query PostgreSQL to ensure it's running, but they're not actually doing any work. And this can cause a lot of incomplete startup packets in the log. So this is an example of what it looks like in version eleven, but in version twelve they say it detects that it is a zero size packet and just ignores it. So that way you can avoid these entries in your log. Now, if a nonzero packet is written, it still logs this invalid length of startup packet, but as long as it's a zero packet size, it will still remove that log. So for example, in version twelve you're still probably going to see information like this for some tools where it says connection reset by peer, whereas in version eleven you'll get that error, but you'll also get the incomplete startup packet, but in version twelve that will go away. So another reason to upgrade to PostgreSQL version twelve, the last post is PostgreSQL twelve, Explain settings. So again, this is a new feature that has been added called Settings. And when you do an Explain plan, anything that you have set at the session level, it will print it out that's different from what the default is. So for example, this is running an explain plan here, not using the settings, and you see this output, he happened to print it out in YAML format, but when he added settings on now it produces the same output. But if you change the session setting by setting sequential page cost to three, for example, or setting the random page cost to one, and now you run that query again, these two additional entries will appear. So it shows you that something has changed at the session level. So, yet another great addition to PostgreSQL twelve. And if you're interested in learning more about that, definitely check out this blog post.
[00:08:41] That does it. For this episode of Scaling Postgres, you can get links to all the content mentioned in the Show Notes. Be sure to head over to Scalingpostgres.com where you can sign up to receive weekly notifications of each episode, or you can subscribe via YouTube or itunes. Thanks.
