Quickstart: Create an Azure Database for PostgreSQL - Flexible Server instance in the Azure portal

APPLIES TO: Azure Database for PostgreSQL - Flexible Server

Azure Database for PostgreSQL flexible server is a managed service that you use to run, manage, and scale highly available PostgreSQL databases in the cloud. This Quickstart shows you how to create an Azure Database for PostgreSQL flexible server instance in about five minutes using the Azure portal.

If you don't have an Azure subscription, create a free Azure account before you begin.

Sign in to the Azure portal

Open your web browser and go to the portal. Enter your credentials to sign in to the portal. The default view is your service dashboard.

Create an Azure Database for PostgreSQL server

An Azure Database for PostgreSQL flexible server instance is created with a configured set of compute and storage resources. The server is created within an Azure resource group.

To create an Azure Database for PostgreSQL flexible server instance, take the following steps:

1. Select Create a resource (+) in the upper-left corner of the portal.

2. Select Databases > Azure Database for PostgreSQL.

   

1. Fill out the Basics form with the following information:

   

   Setting	Suggested Value	Description
   Subscription	Your subscription name	The Azure subscription that you want to use for your server. If you have multiple subscriptions, choose the subscription in which you'd like to be billed for the resource.
   Resource group	Your resource group name	A new resource group name or an existing one from your subscription.
   Workload type	Default SKU selection	You can choose from Development (Burstable SKU), Production small/medium (General Purpose SKU), or Production large (Memory Optimized SKU). You can further customize the SKU and storage by clicking Configure server link.
   Availability zone	Your preferred AZ	You can choose in which availability zone you want your server to be deployed. This is useful to co-locate with your application. If you choose No preference, a default AZ is selected for you.
   High availability	Enable it for same zone or zone-redundant deployment	By selecting this option, a standby server with the same configuration as your primary will be automatically provisioned in the same avaibality zone or a different availability zone in the same region, depending on the option selected for High availability mode. Note: You can enable or disable high availability post server creation as well.
   Server name	Your server name	A unique name that identifies your Azure Database for PostgreSQL flexible server instance. The domain name postgres.database.azure.com is appended to the server name you provide. The server name can contain only lowercase letters, numbers, and the hyphen (-) character. It must contain between 3 and 63 characters.
   Admin username	Your admin user name	Your own login account to use when you connect to the server. The admin username must contain between 1 and 63 characters, must only cotain numbers and letters, can’t start with pg_ and can't be azure_superuser, azure_pg_admin, admin, administrator, root, guest, or public.
   Password	Your password	Specify a password for the server admin account. The password must contain between 8 and 128 characters. It must also contain characters from three of the following four categories: English uppercase letters, English lowercase letters, numbers (0 through 9), and non-alphanumeric characters (!, $, #, %, and so on). Your password can't contain all or part of the login name. Part of a login name is defined as three or more consecutive alphanumeric characters.
   Location	The region closest to your users	The location that's closest to your users.
   Version	The latest major version	The latest PostgreSQL major version, unless you have specific requirements otherwise.
   Compute + storage	General Purpose, 4 vCores, 512 GB, 7 days	The compute, storage, and backup configurations for your new server. Select Configure server. General Purpose, 4 vCores, 512 GB, and 7 days are the default values for Compute tier, vCore, Storage, and Backup retention period (in days). You can leave those sliders as they are or you can adjust them. To configure your server with geo-redundant backups to protect from region-level failures, you can enable the Recover from regional outage or disaster checkbox. Note that the geo-redundant backup can be configured only at the time of server creation. To save this pricing tier selection, select Save. The next screenshot captures these selections.

   

2. Configure Networking options

3. On the Networking tab, you can choose how your server is reachable. Azure Database for PostgreSQL flexible server provides two ways to connect to your server:

   • Public access (allowed IP addresses)
   • Private access (VNet Integration)

   When you use public access, access to your server is limited to allowed IP addresses that you add to a firewall rule. This method prevents external applications and tools from connecting to the server and any databases on the server, unless you create a rule to open the firewall for a specific IP address or range. When you use private access (VNet Integration), access to your server is limited to your virtual network. Learn more about connectivity methods in the concepts article.

   In this quickstart, you learn how to enable public access to connect to the server. On the Networking tab, for Connectivity method select Public access (alllowed IP addresses). To configure Firewall rules, select Add current client IP address.

   Note

   You can't change the connectivity method after you create the server. For example, if you select Public access (allowed IP addresses) when you create the server, you can't change to Private access (VNet Integration) after the server is created. We highly recommend that you create your server with private access to help secure access to your server via VNet Integration. Learn more about private access in the concepts article.

   

4. Select Review + create to review your selections. Select Create to provision the server. This operation may take a few minutes.

5. You will automatically be taken to a screen in which you can monitor the progress of your deployment. Once the deployment is done, you can select Pin to dashboard, which creates a tile for this server on your Azure portal dashboard as a shortcut to the server's Overview page. Selecting Go to resource opens the server's Overview page.

   

   By default, a postgres database is created under your server. The postgres database is a default database that's meant for use by users, utilities, and third-party applications. (The other default database is azure_maintenance. Its function is to separate the managed service processes from user actions. You can't access this database.)

   Note

   Connections to your Azure Database for PostgreSQL flexible server instance communicate over port 5432. When you try to connect from within a corporate network, outbound traffic over port 5432 might not be allowed by your network's firewall. If so, you can't connect to your server unless your IT department opens port 5432.

Get the connection information

When you create your Azure Database for PostgreSQL flexible server instance, a default database named postgres is created. To connect to your database server, you need your full server name and admin login credentials. You might have noted those values earlier in the Quickstart article. If you didn't, you can easily find the server name and login information on the server Overview page in the portal.

Open your server's Overview page. Make a note of the Server name and the Server admin login name. Hover your cursor over each field, and the copy symbol appears to the right of the text. Select the copy symbol as needed to copy the values.

Connect to the Azure Database for PostgreSQL flexible server database using psql

There are a number of applications you can use to connect to your Azure Database for PostgreSQL flexible server instance. If your client computer has PostgreSQL installed, you can use a local instance of psql to connect to an Azure Database for PostgreSQL flexible server instance. Let's now use the psql command-line utility to connect to the Azure Database for PostgreSQL flexible server instance.

1. Run the following psql command to connect to an Azure Database for PostgreSQL flexible server instance.

   psql --host=<servername> --port=<port> --username=<user> --dbname=<dbname>
   

   For example, the following command connects to the default database called postgres on your Azure Database for PostgreSQL flexible server instance mydemoserver.postgres.database.azure.com using access credentials. Enter the <server_admin_password> you chose when prompted for password.

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

   After you connect, the psql utility displays a postgres prompt where you type sql commands. In the initial connection output, a warning may appear because the psql you're using might be a different version than the Azure Database for PostgreSQL flexible server version.

   Example psql output:

   psql (12.3 (Ubuntu 12.3-1.pgdg18.04+1), server 13.2)
   WARNING: psql major version 12, server major version 13.
         Some psql features might not work.
   SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
   Type "help" for help.
   
   postgres=>
   
   

   Tip

   If the firewall isn't configured to allow the IP address of your client, the following error occurs:

   "psql: FATAL: no pg_hba.conf entry for host <IP address>, user "myadmin", database "postgres", SSL on FATAL: SSL connection is required. Specify SSL options and retry.

   Confirm your client's IP is allowed in the firewall rules step above.

2. Create a blank database called "mypgsqldb" at the prompt by typing the following command:

   CREATE DATABASE mypgsqldb;
   

3. At the prompt, execute the following command to switch connections to the newly created database mypgsqldb:

   \c mypgsqldb
   

4. Type \q, and then select the Enter key to quit psql.

You connected to the Azure Database for PostgreSQL flexible server instance via psql, and you created a blank user database.

Clean up resources

You can clean up the resources that you created in the Quickstart in one of two ways. You can delete the Azure resource group, which includes all the resources in the resource group. If you want to keep the other resources intact, delete only the server resource.

To delete the entire resource group, including the newly created server:

1. Locate your resource group in the portal. On the menu on the left, select Resource groups. Then select the name of your resource group in which you created your Azure Database for PostgreSQL flexible server resource.

2. On your resource group page, select Delete. Enter the name of your resource group in the text box to confirm deletion. Select Delete.

To delete only the newly created server:

1. Locate your server in the portal, if you don't have it open. On the menu on the left, select All resources. Then search for the server you created.

2. On the Overview page, select Delete.

   

3. Confirm the name of the server you want to delete, and view the databases under it that are affected. Enter your server name in the text box, and select Delete.

Next steps