Logical Replication with PostgreSQL 10

hi this is Darren from Postgres course comm today I'm going to do a quick demo of logical replication and it's not going to be all the ins and outs of logical replication but instead just like a quick start to using logical replication because about week ago Postgres 10 came out and now there's native support for logical replication and Postgres 10 so here's kind of the outline we're gonna follow I have two machines set up on this tab you'll see a machine called node 1 and then I have another tab open to another machine called node 2 so on node 1 I'm gonna call that the source I'm gonna set up the user and authentication I'll configure the wall files for the proper level to allow replication I'll create a publication and grant rights to my user so I'll explain those steps as I go through but that's the setup I'm gonna do own node 1 and then over here on node 2 I'll create a target table create a subscription and we'll test and make sure that the replication is working right so let's get started the first thing I want to do with step 1 is create a user that no 2 will use to connect to node 1 and begin replication so that's pretty straightforward I'll log in a Postgres and I'll do create role I'll call this user replicator I'm gonna grab the special privilege of replication they need to be able to log in and I'm going to make sure that they have a password that password will just be really simple and silly it'll be password now the next thing I need to do is I need to make sure that that user can authenticate and so I'm going to edit my PGH be a comp file I'll make my privileges to do so and the location this file may be different on your installation but my installation is located there once I get into the file I'll create a line that indicates that the replicator user and connect to all databases from node to all right I'll leave the md5 setting there and that will indicate that they'll need to pass the password but that will give the replicator user access to databases on node one through network connections all right now I need to configure my wall logs and so I need to edit my Postgres comm file looking for a setting of wall level ok so by default this line is commented out indicating that there's nothing special happening with the right ahead logs what I want to do here is set a particular level of right ahead log so that the other machine can come in and copy table based data out all right so there's a number of settings here which we explain all these settings in class but the setting that we need is just the replica setting what this will do is just add enough information to the wall log so that Postgres will create them so that the other machine can come in and logically make copies of data all right other settings have other indications and implications for other settings we would actually turn on wall archiving this is just a very simple logical replication setting that we're going to make that's the one setting we need to change so we're gonna save that finally after making those changes the change to hp.com would normally required just a reload of Postgres but because we're changing how the wall logs are written we actually need to restart Postgres all right so do that this way obviously I could also use the PG control script but there's my restart successfully of Postgres on my primary node all right so let's see what other steps we have to follow all right so we've done step one and two now we need to create a publication and the step of creating a publication is essentially setting up a table in a database so that another machine can come in and copy that a table so let's do that login to Postgres I'll do a listing of the databases and I have this database called Baltimore so I'll connect to Baltimore and inside the Baltimore database I have one table called employees so I'm gonna set up the employees table so that it will have a publication so that command is create publication I'll call it B pub so I gotta give it a name for table and I'll say employees now I have two options there on that I could have said for all tables and you'll see that in the documentation I could also give a comma-separated list of multiple tables but in this case I'm just going to do the single table alright so now I've created a publication one more thing I need to do is I need to grant that replicator user rights to this table so I'll do that very simply I'll grant all on employees - all right so now this sorts machine should be set up to allow my target machine let's just begin replicating so let's move on over to note 2 on that machine we're going to create the target table create a subscription and then we're going to test alright so just so you understand these steps number five creating that target table we need to do that because logical replication does not include DDL our data definition language it doesn't include create table statements or anything like that so we have to manually create that table in a way that's identical to the way the table looks on node one so let's go over to no.2 and just see what kind of shape it's in so I just list databases there you see I don't have my Baltimore database so obviously even inside the Baltimore database I don't have the schema or the set up for the employees table so let's create the database for Baltimore first so now I have a Baltimore database created and I'm gonna do creating that employees table in a really simple way alright so let me show you how I'm gonna do that so over here on note to have already prepared my PG hp.com file so that the other machine can log in with the Postgres user so you'll see here I have a rule that allows the Postgres user to authenticate from node one so what that means is on node one I could do something like this I could do a PG dump on employee on Baltimore a PG dump on the Baltimore database the table is employees I want a schema only dump so by executing that command I get all the DDL I need to create the table employees obviously no data comes along because I specified the minus s which means schema only now I could take that and pipe that over to the P sequel command on the Baltimore database on host node to doesn't have a user called replicator so it can't grant replicator but everything else it did was successful so if we go back over - no - you'll see I connect to Baltimore I have a employees table I'll select star from employees and employees is empty alright so node two has successfully I've successfully added the employees table to the Baltimore database with just the schema alright and just so we know what that looks like on node one saying the structure but on node one there's actually data so to get that data over to know - we need to begin replication alright so over here I need to create a subscription so I do that with the creat subscription command so create subscription I'll call it B sub and then I have to give a connection information so how do how does it need to connect well needs to connect to a DB with the name Baltimore use connect to host node 1 user replicator also need to know the password and niece know which publication to attempt to connect so that was B pub alright says it create a replication slot called B sub on the publisher and we'll select star from employees see if there's data yes now on no - we've replicated that data we could also test for other commands or other actions that would actually create data and demonstrate that no 2 is replicating so let's go back to node 1 let's just do a really simple and dirty insert so I'm back on node 1 and let's do this let's look at the structure of the employees table let's do insert into name and yeah let's just do an insert on name just something ridiculous inside name column on Baltimore database on node 1 Delta if I told it with table right all right so insert into the employees table the name column the values Darren was here all right so let's go back over to know to rest you select star from employees wear name like there's the one I I entered all right so replicating between node 1 and node 2 so just to recap real quick a source we need to give a user allow that user to authenticate in PGH free comp go to Postgres comp change the wall level to logical create a publication for the table we want to replicate grant rights to that user we created in step 1 on the source machine on the target machine we create the target table I showed you a quick and dirty way to do that right we can just do a schema only dump on the source machine and pipe that over to Postgres on the target then we can create a subscription on the target and then we'll test for replication all right so hopefully that was a quick introduction to logical replication the feature that I was most looking forward to being supported natively by Postgres there's a lot of other ways to replicate our data logical replication may or may not the best solution for given application but how do we know well we'll cover that in class so if you would like to see more about logical replication feel free to click the link take a look at our current class schedule and hope to see you soon

Channel: PostgresCourse.com

Views: 26,656

Rating: 4.9130435 out of 5

Keywords: postgres, postgresql, postgres 10, logical replication, tutorial

Id: HkgYdirSdi0

Length: 13min 42sec (822 seconds)

Published: Thu Oct 12 2017