Background
In case you meet the upgrade issue like us , you can follow the below step to recover the existing data to new installed Qlik Sense .
Powered by Moshow郑锴-CSDN博客
please follow below steps:
- pgsql dump backup
- backup table into sql by DBeaver
- stop all qlik sense services
- rename QlikShare folder to QlikShareXXX
- uninstall Qlik Sense (May 2023) with deleting all data
- restart computer
- pure installation for Qlik Sense (May 2024) with new database setup (please remember your database passw)
- ensure all Qlik Sense service are up after reinstall
- stop all Qlik Sense service except Qlik Sense Repository Database
- truncate the table with escape option in the table you want to import , in DBeaver
- rename all he backup SQL file with powershell #1 rename
- update all the backup SQL file with "SET session_replication_role = replica;" in the beginning powershell #1 append
- list and execute all the SQL file you want to import by powershell #3 execute SQL
- restart the computer
- check what amazing happens
- reimport the license and certificate
- verify the app and task , also user , data connection
- install the patching
- verify again
- notice the stakeholders
Backup all table SQL with DBeaver
Backup And Restore Commands
here are some useful dump and restore , also execute sql file script .
cd "C:\Program Files\Qlik\Sense\Repository\PostgreSQL\12.5\bin".\dropdb.exe -h localhost -p 4432 -U postgres QSR.\createdb.exe -h localhost -p 4432 -U postgres -T template0 QSR.\pg_restore.exe -h localhost -p 4432 -U postgres -d QSR E:\Tools\QSR_backup.tar.\pg_restore.exe -h localhost -p 4432 -U postgres -d QSR E:\Tools\QSR_backup_148_latest.tarcd C:\Program Files\Qlik\Sense\Repository\PostgreSQL\14\bin#dump bakcup.\pg_dump.exe -h localhost -p 4432 -U postgres -b -F t -f "E:\Tools\QSR_backup_v14_may2024.tar" QSR#execute single sql.\psql.exe -h localhost -p 4432 -U postgres -d QSR -f "E:\Tools\593_backup\sql2\AppContents_.sql"
Powershell #1 rename
下面是一个PowerShell脚本,它会遍历 E:\Tools\593_backup\csv 目录下的所有 CSV 文件,并重命名这些文件,去除文件名最前面的 _ 字符。
# Powered by Moshow@zhengkai.blog.csdn.net# Define the directory path$directoryPath = "E:\Tools\593_backup\csv"# Get all CSV files in the directory$csvFiles = Get-ChildItem -Path $directoryPath -Filter *.csv# Loop through each fileforeach ($file in $csvFiles) {# Get the current file name$currentFileName = $file.Name# Check if the file name starts with an underscoreif ($currentFileName.StartsWith("_")) {# Create the new file name by removing the leading underscore$newFileName = $currentFileName.Substring(1)# Define the full path for the new file name$newFilePath = Join-Path -Path $directoryPath -ChildPath $newFileName# Rename the fileRename-Item -Path $file.FullName -NewName $newFilePath}}
Powershell#2 append
下面是一个PowerShell脚本,它会遍历 E:\Tools\593_backup\sql 目录下的所有 SQL 文件,并在每个文件的开头写入 SET session_replication_role = replica; 并换行。
# Powered by Moshow@zhengkai.blog.csdn.net
# Define the directory path$directoryPath = "E:\Tools\593_backup\sql"# Get all SQL files in the directory$sqlFiles = Get-ChildItem -Path $directoryPath -Filter *.sql# Loop through each fileforeach ($file in $sqlFiles) {# Read the current content of the file$content = Get-Content -Path $file.FullName# Prepend the new line to the content$newContent = "SET session_replication_role = replica;" + [Environment]::NewLine + $content# Write the new content back to the fileSet-Content -Path $file.FullName -Value $newContent}
Powershell#3 Execute SQL
下面是一个PowerShell脚本,它会遍历 E:\Tools\593_backup\sql2 目录下的所有 SQL 文件,并针对每个文件执行命令 .\psql.exe -h localhost -p 4432 -U postgres -d QSR -f "E:\Tools\593_backup\sql2\"+文件名,同时把已执行过的文件名输出到 E:\Tools\593_backup\sql_result.txt。
# Replace with your actual passw , once you input the credential here , it will auto put into the pgsql without any manual input .
# Powered by Moshow@zhengkai.blog.csdn.net
# Define the directory paths$psqlDirectory = "C:\Program Files\Qlik\Sense\Repository\PostgreSQL\14\bin"$directoryPath = "E:\Tools\593_backup\sql2"$resultFile = "E:\Tools\593_backup\sql_result.txt"$pgPassword = "*********" # Replace with your actual passw# Change to the psql directorySet-Location -Path $psqlDirectory# Get all SQL files in the directory$sqlFiles = Get-ChildItem -Path $directoryPath -Filter *.sql# Loop through each fileforeach ($file in $sqlFiles) {# Define the full path for the SQL file$filePath = Join-Path -Path $directoryPath -ChildPath $file# Set the PGPASSWORD environment variable$env:PGPASSWORD = $pgPassword# Execute the psql command& .\psql.exe -h localhost -p 4432 -U postgres -d QSR -f $filePath# Check if the command was successfulif ($LASTEXITCODE -eq 0) {# Append the file name to the result fileAdd-Content -Path $resultFile -Value $file
License issue when enter QMC
go to DBeaver and search the "Licenses" table ,clean the record, should be only one record here .