822382 - Replication in prod socorro busted

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Selena Deckelmann :selenamarie :selena]

breakpad=# select * from pg_stat_replication; 
  pid  | usesysid |  usename   | application_name | client_addr | client_hostname | client_port |         backend_start
         |   state   | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state 
-------+----------+------------+------------------+-------------+-----------------+-------------+----------------------
---------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
 24753 |    28148 | replicator | walreceiver      | 10.8.70.101 |                 |       49498 | 2012-12-15 19:15:33.0
68765+00 | streaming | EE0/8171EC20  | EE0/8171EC20   | EE0/8171EC20   | EE0/8171EA78    |             0 | async
(1 row)


Communicating with :mpressman about redoing replication

Comment 1

[Selena Deckelmann :selenamarie :selena]

2012-12-18 21:38:06.510 GMT,,,29723,,50d0e23e.741b,1,,2012-12-18 21:38:06 GMT,,0,LOG
,00000,"streaming replication successfully connected to primary",,,,,,,,,""


Will file follow up bugs for making this process a bit more automated in the future.

Comment 2

[Selena Deckelmann :selenamarie :selena]

Notes from etherpad for posterity: 

# Script to manage creating a new base backup & replica in prod

    on master01: puppetctl disable -m "changing WAL config temporarily for a base backup" -time 13:00

    DISABLE THE CRON

/etc/cron.d/postgres_manage_wal_retries

    Changed this setting in postgresql.conf:

# 2012-11-18 selenamarie set this to bypass wal_archive
archive_command = 'scp "%p" postgres@10.8.70.101:/var/lib/pgsql/wal_archive/%f < /dev/null'

    Sending a SIGHUP to postgres on master01 (does not restart the server, just resets the archive_command)

    on master in pg: select pg_start_backup('backup', true);

on slave: puppetctl disable -m "redoing replica for base backup" -time 13:00

    on slave: stop pgbouncer-processor, pgboucer-web, postgresql-9.2

    on slave: delete data directory

    on slave as root: cd  /pgdata/9.2

    on slave as root: nc -l 1237 | tar -xf -

    on master as root: cd /pgdata/9.2

    on master as root: tar --exclude=pg_xlog -cvf - data | nc -s 10.8.70.100 10.8.70.101 1237

    on slave: mkdir -p /pgdata/9.2/data/pg_xlog

    on slave: `make sure configs are correct, there is a backup in /pgdata/9.2/conf

on which machine? master02 rm -f data/*.conf # a little safer :D
copy configs in conf/*.* to data/
-Stop backup mode and turn eveything back on-
on master01: select pg_stop_backup();
on slave: start postgresql-9.2, pgbouncer-web, pgbouncer-processor
ensure replication is running correctly (look in logs for replication notice)

DONE 1:49pm PT

Comment 3

[Sheeri Cabral [:sheeri]]

Is comment 2 documented anywhere that we might find it again? socorro docs or mana docs?