--------------------------------------------- Activar el caminos de la intalación de repmgr --------------------------------------------- export PATH=$PATH:/opt/PostgreSQL/9.1/bin:/usr/lib/postgresql/9.1/bin ----------------------------------------------------- El postgresql.conf debe estar configurado como sigue ----------------------------------------------------- listen_addresses='*' wal_level = 'hot_standby' archive_mode = on archive_command = 'cd .' # we can also use exit 0, anything that # just does nothing max_wal_senders = 10 wal_keep_segments = 5000 # 80 GB required on pg_xlog hot_standby = on ------------- Pre-requisitos de la instalación en Ubuntu------------ sudo apt-get install libxslt-dev libxml2-dev libpam-dev libedit-dev postgresql-server-dev-9.1 -------------------- Inicilizar Standby -------------------- --Nodo2: --------- # service postgresql-9.1 stop # cd $PGDATA # rm -rf * $ repmgr -D /opt/PostgreSQL/9.1/data/ -p 5432 -U postgres -R postgres --verbose standby clone gilbertoc -------------------------- Archivos de configuración -------------------------- Nodo1 repmgr.conf: ----------------- cluster=test node=1 conninfo='host=node1 user=repmgr dbname=pgbench' Nodo2 repmgr.conf: ------------------ cluster=test node=2 conninfo='host=node2 user=repmgr dbname=pgbench' Nodo2 recovery.conf: -------------------- standby_mode = 'on' primary_conninfo = 'host=node1 port=5432 user=repmgr' -------------------- Registrar los nodos -------------------- $ repmgr -f /opt/PostgreSQL/9.1/data/repmgr.conf --verbose master register $ repmgr -f /opt/PostgreSQL/9.1/data/repmgr.conf --verbose standby register ---------------------- Clonación de los nodos ---------------------- $ repmgr -D /opt/PostgreSQL/9.1/data/ -f /opt/PostgreSQL/9.1/data/repmgr.conf -d testreplica -p 5432 -U postgres -R postgres standby clone gilbertoc --force --verbose ************* Msg Error 1 ****************** root@server-desktop:/opt/PostgreSQL/9.1/data# repmgr -f /opt/PostgreSQL/9.1/data/repmgr.conf standby clone --force -h gilbertoc -p 5432 -U postgres -R admin --verbose Only command line parameters for the connection to the master are used when issuing a STANDBY CLONE command. The passed configuration file is neither required nor used for its node configuration portions Opening configuration file: /opt/PostgreSQL/9.1/data/repmgr.conf repmgr: directory "." exists but is not empty repmgr connecting to master database repmgr connected to master, checking its state repmgr needs parameter 'wal_keep_segments' to be set to 5000 or greater (see the '-w' option) *******************-- Solucion --- ******* Para este error debemos ir al postgresql.conf y cambiar el parametro wal_keep_segments: wal_keep_segments = 5000 sevice postgresql reload ************* Msg Error 1 Fin ****************** ************* Msg Error 2 ****************** $ repmgr -D /opt/PostgreSQL/9.1/data/ -f /opt/PostgreSQL/9.1/data/repmgr.conf -d testreplica -p 5432 -U postgres -R admin standby clone gilbertoc --force --verbose Only command line parameters for the connection to the master are used when issuing a STANDBY CLONE command. The passed configuration file is neither required nor used for its node configuration portions Opening configuration file: /opt/PostgreSQL/9.1/data/repmgr.conf repmgr: directory "/opt/PostgreSQL/9.1/data/" exists but is not empty repmgr connecting to master database repmgr connected to master, checking its state Succesfully connected to primary. Current installation size is 6073 MB Permission denied (publickey,password). Cannot connect to the remote host (gilbertoc) repmgr: Aborting, remote host gilbertoc is not reachable. *******************-- Solucion --- ******* Para este error debemos revisar la conexion via psql: psql -h IP-Host -d Bd-Replica -p 5432 -U postgres ************* Msg Error 2 Fin ****************** ************* Msg Error 3 ****************** 2013-07-17 08:42:50 CDT LOG: redo starts at 1/D6000020 2013-07-17 08:42:50 CDT LOG: consistent recovery state reached at 1/D60000C4 2013-07-17 08:42:50 CDT FATAL: the database system is starting up 2013-07-17 08:42:51 CDT FATAL: the database system is starting up 2013-07-17 08:42:52 CDT FATAL: the database system is starting up 2013-07-17 08:42:53 CDT FATAL: the database system is starting up 2013-07-17 08:42:53 CDT FATAL: the database system is starting up 2013-07-17 08:42:53 CDT FATAL: the database system is starting up 2013-07-17 08:42:54 CDT FATAL: the database system is starting up 2013-07-17 08:42:55 CDT FATAL: the database system is starting up 2013-07-17 08:42:56 CDT FATAL: the database system is starting up 2013-07-17 08:42:57 CDT FATAL: the database system is starting up *******************-- Solucion --- ******* --Editar el postgresql.conf: --------------------------- *-verificar los parametros: max_wal_senders = 10 hot_standby = on ************* Msg Error 3 Fin ****************** /*/*/*/*/*/*/*/*/*/ Msg Clonación normal */*/*/*/*/*/*/*/ $ repmgr -D /opt/PostgreSQL/9.1/data/ -f /opt/PostgreSQL/9.1/data/repmgr.conf -d testreplica -p 5432 -U postgres -R postgres standby clone gilbertoc --force --verbose Only command line parameters for the connection to the master are used when issuing a STANDBY CLONE command. The passed configuration file is neither required nor used for its node configuration portions Opening configuration file: /opt/PostgreSQL/9.1/data/repmgr.conf repmgr: directory "/opt/PostgreSQL/9.1/data/" exists but is not empty repmgr connecting to master database repmgr connected to master, checking its state Succesfully connected to primary. Current installation size is 6073 MB Starting backup... standby clone: master control file '/opt/PostgreSQL/9.1/data/global/pg_control' rsync command line: 'rsync --archive --checksum --compress --progress --rsh=ssh --delete postgres@gilbertoc:/opt/PostgreSQL/9.1/data/global/pg_control /opt/PostgreSQL/9.1/data//global/.' receiving incremental file list pg_control 8192 100% 7.81MB/s 0:00:00 (xfer#1, to-check=0/1) sent 102 bytes received 237 bytes 678.00 bytes/sec total size is 8192 speedup is 24.17 standby clone: master data directory '/opt/PostgreSQL/9.1/data' rsync command line: 'rsync --archive --checksum --compress --progress --rsh=ssh --delete --exclude=pg_xlog* --exclude=pg_control --exclude=*.pid postgres@gilbertoc:/opt/PostgreSQL/9.1/data/* /opt/PostgreSQL/9.1/data/' receiving incremental file list backup_label 201 100% 196.29kB/s 0:00:00 (xfer#1, to-check=2226/2229) pg_hba.conf 4319 100% 4.12MB/s 0:00:00 (xfer#2, to-check=2224/2229) pg_hba.conf~ 4251 100% 4.05MB/s 0:00:00 (xfer#3, to-check=2222/2229) postgresql.conf~ 19264 100% 18.37MB/s 0:00:00 (xfer#4, to-check=2218/2229) repmgr.conf 65 100% 7.93kB/s 0:00:00 (xfer#5, to-check=2216/2229) base/11996/ base/11996/pg_internal.init 91648 100% 3.97MB/s 0:00:00 (xfer#6, to-check=1512/2229) base/22499/ deleting global/pgstat.stat base/22499/pg_internal.init 91648 100% 87.40MB/s 0:00:00 (xfer#7, to-check=426/2894) global/ global/pg_internal.init 10500 100% 71.21kB/s 0:00:00 (xfer#8, to-check=147/2894) deleting pg_log/postgresql-2013-07-03_092902.log deleting pg_log/postgresql-2013-07-03_085039.log pg_log/ pg_log/postgresql-2013-07-03_080440.log 1366 100% 1.31kB/s 0:00:01 (xfer#9, to-check=10/2894) pg_log/postgresql-2013-07-03_093346.log 495 100% 0.47kB/s 0:00:01 (xfer#10, to-check=9/2894) pg_log/startup.log 24632 100% 23.51kB/s 0:00:01 (xfer#11, to-check=8/2894) pg_notify/ pg_stat_tmp/ pg_stat_tmp/pgstat.stat 14182 100% 242.98kB/s 0:00:00 (xfer#12, to-check=1/2894) sent 1696 bytes received 112597 bytes 735.00 bytes/sec total size is 6383772807 speedup is 55854.45 standby clone: master config file '/opt/PostgreSQL/9.1/data/postgresql.conf' rsync command line: 'rsync --archive --checksum --compress --progress --rsh=ssh --delete postgres@gilbertoc:/opt/PostgreSQL/9.1/data/postgresql.conf /opt/PostgreSQL/9.1/data//.' receiving incremental file list sent 11 bytes received 82 bytes 62.00 bytes/sec total size is 19264 speedup is 207.14 standby clone: master hba file '/opt/PostgreSQL/9.1/data/pg_hba.conf' rsync command line: 'rsync --archive --checksum --compress --progress --rsh=ssh --delete postgres@gilbertoc:/opt/PostgreSQL/9.1/data/pg_hba.conf /opt/PostgreSQL/9.1/data//.' receiving incremental file list sent 11 bytes received 78 bytes 178.00 bytes/sec total size is 4319 speedup is 48.53 standby clone: master ident file '/opt/PostgreSQL/9.1/data/pg_ident.conf' rsync command line: 'rsync --archive --checksum --compress --progress --rsh=ssh --delete postgres@gilbertoc:/opt/PostgreSQL/9.1/data/pg_ident.conf /opt/PostgreSQL/9.1/data//.' receiving incremental file list sent 11 bytes received 80 bytes 60.67 bytes/sec total size is 1636 speedup is 17.98 Finishing backup... NOTICE: pg_stop_backup complete, all required WAL segments have been archived repmgr requires primary to keep WAL files 00000001000000010000009A until at least 00000001000000010000009A repmgr standby clone complete /*/*/*/*/*/*/*/*/*/*//*/*/*/*/*/*/*/*/*/*/*/*/*//*/*///*/*//// $ repmgr -f /opt/PostgreSQL/9.1/data/repmgr.conf --verbose master register $ repmgr -f /opt/PostgreSQL/9.1/data/repmgr.conf --verbose standby register ----------------- Monitoreo y test ----------------- postgres@node2 $ psql -x -d testreplica -c "SELECT * FROM repmgr_test.repl_monitor" -------------------------------------------------- Promocición del nodo Standby a Primario (failover) -------------------------------------------------- repmgr -f /opt/PostgreSQL/9.1/data/repmgr.conf --verbose standby promote -------------------------- Llevar Primario a Standby -------------------------- repmgr -D /opt/PostgreSQL/9.1/data/ -f /opt/PostgreSQL/9.1/data/repmgr.conf -d testreplica -p 5432 -U postgres -R postgres standby clone server --force --verbose Ajustar el recovery.done ------------------------- rn recovery.done recovery.conf --recovery.conf: ---------------- standby_mode = 'on' primary_conninfo = 'host=server port=5432 user=postgres' --repmgr.conf: -------------- cluster=test node=2 conninfo='host=192.168.207.54 dbname=repmgr' -------------------------------------------------------------------------------- Restauración de las funciones originales (nodo1 Master y llevar nodo2 a standby){failback} --------------------------------------------------------------------------------- --Detener nodo2 --------------- service postgresql stop --Agregar recovery.conf ------------------------ standby_mode = 'on' primary_conninfo = 'host=192.168.207.54 port=5432 user=postgres' --Promover nodo1 ----------------- repmgr -f /opt/PostgreSQL/9.1/data/repmgr.conf --verbose standby promote --Levantar nodo2 ----------------- service postgresql start --Actualizar nodo1 desde nodo2 ------------------------------- repmgr -D /opt/PostgreSQL/9.1/data/ -f /opt/PostgreSQL/9.1/data/repmgr.conf -d testreplica -p 5432 -U postgres -R postgres standby clone server --force --verbose