This article is contributed. See the original author and article here.

If you prefer using a CLI—for one-time tasks as well as for more complicated, automated scripts—you can use the Azure CLI to take advantage of the improvements for provisioning and managing Flexible Server (in preview) in Azure Database for PostgreSQL. Flexible Server is a new deployment option (now in preview) for our fully-managed Postgres database service and is designed to give you more granular control and flexibility over database management functions and configuration settings. 


 


The new Azure CLI experience with Flexible Server for Azure Database for PostgreSQL includes:



  1. Refined output to keep you informed about what’s going on behind the scenes.

  2. One command to create a secure server inside a virtual network.

  3. Ability to use contextual information between CLI commands help reduce the number of keystrokes for each command.


Try Azure Database for PostgreSQL – Flexible Server CLI commands.


 


Screenshot of Azure CLI welcome messageScreenshot of Azure CLI welcome message


 


Use local context to store common information


 


Flexible server CLI commands support local context  with az config param-persist  command that stores information locally such as region, resource group, subscription ID, resource name, etc. for every sequential CLI command you run. You can easily turn on local context to store information with az config param persist on.  If local context is turned on, you can see the contextual information using az config param-persist show. You can always turn if off using az config param persist off.


 


Run the command to view what is in your local context.


 

az config param-persist show

 


The output as shown below will tell you the values stored in your local context.


 

Command group "config param-persist" is experimental and not covered by customer support.
{
   "all":
     {  
       "location":"eastus",
       "resource_group_name":"mynewproject"
     }   
}

 


Ease of provisioning and deprovisioning


 


When creating a Postgres server using our managed database service on Azure, you probably want to get started quickly. Especially if you’re just trying things out. With the new and improved Azure CLI for Flexible Server on Azure Database for PostgreSQL, you can quickly create a Postgres server inside a virtual network. Or if you prefer, you can easily provision a server with firewall rules in one single step. You can also view the progress visually as CLI commands keep you informed about what’s going on behind the scenes.


 


Create a Postgres Flexible Server inside a new virtual network


 


Instead of creating a resource group, a virtual network, and a subnet with separate commands, you can use one command—az postgres flexible-server create as shown below—to create a secure PostgreSQL Flexible Server inside a new virtual network and have a new subnet delegated to the server.


 


Run the command to create a secure server inside a virtual network.


 

az postgres flexible-server create

 


The output shows you at the steps taken to create this server with the virtual network, subnet, username, and password are all auto-generated. 


 

Local context is turned on. Its information is saved in working directory /home/azuser. You can run az local-context off to turn it off.
Command argument values from local context: --resource-group: mynewproject, --location: eastus
Command group "postgres flexible-server" is in preview. It may be changed/removed in a future release.
Checking the existence of the resource group "mynewproject"...
Resource group "mynewproject" exists ? : True
Creating new vnet "VNET095447391" in resource group "mynewproject"...
Creating new subnet "Subnet095447391" in resource group "mynewproject" and delegating it to "Microsoft.DBforPostgreSQL/flexibleServers"...
Creating PostgreSQL Server "server095447391" in group "mynewproject"...
Your server "server095447391" is using sku "Standard_D2s_v3" (Paid Tier). Please refer to https://aka.ms/postgres-pricing for pricing details
Make a note of your password. If you forget, you would have to reset your password with "az postgres flexible-server update -n server095447391 -g mynewproject -p <new-password>".
{
  "connectionString": "postgresql://username:your-password@server095447391.postgres.database.azure.com/postgres?sslmode=require",
  "host": "server095447391.postgres.database.azure.com",
  "id": "/subscriptions/your-subscription-id/resourceGroups/mynewproject/providers/Microsoft.DBforPostgreSQL/flexibleServers/server095447391",
  "location": "East US",
  "password": "your-password",
  "resourceGroup": "mynewproject",
  "skuname": "Standard_D2s_v3",
  "subnetId": "/subscriptions/your-subscription-id/resourceGroups/mynewproject/providers/Microsoft.Network/virtualNetworks/VNET095447391/subnets/Subnet095447391",
  "username": "your-username",
  "version": "12"
}

 


Create a PostgreSQL Flexible Server with public access to all IPs


 


Use az postgres flexible-server create –public-access all if you want to create a publicly accessible postgres server. Publicly accessible server can be access from any client machine as long as you have the correct username and password.  If you want to restrict access only to your IP set –public -access argument to either your <IP – address> or an IP address range such as <Start IP address- End IP address>. 


 


Run the command create a server will public access.


 

az postgres flexible-server create --public-access all

 


The output shows your a new server created with a firewall rule that allows IPs from 0.0.0.0 to 255.255.255.255:


 

Local context is turned on. Its information is saved in working directory /home/azuser. You can run az local-context off to turn it off.
Command argument values from local context: --resource-group: mynewproject, --location: eastus
Command group "postgres flexible-server" is in preview. It may be changed/removed in a future release.
Checking the existence of the resource group "mynewproject"...
Resource group "mynewproject" exists ? : True
Creating PostgreSQL Server "server184001358" in group "mynewproject"...
Your server "server184001358" is using sku "Standard_D2s_v3" (Paid Tier). Please refer to https://aka.ms/postgres-pricing for pricing details
Configuring server firewall rule to accept connections from "0.0.0.0" to "255.255.255.255"...
Make a note of your password. If you forget, you would have to  reset your password with "az postgres flexible-server update -n server184001358 -g mynewproject -p <new-password>".
{
"connectionString": "postgresql://username:your-password@server184001358.postgres.database.azure.com/postgres?sslmode=require",
"firewallName": "AllowAll_2020-11-11_20-29-34",
"host": "server184001358.postgres.database.azure.com",
"id": "/subscriptions/your-subscription-id/resourceGroups/mynewproject/providers/Microsoft.DBforPostgreSQL/flexibleServers/server184001358",
"location": "East US",
"password": "your-password",
"resourceGroup": "mynewproject",
"skuname": "Standard_D2s_v3",
"username": "your-username",
"version": "12"
}

 


Delete the server when using local context


 


Use az postgres flexible-server delete to delete the server which will look in local context to find for any PostgreSQL flexible server. In the example below, it identified server056513445 in local context and hence tries to delete that server.


 


Run the command to delete your server.


 

az postgres flexible-server delete

 


The output prompts you to confirm if you want to delete the server.


 

Local context is turned on. Its information is saved in working directory /home/azuser. You can run az local-context off to turn it off.
Command argument values from local context: --resource-group: mynewproject, --name: server184001358
Command group "postgres flexible-server" is in preview. It may be changed/removed in a future release.
Are you sure you want to delete the server "server184001358" in resource group "mynewproject" (y/n): y

 


Tune your PostgreSQL server


 


Tuning PostgreSQL database parameters is important to configure your server to fit your application’s needs or even to optimize performance. You can use az postgres flexible-server parameter set command to easily update server parameters with ease. You can view all the server parameter with parameter list command and parameter show command to view the parameter values of an specific server parameter.


 


Run the command to configure log_error_verbosity parameter:


 

az postgres flexible-server parameter set --name log_error_verbosity --value TERSE

 


The output shows you if the value has change to TERSE .


 

 Local context is turned on. Its information is saved in working directory /home/azuser. You can run az local-context off to turn it off.
 Command argument values from local context: --server-name: server184001358, --resource-group: sumuth-flexible-server
 Command group "postgres flexible-server parameter" is in preview. It may be changed/removed in a future release.

{
  "allowedValues": "terse,default,verbose",
  "dataType": "Enumeration",
  "defaultValue": "default",
  "description": "Sets the verbosity of logged messages.",
  "id": "/subscriptions/xxxxxxxxxxxxxxxxxxxxxxxxxxx/resourceGroups/sumuth-flexible-server/providers/Microsoft.DBforPostgreSQL/serversv2/server184001358/configurations/log_error_verbosity",
  "name": "log_error_verbosity",
  "resourceGroup": "sumuth-flexible-server",
  "source": "user-override",
  "type": "Microsoft.DBforPostgreSQL/flexibleServers/configurations",
  "value": "TERSE"
}

 


Use powerful Azure CLI utilities with flexible server CLI


 


Azure CLI has powerful utilities that can be used with PostgreSQL Flexible Server CLI commands from finding right commands, getting readable output or even running REST APIs.  



  • az find to find the command you are looking.

  • Use the –help argument to get a complete list of commands and subgroups of a group.

  • Change the output formatting to table or tsv or yaml formats as you see fit.

  • Use az interactive mode which provides interactive shell with auto-completion, command descriptions, and examples.

  • Use az upgrade to update your CLI and extensions.

  • Use az rest command that lets you call your service endpoints to run GET, PUT, PATCH methods in a secure way.


The new experience has been designed to support the best possible experience for developers to create and manage your PostgreSQL servers. We’d love for you to try out Azure Database for PostgreSQL Flexible server CLI commands and share your feedback for new CLI commands or issues with existing ones.

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.