background

Logical Replication, Monitoring Queries, SQL Beautifier, Incomplete Startup Packet | Scaling Postgres 93

Join Over 1,000 Engineers & Get New Episodes Weekly!

In this episode of Scaling Postgres, we discuss logical replication aspects, how to monitor queries, investigating a SQL beautifier and removing incomplete startup packet.

Content Discussed

YouTube Video

Podcast Audio

Transcript



[00:00:00] In this episode of Scaling Postgres we talk about logical replication monitoringqueries, SQL beautifier and incomplete startup packet. I'm Kristen Jameson and this is ScalingPostgres episode 93.

[00:00:22] Alright, I hope you're having a great week. A first piece of content is actually anadvisory that was released on the Postgresql.org website titled Additional Advisory to 2019November 14 Cumulative Update Release for Debian and Ubuntu users and there is a disclosedvulnerability and they suggest updating your PostgreSQL common packages as soon aspossible. And the vulnerability is that the PostgreSQL super user could escalate to root using adeficiency in the Pgctl cluster command. So if you run PostgreSQL on Debian or Ubuntu, goahead 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 fromRafiasabee blogspot.com and basically he's talking about a few things to be aware of whenyou're using logical replication versus physical streaming replication. One of the first things toremember is you can do writes at the secondary. So not only can you replicate to it, unlike areadonly database doing streaming replication, with logical replication you can have rights goingto there you could only be copying over one table or subset of tables, or even copy over datafrom one database and also accept rights to that same database you're replicating to. Thesecond thing to keep in mind is with regard to the schema in that it is not automatically all copiedover once you start the replication. So basically you have to create the tables you want at thesecondary before you start the subscription. In addition, any schema changes aren't going to bereplicated over either, so you need to handle those separately. So for example create them onthe destination database and then make those changes to the primary database. Another thingmentioned 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, theymentioned here the data type can also be different as long as the text representation of the typeis the same as the secondary. Plus the destination target can also have more columns than existin the source target. So just something to keep in mind as you're thinking through how you'reusing logical replication sequences. So sequences do not get copied over to the destinationdatabase. So this is something to be aware of if you're doing writes to the destination databaseseparate from what's being replicated because you could run into an ID collision if you're usingsequences. So it's just something to keep in mind. Next thing mentioned is privileges. So hesays the permissions for the tables are checked only at the time of creston of the subscriptionand never afterwards. So something to keep in mind. And lastly partition tables. So partitiontables, the root or the parent table is not going to be able to be replicated over, but you cancreate a publication for each child table now hopefully in future versions of PostgreSQL they willbe able to handle that as well. But overall a great list of things to be aware of if you're starting touse logical replication. The next post is Understanding PostgreSQL Query Performance and thisis 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 runningagainst your system, particularly if you're trying to find some slow queries. And you can evensee if there's certain weights that are happening or what's being locked or delayed by looking atthe weight event type and the weight event in PG Stat Activity. Now that gives you a current viewof what's going on in the database. If you want to look at more historical activities, there's twodifferent things you can do. The first one is using the extension PG Stats statements and they gothrough the process of how you would be able to install this extension and getting working foryour database. But after you do that, it does create statistics of queries against the system. Andyou can see how many times a particular query was called, what the query was, the total time toexecute, the minimum amount of time. The maximum amount of time, the mean time, and alsogives you statistics with regard to --how memory and disk was potentially used for those queries. So you can see how resourceintensive they were. And this blog post goes through and does a good job of explaining some ofthese different metrics that are collected on PGSTAT statements. Now another option forcapturing historical queries is turning on the slow query log by setting the configurationparameter log min duration statement and putting an amount greater than negative one. So inthis case if you set it to 1000 because it's in milliseconds, any query longer than a second will belogged 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 aparticular period of times in their example here again 1000. Seconds, it will automatically do anauto explain in the query log so you can look at the explain plan for why a particular query wasslow. 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 fromCyberTech Postgresql.com. And they're talking about formatting a query that looks like this tosomething more friendly and how you can do this in PostgreSQL by designing a function thatcreates a temporary view and then uses the built in function PG get view definition to get afriendly printout of that query. So for example, they created this function and what they do is theycreate 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 itprints out all of the column names from what the Asterisk is represented here. So if you'reinterested in potentially doing that, definitely a blog post to check out. The next post isPostgreSQL twelve. Improvement benign Log Entries incomplete Startup Packet so what theysay is that there are some monitoring tools that query PostgreSQL to ensure it's running, butthey're not actually doing any work. And this can cause a lot of incomplete startup packets in thelog. So this is an example of what it looks like in version eleven, but in version twelve they say itdetects that it is a zero size packet and just ignores it. So that way you can avoid these entries inyour log. Now, if a nonzero packet is written, it still logs this invalid length of startup packet, butas long as it's a zero packet size, it will still remove that log. So for example, in version twelveyou're still probably going to see information like this for some tools where it says connectionreset by peer, whereas in version eleven you'll get that error, but you'll also get the incompletestartup packet, but in version twelve that will go away. So another reason to upgrade toPostgreSQL version twelve, the last post is PostgreSQL twelve, Explain settings. So again, thisis 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 thedefault is. So for example, this is running an explain plan here, not using the settings, and yousee this output, he happened to print it out in YAML format, but when he added settings on nowit produces the same output. But if you change the session setting by setting sequential pagecost to three, for example, or setting the random page cost to one, and now you run that queryagain, these two additional entries will appear. So it shows you that something has changed atthe session level. So, yet another great addition to PostgreSQL twelve. And if you're interestedin 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 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. --

episode_image