Configure system parameters and explore metedata with system catalogs and views

In this exercise you will look at system parameters and metadata in PostgreSQL.

Before you start

[!IMPORTANT] You need your own Azure subscription to complete the exercises in this module. If you don’t have an Azure subscription, you can set up a free trial account at Build in the cloud with an Azure free account.

Create the exercise environment

Deploy resources into your Azure subscription

This step guides you through using Azure CLI commands from the Azure Cloud Shell to create a resource group and run a Bicep script to deploy the Azure services necessary for completing this exercise into your Azure subscription.

Note

If you are doing multiple modules in this learning path, you can share the Azure environment between them. In that case, you only need to complete this resource deployment step once.

Open a web browser and navigate to the Azure portal.
Select the Cloud Shell icon in the Azure portal toolbar to open a new Cloud Shell pane at the bottom of your browser window.

If prompted, select the required options to open a Bash shell. If you have previously used a PowerShell console, switch it to a Bash shell.
At the Cloud Shell prompt, enter the following to clone the GitHub repo containing exercise resources:
```
 git clone https://github.com/MicrosoftLearning/mslearn-postgresql.git
```
Next, you run three commands to define variables to reduce redundant typing when using Azure CLI commands to create Azure resources. The variables represent the name to assign to your resource group (RG_NAME), the Azure region (REGION) into which resources will be deployed, and a randomly generated password for the PostgreSQL administrator login (ADMIN_PASSWORD).

In the first command, the region assigned to the corresponding variable is eastus, but you can also replace it with a location of your preference.
```
 REGION=eastus
```
The following command assigns the name to be used for the resource group that will house all the resources used in this exercise. The resource group name assigned to the corresponding variable is rg-learn-work-with-postgresql-$REGION, where $REGION is the location you specified above. However, you can change it to any other resource group name that suits your preference.
```
 RG_NAME=rg-learn-work-with-postgresql-$REGION
```
The final command randomly generates a password for the PostgreSQL admin login. Make sure you copy it to a safe place so that you can use it later to connect to your PostgreSQL flexible server.
```
 a=()
 for i in {a..z} {A..Z} {0..9}; 
    do
    a[$RANDOM]=$i
 done
 ADMIN_PASSWORD=$(IFS=; echo "${a[*]::18}")
 echo "Your randomly generated PostgreSQL admin user's password is:"
 echo $ADMIN_PASSWORD
```
If you have access to more than one Azure subscription, and your default subscription is not the one in which you want to create the resource group and other resources for this exercise, run this command to set the appropriate subscription, replacing the <subscriptionName|subscriptionId> token with either the name or ID of the subscription you want to use:
```
 az account set --subscription <subscriptionName|subscriptionId>
```
Run the following Azure CLI command to create your resource group:
```
 az group create --name $RG_NAME --location $REGION
```
Finally, use the Azure CLI to execute a Bicep deployment script to provision Azure resources in your resource group:
```
 az deployment group create --resource-group $RG_NAME --template-file "mslearn-postgresql/Allfiles/Labs/Shared/deploy-postgresql-server.bicep" --parameters adminLogin=pgAdmin adminLoginPassword=$ADMIN_PASSWORD
```
The Bicep deployment script provisions the Azure services required to complete this exercise into your resource group. The resources deployed are an Azure Database for PostgreSQL - Flexible Server. The bicep script also creates a database - which can be configured on the commandline as a parameter.

The deployment typically takes several minutes to complete. You can monitor it from the Cloud Shell or navigate to the Deployments page for the resource group you created above and observe the deployment progress there.
Close the Cloud Shell pane once your resource deployment is complete.

Troubleshooting deployment errors

You may encounter a few errors when running the Bicep deployment script. The most common messages and the steps to resolve them are:

If you previously ran the Bicep deployment script for this learning path and subsequently deleted the resources, you may receive an error message like the following if you are attempting to rerun the script within 48 hours of deleting the resources:

  {"code": "InvalidTemplateDeployment", "message": "The template deployment 'deploy' is not valid according to the validation procedure. The tracking id is '4e87a33d-a0ac-4aec-88d8-177b04c1d752'. See inner errors for details."}
    
  Inner Errors:
  {"code": "FlagMustBeSetForRestore", "message": "An existing resource with ID '/subscriptions/{subscriptionId}/resourceGroups/rg-learn-postgresql-ai-eastus/providers/Microsoft.CognitiveServices/accounts/{accountName}' has been soft-deleted. To restore the resource, you must specify 'restore' to be 'true' in the property. If you don't want to restore existing resource, please purge it first."}

If you receive this message, modify the azure deployment group create command above to set the restore parameter equal to true and rerun it.

If the selected region is restricted from provisioning specific resources, you must set the REGION variable to a different location and rerun the commands to create the resource group and run the Bicep deployment script.

  {"status":"Failed","error":{"code":"DeploymentFailed","target":"/subscriptions/{subscriptionId}/resourceGroups/{resourceGrouName}/providers/Microsoft.Resources/deployments/{deploymentName}","message":"At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/arm-deployment-operations for usage details.","details":[{"code":"ResourceDeploymentFailure","target":"/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.DBforPostgreSQL/flexibleServers/{serverName}","message":"The resource write operation failed to complete successfully, because it reached terminal provisioning state 'Failed'.","details":[{"code":"RegionIsOfferRestricted","message":"Subscriptions are restricted from provisioning in this region. Please choose a different region. For exceptions to this rule please open a support request with Issue type of 'Service and subscription limits'. See https://review.learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-request-quota-increase for more details."}]}]}}

If the script is unable to create an AI resource due to the requirement to accept the responsible AI agreement, you may experience the following error; in which case use the Azure Portal user interface to create an Azure AI Services resource, and then re-run the deployment script.

  {"code": "InvalidTemplateDeployment", "message": "The template deployment 'deploy' is not valid according to the validation procedure. The tracking id is 'f8412edb-6386-4192-a22f-43557a51ea5f'. See inner errors for details."}
     
  Inner Errors:
  {"code": "ResourceKindRequireAcceptTerms", "message": "This subscription cannot create TextAnalytics until you agree to Responsible AI terms for this resource. You can agree to Responsible AI terms by creating a resource through the Azure Portal then trying again. For more detail go to https://go.microsoft.com/fwlink/?linkid=2164190"}

Connect to the database with Azure Data Studio

If you haven’t done so yet, clone the lab scripts from the PostgreSQL Labs GitHub repository locally:
1. Open a command line/terminal.
2. Run the command:
```
md .\DP3021Lab
git clone https://github.com/MicrosoftLearning/mslearn-postgresql.git .\DP3021Lab
```
  NOTE
  
  If git is not installed, download and install the git app and try running the previous commands again.
If you haven’t installed Azure Data Studio yet, download and install Azure Data Studio.
If you haven’t installed the PostgreSQL extension in Azure Data Studio, install it now.
Open Azure Data Studio.
Select Connections.
Select Servers and select New connection.
In Connection type, select PostgreSQL.
In Server name, type the value that you specified when you deployed the server.
In User name, type pgAdmin.
In Password, type enter the randomly generated password for the pgAdmin login you generated
Select Remember password.
Click Connect
If you haven’t created the zoodb database yet, select File, Open file and navigate to the folder where you saved the scripts. Select ../Allfiles/Labs/02/Lab2_ZooDb.sql and Open.
1. Highlight the DROP and CREATE statements and run them.
2. At the top of the screen, use the drop-down arrow to display the databases on the server, including zoodb and system databases. Select the zoodb database.
3. Highlight the Create tables, Create foreign keys, and Populate tables sections and run them.
4. Highlight the 3 SELECT statements at the end of the script and run them to verify that the tables were created and populated.

Task 1: Explore the vacuum process in PostgreSQL

If not opened, open Azure Data Studio.
In Azure Data Studio, select File, Open File, and then navigate to the lab scripts. Select ../Allfiles/Labs/07/Lab7_vacuum.sql and then select Open. If necessary, reconnect to the server.
Select the zoodb database from the database dropdown.
Highlight and run the section Check zoodb database is selected. If necessary, make zoodb the current database using the drop-down list.
Highlight and run the section Display dead tuples. This query displays the number of dead and live tuples in the database. Make a note of the number of dead tuples.
Highlight and run the section Change weight 10 times in a row. This query updates the weight column for all the animals.
Run the section under Display dead tuples again. Make a note of the number of dead tuples after the updates have been done.
Run the section under Manually run VACUUM to run the vacuum process.
Run the section under Display dead tuples again. Make a note of the number of dead tuples after the vacuum process has been run.

Task 2: Configure autovacuum server parameters

In the Azure portal, navigate to your Azure Database for PostgreSQL flexible server.
Under Settings, select Server parameters.
In the search bar, type vacuum. Find the following parameters, and change the values as follows:
1. autovacuum = ON (it should be ON by default)
2. autovacuum_vacuum_scale_factor = 0.1
3. autovacuum_vacuum_threshold = 50
This is like running the autovacuum process when 10% of a table has rows marked for deletion, or 50 rows updated or deleted in any one table.
Select Save. The server is restarted.

Task 3: View PostgreSQL metadata in the Azure portal

Navigate to the Azure portal and sign in.
Search for Azure Database for PostgreSQL and select it.
Select the Azure Database for PostgreSQL flexible server that you created for this exercise.
In Monitoring, select Metrics.
Select Metric and select CPU percent.
Take note that you can view various metrics about your databases.

Task 4: View data in system catalog tables

Switch to Azure Data Studio.
In SERVERS, select your PostgreSQL server and wait until a connection is made and a green circle is displayed on the server.
Right-click the server and select New Query.

Type the following SQL and select Run:

 SELECT datname, xact_commit, xact_rollback FROM pg_stat_database;

Take note that you can view commits and rollbacks for each database.

View a complex metadata query using a system view

Right-click the server and select New Query.
Type the following SQL and select Run:
```
 SELECT *
 FROM pg_catalog.pg_stats;
```
Take note that you can view a large amount of statistics information.

By using system views, you can reduce the complexity of the SQL that you need to write. The previous query would need the following code if you weren’t using the pg_stats view:

 SELECT n.nspname AS schemaname,
 c.relname AS tablename,
 a.attname,
 s.stainherit AS inherited,
 s.stanullfrac AS null_frac,
 s.stawidth AS avg_width,
 s.stadistinct AS n_distinct,
     CASE
         WHEN s.stakind1 = 1 THEN s.stavalues1
         WHEN s.stakind2 = 1 THEN s.stavalues2
         WHEN s.stakind3 = 1 THEN s.stavalues3
         WHEN s.stakind4 = 1 THEN s.stavalues4
         WHEN s.stakind5 = 1 THEN s.stavalues5
         ELSE NULL::anyarray
     END AS most_common_vals,
     CASE
         WHEN s.stakind1 = 1 THEN s.stanumbers1
         WHEN s.stakind2 = 1 THEN s.stanumbers2
         WHEN s.stakind3 = 1 THEN s.stanumbers3
         WHEN s.stakind4 = 1 THEN s.stanumbers4
         WHEN s.stakind5 = 1 THEN s.stanumbers5
         ELSE NULL::real[]
     END AS most_common_freqs,
     CASE
         WHEN s.stakind1 = 2 THEN s.stavalues1
         WHEN s.stakind2 = 2 THEN s.stavalues2
         WHEN s.stakind3 = 2 THEN s.stavalues3
         WHEN s.stakind4 = 2 THEN s.stavalues4
         WHEN s.stakind5 = 2 THEN s.stavalues5
         ELSE NULL::anyarray
     END AS histogram_bounds,
     CASE
         WHEN s.stakind1 = 3 THEN s.stanumbers1[1]
         WHEN s.stakind2 = 3 THEN s.stanumbers2[1]
         WHEN s.stakind3 = 3 THEN s.stanumbers3[1]
         WHEN s.stakind4 = 3 THEN s.stanumbers4[1]
         WHEN s.stakind5 = 3 THEN s.stanumbers5[1]
         ELSE NULL::real
     END AS correlation,
     CASE
         WHEN s.stakind1 = 4 THEN s.stavalues1
         WHEN s.stakind2 = 4 THEN s.stavalues2
         WHEN s.stakind3 = 4 THEN s.stavalues3
         WHEN s.stakind4 = 4 THEN s.stavalues4
         WHEN s.stakind5 = 4 THEN s.stavalues5
         ELSE NULL::anyarray
     END AS most_common_elems,
     CASE
         WHEN s.stakind1 = 4 THEN s.stanumbers1
         WHEN s.stakind2 = 4 THEN s.stanumbers2
         WHEN s.stakind3 = 4 THEN s.stanumbers3
         WHEN s.stakind4 = 4 THEN s.stanumbers4
         WHEN s.stakind5 = 4 THEN s.stanumbers5
         ELSE NULL::real[]
     END AS most_common_elem_freqs,
     CASE
         WHEN s.stakind1 = 5 THEN s.stanumbers1
         WHEN s.stakind2 = 5 THEN s.stanumbers2
         WHEN s.stakind3 = 5 THEN s.stanumbers3
         WHEN s.stakind4 = 5 THEN s.stanumbers4
         WHEN s.stakind5 = 5 THEN s.stanumbers5
         ELSE NULL::real[]
     END AS elem_count_histogram
 FROM pg_statistic s
  JOIN pg_class c ON c.oid = s.starelid
  JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum
  LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
 WHERE NOT a.attisdropped AND has_column_privilege(c.oid, a.attnum, 'select'::text) AND (c.relrowsecurity = false OR NOT row_security_active(c.oid));

Exercise Clean-up

The Azure Database for PostgreSQL we deployed in this exercise will incur charges you can delete the server after this exercise. Alternatively, you can delete the rg-learn-work-with-postgresql-eastus resource group to remove all resources that we deployed as part of this exercise.
If needed, delete the .\DP3021Lab folder.