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 .
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.tar
cd 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 file
foreach ($file in $csvFiles) {
# Get the current file name
$currentFileName = $file.Name
# Check if the file name starts with an underscore
if ($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 file
Rename-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 file
foreach ($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 file
Set-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 directory
Set-Location -Path $psqlDirectory
# Get all SQL files in the directory
$sqlFiles = Get-ChildItem -Path $directoryPath -Filter *.sql
# Loop through each file
foreach ($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 successful
if ($LASTEXITCODE -eq 0) {
# Append the file name to the result file
Add-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 .