QuestDB自动备份方案

✨ AI 智能摘要

AI 正在生成文章简介，请稍候...

网上的教程很少，好不容易找到一个还是需要安装pgsql的，太折腾了，既然9000端口的web可以执行命令，那应该就可以直接调用questdb的web api去执行备份命令，比安装pgsql的方案方便。

修改配置文件#

路径：questdb程序目录\bin\qdbroot\conf\server.conf 修改后记得重启questdb服务

修改配置文件

测试备份命令#

BACKUP TABLE 表名1, 表名2;

执行备份命令

执行后可以看到有对应的备份文件夹，如果执行多次就是年月日.1、年月日.2

执行备份命令

自动备份配置#

1
<?xml version="1.0" encoding="UTF-16"?>
2
<Task version="1.2" xmlns="http://schemas.microsoft.com/windows/2004/02/mit/task">
3
  <RegistrationInfo>
4
    <Date>2025-07-22T16:40:31.4762726</Date>
5
    <Author>DESKTOP-EGTT6MR\admin</Author>
6
    <URI>\MES\定时备份Questdb</URI>
7
  </RegistrationInfo>
8
  <Triggers>
9
    <CalendarTrigger>
10
      <StartBoundary>2025-07-22T17:15:00</StartBoundary>
11
      <Enabled>true</Enabled>
12
      <ScheduleByDay>
13
        <DaysInterval>1</DaysInterval>
14
      </ScheduleByDay>
15
    </CalendarTrigger>
16
  </Triggers>
17
  <Principals>
18
    <Principal id="Author">
19
      <UserId>S-1-5-21-3828290859-3366777425-2908561605-1001</UserId>
20
      <LogonType>Password</LogonType>
21
      <RunLevel>LeastPrivilege</RunLevel>
22
    </Principal>
23
  </Principals>
24
  <Settings>
25
    <MultipleInstancesPolicy>IgnoreNew</MultipleInstancesPolicy>
26
    <DisallowStartIfOnBatteries>true</DisallowStartIfOnBatteries>
27
    <StopIfGoingOnBatteries>true</StopIfGoingOnBatteries>
28
    <AllowHardTerminate>true</AllowHardTerminate>
29
    <StartWhenAvailable>false</StartWhenAvailable>
30
    <RunOnlyIfNetworkAvailable>false</RunOnlyIfNetworkAvailable>
31
    <IdleSettings>
32
      <StopOnIdleEnd>true</StopOnIdleEnd>
33
      <RestartOnIdle>false</RestartOnIdle>
34
    </IdleSettings>
35
    <AllowStartOnDemand>true</AllowStartOnDemand>
36
    <Enabled>true</Enabled>
37
    <Hidden>false</Hidden>
38
    <RunOnlyIfIdle>false</RunOnlyIfIdle>
39
    <WakeToRun>false</WakeToRun>
40
    <ExecutionTimeLimit>PT72H</ExecutionTimeLimit>
41
    <Priority>7</Priority>
42
  </Settings>
43
  <Actions Context="Author">
44
    <Exec>
45
      <Command>powershell</Command>
46
      <Arguments>-noprofile -executionpolicy bypass -file "E:\Program Files (x86)\questdb\questdb-backup.ps1"</Arguments>
47
      <WorkingDirectory>E:\Program Files (x86)\questdb</WorkingDirectory>
48
    </Exec>
49
  </Actions>
50
</Task>

保存这个xml文件：定时备份Questdb.xml

导入任务计划程序

修改执行用户身份

1
chcp 936 *>$null
2
# ====================== 参数区 ======================
3
$BACKUP_ROOT    = "D:\BackUPQuestdb" # 备份路径与server.conf相同
4
$RETENTION_DAYS = 7 # 只保留近7天的备份文件
5
$DbHost         = "localhost"
6
$HTTP_PORT      = 9000
7
$LOG_FILE       = Join-Path $BACKUP_ROOT ("backup_{0:yyyyMMdd}.log" -f (Get-Date))
8
# ====================================================
9

10
function Write-Log([string]$msg){
11
    $msg = "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')  $msg"
12
    Write-Host $msg
13
    Add-Content -Path $LOG_FILE -Value $msg
14
}
15

16
# 1. 建立/确认目录
17
if (-not (Test-Path $BACKUP_ROOT)) {
18
    New-Item -Path $BACKUP_ROOT -ItemType Directory -Force | Out-Null
19
}
20

21
Write-Log "===== 备份脚本开始 ====="
22

23
# 2. 删除过期目录
24
Get-ChildItem -Path $BACKUP_ROOT -Directory |
25
    Where-Object {
26
        $_.Name -match '^\d{4}-\d{1,2}-\d{1,2}(?:\..+)?$' -and
27
        [DateTime]($_.Name -replace '^(\d{4}-\d{1,2}-\d{1,2}).*$', '$1') -lt (Get-Date).AddDays(-$RETENTION_DAYS)
28
    } | ForEach-Object {
29
        Write-Log "删除目录 - $($_.FullName)"
30
        Remove-Item $_.FullName -Recurse -Force
31
    }
32

33
# 3. 触发 QuestDB 备份
34
Write-Log "开始 HTTP 备份请求"
35

36
$uri  = "http://${DbHost}:${HTTP_PORT}/exec"
37
$query = "BACKUP TABLE 表名1, 表名2;" # 改成你的表名
38

39
# 使用 WebUtility 做 URL 编码
40
$encodedQuery = [System.Net.WebUtility]::UrlEncode($query)
41
$fullUrl = "$uri`?query=$encodedQuery"
42

43
try {
44
    $resp = Invoke-WebRequest -Uri $fullUrl -UseBasicParsing -ErrorAction Stop
45
    Write-Log "备份请求成功：HTTP $($resp.StatusCode)"
46
}
47
catch {
48
    Write-Log "备份请求失败：$($_.Exception.Message)"
49
}
50

51
# 4. 删除过期日志（*.log）
52
Get-ChildItem -Path $BACKUP_ROOT -Filter *.log -Recurse -File |
53
    Where-Object {
54
        $_.Name -match '^backup_(?<dt>\d{8})\.log$' -and
55
        ($dt = [DateTime]::ParseExact($Matches['dt'],'yyyyMMdd',$null)) -lt (Get-Date).AddDays(-$RETENTION_DAYS)
56
    } | ForEach-Object {
57
        Write-Log "删除旧日志 - $($_.FullName)"
58
        Remove-Item $_.FullName -Force
59
    }
60

61
Write-Log "===== 备份脚本完成 ====="