Create an Azure Arc-enabled PostgreSQL server from CLI

This document describes the steps to create a PostgreSQL server on Azure Arc and to connect to it.

Prerequisites

Before you can proceed with the tasks in this article, you need to have the necessary tools. All deployments require the following tools:

• Azure Data Studio

• Azure Arc extension for Azure Data Studio

• Azure CLI (az)

• arcdata extension for Azure CLI

• kubectl

  Additional client tools depending your environment. For a more comprehensive list, see Client tools.

In addition to the required tools, to complete the tasks, you need an Azure Arc data controller.

Getting started

If you are already familiar with the topics below, you may skip this paragraph. There are important topics you may want read before you proceed with creation:

• Overview of Azure Arc-enabled data services
• Connectivity modes and requirements
• Storage configuration and Kubernetes storage concepts
• Kubernetes resource model

If you prefer to try out things without provisioning a full environment yourself, get started quickly with Azure Arc Jumpstart on Azure Kubernetes Service (AKS), AWS Elastic Kubernetes Service (EKS), Google Cloud Kubernetes Engine (GKE) or in an Azure VM.

Preliminary and temporary step for OpenShift users only

Implement this step before moving to the next step. To deploy PostgreSQL server onto Red Hat OpenShift in a project other than the default, you need to execute the following commands against your cluster to update the security constraints. This command grants the necessary privileges to the service accounts that will run your PostgreSQL server. The security context constraint (SCC) arc-data-scc is the one you added when you deployed the Azure Arc data controller.

oc adm policy add-scc-to-user arc-data-scc -z <server-name> -n <namespace-name>

Server-name is the name of the server you will create during the next step.

For more details on SCCs in OpenShift, refer to the OpenShift documentation. Proceed to the next step.

Create an Azure Arc-enabled PostgreSQL server

To create an Azure Arc-enabled PostgreSQL server on your Arc data controller, you will use the command az postgres server-arc create to which you will pass several parameters.

For details about all the parameters you can set at the creation time, review the output of the command:

az postgres server-arc create --help

The main parameters should consider are:

• the name of the server you want to deploy. Indicate either --name or -n followed by a name whose length must not exceed 11 characters.

• The storage classes you want your server to use. It is important you set the storage class right at the time you deploy a server as this setting cannot be changed after you deploy. You may specify the storage classes to use for the data, logs and the backups. By default, if you do not indicate storage classes, the storage classes of the data controller will be used.

  • To set the storage class for the backups, indicate the parameter --storage-class-backups followed by the name of the storage class. Excluding this parameter disables automated backups
  • To set the storage class for the data, indicate the parameter --storage-class-data followed by the name of the storage class.
  • To set the storage class for the logs, indicate the parameter --storage-class-logs followed by the name of the storage class.

  Important

  If you need to change the storage class after deployment, extract the data, delete your server, create a new server, and import the data.

When you execute the create command, you will be prompted to enter the username and password for the administrative user. You may skip the interactive prompt by setting the AZDATA_USERNAME and AZDATA_PASSWORD session environment variables before you run the create command.

Examples

To deploy a PostgreSQL server named postgres01 that uses the same storage classes as the data controller, run the following command:

az postgres server-arc create -n postgres01 --k8s-namespace <namespace> --use-k8s

List the PostgreSQL servers deployed in your Arc data controller

To list the PostgreSQL servers deployed in your Arc data controller, run the following command:

az postgres server-arc list --k8s-namespace <namespace> --use-k8s

  {
    "name": "postgres01",
    "state": "Ready"
  }

Get the endpoints to connect to your Azure Arc-enabled PostgreSQL servers

To view the endpoints for a PostgreSQL server, run the following command:

az postgres server-arc endpoint list -n <server name> --k8s-namespace <namespace> --use-k8s

For example:

{
  "instances": [
    {
      "endpoints": [
        {
          "description": "PostgreSQL Instance",
          "endpoint": "postgresql://postgres:<replace with password>@123.456.78.912:5432"
        },
        {
          "description": "Log Search Dashboard",
        },
        {
          "description": "Metrics Dashboard",
          "endpoint": "https://98.765.432.11:3000/d/postgres-metrics?var-Namespace=arc&var-Name=postgres01"
        }
      ],
      "engine": "PostgreSql",
      "name": "postgres01"
    }
  ],
  "namespace": "arc"
}

You can use the PostgreSQL Instance endpoint to connect to the PostgreSQL server from your favorite tool: Azure Data Studio, pgcli psql, pgAdmin, etc.

At this time, use the insiders build of Azure Data Studio.

Special note about Azure virtual machine deployments

When you are using an Azure virtual machine, then the endpoint IP address will not show the public IP address. To locate the public IP address, use the following command:

az network public-ip list -g azurearcvm-rg --query "[].{PublicIP:ipAddress}" -o table

You can then combine the public IP address with the port to make your connection.

You may also need to expose the port of the PostgreSQL server through the network security gateway (NSG). To allow traffic through the (NSG), set a rule. To set a rule, you will need to know the name of your NSG. You determine the NSG using the command below:

az network nsg list -g azurearcvm-rg --query "[].{NSGName:name}" -o table

Once you have the name of the NSG, you can add a firewall rule using the following command. The example values here create an NSG rule for port 30655 and allows connection from any source IP address.

Replace the value of the --destination-port-ranges parameter below with the port number you got from the az postgres server-arc list command above.

az network nsg rule create -n db_port --destination-port-ranges 30655 --source-address-prefixes '*' --nsg-name azurearcvmNSG --priority 500 -g azurearcvm-rg --access Allow --description 'Allow port through for db access' --destination-address-prefixes '*' --direction Inbound --protocol Tcp --source-port-ranges '*'

Connect with Azure Data Studio

Open Azure Data Studio and connect to your instance with the external endpoint IP address and port number above, and the password you specified at the time you created the instance. If PostgreSQL isn't available in the Connection type dropdown, you can install the PostgreSQL extension by searching for PostgreSQL in the extensions tab.

Remember, if you are using an Azure VM you will need the public IP address, which is accessible via the following command:

az network public-ip list -g azurearcvm-rg --query "[].{PublicIP:ipAddress}" -o table

Connect with psql

To access your PostgreSQL server, pass the external endpoint of the PostgreSQL server that you retrieved from above:

You can now connect either psql:

psql postgresql://postgres:<EnterYourPassword>@10.0.0.4:30655

Related content

• Connect to your Azure Arc-enabled PostgreSQL server: read Get Connection Endpoints And Connection Strings

  * In the documents above, skip the sections Sign in to the Azure portal, & Create an Azure Database for PostgreSQL. Implement the remaining steps in your Azure Arc deployment. Those sections are specific to the Azure Database for PostgreSQL server offered as a PaaS service in the Azure cloud but the other parts of the documents are directly applicable to your Azure Arc-enabled PostgreSQL server.

• Storage configuration and Kubernetes storage concepts

• Expanding Persistent volume claims

• Kubernetes resource model