book

Mastering PostgreSQL Administration: Internals, Operations, Monitoring, and Oracle Migration Strategies

Name: Mastering PostgreSQL Administration: Internals, Operations, Monitoring, and Oracle Migration Strategies
ISBN: 9798868815072

by Y V Ravi Kumar, Arun Kumar Samayam, Phani Kadambari

September 2025

Intermediate to advanced

746 pages

12h 24m

English

Apress

Read now

Unlock full access

Mastering PostgreSQL Administration
Acknowledgments
Table of Contents
About the Authors
About the Technical Reviewers
1. PostgreSQL System Architecture
IntroductionPostgreSQL ComponentsPostgreSQL Server Process TreePostgreSQL Architecture FlowSummary
2. PostgreSQL Installation and Initialization
IntroductionPostgreSQL Available Versions and Supported OS PlatformsSystem Requirements for PostgreSQL Installation on LinuxEnvironment DetailsDownloading PostgreSQL SoftwareInstalling PostgreSQL Using the Binary Packages (RPMs)Exploring a few files under the data directoryShutdown Modes in PostgreSQL Database ClusterUninstallation of PostgreSQL Server (Installed Using RPMs)Installing PostgreSQL Server Using Source CodeUninstallation of PostgreSQL Server (Installed Using Source Code)Summary
3. PostgreSQL Physical Structures
IntroductionPostgreSQL Database ClusterPostgreSQL Directory StructureDatabases in PostgreSQLTablespaces in PostgreSQLDefault TablespacesCreating a New TablespaceChanging Default TablespacePage Structure and Layout in PostgreSQL Database ClusterQuery Processing in PostgreSQLConnection Request StageQuery Execution StageParser StageExecution Plan Generation StageExecution StageInspecting Physical Data with the pg_filedump UtilityOther Administrative OperationsAltering a TablespaceDropping a TablespaceTemporary TablespacesIndex Objects on Separate TablespaceAdvantages of TablespacesCloning a DatabaseDropping a Database in a ClusterDropping Default DatabasesSummary
4. PostgreSQL Management of Database Server
IntroductionCreating Database Using OS CommandSchemas in PostgreSQLCreating a SchemaAccessing Schema Objects in PostgreSQLDatabase Local Connectivity by UserUser AttributesUser RenamingSchema OwnershipDatabase Remote Connectivity by UserThe pg_hba.conf FileAuthentication Method – Trust and RejectPredefined Roles in PostgreSQLExtensions in PostgreSQLDropping Users in PostgreSQLDropping a Schema in PostgreSQLDropping Database Using OS CommandSummary
5. PostgreSQL Backup and Recovery
IntroductionWhy Backup a Database?Importance of a Backup StrategyBackup Types in PostgreSQLSQL DumpFile System-Level BackupContinuous Archiving and Point-in-Time Recovery (PITR)Using External ToolsBarmanInstalling BarmanConfiguring BarmanpgBackRestInstallation and Configuration of pgBackRest ToolReal-World Practical Backup and Recovery ScenariosScenario 1: Backup and Restore a Single TableScenario 2: Backup and Restore a Table Structure Without the DataScenario 3: Backup and Restore the Table Data Without the StructureScenario 4: Backup and Restore Multiple Tables at a TimeScenario 5: Backup and Restore a Table in Custom FormatScenario 5a: Backup and Restore a Table Using pg_dump in tar FormatScenario 5b: Backup and Restore a Table Using pg_dump in dir FormatScenario 6: Backup Multiple Tables and Restore One Table in Custom FormatScenario 7: Backup and Restore a Schema Within a DatabaseScenario 8: Backup Multiple Schemas and Restore a Single Schema Within a DatabaseScenario 9: Backup and Restore an Entire DatabaseScenario 10: Restore a Backup to a New DatabaseScenario 11: Improve Backup Performance with Parallel JobsScenario 12: Restore a Single Table from a Full Database BackupScenario 13: Logical Backup of a Complete Server and Restore Specific DatabasesScenario 14: Backup of Global Database ObjectsScenario 15: Physical Backup of the Complete ServerScenario 15a: Verification of Physical Backups (pg_verifybackup) Before RestoreScenario 16: Restore ProceduresScenario 16a: Restore to a New InstanceScenario 16b: Restore to an Existing InstanceScenario 17: Perform a Point-in-Time Recovery (PITR)Scenario 18: Backup and Restore Using BarmanScenario 19: Backup and Restore Using pgBackRestSummary

6. PostgreSQL Routine Maintenance
IntroductionAnalyzeVacuum in PostgreSQLKey Purposes of vacuumTypes of vacuumVACUUM FULL in PostgreSQLKey Points About VACUUM FULLPostgreSQL vacuum Commands – Summary TableVacuum and Analyze Threshold Settings in postgresql.confvacuumdb Commands for Various ScenariosReindexing in PostgreSQLTIP: REINDEX TABLE <table_name>Reindexing in PostgreSQL – Key OptionsPostgreSQL Catalog Views for Size MonitoringFinding the Largest Tables in the DatabaseFinding and Removing Unused Tables and IndexesClear Write-Ahead Logging (WAL) Logs to Free Up Space in Database ServerBest Practices to Prevent Disk Space Issues in PostgreSQL ClusterLog File Maintenance in PostgreSQLSummary
7. PostgreSQL Monitoring Using PgAdmin and Grafana
IntroductionMonitoring System Resources and OS-Level MetricsUnderstanding Real-Time System BehaviorPostgreSQL System Views: Real-Time Insights, No Extra SetupAdministering Databases Using pgAdminInstallation and Configuration of pgAdminInstalling pgAdmin 4 on WindowsConnectivity Between pgAdmin and pg_serverRegistering PostgreSQL Server in pgAdminIntegrating PostgreSQL with Monitoring Solutions (Grafana)Installation and Configuration of GrafanaDownloading Grafana on WindowsInstalling Grafana on WindowsConnecting to GrafanaAdding a PostgreSQL Data Source in GrafanaBuilding a Dashboard in GrafanaCreating a Bar Chart in the DashboardAdding a New Bar Chart Panel to the Existing DashboardAdding a New XY Chart Panel to the Existing DashboardAdding Multiple Series to the XY ChartMapping Series of Multi-country VisualizationSummary
8. Data Migration from Oracle to PostgreSQL Using Ora2Pg
IntroductionWhy Migrate to PostgreSQLMigration ToolsMigration Best PracticesOracle to PostgreSQL Migration Using Ora2PgMigration ApproachMigration PrerequisitesPre-migration Phase: Prepare Environment for MigrationInstall Perl Modules Required for DBD::Oracle and Ora2PgInstall Ora2PgMigration Phase: Migrate Oracle Schemas to PostgreSQL DatabaseMigrating HR Schema Using Direct Load MethodMigrating CO Schema Using COPY (Bulk Load) MethodMigrating SH Schema Using INSERT MethodMigration IssuesSummary
9. Data Replication from Oracle to PostgreSQL Using Oracle GoldenGate
IntroductionGoldenGate Replication Configuration PrerequisitesConfigure Source Database (Oracle) Ready for ReplicationConfigure Target Database (PostgreSQL) Ready for ReplicationConfigure Oracle GoldenGate HubWhat Is Oracle GoldenGate Hub?Install Oracle ClientInstall GoldenGate SoftwareInstall Oracle GoldenGate for Oracle DatabaseInstall Oracle GoldenGate for PostgreSQLCreate the DeploymentsCreate Deployment for Source Oracle DatabaseCreate Deployment for Target PostgreSQL DatabaseConfigure GoldenGate on Target DeploymentReplication Configuration – Create Dist Path in Source DeploymentSummary
10. New Features of PostgreSQL 17
IntroductionDownloading PostgreSQL 17 Software BinariesInstalling PostgreSQL Using Binary Packages (RPMs)New Features: Incremental Backups in PostgreSQL 17New Features: Logical Replication from Standby Servers in PostgreSQL 17New Features: Combining I/Os in PostgreSQL 17New Features: Query Planner Improvements in PostgreSQL 17New Features: Introducing Split and Merge Partitions in PostgreSQL 17Summary
Index

Content preview from Mastering PostgreSQL Administration: Internals, Operations, Monitoring, and Oracle Migration Strategies

Y. V. Ravi Kumar et al.Mastering PostgreSQL Administrationhttps://doi.org/10.1007/979-8-8688-1507-2_3

3. PostgreSQL Physical Structures

Y V Ravi Kumar¹, Arun Kumar Samayam² and Phani Kadambari³

(1)

Irving, TX, USA

(2)

Dallas, TX, USA

(3)

Lewisville, TX, USA

Introduction

PostgreSQL physical storage is structured into many important components that exist on the file system. The structures are important to the database’s functionality. In this chapter, let us take a detailed look at the physical storage topology and key physical components of PostgreSQL as well as database cluster and tablespace relationships.

The following topics are covered in this chapter:

PostgreSQL ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9798868815072Purchase Link Publisher Website

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Mastering PostgreSQL Administration: Internals, Operations, Monitoring, and Oracle Migration Strategies

by Y V Ravi Kumar, Arun Kumar Samayam, Phani Kadambari

3. PostgreSQL Physical Structures

Introduction

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.