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