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

Contents....5

About the Author....25

About the Technical Reviewer....26

Acknowledgments....27

Preface....28

The Objective of This Book....28

Target Readers for This Book....28

Benefits of Reading the Book....29

How Is This Book Different?....29

Chapter Overview....30

Chapter Dependency....32

Study Objective....32

Disclaimer....33

Chapter 1: Introduction to PostgreSQL Database Management....34

1.1 Abstract....34

1.2 Objective of Learning....35

1.3 Introduction....35

1.3.1 PostgreSQL Cluster Origin....36

1.3.2 PostgreSQL Server Operating System Resources....40

1.3.3 Major Features of PostgreSQL....42

1.3.4 Major Contributors to PostgreSQL Open-Source Software....43

1.3.5 Introduction to Database Management System....45

First Normal Form....48

Second Normal Form....49

Third Normal Form....50

1.3.6 Relational Data Model....52

1.3.7 Star Schema....54

Star Schema Model....55

1.3.8 Object Relational Model....56

1.3.9 Install PostgreSQL Cluster on MAC....59

Install brew on a Mac....59

Install PostgreSQL Software....59

Connect to a PostgreSQL Database....60

DDL and DML Scripts....61

Create a Database....61

Deploy Scripts....61

1.3.10 PostgreSQL Cluster Install on a Windows PC....63

Download Software....63

Execute Binaries....63

Install PostgreSQL Software....64

Set PATH in Windows....65

1.3.11 PostgreSQL Commercial Products....66

The Major PostgreSQL Commercial Product Vendors....66

1.3.12 Version Changes....69

1.4 PostgreSQL Learning Approach....70

1.4.1 Keywords....70

1.4.2 Summary of Learning....71

1.5 Practice Chapter 1....71

1.5.1 Questions Chapter 1....72

References....72

Chapter 2: Capacity Planning....73

2.1 Abstract....73

2.2 Objective of Learning....74

2.3 Introduction....74

2.3.1 Cloud Services....74

2.3.2 AWS Web Services....76

Set Up an Amazon Web Service Account....76

Capacity Estimation for AWS Configurations....77

2.3.3 Microsoft Azure Web Service....79

Azure Cloud Shell....80

Azure Commands for Virtual Machine Metadata....81

2.3.4 Operating System Selection....83

2.3.5 AWS Hardware Configuration....83

2.3.6 Azure Hardware Configuration....85

2.3.7 Disk Space Estimation....86

Disk Space Optimization....88

2.3.8 Data Management Policies....89

Data Retention Policies....89

Read-Only Historical Data....89

Data Encryption Policies....90

Data Masking....90

2.3.9 PostgreSQL Cluster Provisioning Methods....91

Checklist for Provisioning and Configuration....92

2.3.10 Database Limits....93

2.3.11 Cloud Storage....93

Amazon S3....94

File Copy to S3....94

AWS S3 Console....96

Azure Data Blob....96

Load Data to Azure Programmatically....99

2.4 Keywords....102

2.4.1 Practice Chapter 2....102

2.4.2 Questions Chapter 2....102

2.4.3 Summary of Learning....103

References....104

Chapter 3: Amazon EC2Azure VM Linux Install Tasks....106

3.1 Abstract....106

3.2 Objectives of Learning....106

3.3 Introduction....107

3.4 Introduction to the Linux Operating System....107

3.4.1 Major Features of Linux....108

Introduction to Linux Shell....109

Shell Programming....110

If Statement....110

for Loop....111

while Loop....112

The case Statement....113

3.4.2 Cloud VM Creation....115

EC2Azure VM Installation Methods....115

AWS Instance Creation....116

Launch EC2 Instances from the AWS Console....117

Choose the Red Hat Option....117

Choose an Instance Type....118

Create a New Key Pair....119

AWS Network Settings....119

Choose Storage....120

AWS Security Group Networking Configuration....121

Create EC2 from the Command Line....123

EC2 Deployment with CloudFormation YAML File....125

AWS CloudFormation Deployment from JSON....128

3.4.3 Install Linux Azure VM....131

Connect to Azure Portal....131

Create Azure VM from the Command Line....135

3.4.4 Linux Tasks on Amazon ec2Azure VM....136

Connect to EC2Azure VM....136

Connect to EC2 from Cloud Shell....136

Configuration of putty....137

Connect to EC2 from the Linux Host....140

Connect to EC2 from MAC....140

Linux Environment....141

Check the OS Version....141

Linux Kernel....141

Check OS Memory Configuration....141

3.4.5 Linux Huge Pages....142

Configuration of Huge Pages....142

Configuration of Huge Pages in the Operating System....142

3.4.6 Create a Billing Alert....143

Billing Alert with AWS CLI....144

Billing Alert with Azure CLI....144

3.4.7 Volume Group Creation....145

Logical Volume Setup Commands....145

Screen Images of Logical Volume Creation....146

3.4.8 AWS OS Storage Allocation Procedure....148

Allocate Storage from AWS CLI....148

Identify Instance Availability Zones....149

Create a Volume....149

Attach an Instance to a Volume....150

mkfs and Mount the Volume....150

fstab Entry....151

3.4.9 EC2Azure VM Management Commands....152

aws cloudformation list-stacks....152

aws ec2 describe-instances --query....152

aws ec2 start-instances....152

aws ec2 stop-instances....152

3.4.10 Azure CLI....153

Azure Deployment Group List....153

az vm start....153

az vm stop....153

3.4.11 Keywords....153

3.4.12 Summary of Learning....154

3.4.13 Practice Chapter 3....154

3.4.14 Questions Chapter 3....155

References....156

Chapter 4: PostgreSQL Software Installation on Amazon EC2Azure VM Linux....157

4.1 Abstract....157

4.2 Objectives of Learning....158

4.3 Installation of PostgreSQL Software on Linux....158

4.3.1 Download PostgreSQL Cluster Software....160

4.3.2 PostgreSQL Software for Installation on Linux....160

4.3.3 PostgreSQL Server Software Installation....161

4.3.4 Download Direct RPM Packages....164

4.3.5 PostgreSQL RPM Packages on Azure....165

4.3.6 Install PostgreSQL Software on Azure VM....165

4.3.7 Install PostgreSQL with the Configuration Option....166

Copy Installation Software....167

Extract the Software....167

Execute the Configure Command....168

4.3.8 PostgreSQL Cluster Install Directories and Files....169

4.4 Initialize PostgreSQL Cluster....171

4.4.1 Set Up the Linux Environment....171

4.4.2 Check Linux OS Disk Space....172

4.4.3 Initialize PostgreSQL Cluster....173

4.4.4 Connect to a Postgres Database....175

4.4.5 Postgres Data Dictionary....175

4.4.6 Postgres Configuration Files....176

Configure postgresql.conf....176

4.5 Postgres Operating System Configuration....181

4.5.1 Check Memory Size....181

4.5.2 Check Huge Pages in the System....181

4.5.3 Check Huge Page Size....182

4.5.4 Check Huge Pages After Start of Cluster....182

4.6 Parameter Settings....182

4.6.1 Expand Shared Buffers Memory....182

4.6.2 Modify Parameters in the postgresql.conf File....183

4.6.3 Modify Parameters with the Alter System....184

4.6.4 New Parameters Between Versions 10-16....184

4.7 Create Extensions....185

4.8 Postgres Operating System Process Details....189

4.8.1 Process Memory Details....190

4.9 Multiple Instances of PostgreSQL....190

4.10 Create a Non-Default Security Group....192

4.10.1 Create a Security Group From AWS....192

4.10.2 Azure Security Group....193

4.11 Post Installation Tasks....194

4.11.1 Create a Database....194

4.11.2 Validate Access from a Remote Client....195

4.11.3 Validate WAL Archiving....196

4.11.4 Validate Non-Default Block Size....197

4.11.5 Client Connection Details....197

4.11.6 Control Data....199

4.12 Uninstall Postgres from a Linux Host....199

4.13 Keywords....200

4.14 Summary of Learning....200

4.15 Practice Chapter 4....200

4.16 Questions Chapter 4....201

References....201

Chapter 5: Client Tools....202

5.1 Abstract....202

5.2 Objectives of Learning....202

5.3 Introduction....203

5.4 pgAdmin....204

5.4.1 Create a New Server....205

5.4.2 Provide the Host IP Address and User Connection Details....206

5.4.3 Query Tool....206

5.4.4 PSQL....207

5.5 psql Client on Windows....208

5.5.1 PostgreSQL Executable....208

5.6 SQLJ Workbench....209

5.6.1 Download Software....210

5.6.2 Manage JDBC Drivers....210

5.6.3 Statement Tab....212

5.7 AWS CLI on Windows....214

5.7.1 Create an IAM User Account....215

5.7.2 Download AWS CLI....215

5.7.3 Configure AWS CLI....216

5.7.4 AWS CLI on a Mac....217

5.7.5 AWS CLI Install on Linux....217

5.8 Windows Git Bash....218

5.8.1 Download Software....218

5.8.2 Start EC2 from Windows Git Bash Shell....219

5.8.3 Query S3....220

5.9 ODBC....220

5.9.1 Configuration of ODBC on Linux....221

5.9.2 Configuration of ODBC on a Windows Client....222

5.9.3 ODBC with Configuration Option....225

5.10 Install PostgreSQL Client on Linux....226

5.11 Azure Client....228

5.11.1 Azure CLI....228

5.12 Keywords....229

5.13 Summary of Learning....229

5.14 Practice Chapter 5....229

5.15 Questions Chapter 5....230

Chapter 6: PostgreSQL on AWS RDSAzure SQL Database....232

6.1 Abstract....232

6.2 Objective of Learning....233

6.3 Introduction....233

6.4 AWS RDS....234

6.4.1 Database as a Service Architecture....234

6.4.2 RDS PostgreSQL Creation Methods....236

6.4.3 PostgreSQL RDS Creation Procedure from the Console....237

Connect from the Client tool to RDS....237

6.4.4 Create RDS from the Command Line....239

Create RDS from the Command Line....240

Create RDS from YAML Template....241

Start and Stop of RDS Instances with a Script....246

RDS Access From a Non-Default Port....248

6.4.5 Modification to RDS....248

Parameter Group....249

Parameter Modification from RDS API....250

Modify an AWS Parameter Value From the Console....251

Options Group....251

6.4.6 RDS PostgreSQL Logs View....252

Log View from the AWS CLI....252

6.4.7 RDS Performance Monitoring....253

6.4.8 AWS Performance Insights....253

Metrics Dashboard....254

6.4.9 Dimensions Menu....255

6.4.10 PostgreSQL Dynamic View for Metrics....255

6.4.11 Performance Monitoring with the AWS CLI....256

AWS Metrics....257

6.4.12 AWS CLI JSON Metrics Monitoring....257

6.4.13 AWS CLI API....260

6.4.14 Script for Metric Data....260

6.4.15 RDS Extensions....261

6.5 Azure SQL Database....261

6.5.1 Azure SQL Database for PostgreSQL....262

6.5.2 Azure PostgreSQL Creation Steps....263

6.5.3 psql Connection From a Windows Laptop....264

6.5.4 Azure PostgreSQL CLI....265

6.5.5 Create a PostgreSQL Single Server....265

6.5.6 Create PostgreSQL Flexible Server Azure CLI....266

6.5.7 Create PostgreSQL Flexible Server with a Template....266

Bicep Template....267

JSON Template....268

6.5.8 Enable PostgreSQL Parameter for Logging....271

6.5.9 Enable Azure Database Logging....271

6.5.10 Create Analytics Workspace....272

6.5.11 Enable Diagnostic Setting....273

6.5.12 Run KQL....274

6.6 Keywords....274

6.7 Summary of Learning....275

6.8 Practice Chapter 6....275

6.9 Questions Chapter 6....275

Chapter 7: PostgreSQL on Docker....277

7.1 Abstract....277

7.2 Objective of Learning....277

7.3 Introduction....278

7.4 Installation....279

7.5 Docker Platform....279

7.5.1 Docker Info....280

7.5.2 Docker pull....281

7.5.3 Docker Registry....281

7.5.4 Create a PostgreSQL cluster in Docker....282

7.5.5 Docker Compose....285

7.5.6 Docker Process....288

7.5.7 psql Connection to a Docker Local Host....289

7.5.8 psql Connection from a Remote Laptop....290

7.5.9 Docker Utility....290

7.6 Backup and Recovery of Docker....291

7.6.1 Back Up Docker....291

7.6.2 Recover Docker....291

7.6.3 Docker Restore on a Local Host....292

7.7 Orchestration Framework....294

7.7.1 Remove Docker....294

7.7.2 Kubernetes....294

7.7.3 Kubernetes Cluster Infrastructure....296

7.7.4 Kubernetes Application Access....297

7.7.5 Kubernetes Postgres Creation YAML....298

7.8 OpenShift....299

7.9 Amazon EKS....300

7.10 Docker Desktop....301

7.10.1 Docker Export from a Mac and Import to Linux....304

7.11 Summary of Learning....306

7.11.1 Keywords....306

7.12 Practice chapter 7....307

7.13 Questions Chapter 7....307

References....309

Chapter 8: Postgres Cluster and Database Backup....311

8.1 Abstract....311

8.2 Objectives of Learning....311

8.3 Introduction....312

8.4 pg_dump....314

8.4.1 Back Up a Database....316

8.4.2 Back Up a Database to S3....316

8.4.3 Back Up One or Many Tables....317

8.4.4 Back Up a Database for Table Restore....317

8.4.5 Back Up DDL....318

8.4.6 Parallel Backup Configuration....318

8.4.7 Parallel Backup Script....319

8.5 pg_dumpall....322

8.5.1 pg_dumpall Shell Script for S3....323

8.5.2 pg_dumpall Shell Script for Azure....325

8.5.3 pg_dumpall Shell Script for Disk Copy....326

8.6 pg_basebackup....327

8.6.1 pg_basebackup Script....329

8.6.2 Backup Manifest File Details....329

8.6.3 Incremental Backup....330

8.7 RDS Backup....331

8.7.1 RDS Backup from the AWS Console....332

8.7.2 RDS Backup from AWS CLI....333

8.7.3 Backup from Azure Linux Host....333

8.7.4 Back Up a Database from Azure DS to Azure Blob....334

8.7.5 Azure Snapshot Backup....336

8.7.6 Backup Tool....336

8.7.7 Keywords....337

8.8 Summary of Learning....337

8.9 Practice Chapter 8....338

8.10 Questions Chapter 8....338

References....340

Chapter 9: PostgreSQL on Windows Server in Cloud....341

9.1 Abstract....341

9.2 Objective of the Chapter....342

9.3 Postgres on the Windows Operating System....342

9.3.1 Install Windows on AWS....344

9.3.2 JSON Template File....345

9.3.3 RDP Download from AWS Console....347

9.3.4 AWS Details for Windows OS....347

9.3.5 Download the RDP Client....348

9.3.6 Windows Admin Password....348

9.3.7 Download RDP from the Apple Site....350

9.3.8 Connect to Windows Server....350

9.4 Postgres Configuration on Windows....351

9.4.1 psql Shell....352

9.4.2 PostgreSQL Configuration Files....352

9.4.3 StopStart Windows Server....352

9.4.4 Initialize the Postgres Cluster....353

9.4.5 Start and Stop of Postgres Cluster....353

9.4.6 PowerShell Commands to Manage a Postgres Cluster....355

9.4.7 Power Shell Execution....357

9.4.8 Postgres Data Directory Files....357

9.4.9 Registry Entry....358

9.4.10 The forfiles Command....360

9.4.11 Delete Large Files....361

9.4.12 Identify Postgres Process....361

9.4.13 Terminate Postgres Process....362

9.4.14 Tail the Log File....362

9.4.15 Windows PostgreSQL Service....363

9.5 Keywords....364

9.6 Summary of Learning....364

9.7 Chapter 9 Practice....364

9.8 Chapter 9 Questions....365

Chapter 10: PostgreSQL Post-Installation Tasks....366

10.1 Abstract....366

10.2 Objectives of Learning....366

10.3 Introduction....367

10.4 psql....367

10.4.1 psql Connection from a Host....370

10.4.2 psql –help....370

10.4.3 psql Meta-Commands....373

10.4.4 psql Command Prompts....374

10.4.5 psql Background Job....375

10.5 Postgres Schemas....376

10.5.1 Schema Definition....377

10.5.2 Create a Read-Only User....379

10.5.3 Schema Search Path....379

10.5.4 Set Search Path to Schema....380

10.5.5 PostgreSQL Database Onboarding Process Summary....381

Create Database....381

Create a Schema....382

Create Roles....382

Create a Table....383

Grants....383

Create Users....383

Object Grants....384

Object Size....384

10.6 Schema Data Move Procedure....385

10.6.1 Postgres Schema Clone Procedure....385

10.6.2 Table Copy to a Different Schema....386

10.7 Dynamic psql....387

10.7.1 Dynamic SQL script1....387

10.7.2 Dynamic SQL script2....389

10.7.3 Dynamic SQL script3....390

10.7.4 Table Row Count Dynamic Script....392

10.7.5 Dynamic SQL Variable....393

10.7.6 Table Rename Script....395

10.7.7 Session Termination Script....396

10.8 Data Copy and Load....397

10.8.1 Table Copy to OS....397

10.8.2 Truncate Table....398

10.8.3 Table Copy from the OS....398

10.9 Large Objects....398

10.10 SQL Overload Function....399

10.11 Postgres System Functions....402

10.12 Keywords....403

10.13 Summary of Learning....403

10.14 Practice Chapter 10....403

10.15 Questions Chapter 10....404

Chapter 11: Sequences....405

11.1 Abstract....405

11.2 Objectives of Learning....405

11.3 Introduction....406

11.4 Sequence Management Procedures....406

11.4.1 Serial Data Type Sequence Creation and Grants....408

Grants on the Sequences....411

Grants Permissions on the Sequence....412

Check the User Permissions....415

Validate Sequence Access....415

11.4.2 Sequence with Identity column definition....416

11.4.3 Manual Sequence Creation....417

11.4.4 Manual Sequence Increment....418

11.4.5 Data Insertion with Sequences....420

Data Insertion with the Serial Data Type....420

Data Insert with Identity Column....421

Data Insert with a Manual Sequence....422

Associate Sequence with Database Column....423

11.4.6 Data Copy Procedures and Sequence Maintenance....424

Override System Values....427

Increment Sequence Count....428

11.4.7 Data Copy without a Primary Key....428

11.4.8 Sequences with an Empty Table for Data Copy....430

11.4.9 Sequence Truncate Approach....432

11.4.10 Sequence Cache....435

11.5 Keywords....436

11.6 Summary of Learning....436

11.7 Practice Chapter 11....436

11.8 Questions Chapter 11....437

References....438

Chapter 12: Postgres Cluster Upgrade....439

12.1 Abstract....439

12.2 Objectives of Learning....440

12.3 Introduction....440

12.4 pg_upgrade Method....441

12.5 pg_dumpall Upgrade Method....447

12.5.1 pg_dumpall Procedure....449

12.5.2 Backup a Database with the pg_dumpall Backup Utility....450

12.5.3 Restore a database with pg_dumpall....450

12.5.4 Restore a Database with psql....451

12.6 Postgres Upgrade on RDS....451

12.6.1 aws describe db Engine....452

12.6.2 RDS Upgrade Check....453

12.6.3 Upgrade Compatibility Matrix....454

12.6.4 RDS Upgrade....454

12.7 Upgrade an AWS Instance by AWS CLI....455

12.7.1 Upgrade RDS with the Command Line....456

12.7.2 RDS Upgrade Log....457

12.7.3 RDS Logs View....458

12.7.4 Analyze Databases....459

12.8 Logical Upgrade Method....459

12.8.1 Logical Replication Procedure....461

12.8.2 Replication Configuration....461

12.8.3 Replication Slot....463

12.8.4 Postgres Log....464

12.8.5 Remote Database Subscription Details....465

12.9 Azure Postgres Database Upgrade....466

12.9.1 Database Upgrade from Azure Command Line....466

12.9.2 Upgrade Command....467

12.9.3 psql Connection....468

12.10 Upgrade Extensions....469

12.11 Keywords....469

12.12 Summary of Learning....469

12.13 Practice Chapter 12....470

12.14 Questions Chapter 12....470

References....471

Chapter 13: PostgreSQL Recovery....472

13.1 Abstract....472

13.2 Learning Objectives....472

13.3 Postgres Cluster Recovery....473

13.3.1 Restore Procedure for Postgres Versions 11 and Below....474

13.3.2 Point-in-Time Recovery....475

13.3.3 Backup Metadata File....477

13.3.4 Recovery Target Methods....479

13.3.5 Recovery Parameters....482

13.3.6 Recovery Until Backup Time....483

13.3.7 Log Sequence Recovery....484

Log Sequence Recovery Log Details....485

13.3.8 Transaction ID Recovery....485

Transaction ID Recovery Log....486

13.3.9 Time-Based Recovery....486

13.3.10 Recovery to Latest....487

Database Backup Restore with Incremental Backup Files....487

13.3.11 Reset Logs....489

13.3.12 The pg_dumpall and recovery....490

Restore an Individual Table from a Backup....491

Restore a Database from a Backup....492

Parallel Restore Job....492

13.3.13 Remap Schema....494

13.13.1 Full Database Export for Schema Data Load....495

Table Export for a Schema Table Data Load....495

Shell Script for a Schema Data Load....496

13.3.14 Restore from an RDS Backup from the Console....497

13.14.1 Identify Restore Time....498

13.3.15 Restore from an Azure SQL Database....501

Restore to a Point in Time....502

13.4 Keywords....502

13.4.1 Summary of Learning....502

13.4.2 Questions Chapter 13....503

13.4.3 Practice....504

References....504

Chapter 14: PostgreSQL High Availability....506

14.1 Abstract....506

14.2 Learning Objectives....507

14.3 Introduction....507

14.4 Set Up Replication Configuration....510

14.4.1 Primary pg_hba.conf....510

14.4.2 Standby pg_hba.conf....512

14.4.3 Validate Primary Site Replication Access....512

14.4.4 Perform Backup From the Standby Site....513

14.4.5 Set Up Replication....514

14.4.6 Validate Replication....514

Standby Process for Recovery....515

Identifying Replication From Primary Site....515

14.4.7 Replication Configuration Script....515

14.4.8 Replication Status Check Script....520

14.4.9 Replication Status Script Execution....522

14.4.10 Replication Slot....523

14.4.11 Promote Standby to Standalone Role....524

14.5 RDS Read Replica....524

14.5.1 Read Replica Diagram....525

14.5.2 Create a Read Replica from AWS CLI....525

14.5.3 Standby Recovery Log....526

14.5.4 Standby Clone AWS Console View....526

14.5.5 RDS Replica Clone Completed View....526

14.5.6 Connect to a Replica from a Linux Host....527

14.5.7 Promote a Replica to a Standalone Cluster....527

14.5.8 Connect to Promoted Standby Clone....528

14.6 Azure Read Replica....528

14.6.1 Replication from Azure CLI....529

14.6.2 Validate Read Replica....530

14.6.3 Connection to Read Replica....530

14.6.4 Read Replica View from the Primary Database....531

14.6.5 Promote Read Replica....532

14.7 Keywords....533

14.8 Summary of Learning....534

14.9 Practice Chapter 14....534

14.10 Questions Chapter 14....534

References....535

Chapter 15: Table Partitions....536

15.1 Abstract....536

15.2 Objectives of Learning....537

15.3 Introduction....537

15.4 Partition Table Methods....538

15.4.1 Table Partition Creation Options....539

15.4.2 Partition Table Structure....539

15.4.3 Partition DDL Scripts....540

15.4.4 PostgreSQL Extension pg_partman....542

pg_partman Script....542

15.4.5 Partition a Non-Partitioned Table....543

15.4.6 Non-Partitioned to Partitioned Table with Sequences....545

15.4.7 Partition Indexes....546

15.4.8 List with Range Subpartition....547

15.4.9 Range with Hash Subpartition....548

15.4.10 Range with List and Hash Partition....549

15.4.11 List Partition with Range and Hash Subpartition....550

15.4.12 Partitions with Inheritance....552

15.5 PostgreSQL Partition Copy to Cloud Storage....554

15.5.1 PostgreSQL Partition Shell Script to S3 Copy....555

15.5.2 Partition Catalog Metadata Views....555

15.6 Keywords....556

15.7 Summary of Learning....557

15.8 Practice Chapter 15....557

15.9 Question Chapter 15....557

References....558

Chapter 16: Postgres Tablespaces....559

16.1 Abstract....559

16.2 Objectives of Learning....559

16.3 Introduction....560

16.4 Tablespace Creation Procedures....561

16.4.1 Create an Operating System Directory....561

16.4.2 Create a Tablespace....561

16.4.3 Create Tables in a Tablespace....561

16.4.4 Create a Table in a Tablespace with the set Command....562

16.4.5 Create a Table in a Default Tablespace....562

16.4.6 Load Sample Data....563

16.4.7 Query the Location of a Table in a Tablespace....563

16.4.8 Non-Default Tablespaces Creation Summary....564

16.4.9 pg_basebackup....567

16.5 Keywords....567

16.6 Summary of Learning....567

16.7 Practice Chapter 16....568

16.8 Questions Chapter 16....568

Reference....569

Chapter 17: MVCC....570

17.1 Abstract....570

17.2 Objectives of Learning....571

17.3 Multi-Version Concurrency Control....571

17.3.1 ANSI SQL Isolation Levels....571

17.3.2 Read Anomaly....572

17.3.3 Read Commit Isolation Level....573

17.3.4 Repeatable Read Isolation Level....574

17.3.5 Serializable Isolation Level....574

17.4 MVCC and Database Blocks....575

17.5 Linux and Database Concurrency....575

17.6 DBA Tasks....576

17.7 Serializable Isolation Configuration....579

17.8 MVCC and Implicit Locks....579

17.9 MVCC and Explicit Locks....579

17.10 Understanding MVCC Scenarios in the Real World....580

17.11 Serializable Concurrent Transaction Issue....582

17.12 Keywords....583

17.13 Summary of Learning....583

17.14 Questions Chapter 17....584

References....584

This book provides a comprehensive approach to manage PostgreSQL cluster databases on Amazon Web Services and Azure Web Services on the cloud, as well as in Docker and container environments on a Red Hat operating system. Furthermore, detailed references for managing PostgreSQL on both Windows and Mac are provided. This book condenses all the fundamental and essential concepts you need to manage a PostgreSQL cluster into a one-stop guide that is perfect for newcomers to Postgres database administration.  

Each chapter of the book provides historical context and documents version changes of the PostgreSQL cluster, elucidates practical "how-to" methods, and includes illustrations and key word definitions, practices for application, a summary of key learnings, and questions to reinforce understanding. The book also outlines a clear study objective with a weekly learning schedule and hundreds of practice exercises, along with questions and answers.

With its comprehensive and practical approach, this book will help you gain the confidence to manage all aspects of a PostgreSQL cluster in critical production environments so you can better support your organization's database infrastructure on the cloud and in containers.

What You Will Learn

• Install and configure Postgres clusters on the cloud and in containers, monitor database logs, start and stop databases, troubleshoot, tune performance, backup and recover, and integrate with Amazon S3 and Azure Data Blob
• Manage Postgres databases on Amazon Web Services and Azure Web Services on the cloud, as well as in Docker and container environments on a Red Hat operating system
• Access sample references to scripting solutions and database management tools for working with Postgres, Redshift (based on Postgres 8.2), and Docker
• Create Amazon Machine Images (AMI) and Azure Images for managing a fleet of Postgres clusters on the cloud
• Reinforce knowledge with a weekly learning schedule and hundreds of practice exercises, along with questions and answers
• Progress from simple concepts, such as how to choose the correct instance type, to creating complex machine images
• Gain access to an Amazon AMI with a DBA admin tool, allowing you to learn Postgres, Redshift, and Docker in a cloud environment
• Refer to a comprehensive summary of documentations of Postgres, Amazon Web services, Azure Web services, and Red Hat Linux for managing all aspects of Postgres cluster management on the cloud

Who This Book Is For

Newcomers to PostgreSQL database administration and cross-platform support DBAs looking to master PostgreSQL on the cloud.