Recovery Configuration, Alter System, Transaction Isolation, Temp Table Vacuum | Scaling Postgres 85

In this episode of Scaling Postgres, we discuss changes to recovery configuration, the alter system command, transaction isolation and vacuum for temp tables.

Content Discussed

• Replication configuration changes in PostgreSQL 12
• How to Set Up Streaming Replication in PostgreSQL 12
• Where is my recovery.conf file in PostgreSQL v12?
• ALTER SYSTEM
• pg_basebackup
• Time and relative dimension in space
• What is autovacuum doing to my temporary tables?
• Newbie to PostgreSQL – where to start

YouTube Video

Podcast Audio

Transcript

All right. I hope you, your friends, family, and coworkers continue to do well. The first piece of content is "Replication configuration changes in PostgreSQL 12". This is from 2ndquadrant.com. Basically with Postgres 12, the way you set up recovery and replication has changed because the recovery.com file is no longer used. In fact, if you have one in the directory when you try to start the PostgreSQL 12 cluster it will not start and give you a fatal error using the recovery command file recovery.conf is not supported. In place of this file, instead, it's using two signal files. One is that the cluster should be a standby and it has a standby signal cluster in the data directory I believe, or it has a recovery signal file which means it's starting up in a targeted recovery mode. If both files are present, the standby signal file takes precedence. It mentions some of the things here that when the standby is promoted, the standby signal file is removed and if a point in time recovery is taking place, the recovery signal will be removed once the recovery target is reached. 

Of course, what this also means is that if neither of these files are present, the cluster will start acting as a primary database server. Now pretty much all of the configuration options that used to exist in recovery.conf are now in the postgresql.conf file except for two things that they mentioned here. One, the standby_mode is no longer used. They're using the two different file signal types now. And secondly, the trigger_file has been renamed to promote_trigger_file. So it's the file that will, when exists, promote this to the primary. Now the advantage of having these commands in the postgresql.conf file is that now you can do things such as alter the configuration and do a reload, whereas usually changes to the recovery.conf file require a full restart. Now what's great about this post is that it also mentions some gotchas with this and the first one is that ALTER SYSTEM settings take priority. So basically if someone changes the configuration by using ALTER SYSTEM, what it actually does is alter a postgresql.auto.conf file that actually exists in the data directory. 

I tend to use Ubuntu and the postgresql.conf file is located in the etc/PostgreSQL directory and there's a folder structure for each cluster that's on that system whereas this auto file exists in the data directory and basically you're not supposed to modify it. These changes take precedence over settings in the postgresql.conf file. So basically when you use the ALTER SYSTEM, command it actually makes those changes into this separate file. So it's just something to keep in mind that settings in this file take precedence over ones in the postgresql.conf file so you may run into confusion as different things are going to be configured in different areas. While this is important, I'll get into some of it. It looks like some of the utilities may be storing some settings here. The next gotcha is that replication configuration settings may be present even on primary servers. So because this is in the postgresql.com file it could have like primary connection info setting here could be in the primary but it's not a replica. 

Basically, you have to rely upon the standby files to identify if it's a replica or not or query a system table. The next gotcha is no canonical location to write configuration settings. Basically since it's part of the system configuration now it could be in multiple files or multiple locations whereas before it was just known to be in one file. The other thing they also mentioned is of course that the last configuration parameter red takes priority. And again, we're referencing the postgresql.auto.conf file that is modified by ALTER SYSTEM and other utilities that they mentioned here such as pg_base_backup or repmgr, which is a backup replication manager by 2ndQuadrant. Another gotcha is the risk of signal file confusion. So basically you need to just be aware of these two files in their existence and their purpose. The last gotcha that's really mostly just a configuration change is that only one parameter from the recovery target family may be specified. They mentioned that in PostgreSQL 11 and earlier the last instance of these parameters was used. However this time, the system won't start up unless there's one and only one which I think is a safer setting for the recovery target but it's just a change you need to keep in mind.

Now related to this post, there are actually three other posts that talk about these configuration changes. The next one is by percona.com and its title is "How to Set Up Streaming Replication in PostgreSQL 12". Now again they talk a lot about the changes that the fact that recovery.com has gone away and you need to do your configuration slightly differently to do replication. So they are doing this on a CentOS system, I believe so. They're showing the different ways to get the change in the parameters you need to set replication, create a user, and update the pg_hba file so that you can connect to the primary. Then they do a pg_base_backup to the primary. And they're actually using the R option, which we'll look at in a second here. 

That writes the appropriate files to be able to start the system in the recovery mode. So for example using this R option, the pg_base_backup utility actually writes to the postgresql.auto.conf file that was mentioned before that's in the data directory PostgreSQL and it adds the listen addresses and the primary connection info that was input with thepg_base_backup command here. So this is a pretty good post about showing you how to walk through and talk a little bit more about the changes that have come with PostgreSQL 12 with regard to recovery and also replication. 

The only thing that I saw here that kind of gave me pause is the fact that they actually did an echo to write to the postgresql.auto.conf file when it explicitly says in the postgresql.auto.conf file does not edit this file manually. It will be overwritten by the ALTER SYSTEM command. So they overwrote it here. I would probably not do this because it seems to be not the best practice but just something to keep in mind.

Next post is also related to this. So apparently with people trying 12, they may be running into this issue. This is "Where is my recovery.conf file in PostgreSQL v12?". This is from postgresrocks.enterprisedb.com and his short answer is it's gone. Again he runs through the different process, why it's gone again talking about how pg_base_backup with R option and how it works differently. So again, another resource to go and read to help you get up to speed before you implement 12 and need to change your recovery processes potentially as well as your replication processes. With this, I haven't used the ALTER SYSTEM command a lot. Typically what I do is modify the postgresql.conf file and then do a reload of the system but this may see more increased usage so it would probably be a good practice. And again, this command has been around for a long long time. 

But to get familiar with the Alter system command and how it works as well as looking at the pg_base_backup command. So in versions 11 and prior, the R option, or the writerecovery.conf option was to write a minimal recovery.conf file. Well, those no longer exist and in version 12, that same option in pg_base_backup actually does create a standby signal file and append connection settings to the postgresql.auto.com file. This also adds the replication slot if pg_base_backup is using a replication slot. So again, just some changes and some gotchas to be aware of with regard to PostgreSQL 12 in terms of recovery as well as replication.

The next post is "Time and relative dimension in space". This is from pgdba.org and this is a great post. He explains things very well, talking about essentially transaction isolation levels. First, he covers MVCC. So it's MultiVersion concurrency control, how PostgreSQL handles concurrent access to a lot of the different data. Then what each individual can see, he goes into how the XID is assigned once a transaction starts. That along with essentially hidden columns and tables that define what XID it was created or deleted at determines what you can see in the database based on the version that you're using. This was a great explanation of how this works. So if you're not familiar with that, definitely check this out. Then he follows up with a transaction isolation and how essentially this is the SQL standard that defines four levels of transaction isolation in terms of what's possible. 

At the lowest level, it's possible to get dirty reads when a transaction can access the data written by a concurrent not committed transaction when it's possible to get a non-repeatable read, a phantom read, and a serialization anomaly. So all sorts of different possibilities. Then what he did is create this table here that compares these possibilities with the isolation level options available in PostgreSQL. So essentially with a READ UNCOMMITTED, everything but a dirty read is possible. Essentially with PostgreSQL, it's not possible to get dirty reads. And I should mention that the default state is READ COMMITTED for PostgreSQL. So if you do nothing, it's going to be recommitted. So it is possible to get non-repeatable reads. You do a SELECT, you get a value, and another transaction modifies it when you do a SELECT again, you're going to get that updated setting. So you get the most updated data that's in the database when you do a query. 

But that's what a non-repeatable read is. But you can change the isolation level in PostgreSQL to be a REPEATABLE READ. This can be on a per transaction level or for the particular session that you're connecting to the database. With a REPEATABLE READ, you actually get rid of non-repeatable reads and phantom reads. Although it's still possible to get a serialization anomaly. With the order of the transactions, there's no guarantee as to when that can happen. Here he talks about being able to set the transaction isolation level as a part of a transaction and he goes over discussing this in more detail, READ COMMITTED, along with some examples, REPEATABLE READ, and then finally SERIALIZABLE. So again, this is a great post and discusses how this kind of system works and how using these transaction isolation levels you can alter the concurrency of PostgreSQL depending upon your use case. So particularly if you're a developer, I encourage you to check out this post.

The next article is "WHAT IS AUTOMACUUM DOING TO MY TEMPORARY TABLES?". This is from cybertec-postgresql.com and he's talking about what autovacuum does. This essentially cleans tables, like what was mentioned in this previous post that essentially to handle MVCC, what PostgreSQL does is it doesn't do updates in place when there's an update. What it does is it actually creates a new row and then flags the old row essentially for deletion, but it can still be visible based on the transaction isolation level. What autovacuum does is it goes into the table and then clears out all the old rows, essentially finally deleting them as opposed to just being marked for deletion, as well as resetting the XID that was mentioned in this post as well. So autovacuum goes on a regular basis and essentially vacuums all these dead tuples from the tables. 

But an issue is it actually does not vacuum temporary tables. Temporary tables exist within the essential connection that you're using and they get discarded once that connection is stopped or that session has stopped. And he has an example here where he's actually using an extension to be able to get statistics with regard to the tuple. He creates a real table called t_real and then creates a temporary table called t_temp. He inserts 5 million rows. He deletes half of those rows from each of the tables, and waits a bit for autovacuum to clean up the real table. Then when he checks the stats of the tuple for the real table, you can see hardly any dead tuples and a fair amount of free percentage of space has essentially been reclaimed by doing the vacuum operation. Whereas when he looks at the temporary table, you can see tons of dead tuples. 

So autovacuum has not touched this table and you, of course, don't have that much free space. So this can be an issue if you're trying to keep temporary tables around for a long time because they're not going to be autovacuumed. He says you can manually vacuum, so that works, but definitely, again, don't have long-running transactions with temporary tables that last a while because they're not going to be vacuumed. If you do have a temporary table, they get so large it's going to be around a while or potentially run into a transaction ID wraparound, depending on how much data you're working with. Sounds like you may need to manually vacuum it or drop those temp tables when you can and recreate them again. So just something to keep in mind.

The last post is "Newbie to PostgreSQL - where to start". This is from highgo.ca and it's basically listing resources that they suggest to their developers or people getting to start to use PostgreSQL for the first time. The number one link he mentions here is postgresqltutorial.com, as well as momjian.com's website at his extended presentations and of course, the official PostgreSQL documentation. So if you are at the Getting Started stage, it's definitely a blog post to check out.