Exercise - Set up Azure Database for PostgreSQL

25 minutes

This exercise takes you through the process of creating and configuring Azure Database for PostgreSQL.

In this exercise, you'll:

Create a single-server instance of Azure Database for PostgreSQL.
Configure the Azure Database for PostgreSQL server.
Connect to the Azure Database for PostgreSQL server.
Create a sample database and table.

Prerequisites

To perform this exercise, you need:

An Azure subscription.
A Microsoft account or a Microsoft Entra account with the Global Administrator role in the Microsoft Entra tenant that's associated with the Azure subscription and with the Owner or Contributor role in the Azure subscription.

Create a single server instance of Azure Database for PostgreSQL

On a web browser, navigate to the Azure portal and sign in to access the Azure subscription that you'll be using in this module.
Use the Search resources, services, and docs text box at the beginning of the Azure portal page to search for Azure Database for PostgreSQL, and then in the list of results, in the Marketplace section, select Azure Database for PostgreSQL.
On the Select Azure Database for PostgreSQL deployment option blade, on the Single server tile, select Create.

On the Basics tab at the top of the Single server blade, configure the following settings and leave all other settings with their default values:

Setting	Configuration
Subscription	Select the name of the Azure subscription you'll be using in this module.
Resource group	Create a new resource group named postgresql-db-RG.
Server name	Enter a unique name that consists of lower-case letters, digits or dashes, and starts with a letter.
Data source	Select None.
Location	Select the Azure region closest to the location of your lab environment where you can create Azure Database for PostgreSQL instances.
Version	Select 11.
Compute + storage	Select the Configure server link. On the Configure blade, select Basic, set the vCore value to 1 and Storage to 5 GB, and then select OK.
Admin username	Enter Student.
Password	Enter a password.

Screenshot of the Basics tab of the Single server blade in the Azure portal.

Select Review + create.
On the Review + create tab of the Single server blade, select Create. Wait for the provisioning to complete. This might take about five minutes.

Note

The provisioning process automatically creates a database named postgres within the target server.

Configure the Azure Database for PostgreSQL server

Within the browser window displaying the Azure portal with the Azure Database for PostgreSQL single server provisioning status blade, select Go to resource.
On the Azure Database for PostgreSQL single server blade, in the vertical menu, in the Settings section, select Connection security.
On the Connection security blade, set Allow access to Azure services to Yes, select + Add client IP, set Enforce SSL connection to DISABLED, and then select Save.

Note

These settings will allow connectivity to the database from your computer and from applications running in Azure.

Note

Disabling the SSL enforcement is meant strictly to simplify the subsequent exercises. In general, you should keep this setting enabled.

Connect to the Azure Database for PostgreSQL server

Within the browser window displaying the Azure portal with the Azure Database for PostgreSQL single server blade, in the vertical menu, select Overview.
In the Essentials section, identify the entries next to the Server name and Admin username labels and record their values.

You'll use these values to connect to the database from your computer and from applications running in Azure. Note that the username includes the @ symbol followed by the server name you specified in the previous task.
In the Azure portal, open the Cloud Shell by selecting its icon in the toolbar next to the search text box.
If you're prompted to select either Bash or PowerShell, select Bash.

Note

If this is the first time you're starting Azure Cloud Shell and you're presented with the You have no storage mounted message, select the subscription you're using in this lab, and then select Create storage.
Within the Bash session on the Azure Cloud Shell pane, run the following command to connect to the postgres database hosted on the newly deployed single server instance of Azure Database for PostgreSQL. Replace the <server_name> and <user_name> placeholders with the values you recorded previously in this task, and when prompted to provide the password, enter the password your created earlier in this unit:
```
psql --host=<server_name> --port=5432 --username=<user_name> --dbname=postgres
```
Note

Once you successfully connect, you should be presented with the postgres=> prompt.

Create a sample database and table

From the postgres=> prompt, run the following command to create a new database named cnainventory (make sure to include the semicolon at the end of the command):
```
CREATE DATABASE cnainventory;
```
Run the following command to switch the connection to the newly created database:
```
\c cnainventory
```
Run the following command to create an inventory table in the newly created database:
```
CREATE TABLE inventory (
id serial PRIMARY KEY, 
name VARCHAR(50), 
quantity INTEGER,
    date DATE NOT NULL DEFAULT NOW()::date
);
```
Note

The inclusion of the date column facilitates partitioning of the table based on date.
Run the following command to verify that the table has been created successfully:
```
\dt
```

Run the following command to load sample data into the newly created table:

INSERT INTO inventory (id, name, quantity) VALUES (1, 'yogurt', 200);
INSERT INTO inventory (id, name, quantity) VALUES (2, 'milk', 100);

Run the following command to verify that the table contains the data you inserted (the output should display newly added inventory items):
```
SELECT * FROM inventory;
```
Close the Cloud Shell pane displaying a Bash session.

Results

Congratulations! You've completed the first exercise of this module. In this exercise, you created a single server instance of Azure Database for PostgreSQL, configured its connection security options, connected to it, and added a database and table with sample inventory data.