 Hello, everyone. I hope everyone will have a pleasant time in training. As Lindsay has already made my inspection, my name is Asif Reshman. Currently, I am working with hypo software. I have been involved with Postgres for almost 10 years now. Previously, I was working with Enterprise DB where I had added Oracle compatibility there on top of Postgres. I work on implementing multiple features there related to composite types, procedural languages, developing some backup solutions. So, the replication in Postgres. As the topic of this training is, we will be talking about three replication options available for creating a replication setup. And we will implement some of those and see some in action. So, without further ado, let's start our session. So, this is the overall session outline. These are some things that we are going to discuss in this session. We will go through some terminologies used in the replication world. A brief history of replication to give you guys some idea of how it came into the Postgres SQL. We will be doing this setup for experimentation. So, I hope you guys are familiar with Dockers. So, let's start with Agenda. Basically, this training is divided into three sections. In the first part, we will be discussing the replication concepts that include replication overview, replication techniques, various strategies, and replication such as training replication, logical synchronous cascading replication, things like that. After that, we will move on to discussing about how to configure them. We will do that in an interactive session. And then, finally, we will have a two-end session. So, what is replication? In simple terms, replication refers to duplication of data, making it redundant so that it can be utilized in various scenarios where original data became unavailable or unreachable for any kind of, any number of reasons. This can happen in various ways. Your dismay cache, hardware failure, your site where you had set up your servers can fail entirely, or simply because of our human error to just name a few ways which can happen. So, there is an obvious need to ensure and protect the data from such failures. Replication is the natural way to protect data against any such disastrous scenarios. So, that's a general description of replication. So, what's replication when database systems are involved? We know that basically the same concepts apply to the database systems that we just discussed. We copy data to create redundancy. Redundancy can be created on the same server where your primary server is located, or it can be a remote site connected through some kind of internet, some kind of network. So, to create this redundancy or say the replication environment, what you need is to copy the data from main server that is storing all the data. Let's call it the master server or just the master. Over to another server that will house a copy of master's data. Let's call that standby server or simply standby. During the course of this training, I will be using the terminologies of master and primary interchangeably. They mean same thing. Similarly, for standby, a couple of other terms are used. Replica is one of them. Replica or slave, they all represent the same meaning. So, we need to copy data from the main server and copy it to the standby. The process seems very simple. You have to copy data, but how about while you are copying the data, that exact data at that point is being updated. For example, I change the value of a variable or a column from 0 to 1. What will you be copying? Will you have, after your copy is finished, will you have 0 or 1? Which is the correct version, even more so. Consider that when I made the change and then I refer to it, rolled back. So, what will happen during that time? Which data is the correct one? Consider that there is a lot of other data that depends on the value of this column. An application needs to make the cn that if the value is 1, it will execute some kind of command. Otherwise, it will not do so. So, things are very complicated when we talk about replication in database system. Database is simply copying the file may result in corrupted data or perhaps even worse, lead to an unexpected output that may cause mayhem. So, the replication is not just a straightforward process with database system. While copying, we have been sure that in a particular point in time, all the data is same between master and standby. We will see how we accomplish that later in this session. But the concept is simple enough that standby have to maintain a copy of primary or main server's data. So, let's have a quick walkthrough of how the replication was added to the Postgres server. This is just to give you guys an overview of how replication changed and improved over the years. And also, if you have worked with some older versions of the Postgres, then you may know some of the differences. It all started with addition of wall files in Postgres 7.1 version. It could not be called replication feature at that point because it became the foundation for the replication later on. In 7.1, walls could not be used for this purpose. In true sense, it was only one that pointed a proper way to replicate objects in Postgres at first. This only one was trigger-based replication system. Much after that, in Postgres version 8, point in time recovery became part of Postgres, which added the ability to archive the wall files. By the way, we will see wall files in more detail later on. Point in time recovery added the ability to archive the wall files and later on replay them on another server of the same version. So people started using this method to create their own stand-byes. However, those stand-byes could not be used to curate data. They were basically the warm stand-byes. After that came along the streaming replication. It was based on the wall shipping method. From there, this feature kept enhancing, such as the later on. The streaming replication was added, timeline management, and so on. One can say that streaming replication was the first proper replication system in Postgres. And now we also have the logical replication as well. That was introduced in version 10. Let's talk about Docker setup that we will be using for this training. The Docker setup that I am going to use will consist of three containers. Each of the containers will represent either master or stand-by or another stand-by. Basically, in this cluster, we will have one main server and two stand-byes. We will not necessarily be using all the three containers. That will depend on which technique we are going to use at that time. Please clone this repository. If you have not already, please clone this repository. It will contain Docker images that we will be using for our interactive session. I hope everyone has the Docker setup installed. Just give you a little bit of information about the Docker setup. When you clone this repository, there will be a couple of sub-directories. One of the sub-directories is the same example. In this, you will see other sub-directories called R5, streaming, streaming things, cascading. These sub-directories have been already configured. They are for you guys to experiment with. There is a sub-directories named Docker. We will be using that setup for our interactive session. In the next couple of slides, we will cover two things. The terminology is used in the replication. Why do we need replication? What techniques are available to us and how these are techniques work? So, replication terminology. There are some of the terms that have the same meaning and are used interchangeably. Master slave, master standby, master replica, primary, secondary, primary standby. They all have the same meaning. All these terms denote the same configuration. Basically, this tells us that there is one main server, and there are more standby in this configuration. These terms are trying to tell us that. Then there is physical replication term used. Streaming replication is used. Both of these terms are interchangeably used in PostgreSQL documentation. Basically, they are referring to the same thing. They tell us that this kind of replication is basically relying on transmitting the data pages over to the network connection. Horse standby. Horse standby basically represents basically any standby can have two kind of behaviors. One, where our standby is capable of serving the client with means that it can receive the queries. And the other is where the standby cannot accept the query. So, where a standby can accept the query or client connections, basically, those standby are called horse standby. And where a standby cannot accept the query, those standby are called one standby. Horse standby is one other distinction between these two. Sorry, the horse standby is basically the query, but they are only instances. You cannot perform any rights on them. These were some of the major terms. So, let's move on to why we need replication. So, we already have a database server setup, and it's working fine, and the physical hardware is good enough so that there are no performance issues as well. So, why would you set up a replication system? Well, although your system may be working fine, but what will happen if your disk fails? Are you losing network connectivity? Or your site just goes down for any number of reasons? So, basically, these are a couple of scenarios where replication setup comes in handy. So, these are very true scenarios that basically show the importance of replication. The first one is the high availability. So, high availability is one of the main reasons for replication setup. Imagine the database server going down during the peak hours of patrol. If you were to identify and diagnose the problem at that point, how much time would it take? Maybe you can recover the database system quickly enough, but what if no solution is found in time? Or even if you found the solution and implement the same, that solution would surely take some time. Another possibility is that you can bring up one of the backups that you have been keeping, but still restoring the backup and starting over that backup would take quite some time. So, even then, there would be another drawback that backup may not be up to date. You took backup quite a while ago. It can depend on your backing up policy, but still backups cannot ensure that you have up to date data. So, this is the question. Can you afford that much downtime? In most circumstances, the answer would be no. So, what would be the appropriate policy for such scenarios? That would be replication where replica or standby is continuously catching up to the primary server and can be brought up in no time. Disaster recovery, basically the same logic applies as HA. You have set up replication. You have redundancy in place. The head can be on-site, off-site at multiple geographical locations. So, if your site goes down for any reason, you have plenty of options to bring your services online. So, if the workload keeps increasing more than once you can handle, you have the option to have such scenarios with replication as well. If your main server is not able to serve in a timely manner, and perhaps if you have the leverage of replication, you might direct some of the traffic to those replicas that will shortly reduce some burden from the main server. Similarly, replication can be used for load balancing your workload. There are a couple of ways where you can direct your traffic to some of your replicas instead of forwarding them to primary server to serve. All of the read traffic can be diverted to your standby. Backups, although you might be taking regular backups, however, backups tend to not be up-to-date to your current system. Since the standby is always trying to catch up to your main server, there are your most up-to-date backups as well. Not only that, not only you will have a latest backup with standby, recovering from those backups would be more quicker and there would be very low chance to lose any data as well. Here I am talking about data change that has happened between taking the backup and the time it has elapsed since then. Alright, so as we have discussed, the purpose of replication is to have more than one reliable copy of database at all times. To that end, getting a reliable copy of a running database system can be done in plenty of ways, even in more ways than I can imagine right now. For instance, the simplest way would be to install a RAID controller and get the data written to multiple disks at the same time. That will give us a full-fledged working replication system. But the problem with this replication solution is that it will only go against the disk failures and our system will be prone to all other hazards that we can possibly imagine. This also does not protect us from application crashes, which can lead to service downpours. People have been successfully doing database replication by using middleware products like PgPool 2 that sits between client and the server and do the statement-based replication by sending the radials to multiple configured servers. So what I want you guys to understand is that that application is just a way to make reliable data copies and we can have so many ways to keep that. Of course, each of the techniques has its own merits and demerits. Some are easier to set up but are prone to any kinds of failures, while other are somewhat difficult to manage and set up, difficult to manage and set up, yet are more reliable and robust. So when we talk about model replication system in Postgres, we are now normally referring to streaming replication along with the logical replication. Both of these are native to Postgres now and our log-based replication techniques. This training will more or less revolve around that. So in general, these techniques can be divided into statement-based, trigger-based replication, binary replication, and logical replication. These are the most highlighted ones. Statement-based just means that the replication is done using manipulating sequence statements somehow, whether you send queries to database instances directly or have some instances forward those queries. Binary means to have some means with which you can manipulate the actual data pages that were modified to achieve the replication. So with statement-based, it's basically a very basic method to set up a replication system. Pretty much every database system starts with it because all you have to do is direct all writes to all the nodes in your cluster. And that's it. You have one level of replication in place. Definitely you will have to work out a few things, but that's pretty much it. And it's much simpler and easier to implement. So in Postgres case, this kind of replication relies on third-party applications such as PgPool2. Basically your clients connect to PgPool2. Then PgPool2 is then responsible to direct the queries to all database nodes that are connected to it. And then it will try to resolve any inconsistencies between them. The next, we have trigger-based replication setup. Database triggers is a feature that initiates a process when data within a database system is modified. In case of insertion, new data is available to the trigger. And in case of collision or decision, both new and old data is available to it. So another way to do replication would be to use trigger. I think every worthwhile RDBMS system provides this functionality. So what we can do is to write the after-data manipulation trigger and in that trigger functions, just send the copy of data to server maintaining the data copy. Of course, that would require a lot of setup and work. And also reconciling the data would be one heck of a task with that approach. A simpler way to set up trigger-based application is through third-party application again. For trigger-based application, ensure that the center writes to the master and when changes has been applied, then those new changes get distributed to other standby. Basically, this is achieved usually by deploying our daemon process along with each database server. These daemon processes monitor the trigger and synchronize the data between these nodes. Soloni is basically the prime example of this kind of replication. Some of the advantages that are available with this system with trigger-based replication is that you can choose the objects that you want to replicate. You don't have to replicate your whole cluster. You can choose the subset of the objects that you want to replicate. You can work across multiple server, different server versions. You can set them up in cascading replication mode. You can use this kind of replication to upgrade from older versions to the newer versions. And one more thing is that the standby can still accept the read and write traffic. Usually, we will see in the next two slides with streaming replication. Usually, standby are not able to accept the write traffic. However, as I mentioned before, when there are metrics, there are limits of the system as well. So with trigger-based replication, it's always asynchronous. And the data objects that you can replicate, they have to be able to uniquely identifiable. That means you use the job primarily. And in the previous slide, this is kind of a Sony setup where each Sony demon is located with each node. So basically, setting them up is quite complex and its configuration is a lot more difficult. So the binary replication. So before we talk about binary replication, let's discuss what are the wall files because binary replication is based on this whole concept. Wall files are the write ahead logs. These files keep the history of all data changes that are made to the database system. This is done to ensure that the data loss does not occur in case of a system failure. And if a failure occurs, these log files have sufficient information in them so that the database system can recover itself using that information. To do that, whenever a write is made to the database system, it is written to the log file as wall record. These records are first written in memory wall buffer and then when the transaction is submitted, those records are written to the disk. In Postgres, the wall files are by default 16 megabytes, but that size can be changed in current versions of the Postgres. So in binary replication, basically, one server instance receives the writes and then these writes are distributed to other standby. Distribution is done by sending the data pages that are changed during the write operations. In Postgres, we can use this capability in two ways. The one of which is the wall-based log shipping, also known as archiving. In this approach, we ship the complete 15 megabytes of files to the standby and then the standby replays them on themselves. The other way is to instead of sending the complete wall file, the data chunks that are to be stored in the wall files, they are streamed directly to the standby. That way, the standby will apply those chunks just as they would replay the wall file. So there are several advantages to the binary replication. The first one is that it's a bridging method into the Postgres service server. So it's natively supported. It's very easier to set up and it's very highly tested. We will create a setup using this technique later on. Very low administrative cost is involved. Very low overhead is on the primary server because there is very minimal load on database server to maintain the standby. The master or primary only has to send the change blocks to the standby instead of sending the whole chunk of files to standby. So the other advantage is that along with it's Qunas port, it also supports Qunas replication. Qunas replication basically ensures that there is very minimal, there is almost no data loss between main server and the standby. It ensures that standby server is always in sync with the main server. And the other advantage is that it also supports the cascading replication. Cascading replication basically entails that one of your standby can get its data chunks from another standby. Instead of pulling the change blocks from the main server, standby can communicate to another standby. That way it will further distribute the load from the main server. So while there are advantages to binary replication, there are two disadvantages to this approach. So with the binary replication, one of the big disadvantages is that you have to replicate the whole database cluster. You cannot say that although you have a couple of other objects, but the main and important objects, if you want to replicate only the important ones, you cannot select them. You have to have replicated the whole cluster over to the standby. The next one would be that the standby in this configuration cannot accept the right. They can either serve as a warm standby or a hot standby, but they cannot do the right. And the most important one is that you cannot set up this replication across different versions of the database server. You cannot have PostgreSQL version 10 getting replicated over to the PostgreSQL version 12 or vice versa. Okay, so let's turn to the interactive session now and see when the replication works. We will start with file-based log shipping method and we will go from there to streaming replication and then later on we will see about logical replication. Just so you know, from here onwards, basically all the steps that are required to configure this replication are listed in the site, but we will be using the terminal to show how to actually configure the setup. Okay, this is my Gitro Postgre. This is the example directly that I was talking about earlier. This contains already pre-configured. This has already pre-configured replication techniques. R5 is for all archiving. Logical replication is for logical replication and logical replication cascading mode. Similarly, there is a streaming synchronous and streaming synchronous and cascading mode. They are already pre-configured so you can play around them. For this session, we will be using Docker setup. Let's start our Docker container. I think I have been using this network and I did not really set it. Let me take care of that. Okay, so I have this Docker Compose file. Basically, it will launch three containers. I have already named these containers. One is called master. The other one is called standby and another standby that's named standby2. I have already signed them fixed IP addresses. It would be easier for us to create the configuration that we are going to create. My containers are good. Let's attach to the containers. You can attach to the containers using Docker Exit command. Either you can specify the IP direct or since they have been labeled in Docker Compose, you can use those label names as well. When I attach to master, it will be basically my first container that will be serving as a master database server. Each container has already a PostgreSQL server version 12 installed. I have already configured the sudo and ssh server as well. If we have to do ssh from one container to another, that would be easier. A couple of points. The server installation part is the user pg-sql. Each environment has a couple of PostgreSQL-related variables exported which are pg-data and pg-port. These are the standard values that are used in 10-7 base systems. I am just using those values. Let's create our first application setup that is going to be based on archiving. Archiving or in other words, a ball-based shipping method. Let's first initialize the database cluster on the master server. Let's see the contents of our data directory. Let's just initialize. This is the data directory which will be used to run the master database server. Let's see this configuration file. We will be making changes to these configuration files. In PostgreSQL, basically any parameters that you need to set at startup, you can change them in PostgreSQL.com. This file contains all the default values. The good practice is that when you have to make changes, you will add them into the PostgreSQL.com file. So far, there is nothing here. We will be adding our configuration here. Archiving works in the way that you get a mount point or share a disk where your master database server will be saving the wall file. And that location should be accessible to your standby server. So those standby can read those wall files and apply them onto themselves. Generally, you have some kind of network mount point available to you. For our session, we will be copying wall files from one container to another so that those are accessible to the standby server. And to do that, what we need first is to set up a search-based connection between the two containers. So I will be doing that right away. So SSH Key Gen can be used to create a private key that you will use. So you will be able to copy this password phrase file to the other container so that when you access to other containers, you will not have to provide the password every time. We are doing that because we will be using STP command to copy the wall files from a master server to standby server. So the default user is PostgreSQL and its password is the same. But instead of providing the password every time when you do a search, you can set up so that we don't have to provide the password again and again. So our password SSH connection is established. So just to give you a brief overview of file-based log shipping, basically the primary server is going to copy wall files to the configured location that we are going to do right away. These wall files, when a database server is writing to the wall file and megabytes of data has been written to those wall files, server has the option to copy that file over to the location that you can tell it to the server. You do that using the R5 command parameter that is available in PostgreSQL.com file. Similarly, the standby have the option to read those wall files from that location using the restore underscore command parameter. The standby is basically kind of keep pulling that location whenever there is a wall file is available. It will read that and apply that wall file onto itself. Okay. This is our Docker environment that we already discussed. The master is, its hostname is master, the standby hostname is standby, standby 2, again, if we use that, it will have a standby hostname standby 2. So we just created a passwordless search connection. Now we are going to use, we're going to use to create a mount point where the master can copy wall files and standby scan, read those wall files. These are basically the same steps that I just performed to create a passwordless search connection. The next step would be to create location to store the wall file. So this is the command that we are going to use to create a location on the standby server so that it can, so that primary main server can write the wall file there. In this setup, basically I am creating the mount point on standby server. But this location can be any accessible mount point that is accessible to both master and standby server. All right. Since we had already set up hostless search connection, there was no need for it to ask the password again. In fact, let's test the standby server as well, standby. All right. As mentioned before, this container is named standby. Same environment as the master server. And we just created a mount point at R5DIR at the root of the file system. Here we go. So we already have initialized our database system. What we need to do now is make sure that our authentication settings are there so that we can connect to our servers from anywhere. For that, we will be editing our PGHBA configuration file that contains all the authentication settings. So for this session, I'm just using the trust method and I'm allowing the connections to be received from everywhere without any restrictions. But obviously, this is the place where you will have to ensure that your database systems are stored and could not be accessed unauthorized. All right. So this is the default. These are all the default values. I'm just going to copy the configuration file where I have already made changes and we will see those changes. So these are the settings that we are going to use for this setup. Basically, I'm allowing connections, replication connections, as well as normal connections from everywhere in the subset, in the subnet from every device that is available in the subnet of 172 and 22. And they don't need to provide any password for connecting to Postgres. All right. So the next thing we are going to do is configure to see the configuration that we are going to add in our Postgres configuration file. These are the settings that are needed. The first one, listener data, basically it allows connections from everywhere. That means allow the connection from everywhere. Unless you want to restrict that, you can specify comma separated list of addresses if you want to restrict that access. So we are going to use the same default for mixed connection. Basically it tells that how many simultaneous connections that you are going to allow to connect. You can specify the value that you can think is more appropriate. Since we are going to set up ball shipping method, we need to set up the wall level. That is one of the mandatory parameters if we want to set up to work. And we are going to set this to replica prior to this version. There could be other values that you could specify here. For example, you could have specified for standby, memel, and few other. Specifying replica here basically ensures that our wall files have enough information so that the standby scan replicates the changes that have been made on the primary server. If you set it to minimal, wall files will not contain much information. They will only include the very necessary information that is required to start your primary server in case it's dashed. But your standby will not be able to utilize that information. Or they will not be able to replicate changes made on the primary and database server. The next thing is makes wall sender. Oh, sorry. This is not required for archiving purposes. For archiving, what we need is archiving mode. We have to set it to on if we want to enable the archiving mode. Then we have archive underscore command parameters. This is the command that will copy your wall files to a location that is accessible to both instances to primary as well as to the standby. Usually you should utilize some kind of state that it shows that does the error checking and stuff like that. But you can simply use any command, any executable command here. So currently we are using FTP. Basically we are saying that you should copy the wall file. Percent P is basically showing the source path. Posts as 172.IPRES slash the path is basically the destination. In this command, we are basically using a few of the placeholders. Percent P will get replaced internally with full path of the file. While Percent F at the end here, it will only specify the file name. It will remove the base path. Since we are copying from this source to our standby server, the next parameter is archive timeout. Basically this parameter will force the database server to switch wall files after this number of seconds has passed. Basically if there is no load on the master server, then it might not write the wall files frequently enough. So timeout can keep waiting. This is one of the ways to make sure that your setup is in good condition. So let me copy this configuration file to gdata. Let's start the database server on the master. gdata, our database system server has been started. If we are pursuing the wall file, if any of that, we will have to create some kind of traffic, some kind of data. We will do that in a moment. First, let's configure our standby and then we will see if we are getting any data from the master to standby. To do that, let's first take a backup from the master server. We will do that using a pgps backup utility that's available as part of postgres installation. Master, it should be able to transfer it into the IPvS of master. Since we are setting up standby, we can write the same directory that is being used in the same part that is being used by the master. We want to clean backup and we want to control the wall file and we want to see the progress bar. Since there was no additional data, it was quick enough but it will obviously take some time to take a backup. On standby, let's edit the postgres confy and show that we want to basically set up a hot standby. We want to restore or specify the restore command so that it knows where from where to copy the wall file. I already basically have created the configuration for this one. The next step would be that we want to tell the standby to start in the standby mode. We do that by creating an empty file in the pgdata directory by the name of standby.signal file. This is the new way of telling the standby that is going to be started in the standby mode. Let's create that file. We already have started the master database. Let's start our standard database. Let's generate some traffic, create some data on the master database. We will see if the replication is happening between these two instances. I am just creating a demo table with random values. While editing the pghpconf, I removed the default values that were allowing the local connections. That's why we got this error. My table name was just step 2. Let's see if this data is available on standby. Again, same problem. But this time we are going to connect to standby. It has not been replicated so far. Let's see why not files are being generated. I made a mistake while specifying the path. This is the path that actually data is being copied. That actually does not exist. Let me just create the key. Here we go. We know the database server from the master instance has started copying the wall files here. If we run our tree again, we can now accept the same table that we created and inserted values in the master database on standby. This is basically the wall shipping method. You create a location where your master database can copy the wall file and your standby can read from those. The standby can apply those wall files on themselves. That's pretty much it for the file-based log shipping method. Next we will be talking about streaming block-based replication. We will see how that goes. This is a new topic. The next session is about streaming replication. It's called block-based as well because we will be streaming data chunks that have been modified over to the standby servers. That way the standby servers can apply only those chunks instead of reading them from the wall file. In this configuration, the standby servers connect to the primary streams of wall records to the standby as they are generated without waiting for the wall files to actually get filled. This way, it eliminates the need of scripts and commands in archive that we specify in the archive underscore command parameter. It also eliminates the intermediate shared storage that we created for the archiving setup between the servers. Streaming replication is asynchronous by default, but synchronous mode is also supported as well as cascading replication is also supported. Basically, when we set up streaming replication, there are a couple of processes that get launched in this setup. One of them is called wall receiver. Wall receiver is a process that's written to the database server that receives those data chunks that are generated by the master server. And the master server basically streams those data chunks using another server process that's called wall-center process. So basically, these two processes connect with each other to share this information. Some of the major benefits of streaming replication, these are that in this replication setup, basically you have the DDL support. That means any DDL that's made on the primary server is automatically available on standby. You don't have to configure anything extra to bring the DDL there. Hot standby mode is supported. That way, you will be able to use your standby to redirect the read traffic to those standby. Again, since your standby is able to accept the read traffic, you can use that to provide read scalability. Again, cascading replication is supported by this technique. That means that you can use one of, you can use one standby to receive the data changes from the master. While if you have a few other standby, you can connect those standby to get the changes from another standby. That way, let's say you have three standby, one can receive the data changes from the master directly and the other two can receive from the standby. That way, these two standby will not be adding more load directly onto the master database server. Asynchronous, synchronous mode is supported. The additional benefit of this approach is that at any given time, you can add additional standby without bringing down the primary database server down. While the database server is active, you can still attach the standby to that. Finally, in streaming replication, all database objects are replicated. So you don't have to worry about that if you left out some of the objects from being replicated. How it works in this setup, standby server basically connects to establish a database connection to the primary server. Using that connection, it will receive the wall records from the master. Basically, this connection will be made by wall receiver process. This process will utilize primary underscore coninfo parameter. Primary underscore coninfo parameter tells the standby to where to connect, which basically this parameter values will be used to determine which is your master server. Again, for this configuration to work, primary server must ensure that sufficient wall information is being logged. And we ensure that using the wall underscore to the table parameter. As I mentioned earlier, there are a couple of parameters that you can specify. There are a couple of values that you can specify to this minimal replica or logical replica value basically ensures that there is enough information so that the standby can apply that information onto themselves. We also have to ensure next wall sender parameters is enabled. This will tell the primary server that this many standby basically can connect with it. Alright, so the step one for this setup would be again to edit the process.onr2.conf file and make appropriate changes there. So let's go to the terminal. We already established the archiving setup. We will be enhancing the same to implement the streaming application setup. So let me just shut down the previous structured servers so that we can make appropriate changes to the configuration file. Standby equals down primary server. So these are a couple of parameters that we have to ensure that are present. Let me show you the configuration file that we will be using for this setup streaming. So here's the configuration file. Most of the values in this file are the same as archiving. The couple of important parameters are here. So the most important one will be the wall on full level that has to be set to replica. Then we have another parameter, next wall sender. This parameter basically says that you can establish this many replication connections to the database server. Usually when you take a backup using the database backup, the database backup utilizes two to three connections. So whenever you are going to set up the configuration, you have to make sure that there is enough room for the standby to connect to it. The next most important parameter would be wall underscore each underscore segments. This parameter tells the server to keep this many wall segments in the PG wall directory. Postcat server usually starts overwriting the wall segments once those walls have been shaped or when the server determines that those walls are no longer required for the database server itself. That usually happens when there is a checkpoint is made. Checkpoints basically ensures that all the dirty pages, all the changes that have been made so far have been written to the actual relational file. So after that, removing the wall file prior to that point kind of becomes useless for the database server. So it can start overwriting those, but it may happen that those wall files have not been shipped to the standby. If those wall files have not been shipped to the standby, or they have not applied unto themselves yet, and that wall file has been removed by primary server, that would create problems. Standby won't be able to continue working. So you have to ensure that there is enough room available there. So the wall sender timeout. This is basically kind of a ping response for the wall sender process. This way the primary server knows if the standby server is still active or not. So although archiving mode is on, usually it's a good practice to have this configuration as well in addition to streaming repetition. So in case there are delays, the standby still can create the wall files from the shared location. But if you don't, that means that standby will have the only one way to receive the data chance at that is using the primary connection. This is it for the master configuration. Let me show you the standby configuration for streaming setup. Again, so I was talking about primary underscore connection parameter. If you look at the value for this parameter, you will see the IP address. This IP address is the server that you want to connect to. That you want this standby server to get the changes from the port of that and the primary server username that you are going to use that server in password. And lastly, the optional parameter application name. The most important one is the host name or username and password. Okay, so I'm going to copy these creations to PG data for wall archiving. So I'm just going to remove everything in PG data. Using this pgp backup command, I'm going to take the backup again. All right, there we go. Now, the next step is that we need to tell that stand by server to again starting the standby mode. For that we need to again take standby file. After that, we need to set up the primary underscore connection information. I already showed that information to you guys. So I'm just going to copy that file, standby dot streaming PG data. Just make sure that the required information is there. The primary connection is set. So let's start the standby server. There we go. The database server has been structured. So previously we had created a table that got replicated in wall archiving mode. So let's create another table to see if this table is being replicated in this same replication setup. So I'm going to rename the distance for tap to distance for step three. And this information should be available from the standby. If the standby has caught up and our configuration was accessible. And yeah, this information is available. This table has connected to the standby server and it could be that test underscore step three table. So this configuration so far, as I already mentioned that the streaming replication by default is synchronous streaming replication also supports the synchronous mode. In the synchronous mode basically prime primary ensures that standby has same information as as the primary server to configure the same replication setup in synchronous mode. We need to set another parameter. That's called synchronous standby names. Let's go over to the synchronous mode. Okay. There we go. synchronous standby names basically tells the server that you can expect the following standby to connect to you. And that they they should and that and that you should await from for some kind of response from them, telling you that they have caught up to you. All right, so I'm going to switch to master terminal and let's see the configuration for synchronous mode. Master streaming, streaming, streaming, streaming mode. Most of the configuration parameters that are required are the same as asynchronous. The only addition is the synchronous standby name. With this parameter, we tell the primary database server that there's a standby named standby. This is the name that is going to be included in the information that it will receive from the standby. I will just show you in a minute that where this name comes from. So we have set up, set this up, copy this to the data of master server. Posts.auto.con. And let's restart our database server so that it will detect any changes. On the standby mode, standby mode, what we are going to do is first stop the database server. So the required information that standby needs to include is already there as part of primary connection info and that information is the application name. See, the application name is set to standby. This is the name that is being expected that we mentioned in the synchronous standby names file. This parameter accepts comma substitute names, so you can specify multiple standby names. If you have more standby, you should name them differently. So in case of single standby, I have just named them standby. So if the other standby, if I had named standby true, I would comma substitute this list and add that name here as well. So that both standby become synchronous. So let's copy this configuration file in the data, start the server. Okay, so the database server has been started. We already replicated that three. We just converted from asynchronous to synchronous mode. So how do we know that our configuration is under synchronous mode? For that, we have a couple of views available to us by a process server. We are going to use those views to see the information. Let me show you that. Alright, this is the view, pg underscore set underscore application that presents you the information that how many replication servers are connected. Sorry, how many standby servers are connected to this master server. One of the information available is that application name. And that application is connected to connected to the master server and is acting as a standby server. Current state and think underscore state column shows that it's synchronous or asynchronous. So sync here tells us that our current configuration is synchronous replication. Alright, so this concludes the synchronous replication setup. Next, we will see how setup cascading replication. But before doing that, let me launch the second standby node. And attach that node to the master server. That way we will again review this information available in pg state repetition so that we know what's how many standby are connected. And that that second standby I'm going to configure as asynchronous. So we will have one synchronous standby and one asynchronous standby. So, let me launch the other container. And by two. Another container. Let's first take a backup from the primary server. Okay, let me copy the command from terminal. It's going to be the same in the environment. Same for this container as well. Other environment variables and the settings are same. So we just took a backup from the master server. I'm going to copy the application file. Streaming. It's going to copy it. Chatter. We have a primary connection setup. So what is there? I'm going to start this server. Let's see if the data is available on second standby. I'm going to change it to standby two. Okay, it's connected. And it's replicating. So let's see the information again. Let's see if the primary server has not acknowledged it yet. Why is that? Let me try to connect with the master. Okay, I'm able to make a connection from standby two to the master server. Let's try and see if there is any information available in log files. So I guess I did not configure the logging. Let's talk about what we did not do. We did not tell our EG Chatter to start in the standby mode. We did not get this file. That's why it was not connected to master server. It did not start in the standby mode. So we have again fitted this file and that's restart server. Okay, so let's see if the connection has been established. Oh, yes. If you see the output of EG Strat application, you will see two records. Record one is indicating that application by the name of standby is connected to me. And it's in synchronous mode. Then there is another connection. It's clearly telling us that an application by the name of standby is connected, but from a different IP address. And it's state is kind of potential because the second standby had the same name. It's kind of a conflict. So what we should have done was that in Postgres auto, sorry, in this file, when we specified primary connection info, we should have said the name standby 2. In this scene, let's restart server. For some reason it's taking some time. Okay, let me recreate the server. I guess there's some mix up. That's why it's fine. We have changed the name. It's 10 by 2. We missed this task. Okay. It is. It should work. So in the output of the Strat application, the second record is showing that application in standby 2 is connected now. And it's connected in asynchronous mode. Because of the application, the same application name from master server was receiving the same application name from two containers to standby servers. It had some kind of confusion. It took both servers as synchronous servers. So this is, so now our current setup has one synchronous standby and the other one is the asynchronous standby. Okay. So the next topic is creating a cascading application setup. Basically, I have already explained that in cascading reputation, the standby has the ability to replicate itself from another standby. Basically, this lessons overhead from server. And that overhead is basically shifted from the master server to the standby from which it's trying to replicate. The same server creating processes work in this setup as well. The only difference is that instead of the master server instead of that process, it will be a wall-centered process of a standby that is going to send the change data blocks to the other standby server. So let's start with the configuration. Again, on the standby server, we are going to take another backup. But in this configuration, we will tell the primary connection info to point to existing standby instead of the master standby. So we already have two standby right now. One is in synchronous mode and the other one is in asynchronous mode. They both are connected to the primary server. So let's change the standby server to instead of following the master standby, let's tell it to follow the standby server. I'm going to again remove the current setup from standby to node. I'm going to take another backup. It's got the configuration file. For this configuration file, I think that it's already streaming. It's already in this file. Let's see what we have changed in this configuration file. So if you look at the primary connection info, you will see that the IP address has been changed. From 10 to 11, 11 represents the standby server. Let me again change the application name so there is no confusion. So I'm again going to copy this configuration file in BG data. We need to ensure that we're starting the standby mode. So we have to get standby. And that should be it. Let's start the server. Database server has been started. So we did not stop the master server. And now it's showing only one record showing that standby is connected. So right now our standby tool should be connected to the standby one server. So let's see if it shows us anything. Let's click with standby server and see what it shows. Okay, now it is showing that there is a standby named standby two from different IP address. And it's connected with this standby. And it is connected in asynchronous mode. So this way, right now, one standby is connected to another standby. And if any changes are made on the master server, that those changes will be replicated to the standby server. And that standby server will again further send those changes to any other standby that's connected to it. So let's test out this. Let's click to the master server and create some data there. We already have this step three, let's create the step four and put some data in it. So this data should be available on standby. And this is available. We just do it from step four that we just created on the primary server. Let's see if these changes have been replicated on standby server two. And yes, they are. We are able to access that table that we just created on the primary server on the master server. Now let's talk about replication slots. In streaming replication, basically, we so far we have been using a max wall segment configuration parameter to ensure that primary database server does not remove the required wall files. Before they are applied on to the standby servers. But that's the configuration that user has to maintain. To overcome that limitation. Postless came up with the replication slots. Basically, the certification slots ensure that the walls are not removed by a master server until they have been received by standby. To ensure that there is a parameter called max application slots. This parameter tells the database server that it can expect this many slots to be connected on from the standby. And then we will have to create actually create a replication slot on the master server. And use that slot name on the standby is to ensure that they are connected. They are connecting to the master server using that slot name. When slot names are used this way. The database server will ensure basically it will start communicating that whenever there is a data generated on the master server. The replication slots will ensure that they are applied to the standby. Before it allows the database server to remove those wall files. So let's let's just create that. Here we go. Let's edit this configuration on master. Let's create a replication slot to specify the host and the server. We have created a physical replication slot named slot. We are going to tell our standby to use this slot on the standby server. Again, it is a computation file. It is a primary underscore slot name. Okay, so I end up there. Yes, restart the servers. And that's it for the replication slots. Okay, so let's go through monitoring. We have already created with replication modes. So how do we check our replication states? The one way is the G stratification view that we have already gone through. And we have seen the information that's available from this view. There is a couple of more information that's available. And that is sent LSN, write LSN, flash, play LSN and similarly the legs. Basically this information tells you that this much file data has been applied on the standby, whether the wall record has been sent on the standby to the standby, whether the standby has that record has been written on to the standby. It has been flushed to the disk or it's still available in the buffer or whether standby has already displayed that record on to itself. Basically, this information is telling us that ideally all these values should be the same. That will tell you that the standby has appropriate information. If there is differences, then those are reflected here. Similarly, right leg, flush and replay legs are telling you that your standby is how much behind from the master database server. So we had configured the standby, but to ensure that our standby was struck up into the standby mode, we have this function that we should have utilized to see that we started in the standby mode or not. Previously, when we were configuring standby through server, that server was not started as the standby mode. And this actually should have told us exactly that Pg is in recovery. It is true if POSCAS server is in recovery mode, basically is in the standby mode. On the standby, on the master server, you have PgState application view available. That view can only be utilized on the master server. But if you are connected to the standby server and you want to know how much you are lagging behind the master or what waller information has been received and replayed, you can see that from standby as well. You can see that from standby using this function, that's a breaking function by POSCAS server. PgLostWallReplayElecent and lost transaction replay time span at the time when lost wall record was received. So, let's see, somehow it's not sending any information. Let's put some data on the master and see what's going on. Oh, okay. So, I guess, standard one is the, is considered in this minus four. And it's pretty much in sync with the master. So, there is no lag here. But since the standby two is kind of a success mode configuration and it's connected to another standby. So, there is a slight delay and that delay is being shown here. Again, on the standby, we also have a view available to it, PgStateWallReceiver. Basically, it will show us the state of standby. So, standby two, it will show a state of ink on the standby. Here, let me put the same query on the standby as well. Okay. So, basically, this view tells us that we are streaming data from master server. This is the information. This is the server that we are connected to. Now, we are using replication slots and which replication slot is being used for this standby server. And the connection parameters that are being used, they are displayed here as well. Again, it will also show the information related to the ball. It will basically show that which wall records have been received, have been applied. And, yeah, there is a couple of more information available there. The last message send time, receive time, the latest wall record that we have received and the time at which we received that information. So, the next is checking delays that have been there in applying the wall profile. We can use this query to get the time in seconds to see if there have been any delays. It shows that it's pretty much in sync with master. Let's try it on standby two. And it's also in sync. So, let's try and add some more data and see if anything changes. Nope, pretty much quickly caught up. So, that's why it's showing that there is no lag, but if there is, it will show you the time. So, that's pretty much the streaming application setup. We have covered a couple of application modes that are available to us and we have seen how monitoring works. Guys, I think we are kind of short on time. So, if you guys have any questions, you can ask them now. Yeah. So, the question is the primary connection info that we set in Postgres.auto.com for standby. What if we fail over to the standby? Does that need to be manually removed or will Postgres ignore it? Yes. When you fail over to the standby, so basically you will be telling the standby that you are no longer a standby. You are now a primary server, primary active server. That means you can now receive reads and writes as well. So, it will ignore primary underscore connection info parameter in that mode. The question is the replication slots. I thought I heard that it can impact transaction IDs rep rounds. Not sure in which context you get that. However, the replication slots to impact the removal of all files. Basically, it won't allow the LS to rep around the wall segments. When there is certain limit is changed Postgres when determines that some of the wall files in PG wall directly are no longer required. Instead of removing those, it starts to overwrite those wall segments. So, that kind of rep round does happen and is impacted by replication slots. If you have read that article and you still have my question, you can forward that to me on email. My email is assyptathrehman at hygo.ca. Git repository that I shared that includes sites like as well. Thank you very much. Thanks guys. Goodbye.