Type and hit enter to Search
×

PostgreSQL Database Administration

  • 5(625 Rating)

Course Overview

The PostgreSQL Database Administration course is an extensive training program crafted to empower participants with the expertise needed to effectively manage and oversee PostgreSQL databases. Covering a broad spectrum from foundational PostgreSQL Server concepts to advanced functionalities, including client-server architecture, installation, configuration, and security protocols, this course leaves no stone unturned. Participants will delve into diverse aspects of database administration, including server management, metadata handling, transaction management, concurrency control, and performance optimization. Moreover, they will gain insights into table partitioning, extension utilization, and the implementation of robust security protocols to safeguard data integrity. Furthermore, the course delivers hands-on instruction on monitoring, diagnostics, routine maintenance, backup and recovery procedures, data manipulation, and replication techniques. Emphasizing real-world applications, it incorporates best practices for database upgrades and migrations, such as transitioning from Oracle to PostgreSQL using tools like Ora2PG. Upon completion, learners will emerge equipped with both theoretical knowledge and practical experience, ready to excel as proficient PostgreSQL Database Administrators, ensuring the reliability, integrity, and high performance of database systems.

Target Audiance

  • Database Administrators (DBAs)
  • System Administrators managing database servers
  • IT Professionals looking to transition into database roles
  • Data Analysts requiring knowledge of database administration for complex querying
  • Developers who need to understand the backend database management
  • DevOps Engineers involved in CI/CD processes requiring database integration
  • Technical Project Managers overseeing database projects
  • Software Architects designing systems with PostgreSQL databases
  • Data Scientists needing to manage and query large datasets
  • Business Intelligence Professionals seeking to understand the database layer
  • Database Migration Specialists

Schedule Dates

PostgreSQL Database Administration
22 July 2024 - 26 July 2024
PostgreSQL Database Administration
28 October 2024 - 01 November 2024
PostgreSQL Database Administration
03 February 2025 - 07 February 2025
PostgreSQL Database Administration
05 May 2025 - 09 May 2025

Course Content


    Warning: foreach() argument must be of type array|object, bool given in /home/u612197332/domains/counseltrain.com/public_html/wp-content/themes/counseltrain/single-courses.php on line 304


    Warning: foreach() argument must be of type array|object, bool given in /home/u612197332/domains/counseltrain.com/public_html/wp-content/themes/counseltrain/single-courses.php on line 304

  • Installing the PostgreSQL Server
  • Setting Environment Variables
  • Creating a Cluster
  • Running Server

  • Configuring of PostgreSQL Server
  • Connection Settings
  • Security and Authentication
  • Resource Settings
  • WAL
  • Error Reporting and Logging
  • Autovacuum
  • Runtime Statistics, etc.

  • Postgres hosed based access configuration
  • Multiple server installation on one machine
  • Remote connection establishment

  • Introduction To PSQL: Commands and Parameters
  • • Using graphical administration tools- pgAdmin 4: 1. Installation 2. Binary path setting 3. Remote server connection

  • Object Hierarchy
  • Databases and Schemas
  • Tablespaces
  • • Exploring Databases: 1. Locating the database server's message log 2. Locating the database's system identifier 3. Listing databases on this database server 4. How much disk space does a table use? 5. Which are my biggest tables?


    Warning: foreach() argument must be of type array|object, bool given in /home/u612197332/domains/counseltrain.com/public_html/wp-content/themes/counseltrain/single-courses.php on line 304


    Warning: foreach() argument must be of type array|object, bool given in /home/u612197332/domains/counseltrain.com/public_html/wp-content/themes/counseltrain/single-courses.php on line 304

  • Performing actions on many tables
  • Writing a script
  • Adding/removing schemas
  • Moving objects between schemas
  • Adding/removing tablespaces
  • Moving objects between tablespaces
  • Using materialized views

  • Range Partitioning
  • List Partitioning

  • Accessing objects in other PostgreSQL databases (postgres_fdw, dblink)
  • File_fdw, hstore, citext,etc.

  • User Management
  • Superuser
  • Roles and Users
  • Groups and Access Control
  • Ownership, Etc.
  • Preventing Connections
  • Checking secure password
  • Auditing Changes
  • Encrypting Sensitive data

  • Real-time viewing using pgAdmin
  • Checking whether a user is connected
  • Checking which queries are running
  • Checking which queries are active or blocked
  • Knowing who is blocking a query
  • Killing a specific session
  • Knowing when a table was last used
  • Usage of disk space by temporary data
  • Understanding why queries slow down
  • Producing a daily summary of log file errors
  • Analyzing the real-time performance of your queries

  • Find and Tune Slow Running Queries
  • Collecting regular statistics from pg_stat* views
  • Finding out what makes SQL slow
  • Speeding up queries without rewriting them
  • Discovering why a query is not using an index
  • Forcing a query to use an index

  • Controlling automatic database maintenance
  • Removing issues that cause bloat
  • Identifying and fixing bloated tables and indexes
  • Monitoring and tuning vacuum
  • Updating Table Statistics
  • Vacuuming • Re-indexing

  • Planning backups
  • Backup Types
  • Logical: 1. Pg_dump 2. Pg_dumpall
  • • Physical: 1. Standalone hot physical database backup 2. Hot physical backup and continuous archiving 3. PgBaseBackup
  • • Restore: 1. Pg_restore 2. Recovery to a point in time 3. Restore Physical Backup 4. Recovery of a dropped/damaged table 5. Recovery of a dropped/damaged database

  • Exporting/Importing Data To/From A Flat File

  • • Replication: 1. Replication best practices 2. Streaming Replication 3. Implement Hot Standby 4. Replication Slots 5. Logical Replication o repmgr
  • Handling Switchover & Failover
  • • Upgrading Best Practices: 1. Upgrading - minor releases 2. Upgrading - major release(pg_upgrade)
  • Migration from Oracle to Postgres using Ora2PG (introduction)

FAQs

PostgreSQL Database Administration involves managing and maintaining PostgreSQL databases, ensuring they operate efficiently, securely, and reliably. This course provides comprehensive training on these tasks.

This course is designed for database administrators, IT professionals, developers, and anyone involved in managing PostgreSQL databases or interested in advancing their skills in database administration.

The course covers a wide range of topics including PostgreSQL Server basics, advanced features, client-server architecture, installation, configuration, security, server control, metadata management, transactions, concurrency control, performance tuning, table partitioning, extensions, security measures, monitoring, diagnostics, maintenance, backup and recovery, data movement, replication, and database migration.

While there are no strict prerequisites, a basic understanding of databases and SQL would be beneficial for participants. Familiarity with PostgreSQL or other database management systems is helpful but not required.

The course may be delivered through a combination of instructor-led training sessions, hands-on exercises, case studies, and interactive discussions. Depending on the provider, it may be offered in person, virtually, or through self-paced online modules.

Completing this course equips participants with the skills and knowledge needed to effectively manage PostgreSQL databases. It enhances career prospects, improves job performance, and enables individuals to contribute more effectively to their organization’s database management efforts.

Start learning with 15.8k students around the world.
  • 3.3k
    Courses
  • 100+
    Certified Instructors
  • 99.9%
    Success Rate
Open chat
Hello
How Can We Help You?