Replicating Data, SQL Functions, High Availability, Locking Issues | Scaling Postgres 55

In this episode of Scaling Postgres, we review articles covering methods to replicate data, SQL functions, tools for high availability and locking issues.

Content Discussed

• How to Replicate PostgreSQL Data to Remote Sites
• unaccent: Getting rid of umlauts, accents and special characters
• Fun with SQL: Text and system functions
• Managing High Availability in PostgreSQL – Part II
• PostGIS tips: Where to get started
• [YouTube] PostgreSQL Locking Issues: a talk for devs and DBAs

YouTube Video

Podcast Audio

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, episode55.

[00:00:20] All right, I hope everyone is doing well this week. We are a little light on content thisweek. I couldn't find a lot of content. I don't know if it's spring break in the United States or whatit is, but not too much content this week. So this may be a quick show. So the first piece ofcontent is how to replicate PostgreSQL data to remote sites. And this is from the severalnines.com blog. And basically they're talking about a table level replication, how you canreplicate data from one table to another and ideally to a remote location somewhere else. Andthey talk about some of the factors that could impact it with regard to your table structure, howmuch data you're talking about moving, and then basically the resources of the system you'reusing. 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 disadvantagesof using them. And of course, when I was first reading, the first thing I was thinking about isPostgreSQL logical replication. So they go over that and some of the advantages anddisadvantages of that. And then they also cover foreign data wrappers. Now, that's not copyingdata, that's actually enabling access to a remote server as if it was a local table. But forcompleteness they mentioned this as well as an option for accessing data. So if you have aneed to copy tables from one system to another, or from one location to another, definitely ablog post to check out.

[00:01:49] The next post is Unaccent getting rid of umlauts, accents and special characters. Andthis 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 aquality between a character with an Umlaut or some type of accent and a character without it, it'sgoing to consider it false. And even different variations. It still considers it false. However, thereis a PostgreSQL extension called an accent, which I was not aware of. So once you add this anaccent, you can actually use the function called an accent that removes these from characterstrings. So then in that case, you could, for example, use this function to do comparisonoperators. Now, they do say there is one caveat down here. Whereas you can do a functionalindex, it has to return an immutable result. Unfortunately, with this function, that's not the casehere. So they have a quote here, if you want to create an index on an unaccented string, youhave to create an additional column which contains a precalculated value, some form ofmaterialization, otherwise it's not possible to do. But still, this is an extension I was not familiarwith. 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 theunaxent function. These are other built in PostgreSQL functions that they talk about and they goover 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 sometext, reversing characters, or even doing regexes. And then they also talk about two functionsthat are typically used when you're doing database administrative tasks. Is PG size pretty toconvert a size into something more human readable or converting back the other way, using PGsize bytes to convert a particular size to a number of bytes. So PostgreSQL has tons offunctions 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 wediscussed part one where they covered how to use PostgreSQL Automatic Failover or PAF byCluster Labs. In this part two, they're covering using Replication Manager from SecondQuadrant to be able to manage Replicas and switch over High Availability. So basically whatRepmanager does is manages replication and failover for your PostgreSQL clusters. So youhave a primary and you have several Replicas. It coordinates if the primary fails, electing andpromoting one of those Replicas to be the new master and have those existing Replicas startfollowing the new master. So norma --lly this is something you have to manually do, but Rep Manager helps the creation of theseReplicas and also coordinates an election automatically if the primary should fail. So this goesover Rep Manager, how it uses this particular command line interface and there's a daemonassociated with it or a daemon associated with it. They talk a little bit about how it works andthen go into some pros and cons versus potentially some other solutions to do it. And thenactually did some test scenarios where what happens on the standby if we kill the postgresprocess or stop it or reboot it, what happens? And they looked also at the primary as well andthen different network isolation tests. So if you're looking at a High Availability solution, this is aparticular blog post to check out, as well as part one that we actually also covered on a previousepisode of Scaling Postgres.

[00:05:38] The next post is PostGIS tips where to get started. And this is from the MediumInnovation and Technology blog post. And it looks as if it's the City of Boston's analytics teamimplementing a data warehouse platform and how they're using PostGIS extension for datapipelines and analysis. And this is actually part one of a three part blog post. So I'm not thatfamiliar with PostGIS, but if you're interested in this type of content, definitely something tocheck out, particularly the Part twos and Part Three about how they're using PostGIS with theirPostgreSQL 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 videosthat were put out during the Postgres Open in 2018, and I thought this was a pretty goodpresentation. Talk about PostgreSQL Locking Issue a talk for Devs and DBAs so he talks abouthow this can sometimes relate to poor performance, and talks about being mindful of idleintransaction queries, as well as how locking can impact auto vacuum. So it's definitely a goodreview 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 contentmentioned in the show. Notes be sure to head over to Scalingposgrass.com, where you can signup to receive weekly notifications of each episode, or you can subscribe via YouTube or itunes.Thanks. --