How to restore DB from existing backup file for Azure managed PostgreSQL in terraform?

Question

I am using terraform for automating deployment in azure PG flexible server DB. But, in case of managed PostgreSQL, there is no option to crete DB from backup file.

resource "azurerm_postgresql_flexible_server_database" "db" {
  depends_on  = [azurerm_sql_firewall_rule.allowAzureServices]
  name        = var.db_name
  server_id   = azurerm_postgresql_flexible_server.postgreSQL.id
  collation   = "en_US.UTF8"
  charset     = "UTF8"

}

For SQL DB in azure terraform has support like mentioned below : azure sql db has https://www.terraform.io/docs/providers/azurerm/r/sql_database.html with "create_mode" = "restore"

But i didn't find any option for PostgreSql https://registry.terraform.io/providers/hashicorp/azurerm/3.37.0/docs/resources/postgresql_flexible_server_database

How to acheive creation of DB from restoring backup file using terraform?

Answer 1

Here are the backup and restore implementation for PostgreSQL Flexible server here is the code sample.

Initially we need to create a backup restore for PostgreSQL flexible server, it somewhat different than normal PostgreSQL server.

main tf file as follows

resource "azurerm_resource_group" "default" {
  name     = "*********"
  location = "West Europe"
}

resource "azurerm_virtual_network" "default" {
  name                = "demo-vnet"
  location            = azurerm_resource_group.default.location
  resource_group_name = azurerm_resource_group.default.name
  address_space       = ["10.0.0.0/16"]
}

resource "azurerm_network_security_group" "default" {
  name                = "demo-nsg"
  location            = azurerm_resource_group.default.location
  resource_group_name = azurerm_resource_group.default.name

  security_rule {
    name                       = "test123"
    priority                   = 100
    direction                  = "Inbound"
    access                     = "Allow"
    protocol                   = "Tcp"
    source_port_range          = "*"
    destination_port_range     = "*"
    source_address_prefix      = "*"
    destination_address_prefix = "*"
  }
}

resource "azurerm_subnet" "default" {
  name                 = "demo-subnets"
  virtual_network_name = azurerm_virtual_network.default.name
  resource_group_name  = azurerm_resource_group.default.name
  address_prefixes     = ["10.0.2.0/24"]
  service_endpoints    = ["Microsoft.Storage"]

  delegation {
    name = "fs"

    service_delegation {
      name = "Microsoft.DBforPostgreSQL/flexibleServers"

      actions = [
        "Microsoft.Network/virtualNetworks/subnets/join/action",
      ]
    }
  }
}

resource "azurerm_subnet_network_security_group_association" "default" {
  subnet_id                 = azurerm_subnet.default.id
  network_security_group_id = azurerm_network_security_group.default.id
}

resource "azurerm_private_dns_zone" "default" {
  name                = "demo-pdz.postgres.database.azure.com"
  resource_group_name = azurerm_resource_group.default.name

  depends_on = [azurerm_subnet_network_security_group_association.default]
}

resource "azurerm_private_dns_zone_virtual_network_link" "default" {
  name                  = "demo-pdzvnetlink.com"
  private_dns_zone_name = azurerm_private_dns_zone.default.name
  virtual_network_id    = azurerm_virtual_network.default.id
  resource_group_name   = azurerm_resource_group.default.name
}

resource "azurerm_postgresql_flexible_server" "default" {
  name                   = "demonew-server"
  resource_group_name    = azurerm_resource_group.default.name
  location               = azurerm_resource_group.default.location
  version                = "13"
  delegated_subnet_id    = azurerm_subnet.default.id
  private_dns_zone_id    = azurerm_private_dns_zone.default.id
  administrator_login    = "adminTerraform"
  administrator_password = "********"
  zone                   = "1"
  storage_mb             = 32768
  sku_name               = "GP_Standard_D2s_v3"
  backup_retention_days  = 7

  depends_on = [azurerm_private_dns_zone_virtual_network_link.default]
}

resource "azurerm_postgresql_flexible_server_database" "default" {
  name      = "demo-db"
  server_id = azurerm_postgresql_flexible_server.default.id
  collation = "en_US.UTF8"
  charset   = "UTF8"
}

provider file as follows:

terraform {
  
      required_version = "~>1.3.3"
      required_providers {
        azurerm = {
           source = "hashicorp/azurerm"
           version = ">=3.5.0"
             }
           }
 }

 provider "azurerm" {
    features {}
    skip_provider_registration = true
}

upon plan and apply here is the out put

Verificaiton from portal:

DB verification

Backup and restore configuration