如何在Azure PostgreSQL单服务器上调度刷新物化视图？

因为Azure PostgreSQL单服务器不支持pg_cron扩展，以下是几个可行的替代方案：

1. 使用Azure自动化账户（Azure Automation） #

• 创建Azure自动化账户，启用系统分配的托管身份，并为该身份授予PostgreSQL服务器的db_owner或具备足够权限的角色（通过Azure AD认证）。
• 创建Python或PowerShell运行手册，编写脚本连接数据库并执行刷新命令：
  Python脚本示例：

  import psycopg2
  from azure.identity import DefaultAzureCredential
  
  # 配置数据库信息
  server = "your-server-name.postgres.database.azure.com"
  database = "your-database-name"
  user = "your-azure-ad-identity@your-server-name"
  
  # 通过Azure AD获取访问令牌
  credential = DefaultAzureCredential()
  token = credential.get_token("https://ossrdbms-aad.database.windows.net/.default").token
  
  # 连接并刷新物化视图
  conn = psycopg2.connect(
      host=server,
      database=database,
      user=user,
      password=token,
      sslmode="require"
  )
  cur = conn.cursor()
  cur.execute("REFRESH MATERIALIZED VIEW your_materialized_view_name;")
  conn.commit()
  
  cur.close()
  conn.close()
  

• 为运行手册添加定时触发器，设置需要的调度频率（如每天凌晨2点）。

2. 使用Azure函数（Azure Functions） #

• 创建一个带有Timer触发器的Azure函数，通过CRON表达式自定义调度时间（例如0 0 2 * * *表示每天凌晨2点执行）。
• 在函数代码中编写逻辑连接PostgreSQL，执行REFRESH MATERIALIZED VIEW命令。推荐使用Azure AD认证或Azure Key Vault存储数据库凭据，避免硬编码敏感信息。
• 示例C#片段（也可使用Python/JavaScript等）：

  using Npgsql;
  using Azure.Identity;
  
  public static void Run(TimerInfo myTimer, ILogger log)
  {
      var server = "your-server-name.postgres.database.azure.com";
      var database = "your-database-name";
      var user = "your-azure-ad-identity@your-server-name";
      var credential = new DefaultAzureCredential();
      var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://ossrdbms-aad.database.windows.net/.default" }));
  
      using var conn = new NpgsqlConnection($"Host={server};Database={database};Username={user};Password={token.Token};Ssl Mode=Require");
      conn.Open();
      using var cmd = new NpgsqlCommand("REFRESH MATERIALIZED VIEW your_materialized_view_name;", conn);
      cmd.ExecuteNonQuery();
  }
  

3. 外部定时任务（如本地/VM上的cron） #

• 如果有可用的虚拟机或本地服务器，可设置cron任务（Linux）或任务计划（Windows），定期通过psql命令执行刷新：
  Linux cron示例（编辑crontab -e添加）：

  0 2 * * * psql "host=your-server.postgres.database.azure.com port=5432 dbname=your-db user=your-user@your-server password=$(cat /path/to/secure/password/file) sslmode=require" -c "REFRESH MATERIALIZED VIEW your_mv_name;"
  

  • 注意：务必通过密钥管理工具或加密文件存储密码，禁止硬编码。

4. 应用层触发刷新 #

• 如果不需要严格定时，可在应用访问物化视图前，检查数据时效性（例如通过视图的最后更新时间字段），按需执行刷新命令；或者在应用内部添加定时逻辑（如使用Quartz.NET、APScheduler等调度库）触发刷新。

内容的提问来源于stack exchange，提问作者ABC