PostgreSQL Skills Development on Cloud: A Practical Guide to Database Management with AWS and Azure

Contents
About the Author
About the Technical Reviewer
Acknowledgments
Preface
	The Objective of This Book
	Target Readers for This Book
	Benefits of Reading the Book
	How Is This Book Different?
	Chapter Overview
	Chapter Dependency
	Study Objective
	Disclaimer
Chapter 1: Introduction to PostgreSQL Database Management
	1.1 Abstract
	1.2 Objective of Learning
	1.3 Introduction
		1.3.1 PostgreSQL Cluster Origin
		1.3.2 PostgreSQL Server Operating System Resources
		1.3.3 Major Features of PostgreSQL
		1.3.4 Major Contributors to PostgreSQL Open-Source Software
		1.3.5 Introduction to Database Management System
			First Normal Form
			Second Normal Form
			Third Normal Form
		1.3.6 Relational Data Model
		1.3.7 Star Schema
			Star Schema Model
		1.3.8 Object Relational Model
		1.3.9 Install PostgreSQL Cluster on MAC
			Install brew on a Mac
			Install PostgreSQL Software
			Connect to a PostgreSQL Database
			DDL and DML Scripts
			Create a Database
			Deploy Scripts
		1.3.10 PostgreSQL Cluster Install on a Windows PC
			Download Software
			Execute Binaries
			Install PostgreSQL Software
			Set PATH in Windows
		1.3.11 PostgreSQL Commercial Products
			The Major PostgreSQL Commercial Product Vendors
		1.3.12 Version Changes
	1.4 PostgreSQL Learning Approach
		1.4.1 Keywords
		1.4.2 Summary of Learning
	1.5 Practice Chapter 1
		1.5.1 Questions Chapter 1
	References
Chapter 2: Capacity Planning
	2.1 Abstract
	2.2 Objective of Learning
	2.3 Introduction
		2.3.1 Cloud Services
		2.3.2 AWS Web Services
			Set Up an Amazon Web Service Account
			Capacity Estimation for AWS Configurations
		2.3.3 Microsoft Azure Web Service
			Azure Cloud Shell
			Azure Commands for Virtual Machine Metadata
		2.3.4 Operating System Selection
		2.3.5 AWS Hardware Configuration
		2.3.6 Azure Hardware Configuration
		2.3.7 Disk Space Estimation
			Disk Space Optimization
		2.3.8 Data Management Policies
			Data Retention Policies
			Read-Only Historical Data
			Data Encryption Policies
			Data Masking
		2.3.9 PostgreSQL Cluster Provisioning Methods
			Checklist for Provisioning and Configuration
		2.3.10 Database Limits
		2.3.11 Cloud Storage
			Amazon S3
			File Copy to S3
			AWS S3 Console
			Azure Data Blob
			Load Data to Azure Programmatically
	2.4 Keywords
		2.4.1 Practice Chapter 2
		2.4.2 Questions Chapter 2
		2.4.3 Summary of Learning
	References
Chapter 3: Amazon EC2/Azure VM Linux Install Tasks
	3.1 Abstract
	3.2 Objectives of Learning
	3.3 Introduction
	3.4 Introduction to the Linux Operating System
		3.4.1 Major Features of Linux
			Introduction to Linux Shell
			Shell Programming
			If Statement
			for Loop
			while Loop
			The case Statement
		3.4.2 Cloud VM Creation
			EC2/Azure VM Installation Methods
			AWS Instance Creation
				Launch EC2 Instances from the AWS Console
				Choose the Red Hat Option
				Choose an Instance Type
				Create a New Key Pair
				AWS Network Settings
				Choose Storage
				AWS Security Group Networking Configuration
			Create EC2 from the Command Line
			EC2 Deployment with CloudFormation YAML File
			AWS CloudFormation Deployment from JSON
		3.4.3 Install Linux Azure VM
			Connect to Azure Portal
			Create Azure VM from the Command Line
		3.4.4 Linux Tasks on Amazon ec2/Azure VM
			Connect to EC2/Azure VM
			Connect to EC2 from Cloud Shell
			Configuration of putty
			Connect to EC2 from the Linux Host
			Connect to EC2 from MAC
			Linux Environment
			Check the OS Version
			Linux Kernel
			Check OS Memory Configuration
		3.4.5 Linux Huge Pages
			Configuration of Huge Pages
			Configuration of Huge Pages in the Operating System
		3.4.6 Create a Billing Alert
			Billing Alert with AWS CLI
			Billing Alert with Azure CLI
		3.4.7 Volume Group Creation
			Logical Volume Setup Commands
			Screen Images of Logical Volume Creation
		3.4.8 AWS OS Storage Allocation Procedure
			Allocate Storage from AWS CLI
			Identify Instance Availability Zones
			Create a Volume
			Attach an Instance to a Volume
			mkfs and Mount the Volume
			fstab Entry
		3.4.9 EC2/Azure VM Management Commands
			aws cloudformation list-stacks
			aws ec2 describe-instances --query
			aws ec2 start-instances
			aws ec2 stop-instances
		3.4.10 Azure CLI
			Azure Deployment Group List
			az vm start
			az vm stop
		3.4.11 Keywords
		3.4.12 Summary of Learning
		3.4.13 Practice Chapter 3
		3.4.14 Questions Chapter 3
	References
Chapter 4: PostgreSQL Software Installation on Amazon EC2/Azure VM Linux
	4.1 Abstract
	4.2 Objectives of Learning
	4.3 Installation of PostgreSQL Software on Linux
		4.3.1 Download PostgreSQL Cluster Software
		4.3.2 PostgreSQL Software for Installation on Linux
		4.3.3 PostgreSQL Server Software Installation
		4.3.4 Download Direct RPM Packages
		4.3.5 PostgreSQL RPM Packages on Azure
		4.3.6 Install PostgreSQL Software on Azure VM
		4.3.7 Install PostgreSQL with the Configuration Option
			Copy Installation Software
			Extract the Software
			Execute the Configure Command
		4.3.8 PostgreSQL Cluster Install Directories and Files
	4.4 Initialize PostgreSQL Cluster
		4.4.1 Set Up the Linux Environment
		4.4.2 Check Linux OS Disk Space
		4.4.3 Initialize PostgreSQL Cluster
		4.4.4 Connect to a Postgres Database
		4.4.5 Postgres Data Dictionary
		4.4.6 Postgres Configuration Files
			Configure postgresql.conf
	4.5 Postgres Operating System Configuration
		4.5.1 Check Memory Size
		4.5.2 Check Huge Pages in the System
		4.5.3 Check Huge Page Size
		4.5.4 Check Huge Pages After Start of Cluster
	4.6 Parameter Settings
		4.6.1 Expand Shared Buffers Memory
		4.6.2 Modify Parameters in the postgresql.conf File
		4.6.3 Modify Parameters with the Alter System
		4.6.4 New Parameters Between Versions 10-16
	4.7 Create Extensions
	4.8 Postgres Operating System Process Details
		4.8.1 Process Memory Details
	4.9 Multiple Instances of PostgreSQL
	4.10 Create a Non-Default Security Group
		4.10.1 Create a Security Group From AWS
		4.10.2 Azure Security Group
	4.11 Post Installation Tasks
		4.11.1 Create a Database
		4.11.2 Validate Access from a Remote Client
		4.11.3 Validate WAL Archiving
		4.11.4 Validate Non-Default Block Size
		4.11.5 Client Connection Details
		4.11.6 Control Data
	4.12 Uninstall Postgres from a Linux Host
	4.13 Keywords
	4.14 Summary of Learning
	4.15 Practice Chapter 4
	4.16 Questions Chapter 4
	References
Chapter 5: Client Tools
	5.1 Abstract
	5.2 Objectives of Learning
	5.3 Introduction
	5.4 pgAdmin
		5.4.1 Create a New Server
		5.4.2 Provide the Host IP Address and User Connection Details
		5.4.3 Query Tool
		5.4.4 PSQL
	5.5 psql Client on Windows
		5.5.1 PostgreSQL Executable
	5.6 SQLJ Workbench
		5.6.1 Download Software
		5.6.2 Manage JDBC Drivers
		5.6.3 Statement Tab
	5.7 AWS CLI on Windows
		5.7.1 Create an IAM User Account
		5.7.2 Download AWS CLI
		5.7.3 Configure AWS CLI
		5.7.4 AWS CLI on a Mac
		5.7.5 AWS CLI Install on Linux
	5.8 Windows Git Bash
		5.8.1 Download Software
		5.8.2 Start EC2 from Windows Git Bash Shell
		5.8.3 Query S3
	5.9 ODBC
		5.9.1 Configuration of ODBC on Linux
		5.9.2 Configuration of ODBC on a Windows Client
		5.9.3 ODBC with Configuration Option
	5.10 Install PostgreSQL Client on Linux
	5.11 Azure Client
		5.11.1 Azure CLI
	5.12 Keywords
	5.13 Summary of Learning
	5.14 Practice Chapter 5
	5.15 Questions Chapter 5
Chapter 6: PostgreSQL on AWS RDS/Azure SQL Database
	6.1 Abstract
	6.2 Objective of Learning
	6.3 Introduction
	6.4 AWS RDS
		6.4.1 Database as a Service Architecture
		6.4.2 RDS PostgreSQL Creation Methods
		6.4.3 PostgreSQL RDS Creation Procedure from the Console
			Connect from the Client tool to RDS
		6.4.4 Create RDS from the Command Line
			Create RDS from the Command Line
			Create RDS from YAML Template
			Start and Stop of RDS Instances with a Script
			RDS Access From a Non-Default Port
		6.4.5 Modification to RDS
			Parameter Group
			Parameter Modification from RDS API
			Modify an AWS Parameter Value From the Console
			Options Group
		6.4.6 RDS PostgreSQL Logs View
			Log View from the AWS CLI
		6.4.7 RDS Performance Monitoring
		6.4.8 AWS Performance Insights
			Metrics Dashboard
		6.4.9 Dimensions Menu
		6.4.10 PostgreSQL Dynamic View for Metrics
		6.4.11 Performance Monitoring with the AWS CLI
			AWS Metrics
		6.4.12 AWS CLI JSON Metrics Monitoring
		6.4.13 AWS CLI API
		6.4.14 Script for Metric Data
		6.4.15 RDS Extensions
	6.5 Azure SQL Database
		6.5.1 Azure SQL Database for PostgreSQL
		6.5.2 Azure PostgreSQL Creation Steps
		6.5.3 psql Connection From a Windows Laptop
		6.5.4 Azure PostgreSQL CLI
		6.5.5 Create a PostgreSQL Single Server
		6.5.6 Create PostgreSQL Flexible Server Azure CLI
		6.5.7 Create PostgreSQL Flexible Server with a Template
			Bicep Template
			JSON Template
		6.5.8 Enable PostgreSQL Parameter for Logging
		6.5.9 Enable Azure Database Logging
		6.5.10 Create Analytics Workspace
		6.5.11 Enable Diagnostic Setting
		6.5.12 Run KQL
	6.6 Keywords
	6.7 Summary of Learning
	6.8 Practice Chapter 6
	6.9 Questions Chapter 6
Chapter 7: PostgreSQL on Docker
	7.1 Abstract
	7.2 Objective of Learning
	7.3 Introduction
	7.4 Installation
	7.5 Docker Platform
		7.5.1 Docker Info
		7.5.2 Docker pull
		7.5.3 Docker Registry
		7.5.4 Create a PostgreSQL cluster in Docker
		7.5.5 Docker Compose
		7.5.6 Docker Process
		7.5.7 psql Connection to a Docker Local Host
		7.5.8 psql Connection from a Remote Laptop
		7.5.9 Docker Utility
	7.6 Backup and Recovery of Docker
		7.6.1 Back Up Docker
		7.6.2 Recover Docker
		7.6.3 Docker Restore on a Local Host
	7.7 Orchestration Framework
		7.7.1 Remove Docker
		7.7.2 Kubernetes
		7.7.3 Kubernetes Cluster Infrastructure
		7.7.4 Kubernetes Application Access
		7.7.5 Kubernetes Postgres Creation YAML
	7.8 OpenShift
	7.9 Amazon EKS
	7.10 Docker Desktop
		7.10.1 Docker Export from a Mac and Import to Linux
	7.11 Summary of Learning
		7.11.1 Keywords
	7.12 Practice chapter 7
	7.13 Questions Chapter 7
	References
Chapter 8: Postgres Cluster and Database Backup
	8.1 Abstract
	8.2 Objectives of Learning
	8.3 Introduction
	8.4 pg_dump
		8.4.1 Back Up a Database
		8.4.2 Back Up a Database to S3
		8.4.3 Back Up One or Many Tables
		8.4.4 Back Up a Database for Table Restore
		8.4.5 Back Up DDL
		8.4.6 Parallel Backup Configuration
		8.4.7 Parallel Backup Script
	8.5 pg_dumpall
		8.5.1 pg_dumpall Shell Script for S3
		8.5.2 pg_dumpall Shell Script for Azure
		8.5.3 pg_dumpall Shell Script for Disk Copy
	8.6 pg_basebackup
		8.6.1 pg_basebackup Script
		8.6.2 Backup Manifest File Details
		8.6.3 Incremental Backup
	8.7 RDS Backup
		8.7.1 RDS Backup from the AWS Console
		8.7.2 RDS Backup from AWS CLI
		8.7.3 Backup from Azure Linux Host
		8.7.4 Back Up a Database from Azure DS to Azure Blob
		8.7.5 Azure Snapshot Backup
		8.7.6 Backup Tool
		8.7.7 Keywords
	8.8 Summary of Learning
	8.9 Practice Chapter 8
	8.10 Questions Chapter 8
	References
Chapter 9: PostgreSQL on Windows Server in Cloud
	9.1 Abstract
	9.2 Objective of the Chapter
	9.3 Postgres on the Windows Operating System
		9.3.1 Install Windows on AWS
		9.3.2 JSON Template File
		9.3.3 RDP Download from AWS Console
		9.3.4 AWS Details for Windows OS
		9.3.5 Download the RDP Client
		9.3.6 Windows Admin Password
		9.3.7 Download RDP from the Apple Site
		9.3.8 Connect to Windows Server
	9.4 Postgres Configuration on Windows
		9.4.1 psql Shell
		9.4.2 PostgreSQL Configuration Files
		9.4.3 Stop/Start Windows Server
		9.4.4 Initialize the Postgres Cluster
		9.4.5 Start and Stop of Postgres Cluster
		9.4.6 PowerShell Commands to Manage a Postgres Cluster
		9.4.7 Power Shell Execution
		9.4.8 Postgres Data Directory Files
		9.4.9 Registry Entry
		9.4.10 The forfiles Command
		9.4.11 Delete Large Files
		9.4.12 Identify Postgres Process
		9.4.13 Terminate Postgres Process
		9.4.14 Tail the Log File
		9.4.15 Windows PostgreSQL Service
	9.5 Keywords
	9.6 Summary of Learning
	9.7 Chapter 9 Practice
	9.8 Chapter 9 Questions
Chapter 10: PostgreSQL Post-Installation Tasks
	10.1 Abstract
	10.2 Objectives of Learning
	10.3 Introduction
	10.4 psql
		10.4.1 psql Connection from a Host
		10.4.2 psql –help
		10.4.3 psql Meta-Commands
		10.4.4 psql Command Prompts
		10.4.5 psql Background Job
	10.5 Postgres Schemas
		10.5.1 Schema Definition
		10.5.2 Create a Read-Only User
		10.5.3 Schema Search Path
		10.5.4 Set Search Path to Schema
		10.5.5 PostgreSQL Database Onboarding Process Summary
			Create Database
			Create a Schema
			Create Roles
			Create a Table
			Grants
			Create Users
			Object Grants
			Object Size
	10.6 Schema Data Move Procedure
		10.6.1 Postgres Schema Clone Procedure
		10.6.2 Table Copy to a Different Schema
	10.7 Dynamic psql
		10.7.1 Dynamic SQL script1
		10.7.2 Dynamic SQL script2
		10.7.3 Dynamic SQL script3
		10.7.4 Table Row Count Dynamic Script
		10.7.5 Dynamic SQL Variable
		10.7.6 Table Rename Script
		10.7.7 Session Termination Script
	10.8 Data Copy and Load
		10.8.1 Table Copy to OS
		10.8.2 Truncate Table
		10.8.3 Table Copy from the OS
	10.9 Large Objects
	10.10 SQL Overload Function
	10.11 Postgres System Functions
	10.12 Keywords
	10.13 Summary of Learning
	10.14 Practice Chapter 10
	10.15 Questions Chapter 10
Chapter 11: Sequences
	11.1 Abstract
	11.2 Objectives of Learning
	11.3 Introduction
	11.4 Sequence Management Procedures
		11.4.1 Serial Data Type Sequence Creation and Grants
			Grants on the Sequences
			Grants Permissions on the Sequence
			Check the User Permissions
			Validate Sequence Access
		11.4.2 Sequence with Identity column definition
		11.4.3 Manual Sequence Creation
		11.4.4 Manual Sequence Increment
		11.4.5 Data Insertion with Sequences
			Data Insertion with the Serial Data Type
			Data Insert with Identity Column
			Data Insert with a Manual Sequence
				Associate Sequence with Database Column
		11.4.6 Data Copy Procedures and Sequence Maintenance
			Override System Values
			Increment Sequence Count
		11.4.7 Data Copy without a Primary Key
		11.4.8 Sequences with an Empty Table for Data Copy
		11.4.9 Sequence Truncate Approach
		11.4.10 Sequence Cache
	11.5 Keywords
	11.6 Summary of Learning
	11.7 Practice Chapter 11
	11.8 Questions Chapter 11
	References
Chapter 12: Postgres Cluster Upgrade
	12.1 Abstract
	12.2 Objectives of Learning
	12.3 Introduction
	12.4 pg_upgrade Method
	12.5 pg_dumpall Upgrade Method
		12.5.1 pg_dumpall Procedure
		12.5.2 Backup a Database with the pg_dumpall Backup Utility
		12.5.3 Restore a database with pg_dumpall
		12.5.4 Restore a Database with psql
	12.6 Postgres Upgrade on RDS
		12.6.1 aws describe db Engine
		12.6.2 RDS Upgrade Check
		12.6.3 Upgrade Compatibility Matrix
		12.6.4 RDS Upgrade
	12.7 Upgrade an AWS Instance by AWS CLI
		12.7.1 Upgrade RDS with the Command Line
		12.7.2 RDS Upgrade Log
		12.7.3 RDS Logs View
		12.7.4 Analyze Databases
	12.8 Logical Upgrade Method
		12.8.1 Logical Replication Procedure
		12.8.2 Replication Configuration
		12.8.3 Replication Slot
		12.8.4 Postgres Log
		12.8.5 Remote Database Subscription Details
	12.9 Azure Postgres Database Upgrade
		12.9.1 Database Upgrade from Azure Command Line
		12.9.2 Upgrade Command
		12.9.3 psql Connection
	12.10 Upgrade Extensions
	12.11 Keywords
	12.12 Summary of Learning
	12.13 Practice Chapter 12
	12.14 Questions Chapter 12
	References
Chapter 13: PostgreSQL Recovery
	13.1 Abstract
	13.2 Learning Objectives
	13.3 Postgres Cluster Recovery
		13.3.1 Restore Procedure for Postgres Versions 11 and Below
		13.3.2 Point-in-Time Recovery
		13.3.3 Backup Metadata File
		13.3.4 Recovery Target Methods
		13.3.5 Recovery Parameters
		13.3.6 Recovery Until Backup Time
		13.3.7 Log Sequence Recovery
			Log Sequence Recovery Log Details
		13.3.8 Transaction ID Recovery
			Transaction ID Recovery Log
		13.3.9 Time-Based Recovery
		13.3.10 Recovery to Latest
			Database Backup Restore with Incremental Backup Files
		13.3.11 Reset Logs
		13.3.12 The pg_dumpall and recovery
			Restore an Individual Table from a Backup
			Restore a Database from a Backup
			Parallel Restore Job
		13.3.13 Remap Schema
			13.13.1 Full Database Export for Schema Data Load
			Table Export for a Schema Table Data Load
			Shell Script for a Schema Data Load
		13.3.14 Restore from an RDS Backup from the Console
			13.14.1 Identify Restore Time
		13.3.15 Restore from an Azure SQL Database
			Restore to a Point in Time
	13.4 Keywords
		13.4.1 Summary of Learning
		13.4.2 Questions Chapter 13
		13.4.3 Practice
	References
Chapter 14: PostgreSQL High Availability
	14.1 Abstract
	14.2 Learning Objectives
	14.3 Introduction
	14.4 Set Up Replication Configuration
		14.4.1 Primary pg_hba.conf
		14.4.2 Standby pg_hba.conf
		14.4.3 Validate Primary Site Replication Access
		14.4.4 Perform Backup From the Standby Site
		14.4.5 Set Up Replication
		14.4.6 Validate Replication
			Standby Process for Recovery
			Identifying Replication From Primary Site
		14.4.7 Replication Configuration Script
		14.4.8 Replication Status Check Script
		14.4.9 Replication Status Script Execution
		14.4.10 Replication Slot
		14.4.11 Promote Standby to Standalone Role
	14.5 RDS Read Replica
		14.5.1 Read Replica Diagram
		14.5.2 Create a Read Replica from AWS CLI
		14.5.3 Standby Recovery Log
		14.5.4 Standby Clone AWS Console View
		14.5.5 RDS Replica Clone Completed View
		14.5.6 Connect to a Replica from a Linux Host
		14.5.7 Promote a Replica to a Standalone Cluster
		14.5.8 Connect to Promoted Standby Clone
	14.6 Azure Read Replica
		14.6.1 Replication from Azure CLI
		14.6.2 Validate Read Replica
		14.6.3 Connection to Read Replica
		14.6.4 Read Replica View from the Primary Database
		14.6.5 Promote Read Replica
	14.7 Keywords
	14.8 Summary of Learning
	14.9 Practice Chapter 14
	14.10 Questions Chapter 14
	References
Chapter 15: Table Partitions
	15.1 Abstract
	15.2 Objectives of Learning
	15.3 Introduction
	15.4 Partition Table Methods
		15.4.1 Table Partition Creation Options
		15.4.2 Partition Table Structure
		15.4.3 Partition DDL Scripts
		15.4.4 PostgreSQL Extension pg_partman
			pg_partman Script
		15.4.5 Partition a Non-Partitioned Table
		15.4.6 Non-Partitioned to Partitioned Table with Sequences
		15.4.7 Partition Indexes
		15.4.8 List with Range Subpartition
		15.4.9 Range with Hash Subpartition
		15.4.10 Range with List and Hash Partition
		15.4.11 List Partition with Range and Hash Subpartition
		15.4.12 Partitions with Inheritance
	15.5 PostgreSQL Partition Copy to Cloud Storage
		15.5.1 PostgreSQL Partition Shell Script to S3 Copy
		15.5.2 Partition Catalog Metadata Views
	15.6 Keywords
	15.7 Summary of Learning
	15.8 Practice Chapter 15
	15.9 Question Chapter 15
	References
Chapter 16: Postgres Tablespaces
	16.1 Abstract
	16.2 Objectives of Learning
	16.3 Introduction
	16.4 Tablespace Creation Procedures
		16.4.1 Create an Operating System Directory
		16.4.2 Create a Tablespace
		16.4.3 Create Tables in a Tablespace
		16.4.4 Create a Table in a Tablespace with the set Command
		16.4.5 Create a Table in a Default Tablespace
		16.4.6 Load Sample Data
		16.4.7 Query the Location of a Table in a Tablespace
		16.4.8 Non-Default Tablespaces Creation Summary
		16.4.9 pg_basebackup
	16.5 Keywords
	16.6 Summary of Learning
	16.7 Practice Chapter 16
	16.8 Questions Chapter 16
	Reference
Chapter 17: MVCC
	17.1 Abstract
	17.2 Objectives of Learning
	17.3 Multi-Version Concurrency Control
		17.3.1 ANSI SQL Isolation Levels
		17.3.2 Read Anomaly
		17.3.3 Read Commit Isolation Level
		17.3.4 Repeatable Read Isolation Level
		17.3.5 Serializable Isolation Level
	17.4 MVCC and Database Blocks
	17.5 Linux and Database Concurrency
	17.6 DBA Tasks
	17.7 Serializable Isolation Configuration
	17.8 MVCC and Implicit Locks
	17.9 MVCC and Explicit Locks
	17.10 Understanding MVCC Scenarios in the Real World
	17.11 Serializable Concurrent Transaction Issue
	17.12 Keywords
	17.13 Summary of Learning
	17.14 Questions Chapter 17
	References