Home Page Icon
Home Page
Table of Contents for
Index
Close
Index
by Hans-Jürgen Schönig, Zoltan Böszörmenyi
PostgreSQL Replication
PostgreSQL Replication
Table of Contents
PostgreSQL Replication
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Support files, eBooks, discount offers and more
Why Subscribe?
Free Access for Packt account holders
Preface
What this book covers
What you need for this book
Who this book is for
Conventions
Reader feedback
Customer support
Downloading the example code
Errata
Piracy
Questions
1. Understanding Replication Concepts
The CAP theory and physical limitations
Understanding the CAP theory
Why the speed of light matters
Long distance transmission
Why latency matters
Different types of replication
Synchronous versus asynchronous replication
Understanding replication and data loss
Considering the performance issues
Single-master versus multi-master 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
An example of designing a sharded system
An example of 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
External frameworks/middleware
Summary
2. Understanding the PostgreSQL Transaction Log
How PostgreSQL writes data
The PostgreSQL disk layout
Looking into the data directory
PG_VERSION – PostgreSQL version number
base – the actual data directory
Growing data files
Performing I/O in chunks
Relation forks
global – the global data
Dealing with standalone data files
pg_clog – the commit log
pg_hba.conf – host-based network configuration
pg_ident.conf – ident authentication
pg_multixact – multi-transaction status data
pg_notify – LISTEN/NOTIFY data
pg_serial – information about committed serializable transactions
pg_snapshot – exported snapshots
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
Crashing during WAL-writing
Crashing after WAL-writing
Read consistency
The purpose of the shared buffer
Mixed reads and writes
The XLOG and replication
Understanding consistency and data loss
All the way to the disk
From memory to memory
From memory to the disk
One word about batteries
Beyond fsync()
PostgreSQL consistency levels
Tuning checkpoints and the XLOG
Understanding the checkpoints
Configuring checkpoints
About segments and timeouts
To write or not to write?
Scenario 1 – Storing stock-market data
Scenario 2 – Bulk loading
Scenario 3 – I/O spikes and throughput considerations
Conclusion
Tweaking WAL buffers
The internal structure of the XLOG
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
Summary
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
pg_basebackup – self-sufficient backups
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
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 to masters
Mixing streaming 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 the streaming-only replication more robust
Efficient cleanup and the end of recovery
Gaining control over the restart points
Tweaking the end of your recovery
Conflict management
Dealing with the timelines
Summary
5. Setting up Synchronous Replication
Setting up synchronous replication
Understanding the downside of 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 practical implications and performance
Redundancy and stopping replication
Summary
6. Monitoring Your Setup
Checking your archive
Checking the archive_command
Monitoring the transaction log archive
Checking pg_stat_replication
Relevant fields in pg_stat_replication
Checking for operating system processes
Dealing with monitoring tools
Installing check_postgres
Deciding on a monitoring strategy
Summary
7. Understanding Linux High Availability
Understanding the purpose of high availability
Measuring availability
History of high-availability software
OpenAIS and Corosync
Linux-HA (Heartbeat) and Pacemaker
Terminology and concepts
High availability is all about redundancy
PostgreSQL and high availability
High availability with quorum
High availability with STONITH
Summary
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
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
9. Working with pgpool
Installing pgpool
Installing pgpool-regclass and insert_lock
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
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
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
12. Working with Postgres-XC
Understanding the Postgres-XC architecture
Data nodes
GTM – Global Transaction Manager
Coordinators
GTM Proxy
Installing Postgres-XC
Configuring a simple cluster
Creating the GTM
Optimizing for performance
Dispatching the tables
Optimizing the joins
Optimizing for warehousing
Creating a GTM Proxy
Creating the tables and issuing the queries
Adding nodes
Handling failovers and dropping nodes
Handling node failovers
Replacing the nodes
Running a GTM standby
Summary
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 the foreign keys
Upgrading the PL/Proxy nodes
Summary
Index
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Summary
Index
A
admin interface
, configuring /
Configuring the admin interface
configuring /
Configuring the admin interface
management database, using /
Using the management database
runtime information, extracting /
Extracting runtime information
operations, resuming /
Suspending and resuming operations
operations, suspending /
Suspending and resuming operations
ALTER SERVER /
Adding and moving partitions
,
Upgrading the PL/Proxy nodes
application_name field /
Relevant fields in pg_stat_replication
application_name parameter
about /
Understanding the application_name parameter
/
Making synchronous replication work
archive
checking /
Checking your archive
archive_cleanup_command /
Tweaking the end of your recovery
archive_command /
Moving to the bigger picture
checking /
Checking the archive_command
asynchronous replication
versus synchronous replication /
Synchronous versus asynchronous replication
authentication_timeout setting /
Setting up replication and load balancing
Availability
about /
Understanding the CAP theory
availability
measuring /
Measuring availability
B
.backup file /
Cleaning up the XLOG on the way
backend_data_directory0 setting /
Setting up replication and load balancing
backend_flag setting /
Setting up replication and load balancing
backend_hostname0 setting /
Setting up replication and load balancing
backend_port0 setting /
Setting up replication and load balancing
backend_start field /
Relevant fields in pg_stat_replication
backend_weight0 setting /
Setting up replication and load balancing
base directory /
base – the actual data directory
black_function_list setting /
Setting up replication and load balancing
C
CAP
about /
The CAP theory and physical limitations
,
Understanding the CAP theory
speed of light /
Why the speed of light matters
long distance transmission /
Long distance transmission
latency /
Why latency matters
cascaded replication
configuring /
Configuring a cascaded replication
changelog triggers /
Dealing with logical replication
checkpointing /
Understanding the checkpoints
checkpoints
and XLOG /
Tuning checkpoints and the XLOG
about /
Understanding the checkpoints
configuring /
Configuring checkpoints
checkpoint_timeout /
About segments and timeouts
checkpoint_segments /
About segments and timeouts
checkpoint_completion target /
To write or not to write?
,
Scenario 3 – I/O spikes and throughput considerations
checkpoint_segments /
About segments and timeouts
checkpoint_timeout /
About segments and timeouts
check_postgres
installing /
Installing check_postgres
child_life_time setting /
Setting up replication and load balancing
child_max_connections setting /
Setting up replication and load balancing
client_addr field /
Relevant fields in pg_stat_replication
client_hostname field /
Relevant fields in pg_stat_replication
client_idle_limit setting /
Setting up replication and load balancing
client_port field /
Relevant fields in pg_stat_replication
cloned services /
Terminology and concepts
cluster
about /
Terminology and concepts
configuring /
Configuring a simple cluster
CLUSTER keyword /
A basic example
Cluster Resource Manager (CRM) /
Linux-HA (Heartbeat) and Pacemaker
clusters
extending /
Extending and handling clusters in a clever way
handling /
Extending and handling clusters in a clever way
partitions, adding /
Adding and moving partitions
partitions, moving /
Adding and moving partitions
replication /
Increasing the availability
foreign keys, managing /
Managing the foreign keys
PL/Proxy nodes, upgrading /
Upgrading the PL/Proxy nodes
cluster size
increasing /
Increasing and decreasing the size of a cluster
decreasing /
Increasing and decreasing the size of a cluster
commit log /
pg_clog – the commit log
COMMIT record /
Understanding the XLOG records
configure /
Installing skytools
connection_cache setting /
Setting up replication and load balancing
Consistency
about /
Understanding the CAP theory
consumers
adding /
Adding consumers
coordinators /
Coordinators
Corosync
about /
OpenAIS and Corosync
CRC32 checksum /
Making the XLOG reliable
createdb command-line tool /
Checking replication
CREATE EXTENSION /
Upgrading the PL/Proxy nodes
create_user procedure /
Partitioned reads and writes
D
-d command /
Configuring the ticker
data
replicating /
Understanding replication and data loss
adding /
Creating queues and adding data
partitioning /
Partitioning the data
database
replicating /
Replicating your first database
database_list directive /
Configuring the ticker
data distribution
using /
Using sharding and data distribution
data loss
about /
Understanding replication and data loss
data nodes /
Data nodes
Data Node Scan /
Creating the tables and issuing the queries
data replication
londiste, using /
Using londiste to replicate data
DDLs
replicating /
Deploying DDLs
about /
Deploying DDLs
slonik script /
Deploying DDLs
Dell Remote Access Card (DRAC) /
Terminology and concepts
Dequeue operation /
Managing pgq-queues
Detail Records (CDRs) /
Scenario 3 – I/O spikes and throughput considerations
DISCARD ALL /
Cleanup issues
DISTRIBUTE BY clause /
Dispatching the tables
Dow Jones Industrial Average (DJIA) /
Scenario 1 – Storing stock-market data
downtime
about /
Understanding the purpose of high availability
DROP NODE /
Replacing the nodes
dual-strategy cluster, error scenarios
network connection /
Network connection between the master and slave is dead
slave, rebooting /
Rebooting the slave
master, rebooting /
Rebooting the master
XLOG, in archive /
Corrupted XLOG in the archive
E
enable_pool_hba setting /
Setting up replication and load balancing
Enqueue operation /
Managing pgq-queues
external frameworks
about /
External frameworks/middleware
external middleware
about /
External frameworks/middleware
F
fail-over
about /
Terminology and concepts
failovers
performing /
Performing failovers
planned /
Planned failovers
unplanned /
Unplanned failovers
fencing
about /
Terminology and concepts
fields
querying, example /
An example of querying different fields
floating IP address /
PostgreSQL and high availability
flush_location field /
Relevant fields in pg_stat_replication
foreign keys
managing /
Managing the foreign keys
fork() calls /
Cleanup issues
forward keyword /
Replicating your first database
fsync() /
From memory to the disk
,
Beyond fsync()
fsync parameter /
PostgreSQL consistency levels
G
global /
global – the global data
GTM /
GTM – Global Transaction Manager
creating /
Creating the GTM
gtm.conf /
Running a GTM standby
GTM Proxy /
GTM Proxy
creating /
Creating a GTM Proxy
GTM standby
running /
Running a GTM standby
H
HASH /
Dispatching the tables
high-availability
software, history /
History of high-availability software
high availability
redundancy /
High availability is all about redundancy
and PostgreSQL /
PostgreSQL and high availability
with quorum /
High availability with quorum
with STONITH /
High availability with STONITH
High Availability (HA) /
Handling node failovers
hosts
attaching /
Attaching hosts
hot_standby_feedback /
Conflict management
I
ident authentication /
pg_ident.conf – ident authentication
ignore_leading_white_space setting /
Setting up replication and load balancing
init cluster /
Replicating your first database
initdb /
Creating the GTM
init file /
Replicating our first table
initgtm /
Creating the GTM
INSERT statement
about /
A simple INSERT statement
WAL-writing, crashing during /
Crashing during WAL-writing
WAL-writing, crashing after /
Crashing after WAL-writing
/
Partitioned reads and writes
insert_lock
installing /
Installing pgpool-regclass and insert_lock
insert_lock setting /
Setting up replication and load balancing
Integral Lights-Out (iLO) /
Terminology and concepts
Intelligent Platform Management Interface (IPMI) /
Terminology and concepts
J
joins
optimizing /
Optimizing the joins
L
latency
about /
Why latency matters
Linux-HA stack
URL /
Linux-HA (Heartbeat) and Pacemaker
listen_addresses setting /
Setting up replication and load balancing
load balancing
setting up /
Setting up replication and load balancing
load_balance_mode setting /
Setting up replication and load balancing
logfile directive /
More basic settings
logical replication
versus physical replication /
Logical versus physical replication
about /
When to use logical replication
londiste
about /
Dissecting skytools
,
Using londiste to replicate data
used, for replicating data /
Using londiste to replicate data
advantages /
Using londiste to replicate data
table, replicating /
Replicating our first table
londiste command /
Replicating our first table
long distance transmission /
Long distance transmission
LSN
about /
LSNs and shared buffer interaction
M
make installcheck /
Setting up PL/Proxy
management database
using /
Using the management database
master
config files, tweaking /
Tweaking the config files on the master
wal_level /
Tweaking the config files on the master
max_wal_senders /
Tweaking the config files on the master
slaves, turning to /
Turning slaves to masters
configuration /
The master configuration
master-slave resources /
Terminology and concepts
master mode
pgpool configuration, optimizing /
Optimizing pgpool configuration for master/slave mode
max_pool setting /
Setting up replication and load balancing
max_standby_archive_delay parameter /
Conflict management
max_standby_streaming_delay parameter /
Conflict management
max_wal_senders /
pg_basebackup – self-sufficient backups
,
Tweaking the config files on the master
messages
consuming /
Consuming messages
min_pool_size /
Improving performance
monitoring strategy
deciding on /
Deciding on a monitoring strategy
monitoring tools
dealing with /
Dealing with monitoring tools
multi-master replication
versus single-master replication /
Single-master versus multi-master replication
N
N-node cluster
about /
Terminology and concepts
network bandwidth
watching /
Keeping an eye on network bandwidth
node
about /
Terminology and concepts
node failovers
handling /
Handling node failovers
nodes
adding /
Adding nodes
replacing /
Replacing the nodes
num_init_children setting /
Setting up replication and load balancing
O
OLTP /
Creating a GTM Proxy
OpenAIS
about /
OpenAIS and Corosync
OpenHPI
URL /
OpenAIS and Corosync
OpenSAF
URL /
OpenAIS and Corosync
operating system processes
checking for /
Checking for operating system processes
P
partitions
adding /
Adding and moving partitions
moving /
Adding and moving partitions
Partition tolerance
about /
Understanding the CAP theory
password
authenticating /
Password authentication
encrypting /
Password authentication
pcp_port setting /
Setting up replication and load balancing
pcp_socket_dir setting /
Setting up replication and load balancing
performance
optimizing, for speed /
Optimizing for performance
performance issues
about /
Considering the performance issues
pgbench /
A simple benchmark
pgbouncer
about /
Understanding fundamental pgbouncer concepts
advantages /
Understanding fundamental pgbouncer concepts
installing /
Installing pgbouncer
downloading /
Installing pgbouncer
setup, configuring /
Configuring your first pgbouncer setup
connecting to /
Connecting to pgbouncer
Java, issues /
Java issues
pool modes /
Pool modes
cleanup, issues /
Cleanup issues
pgbouncer, configuration
simple config file, writing /
Writing a simple config file and starting pgbouncer up
pgbouncer up, starting /
Writing a simple config file and starting pgbouncer up
requests, dispatching /
Dispatching requests
basic settings /
More basic settings
authentication /
Authentication
pgbouncer, performance
improving /
Improving performance
pgpool
maintaining /
Maintaining pgbouncer
installing /
Installing pgpool
downloading /
Installing pgpool
features /
Understanding pgpool features
architecture /
Understanding the pgpool architecture
setting up /
Setting up replication and load balancing
firing up /
Firing up pgpool and testing the setup
running, with streaming replication /
Running pgpool with streaming replication
configuration, optimizing for master mode /
Optimizing pgpool configuration for master/slave mode
configuration, optimizing for slave mode /
Optimizing pgpool configuration for master/slave mode
for high availability /
Dealing with failovers and high availability
,
pgpool mechanisms for high availability and failover
for failovers /
Dealing with failovers and high availability
PostgreSQL streaming, using /
Using PostgreSQL streaming and Linux HA
Linux HA, using /
Using PostgreSQL streaming and Linux HA
for failover /
pgpool mechanisms for high availability and failover
pgpool-regclass
installing /
Installing pgpool-regclass and insert_lock
pgq
about /
Dissecting skytools
queues, managing /
Managing pgq-queues
running /
Running pgq
queues, creating /
Creating queues and adding data
data, adding /
Creating queues and adding data
consumers, adding /
Adding consumers
ticker, configuring /
Configuring the ticker
messages, consuming /
Consuming messages
queues, dropping /
Dropping queues
used, for large projects /
Using pgq for large projects
pg_basebackup
using /
Using pg_basebackup
features /
pg_basebackup – basic features
about /
pg_basebackup – self-sufficient backups
traditional methods, using /
Making use of traditional methods to create base backups
pg_clog /
pg_clog – the commit log
pg_dump /
Understanding the purpose of PITR
pg_foreign_server /
A basic example
pg_hba.conf
modifying /
Modifying pg_hba.conf
pg_hba.conf file /
pg_hba.conf – host-based network configuration
pg_ident.conf file /
pg_ident.conf – ident authentication
pg_multixact /
pg_multixact – multi-transaction status data
pg_notify /
pg_notify – LISTEN/NOTIFY data
pg_serial /
pg_serial – information about committed serializable transactions
pg_snapshot /
pg_snapshot – exported snapshots
pg_stat_replication
checking /
Checking pg_stat_replication
fields /
Relevant fields in pg_stat_replication
pid field /
Relevant fields in pg_stat_replication
usesysid field /
Relevant fields in pg_stat_replication
usename field /
Relevant fields in pg_stat_replication
application_name field /
Relevant fields in pg_stat_replication
client_addr field /
Relevant fields in pg_stat_replication
client_hostname field /
Relevant fields in pg_stat_replication
client_port field /
Relevant fields in pg_stat_replication
backend_start field /
Relevant fields in pg_stat_replication
state field /
Relevant fields in pg_stat_replication
sent_location field /
Relevant fields in pg_stat_replication
write_location field /
Relevant fields in pg_stat_replication
flush_location field /
Relevant fields in pg_stat_replication
replay_location field /
Relevant fields in pg_stat_replication
sync_priority field /
Relevant fields in pg_stat_replication
sync_state field /
Relevant fields in pg_stat_replication
pg_stat_tmp /
pg_stat_tmp – temporary statistics data
pg_subtrans /
pg_subtrans – subtransaction data
pg_switch_xlog() /
Switching the XLOG files
pg_tblspc directory /
pg_tblspc – symbolic links to tablespaces
pg_twophas /
pg_twophase – information about prepared statements
pg_XLOG /
pg_XLOG – the PostgreSQL transaction log (WAL)
physical replication
versus logical replication /
Logical versus physical replication
about /
When to use physical replication
pid field /
Relevant fields in pg_stat_replication
pid_file_name setting /
Setting up replication and load balancing
PITR
purpose /
Understanding the purpose of PITR
architecture /
Moving to the bigger picture
transaction log, archiving /
Archiving the transaction log
PL/Proxy
about /
Dealing with the bigger picture
installing /
Dealing with the bigger picture
,
Setting up PL/Proxy
setting up /
Setting up PL/Proxy
URL /
Setting up PL/Proxy
example /
A basic example
enabling /
A basic example
used, for partitioning reads /
Partitioned reads and writes
used, for partitioning writes /
Partitioned reads and writes
PL/Proxy nodes
upgrading /
Upgrading the PL/Proxy nodes
planned failovers
about /
Planned failovers
pool_modes
session /
Pool modes
transaction /
Pool modes
statement /
Pool modes
pool_passwd setting /
Setting up replication and load balancing
port setting /
Setting up replication and load balancing
Postgres-XC
URL /
Installing Postgres-XC
installing /
Installing Postgres-XC
Postgres-XC architecture
components /
Understanding the Postgres-XC architecture
data nodes /
Data nodes
GTM /
GTM – Global Transaction Manager
coordinators /
Coordinators
GTM Proxy /
GTM Proxy
PostgreSQL
data, writing /
How PostgreSQL writes data
disk layout /
The PostgreSQL disk layout
consistency levels /
PostgreSQL consistency levels
and high availability /
PostgreSQL and high availability
PostgreSQL-based sharding
about /
PostgreSQL-based sharding
postgresql.conf /
postgresql.conf – the central PostgreSQL configuration file
,
Archiving the transaction log
postgresql.conf parameter /
Java issues
postgresql.conf setting /
Switching the XLOG files
PostgreSQL database system
architecture /
Read consistency
shared buffer /
The purpose of the shared buffer
reads and writes /
Mixed reads and writes
PostgreSQL disk layout
about /
The PostgreSQL disk layout
data directory, looking into /
Looking into the data directory
PostgreSQL version number /
PG_VERSION – PostgreSQL version number
base directory /
base – the actual data directory
data files, growing /
Growing data files
I/O, performing in chunks /
Performing I/O in chunks
PostgreSQL replication
about /
How PostgreSQL writes data
PostgreSQL transaction
data loss /
Understanding consistency and data loss
,
All the way to the disk
,
From memory to memory
,
From memory to the disk
,
One word about batteries
PostgreSQL transaction, data loss
from memory to memory /
From memory to memory
from memory to disk /
From memory to the disk
PostgreSQL transaction log (WAL) /
pg_XLOG – the PostgreSQL transaction log (WAL)
PostgreSQL version number /
PG_VERSION – PostgreSQL version number
postmaster server
signaling /
Signaling the master server
primary_conninfo setting /
Handling pg_basebackup and recovery.conf
projects
pgq, using for /
Using pgq for large projects
Q
queries
issuing /
Creating the tables and issuing the queries
queues
Enqueue operation /
Managing pgq-queues
Dequeue operation /
Managing pgq-queues
creating /
Creating queues and adding data
dropping /
Dropping queues
quorum
about /
Terminology and concepts
high availability with /
High availability with quorum
quorum-server
about /
Terminology and concepts
quorum server /
High availability with quorum
R
random() function /
Making the XLOG deterministic
reads
partitioning, PL/Proxy used /
Partitioned reads and writes
recovery.conf /
More sophisticated positioning in the XLOG
,
Efficient cleanup and the end of recovery
recovery.conf file /
Making synchronous replication work
recovery_end_command /
Cleaning up the XLOG on the way
,
Tweaking the end of your recovery
recovery_target_inclusive /
More sophisticated positioning in the XLOG
redundancy
and sharding, choosing between /
Choosing between sharding and redundancy
replay_location field /
Relevant fields in pg_stat_replication
replicate_select setting /
Setting up replication and load balancing
replication
synchronous versus asynchronous replication /
Synchronous versus asynchronous replication
single-master versus multi-master replication /
Single-master versus multi-master replication
logical versus physical replication /
Logical versus physical replication
and sharding, combining /
Combining sharding and replication
setting up /
Setting up replication and load balancing
checking /
Checking replication
tables, adding /
Adding tables to replication and managing problems
increasing /
Increasing the availability
replication_mode setting /
Setting up replication and load balancing
reset_query_list setting /
Setting up replication and load balancing
resource agents /
Terminology and concepts
resources
about /
Terminology and concepts
restore_command /
Moving to the bigger picture
,
Performing a basic recovery
ROUND ROBIN /
Dispatching the tables
runtime information
extracting /
Extracting runtime information
S
SELECT pg_xlog_replay_resume() /
More sophisticated positioning in the XLOG
SELECT statement /
A basic example
sent_location field /
Relevant fields in pg_stat_replication
serializable /
Understanding the purpose of PITR
services
about /
Terminology and concepts
session, pool mode /
Pool modes
sharded system
designing /
An example of designing a sharded system
sharding
using /
Using sharding and data distribution
need for /
Understanding the purpose of sharding
advantages /
Pros and cons of sharding
disadvantages /
Pros and cons of sharding
and redundancy, choosing between /
Choosing between sharding and redundancy
and replication, combining /
Combining sharding and replication
implementing /
Various sharding solutions
sharding, implementing
PostgreSQL-based sharding /
PostgreSQL-based sharding
external frameworks/middleware /
External frameworks/middleware
SHOW DATABASES command /
Extracting runtime information
single-master replication
versus multi-master replication /
Single-master versus multi-master replication
single point of failure (SPOF)
about /
Terminology and concepts
Skytools
installing /
Installing skytools
URL /
Installing skytools
disecting /
Dissecting skytools
pgq /
Dissecting skytools
londiste /
Dissecting skytools
walmgr /
Dissecting skytools
slave
configuration /
The slave configuration
slave mode
pgpool configuration, optimizing /
Optimizing pgpool configuration for master/slave mode
slaves
turning, to masters /
Turning slaves to masters
slon daemon /
The slon daemon
slonik /
Replicating your first database
Slony
installing /
Installing Slony
working /
Understanding how Slony works
logical replication /
Dealing with logical replication
slon daemon /
The slon daemon
socket_dir setting /
Setting up replication and load balancing
speed of light
about /
Why the speed of light matters
split-brain situation
about /
Terminology and concepts
srvoptions /
A basic example
sr_check_password variable /
Optimizing pgpool configuration for master/slave mode
sr_check_period variable /
Optimizing pgpool configuration for master/slave mode
sr_check_user variable /
Optimizing pgpool configuration for master/slave mode
ssl setting /
Setting up replication and load balancing
standalone services /
Terminology and concepts
STANDBY parameter /
Running a GTM standby
standby_mode setting /
Handling pg_basebackup and recovery.conf
state field /
Relevant fields in pg_stat_replication
statement, pool mode /
Pool modes
STONITH
about /
Terminology and concepts
high availability with /
High availability with STONITH
streaming replication
setting up /
Setting up streaming replication
config files, tweaking on master /
Tweaking the config files on the master
pg_basebackup, handling /
Handling pg_basebackup and recovery.conf
recovery.conf, handling /
Handling pg_basebackup and recovery.conf
slave, making readable /
Making the slave readable
protocols /
The underlying protocol
pgpool, running with /
Running pgpool with streaming replication
SunCluster /
History of high-availability software
Sun Cluster /
History of high-availability software
synchronous replication
versus asynchronous replication /
Synchronous versus asynchronous replication
setting up /
Setting up synchronous replication
downsides /
Understanding the downside of synchronous replication
application_name parameter /
Understanding the application_name parameter
working /
Making synchronous replication work
checking /
Checking replication
performance, issues /
Understanding performance issues
synchronous_commit, setting to on /
Setting synchronous_commit to on
durability settings, changing /
Changing durability settings on the fly
practical implications /
Understanding practical implications and performance
performance /
Understanding practical implications and performance
redundancy /
Redundancy and stopping replication
stopping /
Redundancy and stopping replication
synchronous_commit /
PostgreSQL consistency levels
setting, to on /
Setting synchronous_commit to on
setting, to remote_write /
Setting synchronous_commit to remote_write
setting, to off /
Setting synchronous_commit to off
setting, to local /
Setting synchronous_commit to local
synchronous_commit variable /
Understanding performance issues
synchronous_standby_names setting /
Checking replication
sync_priority field /
Relevant fields in pg_stat_replication
sync_state field /
Relevant fields in pg_stat_replication
sync_state setting /
Checking replication
SysVshmem /
Mixed reads and writes
T
table
replicating /
Replicating our first table
tables
dispatching /
Dispatching the tables
creating /
Creating the tables and issuing the queries
Tablespace
issues /
Tablespace issues
TARGET command /
Partitioned reads and writes
ticker
configuring /
Configuring the ticker
ticker config /
Replicating our first table
timelines
dealing with /
Dealing with the timelines
transaction log
archiving /
Archiving the transaction log
replaying /
Replaying the transaction log
basic recovery, performing /
Performing a basic recovery
XLOG /
More sophisticated positioning in the XLOG
XLOG, cleaning up /
Cleaning up the XLOG on the way
XLOG files, switching /
Switching the XLOG files
transaction log archive
monitoring /
Monitoring the transaction log archive
trigger_file /
pgpool mechanisms for high availability and failover
t_person table /
Optimizing the joins
t_person_payment table /
Optimizing the joins
t_postal_code table /
Optimizing the joins
U
unplanned failovers
about /
Unplanned failovers
uptime
about /
Understanding the purpose of high availability
USAGE permissions /
A basic example
usename field /
Relevant fields in pg_stat_replication
usesysid field /
Relevant fields in pg_stat_replication
V
vacuum_defer_cleanup_age /
Conflict management
virtual IP address /
PostgreSQL and high availability
W
WAL (Write Ahead Log) /
A simple INSERT statement
WAL(Write Ahead Log) /
pg_XLOG – the PostgreSQL transaction log (WAL)
WAL-buffers
tweaking /
Tweaking WAL buffers
walmgr
about /
Dissecting skytools
,
One word about walmgr
walreceiver /
Making the slave readable
wal_debug /
Debugging the XLOG and putting it all together
wal_keep_segments /
Making the streaming-only replication more robust
,
Making synchronous replication work
wal_level /
Tweaking the config files on the master
wal_level settings /
Archiving the transaction log
warehousing
optimizing for /
Optimizing for warehousing
white_function_list setting /
Setting up replication and load balancing
writes
partitioning, PL/Proxy used /
Partitioned reads and writes
write_location field /
Relevant fields in pg_stat_replication
X
--xlog-method=stream /
Making synchronous replication work
--xlog-method=stream option /
pg_basebackup – self-sufficient backups
XLOG
and replication /
The XLOG and replication
and checkpoints /
Tuning checkpoints and the XLOG
,
Understanding the checkpoints
about /
The internal structure of the XLOG
,
Cleaning up the XLOG on the way
records /
Understanding the XLOG records
deterministic, creating /
Making the XLOG deterministic
making reliable /
Making the XLOG reliable
debugging /
Debugging the XLOG and putting it all together
files, switching /
Switching the XLOG files
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset