18 KiB
Executable File
18 KiB
Executable File
#!/bin/bash
# ======================================================
# PostgreSQL HA Logical Replication Script v17.3 (Stable)
# Mehdi Aslani
# ======================================================
set -euo pipefail
IFS=$'\n\t'
# Conditional colors
if [ -t 1 ]; then
RED=$''
YELLOW=$''
GREEN=$''
NC=$''
BLUE=$''
else
RED=''; GREEN=''; YELLOW=''; BLUE=''; NC=''
fi
echo "======================================================"
echo "PostgreSQL HA Logical Replication Script v17.3"
echo "======================================================"
SCRIPT_NAME=$(basename "$0")
TIMESTAMP=$(date +%Y%m%d-%H%M%S)
LOG_DIR="/var/log/postgresql"
LOG_FILE="${LOG_DIR}/ha-rep-${TIMESTAMP}.log"
BACKUP_DIR="/tmp/pg-ha-backup-${TIMESTAMP}"
CONFIG_FILE=""
HBA_FILE=""
SERVICE_NAME="postgresql"
PUB_IP=""
SUB_IP=""
DB_NAME="pline"
DB_USER=""
DB_PASS=""
ACTION=""
PUBLICATION_NAME="pub_from_pub"
SUBSCRIPTION_NAME="sub_from_pub"
log_info() { echo -e "${GREEN}[INFO]${NC} $1" | tee -a "$LOG_FILE"; }
log_ok() { echo -e "${GREEN}[OK]${NC} $1" | tee -a "$LOG_FILE"; }
log_err() { echo -e "${RED}[ERROR]${NC} $1" | tee -a "$LOG_FILE" >&2; }
log_warn() { echo -e "${YELLOW}[WARN]${NC} $1" | tee -a "$LOG_FILE"; }
log_step() { echo -e "${BLUE}----->${NC} $1" | tee -a "$LOG_FILE"; }
setup_logging() {
mkdir -p "$LOG_DIR" "$BACKUP_DIR" 2>/dev/null || true
touch "$LOG_FILE"
log_info "Log file: $LOG_FILE"
}
cleanup() {
unset PGPASSWORD
log_info "Cleanup done."
}
parse_args() {
for arg in "$@"; do
case $arg in
pub=*) PUB_IP="${arg#pub=}" ;;
sub=*) SUB_IP="${arg#sub=}" ;;
db=*) DB_NAME="${arg#db=}" ;;
user=*) DB_USER="${arg#user=}" ;;
pass=*) DB_PASS="${arg#pass=}" ;;
action=*) ACTION="${arg#action=}" ;;
*) log_err "Unknown argument: $arg"; exit 1 ;;
esac
done
if [[ "$ACTION" == "fix" ]]; then
[[ -z "$DB_NAME" ]] && DB_NAME="pline"
[[ -z "$DB_USER" ]] && DB_USER="root"
if [[ -z "$DB_PASS" ]]; then
log_err "For fix action, provide pass="
exit 1
fi
export PGPASSWORD="$DB_PASS"
log_info "Fix action - DB: $DB_NAME, User: $DB_USER"
return 0
fi
local missing=0
[[ -z "$PUB_IP" ]] && { log_err "Missing pub=IP"; missing=1; }
[[ -z "$SUB_IP" ]] && { log_err "Missing sub=IP"; missing=1; }
[[ -z "$DB_NAME" ]] && { log_err "Missing db=NAME"; missing=1; }
[[ -z "$DB_USER" ]] && { log_err "Missing user=USER"; missing=1; }
[[ -z "$DB_PASS" ]] && { log_err "Missing pass=PASSWORD"; missing=1; }
[[ -z "$ACTION" ]] && { log_err "Missing action=ACTION"; missing=1; }
if [[ $missing -eq 1 ]]; then
echo "Valid actions: pub, sub, remove, status, test, fix"
exit 1
fi
export PGPASSWORD="$DB_PASS"
log_info "Publisher: $PUB_IP | Subscriber: $SUB_IP | DB: $DB_NAME | Action: $ACTION"
}
run_sql() {
local sql="$1"
local desc="$2"
local ignore_err="${3:-false}"
local result
result=$(psql -X -A -t -d "$DB_NAME" -c "$sql" 2>&1)
local exit_code=$?
if [[ $exit_code -eq 0 ]] && [[ "$result" != *"ERROR"* ]]; then
echo "$result"
return 0
else
if [[ "$ignore_err" == "true" ]]; then
log_warn "$desc ignored: $result"
return 0
else
log_err "$desc: $result"
return 1
fi
fi
}
run_sql_remote() {
local host="$1"
local sql="$2"
local desc="$3"
local ignore_err="${4:-false}"
local result
result=$(psql -h "$host" -U "$DB_USER" -d "$DB_NAME" -X -A -t -c "$sql" 2>&1)
local exit_code=$?
if [[ $exit_code -eq 0 ]] && [[ "$result" != *"ERROR"* ]]; then
echo "$result"
return 0
else
if [[ "$ignore_err" == "true" ]]; then
log_warn "$desc on $host ignored: $result"
return 0
else
log_err "$desc on $host: $result"
return 1
fi
fi
}
ensure_superuser() {
echo -e "${GREEN}[INFO]${NC} Ensuring user '$DB_USER' is SUPERUSER..."
USER_EXISTS=$(sudo -u postgres psql -tAc "SELECT 1 FROM pg_roles WHERE rolname='$DB_USER';" 2>/dev/null)
if [[ -z "$USER_EXISTS" ]]; then
sudo -u postgres psql -c "CREATE USER \"$DB_USER\" WITH PASSWORD '$DB_PASS' SUPERUSER;"
else
IS_SUPER=$(sudo -u postgres psql -tAc "SELECT rolsuper FROM pg_roles WHERE rolname='$DB_USER';" | xargs)
if [[ "$IS_SUPER" != "t" ]]; then
sudo -u postgres psql -c "ALTER ROLE \"$DB_USER\" WITH SUPERUSER;"
fi
fi
FINAL=$(sudo -u postgres psql -tAc "SELECT rolsuper FROM pg_roles WHERE rolname='$DB_USER';" | xargs)
if [[ "$FINAL" != "t" ]]; then
log_err "User $DB_USER is not SUPERUSER"
exit 1
fi
log_ok "User $DB_USER is SUPERUSER"
}
ensure_postgres_running() {
log_step "Checking PostgreSQL service"
if ! systemctl is-active --quiet "$SERVICE_NAME"; then
systemctl start "$SERVICE_NAME"
sleep 5
fi
log_ok "PostgreSQL is running"
}
detect_config() {
log_step "Detecting PostgreSQL configuration"
CONFIG_FILE=$(psql -t -c "SHOW config_file;" | xargs)
if [[ -z "$CONFIG_FILE" || ! -f "$CONFIG_FILE" ]]; then
log_err "Cannot find config file"
exit 1
fi
CONFIG_DIR=$(dirname "$CONFIG_FILE")
HBA_FILE="$CONFIG_DIR/pg_hba.conf"
log_info "Config: $CONFIG_FILE"
log_ok "Configuration detected"
}
backup_config() {
log_step "Backing up configuration"
cp "$CONFIG_FILE" "$BACKUP_DIR/postgresql.conf" 2>/dev/null || true
cp "$HBA_FILE" "$BACKUP_DIR/pg_hba.conf" 2>/dev/null || true
log_ok "Backup saved to $BACKUP_DIR"
}
restore_config() {
log_step "Restoring configuration"
cp "$BACKUP_DIR/postgresql.conf" "$CONFIG_FILE" 2>/dev/null || true
cp "$BACKUP_DIR/pg_hba.conf" "$HBA_FILE" 2>/dev/null || true
systemctl reload "$SERVICE_NAME" 2>/dev/null || true
log_ok "Configuration restored"
}
configure_postgresql() {
local mode="$1"
log_step "Configuring PostgreSQL ($mode mode)"
if [[ "$mode" == "set" ]]; then
sed -i "s/^#*listen_addresses.*/listen_addresses = '*'/g" "$CONFIG_FILE"
sed -i "s/^#*wal_level.*/wal_level = logical/" "$CONFIG_FILE"
sed -i "s/^#*max_wal_senders.*/max_wal_senders = 20/" "$CONFIG_FILE"
sed -i "s/^#*max_replication_slots.*/max_replication_slots = 20/" "$CONFIG_FILE"
sed -i "s/^#*max_worker_processes.*/max_worker_processes = 20/" "$CONFIG_FILE"
log_ok "Replication parameters set"
else
sed -i "s/^listen_addresses = '\*'/ #listen_addresses = 'localhost'/" "$CONFIG_FILE"
log_warn "Replication parameters reverted"
fi
systemctl restart "$SERVICE_NAME"
sleep 5
log_ok "PostgreSQL restarted"
}
update_pg_hba() {
local action="$1"
local ip="$2"
if [[ "$action" == "add" ]]; then
sed -i "/${ip}\/32/d" "$HBA_FILE"
echo "host replication ${DB_USER} ${ip}/32 md5" >> "$HBA_FILE"
echo "host ${DB_NAME} ${DB_USER} ${ip}/32 md5" >> "$HBA_FILE"
log_ok "Added HBA for $ip"
else
sed -i "/${ip}\/32/d" "$HBA_FILE" 2>/dev/null || true
log_ok "Removed HBA for $ip"
fi
run_sql "SELECT pg_reload_conf();" "Reload HBA" "true" >/dev/null 2>&1
}
reset_sequences() {
log_step "Resetting all sequences to max ID values"
psql -U "$DB_USER" -d "$DB_NAME" -c '
DO $$
DECLARE
seq RECORD;
max_id bigint;
seq_val bigint;
BEGIN
FOR seq IN
SELECT
s.relname AS seq_name,
a.attrelid::regclass::text AS table_name,
a.attname AS column_name
FROM pg_class s
JOIN pg_depend d ON d.objid = s.oid AND d.deptype = '"'a'"'
JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
WHERE s.relkind = '"'S'"'
LOOP
BEGIN
EXECUTE format('"'"'SELECT COALESCE(max(%I), 0) FROM %s'"'"', seq.column_name, seq.table_name) INTO max_id;
EXECUTE format('"'"'SELECT last_value FROM %s'"'"', seq.seq_name) INTO seq_val;
IF max_id >= seq_val THEN
EXECUTE format('"'"'SELECT setval(%L, %s)'"'"', seq.seq_name, max_id + 1);
RAISE NOTICE '"'Sequence % updated from % to %'"', seq.seq_name, seq_val, max_id + 1;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE '"'Error on %: %'"', seq.seq_name, SQLERRM;
END;
END LOOP;
END;
$$;' 2>&1 | tee -a "$LOG_FILE"
log_ok "Sequence reset completed"
}
sync_full_schema() {
log_step "Syncing schema from publisher $PUB_IP (all schemas)"
if ! command -v pg_dump &> /dev/null; then
log_err "pg_dump not found"
exit 1
fi
if pg_dump -h "$PUB_IP" -U "$DB_USER" -d "$DB_NAME" --schema-only --clean --if-exists --no-owner --no-privileges --no-publications --no-subscriptions 2>/tmp/pg_dump_error.log | psql -U "$DB_USER" -d "$DB_NAME" 2>&1 | tee -a "$LOG_FILE"; then
log_ok "Schema synced"
else
log_err "Schema sync failed. Check /tmp/pg_dump_error.log"
return 1
fi
}
test_connection() {
log_step "Testing connection to publisher $PUB_IP"
if run_sql_remote "$PUB_IP" "SELECT 1;" "Test" >/dev/null; then
log_ok "Connected to publisher"
return 0
else
log_err "Cannot connect to publisher"
return 1
fi
}
cleanup_local_replication_objects() {
log_info "Cleaning local replication objects"
psql -U "$DB_USER" -d "$DB_NAME" -c "SELECT pg_terminate_backend(pid) FROM pg_stat_replication;" 2>&1 | tee -a "$LOG_FILE" || true
psql -U "$DB_USER" -d "$DB_NAME" -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE backend_type LIKE '%logical%';" 2>&1 | tee -a "$LOG_FILE" || true
sleep 2
sub_exists=$(psql -U "$DB_USER" -d "$DB_NAME" -tAc "SELECT 1 FROM pg_subscription WHERE subname='$SUBSCRIPTION_NAME';" 2>/dev/null | xargs)
if [[ "$sub_exists" == "1" ]]; then
psql -U "$DB_USER" -d "$DB_NAME" -c "ALTER SUBSCRIPTION $SUBSCRIPTION_NAME DISABLE;" 2>&1 | tee -a "$LOG_FILE" || true
psql -U "$DB_USER" -d "$DB_NAME" -c "ALTER SUBSCRIPTION $SUBSCRIPTION_NAME SET (slot_name = NONE);" 2>&1 | tee -a "$LOG_FILE" || true
psql -U "$DB_USER" -d "$DB_NAME" -c "DROP SUBSCRIPTION $SUBSCRIPTION_NAME;" 2>&1 | tee -a "$LOG_FILE" || true
fi
psql -U "$DB_USER" -d "$DB_NAME" -c "DROP PUBLICATION IF EXISTS $PUBLICATION_NAME;" 2>&1 | tee -a "$LOG_FILE" || true
slots=$(psql -U "$DB_USER" -d "$DB_NAME" -tAc "SELECT slot_name FROM pg_replication_slots WHERE slot_type='logical' AND active_pid IS NOT NULL;" 2>/dev/null)
for slot in $slots; do
pid=$(psql -U "$DB_USER" -d "$DB_NAME" -tAc "SELECT active_pid FROM pg_replication_slots WHERE slot_name='$slot';" 2>/dev/null | xargs)
if [[ -n "$pid" && "$pid" != "0" ]]; then
psql -U "$DB_USER" -d "$DB_NAME" -c "SELECT pg_terminate_backend($pid);" 2>&1 | tee -a "$LOG_FILE" || true
sleep 1
fi
done
psql -U "$DB_USER" -d "$DB_NAME" -c "SELECT pg_drop_replication_slot(slot_name) FROM pg_replication_slots WHERE slot_type='logical';" 2>&1 | tee -a "$LOG_FILE" || true
sed -i "/${SUB_IP}\/32/d" "$HBA_FILE" 2>/dev/null || true
sed -i "/${PUB_IP}\/32/d" "$HBA_FILE" 2>/dev/null || true
psql -U "$DB_USER" -d "$DB_NAME" -c "SELECT pg_reload_conf();" 2>&1 | tee -a "$LOG_FILE" || true
log_ok "Local cleanup done"
}
truncate_all_tables() {
log_step "Truncating all tables in all user schemas"
schemas=$(psql -U "$DB_USER" -d "$DB_NAME" -tAc "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('pg_catalog', 'information_schema');" 2>/dev/null)
if [[ -z "$schemas" ]]; then
log_warn "No user schemas found"
return 0
fi
psql -U "$DB_USER" -d "$DB_NAME" -c "SET session_replication_role = 'replica';" 2>&1 | tee -a "$LOG_FILE" || true
for schema in $schemas; do
tables=$(psql -U "$DB_USER" -d "$DB_NAME" -tAc "SELECT tablename FROM pg_tables WHERE schemaname = '$schema';" 2>/dev/null)
for tbl in $tables; do
psql -U "$DB_USER" -d "$DB_NAME" -c "TRUNCATE TABLE \"$schema\".\"$tbl\" CASCADE;" 2>&1 | tee -a "$LOG_FILE" || true
done
done
psql -U "$DB_USER" -d "$DB_NAME" -c "SET session_replication_role = 'origin';" 2>&1 | tee -a "$LOG_FILE" || true
log_ok "All tables truncated"
}
find_cti_service() {
systemctl list-unit-files | grep -i "cti\.service" | awk '{print $1}' | head -1
}
restart_cti_fully() {
local cti_svc=$(find_cti_service)
if [[ -z "$cti_svc" ]]; then
log_warn "CTI service not found"
return 0
fi
log_info "Restarting $cti_svc"
systemctl restart "$cti_svc" 2>/dev/null || true
sleep 3
if systemctl is-active --quiet "$cti_svc"; then
log_ok "CTI restarted"
else
log_err "CTI restart failed"
fi
}
setup_publisher() {
log_step "Setting up Publisher"
cleanup_local_replication_objects
backup_config
configure_postgresql "set"
update_pg_hba "add" "$SUB_IP"
run_sql "CREATE PUBLICATION $PUBLICATION_NAME FOR ALL TABLES;" "Create publication" || {
log_err "Failed to create publication"
restore_config
exit 1
}
reset_sequences
log_ok "Publisher ready"
restart_cti_fully
}
setup_subscriber() {
log_step "Setting up Subscriber"
cleanup_local_replication_objects
truncate_all_tables
test_connection || exit 1
sync_full_schema || exit 1
backup_config
configure_postgresql "set"
update_pg_hba "add" "$PUB_IP"
psql -h "$PUB_IP" -U "$DB_USER" -d "$DB_NAME" -c "SELECT pg_drop_replication_slot('$SUBSCRIPTION_NAME');" 2>/dev/null || true
conn_string="host=$PUB_IP port=5432 dbname=$DB_NAME user=$DB_USER password=$DB_PASS"
log_info "Creating subscription"
if ! run_sql "CREATE SUBSCRIPTION $SUBSCRIPTION_NAME CONNECTION '$conn_string' PUBLICATION $PUBLICATION_NAME WITH (copy_data = true, enabled = true, create_slot = true);" "Create subscription" "true" >/dev/null 2>&1; then
log_warn "Retry with create_slot=false"
if ! run_sql "CREATE SUBSCRIPTION $SUBSCRIPTION_NAME CONNECTION '$conn_string' PUBLICATION $PUBLICATION_NAME WITH (copy_data = true, enabled = true, create_slot = false);" "Create subscription (no slot)" "true" >/dev/null 2>&1; then
log_err "Failed to create subscription"
restore_config
exit 1
fi
fi
log_ok "Subscription created, waiting for initial sync"
max_wait=600; waited=0
while [[ $waited -lt $max_wait ]]; do
sync_count=$(psql -U "$DB_USER" -d "$DB_NAME" -tAc "SELECT count(*) FROM pg_subscription_rel WHERE srsubstate NOT IN ('r');" 2>/dev/null | xargs)
[[ -z "$sync_count" ]] && sync_count=0
if [[ "$sync_count" -eq 0 ]]; then
log_ok "Initial sync completed"
break
fi
sleep 5
waited=$((waited+5))
if [[ $((waited % 30)) -eq 0 ]]; then
log_info "Still syncing: $sync_count tables left (${waited}s)"
fi
done
psql -U "$DB_USER" -d "$DB_NAME" -c "ALTER SUBSCRIPTION $SUBSCRIPTION_NAME ENABLE;" 2>&1 | tee -a "$LOG_FILE" || true
reset_sequences
log_ok "Subscriber ready"
restart_cti_fully
}
show_status() {
log_step "Replication Status"
echo ""
echo "Subscriptions:"
run_sql "SELECT subname, subenabled FROM pg_subscription;" "Subscriptions" "true" | sed 's/^/ /' || echo " None"
echo ""
echo "Statistics:"
run_sql "SELECT subname, received_lsn, latest_end_lsn FROM pg_stat_subscription;" "Stats" "true" | head -3 | sed 's/^/ /' || echo " None"
echo ""
echo "Publications:"
run_sql "SELECT pubname, puballtables FROM pg_publication;" "Publications" "true" | sed 's/^/ /' || echo " None"
}
remove_replication() {
log_step "Removing replication objects locally"
cleanup_local_replication_objects
restore_config
systemctl restart "$SERVICE_NAME"
sleep 5
if pg_isready -q; then
log_ok "PostgreSQL restarted"
else
log_err "PostgreSQL restart failed"
exit 1
fi
reset_sequences
log_info "Removal complete"
restart_cti_fully
}
action_fix() {
log_step "Manual sequence fix"
reset_sequences
log_ok "Fix done"
}
main() {
parse_args "$@"
setup_logging
if [[ "$ACTION" == "fix" ]]; then
ensure_postgres_running
ensure_superuser
detect_config
if action_fix; then
echo "=== Script Completed ==="
else
echo "=== Script Failed ==="
exit 1
fi
cleanup
exit 0
fi
ensure_postgres_running
ensure_superuser
detect_config
if [[ "$ACTION" == "pub" || "$ACTION" == "sub" || "$ACTION" == "remove" ]]; then
backup_config
trap 'log_err "Script failed"; restore_config; cleanup; echo "=== Script Failed ==="; exit 1' ERR
else
trap 'cleanup' EXIT
fi
case "$ACTION" in
pub) if setup_publisher; then echo "=== Script Completed ==="; else echo "=== Script Failed ==="; exit 1; fi ;;
sub) if setup_subscriber; then echo "=== Script Completed ==="; else echo "=== Script Failed ==="; exit 1; fi ;;
status) show_status ;;
remove) if remove_replication; then echo "=== Script Completed ==="; else echo "=== Script Failed ==="; exit 1; fi ;;
test) if test_connection; then echo "=== Script Completed ==="; else echo "=== Script Failed ==="; exit 1; fi ;;
*) log_err "Invalid action"; echo "=== Script Failed ==="; exit 1 ;;
esac
cleanup
}
main "$@"
# ======================================================
# PostgreSQL HA Logical Replication Script v17.3 (Stable)
# Mehdi Aslani
# ======================================================
set -euo pipefail
IFS=$'\n\t'
# Conditional colors
if [ -t 1 ]; then
RED=$''
YELLOW=$''
GREEN=$''
NC=$''
BLUE=$''
else
RED=''; GREEN=''; YELLOW=''; BLUE=''; NC=''
fi
echo "======================================================"
echo "PostgreSQL HA Logical Replication Script v17.3"
echo "======================================================"
SCRIPT_NAME=$(basename "$0")
TIMESTAMP=$(date +%Y%m%d-%H%M%S)
LOG_DIR="/var/log/postgresql"
LOG_FILE="${LOG_DIR}/ha-rep-${TIMESTAMP}.log"
BACKUP_DIR="/tmp/pg-ha-backup-${TIMESTAMP}"
CONFIG_FILE=""
HBA_FILE=""
SERVICE_NAME="postgresql"
PUB_IP=""
SUB_IP=""
DB_NAME="pline"
DB_USER=""
DB_PASS=""
ACTION=""
PUBLICATION_NAME="pub_from_pub"
SUBSCRIPTION_NAME="sub_from_pub"
log_info() { echo -e "${GREEN}[INFO]${NC} $1" | tee -a "$LOG_FILE"; }
log_ok() { echo -e "${GREEN}[OK]${NC} $1" | tee -a "$LOG_FILE"; }
log_err() { echo -e "${RED}[ERROR]${NC} $1" | tee -a "$LOG_FILE" >&2; }
log_warn() { echo -e "${YELLOW}[WARN]${NC} $1" | tee -a "$LOG_FILE"; }
log_step() { echo -e "${BLUE}----->${NC} $1" | tee -a "$LOG_FILE"; }
setup_logging() {
mkdir -p "$LOG_DIR" "$BACKUP_DIR" 2>/dev/null || true
touch "$LOG_FILE"
log_info "Log file: $LOG_FILE"
}
cleanup() {
unset PGPASSWORD
log_info "Cleanup done."
}
parse_args() {
for arg in "$@"; do
case $arg in
pub=*) PUB_IP="${arg#pub=}" ;;
sub=*) SUB_IP="${arg#sub=}" ;;
db=*) DB_NAME="${arg#db=}" ;;
user=*) DB_USER="${arg#user=}" ;;
pass=*) DB_PASS="${arg#pass=}" ;;
action=*) ACTION="${arg#action=}" ;;
*) log_err "Unknown argument: $arg"; exit 1 ;;
esac
done
if [[ "$ACTION" == "fix" ]]; then
[[ -z "$DB_NAME" ]] && DB_NAME="pline"
[[ -z "$DB_USER" ]] && DB_USER="root"
if [[ -z "$DB_PASS" ]]; then
log_err "For fix action, provide pass="
exit 1
fi
export PGPASSWORD="$DB_PASS"
log_info "Fix action - DB: $DB_NAME, User: $DB_USER"
return 0
fi
local missing=0
[[ -z "$PUB_IP" ]] && { log_err "Missing pub=IP"; missing=1; }
[[ -z "$SUB_IP" ]] && { log_err "Missing sub=IP"; missing=1; }
[[ -z "$DB_NAME" ]] && { log_err "Missing db=NAME"; missing=1; }
[[ -z "$DB_USER" ]] && { log_err "Missing user=USER"; missing=1; }
[[ -z "$DB_PASS" ]] && { log_err "Missing pass=PASSWORD"; missing=1; }
[[ -z "$ACTION" ]] && { log_err "Missing action=ACTION"; missing=1; }
if [[ $missing -eq 1 ]]; then
echo "Valid actions: pub, sub, remove, status, test, fix"
exit 1
fi
export PGPASSWORD="$DB_PASS"
log_info "Publisher: $PUB_IP | Subscriber: $SUB_IP | DB: $DB_NAME | Action: $ACTION"
}
run_sql() {
local sql="$1"
local desc="$2"
local ignore_err="${3:-false}"
local result
result=$(psql -X -A -t -d "$DB_NAME" -c "$sql" 2>&1)
local exit_code=$?
if [[ $exit_code -eq 0 ]] && [[ "$result" != *"ERROR"* ]]; then
echo "$result"
return 0
else
if [[ "$ignore_err" == "true" ]]; then
log_warn "$desc ignored: $result"
return 0
else
log_err "$desc: $result"
return 1
fi
fi
}
run_sql_remote() {
local host="$1"
local sql="$2"
local desc="$3"
local ignore_err="${4:-false}"
local result
result=$(psql -h "$host" -U "$DB_USER" -d "$DB_NAME" -X -A -t -c "$sql" 2>&1)
local exit_code=$?
if [[ $exit_code -eq 0 ]] && [[ "$result" != *"ERROR"* ]]; then
echo "$result"
return 0
else
if [[ "$ignore_err" == "true" ]]; then
log_warn "$desc on $host ignored: $result"
return 0
else
log_err "$desc on $host: $result"
return 1
fi
fi
}
ensure_superuser() {
echo -e "${GREEN}[INFO]${NC} Ensuring user '$DB_USER' is SUPERUSER..."
USER_EXISTS=$(sudo -u postgres psql -tAc "SELECT 1 FROM pg_roles WHERE rolname='$DB_USER';" 2>/dev/null)
if [[ -z "$USER_EXISTS" ]]; then
sudo -u postgres psql -c "CREATE USER \"$DB_USER\" WITH PASSWORD '$DB_PASS' SUPERUSER;"
else
IS_SUPER=$(sudo -u postgres psql -tAc "SELECT rolsuper FROM pg_roles WHERE rolname='$DB_USER';" | xargs)
if [[ "$IS_SUPER" != "t" ]]; then
sudo -u postgres psql -c "ALTER ROLE \"$DB_USER\" WITH SUPERUSER;"
fi
fi
FINAL=$(sudo -u postgres psql -tAc "SELECT rolsuper FROM pg_roles WHERE rolname='$DB_USER';" | xargs)
if [[ "$FINAL" != "t" ]]; then
log_err "User $DB_USER is not SUPERUSER"
exit 1
fi
log_ok "User $DB_USER is SUPERUSER"
}
ensure_postgres_running() {
log_step "Checking PostgreSQL service"
if ! systemctl is-active --quiet "$SERVICE_NAME"; then
systemctl start "$SERVICE_NAME"
sleep 5
fi
log_ok "PostgreSQL is running"
}
detect_config() {
log_step "Detecting PostgreSQL configuration"
CONFIG_FILE=$(psql -t -c "SHOW config_file;" | xargs)
if [[ -z "$CONFIG_FILE" || ! -f "$CONFIG_FILE" ]]; then
log_err "Cannot find config file"
exit 1
fi
CONFIG_DIR=$(dirname "$CONFIG_FILE")
HBA_FILE="$CONFIG_DIR/pg_hba.conf"
log_info "Config: $CONFIG_FILE"
log_ok "Configuration detected"
}
backup_config() {
log_step "Backing up configuration"
cp "$CONFIG_FILE" "$BACKUP_DIR/postgresql.conf" 2>/dev/null || true
cp "$HBA_FILE" "$BACKUP_DIR/pg_hba.conf" 2>/dev/null || true
log_ok "Backup saved to $BACKUP_DIR"
}
restore_config() {
log_step "Restoring configuration"
cp "$BACKUP_DIR/postgresql.conf" "$CONFIG_FILE" 2>/dev/null || true
cp "$BACKUP_DIR/pg_hba.conf" "$HBA_FILE" 2>/dev/null || true
systemctl reload "$SERVICE_NAME" 2>/dev/null || true
log_ok "Configuration restored"
}
configure_postgresql() {
local mode="$1"
log_step "Configuring PostgreSQL ($mode mode)"
if [[ "$mode" == "set" ]]; then
sed -i "s/^#*listen_addresses.*/listen_addresses = '*'/g" "$CONFIG_FILE"
sed -i "s/^#*wal_level.*/wal_level = logical/" "$CONFIG_FILE"
sed -i "s/^#*max_wal_senders.*/max_wal_senders = 20/" "$CONFIG_FILE"
sed -i "s/^#*max_replication_slots.*/max_replication_slots = 20/" "$CONFIG_FILE"
sed -i "s/^#*max_worker_processes.*/max_worker_processes = 20/" "$CONFIG_FILE"
log_ok "Replication parameters set"
else
sed -i "s/^listen_addresses = '\*'/ #listen_addresses = 'localhost'/" "$CONFIG_FILE"
log_warn "Replication parameters reverted"
fi
systemctl restart "$SERVICE_NAME"
sleep 5
log_ok "PostgreSQL restarted"
}
update_pg_hba() {
local action="$1"
local ip="$2"
if [[ "$action" == "add" ]]; then
sed -i "/${ip}\/32/d" "$HBA_FILE"
echo "host replication ${DB_USER} ${ip}/32 md5" >> "$HBA_FILE"
echo "host ${DB_NAME} ${DB_USER} ${ip}/32 md5" >> "$HBA_FILE"
log_ok "Added HBA for $ip"
else
sed -i "/${ip}\/32/d" "$HBA_FILE" 2>/dev/null || true
log_ok "Removed HBA for $ip"
fi
run_sql "SELECT pg_reload_conf();" "Reload HBA" "true" >/dev/null 2>&1
}
reset_sequences() {
log_step "Resetting all sequences to max ID values"
psql -U "$DB_USER" -d "$DB_NAME" -c '
DO $$
DECLARE
seq RECORD;
max_id bigint;
seq_val bigint;
BEGIN
FOR seq IN
SELECT
s.relname AS seq_name,
a.attrelid::regclass::text AS table_name,
a.attname AS column_name
FROM pg_class s
JOIN pg_depend d ON d.objid = s.oid AND d.deptype = '"'a'"'
JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
WHERE s.relkind = '"'S'"'
LOOP
BEGIN
EXECUTE format('"'"'SELECT COALESCE(max(%I), 0) FROM %s'"'"', seq.column_name, seq.table_name) INTO max_id;
EXECUTE format('"'"'SELECT last_value FROM %s'"'"', seq.seq_name) INTO seq_val;
IF max_id >= seq_val THEN
EXECUTE format('"'"'SELECT setval(%L, %s)'"'"', seq.seq_name, max_id + 1);
RAISE NOTICE '"'Sequence % updated from % to %'"', seq.seq_name, seq_val, max_id + 1;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE '"'Error on %: %'"', seq.seq_name, SQLERRM;
END;
END LOOP;
END;
$$;' 2>&1 | tee -a "$LOG_FILE"
log_ok "Sequence reset completed"
}
sync_full_schema() {
log_step "Syncing schema from publisher $PUB_IP (all schemas)"
if ! command -v pg_dump &> /dev/null; then
log_err "pg_dump not found"
exit 1
fi
if pg_dump -h "$PUB_IP" -U "$DB_USER" -d "$DB_NAME" --schema-only --clean --if-exists --no-owner --no-privileges --no-publications --no-subscriptions 2>/tmp/pg_dump_error.log | psql -U "$DB_USER" -d "$DB_NAME" 2>&1 | tee -a "$LOG_FILE"; then
log_ok "Schema synced"
else
log_err "Schema sync failed. Check /tmp/pg_dump_error.log"
return 1
fi
}
test_connection() {
log_step "Testing connection to publisher $PUB_IP"
if run_sql_remote "$PUB_IP" "SELECT 1;" "Test" >/dev/null; then
log_ok "Connected to publisher"
return 0
else
log_err "Cannot connect to publisher"
return 1
fi
}
cleanup_local_replication_objects() {
log_info "Cleaning local replication objects"
psql -U "$DB_USER" -d "$DB_NAME" -c "SELECT pg_terminate_backend(pid) FROM pg_stat_replication;" 2>&1 | tee -a "$LOG_FILE" || true
psql -U "$DB_USER" -d "$DB_NAME" -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE backend_type LIKE '%logical%';" 2>&1 | tee -a "$LOG_FILE" || true
sleep 2
sub_exists=$(psql -U "$DB_USER" -d "$DB_NAME" -tAc "SELECT 1 FROM pg_subscription WHERE subname='$SUBSCRIPTION_NAME';" 2>/dev/null | xargs)
if [[ "$sub_exists" == "1" ]]; then
psql -U "$DB_USER" -d "$DB_NAME" -c "ALTER SUBSCRIPTION $SUBSCRIPTION_NAME DISABLE;" 2>&1 | tee -a "$LOG_FILE" || true
psql -U "$DB_USER" -d "$DB_NAME" -c "ALTER SUBSCRIPTION $SUBSCRIPTION_NAME SET (slot_name = NONE);" 2>&1 | tee -a "$LOG_FILE" || true
psql -U "$DB_USER" -d "$DB_NAME" -c "DROP SUBSCRIPTION $SUBSCRIPTION_NAME;" 2>&1 | tee -a "$LOG_FILE" || true
fi
psql -U "$DB_USER" -d "$DB_NAME" -c "DROP PUBLICATION IF EXISTS $PUBLICATION_NAME;" 2>&1 | tee -a "$LOG_FILE" || true
slots=$(psql -U "$DB_USER" -d "$DB_NAME" -tAc "SELECT slot_name FROM pg_replication_slots WHERE slot_type='logical' AND active_pid IS NOT NULL;" 2>/dev/null)
for slot in $slots; do
pid=$(psql -U "$DB_USER" -d "$DB_NAME" -tAc "SELECT active_pid FROM pg_replication_slots WHERE slot_name='$slot';" 2>/dev/null | xargs)
if [[ -n "$pid" && "$pid" != "0" ]]; then
psql -U "$DB_USER" -d "$DB_NAME" -c "SELECT pg_terminate_backend($pid);" 2>&1 | tee -a "$LOG_FILE" || true
sleep 1
fi
done
psql -U "$DB_USER" -d "$DB_NAME" -c "SELECT pg_drop_replication_slot(slot_name) FROM pg_replication_slots WHERE slot_type='logical';" 2>&1 | tee -a "$LOG_FILE" || true
sed -i "/${SUB_IP}\/32/d" "$HBA_FILE" 2>/dev/null || true
sed -i "/${PUB_IP}\/32/d" "$HBA_FILE" 2>/dev/null || true
psql -U "$DB_USER" -d "$DB_NAME" -c "SELECT pg_reload_conf();" 2>&1 | tee -a "$LOG_FILE" || true
log_ok "Local cleanup done"
}
truncate_all_tables() {
log_step "Truncating all tables in all user schemas"
schemas=$(psql -U "$DB_USER" -d "$DB_NAME" -tAc "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('pg_catalog', 'information_schema');" 2>/dev/null)
if [[ -z "$schemas" ]]; then
log_warn "No user schemas found"
return 0
fi
psql -U "$DB_USER" -d "$DB_NAME" -c "SET session_replication_role = 'replica';" 2>&1 | tee -a "$LOG_FILE" || true
for schema in $schemas; do
tables=$(psql -U "$DB_USER" -d "$DB_NAME" -tAc "SELECT tablename FROM pg_tables WHERE schemaname = '$schema';" 2>/dev/null)
for tbl in $tables; do
psql -U "$DB_USER" -d "$DB_NAME" -c "TRUNCATE TABLE \"$schema\".\"$tbl\" CASCADE;" 2>&1 | tee -a "$LOG_FILE" || true
done
done
psql -U "$DB_USER" -d "$DB_NAME" -c "SET session_replication_role = 'origin';" 2>&1 | tee -a "$LOG_FILE" || true
log_ok "All tables truncated"
}
find_cti_service() {
systemctl list-unit-files | grep -i "cti\.service" | awk '{print $1}' | head -1
}
restart_cti_fully() {
local cti_svc=$(find_cti_service)
if [[ -z "$cti_svc" ]]; then
log_warn "CTI service not found"
return 0
fi
log_info "Restarting $cti_svc"
systemctl restart "$cti_svc" 2>/dev/null || true
sleep 3
if systemctl is-active --quiet "$cti_svc"; then
log_ok "CTI restarted"
else
log_err "CTI restart failed"
fi
}
setup_publisher() {
log_step "Setting up Publisher"
cleanup_local_replication_objects
backup_config
configure_postgresql "set"
update_pg_hba "add" "$SUB_IP"
run_sql "CREATE PUBLICATION $PUBLICATION_NAME FOR ALL TABLES;" "Create publication" || {
log_err "Failed to create publication"
restore_config
exit 1
}
reset_sequences
log_ok "Publisher ready"
restart_cti_fully
}
setup_subscriber() {
log_step "Setting up Subscriber"
cleanup_local_replication_objects
truncate_all_tables
test_connection || exit 1
sync_full_schema || exit 1
backup_config
configure_postgresql "set"
update_pg_hba "add" "$PUB_IP"
psql -h "$PUB_IP" -U "$DB_USER" -d "$DB_NAME" -c "SELECT pg_drop_replication_slot('$SUBSCRIPTION_NAME');" 2>/dev/null || true
conn_string="host=$PUB_IP port=5432 dbname=$DB_NAME user=$DB_USER password=$DB_PASS"
log_info "Creating subscription"
if ! run_sql "CREATE SUBSCRIPTION $SUBSCRIPTION_NAME CONNECTION '$conn_string' PUBLICATION $PUBLICATION_NAME WITH (copy_data = true, enabled = true, create_slot = true);" "Create subscription" "true" >/dev/null 2>&1; then
log_warn "Retry with create_slot=false"
if ! run_sql "CREATE SUBSCRIPTION $SUBSCRIPTION_NAME CONNECTION '$conn_string' PUBLICATION $PUBLICATION_NAME WITH (copy_data = true, enabled = true, create_slot = false);" "Create subscription (no slot)" "true" >/dev/null 2>&1; then
log_err "Failed to create subscription"
restore_config
exit 1
fi
fi
log_ok "Subscription created, waiting for initial sync"
max_wait=600; waited=0
while [[ $waited -lt $max_wait ]]; do
sync_count=$(psql -U "$DB_USER" -d "$DB_NAME" -tAc "SELECT count(*) FROM pg_subscription_rel WHERE srsubstate NOT IN ('r');" 2>/dev/null | xargs)
[[ -z "$sync_count" ]] && sync_count=0
if [[ "$sync_count" -eq 0 ]]; then
log_ok "Initial sync completed"
break
fi
sleep 5
waited=$((waited+5))
if [[ $((waited % 30)) -eq 0 ]]; then
log_info "Still syncing: $sync_count tables left (${waited}s)"
fi
done
psql -U "$DB_USER" -d "$DB_NAME" -c "ALTER SUBSCRIPTION $SUBSCRIPTION_NAME ENABLE;" 2>&1 | tee -a "$LOG_FILE" || true
reset_sequences
log_ok "Subscriber ready"
restart_cti_fully
}
show_status() {
log_step "Replication Status"
echo ""
echo "Subscriptions:"
run_sql "SELECT subname, subenabled FROM pg_subscription;" "Subscriptions" "true" | sed 's/^/ /' || echo " None"
echo ""
echo "Statistics:"
run_sql "SELECT subname, received_lsn, latest_end_lsn FROM pg_stat_subscription;" "Stats" "true" | head -3 | sed 's/^/ /' || echo " None"
echo ""
echo "Publications:"
run_sql "SELECT pubname, puballtables FROM pg_publication;" "Publications" "true" | sed 's/^/ /' || echo " None"
}
remove_replication() {
log_step "Removing replication objects locally"
cleanup_local_replication_objects
restore_config
systemctl restart "$SERVICE_NAME"
sleep 5
if pg_isready -q; then
log_ok "PostgreSQL restarted"
else
log_err "PostgreSQL restart failed"
exit 1
fi
reset_sequences
log_info "Removal complete"
restart_cti_fully
}
action_fix() {
log_step "Manual sequence fix"
reset_sequences
log_ok "Fix done"
}
main() {
parse_args "$@"
setup_logging
if [[ "$ACTION" == "fix" ]]; then
ensure_postgres_running
ensure_superuser
detect_config
if action_fix; then
echo "=== Script Completed ==="
else
echo "=== Script Failed ==="
exit 1
fi
cleanup
exit 0
fi
ensure_postgres_running
ensure_superuser
detect_config
if [[ "$ACTION" == "pub" || "$ACTION" == "sub" || "$ACTION" == "remove" ]]; then
backup_config
trap 'log_err "Script failed"; restore_config; cleanup; echo "=== Script Failed ==="; exit 1' ERR
else
trap 'cleanup' EXIT
fi
case "$ACTION" in
pub) if setup_publisher; then echo "=== Script Completed ==="; else echo "=== Script Failed ==="; exit 1; fi ;;
sub) if setup_subscriber; then echo "=== Script Completed ==="; else echo "=== Script Failed ==="; exit 1; fi ;;
status) show_status ;;
remove) if remove_replication; then echo "=== Script Completed ==="; else echo "=== Script Failed ==="; exit 1; fi ;;
test) if test_connection; then echo "=== Script Completed ==="; else echo "=== Script Failed ==="; exit 1; fi ;;
*) log_err "Invalid action"; echo "=== Script Failed ==="; exit 1 ;;
esac
cleanup
}
main "$@"