Skip to content

Instantly share code, notes, and snippets.

@blt

blt/a_log.md

Created September 1, 2011 16:28
Show Gist options
  • Save blt/1186576 to your computer and use it in GitHub Desktop.
Save blt/1186576 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
a_log.md

I'm trying to configure a hot-standby system. I have two machines:

  • db0 :: continuously archiving master
  • db1 :: continuously replicating slave

I have my system laid out somewhat differently than usual, mostly to accomadate the limitations of puppet. You'll find my postgresql.confs and pg_hba.conf here. The mapping of my disks to mount points are like so:

  • /dev/md0 --> /srv/pg/data
  • /dev/md1 --> /srv/pg/xlog
  • /dev/xvdn --> /srv/pg/archive

You'll note that archive_commmand is set to dump WAL files into /srv/pg/archive. Here's how db1 is prepared to get a hot-copy of db0's database:


root@db1:~# /etc/init.d/postgresql stop
Stopping PostgreSQL 9.0 database server: main.
root@db1:~# umount /dev/md0 && mkfs.xfs -f /dev/md0 && mount /dev/md0
log stripe unit (524288 bytes) is too large (maximum is 256KiB)
log stripe unit adjusted to 32KiB
meta-data=/dev/md0               isize=256    agcount=16, agsize=1638272 blks
         =                       sectsz=512   attr=2, projid32bit=0
data     =                       bsize=4096   blocks=26212352, imaxpct=25
         =                       sunit=128    swidth=256 blks
naming   =version 2              bsize=4096   ascii-ci=0
log      =internal log           bsize=4096   blocks=12800, version=2
         =                       sectsz=512   sunit=8 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0
root@db1:~# umount /dev/md1 && mkfs.xfs -f /dev/md1 && mount /dev/md1
log stripe unit (524288 bytes) is too large (maximum is 256KiB)
log stripe unit adjusted to 32KiB
meta-data=/dev/md1               isize=256    agcount=16, agsize=163712 blks
         =                       sectsz=512   attr=2, projid32bit=0
data     =                       bsize=4096   blocks=2619392, imaxpct=25
         =                       sunit=128    swidth=256 blks
naming   =version 2              bsize=4096   ascii-ci=0
log      =internal log           bsize=4096   blocks=2560, version=2
         =                       sectsz=512   sunit=8 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0
root@db1:~# umount /dev/xvdn && mkfs.xfs -f /dev/xvdn && mount /dev/xvdn
meta-data=/dev/xvdn              isize=256    agcount=4, agsize=655360 blks
         =                       sectsz=512   attr=2, projid32bit=0
data     =                       bsize=4096   blocks=2621440, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0
log      =internal log           bsize=4096   blocks=2560, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0
root@db1:~# chown -R postgres:postgres /srv/pg/ && chmod -R 0700 /srv/pg/
root@db1:~# ls -al /srv/pg/
total 8
drwx------ 5 postgres postgres 4096 Sep  1 13:22 .
drwxr-xr-x 3 root     root     4096 Sep  1 05:12 ..
drwx------ 2 postgres postgres    6 Sep  1 19:27 archive
drwx------ 2 postgres postgres    6 Sep  1 19:26 data
drwx------ 2 postgres postgres    6 Sep  1 19:26 xlog

We move to db0 and


root@db0:~# su - postgres
postgres@db0:~$ psql
psql (9.0.4)
Type "help" for help.

postgres=# SELECT pg_start_backup('memory');
 pg_start_backup 
-----------------
 0/12000020
(1 row)

postgres=# ^D\q
postgres@db0:~$ logout
root@db0:~# rsync -azq --exclude /srv/pg/data/pg_xlog /srv/pg/data/ db1.carepilot.com:/srv/pg/data
[email protected]'s password: 
root@db0:~# su - postgres
postgres@db0:~$ psql
psql (9.0.4)
Type "help" for help.

postgres=# SELECT pg_stop_backup();
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup 
----------------
 0/120000D8
(1 row)

postgres=# ^D\q
postgres@db0:~$ logout
root@db0:~# ls /srv/pg/xlog/
000000010000000000000012  000000010000000000000012.00000020.backup  000000010000000000000013
  000000010000000000000014	archive_status

back on db1


root@db1:~# ls /srv/pg/archive/
000000010000000000000011  000000010000000000000012  000000010000000000000012.00000020.backup
root@db1:~# ... put recovery.conf to /srv/pg/data/recovery.conf ...
root@db1:~# /etc/init.d/postgresql start
Starting PostgreSQL 9.0 database server: main.
root@db1:~# tail -f -n0 /var/log/messages 
Sep  1 20:19:32 db1 postgres[16455]: [1-1] r (4e5fe8d4.4047) -- 2011-09-01 20:19:32.788 UTC>
 LOG:  database system was interrupted; last known up at 2011-09-01 20:17:35 UTC
Sep  1 20:19:32 db1 postgres[16455]: [2-1] r (4e5fe8d4.4047) -- 2011-09-01 20:19:32.788 UTC>
 LOG:  creating missing WAL directory "pg_xlog/archive_status"
Sep  1 20:19:32 db1 postgres[16455]: [3-1] r (4e5fe8d4.4047) -- 2011-09-01 20:19:32.789 UTC>
 LOG:  entering standby mode
Sep  1 20:19:32 db1 postgres[16455]: [4-1] r (4e5fe8d4.4047) -- 2011-09-01 20:19:32.813 UTC>
 LOG:  restored log file "000000010000000000000014" from archive
Sep  1 20:19:32 db1 postgres[16455]: [5-1] r (4e5fe8d4.4047) -- 2011-09-01 20:19:32.817 UTC>
 LOG:  redo starts at 0/14000020
Sep  1 20:19:32 db1 postgres[16455]: [6-1] r (4e5fe8d4.4047) -- 2011-09-01 20:19:32.831 UTC>
 LOG:  consistent recovery state reached at 0/15000000
Sep  1 20:19:32 db1 postgres[16454]: [1-1] r (4e5fe8d3.4046) -- 2011-09-01 20:19:32.832 UTC>
 LOG:  database system is ready to accept read only connections
Sep  1 20:19:32 db1 postgres[16461]: [2-1] r (4e5fe8d4.404d) -- 2011-09-01 20:19:32.838 UTC>
 LOG:  incomplete startup packet

What's the deal? These are logs sent directly by the postgresql daemon.

Raw
db0_postgresql.conf
# -----------------------------
# PostgreSQL-9.0 configuration file
# -----------------------------
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# data location of postgresql server
data_directory = '/srv/pg/data'
# host-based authentication file
hba_file = '/etc/postgresql/9.0/main/pg_hba.conf'
# ident configuration file
ident_file = '/etc/postgresql/9.0/main/pg_ident.conf'
# If external_pid_file is not explicitly set, no extra PID file is written.
external_pid_file = '/var/run/postgresql/9.0-main.pid'
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*'
port = 5432
# total number of open connections per server instance
max_connections = 100
# Note: Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
superuser_reserved_connections = 3 # (change requires restart)
# When all else fails, connect to the server via a socket on the localhost.
unix_socket_directory = '/var/run/postgresql' # (change requires restart)
# - Security and Authentication -
# encrypt the communication channel between db and client?
ssl = false
# Always encrypt db user passwords stored in the database.
password_encryption = on
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 1919MB
temp_buffers = 77MB
work_mem = 384MB # duplicated memory use!
maintenance_work_mem = 1152MB
max_stack_depth = 7192kB
# - Asynchronous Behavior -
effective_io_concurrency = 4 # RAID 10
#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
fsync = true # turns forced synchronization on or off
synchronous_commit = on # immediate fsync at commit
wal_sync_method = fdatasync # like fsync, but lazy metadata writes; ok
full_page_writes = true # recover from partial page writes
wal_buffers = 8MB # memory for WAL data
# - Checkpoints -
checkpoint_segments = 16 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 5min # range 30s-1h
checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
checkpoint_warning = 30s # 0 disables
# - Archiving -
wal_level = hot_standby
archive_mode = on
archive_command = 'rsync -az /srv/pg/data/%p [email protected]:/srv/pg/archive/%f'
#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Cost Constants -
effective_cache_size = 5066MB
# - Other Planner Options -
default_statistics_target = 1000 # one-tenth max, somewhat conservative
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
log_destination = 'syslog'
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
silent_mode = off
# - When to Log -
client_min_messages = notice # messages sent to client
log_min_messages = warning # messages sent to log
log_error_verbosity = default # log verbosity
log_min_error_statement = error # errors sent to log
log_min_duration_statement = 1000 # log queries taking more than 1 second
# - What to Log -
log_line_prefix = 'r (%c) -- %m> ' # <remote host/port (session ID) -- timestamp>
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
autovacuum = on # Autovacuum!
autovacuum_vacuum_cost_delay = 75ms
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
# - Locale and Formatting -
datestyle = 'iso, mdy'
timezone = UTC
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8' # locale for system error messages
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
# default configuration for text search
default_text_search_config = 'pg_catalog.english'
Raw
db1_postgresql.conf
# -----------------------------
# PostgreSQL-9.0 configuration file
# -----------------------------
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# data location of postgresql server
data_directory = '/srv/pg/data'
# host-based authentication file
hba_file = '/etc/postgresql/9.0/main/pg_hba.conf'
# ident configuration file
ident_file = '/etc/postgresql/9.0/main/pg_ident.conf'
# If external_pid_file is not explicitly set, no extra PID file is written.
external_pid_file = '/var/run/postgresql/9.0-main.pid'
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*'
port = 5432
# total number of open connections per server instance
max_connections = 100
# Note: Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
superuser_reserved_connections = 3 # (change requires restart)
# When all else fails, connect to the server via a socket on the localhost.
unix_socket_directory = '/var/run/postgresql' # (change requires restart)
# - Security and Authentication -
# encrypt the communication channel between db and client?
ssl = false
# Always encrypt db user passwords stored in the database.
password_encryption = on
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 1919MB
temp_buffers = 77MB
work_mem = 384MB # duplicated memory use!
maintenance_work_mem = 1152MB
max_stack_depth = 7192kB
# - Asynchronous Behavior -
effective_io_concurrency = 4 # RAID 10
#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
fsync = true # turns forced synchronization on or off
synchronous_commit = on # immediate fsync at commit
wal_sync_method = fdatasync # like fsync, but lazy metadata writes; ok
full_page_writes = true # recover from partial page writes
wal_buffers = 8MB # memory for WAL data
# - Checkpoints -
checkpoint_segments = 16 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 5min # range 30s-1h
checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
checkpoint_warning = 30s # 0 disables
# - Archiving -
wal_level = hot_standby
archive_mode = on
archive_command = 'rsync -az /srv/pg/data/%p [email protected]:/srv/pg/archive/%f'
hot_standby = on
#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Cost Constants -
effective_cache_size = 5066MB
# - Other Planner Options -
default_statistics_target = 1000 # one-tenth max, somewhat conservative
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
log_destination = 'syslog'
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
silent_mode = off
# - When to Log -
client_min_messages = notice # messages sent to client
log_min_messages = warning # messages sent to log
log_error_verbosity = default # log verbosity
log_min_error_statement = error # errors sent to log
log_min_duration_statement = 1000 # log queries taking more than 1 second
# - What to Log -
log_line_prefix = 'r (%c) -- %m> ' # <remote host/port (session ID) -- timestamp>
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
autovacuum = on # Autovacuum!
autovacuum_vacuum_cost_delay = 75ms
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
# - Locale and Formatting -
datestyle = 'iso, mdy'
timezone = UTC
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8' # locale for system error messages
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
# default configuration for text search
default_text_search_config = 'pg_catalog.english'
Raw
db1_recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=db0.example.com port=5432 user=copyist password=scribe'
restore_command = 'cp /srv/pg/archive/%f %p'
trigger_file = '/srv/pg/trigger_file'
archive_cleanup_command = '/usr/lib/postgresql/9.0/bin/pg_archivecleanup /srv/pg/archive %r'
Raw
pg_hba.conf
# DO NOT DISABLE!
# Database administrative login by UNIX sockets
local all postgres trust
host all postgres 10.0.4.0/24 trust
# TYPE DATABASE USER CIDR-ADDRESS METHOD
local all all trust
host all all 10.0.0.0/16 password
host replication copyist 10.0.3.0/16 md5
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment