{"type":"doc","content":[{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"In this page, we have laid out the steps to upgrade postgres using ","type":"text"},{"text":"pg_dump","type":"text","marks":[{"type":"code"}]},{"text":" and ","type":"text"},{"text":"pg_restore","type":"text","marks":[{"type":"code"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"This is a generic document, which you can use to upgrade from any version of postgres to any other version","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"We also talk a bit about Azure postgres and some ways to ensure your services dont update the database during upgrade","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"This upgrade procedures requires a downtime","type":"text"}]}]}]},{"type":"table","attrs":{"layout":"default","localId":"5eb12df1-1c25-4de5-b7cb-53a678d83073"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#deebff","rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"Sl No","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#deebff","rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"Task","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#deebff","rowspan":1,"colwidth":[334.0]},"content":[{"type":"paragraph","content":[{"text":"Command","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#deebff","rowspan":1,"colwidth":[293.0]},"content":[{"type":"paragraph","content":[{"text":"Comments","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"0","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"Create a new postgres instance","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[334.0]},"content":[{"type":"paragraph","content":[{"text":"Choose postgres version 10 (preferably 11 ) and above and same configuration as per existing instance on Azure","type":"text"}]},{"type":"paragraph","content":[{"text":"You are free to have independent instances or a single instance for all services as per your need","type":"text"}]},{"type":"paragraph","content":[{"text":"Whitelist all the IP's as per existing postgres","type":"text"}]},{"type":"paragraph","content":[{"text":"Test the connection by connecting to the instance from jenkins","type":"text"}]},{"type":"paragraph","content":[{"text":"If on VM, create a new VM (preferably Ubuntu 18 and above) and run the Postgres provision role.","type":"text"}]},{"type":"paragraph","content":[{"text":"Note: This document does not cover details on how to upgrade postgres on the same / exisiting VM. Feel free to contribute the steps to this document.","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[293.0]},"content":[{"type":"paragraph","content":[{"text":"New instance of same configuration should be created. We have used Postgres version 11","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"1","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"Update the inventory","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[334.0]},"content":[{"type":"paragraph","content":[{"text":"Search and update all the variables in ","type":"text"},{"text":"common.yml","type":"text","marks":[{"type":"code"}]},{"text":", ","type":"text"},{"text":"secrets.yml","type":"text","marks":[{"type":"code"}]},{"text":" Core and DataPipeline directory in private repository","type":"text"}]},{"type":"paragraph","content":[{"text":"Replace the postgres instance address, postgres user and postgres password with the new details","type":"text"}]},{"type":"paragraph","content":[{"text":"Also look at variable ","type":"text"},{"text":"druid_postgres_user","type":"text","marks":[{"type":"code"}]},{"text":" in case you are using Azure postgres. If you are using a common username and password per instance, then this variable needs to be added and the value should be your instance username. This variable will default to ","type":"text"},{"text":"druid@instance","type":"text","marks":[{"type":"code"}]}]},{"type":"paragraph","content":[{"text":"If you are on a VM, use the new VM IP along with username and password","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[293.0]},"content":[{"type":"paragraph","content":[{"text":"It will be simpler if you use same username and password for all dbs on the postgres instance. ","type":"text"}]},{"type":"paragraph","content":[{"text":"If you want to use different user name and password for each database on an instance, then you need to ensure you create those users and roles before hand by running provision jobs against your postgres instance or do them manually. ","type":"text"}]},{"type":"paragraph","content":[{"text":"This document does not cover how to restore user accounts. For that you can refer to Azure docs or postgres documentation. Below is one such link -","type":"text"}]},{"type":"paragraph","content":[{"text":"https://docs.microsoft.com/en-us/azure/postgresql/how-to-upgrade-using-dump-and-restore","type":"text","marks":[{"type":"strong"},{"type":"link","attrs":{"href":"https://docs.microsoft.com/en-us/azure/postgresql/how-to-upgrade-using-dump-and-restore"}}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"2","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"Start of down time","type":"text"}]},{"type":"paragraph","content":[{"text":"Stop traffic / services","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[334.0]},"content":[{"type":"paragraph","content":[{"text":"Stop traffic","type":"text"}]},{"type":"paragraph","content":[{"text":"Put Jenkins into maintenance mode so nobody can deploy any jobs ","type":"text"}]},{"type":"paragraph","content":[{"text":"Cut off connection to postgres from all services except Jenkins (In azure remove all connecting subnets except Jenkins, disable Azure services connections also)","type":"text"}]},{"type":"paragraph","content":[{"text":"In VM you have multiple ways - Disable outside connections by editing ","type":"text"},{"text":"pg_hba.conf","type":"text","marks":[{"type":"code"}]},{"text":" so only ","type":"text"},{"text":"localhost","type":"text","marks":[{"type":"code"}]},{"text":" connections are accepted. Or stop the below services so that they dont update postgres database","type":"text"}]},{"type":"paragraph","content":[{"text":"List of services that are going to be affected on Sunbird -","type":"text"}]},{"type":"codeBlock","attrs":{"language":"yaml"},"content":[{"text":"- Deploy/DataPipeline/AnalyticsCore\n- Deploy/DataPipeline/CoreDataProducts\n- Deploy/DataPipeline/EdDataProducts\n- Deploy/DataPipeline/FlinkPipelineJobs\n- Deploy/Datapipeline/InternalKong\n- Deploy/Kubernetes/Analytics\n- Deploy/Kubernetes/APIManager\n- Deploy/Kubernetes/Enc\n- Deploy/Kubernetes/HawkeyeSuperset\n- Deploy/Kubernetes/Keycloak\n- Deploy/Kubernetes/Learner\n- Deploy/Kubernetes/LMS\n- Deploy/Kubernetes/Report\n- Deploy/UCI/fusionauth\n- Deploy/UCI/gql\n- Deploy/UCI/Inbound\n- Deploy/UCI/odk\n- Deploy/UCI/Orchestrator\n- Deploy/UCI/Outbound\n- Deploy/UCI/Transformer\n- Deploy/UCI/UCI\n- DataPipeline/Druid\n- Superset","type":"text"}]},{"type":"paragraph","content":[{"text":"List of services that are going to be affected on Vidyadaan -","type":"text"}]},{"type":"codeBlock","content":[{"text":"- Deploy/Kubernetes/APIManager\n- Deploy/Kubernetes/Enc\n- Deploy/Kubernetes/Opensaber\n- Deploy/Kubernetes/Program\n- Deploy/Kubernetes/HawkeyeSuperset\n- Deploy/Kubernetes/PostgresqlMigration\n- Deploy/DataPipeline/EdDataProducts","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[293.0]},"content":[{"type":"paragraph","content":[{"text":"Respective services should be stopped / Postgres reachability should be removed","type":"text"}]},{"type":"paragraph","content":[{"text":"Traffic can be stopped based on situation by removing nginx daemonset or changing nginx service port mapping to something else from 80 / 443","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"3","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"Manually list all the DB's first","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[334.0]},"content":[{"type":"codeBlock","attrs":{"language":"shell"},"content":[{"text":"export PG_HOST=\"\" # Enter the postgres host inside the quotes\nexport PG_USER=\"\" # Enter the postgres user inside the quotes\nexport PGPASSWORD=\"\" # Enter the postgres password inside the quotes\n\npsql -h $PG_HOST -U $PG_USER -d postgres -c \"\\l\"","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[293.0]},"content":[{"type":"paragraph","content":[{"text":"All DB should be listed","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"4","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"Run command to get all the DB's on terminal and store in a file","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[334.0]},"content":[{"type":"codeBlock","attrs":{"language":"shell"},"content":[{"text":"mkdir $PG_HOST-$(date +'%s') && cd $PG_HOST-$(date +'%s')\n\npsql -h $PG_HOST -U $PG_USER -d postgres -c \"\\l\" | awk 'NR>3{print $1}'| grep -v \"|\\|(\\|^$\\|template0\\|template1\\|azure_maintenance\\|postgres\\|azure_sys\" | tee -a dbs.txt","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[293.0]},"content":[{"type":"paragraph","content":[{"text":"Verify all db are present in the file and matches our db's","type":"text"},{"type":"hardBreak"},{"text":"Ignore db's like postgres, template0, template1, azure_maintanance and also remove not required DB's from the file","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"5","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"Take backup of the DB's from current instance","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[334.0]},"content":[{"type":"codeBlock","content":[{"text":"while read -r line; do echo \"Dumping DB $line\" && pg_dump -Fd -j PG_CPU_CORES -h $PG_HOST -U $PG_USER -d $line -f $line; done < dbs.txt","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[293.0]},"content":[{"type":"paragraph","content":[{"text":"Replace ","type":"text"},{"text":"PG_CPU_CORES","type":"text","marks":[{"type":"code"}]},{"text":" with the number of cores of the postgres insatnce. ","type":"text"},{"type":"hardBreak"},{"text":"Example: For 4 core, value will be 4","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"6","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"Get count of all tables from all DB's from current instance","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[334.0]},"content":[{"type":"codeBlock","content":[{"text":"while read -r line; do psql -h $PG_HOST -U $PG_USER -d $line -c \"\\dt\" | awk -F \"|\" 'NF {print $2}' | tr -d ' ' | awk 'NF' | tail -n +2 | tee -a $line-tables.txt; done < dbs.txt\n\nwhile read -r line; do while read -r inline; do echo $inline | tee -a $line-table-count.txt && psql -h $PG_HOST -U $PG_USER -d $line -c \"SELECT COUNT(*) FROM \\\"$inline\\\"\" | tee -a $line-table-count.txt; done < $line-tables.txt; done < dbs.txt","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[293.0]},"content":[{"type":"paragraph","content":[{"text":"All tables and counts will be displayed and also written into the files","type":"text"}]},{"type":"paragraph","content":[{"text":"Repeat Steps 3 - 6 for every postgres instance (in case you are running the workload on separate instances)","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"7","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"Rearrange the DBs across two instances or based on your requirement","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[334.0]},"content":[{"type":"paragraph","content":[{"text":"In this case, we will be creating the following databases across two instances","type":"text"}]},{"type":"paragraph","content":[{"text":"Instance 1 - Keycloak, Public Kong, Private Kong, Quartz, Enc Keys","type":"text"},{"type":"hardBreak"},{"text":"Instance 2 - Analytics, Druid, Graphite","type":"text"}]},{"type":"paragraph","content":[{"text":"Feel free to add any other dbs you have / want to. This is not an exhaustive list","type":"text"}]},{"type":"paragraph","content":[{"text":"Create two directories as below","type":"text"}]},{"type":"codeBlock","attrs":{"language":"shell"},"content":[{"text":"mkdir pg11\nmkdir pg11-dp\ncd pg11\ntouch dbs.txt","type":"text"}]},{"type":"paragraph","content":[{"text":"Add the required db names in the file ","type":"text"},{"text":"dbs.txt","type":"text","marks":[{"type":"code"}]},{"text":". See next column for sample reference","type":"text"}]},{"type":"paragraph","content":[{"text":"Move all the required backup folders and files to this directory. Below is a sample command","type":"text"}]},{"type":"codeBlock","content":[{"text":"mv old_pg_backup_folder/api_manager_kong14* pg11","type":"text"}]},{"type":"paragraph","content":[{"text":"Similarly do for the other dbs and pg11-db folder also (second instance)","type":"text"}]},{"type":"codeBlock","content":[{"text":"cd pg11-dp\ntouch dbs.txt","type":"text"}]},{"type":"paragraph","content":[{"text":"Add the required db names in the file ","type":"text"},{"text":"dbs.txt","type":"text","marks":[{"type":"code"}]},{"text":". See next column for sample reference","type":"text"}]},{"type":"paragraph","content":[{"text":"If you are going to use only once instance, then you can move all the backup data and other files under a single directory","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[293.0]},"content":[{"type":"paragraph","content":[{"text":"Instance 1:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"shell"},"content":[{"text":"cat pg11/dbs.txt \napi_manager_kong14\nkeycloak7\nquartz\napi_manager_internal\ndev-keys\n\nls -lrth pg11\napi_manager_internal\napi_manager_internal-table-count.txt\napi_manager_internal-tables.txt\napi_manager_kong14\napi_manager_kong14-table-count.txt\napi_manager_kong14-tables.txt\nkeycloak7\nkeycloak7-table-count.txt\nkeycloak7-tables.txt\ndev-keys\ndev-keys-table-count.txt\ndev-keys-tables.txt\nquartz\nquartz-table-count.txt\nquartz-tables.txt\ndbs.txt","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Instance 2:","type":"text"}]},{"type":"codeBlock","content":[{"text":"cat pg11-dp/dbs.txt \nanalytics\ndruid-raw\ngraphite\nsuperset\n\nls -lrth pg11-dp\nanalytics\nanalytics-table-count.txt\nanalytics-tables.txt\ndruid-raw\ndruid-raw-table-count.txt\ndruid-raw-tables.txt\ngraphite\ngraphite-table-count.txt\ngraphite-tables.txt\nsuperset\nsuperset-tables.txt\ndbs.txt","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"8","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"Install postgres 11 tools","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[334.0]},"content":[{"type":"codeBlock","content":[{"text":"sudo apt install postgresql-client-11","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[293.0]},"content":[{"type":"paragraph","content":[{"text":"If you to use ","type":"text"},{"text":"pg_dump","type":"text","marks":[{"type":"code"}]},{"text":" and ","type":"text"},{"text":"pg_restore","type":"text","marks":[{"type":"code"}]},{"text":" on older postgres version, install those packages","type":"text"}]},{"type":"paragraph","content":[{"text":"sudo apt install postgresql-client-9.6","type":"text","marks":[{"type":"code"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"9","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"Create empty databases in new instance","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[334.0]},"content":[{"type":"codeBlock","attrs":{"language":"shell"},"content":[{"text":"export PG_HOST=\"\" # Enter the new postgres host inside the quotes\nexport PG_USER=\"\" # Enter the new postgres user inside the quotes\nexport PGPASSWORD=\"\" # Enter the new postgres password inside the quotes\npsql -h $PG_HOST -U $PG_USER -d postgres -c \"\\l\"\n\nwhile read -r line; do echo \"Creating DB $line\" && psql -h $PG_HOST -U $PG_USER -d postgres -c \"CREATE DATABASE $line\"; done < dbs.txt\n \npsql -h $PG_HOST -U $PG_USER -d postgres -c \"\\l\"","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[293.0]},"content":[{"type":"paragraph","content":[{"text":"Empty databases should be created on new postgres instance","type":"text"}]},{"type":"paragraph","content":[{"text":"Note: Add double quotes in ","type":"text","marks":[{"type":"strong"}]},{"text":"dbs.txt","type":"text","marks":[{"type":"code"}]},{"text":" if you have hyphen in DB name ","type":"text","marks":[{"type":"strong"}]},{"text":"\"DB-NAME\"","type":"text","marks":[{"type":"code"}]},{"text":" or else you will receive below error","type":"text","marks":[{"type":"strong"}]}]},{"type":"codeBlock","content":[{"text":"Creating DB loadtest-keys\nERROR: syntax error at or near \"-\"\nLINE 1: CREATE DATABASE loadtest-keys","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"10","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"Restore the DB's to the new instance","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[334.0]},"content":[{"type":"codeBlock","content":[{"text":"while read -r line; do echo \"Restoring DB $line\" && pg_restore -O -j PG_CPU_CORES -h $PG_HOST -U $PG_USER -d $line $line; done < dbs.txt","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[293.0]},"content":[{"type":"paragraph","content":[{"text":"Replace ","type":"text"},{"text":"PG_CPU_CORES","type":"text","marks":[{"type":"code"}]},{"text":" with the number of cores of the postgres insatnce. ","type":"text"},{"type":"hardBreak"},{"text":"Example","type":"text","marks":[{"type":"strong"}]},{"text":": For 4 core, value will be 4","type":"text"}]},{"type":"paragraph","content":[{"text":"Note: Remove double quotes in ","type":"text","marks":[{"type":"strong"}]},{"text":"dbs.txt","type":"text","marks":[{"type":"code"}]},{"text":" if you have hyphen in DB name ","type":"text","marks":[{"type":"strong"}]},{"text":"\"DB-NAME\"","type":"text","marks":[{"type":"code"}]},{"text":" or else you will receive below error","type":"text","marks":[{"type":"strong"}]}]},{"type":"codeBlock","content":[{"text":"Restoring DB \"uci-botdb\"\npg_restore: [archiver] could not open input file \"\"uci-botdb\"\": No such file or directory","type":"text"}]},{"type":"paragraph","content":[{"text":"Ignore errors like","type":"text"}]},{"type":"codeBlock","content":[{"text":"ERROR: role \"postgres\" does not exist \n\npg_restore: [archiver (db)] Error while PROCESSING TOC:\npg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public azure_superuser\npg_restore: [archiver (db)] could not execute query: ERROR: schema \"public\" already exists\n Command was: CREATE SCHEMA public;\n \npg_restore: [archiver (db)] Error while PROCESSING TOC:\npg_restore: [archiver (db)] Error from TOC entry 3728; 0 0 ACL SCHEMA public azure_superuser\npg_restore: [archiver (db)] could not execute query: ERROR: role \"postgres\" does not exist\n Command was: GRANT ALL ON SCHEMA public TO postgres;","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"11","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"Analyze the db's on new instance","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[334.0]},"content":[{"type":"codeBlock","content":[{"text":"while read -r line; do psql -h $PG_HOST -U $PG_USER -d $line -c \"ANALYZE VERBOSE\"; done < dbs.txt","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[293.0]},"content":[{"type":"paragraph","content":[{"text":"Verbose logs will be displayed","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"12","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"Get count of all tables from all DB's from new instance","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[334.0]},"content":[{"type":"codeBlock","content":[{"text":"while read -r line; do while read -r inline; do echo $inline | tee -a $line-table-count-new.txt && psql -h $PG_HOST -U $PG_USER -d $line -c \"SELECT COUNT(*) FROM \\\"$inline\\\"\" | tee -a $line-table-count-new.txt; done < $line-tables.txt; done < dbs.txt","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[293.0]},"content":[{"type":"paragraph","content":[{"text":"All tables and counts will be displayed and also written into the files","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"13","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"Compare the row counts of both the instances","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[334.0]},"content":[{"type":"codeBlock","content":[{"text":"while read -r line; do echo \"Diff of $line-table-count.txt and $line-table-count-new.txt\" && diff $line-table-count.txt $line-table-count-new.txt; done < dbs.txt","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[293.0]},"content":[{"type":"paragraph","content":[{"text":"This will not display any output. Which means files are identical","type":"text"}]},{"type":"paragraph","content":[{"text":"If there are differences use the below to compare and then take fresh backup and restore those dbs again","type":"text"},{"type":"hardBreak"},{"text":"diff -y file1 file2","type":"text","marks":[{"type":"code"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"14","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"Remove all connections to old Postgres","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[334.0]},"content":[{"type":"paragraph","content":[{"text":"Remove all subnets and enable deny public network acces on Azure portal","type":"text"}]},{"type":"paragraph","content":[{"text":"If on VM, stop the VM / postgres service","type":"text"}]},{"type":"paragraph","content":[{"text":"In case of Azure, make sure you update the Backup and Log retention days to a large number","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[293.0]},"content":[{"type":"paragraph","content":[{"text":"To ensure no service can connect to old DBs","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"15","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"Clear offline session from Keycloak DB on Sunbird","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[334.0]},"content":[{"type":"codeBlock","content":[{"text":"truncate offline_client_session;\ntruncate offline_user_session;","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#e8f0fe","rowspan":1,"colwidth":[293.0]},"content":[{"type":"paragraph","content":[{"text":"If we have too many rows in these tables, keycloak will not start","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[48.0]},"content":[{"type":"paragraph","content":[{"text":"16","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"End of down time Redeploy services / Enable Traffic","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[334.0]},"content":[{"type":"paragraph","content":[{"text":"Redeploy the following services in case of Sunbird:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"yaml"},"content":[{"text":"- Deploy/DataPipeline/AnalyticsCore\n- Deploy/DataPipeline/CoreDataProducts\n- Deploy/DataPipeline/EdDataProducts\n- Deploy/DataPipeline/FlinkPipelineJobs (Choose all in job_names_to_deploy)\n- Deploy/Datapipeline/InternalKong\n- Deploy/Kubernetes/Analytics\n- Deploy/Kubernetes/APIManager\n- Deploy/Kubernetes/Enc\n- Deploy/Kubernetes/HawkeyeSuperset\n- Deploy/Kubernetes/Keycloak\n- Deploy/Kubernetes/Learner\n- Deploy/Kubernetes/LMS\n- Deploy/Kubernetes/Report\n- Deploy/UCI/fusionauth\n- Deploy/UCI/gql\n- Deploy/UCI/Inbound\n- Deploy/UCI/odk\n- Deploy/UCI/Orchestrator\n- Deploy/UCI/Outbound\n- Deploy/UCI/Transformer\n- Deploy/UCI/UCI\n- Provision/DataPipeline/Druid (Choose all in service except java)\n- Superset - No Jenkins Job","type":"text"}]},{"type":"paragraph","content":[{"text":"Redeploy the following services in case of Vidyadaan:","type":"text"}]},{"type":"codeBlock","content":[{"text":"- Deploy/Kubernetes/APIManager\n- Deploy/Kubernetes/Enc\n- Deploy/Kubernetes/Opensaber\n- Deploy/Kubernetes/Program\n- Deploy/Kubernetes/HawkeyeSuperset\n- Deploy/Kubernetes/PostgresqlMigration\n- Deploy/DataPipeline/EdDataProducts","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#ffffff","rowspan":1,"colwidth":[293.0]},"content":[{"type":"paragraph","content":[{"text":"The configmaps and configuration files will be updated with new data. Do verify it by checking them manually for all these services.","type":"text"}]},{"type":"paragraph","content":[{"text":"Note: If during deployment, any ansible errors occur due to missing variables or incorrect variables, you will have to fix them and move forward","type":"text","marks":[{"type":"strong"}]}]}]}]}]},{"type":"paragraph"}],"version":1}