AdventureWorks database for Azure Database for PostgreSQL Flexible Server

This project restores the SQL Server AdventureWorks 2016 database backup converted to PostgreSQL schema to an Azure Database for PostgreSQL Flexible Server instance.

1. Provision an Azure Database for PostgreSQL Flexible Server instance

1. Open the script CreatePostgreSQLFlexibleServer.ps1 in Visual Studio Code or PowerShell ISE.
2. Alter the parameters for the function to match what you want the servername, resource group, region and server parameters to be.
   
3. The script call will output a Server object which we can use to get the Fully Qualified Name of the server. We will use this server name to connect to the PostgreSQL database via psql.
   The output will look similar to this:
   

I highly recommend to create the server using the postgres user as the admin user account. The AdventureWorks backup was created with the postgres user owning all objects and will error if the restore occurs under a different account.

2. Enable the necessary PostgreSQL extensions

1. Navigate to the 'Server Parameters' section for the Azure Database for PostgreSQL Flexible Server.
2. Find the azure.extensions option from the Server parameters list and enable the 'TABLEFUNC' and 'UUID-OSSP' extensions. The AdventureWorks database uses these features and the database restore will error if these extensions are not enabled.
3. Click the Save option to enable these extensions on the server.

3. Create the AdventureWorks database on the Azure Database for PostgreSQL Flexible Server

1. Download and install PGAdmin: https://www.pgadmin.org/download/
2. Navigate to where PGAdmin is installed (the location of D:\Program Files\pgAdmin 4\v5\runtime on this test machine) and open a Command Prompt.
3. Execute the following command to connect to the PostgreSQL Flexible Server. Be sure to use the Fully Qualified Name of your server and to enter your Password when prompted.

   psql.exe "host=timchapflexpgtest6.postgres.database.azure.com port=5432 dbname=postgres user=postgres"

The output will look similar to the following:
4. Create the AdventureWorks database by using the following SQL statement:

CREATE DATABASE adventureworks;

Which will have output similar to the following:

4. Restore the AdventureWorks database on the Azure Database for PostgreSQL Flexible Server

1. Execute the pg_restore below. Be sure to use the Fully Qualified Name of your server and the location of where you've cloned this repo.

pg_restore -h timchapflexpgtest6.postgres.database.azure.com -U postgres  -d adventureworks D:/GitHub/postgresql-adventureworks/AdventureWorksPG.gz 

The output should look similar to the following. Note: This script returns 2 Azure extension related errors. These can be safely ignored.

5. Log into the AdventureWorks database via pgAdmin

1. Open pgAdmin. You may need to set up a password if this is your first time using it.
2. Under Browser, right click and choose Create-->Server Group. Give the Server Group a name and then Choose Save. Mine is named Flexible.
3. Enter the name of the Azure Database for PostgreSQL Flexible Server along with the username you chose when you created the Server. The output will look similar to the following:
   
4. Expand the database in the Browser to view the AdventureWorks tables.