Episode Transcript
[00:00:00] In this episode of Scaling Postgres, we talk about replicating data, SQL functions, high availability and locking issues. I'm Kristen Jameson and this is Scaling Postgres, episode 55.
[00:00:20] All right, I hope everyone is doing well this week. We are a little light on content this week. I couldn't find a lot of content. I don't know if it's spring break in the United States or what it is, but not too much content this week. So this may be a quick show. So the first piece of content is how to replicate PostgreSQL data to remote sites. And this is from the several nines.com blog. And basically they're talking about a table level replication, how you can replicate data from one table to another and ideally to a remote location somewhere else. And they talk about some of the factors that could impact it with regard to your table structure, how much data you're talking about moving, and then basically the resources of the system you're using. And it talks about a few ways to do that. Probably I would classify as older versions. There's Slony and also Bucardo, and they go over some of the advantages and disadvantages of using them. And of course, when I was first reading, the first thing I was thinking about is PostgreSQL logical replication. So they go over that and some of the advantages and disadvantages of that. And then they also cover foreign data wrappers. Now, that's not copying data, that's actually enabling access to a remote server as if it was a local table. But for completeness they mentioned this as well as an option for accessing data. So if you have a need to copy tables from one system to another, or from one location to another, definitely a blog post to check out.
[00:01:49] The next post is Unaccent getting rid of umlauts, accents and special characters. And this is from CyberTech postgresql.com.
[00:01:59] And they're talking about how when you normally do a select and you're looking for a quality between a character with an Umlaut or some type of accent and a character without it, it's going to consider it false. And even different variations. It still considers it false. However, there is a PostgreSQL extension called an accent, which I was not aware of. So once you add this an accent, you can actually use the function called an accent that removes these from character strings. So then in that case, you could, for example, use this function to do comparison operators. Now, they do say there is one caveat down here. Whereas you can do a functional index, it has to return an immutable result. Unfortunately, with this function, that's not the case here. So they have a quote here, if you want to create an index on an unaccented string, you have to create an additional column which contains a precalculated value, some form of materialization, otherwise it's not possible to do. But still, this is an extension I was not familiar with. So if you have this issue. Definitely a blog post to check out.
[00:03:07] The next post is fun with SQL text and system functions, so much like looking at the unaxent function. These are other built in PostgreSQL functions that they talk about and they go over some different functions that allow you to manipulate strings in terms of tracking positions, doing concatenation, and also talking about things such as finding a substring, trimming some text, reversing characters, or even doing regexes. And then they also talk about two functions that are typically used when you're doing database administrative tasks. Is PG size pretty to convert a size into something more human readable or converting back the other way, using PG size bytes to convert a particular size to a number of bytes. So PostgreSQL has tons of functions and these are just some examples in this blog post if you'd like to look into it further.
[00:03:58] The next post is Managing High Availability in PostgreSQL Part Two. So we discussed part one where they covered how to use PostgreSQL Automatic Failover or PAF by Cluster Labs. In this part two, they're covering using Replication Manager from Second Quadrant to be able to manage Replicas and switch over High Availability. So basically what Repmanager does is manages replication and failover for your PostgreSQL clusters. So you have a primary and you have several Replicas. It coordinates if the primary fails, electing and promoting one of those Replicas to be the new master and have those existing Replicas start following the new master. So normally this is something you have to manually do, but Rep Manager helps the creation of these Replicas and also coordinates an election automatically if the primary should fail. So this goes over Rep Manager, how it uses this particular command line interface and there's a daemon associated with it or a daemon associated with it. They talk a little bit about how it works and then go into some pros and cons versus potentially some other solutions to do it. And then actually did some test scenarios where what happens on the standby if we kill the postgres process or stop it or reboot it, what happens? And they looked also at the primary as well and then different network isolation tests. So if you're looking at a High Availability solution, this is a particular blog post to check out, as well as part one that we actually also covered on a previous episode of Scaling Postgres.
[00:05:38] The next post is PostGIS tips where to get started. And this is from the Medium Innovation and Technology blog post. And it looks as if it's the City of Boston's analytics team implementing a data warehouse platform and how they're using PostGIS extension for data pipelines and analysis. And this is actually part one of a three part blog post. So I'm not that familiar with PostGIS, but if you're interested in this type of content, definitely something to check out, particularly the Part twos and Part Three about how they're using PostGIS with their PostgreSQL installations, and the last post isn't something that was done this week. However, I've gone back because it was a light week this week and looked at some of the previous videos that were put out during the Postgres Open in 2018, and I thought this was a pretty good presentation. Talk about PostgreSQL Locking Issue a talk for Devs and DBAs so he talks about how this can sometimes relate to poor performance, and talks about being mindful of idle intransaction queries, as well as how locking can impact auto vacuum. So it's definitely a good review post. And if you're looking for more content this week, if you haven't watched this video, definitely one to check out.
[00:06:57] 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 Scalingposgrass.com, where you can sign up to receive weekly notifications of each episode, or you can subscribe via YouTube or itunes. Thanks.
