Exercise: Investigate metadata with system catalogs and system views

Completed

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 PostgreSQL server

  1. Sign in to the Azure portal.

  2. Select Create a resource.

    Screenshot of Create a resource icon.

  3. In Search services and marketplace type PostgreSQL and select Azure Database for PostgreSQL Flexible Server.

  4. Select Create.

  5. Select the relevant Subscription, Resource group, and Region for your environment.

  6. Type a valid Server name.

  7. In PostgreSQL version, select 13.

  8. In Workload type, select Development. Screenshot of settings to create PostgreSQL server.

  9. In Admin username type demo.

  10. In Password type Pa$$w0rd.

  11. In Confirm password type Pa$$w0rd.

  12. Select Next: Networking >.

  13. In Firewall rules, select Allow public access from any Azure service within Azure to this server.

  14. Select Add current client IP address. Screenshot of firewall rules.

  15. Select Review + create.

  16. Select Create.

  17. Wait until the deployment is complete.

Restore AdventureWorks database

  1. Clone the PostgreSQL samples database repository at PostgreSQL Labs.

    1. For example, you could use the following code from a command prompt:
    git clone https://github.com/MicrosoftLearning/mslearn-postgres c:\postgresql-samples-databases
    
  2. Download and install pgAdmin 4 from Download

  3. Download and install Azure Data Studio from Download and install Azure Data Studio.

  4. Start Azure Data Studio.

  5. Select the View menu and select Extensions.

  6. In Search Extensions in Marketplace, type PostgreSQL and select Install. Screenshot of PostgreSQL extension install button.

  7. Select Connections.

    Screenshot of Connections button.

  8. Select Servers and select New connection.

    Screenshot of Create a connection button.

  9. In Connection type, select PostgreSQL.

  10. In Server name, type the value that you specified when you deployed the server.

  11. In User name, type demo.

  12. In Password, type Pa$$w0rd.

  13. Select Remember password.

  14. Start pgAdmin and enter the password that you specified during installation.

  15. Right-click Servers, select Register, and select Server. Screenshot of server registration menu in pgAdmin.

  16. In Name, type PostgreSQL Exercise 8 and select the Connection tab.

  17. In Host name/address, type the value that you specified when you deployed the server.

  18. In Username, type demo.

  19. In Password, type Pa$$w0rd.

  20. Select Save password.

  21. Select Save.

  22. Expand PostgreSQL Exercise, right-click Databases, select Create, and select Database. Screenshot showing Create Database menu item.

  23. In Database, type adventureworks and select Save.

  24. Right-click adventureworks and select Restore.

  25. In Filename, type the path that you specified in the git clone statement plus \AdventureWorksPG.gz.

  26. In Number of jobs, type 1.

  27. In Role name, select demo.

  28. Select Restore.

    Screenshot of Restore dialog box.

  29. Wait until the restore process completes.

  30. You can disregard any errors that occur because those objects aren't required for these modules.

Task 1: View metadata in the Azure portal

  1. Navigate to the Azure portal and sign in.

  2. Select All resources.

    Screenshot of All resources icon.

  3. Select the Azure Database for PostgreSQL flexible server that you created for this exercise.

  4. In Monitoring, select Metrics.

    Screenshot of Metrics icon.

  5. Select Metric and select CPU percent. Screenshot showing Metric selection.

  6. Take note that you can view various metrics about your databases.

Task 2: View data in system catalog tables

  1. Switch to Azure Data Studio.

  2. In SERVERS, select your PostgreSQL server and wait until a connection is made and a green circle is displayed on the server.

    Screenshot of connected server.

  3. Right-click the server and select New Query.

  4. Type the following SQL and select Run:

    SELECT datname, xact_commit, xact_rollback FROM pg_stat_database;
    
  5. Take note that you can view commits and rollbacks for each database.

Task 3: View a complex metadata query using a system view

  1. Right-click the server and select New Query.
  2. Type the following SQL and select Run:
    SELECT *
    FROM pg_catalog.pg_stats;
    
  3. Take note that you can view a large amount of statistics information.
  4. 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));
    

Task 4: Delete the PostgreSQL server

When you've completed all of the tasks in the exercise, you can stop the PostgreSQL server.

  1. In the Azure portal, select your PostgreSQL server and select Overview.

  2. Select Stop.

    Screenshot of the Stop icon.