Logical Replication in PostgreSQL

[Music] hello friends welcome to cloud5 in today's sessions um i am going to discuss how to set up postgresql logical applications and we'll do some kind of you know a few different uh scenarios of the logical application okay so this is the postgresql replications uh you can see that um mostly there are two types of replications one is the physical replications one is the logical replications uh physical applications you know this is introduced in postcard sequel 9. in this mechanisms uh the change in the master is streamed via the wall that is right at logs and there is uh two types of thing one is lock straight shipping one is streaming but today we are going to discuss about the logical applications the logical replications is kind of a unique model because uh the physical applications having some kind of uh constraints or limitations like you know it cannot be replicated a part of the database selected replication is not possible it cannot write to the standby server uh replication cannot be done in between two different major versions these kind of stuffs but the logical replications um all the things can be done you can replicate of one table a few tables multiple tables okay and it's done through a mechanism called the publish subscribe model so what is the public subscriber the publisher node which you can say the source node creates a publications which is also a kind of a chain set of a replication set the replication is simply a set of changes you know it can be a one table it can be multiple tables and then in the subscriber notes is create a subscription and it can be subscribe you know subscriber could be having a one or more publications okay so uh it will uh subs what subscribers to do is to connect to the publications and pull the data and you know and replicate okay um so in today's discussions i am going to discuss that you know create a source that the public publishers um sequel database easy to similarly i'll also create uh two scenarios one is for the another uh postgresql db as a subscriber in ec2 another one is rds and then we'll create the publisher and subscriber we'll be discussing about on two three scenarios uh what could be done in each and every scenarios and you will be definitely in the check of the replications so i'll be as i said i'll be doing you know four tests you know it could be uh and a few multiple tests could be done like first test uh the publishers and subscribers both will be in the ec2 and will be doing the simple test table with the primary unique key normally that is normal things like when there is a replication sap and logical applications the primary unique is is needed now having said that you could you we can do that without the primary unique key and there are some limitations and i will discuss what are the limitations and how we can mitigate the limitations okay the test three uh i will be discussing about uh similar things uh then we'll be discovering that you know uh what i said in the source side there is something called publishing the different destination side that is called subscriber you can take it that way so if suppose uh after creating a publishers uh i thought i want to put some more table into it what kind of issue we can get it in the log files and how to mitigate with that and the last steps i'll be doing everything's together but i will be creating a rds okay mention these things here the version is not very much important uh mostly the postgre sql uh this way uh uh uh there you know normally it can be it was done in the two ways uh in uh previously for 9.4 onwards to 10 i believe 10 um okay databases prior to 10 it was there was a pge logical extension has to be created but now for the 11 12 database 11 12 13 even some versions of the 10 i believe i am not very much sure it is embedded like you don't have to create any kind of extension like pg logical so it is embedded okay so we'll be discussing all these things okay let's proceed well okay so i already launched in ec2 machines and create my um publisher or the source postgre uh cluster so i already login to the postgres the few things uh i'm before starting anything so what i'm gonna do uh i'll be updating my q configuration file that is postgresql.confan pghb acorn so i am going into the postgresql.com file the few things i am going to change uh you know first thing i want to change is listen address is localhost so i will change it to star you can put it depends upon your requirement some ips or list of ips whatever but just for the sake of this video i am going to put in guitar the next level i have to only need to change the wall level um it's default as a replica i'm going to send as a logical okay that is all i need to change the rest of the things is um is not mandatory okay now i'll go to i'll going to change for the pg gb conf okay pg hp account for the local and remote connections okay first is local is okay pr is good uh host level so i'm going to change to the i don't have any address you know you can put a address if you want or some kind of uh cid range i am putting is a all again this is just for the sake of the video you know i can definitely put a destinations ip address and all these things and method is md5 for replications again uh i am putting these things for okay since i have changed the value i have to restart my cluster okay the next thing i'm gonna do um i'll login and i will create a database i'll create a database called uh cloud y code by source okay and also i'm going to do i'll going to change the password of this my user okay why this is required because from the subscriber i am going to connect to these publishers so i need to change the password that's it uh the next steps you know i am going to create this my target database that is my subscriber one okay since this is a uc2 machines uh you know i am going to take an image but you know it could be anything your publishers will try later with the rds but your publishers could be anything so um i am going to uh currently take a image and i will create a another ec2 instance with the postgre sql installer and i'll be using that clusters and that database as a publisher okay well um as i said i have created another ecd instance uh using the image so that you know uh my postgres installations came along with that yes and uh that's it so i'll be creating a database here so this database i will be creating as a uh as uh create database cloud by target my as my subscriber okay so the first things i'll be taking is the very easy steps first this is my um so this is my source uh my publisher so i will be creating a table called table one table t table one and similarly i have to also create the structure here or the material right here i will be giving a table one okay so the first i will be creating um publishers in my source level okay so uh what i'm going to do i'll be creating a publisher publications call my publication or so this is my source my my applications and similarly i'll be creating a subscriber here i'll be creating a subscriber here for creating the subscriber what i need i need a um okay first of all uh let me tell you something uh for the both the two databases uh i have created a single vpc same vpc and the similar um same and under the same security groups and you can see the security equals 5.32 is allowed from everywhere though it is not allowed from everywhere because i am not connecting from the from my client machines or something like that um it's you know it only should be allowed you know from this you know uh your things you know whatever the ip address you want to connect okay ideally um so uh maybe the ec2 machines or the both the ec2 machines or the uh target issued machine to the source machine but since it is my testing environment i have you know given everywhere but this is important okay so for my the what pub subscriber will do it will try to connect to the publisher database so that means my first database ip it will be required this is my first one you can get by the launching time so i will take this first ib address so what i am going to do that i will be changing my subscription subscriptions connections here the host table here i will be putting the ip address of the host ip there is a db name here is my cloud file source okay username is postgres password is um welcome one two three publications my publication's name is my publication so i'll be creating these things in my target level or which is called a subscriber okay so this is the connect okay let me do one thing uh did i create these things okay i did a small mistakes maybe what i have to do that i have to create these things for cloud by source okay now i have to create again because the other tables i created as a my default level i don't want to call it as default i want to create under this uh cloud by source okay similarly here also i'll connecting as a will connect to this target database and we run this command again so i'll put my subscription as a subscription http i'll giving the connection details connection details for this my source or the subscribe publication database okay and i also mention my publications okay i believe this is not taking so let me put as a let me remove this hyphen okay okay so that means so now if i'll create if i put some inserts so it will directly it will be replicated to my as well okay so let's see um select start from table one you can see that it is directly replicated to my target that means my subscriber so let me i just want to show you another thing when it is created it's create some application slots okay you can see that you know it's created replication slot slots name is same as a subscriber name it's a logical slots and it is showing this um what is this the you know restart lsn that you know confirm flashlights and what is the things is created and uh same similar one if i um okay if i go to my doc file i just want to show you the log file how it looks like so this is you know my this is my default log file so i'll so if i'll good you can see everything you can see um logical replications started and then uh the synchronization september stabilize has finished it started and finished so you can see everything okay so the next steps uh so my this is my test one uh the next one i am going to discuss about the test tube so what is test2 test two um is uh okay test two is that you know same things um now uh uh you can see that you know the table we have uh created this time ah this table having a primary key because this is normal you know logical replication needs a primary key now what will happen if my table doesn't have any primary key okay so for that what i'm gonna do uh i will be creating a new table creating a new table and creating a new publishers uh and subscribers and i will discuss what happens okay so suppose i'll create um a table name called no pk it's a small table okay so it doesn't have any primary key or unique and i will be putting some a lot of ah inserts okay so select count star from no pk you can see the 46 rows has been inserted okay so but this table doesn't have any um primary key on any key okay now uh what i'm going to do is similarly i'll be creating a subscriber publishers for that uh let me create a publisher for that same i'll i'll create a publishers called pub1 for [Music] for table no pk for table no pk okay and similarly i'll be creating a subscriber i'll say as is ec2 sub 1 ec2 i'll be putting a subscriber called what is my pop 2 here the okay i'll be separate subscriber i'll be creating p sql minus t cloud by target okay okay so uh if i run the same query here what i should get okay it's not coming because i have not created the table okay so let me you know the tables is required so let me create the table now if i count it's saying the zero white is zero i did i miss some what is my publication's name this publication's name is pop one sorry you know i did a mistake um this is part two so i'll put a mistake here should be publications as a okay subscription names also i have to change say i'll put as a um sub 2 ec2 okay now if i do that you can see this it's automatically replicated that's good now the problem is that suppose in the source delete from no pk i'll try to delete from the source okay now this is something you know happens what happens it's immediately show me some error what is the error is saying that cannot delete from the no pk because it doesn't have any replica identity and the publisher deletes hint to enable deleting from the table we have to set some replica identity now this is something the problems because whenever you are taking without any primary key you cannot perform any kind of dml things updated is not possible i believe so there is a resolutions resolution that we have to put the replica identity change the replica entry for this particular table okay so normally uh the replica identity is set for a table and its value is it's there nothing so normally this is the value identity nothing so the another value you have to change you have to change the value as a full so i'll put these things full now i'll try to do this again okay sorry the typo okay so delete 8 now if i run again is 38 i run the same query again 38 that's it so uh if i put it again the nothing if i'll change these things nothing and okay and i'll try to do the delete again say delete india same thing same thing happens because i have changed the value to nothing so this is another um test that you know uh this is works uh but there is a problem if if you'll do this this replications uh putting you know without this primary or unique key and you'll do this doing the replica nothing that is a there is a negative side is there negative side is that is first of all it is taking the entire table as a row and it is extremely slow so maybe you know for uh my small stable it is okay but whenever you have an actual table and with uh you know few thousands or few millions of rows this is virtually very very very slow okay so you should avoid that that means every table replicating or a logical replication you should have a primary or the unique key okay so that concludes the test the two the next test what i'm going to check here i will be checking this you know what happens suppose i have created as uh publishers and the publishers for our table and also the subscriber and thereafter i will be you know maybe a publisher so i will be creating for all tables okay and i am having a three tables out of it and you know after that i want to you know add another table into this particular uh subscriptions so what will happen you know maybe um it should cater right but i have to do some few things you know refreshing or something on my subscriber so i'll be showing these things and the last and the not the list i will be doing the same steps for the rds so that uh you know the good things about this logical application as i said you know here i have done this all the things for the postgres sequels 12 but it doesn't matter it can cater for cross version so sometimes uh this kind of logical replication used for a you know kind of an alternatives of the upgrade so maybe you can do the logical replications for in between the versions so to cut down the down times in the actual productions so when i will be doing the labs test 4 i will be creating rds in the version 11 and i will be doing this logical application in between two databases okay so uh please hang on um test three so here uh what i'm going to do i'm going to create some few tables um maybe i'll create a table user okay and also i'll table table 2 let's create a table too okay and what i'm going to do i'll be now create a um similar tables here um table two and uh my user table in my target what i'm going to do uh now i'll create a publisher but this time i will be publishing for um say pabol for all tables okay so this will cut up for all tables now if you can see that uh if i'll run this oh sorry i should create these publications here sorry not in the target so if we'll see from the pg replication slots okay uh um this was the this is slot 7 for the subscriber this i created this one as a fault that's why it is temporary this is not active so here i will be creating a publications um subscriptions i will be creating this subscription as all ec2 okay and for my subscription name is pabwal okay so let me clear the subscriptions my bubble it could have some problem because i have created the publications here so it get confused maybe let me try to delete the publication i'll let me drop complications not delete okay i believe this now it will work okay i have made many mistakes it's not definitely not pop one uh this is uh sabal okay and this is papal okay so my subscriptions created so if i check these things it should be having another slots okay so now the good thing is that if i you know if i create some tables um insert some rows it will definitely do that this is normal like you know if i'll put some rows and similarly here also table two ah sorry table two uh this will work when i add this insert these things so automatically it should you can see that um see let's start from table two okay select uh start from users these are normal okay this is automatically replicated now what if um if i'll create another one user okay now there is a glitch since i have created the publications for all tables i can create as many as stable from the publication's end i'll show you another one um for the publication i'll delete the publications and i'll show you another one say um the what are the uh what are the things to be uh need to be done in the publication level as well so let me create here there's a table three okay table three and i'll just copy these things so it will be easier for me i'll copy these things as a table three and as well as and also copy the steps for here okay now uh let me put simple insert for table three um say same things now let me see if it is comes select start from table three no why not let me see the logs okay we are already in the locks 10 minus maybe 40 f what is saying that uh um it's trying to hint uh it's giving me some warning so it's not able to do that what i can do is equal minus t cloud y target so what i can do that um i'll refresh my subscription what is my subscriptions names for all my subscription name is uh subwool okay so what i'm going to do i'm going to refresh my subscriptions and now i'll check again select star from table three now it comes so that means even though my publications have been for all so i didn't have to do anything from my source side that means for publication side but since i have added a table after creating creations of the subscriptions i have to refresh the subscriptions with an alter command so that it comes now the next another things i want to show you if it is not a kind of a publication for all i have to add this particular table to the manually to this publication as well for that i'll drop these publications i'll drop the publications called um why i am doing that because i want to show something else i am i'm i'll be using the same publication name for table table four okay what is table four i'll be creating the same table for table four uh table four i'll be using the same things no change for that and uh for that i'll be creating a publications for table table four this time i am not creating for all okay so here also i will create the table for table four and i'll uh creating a um say i'll also drop the subscriptions and i'll be creating a new subscriptions for i already having a subscriptions for pub wall so i don't have to create drop anything but i will be dropping these things because i'll be creating this table let me drop subscription and i will create this subscription again i'll be creating this subscriptions again what i have done this i don't know uh whether i have to refresh and all this thing because i want to don't want to create this table after that so i'll create that table i'll drop the table 4 drop table 4 table 4 and i'll create the table 4 again okay now it's okay right because if i'll create a inserted row uh it will directly it it should come say inside that row is a table four this is quite normal there is nothing wrong it and select start from table four so this is normal okay uh did i do it for table four what happens table four i'll sorry my bad i have created the you know i have done some gupta i have to create the table after that so i have to refresh i should have created before that okay it's okay now the good things will comes say if i'll add another table after that same i add another table create table table five since i don't have any um all table here okay so if i want to add these things into my subscript publications what i need to do that my publications what is my publication name pub all i have to add this table that is table five so i have to add the table previously i didn't have to do that because it was for all okay so i add these tables now um if i uh insert into table five and i can see the same similar error because it's not been added i have to do the subscription refresh so i can see the same kind of error which i have seen earlier you can see the error because it doesn't exist relationship doesn't exist so normally uh what is the best approach here best approach here is that now you first disable these things um the replications and then you enable so my disable is subwool then you know if i'll disable this replication will be disabled at the subscriber side and i will create the table what is my table i will be creating this table the table is there table is not there and i will also do the refresh now since i enable disable earlier i will be enabling the things now if i do the query select start from table five i should see this it's not coming did i table five it's not coming because i ignored this error it says that you know subscriptions refresh cannot be possible when it's disabled that means i have to uh disable refresh these things after enabling it so these are things i missed so i'll refresh now and i should get this value now okay so my point was saying that uh if you don't have enough publishers for all and if you added the table after creations of your publisher first you have to add these things in the table you have publishers second thing thing if you have error something as like you know i have created this the pub2 somewhere you know unfortunately you know i'll just drop the subscription i believe the error will not stop so the last things what i am going to do that instead of using this ec2 machines i have already created a rds database and for the rds database few parameters maybe one mandatory parameters another few things i have to change so what are the things we have to change first of all we have to set this uh let me put it bigger little bit okay so what it says uh we have to put this says rda as this logical replication static parameter to one this part of applying the parameters and these things you can change you know i can one level maxwell senders max replication slots and max connection this is something we can change in the normal scenario or in a kind of a real scenario we should change because this parameter changes and can increase the wall generation so it is not mandatory but uh having said that if you don't change these maxwell senders or max applications loss number will automatically change the rds levels okay so what uh what i have done it um i have created the parameter groups um um for postgresql 11 non-default parameter groups and what i put here here i put this logical replications as a one so you can you know that that you know i cannot change the i cannot change these default parameters so i have to create a different parameter groups and i have created my database with the parameter groups so if i'll go into here you can see that configurations so my option goes is default but my parameter groups where is my parameter group my parameter group is non-default okay so since i enable these things uh i created uh if you have a running database with the default one you have to first change this choose this modify option put this parameter groups then you have to reboot your database okay since i have done it at the time of creations i don't have to do anything so i'll go to the connections and security and another thing is very important uh this is publicly accessible otherwise you have to do some more stuffs if you don't put in the public equity accessible so the database cannot be um do the replications so this is the endpoints um what i am going to do i will be using these things uh for connecting to this my rds database so i'll be using as a host okay and my database is tsdb my u is test dba and w i am going to put it okay what is my test database name sorry my database name is configurations my database name okay cloud sorry i put the wrong thing my database name is cloud by rds okay i have connected with the cloud by ids database this will be my subscriber or my target okay so i'll be doing uh very simple steps i already having some tables created here okay um i'll be showing few things here um like you know say i'll be creating you know i already having a table like say uh pub all pablo is already creating publications for me so i'll be using this bubble and i'll be creating a um subscription here so okay so same thing everything is same i'll be creating the subwool because this is my okay just for this is everything is remain same but i will just change the subscriptions name okay and let me create a source uh create table for sorry and then let me create this one okay it's also asking for five that's true because it's having the two database now i'll go for this okay now if i'll go check select transfer table 4 and from table 5. so this is very simple right nothing you know new on it this is supposed to be done and it is happening now what if what if i am going to uh add another column into these things okay then what will happen say i'll have put um table 4 ad okay the source side okay and let me put that means or something table 4 let me insert something what will happen here now this is rds right so i cannot check the logs where can i where can i check the logs i have to go to the console so i'll go to the login events and i can see the logs this is the locks last locks and go to the view go to down what is saying saying um it could not it's the same messages keep coming that is saying that relations missing some replicated columns and you if you can replace the locks these messages keep coming it will keep coming and coming so we can do the similar things we can do the similar things what we can do that uh the same thing i did earlier we can disable and we can fix the things and we can enable so what is the subscription name my subscription name is disable now if i'll go back to this my rds i cannot see new the last time is 721 you can see the 721 because it's disabled okay so log is not keep coming not bothering me at all now i have to fix it how can i fix it i will be doing the fix the same way and then i'll enable okay now go back you can see this has started now and everything goes well i can i should see this the value here now the new value here okay you can see the new value so you this is applicable this i have shown you in this rds but this is applicable for uh both um rds ec2 machines aurora post everything this is same now um another thing you have you might have checked this my version is different now you can see these versions okay if i give minus version it's 12.5 okay and uh like you know sorry uh if i see the versions here um this is my 12.5 and you can see here this is my postgres and if i'll go for the versions configurations this is 11 9 so this is different versions so the replications uh can be worked and it's it's work for the different versions so this is one of the major benefit out of it that you can replica replication rep in between the different versions so this is sometime this is works very well in a database upgrade wherever you can limited this downtime time you can replicate your data from uh lower version to the upper version without really down you know bringing down to your uh database or the database cluster for uh upgrade activity okay so we have discussed a few you know few scenarios like you know how it will work with uh normal tables with primary key or unique without primary key or any key what is the how to mitigate you know like delete or kind of update operations then also we have discussed that you know what happens when you can add a table after uh creating your publications and subscriptions what will happen if you change some kind of you know you know like you know if you add tables or you change some type of ddl how you can do that in the subscriptions level uh you know how you can disable how you can enable how can refresh and also we have discussed that you know how to do that in between the cross versions okay cross versions replications we also also done the uh we have to show you uh how we can do that in between the you know postgre sql installed in the ect and postgresql installed on the rds so guys i believe you got some idea how to do the basic level of replications uh this is a this logical replications is one of the very coolest feature in the postgre sequels okay so please try at your end thank you bye

Channel: CloudBhai

Views: 674

Rating: 5 out of 5

Keywords:

Id: X3nqi6spqNU

Length: 48min 53sec (2933 seconds)

Published: Thu Dec 24 2020