Azure Database for PostgreSQL

Prerequisites

The Azure Database for PostgreSQL is a manage database hosted on Azure, which can then be used as the L7|ESP backend database

  • Azure Subscription

CLI tools

The az command line tools will be used to programmatically access/manage the PostgreSQL instance and the Azure subscription. To install the CLI tool, follow the link below and choose the correct operating sytem:

ARM template

The latest ARM template is as follows:

 1param administratorLogin string
 2
 3@secure()
 4param administratorLoginPassword string
 5param location string = resourceGroup().location
 6param serverName string
 7param serverEdition string = 'GeneralPurpose'
 8param skuSizeGB int = 128
 9param dbInstanceType string = 'Standard_D4ds_v4'
10param haMode string = 'ZoneRedundant'
11param availabilityZone string = '1'
12param version string = '12'
13param virtualNetworkExternalId string = ''
14param subnetName string = ''
15param privateDnsZoneArmResourceId string = ''
16
17resource serverName_resource 'Microsoft.DBforPostgreSQL/flexibleServers@2021-06-01' = {
18  name: serverName
19  location: location
20  sku: {
21    name: dbInstanceType
22    tier: serverEdition
23  }
24  properties: {
25    version: version
26    administratorLogin: administratorLogin
27    administratorLoginPassword: administratorLoginPassword
28    network: {
29      delegatedSubnetResourceId: (empty(virtualNetworkExternalId) ? json('null') : json('${virtualNetworkExternalId}/subnets/${subnetName}'))
30      privateDnsZoneArmResourceId: (empty(virtualNetworkExternalId) ? json('null') : privateDnsZoneArmResourceId)
31    }
32    highAvailability: {
33      mode: haMode
34    }
35    storage: {
36      storageSizeGB: skuSizeGB
37    }
38    backup: {
39      backupRetentionDays: 7
40      geoRedundantBackup: 'Disabled'
41    }
42    availabilityZone: availabilityZone
43  }
44}

 1{
 2  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
 3  "contentVersion": "1.0.0.0",
 4  "parameters": {
 5    "administratorLogin": {
 6      "type": "string",
 7    },
 8    "administratorLoginPassword": {
 9      "type": "secureString"
10    },
11    "location": {
12      "type": "string",
13      "defaultValue": "[resourceGroup().location]"
14    },
15    "serverName": {
16      "type": "string"
17    },
18    "serverEdition": {
19      "type": "string",
20      "defaultValue": "GeneralPurpose"
21    },
22    "skuSizeGB": {
23      "type": "int",
24      "defaultValue": 128
25    },
26    "dbInstanceType": {
27      "type": "string",
28      "defaultValue": "Standard_D4ds_v4"
29    },
30    "haMode": {
31      "type": "string",
32      "defaultValue": "ZoneRedundant"
33    },
34    "availabilityZone": {
35      "type": "string",
36      "defaultValue": "1"
37    },
38    "version": {
39      "type": "string",
40      "defaultValue": "12"
41    },
42    "virtualNetworkExternalId": {
43      "type": "string",
44      "defaultValue": ""
45    },
46    "subnetName": {
47      "type": "string",
48      "defaultValue": ""
49    },
50    "privateDnsZoneArmResourceId": {
51      "type": "string",
52      "defaultValue": ""
53    }
54  },
55  "resources": [
56    {
57      "type": "Microsoft.DBforPostgreSQL/flexibleServers",
58      "apiVersion": "2021-06-01",
59      "name": "[parameters('serverName')]",
60      "location": "[parameters('location')]",
61      "sku": {
62        "name": "[parameters('dbInstanceType')]",
63        "tier": "[parameters('serverEdition')]"
64      },
65      "properties": {
66        "version": "[parameters('version')]",
67        "administratorLogin": "[parameters('administratorLogin')]",
68        "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
69        "network": {
70          "delegatedSubnetResourceId": "[if(empty(parameters('virtualNetworkExternalId')), json('null'), json(format('{0}/subnets/{1}', parameters('virtualNetworkExternalId'), parameters('subnetName'))))]",
71          "privateDnsZoneArmResourceId": "[if(empty(parameters('virtualNetworkExternalId')), json('null'), parameters('privateDnsZoneArmResourceId'))]"
72        },
73        "highAvailability": {
74          "mode": "[parameters('haMode')]"
75        },
76        "storage": {
77          "storageSizeGB": "[parameters('skuSizeGB')]"
78        },
79        "backup": {
80          "backupRetentionDays": 7,
81          "geoRedundantBackup": "Disabled"
82        },
83        "availabilityZone": "[parameters('availabilityZone')]"
84      }
85    }
86  ]
87}

ARM template parameters

When deploying the ARM template, you can override parameters like so:

  • administratorLogin: The default administrator username.

  • administratorLoginPassword: The default administrator password

  • location: The location of AKS resource. Default value: same region/location as the resource group you are deploying ARM template into.

  • serverName: The name of the server.

  • serverEdition: The edition of the server.

  • skuSizeGB: the storage size, in Gigabyte, of the Azure Database for PostgreSQL server.

  • dbInstanceType: The size of the virtual machine. Default: Standard_D4_v4

  • haMode: high availability mode.

  • availabilityZone: availability of the database.

  • version: the version of PostgreSQL server to deploy.

Deploy ARM template

$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, centralus) 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 `
    -TemplateFile "postgres-flexible-server-template.json" `
    -serverName $serverName `
    -administratorLogin $adminUser `
    -administratorLoginPassword $adminPassword

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

Review Deployed resources

Follow these steps to verify if your server was created in Azure.

echo "Enter your Azure Database for PostgreSQL Flexible Server name:" &&
read serverName &&
echo "Enter the resource group where the Azure Database for PostgreSQL Flexible Server exists:" &&
read resourcegroupName &&
az resource show --resource-group $resourcegroupName --name $serverName --resource-type "Microsoft.DBforPostgreSQL/flexibleServers"

Connect ESP instance to Azure PostgreSQL

Once you validate that the postgres database have been successfully created on the Azure portal, gather the database connection strings. You can find this under the connection strings settings of the database on the portal.

../../_images/postgres.png

Next, we can add the database connection string on the esp helm chart. on the values.yml file under the database section add the connection string informations:

database: |
  {
     "host": "<postgres_server_url_or_ip>",
     "port": <postgres_server_port>,
     "user": "<esp_db_user>",
     "pass": "<esp_db_password>",
     "name": "<esp_db_name>",
     "schema": "<esp_db_schema>",
     "multitenant": false,
     "start_service": true,
     "l7-esp.concierge": {
           "pool_size": 3
  },
     "l7-esp.notifications": {
           "pool_size": 3
     },
     "l7-esp.hub": {
           "pool_size": 3
     },
     "l7-esp.http": {
           "pool_size": 3
     }
  }

Now deploy the helm chart (see helm documentation). Verify that esp is up and the database schema was successfully created

../../_images/schema.png