Qlik Sense New Install with Restore

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 .

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