This two-day practical course equips experienced PostgreSQL administrators with the skills to handle high availability, replication, encryption, and performance tuning. You’ll work through realistic scenarios in live labs, developing the confidence and technical expertise to manage complex PostgreSQL environments effectively.
PostgreSQL Advanced Administration
Become an advanced PostgreSQL administrator
Across two proactive days, you’ll build advanced PostgreSQL administration skills, from configuring high-availability clusters and encrypting data to performing efficient upgrades and tuning performance. Through practical live labs, you’ll apply techniques to real-world scenarios, ensuring you leave with practical tools ready for immediate use.
Learning objectives
- Plan and run PostgreSQL upgrades
- Secure connections and user access
- Encrypt data with Pgcrypto
- Use partitioning to manage big tables
- Set up and monitor streaming replication
- Use logical replication for specific data
- Handle replication conflicts and failover
- Keep your system running smoothly
What you’ll learn
Gain advanced PostgreSQL administration skills, from planning and executing upgrades to implementing robust security, encryption, and high availability. Learn table partitioning for large datasets and master both streaming and logical replication through hands-on labs, ensuring you can optimise, protect, and scale your PostgreSQL environment effectively.
Database upgrades
You’ll study PostgreSQL versioning and how to plan smooth upgrades from versions 10 through 16. Learn when to choose pg_dump for logical backups, pg_upgrade for faster in-place upgrades, or logical replication for minimal downtime migrations. You’ll practise running each method, completing essential post-upgrade tasks such as reindexing, updating statistics, and validating configurations to ensure performance and stability.
Security and encryption
Explore PostgreSQL’s full security toolkit, including client authentication control, secure password handling, and advanced role and privilege management. You’ll implement row-level security policies to control data visibility and protect sensitive information. Using the pgcrypto module, you’ll practise encrypting specific columns, understanding key management, and weighing the performance implications. Network security will be covered in depth, including configuring SSL/TLS for encrypted TCP/IP connections and securing remote access with SSH tunnels.
Table partitioning
Master strategies for splitting large tables into smaller, more manageable physical partitions. You’ll learn declarative partitioning syntax, inheritance-based methods, and how to create, attach, and detach partitions efficiently. Query performance tuning for partitioned data will be a focus, along with exploring new partitioning features introduced in recent PostgreSQL releases.
Streaming replication
Set up and manage a hot standby server using streaming replication, ensuring it remains synchronised with your primary system. You’ll create dedicated replication roles, configure replication slots, and practise controlled failover and failback procedures. The course covers synchronous replication for mission-critical workloads, conflict resolution, monitoring replication health, and working with timelines to track changes.
What’s included
- 2 days of expert-led training
- Real PostgreSQL lab environments
- Clear walkthroughs of upgrades and replication
- Practical examples with real administration tasks
- Help from experienced PostgreSQL trainers
- Easy-to-follow course notes
- Tips you’ll use straight away
- StayAhead certificate of completion
Key facts
Ideal for
PostgreSQL administrators or technical professionals who already understand core administration and want to advance into high availability, replication, upgrades, and security.
Prerequisites
You should be confident with SQL and have practical PostgreSQL administration experience. If you’ve completed our PostgreSQL administration course, you’ll be comfortable with the course content. Familiarity with Linux/Unix environments is recommended.
Learning experience
Our experienced trainers and interactive labs create a focused, practical environment – so you can put theory into practice from day one.
FAQs
This course is designed for PostgreSQL administrators who want to build high-availability systems, secure their databases and manage upgrades with confidence, all in a practical, hands-on setting.
Will I learn how to upgrade across multiple PostgreSQL versions?
Yes. You’ll practise upgrading PostgreSQL from versions 10 through 16 using pg_dump, pg_upgrade, and logical replication. You’ll also learn to minimise downtime, check compatibility, and carry out post-upgrade performance checks.
Does the course cover real-world security scenarios?
Yes. You’ll work with authentication methods, role management, row-level security, and SSL/TLS setup. You’ll also encrypt columns with pgcrypto, secure network connections, and discuss performance trade-offs when applying encryption in production.
How much replication will we set up during the course?
You’ll configure both streaming and logical replication from scratch. This includes creating replication users, setting up publications and subscriptions, handling replication slots, monitoring replication status, and managing failover and failback.
Will I learn how to optimise performance on large tables?
Yes. You’ll explore partitioning strategies to improve query performance on large datasets, learn how to attach/detach partitions, and test performance improvements through live lab exercises.
What our customers say
"John is an exceptional teacher. I first completed the SQL course with him four years ago, followed by PL/SQL two years later, and most recently, the Advanced SQL training. Each course has significantly contributed to my career development. John’s teaching style, clarity, and support have made a lasting impact on my learning journey."
Mihai, Junior Developer – Outsourcing
"Excellent, very informative and useful."
Simon, Digital Marketing Manager - Construction / Infrastructure
Why study with StayAhead
96% customer satisfaction
We combine experience and passion to deliver training that meets the highest standards
30+ years’ experience
Industry-leading specialists delivering high-impact IT training
Experiential instructor-led training
Courses are delivered by experienced instructors using hands-on, practical live labs