DEV Community: Lester Sim

AWS Certified Database - Specialty Notes

Lester Sim — Sun, 13 Aug 2023 15:28:56 +0000

AWS Database

Disclaimer: The opinions expressed here are my own and I'm not writing on behalf of AWS or Amazon.

Here are some quick notes I've gathered to prepare for the certification:

Amazon RDS

Benefits of Managed Database

Automated provisioning
Continuous backups and ability to restore to specific timestamp
Monitoring dashboards
Read replicas for improved read performance
Multi AZ setup for Disaster Recovery
Maintenance windows for OS patching and version upgrades
Scaling capability (vertical and horizontal)
Storage backed by EBS (gp2 or io1). Can be set to auto-scaling.

Pricing Model

Pay as you go pricing model
Instance types
- On-demand (Pay for compute capacity per hour)
- Reserved (deeply discounted, 1-year or 3-year term contract)
Storage (GB/month) / Backups / Snapshot Export to S3
I/O (per million requests)
Data transfer

RDS Instance Types

Standard
Memory-optimized (memory-intensive, high performance workloads)

RDS Storage Types

General Purpose Storage: General Purpose SSD volumes offer cost-effective storage that is ideal for a broad range of workloads running on medium-sized DB instances. General Purpose storage is best suited for development and testing environments.
Provisioned IOPS: Provisioned IOPS storage is designed to meet the needs of I/O-intensive workloads, particularly database workloads, that require low I/O latency and consistent I/O throughput. Provisioned IOPS storage is best suited for production environments.
RDS Storage Auto Scaling: Storage is scaled up automatically when the utilization nears the provisioned capacity. Triggers:
- Free available space is less than 10% of the allocated storage.
- The low-storage condition lasts at least five minutes.
- At least 6 hours have passed since the last storage modification.
The additional storage is in increments of whichever of the following is greater:
- 5 GiB
- 10% of currently allocated storage
- Storage growth prediction for 7 hours based on the FreeStorageSpace metrics change in the past hour.

RDS Parameter Groups

Configuration file to implement on database instance
Default parameter group cannot be edited. To make config changes, you must create a new parameter group
Changes to dynamic parameters always get applied immediately (irrespective of Apply Immediately setting)
Changes to static parameters require a manual reboot

RDS Option Groups

For configuration of optional features offered by DB engines (not covered by parameter groups)

RDS Security

Traditional Username and Password can be used to log in to the database
IAM-based authentication can be used to login into RDS MySQL & PostgreSQL.
You cannot SSH into an RDS DB instance.
You can map multiple IAM users or roles to the same database user account
Rotating RDS DB Credentials: Use AWS Secrets Manager. Supports automatic rotation of secrets. Secrets Manager provides a Lambda rotation function and populates it automatically with the ARN in the secret.

RDS Backups

RDS supports automatic backups. Capture transaction logs in real time
Enabled by default with a 7-days retention period (0-35 days retention, 0=disable automatic backups) via Console. The default backup retention period is one day if you create the DB instance using the Amazon RDS API or the AWS CLI.
Disabling automatic backups for a DB instance deletes all existing automated backups for the instance
Automated backups are deleted when the DB instance is deleted. Only manually created DB Snapshots are retained after the DB Instance is deleted.
Manual snapshot limits (of 100 per region) does not apply to automated backups.
The first automatic backup is a full backup. Subsequent backups are incremental.
Backup Data is stored in a S3 bucket (owned and managed by RDS service, you won’t see them in your S3 console)
You can share manual DB snapshots with up to 20 AWS accounts. Automated Amazon RDS snapshots cannot be shared directly with other AWS accounts. Can share DB snapshots across different regions.

Multi-AZ Deployments and Read Replicas

Configuring and managing a Multi-AZ deployment: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.MultiAZ.html
Working with Read Replicas: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ReadRepl.html
To create read replicas, you need to enable automatic backups on source RDS DB instance.
Multi-AZ follows synchronous replication and spans at least two Availability Zones within a single region. Read Replicas follow asynchronous replication and can be within an Availability Zone, Cross-AZ, or Cross-Region.
Amazon RDS for MySQL, MariaDB and PostgreSQL allow you to add up to 15 read replicas to each DB Instance. Amazon RDS for Oracle and SQL Server allow you to add up to 5 read replicas to each DB Instance.
For managing multiple read replicas, you may add each read replica endpoint to a Route 53 record set and configure weighted routing to distribute traffic across different read replicas.

RDS Monitoring

In RDS Console/CloudWatch: CPU, Memory, DatabaseConnections, IOPS, disk space consumption, etc
RDS Recommendations: Automated suggestions for DB instances, read replicas, etc
RDS Enhanced Monitoring: Get real-time OS level metrics (CPU, Memory). Agent is automatically installed on DB server to collect metrics. Metrics will be pushed to CloudWatch as well.
RDS Performance Insights: Dashboard for performance tuning and analysis eg. which SQL query has the highest load. Automatically publishes metrics to CloudWatch.

Amazon Aurora

Differences with RDS

Multi-AZ deployments for RDS MySQL follow synchronous replication whereas Multi-AZ deployments for Aurora MySQL follow asynchronous replication
Read Replicas can be manually promoted to a standalone database instance for RDS MySQL whereas Read Replicas for Aurora MySQL can be promoted to the primary instance
The primary and standby DB instances are upgraded at the same time for RDS MySQL Multi-AZ. All instances are upgraded at the same time for Aurora MySQL

Aurora Backtracking

Restoring a DB cluster to a point in time launches a new DB cluster and restores it from backup data or a DB cluster snapshot, which can take hours. Backtracking a DB cluster doesn't require a new DB cluster and rewinds the DB cluster in minutes.
The limit for a backtrack window is 72 hours.
Backtracking affects the entire DB cluster. For example, you can't selectively backtrack a single table or a single data update.

Aurora Cloning

Aurora cloning works at the storage layer of an Aurora DB cluster. Uses a copy-on-write protocol.
Aurora cloning is especially useful for quickly setting up test environments using your production data, without risking data corruption.
Database cloning uses a copy-on-write protocol, in which data is copied only at the time the data changes, either on the source database or the clone database. Cloning is much faster than a manual snapshot of the DB cluster.

Failover

Read replica automatically promoted, failover automatically
Master instance that failed will become read replica when it comes back online

Aurora Global Database

1 Primary Region (R/W), up to 5 secondary regions (Read only). Underlying cluster storage volume replicated to another region.
If 1 region goes down, can promote another region to be the primary region.

Aurora Serverless

Amazon Aurora Serverless is an on-demand, autoscaling configuration for Amazon Aurora. It automatically starts up, shuts down, and scales capacity up or down based on your application's needs. You can run your database in the cloud without managing any database instances.

Amazon DynamoDB

Fully managed, serverless, Key-Value database.

Consistency

Eventually consistent is the default read consistent model for all read operations. When issuing eventually consistent reads to a DynamoDB table or an index, the responses may not reflect the results of a recently completed write operation. If you repeat your read request after a short time, the response should eventually return the more recent item. Eventually consistent reads are supported on tables, local secondary indexes, and global secondary indexes.
Read operations such as GetItem, Query, and Scan provide an optional ConsistentRead parameter. If you set ConsistentRead to true, DynamoDB returns a response with the most up-to-date data, reflecting the updates from all prior write operations that were successful. Strongly consistent reads are only supported on tables and local secondary indexes.

Scan vs Query Operation

Scan

The Scan operation returns one or more items and item attributes by accessing every item in a table or a secondary index. To have DynamoDB return fewer items, you can provide a FilterExpression operation.
Eventual/Strong Consistency
Prefer Query over Scan when possible. Query
Find items based on primary key values (partition key/sort key). Return all items with that partition key.
Eventual/Strong Consistency
Faster than Scan because it only scans through that parition specified

Primary Key

Simple Primary Key: Just 1 partition key
Composite Primary Key: Comprise of 1 partition key and 1 sort key

Partition Key: Used for partition selection via DynamoDB internal hash function
Sort Key: Range select or to order results. Sort keys may not be used on their own.

Local Secondary Indexes

Up to 5 LSIs
Has same partition key as the primary index of the table but has different sort key than the primary index of the table. A local secondary index is "local" in the sense that every partition of a local secondary index is scoped to a base table partition that has the same partition key value.
Can only be created at the time of creating the table and cannot be deleted later
Support eventual / strong / transactional consistency
Use Case:
- When application needs same partition key as the table
- When application needs strongly consistent index reads

Global Secondary Indexes

Up to 20 GSIs
Can have same or different partition key than the table’s primary index
Can have same or different sort key than the table’s primary index. Optional to have sort key.
A global secondary index is considered "global" because queries on the index can span all of the data in the base table, across all partitions.
Can have different schema from base table. Cannot fetch attributes from the base table other than the base table’s primary key attributes.
Supports only eventual consistency
Can be created or deleted any time
Has its own provisioned throughput. If the writes are throttled on the GSI, then the main table will be throttled too.
Use Case:
- When application needs different or same partition key as the table
- When application needs finer throughput control

DynamoDB Accelerator (DAX)

Amazon DynamoDB Accelerator (DAX) is a fully managed, highly available, in-memory cache for Amazon DynamoDB that delivers up to a 10 times performance improvement.

DynamoDB response times: Single-digit milliseconds
DynamoDB with DAX response times: Microseconds
Reduce read load on DynamoDB
Supports only eventual consistency
Redirect your DynamoDB API request to the DAX endpoint instead of DynamoDB endpoint

This is only a brief summary of the core topics I found to be important and not exhaustive. There are more database-related services covered in the certification. Please refer to https://aws.amazon.com/certification/certified-database-specialty/ for the full set of topics to prepare.

Deploy a Python Flask App on Amazon EC2 - Part 1

Lester Sim — Sun, 14 May 2023 07:42:20 +0000

In this series of blogs, we'll be deploying a Python Flask application on Amazon EC2 instances.

When we in our development stages of writing a Python Flask App, we are commonly running it on our local computer as the server and entering eg. localhost:5000 into our browser to see the changes. Now that the app is ready for deploying, how do we deploy it to the cloud?

End Goal
<< Insert Gif >>

Agenda
Part 1: What is Flask, WSGI, Nginx?
Part 2: Create an EC2 instance and accessing it
Part 3: Create Flask App and install Nginx on EC2 instance
Part 4: Configure Auto Scaling Group on AWS
Part 5: Making changes to our Flask App

AWS Machine Learning Certification: Exam Notes

Lester Sim — Mon, 10 Oct 2022 15:29:29 +0000

Disclaimer: The opinions expressed here are my own and I'm not writing on behalf of AWS or Amazon.

The AWS Machine Learning - Specialty Certification covers a wide spectrum of topics from data engineering to exploratory data analysis to model training and deployment. Here are some quick notes I've gathered to prepare for the certification:

AWS AI Services

Beneficial for developers who want to add AI into their applications through API calls instead of developing and training their own ML models from scratch.

Amazon Textract

Extract text from scanned documents using Optical Character Recognition (OCR).

Documents

Returns text, forms, tables and query responses.

Expenses

Extracts data from invoices/receipts eg. vendor name, invoice/receipt date, invoice/receipt number, item name, item price, item quantity, total amount.

Amazon Comprehend

Extract entities, key phrases, language, personal identifiable information (PII), and sentiments from text.

Entities

Extract entities from text documents eg. people, places, locations.

Using AWS Console:

Using AWS API:

Key Phrases

Extract the key phrases (one or more words) from text documents.

Using AWS Console:

Using AWS API:

Sentiment

Predict the overall sentiment of the text - positive, negative, neutral, mixed.

Using AWS Console:

Using AWS API:

Language

Predict the dominant language of the entire text. Amazon Comprehend can recognize 100 languages.

Using AWS Console:

Using AWS API:

Personally Identifiable Information (PII)

List out entities in your input text that contain personal information eg. address, bank account number, or phone number.

Using AWS Console:

Using AWS API:

Vision

Amazon Rekognition

Analyze images and videos to identify objects, people, text, scenes, and activities.

Label Detection

Extract labels of objects, concepts, scenes, and actions in your images.

Facial Analysis

Detect faces and retrieve facial attributes in an image eg. facial expressions, accessories, facial features, etc.

Face Comparison

Compare faces within a set of images with multiple faces in them. Compares the largest face in the source image (reference face) with up to 100 faces detected in the target image (comparison faces), and generate a similarity score.

Other AWS AI Services

Amazon Lex: Build conversational interfaces using voice/text as input
Amazon Polly: Text to speech
Amazon Transcribe: Speech to text
Amazon Translate: To different languages

Domain 1: Data Engineering

AWS Glue

https://docs.aws.amazon.com/glue/latest/dg/what-is-glue.html

Serverless data integration service that makes it easy for analytics users to discover, prepare, move, and integrate data from multiple sources.
Data Sources: S3, RDS, JDBC, DynamoDB, Kinesis Data Streams, Apache Kafka
Data Targets: S3, RDS, JDBC
Crawlers: Automatically infer database and table schema from your source data, storing the associated metadata in the AWS Glue Data Catalog.
ETL Programming Languages: PySpark (Python), Scala
FindMatches Transform: Use this machine learning transformation step to identify duplicate or matching records. Eg. matching customers/products/improve fraud detection, etc.

Amazon Athena

https://docs.aws.amazon.com/athena/latest/ug/what-is.html

Serverless, interactive query service to query data and analyze big data in Amazon S3 using standard SQL.
Integration with AWS Glue: AWS Glue crawlers automatically infer database and table schema from data in S3 and store the associated metadata in AWS Glue Data Catalog. This catalog lets the Athena query engine know how to find, read, and process the data you want to query.
When to use Amazon Athena vs Redshift vs EMR: https://docs.aws.amazon.com/athena/latest/ug/when-should-i-use-ate.html

Amazon Kinesis

https://docs.aws.amazon.com/kinesis/index.html

Kinesis Video Stream

Stream live video data, optionally store it, and make the data available for consumption both in real time and on a batch or ad hoc basis.

Kinesis Data Stream

Collect and process large streams of data records in real time.

Reading from Data Streams (Consumers): Using Kinesis Data Analytics, Kinesis Data Firehose, Lambda, EC2

Kinesis Data Firehose

ETL service that captures, transforms, and delivers streaming data to data lakes, data stores, and analytics services. Buffers incoming streaming data to a certain size or for a certain period of time before delivering it to destinations.

Use Lambda to do data transformation for each buffered batch/convert file format. Eg. Apache Parquet more efficient to query than JSON format.
Delivery Stream Destination: S3, Redshift, Elasticsearch, Splunk, HTTP endpoint, etc

Kinesis Data Analytics

Continuously read and analyze data from a connected streaming source in real-time.

Source: Kinesis Data Stream, Kinesis Data Firehose
Destination: 1/ Kinesis Data Stream, 2/ Kinesis Data Firehose, 3/ Lambda
Runtime: SQL, Apache Flink
Aggregate/Analytical Functions: Hotspots, Random Cut Forest, etc

Domain 2: Exploratory Data Analysis

Data Labelling: AWS Ground Truth (Data labeling service using human annotators from Amazon Mechanical Turk or your own private workforce)
Feature Engineering: 1 hot encoding, binning, outliers, normalization, PCA dimension reduction. For text: TF-IDF, Bag of Words, N-Gram.
Know the different types of data visualization: Histogram, scatter plot, box plot, correlation heatmap, hierarchical plot, etc.

Domain 3: Modelling

https://docs.aws.amazon.com/sagemaker/latest/dg/algos.html

Supervised Learning Algos: XGBoost, k-NN, Linear Learner, DeepAR Forecasting, Object2Vec,
Unsupervised Learning Algos: K-Means, PCA, Random Cut Forest
Text Analysis Algos: BlazingText, Sequence-to-Sequence, LDA, Neural Topic Model (NTM)
Image Processing Algos: MXNet, TensorFlow, Object Detection, Semantic Segmentation (pixel level)
Evaluation of ML Models: Confusion Matrix, AUC-ROC, Accuracy, Precision, Recall, F1 Score, RMSE
Overfitting Solutions: 1/ Use fewer features, 2/ Decrease n-grams size, 3/ Increase amount of regularization used, 4/ Increase amount of training data examples
Underfitting Solutions: 1/ Add new domain-specific features, 2/ Add more Cartesian products, 3/ Increase n-grams size, 4/ Decrease amount of regularization used, 5/ Increase amount of training data examples
Hyperparameter Tuning: Random Search, Bayesian Search
How SageMaker Studio works: https://aws.amazon.com/blogs/machine-learning/dive-deep-into-amazon-sagemaker-studio-notebook-architecture/
SageMaker Studio Notebooks vs SageMaker Notebook Instances: https://docs.aws.amazon.com/sagemaker/latest/dg/notebooks-comparison.html

Domain 4: Machine Learning Implementation & Operations

Real-time Inference: Create a HTTPS endpoint if you require a persistent endpoint for apps to call to get inferences
Batch Transform: Preprocess datasets, run inferences from large datasets, does not require a persistent endpoint.
SageMaker Neo: Automatically optimizes machine learning models for inference on cloud instances and edge devices to run faster with no loss in accuracy.
SageMaker Elastic Inference (EI): Speed up the throughput and decrease the latency of getting real-time inferences from your deep learning models that are deployed as SageMaker hosted models, but at a fraction of the cost of using a GPU instance for your endpoint
Track and monitor SageMaker metrics using: 1/ AWS Console, 2/ CloudWatch, 3/ SageMaker Python SDK APIs

This is only a brief summary of the core topics I found to be important and definitely not exhaustive. Please refer to https://aws.amazon.com/certification/certified-machine-learning-specialty/ for the full set of topics to prepare.

Intro to AWS AI Services: Textract, Comprehend, Rekognition

Lester Sim — Sun, 25 Sep 2022 16:03:13 +0000

Sharing these demos and compiled docs of AWS AI Services to help you visualize how these services work. These pre-built models will be beneficial for developers with no prior experience to machine learning as the models have been pre-trained. You will only need to call the API with the inputs to get the predicted results.

Text & Documents

Amazon Textract

Extract text from scanned documents using Optical Character Recognition (OCR).

Documents

Returns text, forms, tables and query responses.

Expenses

Extracts data from invoices/receipts eg. vendor name, invoice/receipt date, invoice/receipt number, item name, item price, item quantity, total amount

Amazon Comprehend

Extract entities, key phrases, language, personal identifiable information (PII), and sentiments from text.

Entities

Extract entities from text documents eg. people, places, locations.

Using AWS Console:

Using AWS API:

Key Phrases

Extract the key phrases (one or more words) from text documents.

Using AWS Console:

Using AWS API:

Sentiment

Predict the overall sentiment of the text - positive, negative, neutral, mixed.

Using AWS Console:

Using AWS API:

Language

Predict the dominant language of the entire text. Amazon Comprehend can recognize 100 languages.

Using AWS Console:

Using AWS API:

Personally Identifiable Information (PII)

List out entities in your input text that contain personal information eg. address, bank account number, or phone number.

Using AWS Console:

Using AWS API:

Vision

Amazon Rekognition

Analyze images and videos to identify objects, people, text, scenes, and activities.

Label Detection

Extract labels of objects, concepts, scenes, and actions in your images.

Facial Analysis

Detect faces and retrieve facial attributes in an image eg. facial expressions, accessories, facial features, etc.

Face Comparison

Sources:

For other AWS AI services, refer to: https://aws.amazon.com/machine-learning/ai-services/
https://docs.aws.amazon.com/rekognition/latest/dg/what-is.html
https://docs.aws.amazon.com/comprehend/latest/dg/what-is.html

Disclaimer: The opinions expressed here are my own and I'm not writing on behalf of AWS or Amazon.

Deploy a Django Web App to Azure with connection to Azure PostgreSQL

Lester Sim — Tue, 22 Feb 2022 02:54:37 +0000

In this tutorial, we’ll deploy a ready-made Django web app to Azure Web Service and connect the back-end to Azure PostgreSQL for the app to write data to the database.

End Goal

The sample Django repo is a polls web application where a user selects a question and selects one of the options as their answers. The app will record the votes in the PostgreSQL database and display them to the user. A demo of the end state is as shown:

Prerequisites

An Azure subscription is required. If you don't have an Azure subscription, create a free Azure account before you begin.
Basic knowledge of Django, PostgreSQL, Git commands

Agenda

Fork and clone the sample repo of Django Web App
Provision PostgreSQL Flexible Server in Azure
Connect to PostgreSQL Flexible Server and create a database
Provision the web app using Azure App Services
Connect the app to Azure PostgreSQL
Deploy app code from Github to Azure App Services
Run Django database migrations to establish schema on Azure PostgreSQL database
Test the app by creating a poll question
Make changes to the app codes and redeploy

Fork and Clone Sample Repo

Navigate to https://github.com/Azure-Samples/djangoapp and fork the repository into your own GitHub account.

Provision PostgreSQL Flexible Server in Azure

Open the Azure portal.
Search for and select Azure Database for PostgreSQL
Select Create > Flexible Server > Create.

On the Flexible Server page, enter the following information:

Field	Value
Subscription	Select your desired Azure Subscription
Resource Group	Create a new resource group eg. DjangoPostgres-Tutorial-rg
Server Name	A name for the database server that's unique across all Azure
Region	Select a location near you
Workload Type	Development
Compute + Storage	Leave as default. Burstable, B1ms
Availability Zone, High Availability, PostgreSQL version	Leave as default
Admin Username, Password	Enter credentials for an administrator account on the database server. Record these credentials as you'll need them later in this tutorial. Note: do not use the $ character in the username or password as it will conflict with web app environmental variables later on

Select Next: Networking >, and on that page set Connectivity method to Public access, and then under Firewall rules check the box for Allow public access from any Azure service within Azure to this server.
Select Review + Create, then Create. Azure takes a few minutes to provision the database server.
After provisioning is complete, select Go to resource to open the overview page for the database server.

Connect to Azure Postgres and Create a Database

Open the Azure Cloud Shell from the Azure portal by selecting the Cloud Shell icon at the top of the window.

In the Cloud Shell, run the following command:



psql --host=[server-url].postgres.database.azure.com --port=5432 --username=[user-name] --dbname=postgres

Replace [server-name] and [user-name] with the names used in the previous section.

When the shell connects successfully, you should see the prompt postgres=>. This prompt indicates that you're connected to the default administrative database named "postgres". (The "postgres" database isn't intended for app usage.)

Run the command below in postgres to create a database called pollsdb that your app will connect to later:



CREATE DATABASE pollsdb;

If the database is created successfully, the command should display CREATE DATABASE. To verify that the database was created, run:



\c pollsdb

Exit postgres by entering the command exit

Provision the Web App Infrastructure in Azure

Open Azure Portal > Search for App Services > Create

Enter the following information:

Field	Value
Subscription	Select the subscription you want to use if different from the default.
Resource group	Select the "DjangoPostgres-Tutorial-rg" group you created in the previous section.
App name	A name for your web app that's unique across all Azure.
Publish	Select Code.
Runtime stack	Select Python 3.8 from the drop-down list.
Region	Select a location near you.
Linux Plan	The portal will populate this field with an App Service Plan name based on your resource group. If you want to change the name, select Create new.
Sku and size	Select Change size > Dev/Test > Select B1 > Apply. You can scale the plan later for better performance.

Select Review + Create, then select Create. Azure takes a few minutes to provision the infrastructure for the web app. Once done, select Go To Resource.

Connect Azure App Service to Azure Postgres

Configure the environment variables on Azure App Service to connect to Azure Postgres. On the App Service page, scroll down and select Configuration > Application Settings.

Click New Application Setting with the following:

Setting name	Value
DBHOST	The URL of the database server from the previous section; that is, the [server-name].postgres.database.azure.com.
DBNAME	pollsdb
DBUSER	The administrator user name used when you provisioned the database.
DBPASS	The administrator password you created earlier.

Save your settings.

Deploy App to Azure App Service from Github Repo

With the database and connection settings in place, you can now configure the Azure App to deploy code directly from a GitHub repository.

On the same Azure App Service > Scroll down to Deployment section > Select Deployment Center.

In the Source control, select GitHub. Follow the sign-in prompts to use your current GitHub login.

In the GitHub section, select the following values:

Field	Value
Organization	The GitHub account to which you forked the sample repository.
Repository	djangoapp
Branch	Select flexible-server branch
Workflow Option	Add a workflow

Select Save to trigger the build and deployment workflow. Go to Actions in your GitHub repository for djangoapp to monitor progress. Azure will deploy the code and start the app.

Move to the next steps once the build is complete.

Run Django Database Migrations

What remains is to establish the necessary schema in the database itself. Do this by "migrating" the data models in the Django app to the database.

On the same App Service page, scroll down to Development Tools on the left handle > select SSH > Enter the lscommand to show the app's files. Since the app server is hosted on Azure, you are now connected to the server via SSSH.

In the console, run database migrations:



python manage.py migrate

Create a administrator login for the app.



python manage.py createsuperuser

The createsuperuser command prompts you for Django superuser (or admin) credentials, which are used within the web app. For the purposes of this tutorial, use the default username root, press Enter for the email address to leave it blank, and enter password321 for the password.

Create a poll question in the App

You're now ready to run a quick test of the app to demonstrate that it is working with the PostgreSQL database.

In the browser window or tab for the web app, return to the Overview page, then click on the URL for the web app.

The app should display the message "Polls app" and "No polls are available" because there are no specific polls yet in the database.

Under Polls, select Add next to Questions and create a poll question with some choices.

Browse back to the home page to confirm that the questions are now presented to the user. Test out the app, vote for different options, and you will see the database is being updated.

Update the App codes and Redeploy automatically

Azure automatically redeploys your app code whenever you commit changes to the GitHub repository. However, if you change Django app's data models, you must migrate those changes to the database:

Connect to the web app again via SSH as previously
Run the migrations again with python manage.py migrate

Resources

Full Tutorial

About the Sample Django Web App

The sample web app is created by following Django documentation tutorial here.

Create a PostgreSQL Database on Azure in 5mins

Lester Sim — Tue, 19 Oct 2021 13:46:04 +0000

About Azure Database for PostgreSQL

Azure Database for PostgreSQL is a fully managed Database as a Service (DBaaS) offering. As compared to running it on-premises, Azure automatically handles all the fundamental database management tasks so you can focus on developing your applications instead of spending time managing databases. Here's a quick summary of what DBaaS refers to:

Prerequisites

An Azure subscription is required. If you don't have an Azure subscription, create a free Azure account before you begin.
Basic knowledge of pgAdmin/psql commands

Agenda

Creating an Azure Database for PostgreSQL (Flexible Server) using Azure Portal
Configuring the Firewall
Connecting/Querying the Database using pgAdmin/psql

Create an Azure Database for PostgreSQL

Go to the Azure Portal to create an Azure Database for PostgreSQL Single Server database. Search for and select Azure Database for PostgreSQL servers.
Click on Create > select Flexible Server.

Enter the Basics form with the following information:

Setting	Description
Subscription	Select your desired Azure Subscription
Resource Group	Create a new resource group eg. postgres-tutorial
Server Name	Create a globally unique name eg. postgres-tutorial-server-22102021
Region	Select a location closest to you
Workload Type	Development
Compute + Storage	Use the default: Burstable, 1 vCore, 2GiB RAM, 32 GiB Storage
Availability Zone	No preference
PostgreSQL Version	13
High Availability	Leave as unchecked
Admin username	Set your admin username eg. postgresadmin
Password	Set your password

Click Next: Networking
By default, the server that you create is not publicly accessible. If you are querying from your computer, you'll need to allow access from your IP address. In Connectivity Method > select Public Access (allowed IP addresses)
Scroll down to Firewall Rules > select Add current client IP address
Select Review + Create > Create to provision the server. This operation might take a few minutes.
And you're done! PostgreSQL database is successfully created, click Go to Resource to view the database created.

Connecting to the Database with pgAdmin

2 common methods to connect to a PostgreSQL database is using either pgAdmin or psql command line.

Click Overview in Azure Portal to copy the server name and username for our connection later on.
Open pgAdmin > right click on Server > Create Server
Create a name for this connection eg. azure-postgres-tutorial
Click the Connection tab and enter the hostname and username copied from the Overview page on Azure portal. In my example, it will be the following:

Settings Value

Host Name postgres-tutorial-server-22102021.postgres.database.azure.com

Port 5432

Maintenance Database postgres

Username postgresadmin

Password Your password
Click Save. Once connected successfully, expand the server you have just connected to and you’ll see 3 databases already created by Azure. Do not delete these databases.
Perfect! Now you are connected to the PostgreSQL database running on Azure and ready to start adding data into your database!
Right click on Database > Create > Database. Enter any name as your new database name eg. mypgsqldb.
Click on mypgsqldb and the Query Tool icon at the top window.
Copy and paste the commands below into the Query Editor and select Execute Query.

Settings	Value
Host Name	postgres-tutorial-server-22102021.postgres.database.azure.com
Port	5432
Maintenance Database	postgres
Username	postgresadmin
Password	Your password

CREATE TABLE inventory (
    id serial PRIMARY KEY, 
    name VARCHAR(50), 
    quantity INTEGER
);

INSERT INTO inventory (id, name, quantity) VALUES (1, 'banana', 150); 
INSERT INTO inventory (id, name, quantity) VALUES (2, 'orange', 154);

SELECT * FROM inventory;

The results from the Inventory table will be shown as below:

Connecting to the Database with psql

Alternatively you can use psql to connect/query the database.

Open Terminal/Powershell
Run the following command to connect to the default database postgres. Replace hostname and username with what your actual server name and admin username set in previous steps. This can also be found on the Overview tab.

In my example, the code will be:

psql --host=postgres-tutorial-server-22102021.postgres.database.azure.com --port=5432 --username=postgresadmin --dbname=postgres

Enter the database password created previously when prompted. Now, you're connected to the database with the following screen displayed:

psql --host=postgres-tutorial-server-22102021.postgres.database.azure.com --port=5432 --username=postgresadmin --dbname=postgres
Password for user postgresadmin: 
psql (13.1, server 13.4)
SSL connection (protocol: TLSv1.3, cipher: xxxxxxxx, bits: 256, compression: off)
Type "help" for help.

postgres=>

Create a database called mypgsqldb2

CREATE DATABASE mypgsqldb2

Switch the connection to connect to this newly created database instead of the default postgres database that we initially connected to.

\c mypgsqldb2

Create a table in this database

CREATE TABLE inventory (
    id serial PRIMARY KEY, 
    name VARCHAR(50), 
    quantity INTEGER
);

Insert some data into the Inventory table

INSERT INTO inventory (id, name, quantity) VALUES (1, 'banana', 150); 
INSERT INTO inventory (id, name, quantity) VALUES (2, 'orange', 154);

Query the results from the Inventory table

SELECT * FROM inventory;

Wrapping Up

And you're done! You've successfully created a PostgreSQL database on Azure and connected to it using pgAdmin/psql!

What's Next

Use Azure CLI to create PostgreSQL databases
Explore the different Compute + Storage options
Create a Flask App connected to Azure PostgreSQL
Deploy a Flask App to Azure App Service with Azure PostgreSQL

Cleaning Up

If you don't expect to use this database in the future, search for Resource Groups at the top search bar of Azure Portal. Select the resource group name (eg. azure-postgres-tutorial) > Delete Resource Group

Resources

https://docs.microsoft.com/en-us/azure/postgresql/flexible-server/quickstart-create-server-portal
https://docs.microsoft.com/en-us/azure/postgresql/tutorial-design-database-using-azure-portal