From 2e6372511f6d9b262567ee59fdc0a29ad59c609d Mon Sep 17 00:00:00 2001 From: Ahmed Date: Tue, 14 Jan 2014 00:16:35 -0600 Subject: [PATCH] Initialize project --- failover.sh | 15 ++ online-recovery.sh | 145 ++++++++++ pgpool.conf | 444 +++++++++++++++++++++++++++++++ postgresql.conf.master | 556 +++++++++++++++++++++++++++++++++++++++ postgresql.conf.slave | 556 +++++++++++++++++++++++++++++++++++++++ recovery.conf | 12 + streaming-replication.sh | 173 ++++++++++++ 7 files changed, 1901 insertions(+) create mode 100755 failover.sh create mode 100755 online-recovery.sh create mode 100644 pgpool.conf create mode 100644 postgresql.conf.master create mode 100644 postgresql.conf.slave create mode 100644 recovery.conf create mode 100755 streaming-replication.sh diff --git a/failover.sh b/failover.sh new file mode 100755 index 0000000..e2c6d52 --- /dev/null +++ b/failover.sh @@ -0,0 +1,15 @@ +#!/bin/bash +failed_node=$1 +new_master=$2 +trigger_file=$4 +old_primary=$3 +# if standby goes down. +if [ $failed_node != $old_primary ]; then + echo "[INFO] Slave node is down. Failover not triggred !"; + exit 0; +fi +# Create the trigger file if primary node goes down. +echo "[INFO] Master node is down. Performing failover..." +ssh -i /var/lib/postgresql/.ssh/id_rsa postgres@$new_master "touch $trigger_file" + +exit 0; diff --git a/online-recovery.sh b/online-recovery.sh new file mode 100755 index 0000000..3e2aada --- /dev/null +++ b/online-recovery.sh @@ -0,0 +1,145 @@ +#!/bin/bash + +#Postgres data directory +postgres_datadir='/var/lib/postgresql/9.1/main' +#Postgres configuration directory +postgres_configdir='/etc/postgresql/9.1/main' +#Postgres user ssh key +postgres_user_key='/var/lib/postgresql/.ssh/id_rsa' +#Pgpool configuration directory +pgpool_configdir='/etc/pgpool2' + +if [ -f '/tmp/postgres_master' ] +then + #Get current postgres master id + current_master_id=$(cat /tmp/postgres_master); +else + echo "[ERROR] /tmp/postgres_master not found !"; + exit 0; +fi + +#Get postgres master name +current_master_name=$(pcp_node_info 10 localhost 9898 postgres postgres $current_master_id | cut -d' ' -f1) +#Get postgres slave id +[ $current_master_id == 0 ] && current_slave_id=1 || current_slave_id=0 +#Get postgres slave name +current_slave_name=$(pcp_node_info 10 localhost 9898 postgres postgres $current_slave_id | cut -d' ' -f1) + +#Test if pgpool is running +CheckIfPgpoolIsRunning () { + #Send signal 0 to pgpool to check if it's running + if ! killall -0 pgpool; then echo "[ERROR] Pgpool is not running !"; exit 1; fi; +} + +CheckPostgres () { + +} + +AttachNodeToPgpool () { + #pcp_attach_node is a command that permit to attach a specific postgres server (identified by 6th parameter) to pgpool. + #pcp_attach_node dont return a good error code when it fails so here if I catch "BackendError" message in stderr I presume + #that attachment failed. + #TODO:find a condition to break the folowing loop if attachment fails. + while [ "`pcp_attach_node 10 localhost 9898 postgres postgres $1`" == "BackendError" ] + do + pcp_attach_node 10 localhost 9898 postgres postgres $1; + #This sleep is recommanded to avoid stressing pgpool in this infinite loop. + sleep 5; + done +} + +#Whether the slave node is down, start it and attach it to pgpool's backend pool. +ReattachDegeneratedSlave () { + #Reboot slave node + echo "[INFO] Slave node '$current_slave_name' is down. Performing postgres server reboot..." + #Remote postgres reboot via ssh + ssh -i $postgres_user_key postgres@$current_slave_name "/etc/init.d/postgresql restart" + #Test if postgres is running + status=$(ssh -i $postgres_user_key postgres@$current_slave_name "if ! killall -0 postgres; then echo 'error'; else echo 'running'; fi;") + if [ $status == "error" ] + then + echo "[ERROR] Postgres slave still down !"; + exit 0; + else + echo "[OK] Slave node successfully started."; + fi + + #Do 'slave online recovery' to force slave sync if it has incoherent data relatevely to master. + #echo "[INFO] Starting online recovery for slave '$current_slave_name' ..." + #ssh -i /var/lib/postgresql/.ssh/id_rsa postgres@$current_slave_name "bash /var/lib/postgresql/streaming-replication.sh $current_master_name" + #Atttach slave (even master) to pgpool's backends pool + #Reattach the master node if you have performed an online recovery for slave node and not juste a simple reboot. + #Attempting to reatach master to pgpool's backend pool + #echo "[INFO] Attaching master node '$current_master_name' ..." + #AttachNodeToPgpool "$current_master_id" + #echo "[OK] Master node '$current_master_name' has been successfully reattached to pgpool." + #Attempting to reattach slave to pgpool's backend pool + echo "[INFO] Attaching slave node '$current_slave_name'..." + AttachNodeToPgpool "$current_slave_id" + echo "[OK] Slave node '$current_slave_name' has been successfully reattached to pgpool." +} + + +#Whether the master is down do the folowing operations : +SwitchOldMasterToSlave () { + + new_master_name=$current_slave_name + new_master_id=$current_slave_id + new_slave_name=$current_master_name + new_slave_id=$current_master_id + #Setup old master config to slave mode + echo "[INFO] Setting up configuration for the new slave node '$new_slave_name'..." + ssh -i $postgres_user_key postgres@$new_slave_name "/etc/init.d/postgresql stop" + ssh -i $postgres_user_key postgres@$new_slave_name "cp -p $postgres_configdir/postgresql.conf.slave $postgres_configdir/postgresql.conf" + ssh -i $postgres_user_key postgres@$new_slave_name "[ -f $postgres_datadir/recovery.done ] && mv $postgres_datadir/recovery.done $postgres_datadir/recovery.conf" + # Switch slave to new master + echo "[INFO] Setting up configuration for the new master '$new_master_name'..." + ssh -i $postgres_user_key postgres@$new_master_name "[ -f /tmp/trigger_file ] && rm /tmp/trigger_file" + ssh -i $postgres_user_key postgres@$new_master_name "[ -f $postgres_datadir/recovery.conf ] && mv $postgres_datadir/recovery.conf $postgres_datadir/recovery.done" + ssh -i $postgres_user_key postgres@$new_master_name "cp -p $postgres_configdir/postgresql.conf.master $postgres_configdir/postgresql.conf" + echo "[INFO] Restarting new master..." + ssh -i $postgres_user_key postgres@$new_master_name "/etc/init.d/postgresql restart" + status=$(ssh -i $postgres_user_key postgres@$new_master_name "if ! killall -0 postgres; then echo 'error'; else echo 'running'; fi;") + if [ $status == "error" ] + then + echo "[ERROR] New postgres master not running !"; + exit 0; + else + echo "[OK] New master started."; + fi + # Start new slave/master with online recovery + echo "[INFO] Performing online slave recovery..." + ssh -i $postgres_user_key postgres@$new_slave_name "bash /var/lib/postgresql/streaming-replication.sh $new_master_name" + echo "[OK] Online recovery completed." + + #Write changes to pgpool.conf file to keep the same current master and slave nodes even after pgpool reboot. + sed -i "s/^backend_hostname0.*/backend_hostname0='$new_master_name'/" $pgpool_configdir/pgpool.conf + sed -i "s/^backend_hostname1.*/backend_hostname1='$new_slave_name'/" $pgpool_configdir/pgpool.conf + echo "[OK] Pgpool configuration file updated." + + #Attach new master to pgpool + echo "[INFO] Attaching new master node '$new_master_name'..." + AttachNodeToPgpool "$new_master_id" + echo "[OK] New master node '$new_master_name' has been successfully reattached to pgpool." + + #Attach new slave to pgpool + echo "[INFO] Attaching new slave node '$new_slave_name'..." + AttachNodeToPgpool "$new_slave_id" + echo "[OK] New slave node '$new_slave_name' has been successfully reattached to pgpool." + +} + +CheckIfPgpoolIsRunning + +#Get master/slave state +current_master_state=$(pcp_node_info 10 localhost 9898 postgres postgres $current_master_id | cut -d' ' -f3) +current_slave_state=$(pcp_node_info 10 localhost 9898 postgres postgres $current_slave_id | cut -d' ' -f3) + +# state 1 => postgres server is attached but still not receiving connections +# state 2 => postgres server is attached and managing clients connections +# state 3 => postgres server is detached and probably is down. + +#If slave is down and master is up then perform an online slave backup. +[ $current_slave_state == 3 ] && ([ $current_master_state == 1 ] || [ $current_master_state == 2 ]) && ReattachDegeneratedSlave +#If master is down then switch roles between failed master(new server) and the slave(new master). +[ $current_master_state == 3 ] && SwitchOldMasterToSlave diff --git a/pgpool.conf b/pgpool.conf new file mode 100644 index 0000000..f0ca8ec --- /dev/null +++ b/pgpool.conf @@ -0,0 +1,444 @@ +# ---------------------------- +# pgPool-II configuration file +# ---------------------------- +# +# This file consists of lines of the form: +# +# name = value +# +# Whitespace may be used. Comments are introduced with "#" anywhere on a line. +# The complete list of parameter names and allowed values can be found in the +# pgPool-II documentation. +# +# This file is read on server startup and when the server receives a SIGHUP +# signal. If you edit the file on a running system, you have to SIGHUP the +# server for the changes to take effect, or use "pgpool reload". Some +# parameters, which are marked below, require a server shutdown and restart to +# take effect. +# + + +#------------------------------------------------------------------------------ +# CONNECTIONS +#------------------------------------------------------------------------------ + +# - pgpool Connection Settings - + +listen_addresses = '*' + # Host name or IP address to listen on: + # '*' for all, '' for no TCP/IP connections + # (change requires restart) +port = 5431 + # Port number + # (change requires restart) +socket_dir = '/var/run/postgresql' + # Unix domain socket path + # The Debian package defaults to + # /var/run/postgresql + # (change requires restart) + + +# - pgpool Communication Manager Connection Settings - + +pcp_port = 9898 + # Port number for pcp + # (change requires restart) +pcp_socket_dir = '/var/run/postgresql' + # Unix domain socket path for pcp + # The Debian package defaults to + # /var/run/postgresql + # (change requires restart) + +# - Backend Connection Settings - + +#backend_hostname0 = 'host1' + # Host name or IP address to connect to for backend 0 +#backend_port0 = 5432 + # Port number for backend 0 +#backend_weight0 = 1 + # Weight for backend 0 (only in load balancing mode) +#backend_data_directory0 = '/data' + # Data directory for backend 0 +#backend_flag0 = 'ALLOW_TO_FAILOVER' + # Controls various backend behavior + # ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER +backend_hostname0='master.foo.bar' +backend_port0 = 5432 +backend_weight0 = 1 +backend_data_directory0 = '/var/lib/postgres/9.1/main/' +backend_flag0 = 'ALLOW_TO_FAILOVER' + +backend_hostname1='slave.foo.bar' +backend_port1 = 5432 +backend_weight1 = 1 +backend_data_directory1 = '/var/lib/postgres/9.1/main/' +backend_flag1 = 'ALLOW_TO_FAILOVER' + + +# - Authentication - + +enable_pool_hba = on + # Use pool_hba.conf for client authentication +authentication_timeout = 60 + # Delay in seconds to complete client authentication + # 0 means no timeout. + +# - SSL Connections - + +ssl = off + # Enable SSL support + # (change requires restart) +#ssl_key = './server.key' + # Path to the SSL private key file + # (change requires restart) +#ssl_cert = './server.cert' + # Path to the SSL public certificate file + # (change requires restart) +#ssl_ca_cert = '' + # Path to a single PEM format file + # containing CA root certificate(s) + # (change requires restart) +#ssl_ca_cert_dir = '' + # Directory containing CA root certificate(s) + # (change requires restart) + + +#------------------------------------------------------------------------------ +# POOLS +#------------------------------------------------------------------------------ + +# - Pool size - + +num_init_children = 32 + # Number of pools + # (change requires restart) +max_pool = 4 + # Number of connections per pool + # (change requires restart) + +# - Life time - + +child_life_time = 300 + # Pool exits after being idle for this many seconds +child_max_connections = 100 + # Pool exits after receiving that many connections + # 0 means no exit +connection_life_time = 0 + # Connection to backend closes after being idle for this many seconds + # 0 means no close +client_idle_limit = 120 + # Client is disconnected after being idle for that many seconds + # (even inside an explicit transactions!) + # 0 means no disconnection + + +#------------------------------------------------------------------------------ +# LOGS +#------------------------------------------------------------------------------ + +# - Where to log - + +log_destination = 'stderr' + # Where to log + # Valid values are combinations of stderr, + # and syslog. Default to stderr. + +# - What to log - + +print_timestamp = on + # Print timestamp on each line + # (change requires restart) + +log_connections = on + # Log connections +log_hostname = off + # Hostname will be shown in ps status + # and in logs if connections are logged +log_statement = off + # Log all statements +log_per_node_statement = off + # Log all statements + # with node and backend informations +log_standby_delay = 'none' + # Log standby delay + # Valid values are combinations of always, + # if_over_threshold, none + +# - Syslog specific - + +syslog_facility = 'LOCAL0' + # Syslog local facility. Default to LOCAL0 +syslog_ident = 'pgpool' + # Syslog program identification string + # Default to 'pgpool' + +# - Debug - + +debug_level = 0 + # Debug message verbosity level + # 0 means no message, 1 or more mean verbose + + +#------------------------------------------------------------------------------ +# FILE LOCATIONS +#------------------------------------------------------------------------------ + +pid_file_name = '/var/run/postgresql/pgpool.pid' + # PID file name + # (change requires restart) +logdir = '/var/log/postgresql' + # Directory of pgPool status file + # (change requires restart) + + +#------------------------------------------------------------------------------ +# CONNECTION POOLING +#------------------------------------------------------------------------------ + +connection_cache = on + # Activate connection pools + # (change requires restart) + + # Semicolon separated list of queries + # to be issued at the end of a session + # The default is for 8.3 and later +reset_query_list = 'ABORT; DISCARD ALL' + # The following one is for 8.2 and before +#reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT' + + +#------------------------------------------------------------------------------ +# REPLICATION MODE +#------------------------------------------------------------------------------ + +replication_mode = off + # Activate replication mode + # (change requires restart) +replicate_select = off + # Replicate SELECT statements + # when in replication or parallel mode + # replicate_select is higher priority than + # load_balance_mode. + +insert_lock = off + # Automatically locks a dummy row or a table + # with INSERT statements to keep SERIAL data + # consistency + # Without SERIAL, no lock will be issued +lobj_lock_table = '' + # When rewriting lo_creat command in + # replication mode, specify table name to + # lock + +# - Degenerate handling - + +replication_stop_on_mismatch = off + # On disagreement with the packet kind + # sent from backend, degenerate the node + # which is most likely "minority" + # If off, just force to exit this session + +failover_if_affected_tuples_mismatch = off + # On disagreement with the number of affected + # tuples in UPDATE/DELETE queries, then + # degenerate the node which is most likely + # "minority". + # If off, just abort the transaction to + # keep the consistency + + +#------------------------------------------------------------------------------ +# LOAD BALANCING MODE +#------------------------------------------------------------------------------ + +load_balance_mode = on + # Activate load balancing mode + # (change requires restart) +ignore_leading_white_space = on + # Ignore leading white spaces of each query +white_function_list = '' + # Comma separated list of function names + # that don't write to database + # Regexp are accepted +black_function_list = 'currval,lastval,nextval,setval,sp_.*' + # Comma separated list of function names + # that write to database + # Regexp are accepted + + +#------------------------------------------------------------------------------ +# MASTER/SLAVE MODE +#------------------------------------------------------------------------------ + +master_slave_mode = on + # Activate master/slave mode + # (change requires restart) +master_slave_sub_mode = 'stream' + # Master/slave sub mode + # Valid values are combinations slony or + # stream. Default is slony. + # (change requires restart) + +# - Streaming - + +sr_check_period = 0 + # Streaming replication check period + # Disabled (0) by default +sr_check_user = 'postgres' + # Streaming replication check user + # This is necessary even if you disable + # streaming replication delay check with + # sr_check_period = 0 +sr_check_password = 'nopass' + # Password for streaming replication check user +delay_threshold = 0 + # Threshold before not dispatching query to standby node + # Unit is in bytes + # Disabled (0) by default + +# - Special commands - + +follow_master_command = '' + # Executes this command after master failover + # Special values: + # %d = node id + # %h = host name + # %p = port number + # %D = database cluster path + # %m = new master node id + # %H = hostname of the new master node + # %M = old master node id + # %P = old primary node id + # %% = '%' character + + +#------------------------------------------------------------------------------ +# PARALLEL MODE AND QUERY CACHE +#------------------------------------------------------------------------------ + +parallel_mode = off + # Activates parallel query mode + # (change requires restart) +enable_query_cache = off + # Activates query cache + # (change requires restart) + +pgpool2_hostname = '' + # Set pgpool2 hostname + # (change requires restart) + +# - System DB info - + +system_db_hostname = 'localhost' + # (change requires restart) +system_db_port = 5432 + # (change requires restart) +system_db_dbname = 'pgpool' + # (change requires restart) +system_db_schema = 'pgpool_catalog' + # (change requires restart) +system_db_user = 'pgpool' + # (change requires restart) +system_db_password = '' + # (change requires restart) + + +#------------------------------------------------------------------------------ +# HEALTH CHECK +#------------------------------------------------------------------------------ + +health_check_period = 30 + # Health check period + # Disabled (0) by default +health_check_timeout = 20 + # Health check timeout + # 0 means no timeout +health_check_user = 'postgres' + # Health check user +health_check_password = '' + # This parameter is not yet implemented. + # Password for health check user + + +#------------------------------------------------------------------------------ +# FAILOVER AND FAILBACK +#------------------------------------------------------------------------------ + +failover_command = '/usr/local/bin/failover.sh %d %H %P /tmp/trigger_file' + # Executes this command at failover + # Special values: + # %d = failed node id + # %h = failed host name + # %p = port number + # %D = database cluster path + # %m = new master node id + # %H = hostname of the new master node + # %M = old master node id + # %P = old primary node id + # %% = '%' character +failback_command = '' + # Executes this command at failback. + # Special values: + # %d = node id + # %h = host name + # %p = port number + # %D = database cluster path + # %m = new master node id + # %H = hostname of the new master node + # %M = old master node id + # %P = old primary node id + # %% = '%' character + +fail_over_on_backend_error = on + # Initiates failover when writing to the + # backend communication socket fails + # This is the same behaviour of pgpool-II + # 2.2.x and previous releases + # If set to off, pgpool will report an + # error and disconnect the session. + + +#------------------------------------------------------------------------------ +# ONLINE RECOVERY +#------------------------------------------------------------------------------ + +recovery_user = 'postgres' + # Online recovery user +recovery_password = 'nopass' + # Online recovery password +#recovery_1st_stage_command = 'basebackup.sh' + # Executes a command in first stage +recovery_2nd_stage_command = '' + # Executes a command in second stage +recovery_timeout = 30 + # Timeout in seconds to wait for the + # recovering node's postmaster to start up + # 0 means no wait +client_idle_limit_in_recovery = 0 + # Client is disconnected after being idle + # for that many seconds in the second stage + # of online recovery + # 0 means no disconnection + # -1 means immediate disconnection + + +#------------------------------------------------------------------------------ +# OTHERS +#------------------------------------------------------------------------------ + +relcache_expire = 0 + # Life time of relation cache in seconds. + # 0 means no cache expiration(the default). + # The relation cache is used for cache the + # query result against PostgreSQL system + # catalog to obtain various information + # including table structures or if it's a + # temporary table or not. The cache is + # maintained in a pgpool child local memory + # and being kept as long as it survives. + # If someone modify the table by using + # ALTER TABLE or some such, the relcache is + # not consistent anymore. + # For this purpose, cache_expiration + # controls the life time of the cache. + diff --git a/postgresql.conf.master b/postgresql.conf.master new file mode 100644 index 0000000..d254b3c --- /dev/null +++ b/postgresql.conf.master @@ -0,0 +1,556 @@ +# ----------------------------- +# PostgreSQL configuration file +# ----------------------------- +# +# This file consists of lines of the form: +# +# name = value +# +# (The "=" is optional.) Whitespace may be used. Comments are introduced with +# "#" anywhere on a line. The complete list of parameter names and allowed +# values can be found in the PostgreSQL documentation. +# +# The commented-out settings shown in this file represent the default values. +# Re-commenting a setting is NOT sufficient to revert it to the default value; +# you need to reload the server. +# +# This file is read on server startup and when the server receives a SIGHUP +# signal. If you edit the file on a running system, you have to SIGHUP the +# server for the changes to take effect, or use "pg_ctl reload". Some +# parameters, which are marked below, require a server shutdown and restart to +# take effect. +# +# Any parameter can also be given as a command-line option to the server, e.g., +# "postgres -c log_connections=on". Some parameters can be changed at run time +# with the "SET" SQL command. +# +# Memory units: kB = kilobytes Time units: ms = milliseconds +# MB = megabytes s = seconds +# GB = gigabytes min = minutes +# h = hours +# d = days + + +#------------------------------------------------------------------------------ +# FILE LOCATIONS +#------------------------------------------------------------------------------ + +# The default values of these variables are driven from the -D command-line +# option or PGDATA environment variable, represented here as ConfigDir. + +data_directory = '/var/lib/postgresql/9.1/main' # use data in another directory + # (change requires restart) +hba_file = '/etc/postgresql/9.1/main/pg_hba.conf' # host-based authentication file + # (change requires restart) +ident_file = '/etc/postgresql/9.1/main/pg_ident.conf' # ident configuration file + # (change requires restart) + +# If external_pid_file is not explicitly set, no extra PID file is written. +external_pid_file = '/var/run/postgresql/9.1-main.pid' # write an extra PID file + # (change requires restart) + + +#------------------------------------------------------------------------------ +# CONNECTIONS AND AUTHENTICATION +#------------------------------------------------------------------------------ + +# - Connection Settings - + +listen_addresses = '*' # what IP address(es) to listen on; + # comma-separated list of addresses; + # defaults to 'localhost', '*' = all + # (change requires restart) +port = 5432 # (change requires restart) +max_connections = 1000 # (change requires restart) +# 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) +unix_socket_directory = '/var/run/postgresql' # (change requires restart) +#unix_socket_group = '' # (change requires restart) +#unix_socket_permissions = 0777 # begin with 0 to use octal notation + # (change requires restart) +#bonjour = off # advertise server via Bonjour + # (change requires restart) +#bonjour_name = '' # defaults to the computer name + # (change requires restart) + +# - Security and Authentication - + +#authentication_timeout = 1min # 1s-600s +ssl = true # (change requires restart) +#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers + # (change requires restart) +#ssl_renegotiation_limit = 512MB # amount of data between renegotiations +#password_encryption = on +#db_user_namespace = off + +# Kerberos and GSSAPI +#krb_server_keyfile = '' +#krb_srvname = 'postgres' # (Kerberos only) +#krb_caseins_users = off + +# - TCP Keepalives - +# see "man 7 tcp" for details + +#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds; + # 0 selects the system default +#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds; + # 0 selects the system default +#tcp_keepalives_count = 0 # TCP_KEEPCNT; + # 0 selects the system default + + +#------------------------------------------------------------------------------ +# RESOURCE USAGE (except WAL) +#------------------------------------------------------------------------------ + +# - Memory - + +shared_buffers = 24MB # min 128kB + # (change requires restart) +#temp_buffers = 8MB # min 800kB +#max_prepared_transactions = 0 # zero disables the feature + # (change requires restart) +# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory +# per transaction slot, plus lock space (see max_locks_per_transaction). +# It is not advisable to set max_prepared_transactions nonzero unless you +# actively intend to use prepared transactions. +#work_mem = 1MB # min 64kB +#maintenance_work_mem = 16MB # min 1MB +#max_stack_depth = 2MB # min 100kB + +# - Kernel Resource Usage - + +#max_files_per_process = 1000 # min 25 + # (change requires restart) +#shared_preload_libraries = '' # (change requires restart) + +# - Cost-Based Vacuum Delay - + +#vacuum_cost_delay = 0ms # 0-100 milliseconds +#vacuum_cost_page_hit = 1 # 0-10000 credits +#vacuum_cost_page_miss = 10 # 0-10000 credits +#vacuum_cost_page_dirty = 20 # 0-10000 credits +#vacuum_cost_limit = 200 # 1-10000 credits + +# - Background Writer - + +#bgwriter_delay = 200ms # 10-10000ms between rounds +#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round +#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers scanned/round + +# - Asynchronous Behavior - + +#effective_io_concurrency = 1 # 1-1000. 0 disables prefetching + + +#------------------------------------------------------------------------------ +# WRITE AHEAD LOG +#------------------------------------------------------------------------------ + +# - Settings - + +wal_level = hot_standby # minimal, archive, or hot_standby + # (change requires restart) +#fsync = on # turns forced synchronization on or off +#synchronous_commit = on # synchronization level; on, off, or local +#wal_sync_method = fsync # the default is the first option + # supported by the operating system: + # open_datasync + # fdatasync (default on Linux) + # fsync + # fsync_writethrough + # open_sync +#full_page_writes = on # recover from partial page writes +#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers + # (change requires restart) +#wal_writer_delay = 200ms # 1-10000 milliseconds + +#commit_delay = 0 # range 0-100000, in microseconds +#commit_siblings = 5 # range 1-1000 + +# - Checkpoints - + +#checkpoint_segments = 3 # 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 - + +archive_mode = on # allows archiving to be done + # (change requires restart) +archive_command = 'cp -i "%p" /var/lib/postgresql/9.1/archive/"%f" 0 logs only + # statements running at least this number + # of milliseconds + + +# - What to Log - + +#debug_print_parse = off +#debug_print_rewritten = off +#debug_print_plan = off +#debug_pretty_print = on +#log_checkpoints = off +#log_connections = off +#log_disconnections = off +#log_duration = off +#log_error_verbosity = default # terse, default, or verbose messages +#log_hostname = off +log_line_prefix = '%t ' # special values: + # %a = application name + # %u = user name + # %d = database name + # %r = remote host and port + # %h = remote host + # %p = process ID + # %t = timestamp without milliseconds + # %m = timestamp with milliseconds + # %i = command tag + # %e = SQL state + # %c = session ID + # %l = session line number + # %s = session start timestamp + # %v = virtual transaction ID + # %x = transaction ID (0 if none) + # %q = stop here in non-session + # processes + # %% = '%' + # e.g. '<%u%%%d> ' +#log_lock_waits = off # log lock waits >= deadlock_timeout +#log_statement = 'none' # none, ddl, mod, all +#log_temp_files = -1 # log temporary files equal or larger + # than the specified size in kilobytes; + # -1 disables, 0 logs all temp files +#log_timezone = '(defaults to server environment setting)' + + +#------------------------------------------------------------------------------ +# RUNTIME STATISTICS +#------------------------------------------------------------------------------ + +# - Query/Index Statistics Collector - + +#track_activities = on +#track_counts = on +#track_functions = none # none, pl, all +#track_activity_query_size = 1024 # (change requires restart) +#update_process_title = on +#stats_temp_directory = 'pg_stat_tmp' + + +# - Statistics Monitoring - + +#log_parser_stats = off +#log_planner_stats = off +#log_executor_stats = off +#log_statement_stats = off + + +#------------------------------------------------------------------------------ +# AUTOVACUUM PARAMETERS +#------------------------------------------------------------------------------ + +#autovacuum = on # Enable autovacuum subprocess? 'on' + # requires track_counts to also be on. +#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and + # their durations, > 0 logs only + # actions running at least this number + # of milliseconds. +#autovacuum_max_workers = 3 # max number of autovacuum subprocesses + # (change requires restart) +#autovacuum_naptime = 1min # time between autovacuum runs +#autovacuum_vacuum_threshold = 50 # min number of row updates before + # vacuum +#autovacuum_analyze_threshold = 50 # min number of row updates before + # analyze +#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum +#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze +#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum + # (change requires restart) +#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for + # autovacuum, in milliseconds; + # -1 means use vacuum_cost_delay +#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for + # autovacuum, -1 means use + # vacuum_cost_limit + + +#------------------------------------------------------------------------------ +# CLIENT CONNECTION DEFAULTS +#------------------------------------------------------------------------------ + +# - Statement Behavior - + +#search_path = '"$user",public' # schema names +#default_tablespace = '' # a tablespace name, '' uses the default +#temp_tablespaces = '' # a list of tablespace names, '' uses + # only default tablespace +#check_function_bodies = on +#default_transaction_isolation = 'read committed' +#default_transaction_read_only = off +#default_transaction_deferrable = off +#session_replication_role = 'origin' +#statement_timeout = 0 # in milliseconds, 0 is disabled +#vacuum_freeze_min_age = 50000000 +#vacuum_freeze_table_age = 150000000 +#bytea_output = 'hex' # hex, escape +#xmlbinary = 'base64' +#xmloption = 'content' + +# - Locale and Formatting - + +datestyle = 'iso, mdy' +#intervalstyle = 'postgres' +#timezone = '(defaults to server environment setting)' +#timezone_abbreviations = 'Default' # Select the set of available time zone + # abbreviations. Currently, there are + # Default + # Australia + # India + # You can create your own file in + # share/timezonesets/. +#extra_float_digits = 0 # min -15, max 3 +#client_encoding = sql_ascii # actually, defaults to database + # encoding + +# These settings are initialized by initdb, but they can be changed. +lc_messages = 'en_US.UTF-8' # locale for system error message + # strings +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' + +# - Other Defaults - + +#dynamic_library_path = '$libdir' +#local_preload_libraries = '' + + +#------------------------------------------------------------------------------ +# LOCK MANAGEMENT +#------------------------------------------------------------------------------ + +#deadlock_timeout = 1s +#max_locks_per_transaction = 64 # min 10 + # (change requires restart) +# Note: Each lock table slot uses ~270 bytes of shared memory, and there are +# max_locks_per_transaction * (max_connections + max_prepared_transactions) +# lock table slots. +#max_pred_locks_per_transaction = 64 # min 10 + # (change requires restart) + +#------------------------------------------------------------------------------ +# VERSION/PLATFORM COMPATIBILITY +#------------------------------------------------------------------------------ + +# - Previous PostgreSQL Versions - + +#array_nulls = on +#backslash_quote = safe_encoding # on, off, or safe_encoding +#default_with_oids = off +#escape_string_warning = on +#lo_compat_privileges = off +#quote_all_identifiers = off +#sql_inheritance = on +#standard_conforming_strings = on +#synchronize_seqscans = on + +# - Other Platforms and Clients - + +#transform_null_equals = off + + +#------------------------------------------------------------------------------ +# ERROR HANDLING +#------------------------------------------------------------------------------ + +#exit_on_error = off # terminate session on any error? +#restart_after_crash = on # reinitialize after backend crash? + + +#------------------------------------------------------------------------------ +# CUSTOMIZED OPTIONS +#------------------------------------------------------------------------------ + +#custom_variable_classes = '' # list of custom variable class names diff --git a/postgresql.conf.slave b/postgresql.conf.slave new file mode 100644 index 0000000..214bf09 --- /dev/null +++ b/postgresql.conf.slave @@ -0,0 +1,556 @@ +# ----------------------------- +# PostgreSQL configuration file +# ----------------------------- +# +# This file consists of lines of the form: +# +# name = value +# +# (The "=" is optional.) Whitespace may be used. Comments are introduced with +# "#" anywhere on a line. The complete list of parameter names and allowed +# values can be found in the PostgreSQL documentation. +# +# The commented-out settings shown in this file represent the default values. +# Re-commenting a setting is NOT sufficient to revert it to the default value; +# you need to reload the server. +# +# This file is read on server startup and when the server receives a SIGHUP +# signal. If you edit the file on a running system, you have to SIGHUP the +# server for the changes to take effect, or use "pg_ctl reload". Some +# parameters, which are marked below, require a server shutdown and restart to +# take effect. +# +# Any parameter can also be given as a command-line option to the server, e.g., +# "postgres -c log_connections=on". Some parameters can be changed at run time +# with the "SET" SQL command. +# +# Memory units: kB = kilobytes Time units: ms = milliseconds +# MB = megabytes s = seconds +# GB = gigabytes min = minutes +# h = hours +# d = days + + +#------------------------------------------------------------------------------ +# FILE LOCATIONS +#------------------------------------------------------------------------------ + +# The default values of these variables are driven from the -D command-line +# option or PGDATA environment variable, represented here as ConfigDir. + +data_directory = '/var/lib/postgresql/9.1/main' # use data in another directory + # (change requires restart) +hba_file = '/etc/postgresql/9.1/main/pg_hba.conf' # host-based authentication file + # (change requires restart) +ident_file = '/etc/postgresql/9.1/main/pg_ident.conf' # ident configuration file + # (change requires restart) + +# If external_pid_file is not explicitly set, no extra PID file is written. +external_pid_file = '/var/run/postgresql/9.1-main.pid' # write an extra PID file + # (change requires restart) + + +#------------------------------------------------------------------------------ +# CONNECTIONS AND AUTHENTICATION +#------------------------------------------------------------------------------ + +# - Connection Settings - + +listen_addresses = '*' # what IP address(es) to listen on; + # comma-separated list of addresses; + # defaults to 'localhost', '*' = all + # (change requires restart) +port = 5432 # (change requires restart) +max_connections = 1000 # (change requires restart) +# 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) +unix_socket_directory = '/var/run/postgresql' # (change requires restart) +#unix_socket_group = '' # (change requires restart) +#unix_socket_permissions = 0777 # begin with 0 to use octal notation + # (change requires restart) +#bonjour = off # advertise server via Bonjour + # (change requires restart) +#bonjour_name = '' # defaults to the computer name + # (change requires restart) + +# - Security and Authentication - + +#authentication_timeout = 1min # 1s-600s +ssl = true # (change requires restart) +#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers + # (change requires restart) +#ssl_renegotiation_limit = 512MB # amount of data between renegotiations +#password_encryption = on +#db_user_namespace = off + +# Kerberos and GSSAPI +#krb_server_keyfile = '' +#krb_srvname = 'postgres' # (Kerberos only) +#krb_caseins_users = off + +# - TCP Keepalives - +# see "man 7 tcp" for details + +#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds; + # 0 selects the system default +#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds; + # 0 selects the system default +#tcp_keepalives_count = 0 # TCP_KEEPCNT; + # 0 selects the system default + + +#------------------------------------------------------------------------------ +# RESOURCE USAGE (except WAL) +#------------------------------------------------------------------------------ + +# - Memory - + +shared_buffers = 24MB # min 128kB + # (change requires restart) +#temp_buffers = 8MB # min 800kB +#max_prepared_transactions = 0 # zero disables the feature + # (change requires restart) +# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory +# per transaction slot, plus lock space (see max_locks_per_transaction). +# It is not advisable to set max_prepared_transactions nonzero unless you +# actively intend to use prepared transactions. +#work_mem = 1MB # min 64kB +#maintenance_work_mem = 16MB # min 1MB +#max_stack_depth = 2MB # min 100kB + +# - Kernel Resource Usage - + +#max_files_per_process = 1000 # min 25 + # (change requires restart) +#shared_preload_libraries = '' # (change requires restart) + +# - Cost-Based Vacuum Delay - + +#vacuum_cost_delay = 0ms # 0-100 milliseconds +#vacuum_cost_page_hit = 1 # 0-10000 credits +#vacuum_cost_page_miss = 10 # 0-10000 credits +#vacuum_cost_page_dirty = 20 # 0-10000 credits +#vacuum_cost_limit = 200 # 1-10000 credits + +# - Background Writer - + +#bgwriter_delay = 200ms # 10-10000ms between rounds +#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round +#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers scanned/round + +# - Asynchronous Behavior - + +#effective_io_concurrency = 1 # 1-1000. 0 disables prefetching + + +#------------------------------------------------------------------------------ +# WRITE AHEAD LOG +#------------------------------------------------------------------------------ + +# - Settings - + +#wal_level = hot_standby # minimal, archive, or hot_standby + # (change requires restart) +#fsync = on # turns forced synchronization on or off +#synchronous_commit = on # synchronization level; on, off, or local +#wal_sync_method = fsync # the default is the first option + # supported by the operating system: + # open_datasync + # fdatasync (default on Linux) + # fsync + # fsync_writethrough + # open_sync +#full_page_writes = on # recover from partial page writes +#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers + # (change requires restart) +#wal_writer_delay = 200ms # 1-10000 milliseconds + +#commit_delay = 0 # range 0-100000, in microseconds +#commit_siblings = 5 # range 1-1000 + +# - Checkpoints - + +#checkpoint_segments = 3 # 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 - + +#archive_mode = on # allows archiving to be done + # (change requires restart) +#archive_command = 'cp -i "%p" /var/lib/postgresql/9.1/archive/"%f" 0 logs only + # statements running at least this number + # of milliseconds + + +# - What to Log - + +#debug_print_parse = off +#debug_print_rewritten = off +#debug_print_plan = off +#debug_pretty_print = on +#log_checkpoints = off +#log_connections = off +#log_disconnections = off +#log_duration = off +#log_error_verbosity = default # terse, default, or verbose messages +#log_hostname = off +log_line_prefix = '%t ' # special values: + # %a = application name + # %u = user name + # %d = database name + # %r = remote host and port + # %h = remote host + # %p = process ID + # %t = timestamp without milliseconds + # %m = timestamp with milliseconds + # %i = command tag + # %e = SQL state + # %c = session ID + # %l = session line number + # %s = session start timestamp + # %v = virtual transaction ID + # %x = transaction ID (0 if none) + # %q = stop here in non-session + # processes + # %% = '%' + # e.g. '<%u%%%d> ' +#log_lock_waits = off # log lock waits >= deadlock_timeout +#log_statement = 'none' # none, ddl, mod, all +#log_temp_files = -1 # log temporary files equal or larger + # than the specified size in kilobytes; + # -1 disables, 0 logs all temp files +#log_timezone = '(defaults to server environment setting)' + + +#------------------------------------------------------------------------------ +# RUNTIME STATISTICS +#------------------------------------------------------------------------------ + +# - Query/Index Statistics Collector - + +#track_activities = on +#track_counts = on +#track_functions = none # none, pl, all +#track_activity_query_size = 1024 # (change requires restart) +#update_process_title = on +#stats_temp_directory = 'pg_stat_tmp' + + +# - Statistics Monitoring - + +#log_parser_stats = off +#log_planner_stats = off +#log_executor_stats = off +#log_statement_stats = off + + +#------------------------------------------------------------------------------ +# AUTOVACUUM PARAMETERS +#------------------------------------------------------------------------------ + +#autovacuum = on # Enable autovacuum subprocess? 'on' + # requires track_counts to also be on. +#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and + # their durations, > 0 logs only + # actions running at least this number + # of milliseconds. +#autovacuum_max_workers = 3 # max number of autovacuum subprocesses + # (change requires restart) +#autovacuum_naptime = 1min # time between autovacuum runs +#autovacuum_vacuum_threshold = 50 # min number of row updates before + # vacuum +#autovacuum_analyze_threshold = 50 # min number of row updates before + # analyze +#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum +#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze +#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum + # (change requires restart) +#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for + # autovacuum, in milliseconds; + # -1 means use vacuum_cost_delay +#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for + # autovacuum, -1 means use + # vacuum_cost_limit + + +#------------------------------------------------------------------------------ +# CLIENT CONNECTION DEFAULTS +#------------------------------------------------------------------------------ + +# - Statement Behavior - + +#search_path = '"$user",public' # schema names +#default_tablespace = '' # a tablespace name, '' uses the default +#temp_tablespaces = '' # a list of tablespace names, '' uses + # only default tablespace +#check_function_bodies = on +#default_transaction_isolation = 'read committed' +#default_transaction_read_only = off +#default_transaction_deferrable = off +#session_replication_role = 'origin' +#statement_timeout = 0 # in milliseconds, 0 is disabled +#vacuum_freeze_min_age = 50000000 +#vacuum_freeze_table_age = 150000000 +#bytea_output = 'hex' # hex, escape +#xmlbinary = 'base64' +#xmloption = 'content' + +# - Locale and Formatting - + +datestyle = 'iso, mdy' +#intervalstyle = 'postgres' +#timezone = '(defaults to server environment setting)' +#timezone_abbreviations = 'Default' # Select the set of available time zone + # abbreviations. Currently, there are + # Default + # Australia + # India + # You can create your own file in + # share/timezonesets/. +#extra_float_digits = 0 # min -15, max 3 +#client_encoding = sql_ascii # actually, defaults to database + # encoding + +# These settings are initialized by initdb, but they can be changed. +lc_messages = 'en_US.UTF-8' # locale for system error message + # strings +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' + +# - Other Defaults - + +#dynamic_library_path = '$libdir' +#local_preload_libraries = '' + + +#------------------------------------------------------------------------------ +# LOCK MANAGEMENT +#------------------------------------------------------------------------------ + +#deadlock_timeout = 1s +#max_locks_per_transaction = 64 # min 10 + # (change requires restart) +# Note: Each lock table slot uses ~270 bytes of shared memory, and there are +# max_locks_per_transaction * (max_connections + max_prepared_transactions) +# lock table slots. +#max_pred_locks_per_transaction = 64 # min 10 + # (change requires restart) + +#------------------------------------------------------------------------------ +# VERSION/PLATFORM COMPATIBILITY +#------------------------------------------------------------------------------ + +# - Previous PostgreSQL Versions - + +#array_nulls = on +#backslash_quote = safe_encoding # on, off, or safe_encoding +#default_with_oids = off +#escape_string_warning = on +#lo_compat_privileges = off +#quote_all_identifiers = off +#sql_inheritance = on +#standard_conforming_strings = on +#synchronize_seqscans = on + +# - Other Platforms and Clients - + +#transform_null_equals = off + + +#------------------------------------------------------------------------------ +# ERROR HANDLING +#------------------------------------------------------------------------------ + +#exit_on_error = off # terminate session on any error? +#restart_after_crash = on # reinitialize after backend crash? + + +#------------------------------------------------------------------------------ +# CUSTOMIZED OPTIONS +#------------------------------------------------------------------------------ + +#custom_variable_classes = '' # list of custom variable class names diff --git a/recovery.conf b/recovery.conf new file mode 100644 index 0000000..918bf40 --- /dev/null +++ b/recovery.conf @@ -0,0 +1,12 @@ +# If "recovery.conf" is present in the PostgreSQL data directory, it is +# read on postmaster startup. After successful recovery, it is renamed +# to "recovery.done" to ensure that we do not accidentally re-enter +# archive recovery or standby mode. +# +standby_mode = 'on' +primary_conninfo = 'host=master-or-slave.foo.bar port=5432 user=postgres password=nopass' +trigger_file = '/tmp/trigger_file' +#Note about restorecommand: It can be an scp to the "other" machines archive dir, useful if the slave falls behind (beyond the px_log) +#and needs access to older logs. (Alternativly you can write your pglogs to a shared space (eg: nfs) ) +restore_command = 'cp /var/lib/postgresql/9.1/archive/%f "%p"' +archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /var/lib/postgresql/9.1/archive/ %r' diff --git a/streaming-replication.sh b/streaming-replication.sh new file mode 100755 index 0000000..2cde56c --- /dev/null +++ b/streaming-replication.sh @@ -0,0 +1,173 @@ +#!/bin/bash + +##This is meanst to be run on the slave, with the masters ip as the passed variable. ($1) +sourcehost="$1" +datadir=/var/lib/postgresql/9.1/main +archivedir=/var/lib/postgresql/9.1/archive +archivedirdest=/var/lib/postgresql/9.1/archive + +#Usage +if [ "$1" = "" ] || [ "$1" = "-h" ] || [ "$1" = "-help" ] || [ "$1" = "--help" ]; +then + echo "Usage: $0 masters ip address" +exit 0 +fi +#This script must be run as postgres user +Whoami () { + if [[ $(whoami) != "postgres" ]] + then + echo "[INFO] This script must be run as postgres user !" + exit 1 + fi +} + +#Chek if postgres server is running on remote host +CheckIfPostgresIsRunningOnRemoteHost () { + isrunning="$(ssh postgres@"$1" 'if killall -0 postgres; then echo "postgres_running"; else echo "postgress_not_running"; fi;')" + + if [[ "$isrunning" = "postgress_not_running" ]] + then + echo "[ERROR] Postgres not running on the master. Exiting.."; + exit 1 + + elif [[ "$isrunning" = "postgres_running" ]] + then + echo "[OK] Postgres master running on remote host"; + + elif echo "[ERROR] Unexpected response. Exiting.." + then + exit 1 + fi +} + +#Check if the supposed master is actually a master +CheckIfMasterIsActuallyAMaster () { + ismaster="$(ssh postgres@"$1" 'if [ -f /var/lib/postgresql/9.1/main/recovery.done ]; then echo "postgres_is_a_master_instance"; else echo "postgres_is_not_master"; fi;')" + + if [[ "$ismaster" = "postgres_is_not_master" ]] + then + echo "[ERROR] Postgres is already running as a slave. Exiting.."; + exit 1 + elif [[ "$ismaster" = "postgres_is_a_master_instance" ]] + then + echo "[INFO] Postgres is running as master (probably)"; + elif echo "[ERROR] Unexpected response. Exiting.." + then + exit 1 + fi +} + +#prepare local server to become the new slave server. +PrepareLocalServer () { + + if [ -f '/tmp/trigger_file' ] + then + rm /tmp/trigger_file + fi + echo "[INFO] Stopping slave node.." + bash /etc/init.d/postgresql stop + + if [[ -f "$datadir/recovery.done" ]]; + then + mv "$datadir"/recovery.done "$datadir"/recovery.conf + fi + + #Remove old WAL logs + rm /var/lib/postgresql/9.1/archive/* +} + + +CheckForRecoveryConfig () { + if [[ -f "$datadir/recovery.conf" ]]; + then + echo "[OK] Slave config file found, Continuing.." + else + echo "[ERROR] recovery.conf not found. Postgres is not a slave. Exiting.." + exit 1 + fi +} + + +#Put master into backup mode +#Before doing PutMasterIntoBackupMode clean up archive logs (IE rm or mv /var/lib/postgresql/9.1/archive/*). They are not needed since we are effectivly createing a new base backup and then synching it. +PutMasterIntoBackupMode () { + echo "[INFO] Putting postgres master '$1' in backup mode." + ssh postgres@"$1" "rm /var/lib/postgresql/9.1/archive/*" + ssh postgres@"$1" "psql -c \"SELECT pg_start_backup('Streaming Replication', true)\" postgres" +} + +#rsync master's data to local postgres dir +RsyncWhileLive () { + echo "[INFO] Transfering data from master '$1' ..." + rsync -C -av --delete --progress -e ssh --exclude server.key --exclude server.crt --exclude recovery.conf --exclude recovery.done --exclude postmaster.pid --exclude pg_xlog/ "$1":"$datadir"/ "$datadir"/ > /dev/null + if [ $? == 0 ] + then + echo "[OK] Transfert completed."; + else + echo "[ERROR] Error during transfer !"; + exit 0; + fi +} + + +#This archives the WAL log (ends writing to it and moves it to the $archive dir +StopBackupModeAndArchiveIntoWallLog () { + echo "[INFO] Disable backup mode from master '$1'." + ssh postgres@"$1" "psql -c \"SELECT pg_stop_backup()\" postgres" + echo "[INFO] Synchronising master/slave archive directory..." + rsync -C -a --progress -e ssh "$1":"$archivedir"/ "$archivedirdest"/ > /dev/null + if [ $? == 0 ] + then + echo "[OK] Sync achieved."; + else + echo "[ERROR] Error during sync !"; + exit 0; + fi +} + +#stop postgres and copy transactions made during the last two rsync's +StopPostgreSqlAndFinishRsync () { + echo "[INFO] Stopping master node.." + ssh postgres@"$1" "/etc/init.d/postgresql stop" + echo "[INFO] Transfering xlog files from master... " + rsync -av --delete --progress -e ssh "$sourcehost":"$datadir"/pg_xlog/ "$datadir"/pg_xlog/ > /dev/null + if [ $? == 0 ] + then + echo "[OK] Transfert completed."; + else + echo "[ERROR] Error during transfer !"; + exit 0; + fi +} + +#Start both Master and Slave +StartLocalAndThenRemotePostGreSql () { + echo "[INFO] Starting slave node.." + /etc/init.d/postgresql start + if ! killall -0 postgres; then echo '[ERROR] Slave not running !'; else echo "[OK] Slave started."; fi; + + + echo "[INFO] Starting master node.." + ssh postgres@"$1" "/etc/init.d/postgresql start" + + status=$(ssh postgres@$1 "if ! killall -0 postgres; then echo 'error'; else echo 'running'; fi;") + if [ $status == "error" ] + then + echo "[ERROR] Master not running !"; + exit 0; + else + echo "[OK] Master started."; + fi +} + +#Execute above operations +Whoami +CheckIfPostgresIsRunningOnRemoteHost "$1" +CheckIfMasterIsActuallyAMaster "$1" +PrepareLocalServer "$datadir" +CheckForRecoveryConfig "$datadir" +PutMasterIntoBackupMode "$1" +RsyncWhileLive "$1" +StopBackupModeAndArchiveIntoWallLog "$1" "$archivedir" "$archivedirdest" +StopPostgreSqlAndFinishRsync "$1" +StartLocalAndThenRemotePostGreSql "$1" -- 2.20.1