Quickstart: Use an ARM template to create an Azure Database for PostgreSQL - single server

Article
08/22/2024

APPLIES TO: Azure Database for PostgreSQL - Single Server

Important

Azure Database for PostgreSQL - Single Server is on the retirement path. We strongly recommend that you upgrade to Azure Database for PostgreSQL - Flexible Server. For more information about migrating to Azure Database for PostgreSQL - Flexible Server, see What's happening to Azure Database for PostgreSQL Single Server?.

Azure Database for PostgreSQL is a managed service that you use to run, manage, and scale highly available PostgreSQL databases in the cloud. In this quickstart, you use an Azure Resource Manager template (ARM template) to create an Azure Database for PostgreSQL - single server in the Azure portal, PowerShell, or Azure CLI.

An Azure Resource Manager template is a JavaScript Object Notation (JSON) file that defines the infrastructure and configuration for your project. The template uses declarative syntax. You describe your intended deployment without writing the sequence of programming commands to create the deployment.

If your environment meets the prerequisites and you're familiar with using ARM templates, select the Deploy to Azure button. The template will open in the Azure portal.

Prerequisites

APPLIES TO: Azure Database for PostgreSQL - Single Server

Important

An Azure account with an active subscription. Create one for trial.

Review the template

You create an Azure Database for PostgreSQL server with a configured set of compute and storage resources. To learn more, see Pricing tiers in Azure Database for PostgreSQL - Single Server. You create the server within an Azure resource group.

The template used in this quickstart is from Azure Quickstart Templates.

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "serverName": {
      "type": "string",
      "metadata": {
        "description": "Server Name for Azure database for PostgreSQL"
      }
    },
    "administratorLogin": {
      "type": "string",
      "minLength": 1,
      "metadata": {
        "description": "Database administrator login name"
      }
    },
    "administratorLoginPassword": {
      "type": "securestring",
      "minLength": 8,
      "metadata": {
        "description": "Database administrator password"
      }
    },
    "skuCapacity": {
      "type": "int",
      "defaultValue": 2,
      "metadata": {
        "description": "Azure database for PostgreSQL compute capacity in vCores (2,4,8,16,32)"
      }
    },
    "skuName": {
      "type": "string",
      "defaultValue": "GP_Gen5_2",
      "metadata": {
        "description": "Azure database for PostgreSQL sku name "
      }
    },
    "skuSizeMB": {
      "type": "int",
      "defaultValue": 51200,
      "metadata": {
        "description": "Azure database for PostgreSQL Sku Size "
      }
    },
    "skuTier": {
      "type": "string",
      "defaultValue": "GeneralPurpose",
      "metadata": {
        "description": "Azure database for PostgreSQL pricing tier"
      }
    },
    "skuFamily": {
      "type": "string",
      "defaultValue": "Gen5",
      "metadata": {
        "description": "Azure database for PostgreSQL sku family"
      }
    },
    "postgresqlVersion": {
      "type": "string",
      "defaultValue": "11",
      "allowedValues": [
        "9.5",
        "9.6",
        "10",
        "11"
      ],
      "metadata": {
        "description": "PostgreSQL version"
      }
    },
    "location": {
      "type": "string",
      "defaultValue": "[resourceGroup().location]",
      "metadata": {
        "description": "Location for all resources."
      }
    },
    "backupRetentionDays": {
      "type": "int",
      "defaultValue": 7,
      "metadata": {
        "description": "PostgreSQL Server backup retention days"
      }
    },
    "geoRedundantBackup": {
      "type": "string",
      "defaultValue": "Disabled",
      "metadata": {
        "description": "Geo-Redundant Backup setting"
      }
    },
    "virtualNetworkName": {
      "type": "string",
      "defaultValue": "azure_postgresql_vnet",
      "metadata": {
        "description": "Virtual Network Name"
      }
    },
    "subnetName": {
      "type": "string",
      "defaultValue": "azure_postgresql_subnet",
      "metadata": {
        "description": "Subnet Name"
      }
    },
    "virtualNetworkRuleName": {
      "type": "string",
      "defaultValue": "AllowSubnet",
      "metadata": {
        "description": "Virtual Network RuleName"
      }
    },
    "vnetAddressPrefix": {
      "type": "string",
      "defaultValue": "10.0.0.0/16",
      "metadata": {
        "description": "Virtual Network Address Prefix"
      }
    },
    "subnetPrefix": {
      "type": "string",
      "defaultValue": "10.0.0.0/16",
      "metadata": {
        "description": "Subnet Address Prefix"
      }
    }
  },
  "variables": {
    "firewallrules": {
      "batch": {
        "rules": [
          {
            "Name": "rule1",
            "StartIpAddress": "0.0.0.0",
            "EndIpAddress": "255.255.255.255"
          },
          {
            "Name": "rule2",
            "StartIpAddress": "0.0.0.0",
            "EndIpAddress": "255.255.255.255"
          }
        ]
      }
    }
  },
  "resources": [
    {
      "type": "Microsoft.Network/virtualNetworks",
      "apiVersion": "2020-06-01",
      "name": "[parameters('virtualNetworkName')]",
      "location": "[parameters('location')]",
      "properties": {
        "addressSpace": {
          "addressPrefixes": [
            "[parameters('vnetAddressPrefix')]"
          ]
        }
      },
      "resources": [
        {
          "type": "subnets",
          "apiVersion": "2020-06-01",
          "name": "[parameters('subnetName')]",
          "location": "[parameters('location')]",
          "dependsOn": [
            "[parameters('virtualNetworkName')]"
          ],
          "properties": {
            "addressPrefix": "[parameters('subnetPrefix')]"
          }
        }
      ]
    },
    {
      "type": "Microsoft.DBforPostgreSQL/servers",
      "apiVersion": "2017-12-01",
      "name": "[parameters('serverName')]",
      "location": "[parameters('location')]",
      "sku": {
        "name": "[parameters('skuName')]",
        "tier": "[parameters('skuTier')]",
        "capacity": "[parameters('skuCapacity')]",
        "size": "[parameters('skuSizeMB')]",
        "family": "[parameters('skuFamily')]"
      },
      "properties": {
        "createMode": "Default",
        "version": "[parameters('postgresqlVersion')]",
        "administratorLogin": "[parameters('administratorLogin')]",
        "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
        "storageProfile": {
          "storageMB": "[parameters('skuSizeMB')]",
          "backupRetentionDays": "[parameters('backupRetentionDays')]",
          "geoRedundantBackup": "[parameters('geoRedundantBackup')]"
        }
      },
      "resources": [
        {
          "type": "virtualNetworkRules",
          "apiVersion": "2017-12-01",
          "name": "[parameters('virtualNetworkRuleName')]",
          "dependsOn": [
            "[resourceId('Microsoft.DBforPostgreSQL/servers/', parameters('serverName'))]"
          ],
          "properties": {
            "virtualNetworkSubnetId": "[resourceId('Microsoft.Network/virtualNetworks/subnets', parameters('virtualNetworkName'), parameters('subnetName'))]",
            "ignoreMissingVnetServiceEndpoint": true
          }
        }
      ]
    },
    {
      "type": "Microsoft.DBforPostgreSQL/servers/firewallRules",
      "apiVersion": "2017-12-01",
      "name": "[concat(parameters('serverName'),'/',variables('firewallrules').batch.rules[copyIndex()].Name)]",
      "location": "[parameters('location')]",
      "copy": {
        "name": "firewallRulesCopy",
        "mode": "Serial",
        "batchSize": 1,
        "count": "[length(variables('firewallrules').batch.rules)]"
      },
      "dependsOn": [
        "[resourceId('Microsoft.DBforPostgreSQL/servers/', parameters('serverName'))]"
      ],
      "properties": {
        "startIpAddress": "[variables('firewallrules').batch.rules[copyIndex()].StartIpAddress]",
        "endIpAddress": "[variables('firewallrules').batch.rules[copyIndex()].EndIpAddress]"
      }
    }
  ]
}

The template defines five Azure resources:

More Azure Database for PostgreSQL template samples can be found in Azure Quickstart Templates.

Deploy the template

APPLIES TO: Azure Database for PostgreSQL - Single Server

Important

Select the following link to deploy the Azure Database for PostgreSQL server template in the Azure portal:

On the Deploy Azure Database for PostgreSQL with VNet page:

For Resource group, select Create new, enter a name for the new resource group, and select OK.
If you created a new resource group, select a Location for the resource group and the new server.
Enter a Server Name, Administrator Login, and Administrator Login Password.
Change the other default settings if you want:
- Subscription: the Azure subscription you want to use for the server.
- Sku Capacity: the vCore capacity, which can be 2 (the default), 4, 8, 16, 32, or 64.
- Sku Name: the SKU tier prefix, SKU family, and SKU capacity, joined by underscores, such as B_Gen5_1, GP_Gen5_2 (the default), or MO_Gen5_32.
- Sku Size MB: the storage size, in megabytes, of the Azure Database for PostgreSQL server (default 51200).
- Sku Tier: the deployment tier, such as Basic, GeneralPurpose (the default), or MemoryOptimized.
- Sku Family: Gen4 or Gen5 (the default), which indicates hardware generation for server deployment.
- PostgreSQL Version: the version of PostgreSQL server to deploy, such as 9.5, 9.6, 10, or 11 (the default).
- Backup Retention Days: the desired period for geo-redundant backup retention, in days (default 7).
- Geo Redundant Backup: Enabled or Disabled (the default), depending on geo-disaster recovery (Geo-DR) requirements.
- Virtual Network Name: the name of the virtual network (default azure_postgresql_vnet).
- Subnet Name: the name of the subnet (default azure_postgresql_subnet).
- Virtual Network Rule Name: the name of the virtual network rule allowing the subnet (default AllowSubnet).
- Vnet Address Prefix: the address prefix for the virtual network (default 10.0.0.0/16).
- Subnet Prefix: the address prefix for the subnet (default 10.0.0.0/16).
Read the terms and conditions, and then select I agree to the terms and conditions stated above.
Select Purchase.

APPLIES TO: Azure Database for PostgreSQL - Single Server

Important

Use the following interactive code to create a new Azure Database for PostgreSQL server using the template. The code prompts you for the new server name, the name and location of a new resource group, and an administrator account name and password.

$serverName = Read-Host -Prompt "Enter a name for the new Azure Database for PostgreSQL server"
$resourceGroupName = Read-Host -Prompt "Enter a name for the new resource group where the server will exist"
$location = Read-Host -Prompt "Enter an Azure region (for example, chinanorth2) for the resource group"
$adminUser = Read-Host -Prompt "Enter the Azure Database for PostgreSQL server's administrator account name"
$adminPassword = Read-Host -Prompt "Enter the administrator password" -AsSecureString

New-AzResourceGroup -Name $resourceGroupName -Location $location # Use this command when you need to create a new resource group for your deployment
New-AzResourceGroupDeployment -ResourceGroupName $resourceGroupName `
    -TemplateUri https://raw.githubusercontent.com/Azure/azure-quickstart-templates/master/quickstarts/microsoft.dbforpostgresql/managed-postgresql-with-vnet/azuredeploy.json `
    -serverName $serverName `
    -administratorLogin $adminUser `
    -administratorLoginPassword $adminPassword

Read-Host -Prompt "Press [ENTER] to continue: "

APPLIES TO: Azure Database for PostgreSQL - Single Server

Important

read -p "Enter a name for the new Azure Database for PostgreSQL server:" serverName &&
read -p "Enter a name for the new resource group where the server will exist:" resourceGroupName &&
read -p "Enter an Azure region (for example, chinanorth2) for the resource group:" location &&
read -p "Enter the Azure Database for PostgreSQL server's administrator account name:" adminUser &&
read -p "Enter the administrator password:" adminPassword &&
params='serverName='$serverName' administratorLogin='$adminUser' administratorLoginPassword='$adminPassword &&
az group create --name $resourceGroupName --location $location &&
az deployment group create --resource-group $resourceGroupName --parameters $params --template-uri https://raw.githubusercontent.com/Azure/azure-quickstart-templates/master/quickstarts/microsoft.dbforpostgresql/managed-postgresql-with-vnet/azuredeploy.json &&
read -p "Press [ENTER] to continue: "

Review deployed resources

APPLIES TO: Azure Database for PostgreSQL - Single Server

Important

Follow these steps to see an overview of your new Azure Database for PostgreSQL server:

In the Azure portal, search for and select Azure Database for PostgreSQL servers.
In the database list, select your new server. The Overview page for your new Azure Database for PostgreSQL server appears.

APPLIES TO: Azure Database for PostgreSQL - Single Server

Important

Run the following interactive code to view details about your Azure Database for PostgreSQL server. You'll have to enter the name of the new server.

$serverName = Read-Host -Prompt "Enter the name of your Azure Database for PostgreSQL server"
Get-AzResource -ResourceType "Microsoft.DbForPostgreSQL/servers" -Name $serverName | ft
Read-Host -Prompt "Press [ENTER] to continue: "

APPLIES TO: Azure Database for PostgreSQL - Single Server

Important

Run the following interactive code to view details about your Azure Database for PostgreSQL server. You'll have to enter the name and the resource group of the new server.

read -p "Enter your Azure Database for PostgreSQL server name: " serverName &&
read -p "Enter the resource group where the Azure Database for PostgreSQL server exists: " resourcegroupName &&
az resource show --resource-group $resourcegroupName --name $serverName --resource-type "Microsoft.DbForPostgreSQL/servers" &&
read -p "Press [ENTER] to continue: "

Exporting ARM template from the portal

You can export an ARM template from the Azure portal. There are two ways to export a template:

Export from resource group or resource. This option generates a new template from existing resources. The exported template is a "snapshot" of the current state of the resource group. You can export an entire resource group or specific resources within that resource group.
Export before deployment or from history. This option retrieves an exact copy of a template used for deployment.

When exporting the template, in the "properties":{ } section of the PostgreSQL server resource you will notice that administratorLogin and administratorLoginPassword will not be included for security reasons. You MUST add these parameters to your template before deploying the template or the template will fail.

"resources": [
    {
      "type": "Microsoft.DBforPostgreSQL/servers",
      "apiVersion": "2017-12-01",
      "name": "[parameters('servers_name')]",
      "location": "chinaeast2",
      "sku": {
                "name": "B_Gen5_1",
                "tier": "Basic",
                "family": "Gen5",
                "capacity": 1
            },
      "properties": {
        "administratorLogin": "[parameters('administratorLogin')]",
        "administratorLoginPassword": "[parameters('administratorLoginPassword')]",

Clean up resources

When it's no longer needed, delete the resource group, which deletes the resources in the resource group.

APPLIES TO: Azure Database for PostgreSQL - Single Server

Important

In the Azure portal, search for and select Resource groups.
In the resource group list, choose the name of your resource group.
In the Overview page of your resource group, select Delete resource group.
In the confirmation dialog box, type the name of your resource group, and then select Delete.

APPLIES TO: Azure Database for PostgreSQL - Single Server

Important

$resourceGroupName = Read-Host -Prompt "Enter the Resource Group name"
Remove-AzResourceGroup -Name $resourceGroupName
Read-Host -Prompt "Press [ENTER] to continue: "

APPLIES TO: Azure Database for PostgreSQL - Single Server

Important

read -p "Enter the Resource Group name: " resourceGroupName &&
az group delete --name $resourceGroupName &&
read -p "Press [ENTER] to continue: "

Next steps

For a step-by-step tutorial that guides you through the process of creating a template, see:

Tutorial: Create and deploy your first ARM template

Quickstart: Use an ARM template to create an Azure Database for PostgreSQL - single server

Prerequisites

Review the template

Deploy the template

Review deployed resources

Exporting ARM template from the portal

Clean up resources

Next steps

Additional resources