In the last post we had a look on how you can bring up a customized PostgreSQL instance in the Azure cloud. Now I want to check what you can do with the managed service. For the managed service I am expecting that I can bring up a PostgreSQL quite easily and fast and that I can add replicas on demand. Lets see what is there and how you can use it.

Of course we need to login again:

dwe@dwe:~$ cd /var/tmp
dwe@dwe:/var/tmp$ az login

The az command for working with PostgreSQL is simply “postgres”:

dwe@dwe:~$ az postgres --help

Group
    az postgres : Manage Azure Database for PostgreSQL servers.

Subgroups:
    db          : Manage PostgreSQL databases on a server.
    server      : Manage PostgreSQL servers.
    server-logs : Manage server logs.

Does not look like we can do much but you never know so lets bring up an instance. Again, we need a resource group first:

dwe@dwe:~$ az group create --name PGTEST --location "westeurope"
{
  "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/PGTEST",
  "location": "westeurope",
  "managedBy": null,
  "name": "PGTEST",
  "properties": {
    "provisioningState": "Succeeded"
  },
  "tags": null
}

Lets try to bring up an instance with a little storage (512MB), SSL enabled and the standard postgres user:

dwe@dwe:~$ az postgres server create --name mymanagedpg1 --resource-group PGTEST --sku-name B_Gen4_2 --ssl-enforcement Enabled --storage-size 512 --admin-user postgres --admin-password xxxxx --location westeurope
Deployment failed. Correlation ID: e3cd6d04-3557-4c2a-b70f-7c11a61c395d. Server name 'PG1' cannot be empty or null. It can only be made up of lowercase letters 'a'-'z', the numbers 0-9 and the hyphen. The hyphen may not lead or trail in the name.

Ok, seems upper case letters are not allowed, try again:

dwe@dwe:~$ az postgres server create --name mymanagedpg1 --resource-group PGTEST --sku-name B_Gen4_2 --ssl-enforcement Enabled --storage-size 512 --admin-user postgres --admin-password postgres --location westeurope
Deployment failed. Correlation ID: e50ca5d6-0e38-48b8-8015-786233c0d103. The storage size of 512 MB does not meet the minimum required storage of 5120 MB.

Ok, we need a minimum of 5120 MB of storage, again:

dwe@dwe:~$ az postgres server create --name mymanagedpg1 --resource-group PGTEST --sku-name B_Gen4_2 --ssl-enforcement Enabled --storage-size 5120 --admin-user postgres --admin-password postgres --location westeurope
Deployment failed. Correlation ID: 470975ce-1ee1-4531-8703-55947772fb51. Password validation failed. The password does not meet policy requirements because it is not complex enough.

This one is good as it at least denies the postgres/postgres combination. Again with a better password:

dwe@dwe:~$ az postgres server create --name mymanagedpg1 --resource-group PGTEST --sku-name B_Gen4_2 --ssl-enforcement Enabled --storage-size 5120 --admin-user postgres --admin-password "xxx" --location westeurope
{
  "administratorLogin": "postgres",
  "earliestRestoreDate": "2018-08-13T12:30:10.763000+00:00",
  "fullyQualifiedDomainName": "mymanagedpg1.postgres.database.azure.com",
  "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/PGTEST/providers/Microsoft.DBforPostgreSQL/servers/mymanagedpg1",
  "location": "westeurope",
  "name": "mymanagedpg1",
  "resourceGroup": "PGTEST",
  "sku": {
    "capacity": 2,
    "family": "Gen4",
    "name": "B_Gen4_2",
    "size": null,
    "tier": "Basic"
  },
  "sslEnforcement": "Enabled",
  "storageProfile": {
    "backupRetentionDays": 7,
    "geoRedundantBackup": "Disabled",
    "storageMb": 5120
  },
  "tags": null,
  "type": "Microsoft.DBforPostgreSQL/servers",
  "userVisibleState": "Ready",
  "version": "9.6"
}

Better. What I am not happy with is that the default seems to be PostgreSQL 9.6. PostgreSQL 10 is out around a year now and that should definitely by the default. In the portal it looks like this and there you can also find the information required for connecting to the instance:
Selection_004

So lets try to connect:

dwe@dwe:~$ psql -h mymanagedpg1.postgres.database.azure.com -U postgres@mymanagedpg1
psql: FATAL:  no pg_hba.conf entry for host "x.x.x.xx", user "postgres", database "postgres@mymanagedpg1", SSL on
FATAL:  SSL connection is required. Please specify SSL options and retry.

How do we manage that with the managed PostgreSQL service? Actually there is no az command to modify pg_hba_conf but what we need to do is to create a firewall rule:

dwe@dwe:~$ az postgres server firewall-rule create -g PGTEST -s mymanagedpg1 -n allowall --start-ip-address 0.0.0.0 --end-ip-address 255.255.255.255
{
  "endIpAddress": "255.255.255.255",
  "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/PGTEST/providers/Microsoft.DBforPostgreSQL/servers/mymanagedpg1/firewallRules/allowall",
  "name": "allowall",
  "resourceGroup": "PGTEST",
  "startIpAddress": "0.0.0.0",
  "type": "Microsoft.DBforPostgreSQL/servers/firewallRules"
}

Of course you should not open to the whole world as I am doing here. When the rule is in place connections do work:

dwe@dwe:~$ psql -h mymanagedpg1.postgres.database.azure.com -U postgres@mymanagedpg1 postgres
Password for user postgres@mymanagedpg1: 
psql (9.5.13, server 9.6.9)
WARNING: psql major version 9.5, server major version 9.6.
         Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> 

There is an additional database called “azure_maintenance” and we are not allowed to connect there:

postgres=> \l
                                                               List of databases
       Name        |      Owner      | Encoding |          Collate           |           Ctype            |          Access privileges          
-------------------+-----------------+----------+----------------------------+----------------------------+-------------------------------------
 azure_maintenance | azure_superuser | UTF8     | English_United States.1252 | English_United States.1252 | azure_superuser=CTc/azure_superuser
 postgres          | azure_superuser | UTF8     | English_United States.1252 | English_United States.1252 | 
 template0         | azure_superuser | UTF8     | English_United States.1252 | English_United States.1252 | =c/azure_superuser                 +
                   |                 |          |                            |                            | azure_superuser=CTc/azure_superuser
 template1         | azure_superuser | UTF8     | English_United States.1252 | English_United States.1252 | =c/azure_superuser                 +
                   |                 |          |                            |                            | azure_superuser=CTc/azure_superuser
(4 rows)
postgres=> \c azure_maintenance
FATAL:  permission denied for database "azure_maintenance"
DETAIL:  User does not have CONNECT privilege.
Previous connection kept

The minor release is one release behind but as the latest minor release was released this week that seems to be fine:

postgres=> select version();
                           version                           
-------------------------------------------------------------
 PostgreSQL 9.6.9, compiled by Visual C++ build 1800, 64-bit
(1 row)

postgres=> 

I would probably not compile PostgreSQL with “Visual C++” but given that we use a Microsoft product, surprise, we are running on Windows:

postgres=> select name,setting from pg_settings where name = 'archive_azure_location';
          name          |          setting          
------------------------+---------------------------
 archive_azure_location | c:\BackupShareDir\Archive
(1 row)

… and the PostgreSQL source code was modified as this parameter does not exist in the community version.

Access to the server logs is quite easy:

dwe@dwe:~$ az postgres server-logs list --resource-group PGTEST --server-name mymanagedpg1
[
  {
    "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/PGTEST/providers/Microsoft.DBforPostgreSQL/servers/mymanagedpg1/logFiles/postgresql-2018-08-13_122334.log",
    "lastModifiedTime": "2018-08-13T12:59:26+00:00",
    "logFileType": "text",
    "name": "postgresql-2018-08-13_122334.log",
    "resourceGroup": "PGTEST",
    "sizeInKb": 6,
    "type": "Microsoft.DBforPostgreSQL/servers/logFiles",
    "url": "https://wasd2prodweu1afse118.file.core.windows.net/74484e5541e04b5a8556eac6a9eb37c8/pg_log/postgresql-2018-08-13_122334.log?sv=2015-04-05&sr=f&sig=ojGG2km5NFrfQ8dJ0btz8bhmwNMe0F7oq0iTRum%2FjJ4%3D&se=2018-08-13T14%3A06%3A16Z&sp=r"
  },
  {
    "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/PGTEST/providers/Microsoft.DBforPostgreSQL/servers/mymanagedpg1/logFiles/postgresql-2018-08-13_130000.log",
    "lastModifiedTime": "2018-08-13T13:00:00+00:00",
    "logFileType": "text",
    "name": "postgresql-2018-08-13_130000.log",
    "resourceGroup": "PGTEST",
    "sizeInKb": 0,
    "type": "Microsoft.DBforPostgreSQL/servers/logFiles",
    "url": "https://wasd2prodweu1afse118.file.core.windows.net/74484e5541e04b5a8556eac6a9eb37c8/pg_log/postgresql-2018-08-13_130000.log?sv=2015-04-05&sr=f&sig=k8avZ62KyLN8RW0ZcIigyPZa40EKNBJvNvneViHjyeI%3D&se=2018-08-13T14%3A06%3A16Z&sp=r"
  }
]

We can just download the logs and have a look at them:

dwe@dwe:~$ wget "https://wasd2prodweu1afse118.file.core.windows.net/74484e5541e04b5a8556eac6a9eb37c8/pg_log/postgresql-2018-08-13_122334.log?sv=2015-04-05&sr=f&sig=Mzy2dQ%2BgRPY8lfkUAP5X%2FkXSxoxWSwrphy7BphaTjLk%3D&se=2018-08-13T14%3A07%3A29Z&sp=r" 
we@dwe:~$ more postgresql-2018-08-13_122334.log\?sv\=2015-04-05\&sr\=f\&sig\=Mzy2dQ%2BgRPY8lfkUAP5X%2FkXSxoxWSwrphy7BphaTjLk%3D\&se\=2018-08-13T14%3A07%3A29Z\&sp\=r
2018-08-13 12:23:34 UTC-5b717845.6c-LOG:  could not bind IPv6 socket: A socket operation was attempted to an unreachable host.
	
2018-08-13 12:23:34 UTC-5b717845.6c-HINT:  Is another postmaster already running on port 20686? If not, wait a few seconds and retry.
2018-08-13 12:23:34 UTC-5b717846.78-LOG:  database system was shut down at 2018-08-13 12:23:32 UTC
2018-08-13 12:23:35 UTC-5b717846.78-LOG:  database startup complete in 1 seconds, startup began 2 seconds after last stop
...

The PostgreSQL configuration is accessible quite easy:

dwe@dwe:~$ az postgres server configuration list --resource-group PGTEST --server-name mymanagedpg1 | head -20
[
  {
    "allowedValues": "on,off",
    "dataType": "Boolean",
    "defaultValue": "on",
    "description": "Enable input of NULL elements in arrays.",
    "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/PGTEST/providers/Microsoft.DBforPostgreSQL/servers/mymanagedpg1/configurations/array_nulls",
    "name": "array_nulls",
    "resourceGroup": "PGTEST",
    "source": "system-default",
    "type": "Microsoft.DBforPostgreSQL/servers/configurations",
    "value": "on"
  },
  {
    "allowedValues": "safe_encoding,on,off",
    "dataType": "Enumeration",
    "defaultValue": "safe_encoding",
    "description": "Sets whether \"\\'\" is allowed in string literals.",
    "id": "/subscriptions/030698d5-42d6-41a1-8740-355649c409e7/resourceGroups/PGTEST/providers/Microsoft.DBforPostgreSQL/servers/mymanagedpg1/configurations/backslash_quote",
    "name": "backslash_quote",

Setting a parameter is easy as well:

dwe@dwe:~$ az postgres server configuration set --name work_mem --value=32 --resource-group PGTEST --server-name mymanagedpg1
Deployment failed. Correlation ID: 634fd473-0c28-43a7-946e-ecbb26faf961. The value '32' for configuration 'work_mem' is not valid. The allowed values are '4096-2097151'.
dwe@dwe:~$ az postgres server configuration set --name work_mem --value=4096 --resource-group PGTEST --server-name mymanagedpg1
{
  "allowedValues": "4096-2097151",
  "dataType": "Integer",
  "defaultValue": "4096",
  "description": "Sets the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files.",
  "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/PGTEST/providers/Microsoft.DBforPostgreSQL/servers/mymanagedpg1/configurations/work_mem",
  "name": "work_mem",
  "resourceGroup": "PGTEST",
  "source": "system-default",
  "type": "Microsoft.DBforPostgreSQL/servers/configurations",
  "value": "4096"
}

The interesting point is what happens when we change a parameter that requires a restart:

dwe@dwe:~$ az postgres server configuration set --name shared_buffers --value=4096 --resource-group PGTEST --server-name mymanagedpg1
Deployment failed. Correlation ID: d849b302-1c41-4b13-a2d5-6b24f144be89. The configuration 'shared_buffers' does not exist for PostgreSQL server version 9.6.
dwe@dwe:~$ psql -h mymanagedpg1.postgres.database.azure.com -U postgres@mymanagedpg1 postgresPassword for user postgres@mymanagedpg1: 
psql (9.5.13, server 9.6.9)
WARNING: psql major version 9.5, server major version 9.6.
         Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> show shared_buffers ;
 shared_buffers 
----------------
 512MB
(1 row)
postgres=> 

So memory configuration depends on the pricing models, more information here. If you want to scale up or down “you can independently change the vCores, the hardware generation, the pricing tier (except to and from Basic), the amount of storage, and the backup retention period”.

Some final thoughts: Bringing an instance up is quite easy and simple. The default PostgreSQL version is 9.6.x, which is not a good choice in my opinion, version 10 already got the 5th minor release and is stable and the most recent version. Scaling up and down is a matter of changing basic stuff such as cores, memory, storage and pricing models. For many workloads this is probably fine, if you want to have more control you’d do better in provisioning VMs and then do the PostgreSQL stuff for your own. High availability is not implemented by adding replicas but by creating new nodes, attaching the storage to that node and then bring it up. This might be sufficient, it might be not, depends on your requirements.

In a next post we will build our own PostgreSQL HA solution on Azure.