PostgreSQL 10 Database Administration
Learn to install, administer, secure, optimize, back up, recover, and manage PostgreSQL databases in production environments.
Overview
This 5-day PostgreSQL 10 Database Administration training program provides comprehensive knowledge and practical skills for installing, configuring, securing, monitoring, and maintaining PostgreSQL database servers. Participants will learn PostgreSQL architecture, database management, user and role administration, transaction control, performance tuning, query optimization, and routine maintenance. The course also covers backup and recovery strategies, replication, high availability, failover management, database upgrades, and migration from Oracle to PostgreSQL. Through practical exercises, learners will develop the skills required to manage reliable, secure, and high-performing PostgreSQL database environments.
Certification
“It’s not the certificate that matters the most but preparing for the Industry”
- Participation certificate for all the attendees.
- Deserving performers will be awarded Merit certificates.
- Participants will receive the certificate with a unique code which can be verified online.
- Internship opportunity on Course Completion
Prerequisite
- Basic knowledge of relational database concepts and SQL.
- Familiarity with database tables, schemas, indexes, and transactions.
- Basic knowledge of Linux or Unix command-line operations is recommended.
- Understanding of database backup, recovery, and security concepts is helpful.
- Previous PostgreSQL experience is beneficial but not mandatory.
Eligibility
- Database Administrators (DBAs) who want to develop PostgreSQL administration skills.
- Database Developers and SQL Professionals.
- System Administrators and IT Infrastructure Professionals.
- Data Engineers and Database Support Engineers.
- Professionals working with database performance, security, backup, and recovery.
- Oracle Database Administrators interested in migrating to PostgreSQL.
- IT professionals seeking practical database administration and high-availability skills.
Topics to be covered on this course
- Day 1
- Introduction to PostgreSQL Server and Advance Features
- Postgres Client Server Architecture
- Getting Started
- 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.
- Server Control
- Postgres hosed based access configuration
- Day 2
- Client and Tools
- Introduction To PSQL
- Commands and Parameters
- Using graphical administration tools- pgAdmin
- Client and Tools
- Creating and Managing Databases
- Object Hierarchy
- Databases and Schemas
- Tablespaces
- Exploring Databases
- Locating the database server’s message log
- Locating the database’s system identifier
- Listing databases on this database server
- How much disk space does a table use?
- Which are my biggest tables?
- How many rows are there in a table?
- Quickly estimating the number of rows in a table
- Understanding object dependencies
- Obtaining Metadata
- Transactions & Concurrency Control
- Day 3
- Database Administration
- Performing actions on many tables
- Writing a script
- Adding/removing schemas
- Moving objects between schemas
- Adding/removing tablespaces s
- Moving objects between tablespaces
- Using materialized views
- Table Partitioning
- Extensions
- Accessing objects in other PostgreSQL databases (postgres_fdw, dblink)
- File_fdw, hstore, pgcrypto,etc.
- Security
- User Management
- Superuser
- Roles and Users
- Groups and Access Control
- Ownership, Etc.
- Preventing Connections
- Checking secure password
- Auditing Changes
- Encrypting Sensitive data
- User Management
- Monitoring and Diagnosis
- 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
- Performance and Concurrency
- 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
- Day 4
- Regular Maintenance
- 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
- Backup and Recovery
- Planning backups
- Backup Types
- Logical
- Pg_dump
- Pg_dumpall
- Physical
- Standalone hot physical database backup
- Hot physical backup and continuous archiving
- BarMan
- PgBaseBackup
- Logical
- Restore
- Pg_restore
- Recovery to a point in time
- Restore Physical Backup
- Recovery of a dropped/damaged table
- Recovery of a dropped/damaged database
- Moving Data
- Exporting/Importing Data To/From A Flat File
- Day 5
- Replication and Upgrades
- Replication
- Replication best practicesStreaming ReplicationImplement Hot StandbyReplication SlotsLogical ReplicationUsing repmgrMulti-masterHandling Switchover & Failover
- Replication
- Upgrading Best Practices
- Upgrading – minor releases
- Upgrading – major release(pg_upgrade)
