5Course 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.
Learning Options
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
PostgreSQL Database Administration
PostgreSQL Database Administration
PostgreSQL Database Administration
Course Content
- 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?
- 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.
Duration
Language
Batch Options
Upcoming Batch
Soft copy of Courseware
Certificate of Course Complition
Experienced and Professional Trainer
Lab and Exercises During Training 
Sample Questions for Exam Prepration
Mock Test on the Last Day of Training 
Exam Registration Guidance 
5-Star Training Environmment 