Quickstart: Create an Azure Database for PostgreSQL server by using the Azure CLI

This quickstart shows how to use Azure CLI commands in Azure Cloud Shell to create a single Azure Database for PostgreSQL server in five minutes. If you don't have an Azure subscription, create a free account before you begin.

Use Azure Cloud Shell

Azure hosts Azure Cloud Shell, an interactive shell environment that you can use through your browser. You can use either Bash or PowerShell with Cloud Shell to work with Azure services. You can use the Cloud Shell preinstalled commands to run the code in this article without having to install anything on your local environment.

To start Azure Cloud Shell:

Option Example/Link
Select Try It in the upper-right corner of a code block. Selecting Try It doesn't automatically copy the code to Cloud Shell. Example of Try It for Azure Cloud Shell
Go to https://shell.azure.com, or select the Launch Cloud Shell button to open Cloud Shell in your browser. Launch Cloud Shell in a new window
Select the Cloud Shell button on the menu bar at the upper right in the Azure portal. Cloud Shell button in the Azure portal

To run the code in this article in Azure Cloud Shell:

  1. Start Cloud Shell.

  2. Select the Copy button on a code block to copy the code.

  3. Paste the code into the Cloud Shell session by selecting Ctrl+Shift+V on Windows and Linux or by selecting Cmd+Shift+V on macOS.

  4. Select Enter to run the code.

Tip

Consider using the simpler az postgres up Azure CLI command that's currently in preview. Try out the quickstart.

Prerequisites

This article requires you to run the Azure CLI version 2.0 or later locally. To see the version installed, run the az --version command. If you need to install or upgrade, see Install the Azure CLI.

You need to log in to your account by using the az login command. Note the id property, which refers to Subscription ID for your Azure account.

az login

Select the specific subscription ID under your account by using the az account set command. Make a note of the id value from the az login output to use as the value for the subscription argument in the command.

az account set --subscription <subscription id>

If you have multiple subscriptions, choose the appropriate subscription in which the resource should be billed. To get all your subscriptions, use az account list.

Create an Azure Database for PostgreSQL server

Create an Azure resource group by using the az group create command, and then create your PostgreSQL server inside this resource group. You should provide a unique name. The following example creates a resource group named myresourcegroup in the westus location.

az group create --name myresourcegroup --location westus

Create an Azure Database for PostgreSQL server by using the az postgres server create command. A server can contain multiple databases.

az postgres server create --resource-group myresourcegroup --name mydemoserver  --location westus --admin-user myadmin --admin-password <server_admin_password> --sku-name GP_Gen5_2 

Here are the details for the preceding arguments:

Setting Sample value Description
name mydemoserver Unique name that identifies your Azure Database for PostgreSQL server. The server name can contain only lowercase letters, numbers, and the hyphen (-) character. It must contain 3 to 63 characters. For more information, see Azure Database for PostgreSQL Naming Rules.
resource-group myresourcegroup Name of the Azure resource group.
location westus Azure location for the server.
admin-user myadmin Username for the administrator login. It can't be azure_superuser, admin, administrator, root, guest, or public.
admin-password secure password Password of the administrator user. It must contain 8 to 128 characters from three of the following categories: English uppercase letters, English lowercase letters, numbers, and non-alphanumeric characters.
sku-name GP_Gen5_2 Name of the pricing tier and compute configuration. Follow the convention {pricing tier}{compute generation}{vCores} in shorthand. For more information, see Azure Database for PostgreSQL pricing.

Important

Configure a server-level firewall rule

By default, the server that you created is not publicly accessible and is protected with firewall rules. You can configure the firewall rules on your server by using the az postgres server firewall-rule create command to give your local environment access to connect to the server.

The following example creates a firewall rule called AllowMyIP that allows connections from a specific IP address, 192.168.0.1. Replace the IP address or range of IP addresses that corresponds to where you'll be connecting from. If you don't know your IP address, go to WhatIsMyIPAddress.com to get it.

az postgres server firewall-rule create --resource-group myresourcegroup --server mydemoserver --name AllowMyIP --start-ip-address 192.168.0.1 --end-ip-address 192.168.0.1

Note

To avoid connectivity issues, make sure your network's firewall allows port 5432. Azure Database for PostgreSQL servers use that port.

Get the connection information

To connect to your server, provide host information and access credentials.

az postgres server show --resource-group myresourcegroup --name mydemoserver

The result is in JSON format. Make a note of the administratorLogin and fullyQualifiedDomainName values.

{
  "administratorLogin": "myadmin",
  "earliestRestoreDate": null,
  "fullyQualifiedDomainName": "mydemoserver.postgres.database.azure.com",
  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/myresourcegroup/providers/Microsoft.DBforPostgreSQL/servers/mydemoserver",
  "location": "westus",
  "name": "mydemoserver",
  "resourceGroup": "myresourcegroup",
  "sku": {
    "capacity": 2,
    "family": "Gen5",
    "name": "GP_Gen5_2",
    "size": null,
    "tier": "GeneralPurpose"
  },
  "sslEnforcement": "Enabled",
  "storageProfile": {
    "backupRetentionDays": 7,
    "geoRedundantBackup": "Disabled",
    "storageMb": 5120
  },
  "tags": null,
  "type": "Microsoft.DBforPostgreSQL/servers",
  "userVisibleState": "Ready",
  "version": "9.6"
}

Connect to the Azure Database for PostgreSQL server by using psql

The psql client is a popular choice for connecting to PostgreSQL servers. You can connect to your server by using psql with Azure Cloud Shell. You can also use psql on your local environment if you have it available. An empty database, postgres, is automatically created with a new PostgreSQL server. You can use that database to connect with psql, as shown in the following code.

psql --host=mydemoserver.postgres.database.azure.com --port=5432 --username=myadmin@mydemoserver --dbname=postgres

Tip

If you prefer to use a URL path to connect to Postgres, URL encode the @ sign in the username with %40. For example, the connection string for psql would be:

psql postgresql://myadmin%40mydemoserver@mydemoserver.postgres.database.azure.com:5432/postgres

Clean up resources

If you don't need these resources for another quickstart or tutorial, you can delete them by running the following command.

az group delete --name myresourcegroup

If you just want to delete the one newly created server, you can run the az postgres server delete command.

az postgres server delete --resource-group myresourcegroup --name mydemoserver

Next steps