| title | description | services | author | ms.author | manager | editor | ms.service | ms.devlang | ms.topic | ms.date |
|---|---|---|---|---|---|---|---|---|---|---|
Create an Azure Database for PostgreSQL using the Azure CLI | Microsoft Docs |
Quickstart guide to create and manage Azure Database for PostgreSQL server using Azure CLI (command line interface). |
postgresql |
sanagama |
sanagama |
jhubbard |
jasonwhowell |
postgresql |
azure-cli |
quickstart |
11/03/2017 |
Azure Database for PostgreSQL is a managed service that enables you to run, manage, and scale highly available PostgreSQL databases in the cloud. The Azure CLI is used to create and manage Azure resources from the command line or in scripts. This quickstart shows you how to create an Azure Database for PostgreSQL server in an Azure resource group using the Azure CLI.
If you don't have an Azure subscription, create a free account before you begin.
[!INCLUDE cloud-shell-try-it]
If you choose to install and use the CLI locally, this article requires that you are running the Azure CLI version 2.0 or later. Run az --version to find the version. If you need to install or upgrade, see Install Azure CLI 2.0.
If you are running the CLI locally, you need to log in to your account using the az login command.
az login
If you have multiple subscriptions, choose the appropriate subscription in which the resource should be billed. Select a specific subscription ID under your account using az account set command.
az account set --subscription 00000000-0000-0000-0000-000000000000
Create an Azure resource group using the az group create command. A resource group is a logical container into which Azure resources are deployed and managed as a group. 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 using the az postgres server create command. A server contains a group of databases managed as a group.
The following example creates a server named mypgserver-20170401 in your resource group myresourcegroup with server admin login mylogin. The name of a server maps to DNS name and is thus required to be globally unique in Azure. Substitute the <server_admin_password> with your own value.
az postgres server create --resource-group myresourcegroup --name mypgserver-20170401 --location westus --admin-user mylogin --admin-password <server_admin_password> --performance-tier Basic --compute-units 50 --version 9.6
Important
The server admin login and password that you specify here are required to log in to the server and its databases later in this quick start. Remember or record this information for later use.
By default, postgres database gets created under your server. The postgres database is a default database meant for use by users, utilities, and third-party applications.
Create an Azure PostgreSQL server-level firewall rule with the az postgres server firewall-rule create command. A server-level firewall rule allows an external application, such as psql or PgAdmin to connect to your server through the Azure PostgreSQL service firewall.
You can set a firewall rule that covers an IP range to be able to connect from your network. The following example uses az postgres server firewall-rule create to create a firewall rule AllowAllIps for an IP address range. To open all IP addresses, use 0.0.0.0 as the starting IP address and 255.255.255.255 as the ending address.
az postgres server firewall-rule create --resource-group myresourcegroup --server mypgserver-20170401 --name AllowAllIps --start-ip-address 0.0.0.0 --end-ip-address 255.255.255.255
Note
Azure PostgreSQL server communicates over port 5432. When connecting from within a corporate network, outbound traffic over port 5432 may not be allowed by your network's firewall. Have your IT department open port 5432 to connect to your Azure SQL Database server.
To connect to your server, you need to provide host information and access credentials.
az postgres server show --resource-group myresourcegroup --name mypgserver-20170401
The result is in JSON format. Make a note of the administratorLogin and fullyQualifiedDomainName.
{
"administratorLogin": "mylogin",
"fullyQualifiedDomainName": "mypgserver-20170401.postgres.database.azure.com",
"id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/myresourcegroup/providers/Microsoft.DBforPostgreSQL/servers/mypgserver-20170401",
"location": "westus",
"name": "mypgserver-20170401",
"resourceGroup": "myresourcegroup",
"sku": {
"capacity": 50,
"family": null,
"name": "PGSQLS2M50",
"size": null,
"tier": "Basic"
},
"sslEnforcement": null,
"storageMb": 51200,
"tags": null,
"type": "Microsoft.DBforPostgreSQL/servers",
"userVisibleState": "Ready",
"version": "9.6"
}If your client computer has PostgreSQL installed, you can use a local instance of psql to connect to an Azure PostgreSQL server. Let's now use the psql command-line utility to connect to the Azure PostgreSQL server.
- Run the following psql command to connect to an Azure Database for PostgreSQL server
psql --host=<servername> --port=<port> --username=<user@servername> --dbname=<dbname>
For example, the following command connects to the default database called postgres on your PostgreSQL server mypgserver-20170401.postgres.database.azure.com using access credentials. Enter the <server_admin_password> you chose when prompted for password.
psql --host=mypgserver-20170401.postgres.database.azure.com --port=5432 --username=mylogin@mypgserver-20170401 --dbname=postgres
- Once you are connected to the server, create a blank database at the prompt.
CREATE DATABASE mypgsqldb;- At the prompt, execute the following command to switch connection to the newly created database mypgsqldb:
\c mypgsqldbTo connect to Azure PostgreSQL server using the GUI tool pgAdmin
-
Launch the pgAdmin application on your client computer. You can install pgAdmin from http://www.pgadmin.org/.
-
Choose Add New Server from the Quick Links menu.
-
In the Create - Server dialog box General tab, enter a unique friendly Name for the server. Say Azure PostgreSQL Server.
-
In the Create - Server dialog box, Connection tab:
- Enter the fully qualified server name (for example, mypgserver-20170401.postgres.database.azure.com) in the Host Name/ Address box.
- Enter port 5432 into the Port box.
- Enter the Server admin login (user@mypgserver) obtained earlier in this quickstart and password you entered when you created the server into the Username and Password boxes, respectively.
- Select SSL Mode as Require. By default, all Azure PostgreSQL servers are created with SSL enforcing turned ON. To turn OFF SSL enforcing, see details in Enforcing SSL.
-
Click Save.
-
In the Browser left pane, expand the Server Groups. Choose your server Azure PostgreSQL Server.
-
Choose the Server you connected to, and then choose Databases under it.
-
Right-click on Databases to Create a Database.
-
Choose a database name mypgsqldb and the owner for it as server admin login mylogin.
-
Click Save to create a blank database.
-
In the Browser, expand the Servers group. Expand the server you created, and see the database mypgsqldb under it.
Clean up all resources you created in the quickstart by deleting the Azure resource group.
Tip
Other quickstarts in this collection build upon this quickstart. If you plan to continue to work with subsequent quickstarts, do not clean up the resources created in this quickstart. If you do not plan to continue, use the following steps to delete all resources created by this quickstart in the Azure CLI.
az group delete --name myresourcegroup
If you would just like to delete the one newly created server, you can run az postgres server delete command.
az postgres server delete --resource-group myresourcegroup --name mypgserver-20170401
[!div class="nextstepaction"] Migrate your database using Export and Import