Part 20 - PostgreSQL: what is the pg_upgrade utility.

hello and welcome to email to skills video tutorial in this video tutorial we will learn how to upgrade post gray lower version to higher version using PG and a score upgrade utility so first of all let's understand what is PG and a score upgrade utility in post gray sequel so PG and a score upgrade utility is also known as PG underscore migrator in earlier version of post gray sequel and it is a utility which is used to to upgrade the post crane strengths from lower version to higher version let's say if I have to upgrade my post screen instance from 10 to 212 I can use PG underscore upgrade utility so PG underscore upgrade utility supports upgrade from 8.4 dot a star version to the latest version the current version is 12 and 13th is the Vita version so this particular utility PG and the score upgrade is not applicable for minor minor upgrade that means if let's say if I am on 10.1 and if I wanted to upgrade it to 10.2 this is this is not valid so this is a utility which is used to upgrade from lower to higher version and see let's see how how it is used PG underscore upgrade if you say PG underscore upgrade - - alpha you will see the different options the first option is - small B which represents to the older cluster having the executable location if I say capital B that means the new cluster executable location if I simply say - C it means I can check the status whether this particular PC and the score upgrade can upgrade from one version to another version or not - small D represents the data directory of older cluster - capital D represents for the data directory of new cluster and some other important parameters are - a small P which represents the port number of older cluster - P represents the port number of new cluster you can mention the username if there is a some other user other than post-race you can mention that - v is used to for verbose you can see the proceedings on the screen as well so how do we use the pre PG underscore upgrade utility so what we have to do before running this utility you have to create a new cluster and you have to instantiate is in Shanthi intention at using the init DB and once you instantiate the new cluster in our case the demo which is which we are going to perform is from 10 to 12 upgrade so there will be one instance of Postgres equal 10 and another will be post grace equal to L so we will update from Postgres equal 10 to post grace equal 12 so you have to shut down the older instance and you have to to shut down the the new instance as well before we use use this upgrade utility so what happens in the background when you upgrade use this utility so what it does it it copies the the data from older data directory to the newer data directory and since there will be two cluster one will be on 10g ten and another will be on twelve so you have to make sure that you give the exact location of data directory of old location new location binary directory location of old environment and the new environment so this is how do we use the PG upgrade PG upgrade - D small D represents the older clustered data directory location capital D represents the new data directory location and then small B represents the bin location of older cluster capital V represents the min location of for a new cluster so let's see the demo now and the demo which we are going to perform is like this so in the demo what we will do is we are going to upgrade Postgres equal ten to Postgres equal twelve on RHEL 7.5 and we will be upgrading one database which is DVD rental or it is applicable for all the databases which are hosted on the older cluster so let's so I have listed all the steps here and a notepad so we are going to execute this this is a step by step procedure to upgrade most K is equal to n to Postgres equal 12 so I have a Linux system the IP address as 192 168 1 dot 56 I have the root access and I'm logged in as Momo backstrom so the step first I'll check whether I have the internet e Internet activity activated on my system or not so I will just say ping google.com so this is working fine and the step to it is everything from the beginning so I'm going to install the post grace equal 10 version so yum install post grace equal 10 - server post grace equal 10 so I'm going to install this this entire demo is going to take at least 10 to 15 minutes so once we install this we have to instantiate it yes the packages will be downloaded it is very very fast next step is we'll say Postgres equal to n - set up a space in a DV this is going to initialize the database and your data directory will be created this is done once it is done you just switch to post GRA user 0 su space - post GRA just clearing the screen once it is done you just try to start it using sudo systemctl start post Chris equal 10 once it has done just check the status euro systemctl status post grace equal 10 you can see that it is in running status now let's do let's load a sample data so I have database in copied here in all doors equal so I'm just going to import that once it is done we will log in to PostScript prompt and we'll see what is the relevance hosted over there so I have a number of databases mostly this DVD rental there is just to showcase like how this database will be migrated from sorry upgraded from 10 to 12 just say P SQL and so I have deodorant DVD rental DVD rental and us canoe then we dare enter underscore now restore DVD rental and there are so many DVD rental databases I'll just quit out of this and then we will go to the step 3 so we have performed 2 steps and now we are going to install the newer version of Postgres equal so in this we are going to install pause grace equal 12 so this has to be done as the root user so I'll say yum install poster is equal 12 - server and this all will be mentioned in the comment section that's very very easy and just say yes so this will be downloaded once it is downloaded you just have to go to the complete location of the the binary location and then you have to execute it because you have two clusters now one is on Tengen and there is on 12 so us are /pg sequel 12 in Postgres equal 12 - set up in a TV so we are going to initialize it once it is initialized what we have to do is we will switch as the post gray user and we have to start the new instance of poor screen but this is not possible on the default port so we have to change the default port of 5 4 3 2 to some other value so what I am going to do is so we are going to add it the Postgres equal dot count of 12 Postgres equal 12 and we will replace 5 4 3 2 which is the default port to the new port because you cannot run two instances on the same port so you have to change it to some other value so I'm giving 5 4 3 3 and this is the newer port and then we will restart the post gray sequel just starting and then check the status so you can see we have it as running now we have 2 instance running as post gray sequel so check instances are running or not so we will say psi 1 EF grab post gray post master which is the main background process so you can see one post gray master is running out of piece equal 10 and another from 12 and the data directory are different now using the PG CTL we have to check this status of both the cluster so we'll say PG CTL and this has to be from the Postgres equal ten version and then we will give the location of the data directory and then just say status so you can say that PG CTL service is running then similarly we will do it for twelve so you just make sure that you give the location of your ten and twelve Postgres equal respectively so this is also running so what is running then what we are going to do is we have to stop both Postgres equal instances so using the PG CTL only we have to stop it so PG CTL - D data directory location and they'll just say stop so the Postgres equal ten instances stopped now we are stopping the Postgres equal twelve instance so PG CTL - d and data directory location and then stop it has done just check the status using the status command instead of stop we'll just say status it is not running similarly check it for twelve you can see that it is not running so both are not running we can check it from the background process which is psi phony F crap postmaster just execute it and there you can see there is no output this is just reflection of the image they come out which we executed now we will check if the cluster is ready for the upgrade or not so I'll explain the what is this all script is about so this is PG upgrade which is from the newer so slash USR PG sequel 12 in the PG underscore upgrade is from Postgres equal 12 - - older data directory what is the location of old data directory or the location of old data directory is this one - - new data directory this is the location of Postgres equal 12 and then we will give the location of o the binary location of Postgres equal to n so this is us our PG SQL 10 - bin then we will give the location of bin directory of Postgres equal 12 - C is just to check whether our cluster can be upgraded or not so you can see that checking cluster version then checking database user is install user or not then it is checking the connections whether it is it can't connect to both the instances or not then it is checking where the clusters are compatible whether it can be upgraded from newer version to higher version that is post Chris equal to 10 to 12 so everything is fine so now we will invoke the same command without the without - seek option so that means it is VG upgrade we have given the location of old data directory new data directory we have given the location of old binary directory and we have given the location of new one redirect tree so the first option will be for the existing environment and the next will be for the new environment and just press enter so this is going to take few minutes depending upon the size of the database you can see that creating dump of the database schema so it just taking all the databases one by one and this will copy and paste from post case equal 10 to 12 so behind the scenes it is doing a number of things it is analyzing it is restoring the object from one is from lower version to higher version so it has created two scripts to analyze the new cluster if in case you wanted to analyze it and the another script is used to delete the old cluster what we will do now is we will start the new cluster let's stop it first if it has started will say PG CTL - D and directory location and stop it is not running so we can say either it started or if it is not starting what we will do is we will change the okay server status is running and sometimes it creates the problem and what we have to do is in post case equal conf we have to change the the port number so the port number let's make it 5 4 3 2 as it was there in the older older what do you say the the post case equal 10 so let's let's start at stop and start again and we will try to connect it has stopped using the PG CTL again started it started let's try to log in psql just says - l and you will see all the databases DVD rental DVD rental understood new then now and there was a user also which is user to underscore remote if something is not migrated that it will it will show you like user to run the script so we are not going to analyze it now and I just wanted to keep it for some more time the older cluster and if you don't need it just you just go and delete it quit and delete the holder cluster so we have a new cluster now so I'll revisit what we have done is in our demo week we installed post Chris equal 10 we initialize the cluster then we imported some data which was having DVD rental database then we installed a newer version of the Postgres equal 12 then we we change the directory from 5 4 3 2 to 5 4 3 3 because we cannot run 2 instances on one one port then we started the post-race equal 12 then we stopped both the servers for a sequel ten and twelve using PT CTL command and it has to be from respective binary location then we using the PG upgrade command we mentioned the location of old directory of existing environment and new environment then we mentioned the bin directory location of older environment and newer environment and we mentioned the - C to check whether the cluster can be upgraded or not and finally we fired the same command without - C and we have graded the cluster within no time because the data was very less then we started the new cluster we changed the the port from 5 4 3 2 to 5 4 3 3 to 5 4 3 2 and then we were verified if the database has copied or not so this was all about the PG upgrade utility to upgrade a Postgres equal 10 to Postgres equal 12 on our HG CL 7.5 I hope this is going to help and all the commands will be there in the comment section and thanks for watching

Channel: E-Multi-Skills Database Tutorials

Views: 1,636

Rating: 5 out of 5

Keywords: pg_upgrade 10 to 11, pg_upgrade example, pg_upgrade tutorial, postgres pg_upgrade, how to upgrade pg-9023, how to upgrade postgresql from lower to higher version

Id: d4YjT8L7GHs

Length: 18min 54sec (1134 seconds)

Published: Sat Jul 25 2020