How to upgrade from Postgres 9.6 to Postgres13

[Music] hi my name is denis for this session i'll be showing you how to do a major pascal's upgrade from version 9.6 to version 13. we will be using a pg upgrade to do this and let me also discuss some of the best practices in process upgrade so what we're going to do is have postgas 9.6 installed and i want to upgrade this to version 13. i will be installing postgres 13 on the same server and then we will perform the upgrade using the latest version of pg upgrade utility command so for this demo we will not be doing the backup but please take note that backing up your database is a very important step before you do the upgrade okay you can now back up your database just using the internet approach it could be online logical or file system backup it's up to you as long as you can recover your database if anything goes wrong during the upgrade process and i think that will be fine also i'll be sharing with you some tips on how to roll it back if something goes wrong during the upgrade process so let's start let me connect to the target database with ip address 192.168.10.200. and let me key in my password and let's see the pascal's database version like mpsql minus c and then the select statement number shown and let's also check the data directory and make sure you know where your data directory is because we will be needing this information during the upgrade the sql minus c and then show the underscore the directory there you go and let me check my database so i have demo database and order entry database so as you can see i'm using poscas 9.6 and this is the data directory and these are my two databases installed in 9.6 when when you're doing the upgrade please make sure that the poscas version is compatible with your current os version it would be easy if you check and read the installation guide and i think that's the big way that's that's the best way to do it right so at this time i'm using centos version 7 and this is compatible with poscas13 so let's uh make sure that uh i have a correct version this one i'm using version seven so next thing to do is install process 13. you know we have a lot of choices to install pos quests you can use from compile source code or packages provided by your operating system distribution you can also download using apt and yum based uh pascal sql repository it depends on your system so for this demo let's uh just go to the posca sql.org website and from there you can select the version that you need and it will provide you the procedure how to install it and it's a very very easy way go to the website and just click on download then you can select your operating system send ask and then from here you can select your past customization so for this demo we will be using the latest version which is 13. and i'm using centos since i have um 64-bit platform this is the architecture there you go you can you know just copy and paste and you can proceed with the installation let me just jump into this one and let me first verify and making sure that there is no 13 here right so we only have 9.6 so since i already have i already installed the process rp and so we can proceed with the installation just type in c sudo yum install sy sql 13 server type in your password and then it will be installed so now we got 9.6 and 13 then let's initialize the database and the data directory will be created created using it utility command just type in sudo usr so we'll be using 13 13. f and database was already initialized cool so we got 9.6 and now we have 13. before we start the poscas instance let me change the default part number which is 5432 because 5432 is already used by postcasting.c so let me assign the part number 5433 for pascal's 30 because you know you can't run poscas on the same part right so let's just go to slash bar like g since we will be editing the configuration file in 13 let's go to this directory you can list down and edit or modify the posca sql.conf so for the part and then there you go change it and then assign five part kp and then once we change the part number you can start the process 13 service type into the system cdl start let's get this post yes turkey right [Music] let's check the status so now we have possibly started service active and running and we also have 9.6 active and running so now we can proceed with the upgrade so first thing to do is you need to stop these two services possible sql 9.6 and 13 services because password services should be down when you do the upgrade just issue these palin commands so let's try to stop to the system gpl stop pause class six and then sorry and then turkey after that the next thing to do before you upgrade is you need to perform the compatibility check using minus small c option don't worry this will not touch your old database cluster it will just run the consistency and compatibility check and this will tell you when something is wrong or if you're good to go for the upgrade so also make sure that you're gonna use the pg upgrade utility command from the latest version which is postcast 30. so since we will be using the uh pg upgrade from 13 so that's just 13 then this is the pg upgrade from pg sql 13. so now the next uh you have to uh type in minus v option for all binaries of postgres 9.6 and [Music] specify the binaries or the x executable directory for postgresql 9.6 and then minus capital b option for the new binary for postcast 30. minus small d option this is the old data directory of your postgres 9.6 right sql 9.6 and then the data directory for your old pascal's database minus capital d option this is the new directory of poscas 13 bar live gs 13 data there you go and minus the option this will check your database cluster for compatibility again it will check if your old version can be upgraded to the latest version so let's run so we got an error and not to my surprise because i you know intentionally forget what postcards extension which is the pj crypto it is simply because i want to show you that minus c option really perform the compatibility and consistency check and again this option really tell us if we are really ready for the update so let's check the error message as i said i have pgcrypto extension in my 9.6 database and it says that cannot access the pj crypto so please remember that when you have any non-default extension you will need to install them on the new database before you upgrade so on this them again it's pidgey crypto again you need to install all the poscas extension and their libraries in the new database before you can do the actual upgrade okay so let's install posca sql 13 uh country package and create a pcg crypto extension inside the database by the way pg crypto is a cryptographic extension that provides some hashing and cryptographic functions that most of the dbas are using so let's install it type in sudo [Music] yes type in yes there you go and let's start the posca started sudo systems ctl start plus 13. and then since i'm using 5433 for poscas 13 let me connect using minus the option for part number 543 and then let's see the extensions so currently we only have plpg sql and then let's create the extension for [Music] pgcrypto because we really need to fix first the error message before we can continue with the upgrade pg crypto okay so create a pgcrypto was already created and then let's stop the service and let's do it one more time with a minus the option create and then let's stop this and let's rerun the okay the same command with minus the option let's see if the error message will be removed there you go clusters are compatible and since we cannot find any error and this does ask the the cluster are compatible so we can actually ready to do the upgrade right so the same script or command but this time we will be removing minus the option it is really really fast right because i just have a very small database then uh well actually the upgrade process depends on the size of your database actually so once the upgrade is completed then we can start the uh basket sql 13. this is so let me check the status so basket basket skeleton service is now up and running as you can see after the upgrade it creates two scripts that you need to run after the update number one is to analyze the new cluster and it will provide a device or statistic otherwise your database will be slow and number two is the script the delete the all process database and i suggest that you do not delete your old database cluster unless you've done all the testing another thing is you need to consider is changing the posca sql.conf and pg underscore hpa code f as per your requirement after that we can modify the uh you know the connect stream connection strings using the new passcast database and you can start your application then you can connect to the upgraded podcast database that's it your pascus 9.6 is now upgraded to pos just 13 and ready to use so let me check if my two databases was also upgraded from 9.6 to version 13. uh 43. so as you can see my two databases are already here demo and order empty now if you need to roll back for some reason just simply stop all the application stop this podcast 13 then you can start your all passwords virtual repo which is on this case 9.6 and then you can restore the connection stream so on the application layer to connect to the all postcast database then it's done so before i end my demo let me just share you with some tips back up your database before you change add delete and modify this will save everyone you know you should know all database objects including your records number of rows functions everything inside the database create a you know a very detailed test plan and test cases before you delete your old podcast version and lastly keep your database up to date there are several improvements and fixes with the latest version of poscas and here are the steps that you can use as a guide for your update so thank you [Music]

Channel: Ashnik

Views: 1,927

Keywords: Big Data, MongoDB, Open source solutions, PostgreSQL

Id: 15Pwl30Dut0

Length: 20min 59sec (1259 seconds)

Published: Tue Sep 14 2021