Azure CLI improvements for Azure Database for PostgreSQL – Single server

by Contributed | Nov 24, 2020 | Azure, Microsoft, Technology

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

With the new changes to the Azure CLI, building applications has become much easier. This blog post walks you through key changes that can help enhance your productivity.

Some major changes include:

1. A simplified single command for creating a new Postgres single server on Azure.


2. The ability to use contextual information between CLI commands to help reduce the number of keystrokes for each command.


3. New values for the parameter –public to create firewall rules as a part of the create experience.


4. New command – list connection strings.


5. Improved readable tabular formatted output for sku list and server list commands.

Note: Download the latest official release of the Azure CLI from the Azure CLI page or the dev build from the GitHub Azure CLI homepage.

Important: While this post focuses on Azure Database for PostgreSQL Single server, the changes described apply equally to our Flexible server deployment model.

1. Single command for creating a single server instance

Login to your Azure account using  `az login`, select your subscription (if different from default), and then run the command `az postgres server create’ to create a Postgres Single Server Instance on Azure.

Note the following key points:

• A resource group with a random name is created for you in the default region.


• A server-name, admin username, and password are auto-generated for you.


• The text in yellow indicates what is going on behind the scenes. The server gets created with the defaults which can be found using `az postgres server create –help`.


• Two new fields (password and connection-string) are introduced in the output.


• To override a default value, pass that parameter with the value of your choice.

2. Support for param-persist

Azure Database for PostgreSQL server CLI commands now support persisting values of parameters with the `az config param-persist` command , which locally stores information such as location, resource group, administrator login and server name for every sequential CLI command you execute. You can easily turn on param persist to store information using `az config param-persist on`. With param persist turned on, you can see the contextual information using `az config param-persist show`. You can always turn off support for param persist by using `az config param-persist off`.

A summary of points that you might find helpful is listed below:

• Fields commonly stored in param persist are location, resource group, server name and administrator login.


• The param persist is designed to hold only one value for any parameter – which is always from the latest executed command.


• Turning off the param persist does not automatically clear the stored fields. You can clear up all or specific values in the param persist using the commands in `az config param-persist delete`.


• An example of how param persist can reduce the effort is shown below.

• Note how there is no longer a need to supply the server name and resource group in the firewall creation command when you add a new firewall rule to the server you just created. 

az postgres server firewall-rule create -n firewall-rule-1 --start-ip-address 107.223.9.21 --end-ip-address 107.223.9.27

•  When in confusion about the values fetched from param persist – look for the text ‘Command argument values from local context’ field in the command output as shown below.

Local context is turned on. Its information is saved in working directory /home/aritra. You can run `az local-context off` to turn it off.
Command argument values from local context: --resource-group: group2029187709, --server-name: server905314632
{- Finished ..
  "endIpAddress": "107.223.9.27",
  "id": "/subscriptions/<your-subscription-id>/resourceGroups/group2029187709/providers/Microsoft.DBforPostgreSQL/servers/server905314632/firewallRules/firewall-rule-1",
  "name": "firewall-rule-1",
  "resourceGroup": "group2029187709",
  "startIpAddress": "107.223.9.21",
  "type": "Microsoft.DBforPostgreSQL/servers/firewallRules"
}

3. New values for the –public parameter in az postgres server create command

When creating a Postgres server using our managed database service on Azure, you want to get started quickly. You can now let the create experience take care of the accessibility based on the value you pass for the –public parameter in the create command. When a server is created with any of these newly supported values, a new field `firewallName` will show up in the output of the create command. In addition to the existing values of ‘Enabled’ and ‘Disabled’, we are now supporting the below values:

4. New command – az postgres server show-connection-string

`az postgres server show-connection-string`: Lists the connections string for connecting your Postgres database to applications such as ADO.NET, JDBC, JDBC Spring, Node.JS, PHP, Python, and Ruby.

az postgres server show-connection-string -s server905314632 -u flexibleHorse

All placeholder fields within {} in the connection string can be substituted with actual values when you pass them as parameters in the above command. 

{
  "connectionStrings": {
    "C++ (libpq)": "host=server905314632.postgres.database.azure.com port=5432 dbname={database} user=flexibleHorse@server905314632 password={password} sslmode=require",
    "ado.net": "Server=server905314632.postgres.database.azure.com;Database={database};Port=5432;User Id=flexibleHorse@server905314632;Password={password};",
    "jdbc": "jdbc:postgresql://server905314632.postgres.database.azure.com:5432/{database}?user=flexibleHorse@server905314632&password={password}",
    "node.js": "var client = new pg.Client('postgres://flexibleHorse@server905314632:{password}@server905314632.postgres.database.azure.com:5432/{database}');",
    "php": "host=server905314632.postgres.database.azure.com port=5432 dbname={database} user=flexibleHorse@server905314632 password={password}",
    "psql_cmd": "postgresql://flexibleHorse@server905314632:{password}@server905314632.postgres.database.azure.com/{database}?sslmode=require",
    "python": "cnx = psycopg2.connect(database='{database}', user='flexibleHorse@server905314632', host='server905314632.postgres.database.azure.com', password='{password}', port='5432')",
    "ruby": "cnx = PG::Connection.new(:host => 'server905314632.postgres.database.azure.com', :user => 'flexibleHorse@server905314632', :dbname => '{database}', :port => '5432', :password => '{password}')"
  }
}

5. Improved readable tabular format

For the commands below, the latest version of the Azure CLI supports viewing output in a tabular format, which provides you with a snapshot view.

• `az postgres server list-skus -l eastus -o table`


• `az postgres server list [-g <group_name>] [-n <server_name>] -o table`
  
  
  
  • Not supplying -g and –n would list all servers in the subscription.
  
  
  • Supplying -g and -n would list the details of a single server.
  
  
  • Supplying only -g would list the details of all servers in the resource group.
  
  
  
  

Use powerful Azure CLI utilities with single server CLI

Azure CLI has powerful utilities that can be used with PostgreSQL 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 improvements have been designed to support the best possible experience for developers to create and manage their PostgreSQL servers. We’d love for you to try out the improvements and share your feedback for new CLI commands or issues with existing ones.