PostgreSQL Replication - Second Edition

Name: PostgreSQL Replication - Second Edition | Leverage the power of PostgreSQL replication to make your databases more robust, secure, scalable, and fast
Brand: Packt Publishing
Availability: OnlineOnly

Leverage the power of PostgreSQL replication to make your databases more robust, secure, scalable, and fast

Hans-Jurgen Schonig(Autor*in)

Packt Publishing

1. Auflage

Erschienen am 28. Juli 2015

322 Seiten

E-Book

ePUB mit Adobe-DRM

Systemvoraussetzungen

E-Book

PDF mit Adobe-DRM

Systemvoraussetzungen

978-1-78398-854-9 (ISBN)

ab 33,99 €

Als Download verfügbar

Merkliste: siehe Preise

Beschreibung

Alle Preise

Weitere Details

Weitere Ausgaben

Inhalt

Cover
Copyright
Credits
About the Author
About the Reviewers
www.PacktPub.com
Table of Contents
Preface
Chapter 1: Understanding Concepts of Replication
The CAP theorem and physical limitations of replication
Understanding the CAP theorem
Understanding the limits of physics
Different types of replication
Synchronous versus asynchronous replication
Considering performance issues
Understanding replication and data loss
Single-master versus multimaster replication
Logical versus physical replication
When to use physical replication
When to use logical replication
Using sharding and data distribution
Understanding the purpose of sharding
Designing a sharded system
Querying different fields
Pros and cons of sharding
Choosing between sharding and redundancy
Increasing and decreasing the size of a cluster
Combining sharding and replication
Various sharding solutions
PostgreSQL-based sharding
Summary
Chapter 2: Understanding the PostgreSQL Transaction Log
How PostgreSQL writes data
The PostgreSQL disk layout
Looking into the data directory
PG_VERSION - the PostgreSQL version number
base - the actual data directory
global - the global data
pg_clog - the commit log
pg_dynshmem - shared memory
pg_hba.conf - host-based network configuration
pg_ident.conf - ident authentication
pg_logical - logical decoding
pg_multixact - multitransaction status data
pg_notify - LISTEN/NOTIFY data
pg_replslot - replication slots
pg_serial - information about committed serializable transactions
pg_snapshot - exported snapshots
pg_stat - permanent statistics
pg_stat_tmp - temporary statistics data
pg_subtrans - subtransaction data
pg_tblspc - symbolic links to tablespaces
pg_twophase - information about prepared statements
pg_xlog - the PostgreSQL transaction log (WAL)
postgresql.conf - the central PostgreSQL configuration file
Writing one row of data
A simple INSERT statement
Read consistency
The purpose of the shared buffer
Mixed reads and writes
The format of the XLOG
The XLOG and replication
Understanding consistency and data loss
All the way to the disk
From memory to memory
From the memory to the disk
A word about batteries
Beyond the fsync function
PostgreSQL consistency levels
Tuning checkpoints and the XLOG
Understanding the checkpoints
Configuring checkpoints
About segments and timeouts
To write or not to write?
Tweaking WAL buffers
Experiencing XLOG in action
Understanding the XLOG records
Making the XLOG deterministic
Making the XLOG reliable
LSNs and shared buffer interaction
Debugging the XLOG and putting it all together
Making use of replication slots
Physical replication slots
Logical replication slots
Configuring replication identities
Summary
Chapter 3: Understanding Point-in-time Recovery
Understanding the purpose of PITR
Moving to the bigger picture
Archiving the transaction log
Taking base backups
Using pg_basebackup
Modifying pg_hba.conf
Signaling the master server
pg_basebackup - basic features
Making use of traditional methods to create base backups
Tablespace issues
Keeping an eye on network bandwidth
Replaying the transaction log
Performing a basic recovery
More sophisticated positioning in the XLOG
Cleaning up the XLOG on the way
Switching the XLOG files
Summary
Chapter 4: Setting Up Asynchronous Replication
Setting up streaming replication
Tweaking the config files on the master
Handling pg_basebackup and recovery.conf
Making the slave readable
The underlying protocol
Configuring a cascaded replication
Turning slaves into masters
Mixing streaming-based and file-based recovery
The master configuration
The slave configuration
Error scenarios
Network connection between the master and slave is dead
Rebooting the slave
Rebooting the master
Corrupted XLOG in the archive
Making streaming-only replication more robust
Using wal_keep_segments
Utilizing replication slots
Efficient cleanup and the end of recovery
Gaining control over the restart points
Tweaking the end of your recovery
Conflict management
Dealing with timelines
Delayed replicas
Handling crashes
Summary
Chapter 5: Setting Up Synchronous Replication
Synchronous replication setup
Understanding the downside to synchronous replication
Understanding the application_name parameter
Making synchronous replication work
Checking replication
Understanding performance issues
Setting synchronous_commit to on
Setting synchronous_commit to remote_write
Setting synchronous_commit to off
Setting synchronous_commit to local
Changing durability settings on the fly
Understanding the practical implications and performance
Redundancy and stopping replication
Summary
Chapter 6: Monitoring Your Setup
Checking your archive
Checking archive_command
Monitoring the transaction log archive
Checking pg_stat_replication
Relevant fields in pg_stat_replication
Checking for operating system processes
Checking for replication slots
Dealing with monitoring tools
Installing check_postgres
Deciding on a monitoring strategy
Summary
Chapter 7: Understanding Linux High Availability
Understanding the purpose of High Availability
Measuring availability
Durability and availability
Detecting failures
The split-brain syndrome
Understanding Linux-HA
Corosync
Pacemaker
Resource agents / fence agents
PCS
The PostgreSQL resource agent
Setting up a simple HA cluster
Preparing the servers
Installing the necessary software
Configuring the clustering software
Preparing for PostgreSQL installation
Syncing the standby
Configuring the cluster
Configuring cluster resources
Configuring the constraints
Setting up fencing
Verifying the setup
Common maintenance tasks
Performing maintenance on a single node
Forcing a failover
Recovering from failed PostgreSQL starts
Performing cluster-wide maintenance
Resynchronizing after master failure
Summary
Chapter 8: Working with PgBouncer
Understanding fundamental PgBouncer concepts
Installing PgBouncer
Configuring your first PgBouncer setup
Writing a simple config file and starting PgBouncer up
Dispatching requests
More basic settings
Handling pool sizes
max_client_conn
default_pool_size
min_pool_size
reserve_pool_size
pool_size
Authentication
Connecting to PgBouncer
Java issues
Pool modes
Cleanup issues
Improving performance
A simple benchmark
Maintaining PgBouncer
Configuring the admin interface
Using the management database
Extracting runtime information
Suspending and resuming operations
Summary
Chapter 9: Working with pgpool
Installing pgpool
Installing additional modules
Understanding pgpool features
Understanding the pgpool architecture
Setting up replication and load balancing
Password authentication
Firing up pgpool and testing the setup
Attaching hosts
Checking replication
Running pgpool with streaming replication
Optimizing pgpool configuration for master/slave mode
Dealing with failovers and high availability
Using PostgreSQL streaming and Linux HA
pgpool mechanisms for high availability and failover
Summary
Chapter 10: Configuring Slony
Installing Slony
Understanding how Slony works
Dealing with logical replication
The slon daemon
Replicating your first database
Deploying DDLs
Adding tables to replication and managing problems
Performing failovers
Planned failovers
Unplanned failovers
Summary
Chapter 11: Using SkyTools
Installing SkyTools
Dissecting SkyTools
Managing pgq queues
Running pgq
Creating queues and adding data
Adding consumers
Configuring the ticker
Consuming messages
Dropping queues
Using pgq for large projects
Using Londiste to replicate data
Replicating our first table
One word about walmgr
Summary
Chapter 12: Working with Postgres-XC
Understanding the Postgres-XC architecture
Data nodes
GTM
Coordinators
GTM proxy
Installing Postgres-XC
Configuring a simple cluster
Creating the GTM
Optimizing for performance
Dispatching the tables
Optimizing joins
Optimizing for warehousing
Creating a GTM proxy
Creating tables and issuing queries
Adding nodes
Rebalancing data
Handling failovers and dropping nodes
Handling node failovers
Replacing the nodes
Running a GTM standby
Summary
Chapter 13: Scaling with PL/Proxy
Understanding the basic concepts
Dealing with the bigger picture
Partitioning the data
Setting up PL/Proxy
A basic example
Partitioned reads and writes
Extending and handling clusters in a clever way
Adding and moving partitions
Increasing the availability
Managing foreign keys
Upgrading the PL/Proxy nodes
Summary
Chapter 14: Scaling with BDR
Understanding BDR replication concepts
Understanding eventual consistency
Handling conflicts
Distributing sequences
Handling DDLs
Use cases for BDR
Good use cases for BDR
Bad use cases for BDR
Logical decoding does the trick
Installing BDR
Installing binary packages
Setting up a simple cluster
Arranging storage
Creating database instances
Loading modules and firing up the cluster
Checking your setup
Handling conflicts
Understanding sets
Unidirectional replication
Handling data tables
Controlling replication
Summary
Chapter 15: Working with Walbouncer
The concepts of walbouncer
Filtering XLOG
Installing walbouncer
Configuring walbouncer
Creating a base backup
Firing up walbouncer
Using additional configuration options
Adjusting filtering rules
Removing and filtering objects
Adding objects to slaves
Summary
Index

Systemvoraussetzungen

Dateiformat: ePUB
Kopierschutz: Adobe-DRM (Digital Rights Management)

Systemvoraussetzungen:

Computer (Windows; MacOS X; Linux): Installieren Sie bereits vor dem Download die kostenlose Software Adobe Digital Editions (siehe E-Book Hilfe).
Tablet/Smartphone (Android; iOS): Installieren Sie bereits vor dem Download die kostenlose App Adobe Digital Editions oder die App PocketBook (siehe E-Book Hilfe).
E-Book-Reader: Bookeen, Kobo, Pocketbook, Sony, Tolino u.v.a.m. (nicht Kindle)

Das Dateiformat ePUB ist sehr gut für Romane und Sachbücher geeignet – also für „fließenden” Text ohne komplexes Layout. Bei E-Readern oder Smartphones passt sich der Zeilen- und Seitenumbruch automatisch den kleinen Displays an.
Mit Adobe-DRM wird hier ein „harter” Kopierschutz verwendet. Wenn die notwendigen Voraussetzungen nicht vorliegen, können Sie das E-Book leider nicht öffnen. Daher müssen Sie bereits vor dem Download Ihre Lese-Hardware vorbereiten.

Bitte beachten Sie: Wir empfehlen Ihnen unbedingt nach Installation der Lese-Software diese mit Ihrer persönlichen Adobe-ID zu autorisieren!

Weitere Informationen finden Sie in unserer E-Book Hilfe.

Dateiformat: PDF
Kopierschutz: Adobe-DRM (Digital Rights Management)

Systemvoraussetzungen:

Computer (Windows; MacOS X; Linux): Installieren Sie bereits vor dem Download die kostenlose Software Adobe Digital Editions (siehe E-Book Hilfe).
Tablet/Smartphone (Android; iOS): Installieren Sie bereits vor dem Download die kostenlose App Adobe Digital Editions oder die App PocketBook (siehe E-Book Hilfe).
E-Book-Reader: Bookeen, Kobo, Pocketbook, Sony, Tolino u.v.a.m. (nicht Kindle)

Das Dateiformat PDF zeigt auf jeder Hardware eine Buchseite stets identisch an. Daher ist eine PDF auch für ein komplexes Layout geeignet, wie es bei Lehr- und Fachbüchern verwendet wird (Bilder, Tabellen, Spalten, Fußnoten). Bei kleinen Displays von E-Readern oder Smartphones sind PDF leider eher nervig, weil zu viel Scrollen notwendig ist.
Mit Adobe-DRM wird hier ein „harter” Kopierschutz verwendet. Wenn die notwendigen Voraussetzungen nicht vorliegen, können Sie das E-Book leider nicht öffnen. Daher müssen Sie bereits vor dem Download Ihre Lese-Hardware vorbereiten.

Bitte beachten Sie: Wir empfehlen Ihnen unbedingt nach Installation der Lese-Software diese mit Ihrer persönlichen Adobe-ID zu autorisieren!

Weitere Informationen finden Sie in unserer E-Book Hilfe.

Als PDF speichern Als Link merken