 Good afternoon everyone, welcome back and please welcome Josh Brokers who is here to talk about PostgreSQL replication. I hope you all enjoy it and please raise your hand if you have any questions. Thank you. Okay so welcome everybody to the PostgreSQL replication mini tutorial. There's a few things before we get started. First of all there were some exercises in a virtual environment to download and install in your laptop before you got here. If you haven't done it you'll just have to follow along with what I do on screen. Everybody else should be able to go through it. In the get checkout and in a directory in that virtual machine are those exercises as a text file so that you can do copy and paste for some of the longer command lines. This is actually the cut-down version of a four-hour tutorial which will be going through the first three or four sections of. Okay which will be going through the first three or four sections of. You're welcome to continue with the self-paced exercises and directions after the tutorial. Are any of you going to be at the longer version of this in Wellington as well? Okay that makes sense. I'll be doing a longer version of this in Wellington next week but I believe that's already sold out so even if you are in Wellington it doesn't help you much. The so and the other thing I wanted to do was apologize to any VI users because apparently the version of VI on that Docker image is still broken and you'll have to use nano to edit things. So sorry about that. First time doing this in Docker so I did not notice that. So let's talk a little bit about this. I'm on the PostgreSQL core team, couldn't know. I do a lot of Postgre's stuff including training and including setting up replication for clients. The purpose of this tutorial is to actually cover the basic built-in tools and methods for replication. There are a bunch of management tools that are available in order to manage some of this stuff at a higher level for you but I find first of all the built-in base level tools are actually not that hard and second before you rely on a management tool it's really important to understand what it's doing under the hood and so that's what you're going to learn here is what the basics are a Postgre's replication on the command line and in the Postgre's core so that if necessary you can write your own tools to integrate them into your environment. So we'll be covering the first couple of sections of this so basic asynchronous replication, replication configuration, some tools and monitoring depending on how the time goes we'll get to archiving and file based replication on this. Maybe even failover we will see. Things that I'm not even going to attempt to cover. Disaster recovery planning, third-party tools, application design, other forms of replication, point-in-time recovery, too much stuff. So first of all it explains this is all about binary application. Let me explain what binary replication is and by the way for those of you particularly for those of you who are using Vagrant you should bring it up now because it takes a little while. For Docker you can set it up later except you just want to make sure that it's working. So let's start out with some terminology. The for so the built-in replication for Postgre's at least up until very recently is what's known as single master replication as in there is one read write server and a number of read only servers that receive data and there's different terms used in the database industry for these things. The one used in the Postgre's kill documentation is master standby. I find that a little bit confusing for pure replication when you're not looking at necessarily a failover configuration so I tend to use the terms master replica but these mean the same thing. Now I also talk about this is binary replication and what makes it binary as opposed to other forms of replication. There's three basic sort of forms of replication for SQL relational databases. Actually it's true even of non-relational databases. One is statement replication. The second is role replication and the third is binary replication and these replicate respectively queries, rows and data pages. So for statement replication which is pretty much the first kind of replication that every new database system starts with because the easiest to implement you simply send any right statements get sent to all nodes and then you try to work out any inconsistencies between how the nodes process those right statements. There's a lot of examples of this within the Postgre's and other world. PG pool 2's built-in replication, grid SQL which is now called Stato, clustered JDBC, etc. The replication that came with my SQL version 3 was pure statement replication. Our second replication mechanism and something that's been around in Postgre's for a while is what we call row-based replication and with row-based replication is the rights go to a single master and then the new versions of the rows from the various tables get distributed to the replicas from that single master. This is better than statement-based replication for most things because you eliminate a lot of inconsistencies around for example timing and random changes and statements that fail on one server but not on the others. There's a number of systems to do this within the Postgre's world, Sloney 1, Lundista, Bucardo. Current MySQL built-in replication is essentially row-based replication and we're going to be returning to row-based replication while we are returning to row-based replication with Postgre's 9.4 which offers a new option for that. I'll mention that later. I won't really have time for a demo. Now what we're going to be going over in this tutorial is what's known as binary replication and in the case of binary replication what happens is a single master receives rights and then those rights are distributed to the replicas as binary blocks of data that is chunks of the table files themselves get distributed. For those of you familiar with DRBD, how many people here have used DRBD? So you can think of this as sort of a form of dedicated PostgreSQL DRBD without a lot of the performance overhead. It has gone by a number of names reflecting a lot of its development history. One of them is streaming replication which refers to the ability to stream data changes over a database connection. It's also called hot standby which refers to the ability of standby servers, otherwise known as replicas, to run read-only queries while in standby mode and that's as opposed to another term we use called warm standby which is where you have a read-only server that is not able to accept requests but is able to get promoted to master node very quickly. The advantages of binary replication compared to other forms of replication are that it's low administration, there's very low overhead on the master for having replicas, it's non-invasive, it's completely agnostic to what's going on in your application, low latency and it's good for large databases because your copies are basically block-based disc copies so you're doing a lot of sequential reads which is faster. There are some disadvantages though. When you're replicating PostgreSQL, you're replicating your entire PostgreSQL installation what's known as the PostgreSQL instance or PG data directory or confusingly enough cluster which is a term from the SQL standard. So you can't replicate just one table or just one database within a group of databases or one schema or whatever, you have to replicate the whole thing. A second disadvantage is that the replicas cannot accept writes of any kind, not even temporary tables. There are some things that don't get replicated that I will mention later and there's this thing called query cancel which is another drawback where the replication stream conflicts with load balancing. So but let's go over what we're doing first and and move into actually doing some hands-on stuff and do some streaming asynchronous replication. This is what we should start with because this is the kind of replication that most people want most of the time, right? You just want you want to copy your PostgreSQL server, you want it to be as up-to-date as possible without putting any load in the master and you want to be able to fail over quickly and that is streaming asynchronous replication for you. So let's throw some more terminology at you. Binary replication PostgreSQL has actually grew out of our continuous backup feature which uses another industry term called point-in-time recovery and so this term recovery is used both in the replication documentation and in some file and directory names. So just understand that when you see the word recovery that also means replication. I'm going to use a couple of other terms called snapshot or clone which refer to taking a point-in-time, snap a point-in- time copy of a running PostgreSQL server and the final term is standalone and that is a server which is read-write and is neither a met is not participating replication. It's neither a master nor a replica. So here's basically how it works. You have your PostgreSQL server. This is your master server but right now it's a standalone and you take a copy, a clone of the server and then you tell that clone of that server, hey, you're a clone of this other server so go ahead and spin up. And now one of the important things about taking the clone of the server is that if we're just doing our sync or CP can we take an instantaneous copy of the server so that all files are 100% consistent with each other? No. So we actually need something to bridge the gap from when we started the copy to when we finished the copy in order to make the files 100% consistent to a particular moment in time. Fortunately, built into PostgreSQL we actually have that kind of mechanism which is called PostgreSQL transaction logs. PostgreSQL transaction logs are actually just a series of binary differences between before and after pictures of the files that make up your database. And so by shipping over the transaction logs from the beginning of the copy to the end of the copy we can bring the clone up to a consistent state without needing an external mechanism to take an instantaneous point in time copy of the master and without shutting the master down. So we copy the files, we copy the transaction log, we apply those transaction log to the replica and this is all done by PostgreSQL machinery but you should understand what's going on. And then we start up the replica and tell it, hey, by the way, you're a replica so you should actually communicate with your master which is located here. And so the replica starts up this process called the wall receiver. The wall receiver connects to the master and says, hey, I'm a replica here. The master starts up a process called the wall sender which connects back to the replica and starts sending over a continuous stream of data changes that represent all of the rights to the master database. And then at that point you're replicating and you can repeat the process with additional servers for however many replicas you want to support. Let's go ahead and do that. So this is the Docker environment. For those of you who are using Docker, go ahead and do the Docker run. I dash IT for active. For those of you in Vagrant, hopefully already brought it up. Log into Vagrant. Now a couple of things. One is because we're going to be switching back and forth between different sessions, we're going to want Tmux on here. Is there anybody here who is unfamiliar with Tmux? Oh, a few people. Okay. So Tmux is kind of screen 2.0 if you're familiar with screen. It's a virtual terminal or a set of virtual terminals. So if you type that command Tmux as root as we just did. For those of you in Vagrant, you need to switch to root. So su-dash per the exercises. Because you need to start out as root here. For those of you in Docker, you start out as root. So you want to go ahead and start up Tmux which has a virtual terminal here and we're going to actually create a second virtual terminal because we need to switch back and forth. So hit control B and then the letter C without control. And that creates a second virtual terminal. And in the second virtual terminal we're going to switch to being the Postgres user. Control B and then the letter C without control. Yeah, control B then C. So and then we can switch back and forth with control B and then N. And so switch back and forth until you're back into that Postgres shell. For those of you in Vagrant Postgres, the master should have auto started. For Docker it won't. So we're going to actually send a command right now to start up the master. So we use because I've arranged several different Postgres instances on this same image. We need to do this manually not using the service command. So there's your command to start the master here. And that goes ahead and starts up the master. Okay, so now we're going to switch into the 9.4 directory. This is the directory that we're in right here. So yeah, what are other people getting this to work? Yeah, okay. I'm afraid we're going to just have to push on because I don't have an assistant here to help people troubleshoot individual container problems. The so switch into the 9.4 directory here. And if you actually look at that I've set up so each one of these directories represents a Postgres home, what we call PG data. And so we're going to spin those up at one point or another as completely separate instances of Postgres with their own master process. Now, obviously, if you were really doing replication, these would be on separate machines. But it wasn't really practical to set that up for a demo for a training exercise. So we're all going to be running these inside the same virtual machine or container. So now the command that we use in order to do the clone is called PG base backup. So go ahead and take pull up the help for PG base backup, because it's got a lot of a lot of switches available to it. We're not going to be using all of these switches. But you can see what's going on there. A bunch of these are actually new for 9.4. So they if you go back to your installation, you're 9.3 or 9.2, you won't actually necessarily have all of these options. Things like the max rate option to bottle, to limit the amount of network bandwidth you use is new, as is the tablespace mapping command. We're not really going to be going over those. So you've got actually sort of your command here. We'll go over a few of these options as we do our cloning. So now we're going to actually use PG base backup. So dash x. And dash x says, also copy the transaction logs. Copy the transaction logs at the end of copying the files. There are other options, which we'll talk about later on. Dash p is just show me a progress meter. That's just for interactive. And then dash d is actually fairly well required because that is telling PG base backup where the data directory that you're copying into is located. And that is located in replica one, which is currently an empty directory. And so then you should get output that looks like that. That's a little progress meter. Of course, it takes no time at all because we've got a small installation and it's local. Copying a terabyte database across the network. It'll take a little bit longer. Show the source. Ah, yes. Because we were using implied stuff. That was what effectively what we did because we're just pulling from the default port on local host. Obviously, if we were pulling from another machine, you would have to actually give it a host location to pull from. And by the way, if you actually try to run this a second time, PG base backup will only pull into an empty directory. It is designed to fail if you attempt to copy it into a directory that already has stuff in it. We can't actually, because we're running multiple Postgreses on the same machine, we can't use the default configuration for this. So we're going to copy one that I have for the exercises. And we're just going to go ahead and copy that into the replica one directory right there. And so that's copying a Postgresql.conf and a recovery.conf and a bunch of other things that you'll see later. And then once you've copied that, you should be able to start it up. And if you look at the process list, there was a lot of wrapping there. Look at the process list. Hold on. Let's page that. We actually have two different Postmasters running on the same machine. And you actually see some of that other stuff I was talking about. Because we've got the replica right now telling us that it's actually slurping data from the master, which is where you see, again, recovery. It's recovering data. And then we have this wall receiver process that is streaming and that sort of thing. And now I'm going to actually start using port numbers because we now have two Postgreses running on here. So the first one's running in port 5432. And go ahead and connect to this test database, this demo database I have called libdata. If you look in libdata, we have a small set of tables. Quit out of that. We can connect to the replica at port 5433. And again, if you look, we've copied over all of our data from the master. Of course, that was actually kind of simple, partly because the master wasn't doing anything. It was completely a rest. So how about if we actually create some traffic on the master to make this a little bit more interesting and to actually verify that data does ship from master to replica? So first thing we're going to do is we're going to create a benchmark database. So create the bench database. And that command, and I'll actually go over it again once it's done because it kind of scrolled past pretty quickly. But that's the command right there. So pgbench is a microbenchmark that ships with PostgresQL. And it's useful for doing tests like this. Don't call it a real benchmark. It does a set of very simple queries, repeatedly. But it is useful for doing traffic tests and the like, which is what we're going to do here. And so what we just did in the master was we created a database, and then we populated that database with this fake minibenchmark data. And so that's pgbench-i for initialize, dash s for scale, dash u, capital U for what user we're doing this as, and then bench as the name of the target database. And that should go ahead and generate some data. And then we can see we have this benchmark database created. And if we connect to the replica on port 5433, we have the same thing. And that's actually one of the big advantages of binary replication over row-based replication, which is it really doesn't care what you do on the master up to and including creating and dropping databases, any of that stuff. It will faithfully replicate, which can actually be a drawback if you dropped a database and you didn't mean to. But it will go ahead and do that. And the nice thing about that is that it keeps your ops completely separate from your development. So there we are. We're replicating asynchronously and streaming. So let's talk a little bit. Now that we've got a replica up and running, let's talk a little bit about administering it. First of all, there's a couple of configuration files, postgresql.conf, which you'll be familiar with if you've run postgresql before it all, which is our general configuration file for configuring Postgres. And there's a bunch of settings in there, both for the master and for the replica in terms of its behavior. And then there's this special file called recovery.conf. There's the recovery word again. And recovery.conf is special because it has this weird hybrid role where it is both a trigger file that turns on replication and contains certain configuration options that can only show up in recovery.conf for replication. So if a recovery.conf file is present when you start up postgres, the postgres that you start up will attempt to go into replication or point in time recovery mode, depending on what options you've given it in the file. If one's not present, it will not attempt to replicate. And this file always needs to go in the actual data directory, even if your configuration is normally kept somewhere else. So like if we were doing this using standard Ubuntu configuration, the configuration or Debian configuration, the configuration being postgresql, et cetera. But recovery.conf needs to go into verlibdata 9.4 main or wherever your data actually lives. Sorry? The question was, can you link it? You can link it, but I actually recommend putting the actual file in the data directory and then linking it to somewhere else. Because one of the reasons it has to be in your data directory is that, for example, when you promote a replica to a master, postgres moves the file. We also have a set of views and functions in order to help you administer postgres. You can't obviously look at the process list like we just did. And that's a good method of troubleshooting when everything else has failed. But in general, you're going to be using some things. One of the things you use heavily is a view on the master called pgstatreplication. And pgstatreplication tells you the status of all of your connected replicas. pg is in recovery, which we'll also show you, which is a function you run on the replica. And a set of functions that check the status of the data stream that generally start with pgxlog. So let's actually do some of those. First, let's actually look at the configuration here. So open your favorite editor unless you're on Docker, and your favorite editor is VI, in which case it won't work. In that case, use nano. For Emacs users like me, I have a mini version of Emacs called jmax. Otherwise, like I said, nano's available. For anybody who just installed the image or who's on vagrant, VI is available, or VIM is available on Docker. Otherwise, sort of pick your poison. And we're just going to go ahead and look at some of the configuration here. So this is the configuration on the master. This is a simplified Postgres configuration without all of the access baggage that we put in the configuration file we ship with Postgres. I actually strongly recommend for people administering Postgres in production, get rid of that default Postgres.conf file that ships with Postgres, and put in a stripped down Postgres file that only has the options you're actually using, because that will save you a lot of scrolling every time you want to look at something. So most of this is your sort of basic Postgres configuration. I'm not going to go over that right now, because it's not related to replication. Instead, we're going to go over just the replication connections. So one of the things that you need to change right off when you are going to set up something as a replication that is not turned on by default for security reasons is, well actually for performance reasons, it's not turned on by default. There's this parameter called wall level, and wall level says, how much stuff am I writing to the transaction log? The default we ship with is minimal, as in, write the minimum to the transaction log necessary for crash recovery. But that doesn't contain some extra data that we need for replication for the replica to read. So in order for replication to work, wall level needs to be set to hot standby, or the next higher level, which is what I have set here, which is logical. And logical is strictly 9-4. So if you're using 9-3 or earlier, you want to set this to hot standby. The second thing that you have to do is, remember I said that the streaming is coming from this process called the wall sender, which is a process run by the master. It's actually several processes run by the master that form the end of a socket that we're sending data over. So this max wall senders right here is how many of those am I allowed to have? And by default, in most packaging, this is actually set to zero. The reason it is set to zero is for out-of-the-box security. Because if it's set higher than zero out-of-the-box and you have not configured your replication security, then somebody can use it as a vector to hack your master server. And next one is called wall keep segments. I will be going over that later in the presentation, hopefully. That has to do with telling the master to hold on to extra data that the replica might need. Max replication slots is part of the new 9.4 replication slot feature. We probably won't have time for that today. And then there's a couple of ones. The other one for the master is, wall sender timeout is simply, how often does the master ping the replica if it hasn't heard from it in a while? And we're not going to go over the rest of these right now. Those are the essential ones on the master for the initial configuration. Now, if you look at replica1's PostgreSQL.conf, you'll see, take a look at the second section of the configuration that's called the replica. So one of the first things that you'll see, actually, if you look at both of those files is, aside from the port number, we are using the same configuration file for the master and for the replica. That's one of the things that we did in PostgreSQL 9.1 is, if you are a master, the replica options will be ignored. And if you are a replica, the master options will be ignored. And that's a useful thing, because it means that you can actually put the same file in the configuration management system for all of your servers. There are a couple of things. So quirk number one, which I actually don't have on the screen right now, max connections. The replica and binary replication must have max connections set to the same number or higher as the master. The reason for that is, within the PostgreSQL system catalog, we use those connection slots to keep track of new data coming in from the master. And if you set the replica to a lower max connections, then it can't actually replicate all the data that the master is sending it and will fail. So some of the other things for this. So there's this parameter called hot standby. And hot standby is a Boolean parameter on or off. And it basically says, OK, I'm a replica. Am I taking requests? That is, am I allowing users to connect with me and run read-only queries? If hot standby is on, that's yes. If hot standby is off, that's no. One of the reasons why that might be no, for example, is if you have a replica that is a dedicated backup failover, and you don't want to ever run any load on it, that might cause it to fall behind the master. Better to just limit who can connect to it, because you do kind of want to run queries on that to make sure it's still responding. But you can turn it off. The second thing is there are a couple of timeouts. There are a couple of delays here. This has to do with what's called query cancel, which is if load on the replica is conflicting with replication, how long do I wait before I terminate that load on the replica so that I can catch up? And streaming delay refers to streaming replication. Archive delay refers to file-based replication. Hot standby feedback will hopefully have a chance to go over later. And then again, this is just a ping time. How often do I ping the master, if I haven't heard from it in a while? And then the last thing that we want to look at here is specialrecovery.conf file I talked about. So this is a separate configuration file right now, because we still haven't fixed this. So the formatting is a little bit different from the standard postgres.conf. That's something that's been our to-do list for a while, and will finally be fixed in 9.5. But for now, just keep in mind that you have to quote most parameters, which is not true in postgres.conf. So we've got a few things. Number one is we have the standby mode parameter. And the standby mode once again says, am I a replica? This actually could be called as a parameter, am I a replica? It's called standby mode largely for historical reasons. The reason why this would be off is that we use the same replication mechanisms to recover postgres from a binary backup. And so if you were recovering a binary backup and not running a replica, standby mode would be off. But any time you're doing replication standby mode is going to be on. The second one right here, primary con info, is the connection string in postgres connection string format to connect to the master in order to replicate. What host are we connecting to? What port are we connecting to? What user are we connecting as? And then there's this extra parameter called application name. This is optional, but I highly suggest that you set it. Because that application name is just a tag. It can be any arbitrary string. But that tag gets passed up to the master and is then visible in the administrative views on the master. And that really helps you distinguish one replica from another, as opposed to trying to track them by AP address, which is your other choice. Other things can be put in the connection info. For example, if you're using password, yes. The question was you have to have the role set up in the master, and the answer is yes. And I will be going over that. So you can put in other things in here, like you can put a password in the con info. I suggest that you don't do that. And I'll show you some other mechanisms for if you need password authentication. Because there are circumstances where that connection string is visible to unprivileged users. And so putting a password in there is a bad idea. And you can put other things. This supports SSL. So if you have installed Postgres with SSL support, you can tell it to use an SSL connection for replication and similar things. One of these other things here, and I'll mention this now because we're not going to get to the section of the tutorial that has to do with remastering. And remastering is when server one was the master and server two is now the master, and you want all of the remaining replicas to connect and read from server two. You need this cryptic parameter called recovery target timeline equals latest. And that is a cryptic way of saying if the replication stream changes, follow it. Because if you don't have that in there, the moment that the replica notices that it's getting a different replication stream than what it was getting before, it will stop replicating. So just set that. And so that is our recovery.conf. We will be adding and changing things in recovery.conf as we go along. And so you'll get more familiar with it. OK. So now, again, do the Tmux switch thing, Control-B, and switch back to the root shell. I said we were going to create some load to actually make this more interesting. So get into the setup pgbench directory where I've set up some fake benchmarks that generate a little bit of traffic. And first of this is just the script runbench.sh. And so that will start up. And then Control-B and switch back to the Postgres shell. And we're going to switch back to the Postgres shell and log into the bench database on the master. So port 5, 4, 3, 2 database bench. So we're going to look at the pgbench history. The pgbench history table is an audit table for this microbench mark. So it's continuously getting new records as the microbench mark runs. If you run this several times, you can see that that's increasing. Now, let's actually take a look at some of the other stuff. So pgstatactivity is your view in Postgres to what are all of my concurrent connections on the server, what's going on on the server? So we can look in pgstatactivity. Oh, that backslash-sh switch is to switch from landscape to portrait mode for output, because this is a very wide system view. And otherwise, it wraps around a lot. So this shows that we've got a couple of connections going on for this benchmark that are running queries on the benchmark. Now, I said that one of your main ways of administering replication is this pgstatreplication view. So if you look here for pgstatreplication, there's exactly one row, because we have one replica connected. We've got some not as useful information on what's the process ID can be useful. So we've got a process ID. We've got some Postgres internal information on this. We've got what user name is it connecting as, that application name. See, this is where you should put in, even though it's an optional parameter, you should put in that application name, because it's visible here in pgstat replication and makes it really easy and fast to identify which replica is which. Then client address and host name, which aren't populated here because we're connecting over local host. The port number that was assigned, which you don't really care about, back end start is when it actually connected. And then we have a bunch of status information. And this is actually where it's much more useful for monitoring. So that state that we're in is streaming, which means that we are actively streaming replication. There are a few different states. The main ones that you will see are streaming. And the thing is, when we've connected to the master, but we haven't yet caught up to the point where the replica is streaming new data. And that'll generally happen right after you've cloned a new replica and you've started it up, but it hasn't gone live yet. If you have a very large, very high traffic database, it'll actually be in that state for a while, because it'll take it a while to catch up. Then we've got all of these locations. And those hexadecimal strings represent positions in the transaction log for Postgres. And those tell us what is going on in terms of sending data to the replica. There's a four different locations. Scent is what data do we last send to the replica. Right is what data did the replica last accept. Flush is what data did the replica last write to its own transaction log. And replay is what data did the replica last apply to its version of the tables in memory. So the one that you actually care about the most here is actually replay, because replay shows you your level of consistency between the master and the replica. And you'll notice, by the way, that we are caught up on scent, write, and flush, but it's replay that's behind. And that's usually the case. I mean, in this case, it's only about 16 bytes behind. And I'll show you some functions to actually make use of these cryptic numbers, which otherwise are not very human readable. There's a couple of other columns in here. Sync priority and sync state refer to doing synchronous replication. If we were doing synchronous replication rather than asynchronous replication, then this replica would have a sync priority, which said where it was in the preference for synchronous replicas and whether or not it was currently synced. In this case, you see the sync state is async, indicating that we are doing asynchronous replication. So one of the problems with those locations is that they are hexadecimal strings that are interpreted according to a bunch of math about how we lay out our transaction log files, which is not very helpful in determining how far is this replica actually behind. So what you want to do is you want to actually use some of our built-in functions. Well, there's this function called pgxlog location diff that will take any of those two of those location strings and give you the number of bytes that are different. So for example, if we want to see how much lag there is in applying new data on the replica, we could compare the right location with the replay location. And here we get 544 bytes of difference, which is a very small lag. In real production monitoring, I would recommend actually using standard divide by 1024 to roll this up into k or megabytes. That tells you, in terms of volume of data, how far you are behind. Let me actually look at this a couple of times. And you'll see that this goes up and down as new data gets written and as the replica catches up from new data being written. In regards to monitoring, at what value would you alert in a production database? The question was, at what value would you alert in a production database? And that actually comes strictly down to an operational decision of how far behind your replicas can get before it's a problem. And part of that depends on how much traffic you're doing. Because in a database like this, where we're only writing a few bytes a minute, if we were to fall a megabyte behind, that would be an indication of a major problem. In a database where you're doing 100,000 transactions per minute, being a megabyte behind would be doing very well. So a few other, so let's actually look at what we can do on the replica, because that's looking at stuff from the master. And the master is the only place we can get a view of all of the replicas at once. The replicas only know about themselves. But let's go ahead and connect to the replica. And you can see from PG Manch history here that we are receiving data that is streaming along. Now, one of the first things you want to do with the replica, if you're doing monitoring, is to connect to it as a database server and say, hey, am I a replica or not? And the am I a replica or not is actually a function called PG is in recovery. So on a replica, PG is in recovery will return true. On a master or standalone, it will return false. So for example, that's one of the things you can do in your monitoring is, if according to your configuration management system, server three is supposed to be a replica, and PG is in recovery is returning false, then something is wrong. So does this count 2012? I'm counting the number of rows in the audit table, because we're still running a benchmark in the background. And I'm just showing that data is actually flowing from the master to the replica. So let's actually take a look at some of the other stuff we can look at on the replica. So PG Last X-Log receive location is a function that you can run on the replica to say, what data did I last get? When did I last get? What data did I last get from the master? Now obviously, if you're having a network lag problem, receive location will be substantially behind sent location on the master. That's a little hard to measure in monitoring because you're connecting to two different servers, and so there's no way for you to actually check that simultaneously on the master and the replica. So if you set up monitoring to look for network lag by comparing sent location and receive location, keep in mind that the process of monitoring is itself laggy. But this tells us what data did we last receive. And then there's a second one. And that says, what data did we last apply? This is our point at which the data becomes merged into the version of the database we have in memory on the replica. And that means that we can actually compare those two on the replica to check on the replica how far we are behind. Right now this replica is keeping up pretty well. OK, either that or benchmark ended. Now, one of the things that people look at this, they say, OK, that tells me what volume of data I am behind. But what I really want to know for a signature application is how far I'm behind in time. Because generally, if you have a spec that says the replicas must be a certain amount consistent, that's expressed in time. The replica must be consistent within one minute. So there's a little bit of a problem with that, which is that Postgres' transaction log does not contain a constant stream of time stamps. In fact, the only time stamp that's coming across is every time a transaction commits, you get a time stamp with that. Now, in a database where you're mostly doing single row writes, that's a pretty constant stream of time stamps. But in a database where you do big batch operations, the commit might come minutes later. So but that's the only time stamp that we have, and so that's the one that we actually look at. So there's that function. And that gives you the time stamp of the last commit to come down the replication stream. Yes? Does server locale and date time matters here between master and replica? It'll use whatever your time zone is set in the client. So whatever you've set time zone to in the client, that's what it'll use. Yes? It's sending you an absolute time stamp and converting it to whatever time zone it thinks you want to have. In this case, I've set up the Docker image to use UTC, which means that that's what it's giving me. And that means that, of course, we can create a delay out of that by just subtracting it from the current time stamp. So for example, right there, that tells me that I am about half a second behind, 542 milliseconds behind in terms of commit time stamps. Now again, if you're using that for monitoring, like I said, keep in mind that these are commit time stamps, and so there will be a certain amount of stuttering. So if you're going to have this alert, it should be alerting at some whole number of several seconds. Do not set this to alert if you fall more than 10 seconds behind, because you will be getting a lot of false alerts. Where the replica isn't actually behind, you just had a brief pause in the amount of traffic on the master. Now, we added this function in Postgres 9.3 to allow you to dynamically stop replication without taking the replica down. And this is called pgxlogreplaypause. And so when you run this, what it does is the replica is still receiving stuff from the master, but it's not applying any of it. Wait, sorry? These are case insensitive. So sorry about the capital. It's case insensitive, though. And now if we actually do that location diff query again, you see that we're pretty significantly behind at this point, or 700k behind. And for that matter, if we do this, we see that we're 34 seconds behind. Yeah, so the replica is still receiving data, and it's putting it in a buffer, but it is not saving it. When it fills up its buffer, it will stop receiving it, which could be bad if you don't have a buffer in the master. This is something you generally do in order to fix something on the replica. And if we want to actually start it up again, just pgxlogreplay resume, and then we will resume replicating. So I did say I will go over security, and I will. So there are several layers of security that apply to replication in Postgres. One is that the user connecting to Postgres needs to have the special replication permission in order to read a replication stream. The second is Postgres' own host-based access file, pghbe.conf that defines who can connect to Postgres and how. The third part is whether you have max wall senders greater than zero, and whether you have enough for how many people are connecting. And then of course the last is that you can use network and routing and firewall configurations on the host level to secure replication. And it is really important to secure replication, because if somebody can get a validated replication connection, they have a copy of your entire database, including, by the way, passwords. So you really don't want somebody illegitimate to get, I mean, we hash the passwords inside Postgres, but if somebody has a copy of the database and lots of time, they can crack that hash. I'll tell you. The assumption is if somebody gets that file and has an unlimited amount of time, they're going to break it. So you do want to secure replication, and for that matter, Postgres ships with replication locked down out of the box, which is unfortunate for people who are just getting started, because it means they try to start up a replica and they can't do anything. But we couldn't see another way to do it, because the danger of having an unsecured replication connection is too bad. So you actually do have to change some of those things when you get set up. So now, in the image, of course, I already changed permissions so that we could have a replica. But when you're setting up replication in the Postgres server for the first time, you will have to change permissions yourself. So let's switch terminals within Tmux, control BN. So we're going to actually go over some of the security. So switch users back to the root user, control BN, and hit control C to stop the benchmark from running. And now we will switch back to the Postgres user, and we're going to shut down the replica. PG control dash D, capital D, replica one stop. So we've just shut down the replica. And we're going to undo our previous work, and we're going to wipe out that whole directory. RMRF replica one slash star, make sure that you have replica one slash star and not just star. Now, I just realized that actually I jumped ahead in the exercises because I got distracted. I'm sorry, we're going to need to set up replication again because that was actually getting ahead in the exercises. So we're actually going to, if anybody has not already done the RMRF star, then don't do it. For those of you who did do it, we're going to restore the replica. So do PG base backup dash X, dash P, dash D, replica one, right there. And then copy the configuration. Go ahead and bring it up. OK, sorry about that. A little bit extra tutorial training here. So for those of you who are looking at the exercises worksheet, I just, because I accidentally jumped ahead to the next exercise because of the interruption, I just redid the initial replica setup, right where it says basic two node replication. In order to get replica one working again. So we're going to now actually go through some of the security setup here. So the first thing that I said was that we need a replica permission. Now one of the things that I actually, the one user out of the box in Postgres, database user, that has the replica permission automatically is the Postgres user, the super user that owns the installation. However, in a secure setup, I actually recommend creating and using a different dedicated replication user. Because among other things, if you actually have to look at security on the network or process level, it's very helpful to have that second user so you can identify which one is the replication connection. And it means that if you have to change the password for the Postgres user because it's been compromised, you don't have to change it for the replication user. So let's connect to the master. And we're going to create a dedicated replication user. So we're creating a role named a replicator with the password replicate. Please do use that because I've set it up in some of the other configuration files. And some of the rest of the tutorial, you'll have to fix things if you choose a different password. Create a role replicator, password replicate. So I'm going to write a password because we use password authentication here. And then I need to give it two permissions. I need to give it the login permission so it can connect to begin with. And I need to give it the replication permission so that it can replicate. And if you actually look at the user's view, backslash du, you can actually see right there. We've got our users. And we have the PostgreSQL user, of course, has all permissions in the world. And the replicator has just the replication permission. So quit out of that. And then again, user, we're going to get into the host based access file for PostgreSQL. So editor master slash pghba.conf. And we're going to actually change this. So initially, when we actually set this up, I just set it up so that we could have trust authentication, so passwordless authentication, with the PostgreSQL user to do replication so that we could get a quick start here. But now we're going to set up with a more secure configuration where only this dedicated replicator user can connect. And it has to use a password. So comment out those three lines. So the PostgreSQL user will no longer be able to connect for replication purposes. So we're going to comment out those three lines. And when we comment out those three lines with the host based access file, it keeps looking down until it finds a matching line, and then applies the rule in that line, it's like IP tables. So by commenting out those three lines, the next set are going to take effect. And we've now said that the replicator user has replication permission. And you can see here we are using hash passwords as authentication. So go ahead and save that. Now, we've just changed the user and the authentication method, which means we're going to need to tell the replica to connect in a different way. So let's go ahead and do that. So here we are in the recovery.cont file in replica one. So the first thing we need to change here is that user. So that needs to change right there in the primary con info parameter. So change it to user right here, replicator. And go ahead and save. One of the problems that came up, I said you could put the password in that primary con info parameter. But I really don't recommend that because depending on how processes are set up in your system, that primary con info string may be visible in the process list. In the future, in Postgres 9.5, we're going to make it visible in a system view so that you can actually check the connection string. But then that means that people can get the password. Yes? Oh, doing standard joe, hit Control C. And you should be able to say yes to save it. No? Lose changes because now we go back to it. Oh. I don't remember how to use the standard joe commands. Exit and do it in nano instead. Oh, so you didn't update this morning. Anybody here a regular joe user? OK. So anyway, so we can set that. So I recommend instead using another method of getting passwordless authentication. Now, Postgres does support more sophisticated options like SSL certificate authentication or GSS API or Kerberos or whatever. We're not going to do any of those right now. But what we're going to do is actually what's called the pgpass file. So copy this pgpass file into your home directory. It's in Setup Postgres pgpass. Whoa, that was my fault. It's in Setup Postgres pgpass and copy it into the home directory. So the pgpass is a .file. We need to change the permissions on it because, like other things that contain passwords, Postgres will refuse to use it if it is set to liberally. So I changed it to 700. And now let's actually take a look at it. So the pgpass file is just a file of users and passwords. There's five fields in it separated by colons. Host, port, database, user, password. This does have your passwords in plain text, which is the reason for wanting to keep it in a .file in your home directory with restrictive permissions. And if you are on some kind of hosting where that is not good enough to prevent people from accessing it, then you maybe don't want to use this method. Also, think about your pgpass files when you're actually doing backups. Like if your backups are unsecured, you probably want to exclude the pgpass file from your backups. But right here, this allows the replicator to connect to the master using a password non-interactively. And so that's why we're setting this up. So now we need to tell both servers to look at the changes. So we're going to tell the master reload, which among other things will have it reread its host-based access file. Now one of the things about replication currently, that I say currently because we're planning to change this in 9.5, is that right now you can't change any of the parameters in the recovery .conf file without restarting the replica. That is, the replica reads those parameters only on startup. And so you need to actually do a full restart of the replica, which will kick any people who are connected to the replica off in order to do a parameter change. So that's what we're going to do. And if you look at the process list, we have that one replicator connection right there. If you connect to the master, you can see that our replication connection is now has the replicator user. OK. Let's talk about a couple of other issues that come up a lot of the time. Postgres supports a robust extension system that includes things like PostGIS, and the ISBN data type, and a whole bunch of other extra functionality people like to load into Postgres. It's important to understand that the replication stream does not include the libraries that those extensions rely on. So the way that you install an extension when Postgres is being replicated is you install the libraries, the SO files that come in the packages, on each server, on the master and the replica, et cetera, using whatever installation management system you have. And then you install the extension into the database on the master. And you need to make sure that the libraries are installed in the replica, because if you install the extension in the master and the replica does not have those libraries, the first time it encounters the use of the extension, it will stop replication. So actually, I'll do this for PostGIS. So a couple of other things. So one of the other limitations to binary replication is that all replicas need to be running the same version of Postgres QL. That is, we cannot have Postgres QL 9.3 replicating to Postgres QL 9.4 or vice versa. Now, this doesn't in general apply to minor updates. So for example, this is 9.4.0, the 9.4.1 update will come out probably in a couple of weeks. In general, replicating from 9.4.0 to 9.4.1 or back is fine. There are exceptions to that in some of the 9.3, some of the older updates. Like if you haven't patched your Postgres in a while, 9.3.2 and 9.3.3 contain fixes to the replication system itself. And so I don't recommend replicating from 9.3.2 to 9.3.3 for any length of time because having the fix on one server and not on the other server is a problem. But so in general, in a replicated cluster, there's no current way within Postgres to do a rolling major version upgrade. Now, I didn't mention before that one of the other limitations of binary replication is that there's some stuff that does not get replicated. Number one, unlogged tables. In 9.1, we introduced this feature that said that you could have tables that did less IO to make them faster, which were called unlogged tables. Well, the thing is we replicate via the transaction log. So if you have an unlogged table on the master, it will be empty on the replica. It will not only be empty, it will give you this warning every time you try to query it because it'll say, you know, this is going to be empty on the replica. So anything you've got an unlogged table on the master is accessible only on the master. Temporary tables in Postgres are per session. And so once again, a temporary table, you can't create temporary tables in the replica. And a temporary table created in the master is not visible on any replica. And we still haven't figured out how to make Listen Notify work across replication. It's been on the to-do list for a while, but currently it doesn't work. Yeah? Listen Notify. Yes. So Listen Notify is an internal messaging system within Postgres that allows you to send a notification that other database clients can be listening for. It's really useful for things where you've got a bunch of asynchronous clients that you want to let them know that there's new data available. And we would like it that you can put the notification on the master and listen for it on the replicas. But we haven't figured out a way to make that work right now. So right now Listen Notify works only on the master. So let's talk a little bit about cloning in the remaining time that we have and do some cloning exercises. So now I mentioned, so we actually did this through PGBase backup, but let's get a little more in-depth with it in case any of you have configurations or workloads that require using a more sophisticated approach. So here's the requirements for cloning a master in order to turn it into a replica. One is you can figure out some way to actually get a genuine point-in-time snapshot of the database. Or you can do what PGBase backup does under the hood, which is to copy all database files plus all transaction logs between the beginning and the end of copy. Now the simplest way to do this is what we call downtime cloning. This requires no special tools. Shut down the master, copy all the files to the replica, add a recovery.conf, and start them both back up. That actually works. And there are circumstances where that is, in fact, the most sensible route. Particularly if you had to shut down the master for some maintenance-related reason. A second one is if you are using ZFS or LVM or sophisticated sands or Butter FS or some other form of storage that can take a genuine point-in-time snapshot of a set of files at a single moment, single microsecond in time, then that is a perfectly legitimate way to take a copy of a replica. Like you export a ZFS snapshot to another machine. That will work as a way to jump-start a replica. And if you happen to have a centralized storage-based architecture with snapshot-capable storage, that can actually be your best way of taking a replica. The mainstream way to do this is obviously the PgBase backup utility. It's a command-line tool for cloning. It copies over 5, 4, 3, 2, so no special SSH or R-sync or whatever permission is required. It also, depending on the switch you pass it, copies the required logs. It does require you to have set up streaming replication, so that replication permission, et cetera. And it actually requires two replication connections, because it uses one to copy the database files and one to copy the transaction logs. Now it does have some drawbacks, which is that it doesn't compress anything. So it's just doing a raw copy of all of the files. And the second one is it can't do any kind of incremental updating the way that you can with something like, say, R-sync. That is, it's going to do an entire, you have to clear out the directory, so it's going to do an entire copy of every single file. Which if you have a two-terabyte database, and you need to start, you know, you need to re-sync a replica that is only a few hundred megabytes behind, is kind of network-intensive. Those are ways to create snapshots. Now, one of the reasons why I'm telling you about these different ways to create snapshots is because we're going to get into different forms, different ways of doing asynchronous replication, particularly archiving replication. So archiving replication refers to replicating Postgres via archive files of data changes rather than via a stream. And so the general way to jumpstart archiving replication is you set up all of the archiving machinery. You set up all the archiving settings and configuration, we'll go over that. You tell Postgres that you're taking a backup. You copy all the files. You tell Postgres you're finished taking a backup. And then you bring up the replica. We'll do that interactively. Now, there's a number of reasons why you would do this. Number one, if you have a really large database, and say the replica has been offline for 20 hours and it can no longer catch up with the master. And so you want to re-sync it, but you don't want to do a full copy of the entire data directory because there aren't that many changes. This happens a lot with data warehousing. So you want to use R-sync so you can do an incremental update or some other tool to do an incremental update. So that's one reason to do it. Second reason to do it is you may already have a point-in-time recovery-based backup system, such as Wally or Barman or some other tool that does continuous backup for Postgres. And you would like to use that continuous backup to also jumpstart your replicas. Again, this only really makes sense if you have a large database, but if you do have a large database, it makes a lot of sense. Another reason you might want to do this is that imagine your master is located here on the ground and your replica is located in the satellite, and it only connects, it's only able to connect with the master for three hours out of the day. This is, believe it or not, a real use case from NASA. And so in that case, you would much rather ship files, particularly ship files that you can triple check for a bit flipping than to have a streaming connection. Also, if you are using Postgres 9.2 or earlier, remastering does not, that is switching masters for a live replica, does not work unless you are also doing archiving. What is your doing? Archiving. My real answer is if you think you're going to need remastering, please upgrade to 9.3 or 9.4, and your life gets much easier. But sometimes you can't do that. So let's see, we still got 10 minutes before the Q&A. So let's do a little hands-on exercise with archiving. So I think we already killed PG Bench. So let's go ahead and stop the replica. And now this is where I jumped ahead to earlier. Now we really are going to blow away the replica. Blow away the replica. We now need to make some changes in the master configuration to turn archiving on. So here's the changes we need to do. First of all, there's this setting called archive mode. And archive mode is either on or off. If it's on, you are theoretically doing archiving. Now, archive mode can only be changed with a restart. So what I recommend is when you configure a new master, if you think you might need to use archiving in the future, turn archive mode on and set archive command to something like bin true that always returns true. Because you can change archive command at runtime. And if archive mode is always on, you can avoid needing to schedule a downtime for your master, which is nice. But in this case, go ahead and uncomment this. We are setting archive command to a shell script that I have on the server that archives files. So set up postgresarchivelogs.sh. And the way this works here is you have these special placeholders that you can pass to the command. And percentage P is the full path to the transaction. By the way, so what we are archiving is transaction log files. And so percentage P is the full path to the transaction log file. Percentage F is just the file name itself. The percentage P does include the file name. We're passing those things separately so that we don't have to parse out the full path to figure out the naked file name, to figure out the plain file name. And those are generally the two things that you need in order to populate whatever script you have. Another way to uncomment here is archive timeout. So normally, archive logs get shipped when you fill up a log, which is 16 megabytes. Now, on a high traffic database, you're going to be doing several of those logs per minute. So that's not really an issue. On a low traffic database, it might, or a very small database, it might take you a while up 16 megabytes. So we have a timeout there that you can set according to whatever your requirements are, which will zero fill a log and send it over, even though it's not full yet, when the timeout counter counts down. So we've gone ahead and modified that. And now we have to restart the master because we turned archive mode on. What happened there? That's very interesting. Hold on. OK, I'm not sure what's going on there. Yeah, but I don't know where it's getting that string from. So just stop it and start it, because start works normally. So I'm not quite sure where it's getting that string from, and I don't want to troubleshoot it up here in the podium. But if you just go ahead and do stop, and then start, that'll work. So we've now turned on archiving, and Postgres has started up a new process called the archiver process that is in charge of shipping these archive logs. So switch terminals again. We're going to start back up the benchmarks so that we actually have some traffic, and we have something to archive. Switch back to Postgres. Connect to the master. We can't connect to the replica right now, because the replica is gone. And we're going to tell it we're starting a backup. Now PD Start Backup requires you to give it a label, which is used in the file name of the backup bookmark file. This label is completely arbitrary. It is for your own use. It can be any string you want it to be. And if you've forgotten whether or not you started a backup, you can use this function pgisinbackup, which will tell you whether or not you've started to do a backup. That's actually kind of important, because since this is related to the transaction log, which is monolithic, you can't actually have two different backup sessions running at the same time. So if you script this automatically, one of the most common reason for these scripts to fail is that you actually tried to start the backup a second time, and the second time will fail. So let's exit out of here. Now in a real production thing, we would be sending these archive files to the replica, or we might be sending them to a cloud storage server or somewhere else that's reasonably accessible. On this demo, of course, we're sending them to a local directory, a wall archive in the home directory. Not seeing anything yet. OK. OK, so bonus material. One of the other things that happens with archiving a lot is that archiving doesn't work. And the way that you actually check this is by going into Postgres' activity log, which is wherever you've configured it to be, often somewhere in Verilog. So in this case, it's in Verilog Postgres PostgreSQL master. And so here it's telling us that archiving logs is failing. And the reason why archive logs is failing is because, again, this is my first time doing this on Docker. And apparently, R-sync did not install when I did my last Docker build. What? Vagrant will work because R-sync is there. On the Docker version, this is going to halt because R-sync is not there. And since we only have one minute left before the 10 minutes of Q&A, I'm not going to actually try to solve this on Vagrant on Docker, particularly because I am currently having a problem with Docker and networking. So I know I can't solve this in my particular Docker. If you install R-sync, for any of you in Docker, if you go ahead and do apket R-sync as root, it should apket install R-sync as root. It should start working. Oh, that's true. Oh, whoops. Don't have permissions. OK, we're running out of time, so I'm actually not going to trouble shoot this for you. So this ends at three, correct? So that's all we have time for in terms of interactive demos today, interactive hands-on today. There are additional exercises in here. And I will upload yet another updated version to the Docker image that actually has R-sync present. If you want to start over, otherwise you can, as suggested, switch to using CP. And there's additional exercises for failover, doing dual replication, failover and failback, and configuring query lag. A few other last notes for archiving. And that's all we have for today, except questions. So questions. Yeah. So the question is, how much slower is DRBD than Postgres native replication? And the answer is three to four times slower. Here's the reason. Postgres does a lot of writing that is not necessary for preserving your data. As a result, because DRBD is doing replication at the file system block level, it has to replicate all of that extra traffic. For example, if you create a temporary table in the master, and the temporary table is bigger than your configured temporary table limit, Postgres will write that temporary table to disk. DRBD will have to replicate that temporary table to the DRBD replica, even though it is not readable on the other server. The other big problem with doing DRBD is that you can only have a warm standby. You cannot query in a read-only fashion that other server, because the replica does need its own file space that it can write to for a bunch of extra temporary information and that sort of thing. And particularly, it needs its own transaction log directory. Yes. The question was, we recommended turning journaling off, since Postgres has its own journal. And my answer is no, because Postgres' journal will not allow you to recover from some failures that the file system journal can recover from. As in, if your file system ends up corrupted because you're not journaling, then Postgres will not be able to recover that. The only circumstance where I could see doing that is if you were running an ephemeral replica. That is, one of the things that people do on Amazon a lot is you run replicas where, if it shuts down, you don't restart it. You just blow it away and spin up a new instance. In that case, I would turn journaling off, as well as several other things. So having sort of mastered running ephemeral in the cloud, is that sort of the scenario that you would use? Yeah, so the question was, with the 9.5's bi-directional replication, does it make more sense, does that contribute to ephemeral? I think it's actually kind of orthogonal, because ephemeral replicas make perfect sense with just asynchronous streaming replication. I wouldn't recommend using synchronous replication with an ephemeral replica, because what's the point? Synchronous replication is ordered to protect you against data loss. And if it's an ephemeral replica, then you're not protected against data loss. Yeah. Yeah. And so that would be an example. It's the same idea. Stateless containers is the same idea with that. So let's take another question. We had one. I can't recall exact error, but sometime when you have fairly large database in the hot stand by, and you're trying to take SQL dump from a hot stand by, after sometime it fails with something like data expired, data is not here anymore. Yes. And that's called query cancel. And that's one of the sort of configurable things. So one of the configurable things within replication, which is there are certain operations that the master has to do some of the time. Usually garbage collection, which we call vacuum, which can interfere with queries running on the replica. And as an administrator, the decision that you have to make on that is, do you want to pause replication, or do you want to cancel the query on the replica? And that is an operational decision, right? If failover is more important than you want to cancel the query. If completing the query on the replica is more important than you don't want to cancel the query. OK, I don't have a slide for that right here. Then you don't want to cancel the query. But that's the message that you get when Postgres has decided, hey, we need to do a file cleanup vacuum. The query is running on the replica that is looking at the table that we are in the process of cleaning up. And therefore, we're going to cancel that query. If you don't want those cancels to happen, then there are a number of settings that you can change to make that less likely. And if you actually look at the second version of this presentation in the GitHub repository, if you go to the GitHub repository right now, and there's a second version of the slides called PGReplicationTutorial94+, that actually has more detailed information about the different settings that you use to configure that cancel activity. Oh, wait, he's got the mic. He's got the mic. About 9.1 replication, the slaves out of sync, but I don't want to bring the master down. What can I do? OK. So the slave is out of sync to the point where it's telling you it can't catch up, correct? Yeah, yeah, that's right. Well, if you're already in that situation, the answer would be to just run another PG base backup and overwrite the replica and bring that up. OK, and I can do that without disturbing the access to the master. Correct. These are all hot copies. The master is running and processing traffic. The only thing I will say is that taking a copy of the master is running a PG base backup that is a significant IO hit on the master if you have a large database. So it doesn't require taking the master down, but it may interfere with concurrent traffic from a performance perspective. So you might want to wait until whatever your low traffic time is to do it. Yeah, got it. Thanks. Hi, Josh. I have two questions. The first one is, are there still any issues with replicating hash indexes? Yes, they still don't work. Well, they still don't work reliably. Here's the problem with PostgreSQL's hash indexes. No one has ever fixed issues with logging the hash indexes to the transaction log. Because they are not properly logged to the transaction log, a lot of things can happen with your hash indexes that can cause them to be effectively useless on the replica. So that is still a problem. And part of the reason why nobody's fixed this is as of PostgreSQL's 9.4, GIN indexes, which are another type of index, which you can actually implement the hash index on top of and people have and contribute there's something called GIN hash, are faster than the hash indexes. So there's even less incentive for us to fix them. But I'll tell you, those are, in fact, still broken as a concept. All right, I'll have to make a upstream thing. The second question, I'm sorry if you answered the start, I was a little bit late. Can you just give a brief synopsis, I suppose, of, I'm familiar with PG Barman versus the streaming rep and just anything that you can, PostgreSQL's backup and recovery manager? OK, so those, they're not PG, Barman and streaming replication are not alternatives to each other. They're complementary. Barman is for automating and managing continuous backup. If you really care about your data, you're going to be doing both. You're going to be doing continuous backup and replication, right? Replication for fast failover, continuous backup for recovering from things like administrator error and being hacked and software bugs. Looking at, this is a question for sort of small business-type implementations. If you're wanting to do off-site replication, is using PostgreSQL's inbuilt replication a bad idea because of the speed you typically get over a internet connection? Well, if you're doing off-site replication, it really depends on what your, I mean, just compare your data volume with your network bandwidth. I mean, I will tell you that off-site replication over cheap networks is a big reason to do the file-based replication I started to demonstrate. Because then you have a lot of options for how you're going to ship those files. In an extreme example, the Music Brains Foundation for the Music Brains software actually has people download daily packages of replication files from FTP as an example of extremely slow replication. I think we're out of time, yes? OK, thank you very much. I'll be happy to answer questions, troubleshoot additional exercises after the session outside, because someone else will be in this room soon. Thank you very much. Thank you very much, sir. Thank you. The next talk will be on 340. So hope you'll enjoy the talk. Thank you so much.