Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying PostgreSQL 9 High Availability Cookbook
  • Table Of Contents Toc
PostgreSQL 9 High Availability Cookbook

PostgreSQL 9 High Availability Cookbook

By : Shaun Thomas
4.7 (6)
Buy this Book
close
close
PostgreSQL 9 High Availability Cookbook

PostgreSQL 9 High Availability Cookbook

4.7 (6)
By: Shaun Thomas
Buy this Book

Overview of this book

A comprehensive series of dependable recipes to design, build, and implement a PostgreSQL server architecture free of common pitfalls that can operate for years to come. Each chapter is packed with instructions and examples to simplify even highly complex database operations. If you are a PostgreSQL DBA working on Linux systems who want a database that never gives up, this book is for you. If you've ever experienced a database outage, restored from a backup, spent hours trying to repair a malfunctioning cluster, or simply want to guarantee system stability, this book is definitely for you.
Table of Contents (12 chapters)
close
close
close
Preface
Icon
Preface
Icon
What this book covers
Icon
What you need for this book
Icon
Who this book is for
Icon
Sections
Icon
Conventions
Icon
Reader feedback
Icon
Customer support
Lock Free Chapter
1
1. Hardware Planning
Icon
1. Hardware Planning
Icon
Introduction
Icon
Planning for redundancy
Icon
Having enough IOPS
Icon
Sizing storage
Icon
Investing in a RAID
Icon
Picking a processor
Icon
Making the most of memory
Icon
Exploring nimble networking
Icon
Managing motherboards
Icon
Selecting a chassis
Icon
Saddling up to a SAN
Icon
Tallying up
Icon
Protecting your eggs
2
2. Handling and Avoiding Downtime
Icon
2. Handling and Avoiding Downtime
Icon
Introduction
Icon
Determining acceptable losses
Icon
Configuration – getting it right the first time
Icon
Configuration – managing scary settings
Icon
Identifying important tables
Icon
Defusing cache poisoning
Icon
Exploring the magic of virtual IPs
Icon
Terminating rogue connections
Icon
Reducing contention with concurrent indexes
Icon
Managing system migrations
Icon
Managing software upgrades
Icon
Mitigating the impact of hardware failure
Icon
Applying bonus kernel tweaks
3
3. Pooling Resources
Icon
3. Pooling Resources
Icon
Introduction
Icon
Determining connection costs and limits
Icon
Installing PgBouncer
Icon
Configuring PgBouncer safely
Icon
Connecting to PgBouncer
Icon
Listing PgBouncer server connections
Icon
Listing PgBouncer client connections
Icon
Evaluating PgBouncer pool health
Icon
Installing pgpool
Icon
Configuring pgpool for master/slave mode
Icon
Testing a write query on pgpool
Icon
Swapping active nodes with pgpool
Icon
Combining the power of PgBouncer and pgpool
4
4. Troubleshooting
Icon
4. Troubleshooting
Icon
Introduction
Icon
Performing triage
Icon
Installing common statistics packages
Icon
Evaluating the current disk performance with iostat
Icon
Tracking I/O-heavy processes with iotop
Icon
Viewing past performance with sar
Icon
Correlating performance with dstat
Icon
Interpreting /proc/meminfo
Icon
Examining /proc/net/bonding/bond0
Icon
Checking the pg_stat_activity view
Icon
Checking the pg_stat_statements view
Icon
Debugging with strace
Icon
Logging checkpoints properly
5
5. Monitoring
Icon
5. Monitoring
Icon
Introduction
Icon
Figuring out what to monitor
Icon
Installing and configuring Nagios
Icon
Configuring Nagios to monitor a database host
Icon
Enhancing Nagios with check_mk
Icon
Getting to know check_postgres
Icon
Installing and configuring collectd
Icon
Adding a custom PostgreSQL monitor to collectd
Icon
Installing and configuring Graphite
Icon
Adding collectd data to Graphite
Icon
Building a graph in Graphite
Icon
Customizing a Graphite graph
Icon
Creating a Graphite dashboard
6
6. Replication
Icon
6. Replication
Icon
Introduction
Icon
Deciding what to copy
Icon
Securing the WAL stream
Icon
Setting up a hot standby
Icon
Upgrading to asynchronous replication
Icon
Bulletproofing with synchronous replication
Icon
Faking replication with pg_receivexlog
Icon
Setting up Slony
Icon
Copying a few tables with Slony
Icon
Setting up Bucardo
Icon
Copying a few tables with Bucardo
Icon
Setting up Londiste
Icon
Copying a few tables with Londiste
7
7. Replication Management Tools
Icon
7. Replication Management Tools
Icon
Introduction
Icon
Deciding when to use third-party tools
Icon
Installing and configuring Barman
Icon
Backing up a database with Barman
Icon
Restoring a database with Barman
Icon
Installing and configuring OmniPITR
Icon
Managing WAL files with OmniPITR
Icon
Installing and configuring repmgr
Icon
Cloning a database with repmgr
Icon
Swapping active nodes with repmgr
Icon
Installing and configuring walctl
Icon
Cloning a database with walctl
Icon
Managing WAL files with walctl
8
8. Advanced Stack
Icon
8. Advanced Stack
Icon
Introduction
Icon
Preparing systems for the stack
Icon
Getting started with the Linux Volume Manager
Icon
Adding block-level replication
Icon
Incorporating the second LVM layer
Icon
Verifying a DRBD filesystem
Icon
Correcting a DRBD split brain
Icon
Formatting an XFS filesystem
Icon
Tweaking XFS performance
Icon
Maintaining an XFS filesystem
Icon
Using LVM snapshots
Icon
Switching live stack systems
Icon
Detaching a problematic node
9
9. Cluster Control
Icon
9. Cluster Control
Icon
Introduction
Icon
Installing the components
Icon
Configuring Corosync
Icon
Preparing startup services
Icon
Starting with base options
Icon
Adding DRBD to cluster management
Icon
Adding LVM to cluster management
Icon
Adding XFS to cluster management
Icon
Adding PostgreSQL to cluster management
Icon
Adding a virtual IP to hide the cluster
Icon
Adding an e-mail alert
Icon
Grouping associated resources
Icon
Combining and ordering related actions
Icon
Performing a managed resource migration
Icon
Using an outage to test migration
10
10. Data Distribution
chevron up
Icon
10. Data Distribution
Icon
Introduction
Icon
Identifying horizontal candidates
Icon
Setting up a foreign PostgreSQL server
Icon
Mapping a remote user
Icon
Creating a foreign table
Icon
Using a foreign table in a query
Icon
Optimizing foreign table access
Icon
Transforming foreign tables into local tables
Icon
Creating a scalable nextval replacement
Icon
Building a sharding API
Icon
Talking to the right shard
Icon
Moving a shard to another server
11
Index
Icon
Index

Transforming foreign tables into local tables

Remote tables provide an easy and convenient way to access remote data in a PostgreSQL database. This is good for highly available systems, as a properly compartmentalized system invites segmented maintenance. Yet, remote data comes with a rather drastic cost regarding data fetching and handling overhead.

With the release of PostgreSQL 9.3 comes internal support of materialized views. Traditionally, materialized views merely instantiate a view into a physical structure to avoid expensive or complicated query plans and result sets. They also make it possible to index or optimize a view in ways not normally possible. Now, imagine what we can do with such a structure when utilizing foreign tables.

In this recipe, we will explore how materialized views can drastically increase local data access capability within a PostgreSQL database.

Getting ready

As we will be using the pgbench_accounts foreign table in this recipe, please follow all recipes up to...

CONTINUE READING
83
Tech Concepts
36
Programming languages
73
Tech Tools
Icon Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.
Icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Icon 50+ new titles added per month and exclusive early access to books as they are being written.
PostgreSQL 9 High Availability Cookbook
End of Section 10
Next Section
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist download Download options
font-size Font size

Change the font size

margin-width Margin width

Change margin width

day-mode Day/Sepia/Night Modes

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY

Submit Your Feedback

Modal Close icon
Modal Close icon
Modal Close icon