Azure PostgreSQL command line create, connect, delete

By Brian Fitzgerald

Introduction

This blog post demonstrates creating, connecting, and deleting an Azure PostgreSQL database using command line tools.

Create

Configure

C:\>az configure --defaults location=eastus

C:\>az group create --name dflgrp

C:\>az configure --defaults group=dflgrp

Unique name

The postgres server name must be unique across Azure and across public DNS domain postgres.database.azure.com. Common names, like “test” are already taken. The name must be “available”.

When you enter the name in Azure Portal, your browser checks availability by making AJAX calls as you type.

check.name.avail

This command line script accomplishes the same name check.

Script pg_name_avail.py:

from argparse import ArgumentParser
from azure.common.credentials import get_azure_cli_credentials
from azure.mgmt.rdbms.postgresql.postgre_sql_management_client import PostgreSQLManagementClient

ap = ArgumentParser()
ap.add_argument('--name', '-n')
args = ap.parse_args()

cli = PostgreSQLManagementClient(
    *get_azure_cli_credentials()
)
na = cli.check_name_availability.execute(args.name).as_dict()
print(
    'Name "%s" %s' %
    (
        args.name,
        'is available.' if na['name_available']
        else 'is not available. %s.' % na['message']
    )
)

examples:

C:\>python pg_name_avail.py -n test
Name "test" is not available. Specified server name is already used.

C:\>python pg_name_avail.py -n azpgsrv
Name "azpgsrv" is available.

Create server

C:\>az postgres server create -n azpgsrv --sku-name B_Gen5_2 --admin-user azpgusr --admin-password azpgpw..212

Output is JSON:

{
  "administratorLogin": "azpgusr",
  "earliestRestoreDate": "2019-05-07T15:56:16.430000+00:00",
  "fullyQualifiedDomainName": "azpgsrv.postgres.database.azure.com",
  "id": "/subscriptions/7fe8a9c3-0812-42e2-9733-3f567308a0d0/resourceGroups/dflgrp/providers/Microsoft.DBforPostgreSQL/servers/azpgsrv",
  "location": "eastus",
  "masterServerId": "",
  "name": "azpgsrv",
  "replicaCapacity": 5,
  "replicationRole": "None",
  "resourceGroup": "dflgrp",
  "sku": {
    "capacity": 2,
    "family": "Gen5",
    "name": "B_Gen5_2",
    "size": null,
    "tier": "Basic"
  },
  "sslEnforcement": "Enabled",
  "storageProfile": {
    "backupRetentionDays": 7,
    "geoRedundantBackup": "Disabled",
    "storageAutoGrow": "Disabled",
    "storageMb": 5120
  },
  "tags": null,
  "type": "Microsoft.DBforPostgreSQL/servers",
  "userVisibleState": "Ready",
  "version": "9.6"
}

DNS Name

Microsoft Azure pushes a public, unique, fully qualified domain name for your server.

C:\>nslookup azpgsrv.postgres.database.azure.com
Server:  Wireless_Broadband_Router.home
Address:  192.168.1.1

Non-authoritative answer:
Name:    cr1.eastus1-a.control.database.windows.net
Address:  191.238.6.43
Aliases:  azpgsrv.postgres.database.azure.com

Connect

Firewall issue

C:\>psql -h azpgsrv.postgres.database.azure.com -p 5432 -U azpgusr@azpgsrv -d postgres
psql: FATAL:  no pg_hba.conf entry for host "100.12.249.90", user "azpgusr", database "postgres", SSL on
FATAL:  SSL connection is required. Please specify SSL options and retry.

C:\>az postgres server firewall-rule create -n fwrule1 -s azpgsrv --start-ip-address 100.12.249.90 --end-ip-address 100.12.249.90
{
  "endIpAddress": "100.12.249.90",
  "id": "/subscriptions/7fe8a9c3-0812-42e2-9733-3f567308a0d0/resourceGroups/dflgrp/providers/Microsoft.DBforPostgreSQL/servers/azpgsrv/firewallRules/fwrule1",
  "name": "fwrule1",
  "resourceGroup": "dflgrp",
  "startIpAddress": "100.12.249.90",
  "type": "Microsoft.DBforPostgreSQL/servers/firewallRules"
}

Code page issue

C:\>psql -h azpgsrv.postgres.database.azure.com -p 5432 -U azpgusr@azpgsrv -d postgres
Password for user azpgusr@azpgsrv:
psql (10.4, server 9.6.12)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> \q

C:\>psql -h azpgsrv.postgres.database.azure.com -p 5432 -U azpgusr@azpgsrv -d postgres

C:\>chcp 1252
Active code page: 1252

Success

C:\>psql -h azpgsrv.postgres.database.azure.com -p 5432 -U azpgusr@azpgsrv -d postgres
Password for user azpgusr@azpgsrv:
psql (10.4, server 9.6.12)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

Python connection

C:\Users\Brian Fitzgerald\python\blog>type pgcon.py
import psycopg2

conn = psycopg2.connect(
    dbname='postgres',
    user='azpgusr@azpgsrv',
    host='azpgsrv.postgres.database.azure.com',
    password='azpgpw..212',
    port=5432,
    sslmode='require'
)
print('connected')
C:\>python pgcon.py
connected

Delete

C:\>az postgres server firewall-rule delete -y -n fwrule1 -s azpgsrv

C:\>az postgres server delete -y -n azpgsrv

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s