Bases de Datos Tutoriales

Sincronización Incremental de PostgreSQL con Túnel SSH

Prepara tus bases de datos para hacer copias de seguridad incrementales automatizadas con Cron.

26 de enero de 2026 10 min de lectura

Sincronización Incremental de PostgreSQL con Túnel SSH: Guía Completa

Tabla de Contenidos

  1. Introducción
  2. Conceptos Técnicos
  3. Requisitos Previos
  4. Instalación
  5. Configuración
  6. El Script de Sincronización
  7. Automatización con CRON
  8. Monitoreo y Mantenimiento
  9. Solución de Problemas
  10. Conclusión

Introducción

Esta guía documenta la implementación de un sistema completo de sincronización incremental entre dos bases de datos PostgreSQL ubicadas en servidores diferentes, utilizando un túnel SSH para establecer una conexión segura.

¿Qué problema resuelve?

Cuando tienes una aplicación Django (u otra aplicación web) con bases de datos PostgreSQL en diferentes ubicaciones y necesitas mantenerlas sincronizadas, este script proporciona:

  • Sincronización incremental: Solo sincroniza cambios recientes
  • Seguridad: Usa túnel SSH para la conexión
  • Inteligencia: Detecta dependencias de Foreign Keys automáticamente
  • Robustez: Maneja esquemas diferentes y case-sensitivity
  • Automatización: Se ejecuta según horarios programados

Casos de uso

  • Respaldo activo: Mantener una copia actualizada en un servidor de respaldo
  • Entornos distribuidos: Sincronizar datos entre oficinas
  • Desarrollo a producción: Migrar cambios de datos entre entornos
  • Análisis: Replicar datos a un servidor de análisis sin afectar producción

Conceptos Técnicos

1. Sincronización Incremental

Definición: Proceso que solo transfiere los registros que han cambiado desde la última sincronización, en lugar de copiar toda la base de datos.

Ventajas:

  • Reduce el tiempo de ejecución
  • Minimiza el uso de ancho de banda
  • Menos carga en el servidor de base de datos

Implementación: Se basa en columnas de timestamp (como updated_at, modified_at) que Django genera automáticamente con auto_now=True.

python 
# Ejemplo en modelo Django
class MiModelo(models.Model):
    nombre = models.CharField(max_length=100)
    created_at = models.DateTimeField(auto_now_add=True)  # Se establece al crear
    updated_at = models.DateTimeField(auto_now=True)       # Se actualiza en cada modificación
# Ejemplo en modelo Django
class MiModelo(models.Model):
    nombre = models.CharField(max_length=100)
    created_at = models.DateTimeField(auto_now_add=True)  # Se establece al crear
    updated_at = models.DateTimeField(auto_now=True)       # Se actualiza en cada modificación

2. Túnel SSH (SSH Tunneling)

Definición: Técnica que permite redirigir el tráfico de red a través de una conexión SSH cifrada.

Funcionamiento:

plaintext 
[Cliente Local] → [Puerto Local:5433] → [Túnel SSH] → [Servidor Remoto:22] → [PostgreSQL:5432]
[Cliente Local] → [Puerto Local:5433] → [Túnel SSH] → [Servidor Remoto:22] → [PostgreSQL:5432]

Ventajas:

  • Cifrado de extremo a extremo
  • No requiere exponer PostgreSQL directamente a Internet
  • Usa infraestructura SSH existente

Comando básico:

bash 
ssh -L puerto_local:host_remoto:puerto_remoto usuario@servidor
ssh -L puerto_local:host_remoto:puerto_remoto usuario@servidor

3. UPSERT (INSERT + UPDATE)

Definición: Operación que inserta un registro si no existe, o lo actualiza si ya existe.

Sintaxis en PostgreSQL:

sql 
INSERT INTO tabla (columnas...)
VALUES (valores...)
ON CONFLICT (clave_primaria) DO UPDATE SET
  columna1 = EXCLUDED.columna1,
  columna2 = EXCLUDED.columna2;
INSERT INTO tabla (columnas...)
VALUES (valores...)
ON CONFLICT (clave_primaria) DO UPDATE SET
  columna1 = EXCLUDED.columna1,
  columna2 = EXCLUDED.columna2;

Uso: Esencial para sincronización incremental, ya que no sabemos si el registro ya existe en destino.

4. Foreign Keys y Orden Topológico

Definición: Las Foreign Keys crean dependencias entre tablas. El orden topológico es el orden correcto de sincronización que respeta estas dependencias.

Ejemplo:

plaintext 
personas (sin FK) → Nivel 0
  ↓
acompañantes (FK a personas) → Nivel 1
  ↓
atenciones (FK a acompañantes) → Nivel 2
personas (sin FK) → Nivel 0
  ↓
acompañantes (FK a personas) → Nivel 1
  ↓
atenciones (FK a acompañantes) → Nivel 2

Importancia: Si sincronizas en orden incorrecto, obtendrás errores de violación de integridad referencial.

5. Case Sensitivity en PostgreSQL

Definición: PostgreSQL distingue entre mayúsculas y minúsculas en nombres de identificadores cuando están entre comillas dobles.

Sin comillas:

sql 
SELECT nombreUsuaria FROM tabla;
-- PostgreSQL lo convierte a: nombreusuaria (todo minúsculas)
SELECT nombreUsuaria FROM tabla;
-- PostgreSQL lo convierte a: nombreusuaria (todo minúsculas)

Con comillas:

sql 
SELECT "nombreUsuaria" FROM tabla;
-- PostgreSQL respeta: nombreUsuaria (case exacto)
SELECT "nombreUsuaria" FROM tabla;
-- PostgreSQL respeta: nombreUsuaria (case exacto)

Relevancia: Django a veces genera columnas con camelCase (especialmente en ForeignKeys), por lo que debemos preservar el case exacto.

6. URL Encoding en Contraseñas

Definición: Proceso de convertir caracteres especiales a formato seguro para URLs y connection strings.

Tabla de codificación:

Carácter Codificado Ejemplo
@ %40 user@pass → user%40pass
$ %24 pass$123 → pass%24123
! %21 pass! → pass%21
# %23 pass#word → pass%23word
% %25 pass% → pass%25

Uso: Connection strings de PostgreSQL (postgresql://user:pass@host/db) requieren que los caracteres especiales en contraseñas estén codificados.

Requisitos Previos

Software Requerido

En el servidor que ejecutará el script:

bash 
# PostgreSQL client (para psql, pg_dump)
postgresql-client >= 10.x

# SSH client con soporte de password
sshpass >= 1.06

# Bash >= 4.0
bash >= 4.0
# PostgreSQL client (para psql, pg_dump)
postgresql-client >= 10.x

# SSH client con soporte de password
sshpass >= 1.06

# Bash >= 4.0
bash >= 4.0

En ambos servidores (local y remoto):

bash 
# PostgreSQL Server
postgresql-server >= 10.x

# SSH Server (en el servidor remoto)
openssh-server >= 7.0
# PostgreSQL Server
postgresql-server >= 10.x

# SSH Server (en el servidor remoto)
openssh-server >= 7.0

Requisitos de Base de Datos

Tablas compatibles deben tener:

  1. Clave primaria (obligatorio para UPSERT)

    sql 
    ALTER TABLE mi_tabla ADD PRIMARY KEY (id);
    ALTER TABLE mi_tabla ADD PRIMARY KEY (id);

  2. Columna de timestamp (obligatorio para sincronización incremental)

    sql 
    ALTER TABLE mi_tabla ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- Trigger para actualización automática
CREATE TRIGGER update_mi_tabla_updated_at 
BEFORE UPDATE ON mi_tabla 
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
    ALTER TABLE mi_tabla ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- Trigger para actualización automática
CREATE TRIGGER update_mi_tabla_updated_at 
BEFORE UPDATE ON mi_tabla 
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

Permisos Requeridos

En el servidor local:

  • Usuario con privilegios sudo para instalar paquetes y configurar cron
  • Acceso de lectura a la base de datos local

En el servidor remoto:

  • Cuenta SSH con acceso al servidor
  • Acceso de escritura a la base de datos remota
  • Puerto SSH abierto en el firewall

Instalación

Paso 1: Instalar Dependencias

bash 
# Actualizar lista de paquetes
sudo apt-get update

# Instalar paquetes necesarios
sudo apt-get install -y sshpass postgresql-client

# Verificar instalación
psql --version
# Salida esperada: psql (PostgreSQL) 10.x o superior

sshpass -V
# Salida esperada: sshpass 1.06 o superior
# Actualizar lista de paquetes
sudo apt-get update

# Instalar paquetes necesarios
sudo apt-get install -y sshpass postgresql-client

# Verificar instalación
psql --version
# Salida esperada: psql (PostgreSQL) 10.x o superior

sshpass -V
# Salida esperada: sshpass 1.06 o superior

¿Qué hace cada paquete?

  • postgresql-client: Proporciona herramientas como psql (cliente de PostgreSQL) y pg_dump (para exportar datos)
  • sshpass: Permite pasar contraseñas a SSH de forma no interactiva (necesario para automatización)

Paso 2: Crear Estructura de Directorios

bash 
# Directorio para configuración (credenciales)
sudo mkdir -p /etc/pg_sync/

# Directorio para el script principal
sudo mkdir -p /opt/pg_sync/

# Directorio para logs de ejecución
sudo mkdir -p /var/log/pg_sync/

# Directorio para archivos de control (timestamps, PIDs)
sudo mkdir -p /var/lib/pg_sync/
# Directorio para configuración (credenciales)
sudo mkdir -p /etc/pg_sync/

# Directorio para el script principal
sudo mkdir -p /opt/pg_sync/

# Directorio para logs de ejecución
sudo mkdir -p /var/log/pg_sync/

# Directorio para archivos de control (timestamps, PIDs)
sudo mkdir -p /var/lib/pg_sync/

Estructura final:

plaintext 
/etc/pg_sync/          → Configuración (credenciales)
  └── config.env       → Variables de entorno

/opt/pg_sync/          → Scripts ejecutables
  └── pg_sync.sh       → Script principal de sincronización

/var/log/pg_sync/      → Logs de ejecución
  ├── sync_20251231_120000.log  → Log individual por ejecución
  └── cron.log                   → Log agregado de CRON

/var/lib/pg_sync/      → Archivos de control
  ├── last_sync_timestamp.txt   → Timestamp de última sincronización
  ├── ssh_tunnel.pid             → PID del túnel SSH activo
  └── table_dependencies.txt     → Orden de dependencias detectado
/etc/pg_sync/          → Configuración (credenciales)
  └── config.env       → Variables de entorno

/opt/pg_sync/          → Scripts ejecutables
  └── pg_sync.sh       → Script principal de sincronización

/var/log/pg_sync/      → Logs de ejecución
  ├── sync_20251231_120000.log  → Log individual por ejecución
  └── cron.log                   → Log agregado de CRON

/var/lib/pg_sync/      → Archivos de control
  ├── last_sync_timestamp.txt   → Timestamp de última sincronización
  ├── ssh_tunnel.pid             → PID del túnel SSH activo
  └── table_dependencies.txt     → Orden de dependencias detectado

Configuración

Paso 1: Crear Archivo de Configuración

bash 
sudo nano /etc/pg_sync/config.env
sudo nano /etc/pg_sync/config.env

Paso 2: Contenido del config.env

Plantilla con explicaciones:

bash 
# ============================================
# CONFIGURACIÓN BASE DE DATOS LOCAL
# ============================================

# Host de la base de datos local (generalmente localhost)
export LOCAL_DB_HOST="localhost"

# Puerto de PostgreSQL local (por defecto 5432)
export LOCAL_DB_PORT="5432"

# Nombre de la base de datos a sincronizar
export LOCAL_DB_NAME="mi_base_local"

# Usuario de PostgreSQL con permisos de lectura
export LOCAL_DB_USER="usuario_local"

# Contraseña del usuario (con URL encoding si tiene caracteres especiales)
# Ejemplo: $miPass@123 → %24miPass%40123
export LOCAL_DB_PASSWORD="password_local"


# ============================================
# CONFIGURACIÓN SERVIDOR SSH REMOTO
# ============================================

# IP o hostname del servidor remoto
export SSH_HOST="192.168.1.100"

# Puerto SSH del servidor remoto (por defecto 22)
export SSH_PORT="22"

# Usuario SSH para conectarse al servidor remoto
export SSH_USER="usuario_ssh"

# Contraseña SSH (sin encoding, se usa tal cual)
export SSH_PASSWORD="password_ssh"


# ============================================
# CONFIGURACIÓN TÚNEL SSH
# ============================================

# Puerto local para el túnel SSH (debe estar libre)
# Este puerto se usará temporalmente para redirigir conexiones
export LOCAL_TUNNEL_PORT="5433"


# ============================================
# CONFIGURACIÓN BASE DE DATOS REMOTA
# ============================================

# Host de PostgreSQL DENTRO del servidor remoto
# Generalmente "localhost" porque estamos dentro del servidor
export REMOTE_DB_HOST="localhost"

# Puerto de PostgreSQL en el servidor remoto
export REMOTE_DB_PORT="5432"

# Nombre de la base de datos de destino
export REMOTE_DB_NAME="mi_base_remota"

# Usuario de PostgreSQL remoto con permisos de escritura
export REMOTE_DB_USER="usuario_remoto"

# Contraseña del usuario remoto (con URL encoding si tiene caracteres especiales)
export REMOTE_DB_PASSWORD="password_remoto"


# ============================================
# DIRECTORIOS (NO MODIFICAR)
# ============================================

export LOG_DIR="/var/log/pg_sync"
export CONTROL_DIR="/var/lib/pg_sync"
# ============================================
# CONFIGURACIÓN BASE DE DATOS LOCAL
# ============================================

# Host de la base de datos local (generalmente localhost)
export LOCAL_DB_HOST="localhost"

# Puerto de PostgreSQL local (por defecto 5432)
export LOCAL_DB_PORT="5432"

# Nombre de la base de datos a sincronizar
export LOCAL_DB_NAME="mi_base_local"

# Usuario de PostgreSQL con permisos de lectura
export LOCAL_DB_USER="usuario_local"

# Contraseña del usuario (con URL encoding si tiene caracteres especiales)
# Ejemplo: $miPass@123 → %24miPass%40123
export LOCAL_DB_PASSWORD="password_local"


# ============================================
# CONFIGURACIÓN SERVIDOR SSH REMOTO
# ============================================

# IP o hostname del servidor remoto
export SSH_HOST="192.168.1.100"

# Puerto SSH del servidor remoto (por defecto 22)
export SSH_PORT="22"

# Usuario SSH para conectarse al servidor remoto
export SSH_USER="usuario_ssh"

# Contraseña SSH (sin encoding, se usa tal cual)
export SSH_PASSWORD="password_ssh"


# ============================================
# CONFIGURACIÓN TÚNEL SSH
# ============================================

# Puerto local para el túnel SSH (debe estar libre)
# Este puerto se usará temporalmente para redirigir conexiones
export LOCAL_TUNNEL_PORT="5433"


# ============================================
# CONFIGURACIÓN BASE DE DATOS REMOTA
# ============================================

# Host de PostgreSQL DENTRO del servidor remoto
# Generalmente "localhost" porque estamos dentro del servidor
export REMOTE_DB_HOST="localhost"

# Puerto de PostgreSQL en el servidor remoto
export REMOTE_DB_PORT="5432"

# Nombre de la base de datos de destino
export REMOTE_DB_NAME="mi_base_remota"

# Usuario de PostgreSQL remoto con permisos de escritura
export REMOTE_DB_USER="usuario_remoto"

# Contraseña del usuario remoto (con URL encoding si tiene caracteres especiales)
export REMOTE_DB_PASSWORD="password_remoto"


# ============================================
# DIRECTORIOS (NO MODIFICAR)
# ============================================

export LOG_DIR="/var/log/pg_sync"
export CONTROL_DIR="/var/lib/pg_sync"

Ejemplo Completo con Datos de Ejemplo

bash 
# Base de Datos Local
export LOCAL_DB_HOST="localhost"
export LOCAL_DB_PORT="5432"
export LOCAL_DB_NAME="myapp_local"
export LOCAL_DB_USER="local_db_user"
export LOCAL_DB_PASSWORD="%24MyP%40ss123"  # Contraseña: $MyP@ss123

# Servidor SSH Remoto
export SSH_HOST="192.168.1.100"
export SSH_PORT="22"
export SSH_USER="sshuser"
export SSH_PASSWORD="SecureSSH456"

# Túnel SSH
export LOCAL_TUNNEL_PORT="5433"

# Base de Datos Remota
export REMOTE_DB_HOST="localhost"
export REMOTE_DB_PORT="5432"
export REMOTE_DB_NAME="myapp_remote"
export REMOTE_DB_USER="remote_db_user"
export REMOTE_DB_PASSWORD="RemoteP%40ss789"  # Contraseña: RemoteP@ss789

# Directorios
export LOG_DIR="/var/log/pg_sync"
export CONTROL_DIR="/var/lib/pg_sync"
# Base de Datos Local
export LOCAL_DB_HOST="localhost"
export LOCAL_DB_PORT="5432"
export LOCAL_DB_NAME="myapp_local"
export LOCAL_DB_USER="local_db_user"
export LOCAL_DB_PASSWORD="%24MyP%40ss123"  # Contraseña: $MyP@ss123

# Servidor SSH Remoto
export SSH_HOST="192.168.1.100"
export SSH_PORT="22"
export SSH_USER="sshuser"
export SSH_PASSWORD="SecureSSH456"

# Túnel SSH
export LOCAL_TUNNEL_PORT="5433"

# Base de Datos Remota
export REMOTE_DB_HOST="localhost"
export REMOTE_DB_PORT="5432"
export REMOTE_DB_NAME="myapp_remote"
export REMOTE_DB_USER="remote_db_user"
export REMOTE_DB_PASSWORD="RemoteP%40ss789"  # Contraseña: RemoteP@ss789

# Directorios
export LOG_DIR="/var/log/pg_sync"
export CONTROL_DIR="/var/lib/pg_sync"

Paso 3: Proteger el Archivo de Configuración

bash 
# Dar permisos restrictivos (solo root puede leer/escribir)
sudo chmod 600 /etc/pg_sync/config.env

# Verificar permisos
ls -l /etc/pg_sync/config.env
# Debe mostrar: -rw------- 1 root root
# Dar permisos restrictivos (solo root puede leer/escribir)
sudo chmod 600 /etc/pg_sync/config.env

# Verificar permisos
ls -l /etc/pg_sync/config.env
# Debe mostrar: -rw------- 1 root root

IMPORTANTE: Este archivo contiene contraseñas en texto plano. Los permisos 600 aseguran que solo el usuario root puede accederlo.

Codificar Contraseñas con Caracteres Especiales

Si tu contraseña contiene caracteres especiales, debes usar URL encoding:

Método 1: Herramienta online

Método 2: Comando en terminal

bash 
# Usando Python
python3 -c "import urllib.parse; print(urllib.parse.quote(input('Password: ')))"

# Ejemplo:
# Input: $Pass@123
# Output: %24Pass%40123
# Usando Python
python3 -c "import urllib.parse; print(urllib.parse.quote(input('Password: ')))"

# Ejemplo:
# Input: $Pass@123
# Output: %24Pass%40123

Tabla de referencia rápida:

Carácter Codificado
! %21
@ %40
# %23
$ %24
% %25
^ %5E
& %26
* %2A
( %28
) %29
= %3D
+ %2B
(espacio) %20

El Script de Sincronización

Crear el Script Principal

bash 
sudo nano /opt/pg_sync/pg_sync.sh
sudo nano /opt/pg_sync/pg_sync.sh

Contenido del Script

El script completo implementa las siguientes funcionalidades:

Características principales:

  1. Detección automática de Foreign Keys usando análisis recursivo de dependencias
  2. Sincronización en orden topológico (respeta dependencias)
  3. Case sensitivity perfecto (usa comillas dobles en todos los identificadores)
  4. Solo sincroniza columnas comunes (compatible con esquemas diferentes)
  5. UPSERT inteligente (INSERT o UPDATE según corresponda)
  6. Deshabilita Foreign Keys temporalmente (modo replicación)
  7. Manejo robusto de errores con logs detallados
  8. Limpieza automática de recursos (túnel SSH, archivos temporales)

Script completo (pg_sync.sh):

bash 
#!/bin/bash

#####################################################################
# Script de Sincronización Incremental PostgreSQL
# Con detección automática de dependencias (Foreign Keys)
# Manejo perfecto de case-sensitivity y esquemas diferentes
#####################################################################

# Cargar configuración
if [ -f /etc/pg_sync/config.env ]; then
    source /etc/pg_sync/config.env
else
    echo "ERROR: Archivo de configuración no encontrado: /etc/pg_sync/config.env"
    exit 1
fi

LOG_FILE="$LOG_DIR/sync_$(date +%Y%m%d_%H%M%S).log"
TIMESTAMP_FILE="$CONTROL_DIR/last_sync_timestamp.txt"
PID_FILE="$CONTROL_DIR/ssh_tunnel.pid"
DEPENDENCIES_FILE="$CONTROL_DIR/table_dependencies.txt"

mkdir -p "$LOG_DIR" "$CONTROL_DIR"

# Connection strings
LOCAL_CONN="postgresql://${LOCAL_DB_USER}:${LOCAL_DB_PASSWORD}@${LOCAL_DB_HOST}:${LOCAL_DB_PORT}/${LOCAL_DB_NAME}"
REMOTE_CONN="postgresql://${REMOTE_DB_USER}:${REMOTE_DB_PASSWORD}@localhost:${LOCAL_TUNNEL_PORT}/${REMOTE_DB_NAME}"

# Logging
log() {
    local level="${2:-INFO}"
    local color=""
    
    if [ -t 1 ]; then
        case $level in
            ERROR) color="\033[0;31m" ;;
            SUCCESS) color="\033[0;32m" ;;
            WARNING) color="\033[0;33m" ;;
            *) color="\033[0m" ;;
        esac
    fi
    
    local reset="\033[0m"
    echo -e "${color}[$(date '+%Y-%m-%d %H:%M:%S')] [$level] $1${reset}" | tee -a "$LOG_FILE"
}

cleanup() {
    log "Limpiando recursos..." "INFO"
    if [ -f "$PID_FILE" ]; then
        SSH_PID=$(cat "$PID_FILE")
        if ps -p $SSH_PID > /dev/null 2>&1; then
            kill $SSH_PID
            log "Túnel SSH cerrado (PID: $SSH_PID)" "INFO"
        fi
        rm -f "$PID_FILE"
    fi
}

trap cleanup EXIT INT TERM

check_dependencies() {
    local missing_deps=()
    
    for cmd in psql pg_dump sshpass; do
        if ! command -v $cmd &> /dev/null; then
            missing_deps+=($cmd)
        fi
    done
    
    if [ ${#missing_deps[@]} -gt 0 ]; then
        log "ERROR: Dependencias faltantes: ${missing_deps[*]}" "ERROR"
        log "Instala: sudo apt-get install postgresql-client sshpass" "ERROR"
        exit 1
    fi
}

setup_ssh_tunnel() {
    log "Estableciendo túnel SSH..." "INFO"
    
    if lsof -Pi :${LOCAL_TUNNEL_PORT} -sTCP:LISTEN -t >/dev/null 2>&1; then
        log "Ya existe un proceso en puerto $LOCAL_TUNNEL_PORT" "WARNING"
        LOCAL_TUNNEL_PORT=$((LOCAL_TUNNEL_PORT + 1))
        log "Usando puerto alternativo: $LOCAL_TUNNEL_PORT" "INFO"
        REMOTE_CONN="postgresql://${REMOTE_DB_USER}:${REMOTE_DB_PASSWORD}@localhost:${LOCAL_TUNNEL_PORT}/${REMOTE_DB_NAME}"
    fi
    
    sshpass -p "$SSH_PASSWORD" ssh -f -N \
        -L ${LOCAL_TUNNEL_PORT}:${REMOTE_DB_HOST}:${REMOTE_DB_PORT} \
        -p "$SSH_PORT" \
        -o ServerAliveInterval=60 \
        -o ServerAliveCountMax=3 \
        -o ExitOnForwardFailure=yes \
        -o StrictHostKeyChecking=no \
        ${SSH_USER}@${SSH_HOST}
    
    if [ $? -ne 0 ]; then
        log "ERROR: No se pudo establecer el túnel SSH" "ERROR"
        exit 1
    fi
    
    SSH_PID=$(pgrep -f "ssh.*${LOCAL_TUNNEL_PORT}:${REMOTE_DB_HOST}:${REMOTE_DB_PORT}" | tail -1)
    echo $SSH_PID > "$PID_FILE"
    log "Túnel SSH establecido (PID: $SSH_PID) en puerto $LOCAL_TUNNEL_PORT" "SUCCESS"
    
    sleep 2
}

verify_connections() {
    log "Verificando conexión a base de datos local..." "INFO"
    psql "$LOCAL_CONN" -c "SELECT 1" > /dev/null 2>&1
    
    if [ $? -ne 0 ]; then
        log "ERROR: No se pudo conectar a la base de datos local" "ERROR"
        exit 1
    fi
    log "Conexión local exitosa" "SUCCESS"
    
    log "Verificando conexión a base de datos remota..." "INFO"
    psql "$REMOTE_CONN" -c "SELECT 1" > /dev/null 2>&1
    
    if [ $? -ne 0 ]; then
        log "ERROR: No se pudo conectar a la base de datos remota" "ERROR"
        exit 1
    fi
    log "Conexión remota exitosa" "SUCCESS"
}

get_tables_sorted_by_dependencies() {
    log "Analizando dependencias de tablas (Foreign Keys)..." "INFO"
    
    psql "$LOCAL_CONN" -t -c "
        WITH RECURSIVE fk_tree AS (
            SELECT 
                t.tablename,
                0 as level,
                ARRAY[t.tablename] as path
            FROM pg_tables t
            WHERE t.schemaname = 'public'
            AND NOT EXISTS (
                SELECT 1 
                FROM pg_constraint c
                JOIN pg_class cl ON c.conrelid = cl.oid
                WHERE cl.relname = t.tablename
                AND c.contype = 'f'
            )
            
            UNION ALL
            
            SELECT DISTINCT
                cl.relname as tablename,
                ft.level + 1 as level,
                ft.path || cl.relname as path
            FROM fk_tree ft
            JOIN pg_class ref_cl ON ref_cl.relname = ft.tablename
            JOIN pg_constraint c ON c.confrelid = ref_cl.oid AND c.contype = 'f'
            JOIN pg_class cl ON c.conrelid = cl.oid
            JOIN pg_tables t ON t.tablename = cl.relname AND t.schemaname = 'public'
            WHERE NOT cl.relname = ANY(ft.path)
        )
        SELECT DISTINCT tablename, MAX(level) as max_level
        FROM fk_tree
        GROUP BY tablename
        ORDER BY max_level, tablename;
    " > "$DEPENDENCIES_FILE"
    
    if [ $? -ne 0 ]; then
        log "ERROR: No se pudo analizar dependencias" "ERROR"
        psql "$LOCAL_CONN" -t -c \
            "SELECT tablename FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename" \
            > "$DEPENDENCIES_FILE"
    fi
    
    local max_level=$(awk '{print $2}' "$DEPENDENCIES_FILE" | sort -n | tail -1)
    log "Niveles de dependencia detectados: ${max_level:-0}" "INFO"
    
    awk '{print $1}' "$DEPENDENCIES_FILE"
}

sync_table_with_upsert() {
    local table=$1
    local timestamp_col=$2
    local last_sync=$3
    
    local pk_cols=$(psql "$LOCAL_CONN" -t -c \
        "SELECT string_agg(a.attname, ',' ORDER BY a.attnum)
         FROM pg_index i
         JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
         WHERE i.indrelid = 'public.\"$table\"'::regclass AND i.indisprimary" | xargs)
    
    if [ -z "$pk_cols" ]; then
        log "  Advertencia: Tabla $table no tiene clave primaria" "WARNING"
        return 1
    fi
    
    local pk_cols_quoted=""
    IFS=',' read -ra PK_TEMP <<< "$pk_cols"
    for pk in "${PK_TEMP[@]}"; do
        if [ -n "$pk_cols_quoted" ]; then
            pk_cols_quoted="$pk_cols_quoted, "
        fi
        pk_cols_quoted="${pk_cols_quoted}\"${pk}\""
    done
    
    local local_cols=$(psql "$LOCAL_CONN" -t -c \
        "SELECT string_agg(a.attname, ',' ORDER BY a.attnum)
         FROM pg_attribute a
         JOIN pg_class c ON a.attrelid = c.oid
         JOIN pg_namespace n ON c.relnamespace = n.oid
         WHERE c.relname = '$table' 
         AND n.nspname = 'public'
         AND a.attnum > 0 
         AND NOT a.attisdropped" | xargs)
    
    local remote_cols=$(psql "$REMOTE_CONN" -t -c \
        "SELECT string_agg(a.attname, ',' ORDER BY a.attnum)
         FROM pg_attribute a
         JOIN pg_class c ON a.attrelid = c.oid
         JOIN pg_namespace n ON c.relnamespace = n.oid
         WHERE c.relname = '$table' 
         AND n.nspname = 'public'
         AND a.attnum > 0 
         AND NOT a.attisdropped" | xargs)
    
    if [ -z "$local_cols" ] || [ -z "$remote_cols" ]; then
        log "  ERROR: No se pudieron obtener columnas" "ERROR"
        return 1
    fi
    
    local all_cols=""
    IFS=',' read -ra LOCAL_ARRAY <<< "$local_cols"
    IFS=',' read -ra REMOTE_ARRAY <<< "$remote_cols"
    
    for local_col in "${LOCAL_ARRAY[@]}"; do
        for remote_col in "${REMOTE_ARRAY[@]}"; do
            if [ "$local_col" = "$remote_col" ]; then
                if [ -n "$all_cols" ]; then
                    all_cols="$all_cols,"
                fi
                all_cols="${all_cols}${local_col}"
                break
            fi
        done
    done
    
    if [ -z "$all_cols" ]; then
        log "  ERROR: No hay columnas comunes" "ERROR"
        return 1
    fi
    
    local all_cols_quoted=""
    IFS=',' read -ra COLS_ARRAY <<< "$all_cols"
    for col in "${COLS_ARRAY[@]}"; do
        if [ -n "$all_cols_quoted" ]; then
            all_cols_quoted="$all_cols_quoted,"
        fi
        all_cols_quoted="${all_cols_quoted}\"${col}\""
    done
    
    local local_count=$(echo "$local_cols" | tr ',' '\n' | wc -l)
    local common_count=$(echo "$all_cols" | tr ',' '\n' | wc -l)
    local excluded_count=$((local_count - common_count))
    
    if [ $excluded_count -gt 0 ]; then
        log "  Advertencia: $excluded_count columnas omitidas (no existen en remoto)" "WARNING"
    fi
    
    log "  Columnas comunes a sincronizar: $common_count" "INFO"
    
    local temp_csv="/tmp/pg_sync_${table}_$(date +%s).csv"
    
    psql "$LOCAL_CONN" > "$temp_csv" <<COPY_SQL
COPY (SELECT $all_cols_quoted FROM public."$table" WHERE "$timestamp_col" > '$last_sync'::timestamp) 
TO STDOUT WITH CSV HEADER;
COPY_SQL
    
    if [ ! -s "$temp_csv" ]; then
        log "  No hay cambios para sincronizar" "INFO"
        rm -f "$temp_csv"
        return 0
    fi
    
    local rows_to_sync=$(wc -l < "$temp_csv")
    rows_to_sync=$((rows_to_sync - 1))
    
    if [ $rows_to_sync -eq 0 ]; then
        log "  No hay cambios para sincronizar" "INFO"
        rm -f "$temp_csv"
        return 0
    fi
    
    log "  Sincronizando $rows_to_sync registros..." "INFO"
    
    local temp_table="temp_${table}_$(date +%s)"
    
    IFS=',' read -ra PK_ARRAY <<< "$pk_cols"
    IFS=',' read -ra ALL_COLS_ARRAY <<< "$all_cols"
    
    local update_set=""
    for col in "${ALL_COLS_ARRAY[@]}"; do
        local is_pk=false
        for pk in "${PK_ARRAY[@]}"; do
            if [ "$col" = "$pk" ]; then
                is_pk=true
                break
            fi
        done
        if [ "$is_pk" = false ]; then
            if [ -n "$update_set" ]; then
                update_set="$update_set, "
            fi
            update_set="${update_set}\"${col}\" = EXCLUDED.\"${col}\""
        fi
    done
    
    psql "$REMOTE_CONN" -v ON_ERROR_STOP=1 <<EOF >> "$LOG_FILE" 2>&1
        SET session_replication_role = replica;
        
        CREATE TEMP TABLE $temp_table AS SELECT $all_cols_quoted FROM public."$table" WHERE 1=0;
        
        \copy $temp_table ($all_cols_quoted) FROM '$temp_csv' WITH CSV HEADER
        
        INSERT INTO public."$table" ($all_cols_quoted)
        SELECT $all_cols_quoted FROM $temp_table
        ON CONFLICT ($pk_cols_quoted) DO UPDATE SET $update_set;
        
        SET session_replication_role = DEFAULT;
        
        DROP TABLE $temp_table;
EOF
    
    local psql_exit_code=$?
    
    if [ $psql_exit_code -eq 0 ]; then
        log "  [OK] UPSERT exitoso: $rows_to_sync registros" "SUCCESS"
        rm -f "$temp_csv"
        return 0
    else
        log "  [ERROR] ERROR en UPSERT (código: $psql_exit_code)" "ERROR"
        tail -10 "$LOG_FILE" | grep -E "(ERROR|FATAL)" | while read line; do
            log "    $line" "ERROR"
        done
        rm -f "$temp_csv"
        return 1
    fi
}

# INICIO DEL SCRIPT
log "==========================================" "INFO"
log "Sincronización Incremental PostgreSQL" "INFO"
log "Con manejo automático de Foreign Keys" "INFO"
log "==========================================" "INFO"

check_dependencies
setup_ssh_tunnel
verify_connections

if [ -f "$TIMESTAMP_FILE" ]; then
    LAST_SYNC=$(cat "$TIMESTAMP_FILE")
    log "Última sincronización: $LAST_SYNC" "INFO"
else
    LAST_SYNC="1970-01-01 00:00:00"
    log "Primera sincronización" "INFO"
fi

CURRENT_SYNC=$(date '+%Y-%m-%d %H:%M:%S')

log "Obteniendo tablas ordenadas por dependencias..." "INFO"
TABLES=$(get_tables_sorted_by_dependencies)

if [ -z "$TABLES" ]; then
    log "ERROR: No se encontraron tablas" "ERROR"
    exit 1
fi

TOTAL_TABLES=0
SUCCESS_TABLES=0
ERROR_TABLES=0

for TABLE in $TABLES; do
    TOTAL_TABLES=$((TOTAL_TABLES + 1))
    log "Procesando tabla: $TABLE" "INFO"
    
    TIMESTAMP_COLUMN=$(psql "$LOCAL_CONN" -t -c \
        "SELECT column_name FROM information_schema.columns 
         WHERE table_name = '$TABLE' AND table_schema = 'public'
         AND data_type IN ('timestamp', 'timestamp with time zone', 'timestamp without time zone')
         ORDER BY CASE 
             WHEN column_name = 'updated_at' THEN 1
             WHEN column_name = 'modified_at' THEN 2
             WHEN column_name = 'updated' THEN 3
             ELSE 4
         END
         LIMIT 1" | xargs)
    
    if [ -z "$TIMESTAMP_COLUMN" ]; then
        log "  Advertencia: No hay columna de timestamp, se omite" "WARNING"
        log "---" "INFO"
        continue
    fi
    
    log "  Columna de timestamp: $TIMESTAMP_COLUMN" "INFO"
    
    if sync_table_with_upsert "$TABLE" "$TIMESTAMP_COLUMN" "$LAST_SYNC"; then
        SUCCESS_TABLES=$((SUCCESS_TABLES + 1))
    else
        ERROR_TABLES=$((ERROR_TABLES + 1))
    fi
    
    log "---" "INFO"
done

if [ $ERROR_TABLES -eq 0 ]; then
    echo "$CURRENT_SYNC" > "$TIMESTAMP_FILE"
    log "Timestamp guardado: $CURRENT_SYNC" "SUCCESS"
fi

log "==========================================" "INFO"
log "Sincronización completada" "INFO"
log "Total de tablas: $TOTAL_TABLES" "INFO"
log "Exitosas: $SUCCESS_TABLES" "SUCCESS"
log "Con errores: $ERROR_TABLES" $([ $ERROR_TABLES -gt 0 ] && echo "ERROR" || echo "INFO")
log "==========================================" "INFO"

exit $ERROR_TABLES
#!/bin/bash

#####################################################################
# Script de Sincronización Incremental PostgreSQL
# Con detección automática de dependencias (Foreign Keys)
# Manejo perfecto de case-sensitivity y esquemas diferentes
#####################################################################

# Cargar configuración
if [ -f /etc/pg_sync/config.env ]; then
    source /etc/pg_sync/config.env
else
    echo "ERROR: Archivo de configuración no encontrado: /etc/pg_sync/config.env"
    exit 1
fi

LOG_FILE="$LOG_DIR/sync_$(date +%Y%m%d_%H%M%S).log"
TIMESTAMP_FILE="$CONTROL_DIR/last_sync_timestamp.txt"
PID_FILE="$CONTROL_DIR/ssh_tunnel.pid"
DEPENDENCIES_FILE="$CONTROL_DIR/table_dependencies.txt"

mkdir -p "$LOG_DIR" "$CONTROL_DIR"

# Connection strings
LOCAL_CONN="postgresql://${LOCAL_DB_USER}:${LOCAL_DB_PASSWORD}@${LOCAL_DB_HOST}:${LOCAL_DB_PORT}/${LOCAL_DB_NAME}"
REMOTE_CONN="postgresql://${REMOTE_DB_USER}:${REMOTE_DB_PASSWORD}@localhost:${LOCAL_TUNNEL_PORT}/${REMOTE_DB_NAME}"

# Logging
log() {
    local level="${2:-INFO}"
    local color=""
    
    if [ -t 1 ]; then
        case $level in
            ERROR) color="\033[0;31m" ;;
            SUCCESS) color="\033[0;32m" ;;
            WARNING) color="\033[0;33m" ;;
            *) color="\033[0m" ;;
        esac
    fi
    
    local reset="\033[0m"
    echo -e "${color}[$(date '+%Y-%m-%d %H:%M:%S')] [$level] $1${reset}" | tee -a "$LOG_FILE"
}

cleanup() {
    log "Limpiando recursos..." "INFO"
    if [ -f "$PID_FILE" ]; then
        SSH_PID=$(cat "$PID_FILE")
        if ps -p $SSH_PID > /dev/null 2>&1; then
            kill $SSH_PID
            log "Túnel SSH cerrado (PID: $SSH_PID)" "INFO"
        fi
        rm -f "$PID_FILE"
    fi
}

trap cleanup EXIT INT TERM

check_dependencies() {
    local missing_deps=()
    
    for cmd in psql pg_dump sshpass; do
        if ! command -v $cmd &> /dev/null; then
            missing_deps+=($cmd)
        fi
    done
    
    if [ ${#missing_deps[@]} -gt 0 ]; then
        log "ERROR: Dependencias faltantes: ${missing_deps[*]}" "ERROR"
        log "Instala: sudo apt-get install postgresql-client sshpass" "ERROR"
        exit 1
    fi
}

setup_ssh_tunnel() {
    log "Estableciendo túnel SSH..." "INFO"
    
    if lsof -Pi :${LOCAL_TUNNEL_PORT} -sTCP:LISTEN -t >/dev/null 2>&1; then
        log "Ya existe un proceso en puerto $LOCAL_TUNNEL_PORT" "WARNING"
        LOCAL_TUNNEL_PORT=$((LOCAL_TUNNEL_PORT + 1))
        log "Usando puerto alternativo: $LOCAL_TUNNEL_PORT" "INFO"
        REMOTE_CONN="postgresql://${REMOTE_DB_USER}:${REMOTE_DB_PASSWORD}@localhost:${LOCAL_TUNNEL_PORT}/${REMOTE_DB_NAME}"
    fi
    
    sshpass -p "$SSH_PASSWORD" ssh -f -N \
        -L ${LOCAL_TUNNEL_PORT}:${REMOTE_DB_HOST}:${REMOTE_DB_PORT} \
        -p "$SSH_PORT" \
        -o ServerAliveInterval=60 \
        -o ServerAliveCountMax=3 \
        -o ExitOnForwardFailure=yes \
        -o StrictHostKeyChecking=no \
        ${SSH_USER}@${SSH_HOST}
    
    if [ $? -ne 0 ]; then
        log "ERROR: No se pudo establecer el túnel SSH" "ERROR"
        exit 1
    fi
    
    SSH_PID=$(pgrep -f "ssh.*${LOCAL_TUNNEL_PORT}:${REMOTE_DB_HOST}:${REMOTE_DB_PORT}" | tail -1)
    echo $SSH_PID > "$PID_FILE"
    log "Túnel SSH establecido (PID: $SSH_PID) en puerto $LOCAL_TUNNEL_PORT" "SUCCESS"
    
    sleep 2
}

verify_connections() {
    log "Verificando conexión a base de datos local..." "INFO"
    psql "$LOCAL_CONN" -c "SELECT 1" > /dev/null 2>&1
    
    if [ $? -ne 0 ]; then
        log "ERROR: No se pudo conectar a la base de datos local" "ERROR"
        exit 1
    fi
    log "Conexión local exitosa" "SUCCESS"
    
    log "Verificando conexión a base de datos remota..." "INFO"
    psql "$REMOTE_CONN" -c "SELECT 1" > /dev/null 2>&1
    
    if [ $? -ne 0 ]; then
        log "ERROR: No se pudo conectar a la base de datos remota" "ERROR"
        exit 1
    fi
    log "Conexión remota exitosa" "SUCCESS"
}

get_tables_sorted_by_dependencies() {
    log "Analizando dependencias de tablas (Foreign Keys)..." "INFO"
    
    psql "$LOCAL_CONN" -t -c "
        WITH RECURSIVE fk_tree AS (
            SELECT 
                t.tablename,
                0 as level,
                ARRAY[t.tablename] as path
            FROM pg_tables t
            WHERE t.schemaname = 'public'
            AND NOT EXISTS (
                SELECT 1 
                FROM pg_constraint c
                JOIN pg_class cl ON c.conrelid = cl.oid
                WHERE cl.relname = t.tablename
                AND c.contype = 'f'
            )
            
            UNION ALL
            
            SELECT DISTINCT
                cl.relname as tablename,
                ft.level + 1 as level,
                ft.path || cl.relname as path
            FROM fk_tree ft
            JOIN pg_class ref_cl ON ref_cl.relname = ft.tablename
            JOIN pg_constraint c ON c.confrelid = ref_cl.oid AND c.contype = 'f'
            JOIN pg_class cl ON c.conrelid = cl.oid
            JOIN pg_tables t ON t.tablename = cl.relname AND t.schemaname = 'public'
            WHERE NOT cl.relname = ANY(ft.path)
        )
        SELECT DISTINCT tablename, MAX(level) as max_level
        FROM fk_tree
        GROUP BY tablename
        ORDER BY max_level, tablename;
    " > "$DEPENDENCIES_FILE"
    
    if [ $? -ne 0 ]; then
        log "ERROR: No se pudo analizar dependencias" "ERROR"
        psql "$LOCAL_CONN" -t -c \
            "SELECT tablename FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename" \
            > "$DEPENDENCIES_FILE"
    fi
    
    local max_level=$(awk '{print $2}' "$DEPENDENCIES_FILE" | sort -n | tail -1)
    log "Niveles de dependencia detectados: ${max_level:-0}" "INFO"
    
    awk '{print $1}' "$DEPENDENCIES_FILE"
}

sync_table_with_upsert() {
    local table=$1
    local timestamp_col=$2
    local last_sync=$3
    
    local pk_cols=$(psql "$LOCAL_CONN" -t -c \
        "SELECT string_agg(a.attname, ',' ORDER BY a.attnum)
         FROM pg_index i
         JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
         WHERE i.indrelid = 'public.\"$table\"'::regclass AND i.indisprimary" | xargs)
    
    if [ -z "$pk_cols" ]; then
        log "  Advertencia: Tabla $table no tiene clave primaria" "WARNING"
        return 1
    fi
    
    local pk_cols_quoted=""
    IFS=',' read -ra PK_TEMP <<< "$pk_cols"
    for pk in "${PK_TEMP[@]}"; do
        if [ -n "$pk_cols_quoted" ]; then
            pk_cols_quoted="$pk_cols_quoted, "
        fi
        pk_cols_quoted="${pk_cols_quoted}\"${pk}\""
    done
    
    local local_cols=$(psql "$LOCAL_CONN" -t -c \
        "SELECT string_agg(a.attname, ',' ORDER BY a.attnum)
         FROM pg_attribute a
         JOIN pg_class c ON a.attrelid = c.oid
         JOIN pg_namespace n ON c.relnamespace = n.oid
         WHERE c.relname = '$table' 
         AND n.nspname = 'public'
         AND a.attnum > 0 
         AND NOT a.attisdropped" | xargs)
    
    local remote_cols=$(psql "$REMOTE_CONN" -t -c \
        "SELECT string_agg(a.attname, ',' ORDER BY a.attnum)
         FROM pg_attribute a
         JOIN pg_class c ON a.attrelid = c.oid
         JOIN pg_namespace n ON c.relnamespace = n.oid
         WHERE c.relname = '$table' 
         AND n.nspname = 'public'
         AND a.attnum > 0 
         AND NOT a.attisdropped" | xargs)
    
    if [ -z "$local_cols" ] || [ -z "$remote_cols" ]; then
        log "  ERROR: No se pudieron obtener columnas" "ERROR"
        return 1
    fi
    
    local all_cols=""
    IFS=',' read -ra LOCAL_ARRAY <<< "$local_cols"
    IFS=',' read -ra REMOTE_ARRAY <<< "$remote_cols"
    
    for local_col in "${LOCAL_ARRAY[@]}"; do
        for remote_col in "${REMOTE_ARRAY[@]}"; do
            if [ "$local_col" = "$remote_col" ]; then
                if [ -n "$all_cols" ]; then
                    all_cols="$all_cols,"
                fi
                all_cols="${all_cols}${local_col}"
                break
            fi
        done
    done
    
    if [ -z "$all_cols" ]; then
        log "  ERROR: No hay columnas comunes" "ERROR"
        return 1
    fi
    
    local all_cols_quoted=""
    IFS=',' read -ra COLS_ARRAY <<< "$all_cols"
    for col in "${COLS_ARRAY[@]}"; do
        if [ -n "$all_cols_quoted" ]; then
            all_cols_quoted="$all_cols_quoted,"
        fi
        all_cols_quoted="${all_cols_quoted}\"${col}\""
    done
    
    local local_count=$(echo "$local_cols" | tr ',' '\n' | wc -l)
    local common_count=$(echo "$all_cols" | tr ',' '\n' | wc -l)
    local excluded_count=$((local_count - common_count))
    
    if [ $excluded_count -gt 0 ]; then
        log "  Advertencia: $excluded_count columnas omitidas (no existen en remoto)" "WARNING"
    fi
    
    log "  Columnas comunes a sincronizar: $common_count" "INFO"
    
    local temp_csv="/tmp/pg_sync_${table}_$(date +%s).csv"
    
    psql "$LOCAL_CONN" > "$temp_csv" <<COPY_SQL
COPY (SELECT $all_cols_quoted FROM public."$table" WHERE "$timestamp_col" > '$last_sync'::timestamp) 
TO STDOUT WITH CSV HEADER;
COPY_SQL
    
    if [ ! -s "$temp_csv" ]; then
        log "  No hay cambios para sincronizar" "INFO"
        rm -f "$temp_csv"
        return 0
    fi
    
    local rows_to_sync=$(wc -l < "$temp_csv")
    rows_to_sync=$((rows_to_sync - 1))
    
    if [ $rows_to_sync -eq 0 ]; then
        log "  No hay cambios para sincronizar" "INFO"
        rm -f "$temp_csv"
        return 0
    fi
    
    log "  Sincronizando $rows_to_sync registros..." "INFO"
    
    local temp_table="temp_${table}_$(date +%s)"
    
    IFS=',' read -ra PK_ARRAY <<< "$pk_cols"
    IFS=',' read -ra ALL_COLS_ARRAY <<< "$all_cols"
    
    local update_set=""
    for col in "${ALL_COLS_ARRAY[@]}"; do
        local is_pk=false
        for pk in "${PK_ARRAY[@]}"; do
            if [ "$col" = "$pk" ]; then
                is_pk=true
                break
            fi
        done
        if [ "$is_pk" = false ]; then
            if [ -n "$update_set" ]; then
                update_set="$update_set, "
            fi
            update_set="${update_set}\"${col}\" = EXCLUDED.\"${col}\""
        fi
    done
    
    psql "$REMOTE_CONN" -v ON_ERROR_STOP=1 <<EOF >> "$LOG_FILE" 2>&1
        SET session_replication_role = replica;
        
        CREATE TEMP TABLE $temp_table AS SELECT $all_cols_quoted FROM public."$table" WHERE 1=0;
        
        \copy $temp_table ($all_cols_quoted) FROM '$temp_csv' WITH CSV HEADER
        
        INSERT INTO public."$table" ($all_cols_quoted)
        SELECT $all_cols_quoted FROM $temp_table
        ON CONFLICT ($pk_cols_quoted) DO UPDATE SET $update_set;
        
        SET session_replication_role = DEFAULT;
        
        DROP TABLE $temp_table;
EOF
    
    local psql_exit_code=$?
    
    if [ $psql_exit_code -eq 0 ]; then
        log "  [OK] UPSERT exitoso: $rows_to_sync registros" "SUCCESS"
        rm -f "$temp_csv"
        return 0
    else
        log "  [ERROR] ERROR en UPSERT (código: $psql_exit_code)" "ERROR"
        tail -10 "$LOG_FILE" | grep -E "(ERROR|FATAL)" | while read line; do
            log "    $line" "ERROR"
        done
        rm -f "$temp_csv"
        return 1
    fi
}

# INICIO DEL SCRIPT
log "==========================================" "INFO"
log "Sincronización Incremental PostgreSQL" "INFO"
log "Con manejo automático de Foreign Keys" "INFO"
log "==========================================" "INFO"

check_dependencies
setup_ssh_tunnel
verify_connections

if [ -f "$TIMESTAMP_FILE" ]; then
    LAST_SYNC=$(cat "$TIMESTAMP_FILE")
    log "Última sincronización: $LAST_SYNC" "INFO"
else
    LAST_SYNC="1970-01-01 00:00:00"
    log "Primera sincronización" "INFO"
fi

CURRENT_SYNC=$(date '+%Y-%m-%d %H:%M:%S')

log "Obteniendo tablas ordenadas por dependencias..." "INFO"
TABLES=$(get_tables_sorted_by_dependencies)

if [ -z "$TABLES" ]; then
    log "ERROR: No se encontraron tablas" "ERROR"
    exit 1
fi

TOTAL_TABLES=0
SUCCESS_TABLES=0
ERROR_TABLES=0

for TABLE in $TABLES; do
    TOTAL_TABLES=$((TOTAL_TABLES + 1))
    log "Procesando tabla: $TABLE" "INFO"
    
    TIMESTAMP_COLUMN=$(psql "$LOCAL_CONN" -t -c \
        "SELECT column_name FROM information_schema.columns 
         WHERE table_name = '$TABLE' AND table_schema = 'public'
         AND data_type IN ('timestamp', 'timestamp with time zone', 'timestamp without time zone')
         ORDER BY CASE 
             WHEN column_name = 'updated_at' THEN 1
             WHEN column_name = 'modified_at' THEN 2
             WHEN column_name = 'updated' THEN 3
             ELSE 4
         END
         LIMIT 1" | xargs)
    
    if [ -z "$TIMESTAMP_COLUMN" ]; then
        log "  Advertencia: No hay columna de timestamp, se omite" "WARNING"
        log "---" "INFO"
        continue
    fi
    
    log "  Columna de timestamp: $TIMESTAMP_COLUMN" "INFO"
    
    if sync_table_with_upsert "$TABLE" "$TIMESTAMP_COLUMN" "$LAST_SYNC"; then
        SUCCESS_TABLES=$((SUCCESS_TABLES + 1))
    else
        ERROR_TABLES=$((ERROR_TABLES + 1))
    fi
    
    log "---" "INFO"
done

if [ $ERROR_TABLES -eq 0 ]; then
    echo "$CURRENT_SYNC" > "$TIMESTAMP_FILE"
    log "Timestamp guardado: $CURRENT_SYNC" "SUCCESS"
fi

log "==========================================" "INFO"
log "Sincronización completada" "INFO"
log "Total de tablas: $TOTAL_TABLES" "INFO"
log "Exitosas: $SUCCESS_TABLES" "SUCCESS"
log "Con errores: $ERROR_TABLES" $([ $ERROR_TABLES -gt 0 ] && echo "ERROR" || echo "INFO")
log "==========================================" "INFO"

exit $ERROR_TABLES

Dar Permisos de Ejecución

bash 
# Hacer el script ejecutable
sudo chmod +x /opt/pg_sync/pg_sync.sh

# Verificar permisos
ls -l /opt/pg_sync/pg_sync.sh
# Debe mostrar: -rwxr-xr-x
# Hacer el script ejecutable
sudo chmod +x /opt/pg_sync/pg_sync.sh

# Verificar permisos
ls -l /opt/pg_sync/pg_sync.sh
# Debe mostrar: -rwxr-xr-x

Explicación de permisos:

  • rwx (propietario): lectura, escritura, ejecución
  • r-x (grupo): lectura, ejecución (sin escritura)
  • r-x (otros): lectura, ejecución (sin escritura)

Prueba Manual

bash 
# Ejecutar el script manualmente
sudo bash /opt/pg_sync/pg_sync.sh

# Ver la salida en tiempo real
# Debe mostrar algo como:
# [2025-12-31 10:00:00] [INFO] ==========================================
# [2025-12-31 10:00:00] [INFO] Sincronización Incremental PostgreSQL
# [2025-12-31 10:00:00] [SUCCESS] Túnel SSH establecido
# [2025-12-31 10:00:00] [SUCCESS] Conexión local exitosa
# ...
# Ejecutar el script manualmente
sudo bash /opt/pg_sync/pg_sync.sh

# Ver la salida en tiempo real
# Debe mostrar algo como:
# [2025-12-31 10:00:00] [INFO] ==========================================
# [2025-12-31 10:00:00] [INFO] Sincronización Incremental PostgreSQL
# [2025-12-31 10:00:00] [SUCCESS] Túnel SSH establecido
# [2025-12-31 10:00:00] [SUCCESS] Conexión local exitosa
# ...

Posibles salidas:

Ejecución exitosa:

plaintext 
[SUCCESS] Total de tablas: 145
[SUCCESS] Exitosas: 145
[INFO] Con errores: 0
[SUCCESS] Total de tablas: 145
[SUCCESS] Exitosas: 145
[INFO] Con errores: 0

Con advertencias (normal si esquemas son diferentes):

plaintext 
[WARNING] Advertencia: 2 columnas omitidas (no existen en remoto)
[SUCCESS] UPSERT exitoso: 100 registros
[WARNING] Advertencia: 2 columnas omitidas (no existen en remoto)
[SUCCESS] UPSERT exitoso: 100 registros

Con errores:

plaintext 
[ERROR] No se pudo conectar a la base de datos local
[ERROR] No se pudo conectar a la base de datos local

Automatización con CRON

¿Qué es CRON?

CRON es un servicio de Linux que ejecuta comandos o scripts automáticamente en horarios programados.

Componentes:

  • cron daemon: Servicio que se ejecuta en segundo plano
  • crontab: Tabla de tareas programadas para un usuario
  • cron jobs: Tareas individuales programadas

Formato de CRON

plaintext 
┌─────── minuto (0-59)
│ ┌───── hora (0-23)
│ │ ┌─── día del mes (1-31)
│ │ │ ┌─ mes (1-12)
│ │ │ │ ┌ día de la semana (0-7, donde 0 y 7 = domingo)
│ │ │ │ │
* * * * * comando_a_ejecutar
┌─────── minuto (0-59)
│ ┌───── hora (0-23)
│ │ ┌─── día del mes (1-31)
│ │ │ ┌─ mes (1-12)
│ │ │ │ ┌ día de la semana (0-7, donde 0 y 7 = domingo)
│ │ │ │ │
* * * * * comando_a_ejecutar

Ejemplos comunes:

Expresión Significado
0 * * * * Cada hora en punto
30 2 * * * Diariamente a las 2:30 AM
0 9-17 * * * Cada hora de 9 AM a 5 PM
*/15 * * * * Cada 15 minutos
0 0 * * 0 Cada domingo a medianoche
0 8 1 * * Primer día de cada mes a las 8 AM

Configurar CRON para el Script

Paso 1: Editar crontab

bash 
sudo crontab -e
sudo crontab -e

Nota: La primera vez que ejecutes este comando, te preguntará qué editor usar:

plaintext 
Select an editor. To change later, run 'select-editor'.
  1. /bin/nano        <---- easiest
  2. /usr/bin/vim.basic
  3. /usr/bin/vim.tiny

Choose 1-3 [1]:
Select an editor. To change later, run 'select-editor'.
  1. /bin/nano        <---- easiest
  2. /usr/bin/vim.basic
  3. /usr/bin/vim.tiny

Choose 1-3 [1]:

Recomendación: Selecciona 1 (nano) si no estás familiarizado con vim.

Paso 2: Agregar las tareas programadas

Para ejecutar a las 7:50 AM, 12:10 PM y 4:30 PM diariamente:

# Sincronización PostgreSQL - 3 veces al día
50 7 * * * /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1
10 12 * * * /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1
30 16 * * * /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1

Desglose de la línea:

plaintext 
30 16 * * * /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1
│  │  │ │ │  │                        │
│  │  │ │ │  │                        └─ Redirigir stdout y stderr al log
│  │  │ │ │  └─ Script a ejecutar
│  │  │ │ └─ Día de la semana: * (todos)
│  │  │ └─ Mes: * (todos)
│  │  └─ Día del mes: * (todos)
│  └─ Hora: 16 (4:00 PM)
└─ Minuto: 30
30 16 * * * /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1
│  │  │ │ │  │                        │
│  │  │ │ │  │                        └─ Redirigir stdout y stderr al log
│  │  │ │ │  └─ Script a ejecutar
│  │  │ │ └─ Día de la semana: * (todos)
│  │  │ └─ Mes: * (todos)
│  │  └─ Día del mes: * (todos)
│  └─ Hora: 16 (4:00 PM)
└─ Minuto: 30

Significado de >> /var/log/pg_sync/cron.log 2>&1:

  • >>: Anexar salida al archivo (no sobrescribir)
  • 2>&1: Redirigir stderr (canal 2) a stdout (canal 1)
  • Resultado: Todo el output (normal y errores) se guarda en cron.log

Paso 3: Guardar y salir

En nano (editor recomendado):

  1. Presiona Ctrl + O para guardar
  2. Presiona Enter para confirmar el nombre del archivo
  3. Presiona Ctrl + X para salir

En vim (si lo seleccionaste):

  1. Presiona Esc para entrar en modo comando
  2. Escribe :wq! y presiona Enter

Salida esperada:

plaintext 
crontab: installing new crontab
crontab: installing new crontab

Paso 4: Verificar configuración

bash 
# Ver las tareas CRON configuradas
sudo crontab -l

# Debe mostrar las 3 líneas agregadas
# Ver las tareas CRON configuradas
sudo crontab -l

# Debe mostrar las 3 líneas agregadas

Verificar Estado del Servicio CRON

bash 
# Ver si CRON está corriendo
sudo systemctl status cron

# Salida esperada:
# ● cron.service - Regular background program processing daemon
#    Loaded: loaded (/lib/systemd/system/cron.service; enabled; vendor preset: enabled)
#    Active: active (running) since Mon 2025-12-30 08:00:00 UTC; 2h ago
# Ver si CRON está corriendo
sudo systemctl status cron

# Salida esperada:
# ● cron.service - Regular background program processing daemon
#    Loaded: loaded (/lib/systemd/system/cron.service; enabled; vendor preset: enabled)
#    Active: active (running) since Mon 2025-12-30 08:00:00 UTC; 2h ago

Estados posibles:

Estado Significado Acción
active (running) CRON está funcionando correctamente Todo bien
inactive (dead) CRON no está corriendo sudo systemctl start cron
failed CRON falló al iniciar Ver logs: journalctl -u cron

Comandos útiles:

bash 
# Iniciar CRON
sudo systemctl start cron

# Detener CRON
sudo systemctl stop cron

# Reiniciar CRON
sudo systemctl restart cron

# Habilitar CRON al inicio del sistema
sudo systemctl enable cron
# Iniciar CRON
sudo systemctl start cron

# Detener CRON
sudo systemctl stop cron

# Reiniciar CRON
sudo systemctl restart cron

# Habilitar CRON al inicio del sistema
sudo systemctl enable cron

Horarios Alternativos

Solo días laborables (lunes a viernes)

50 7 * * 1-5 /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1
10 12 * * 1-5 /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1
30 16 * * 1-5 /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1

Cada 4 horas

0 */4 * * * /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1

Solo fines de semana

50 7 * * 0,6 /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1
10 12 * * 0,6 /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1
30 16 * * 0,6 /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1

Monitoreo y Mantenimiento

Ver Logs de Ejecución

Log individual más reciente

bash 
# Ver último log completo
ls -t /var/log/pg_sync/sync_*.log | head -1 | xargs cat

# Ver solo las últimas 50 líneas
ls -t /var/log/pg_sync/sync_*.log | head -1 | xargs tail -50

# Ver solo el resumen final
ls -t /var/log/pg_sync/sync_*.log | head -1 | xargs tail -20
# Ver último log completo
ls -t /var/log/pg_sync/sync_*.log | head -1 | xargs cat

# Ver solo las últimas 50 líneas
ls -t /var/log/pg_sync/sync_*.log | head -1 | xargs tail -50

# Ver solo el resumen final
ls -t /var/log/pg_sync/sync_*.log | head -1 | xargs tail -20

Log agregado de CRON

bash 
# Ver log de CRON en tiempo real
tail -f /var/log/pg_sync/cron.log

# Ver últimas 100 líneas
tail -100 /var/log/pg_sync/cron.log

# Ver ejecuciones de hoy
grep "$(date +%Y-%m-%d)" /var/log/pg_sync/cron.log
# Ver log de CRON en tiempo real
tail -f /var/log/pg_sync/cron.log

# Ver últimas 100 líneas
tail -100 /var/log/pg_sync/cron.log

# Ver ejecuciones de hoy
grep "$(date +%Y-%m-%d)" /var/log/pg_sync/cron.log

Buscar errores

bash 
# Ver todos los errores
grep ERROR /var/log/pg_sync/sync_*.log

# Ver errores de hoy
grep ERROR /var/log/pg_sync/sync_$(date +%Y%m%d)*.log

# Ver últimos 20 errores
grep ERROR /var/log/pg_sync/sync_*.log | tail -20
# Ver todos los errores
grep ERROR /var/log/pg_sync/sync_*.log

# Ver errores de hoy
grep ERROR /var/log/pg_sync/sync_$(date +%Y%m%d)*.log

# Ver últimos 20 errores
grep ERROR /var/log/pg_sync/sync_*.log | tail -20

Ver tablas sincronizadas exitosamente

bash 
# Ver todas las sincronizaciones exitosas
grep "UPSERT exitoso" /var/log/pg_sync/sync_*.log

# Contar tablas sincronizadas en última ejecución
ls -t /var/log/pg_sync/sync_*.log | head -1 | xargs grep -c "UPSERT exitoso"

# Ver estadísticas de última sincronización
ls -t /var/log/pg_sync/sync_*.log | head -1 | xargs grep -A 3 "Sincronización completada"
# Ver todas las sincronizaciones exitosas
grep "UPSERT exitoso" /var/log/pg_sync/sync_*.log

# Contar tablas sincronizadas en última ejecución
ls -t /var/log/pg_sync/sync_*.log | head -1 | xargs grep -c "UPSERT exitoso"

# Ver estadísticas de última sincronización
ls -t /var/log/pg_sync/sync_*.log | head -1 | xargs grep -A 3 "Sincronización completada"

Estadísticas de Ejecución

Script de estadísticas

Crear un script helper:

bash 
sudo nano /opt/pg_sync/stats.sh
sudo nano /opt/pg_sync/stats.sh

Contenido:

bash 
#!/bin/bash

echo "=========================================="
echo "Estadísticas de Sincronización PostgreSQL"
echo "=========================================="
echo ""

# Total de ejecuciones
TOTAL=$(ls /var/log/pg_sync/sync_*.log 2>/dev/null | wc -l)
echo "Total de ejecuciones: $TOTAL"

# Ejecuciones exitosas
SUCCESS=$(grep -l "Con errores: 0" /var/log/pg_sync/sync_*.log 2>/dev/null | wc -l)
echo "Ejecuciones exitosas: $SUCCESS"

# Ejecuciones con errores
ERRORS=$((TOTAL - SUCCESS))
echo "Ejecuciones con errores: $ERRORS"

# Tasa de éxito
if [ $TOTAL -gt 0 ]; then
    RATE=$(awk "BEGIN {printf \"%.2f\", ($SUCCESS/$TOTAL)*100}")
    echo "Tasa de éxito: ${RATE}%"
fi

echo ""

# Última ejecución
LAST_LOG=$(ls -t /var/log/pg_sync/sync_*.log 2>/dev/null | head -1)
if [ -n "$LAST_LOG" ]; then
    echo "Última ejecución:"
    echo "  Archivo: $(basename $LAST_LOG)"
    echo "  Fecha: $(echo $LAST_LOG | sed 's/.*sync_//;s/.log//' | sed 's/_/ / ; s/_/:/')"
    
    # Extraer estadísticas
    grep "Total de tablas:" $LAST_LOG | tail -1
    grep "Exitosas:" $LAST_LOG | tail -1
    grep "Con errores:" $LAST_LOG | tail -1
fi

echo ""

# Ejecuciones de hoy
TODAY_COUNT=$(ls /var/log/pg_sync/sync_$(date +%Y%m%d)*.log 2>/dev/null | wc -l)
echo "Ejecuciones hoy: $TODAY_COUNT"

# Tamaño total de logs
TOTAL_SIZE=$(du -sh /var/log/pg_sync 2>/dev/null | cut -f1)
echo "Tamaño total de logs: $TOTAL_SIZE"

echo "=========================================="
#!/bin/bash

echo "=========================================="
echo "Estadísticas de Sincronización PostgreSQL"
echo "=========================================="
echo ""

# Total de ejecuciones
TOTAL=$(ls /var/log/pg_sync/sync_*.log 2>/dev/null | wc -l)
echo "Total de ejecuciones: $TOTAL"

# Ejecuciones exitosas
SUCCESS=$(grep -l "Con errores: 0" /var/log/pg_sync/sync_*.log 2>/dev/null | wc -l)
echo "Ejecuciones exitosas: $SUCCESS"

# Ejecuciones con errores
ERRORS=$((TOTAL - SUCCESS))
echo "Ejecuciones con errores: $ERRORS"

# Tasa de éxito
if [ $TOTAL -gt 0 ]; then
    RATE=$(awk "BEGIN {printf \"%.2f\", ($SUCCESS/$TOTAL)*100}")
    echo "Tasa de éxito: ${RATE}%"
fi

echo ""

# Última ejecución
LAST_LOG=$(ls -t /var/log/pg_sync/sync_*.log 2>/dev/null | head -1)
if [ -n "$LAST_LOG" ]; then
    echo "Última ejecución:"
    echo "  Archivo: $(basename $LAST_LOG)"
    echo "  Fecha: $(echo $LAST_LOG | sed 's/.*sync_//;s/.log//' | sed 's/_/ / ; s/_/:/')"
    
    # Extraer estadísticas
    grep "Total de tablas:" $LAST_LOG | tail -1
    grep "Exitosas:" $LAST_LOG | tail -1
    grep "Con errores:" $LAST_LOG | tail -1
fi

echo ""

# Ejecuciones de hoy
TODAY_COUNT=$(ls /var/log/pg_sync/sync_$(date +%Y%m%d)*.log 2>/dev/null | wc -l)
echo "Ejecuciones hoy: $TODAY_COUNT"

# Tamaño total de logs
TOTAL_SIZE=$(du -sh /var/log/pg_sync 2>/dev/null | cut -f1)
echo "Tamaño total de logs: $TOTAL_SIZE"

echo "=========================================="

Dar permisos y ejecutar:

bash 
sudo chmod +x /opt/pg_sync/stats.sh
sudo /opt/pg_sync/stats.sh
sudo chmod +x /opt/pg_sync/stats.sh
sudo /opt/pg_sync/stats.sh

Rotación de Logs

Para evitar que los logs crezcan indefinidamente, configura logrotate:

bash 
sudo nano /etc/logrotate.d/pg_sync
sudo nano /etc/logrotate.d/pg_sync

Contenido:

plaintext 
/var/log/pg_sync/*.log {
    daily
    rotate 30
    compress
    delaycompress
    notifempty
    missingok
    create 0644 root root
    sharedscripts
    postrotate
        # Opcional: enviar notificación
    endscript
}
/var/log/pg_sync/*.log {
    daily
    rotate 30
    compress
    delaycompress
    notifempty
    missingok
    create 0644 root root
    sharedscripts
    postrotate
        # Opcional: enviar notificación
    endscript
}

Explicación de opciones:

Opción Significado
daily Rotar logs diariamente
rotate 30 Mantener 30 versiones rotadas (30 días)
compress Comprimir logs antiguos (.gz)
delaycompress No comprimir el log más reciente rotado
notifempty No rotar si el log está vacío
missingok No error si el archivo no existe
create 0644 Permisos del nuevo archivo de log

Probar configuración:

bash 
# Simular rotación (no modifica archivos)
sudo logrotate -d /etc/logrotate.d/pg_sync

# Forzar rotación
sudo logrotate -f /etc/logrotate.d/pg_sync
# Simular rotación (no modifica archivos)
sudo logrotate -d /etc/logrotate.d/pg_sync

# Forzar rotación
sudo logrotate -f /etc/logrotate.d/pg_sync

Alertas por Email

Para recibir notificaciones por email cuando haya errores:

Opción 1: Configurar MAILTO en crontab

bash 
sudo crontab -e
sudo crontab -e

Agregar al inicio:

MAILTO=tu_email@ejemplo.com

50 7 * * * /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1
10 12 * * * /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1
30 16 * * * /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1

Nota: Requiere que el servidor tenga configurado un MTA (como postfix o sendmail).

Opción 2: Script wrapper con notificaciones

Crear script wrapper:

bash 
sudo nano /opt/pg_sync/sync_with_alerts.sh
sudo nano /opt/pg_sync/sync_with_alerts.sh

Contenido:

bash 
#!/bin/bash

LOG_FILE="/tmp/pg_sync_result.log"
EMAIL="tu_email@ejemplo.com"

# Ejecutar sincronización
/opt/pg_sync/pg_sync.sh > "$LOG_FILE" 2>&1
EXIT_CODE=$?

# Si hubo errores, enviar email
if [ $EXIT_CODE -ne 0 ]; then
    SUBJECT="[ERROR] Sincronización PostgreSQL Falló"
    mail -s "$SUBJECT" "$EMAIL" < "$LOG_FILE"
fi

# Limpiar
rm -f "$LOG_FILE"

exit $EXIT_CODE
#!/bin/bash

LOG_FILE="/tmp/pg_sync_result.log"
EMAIL="tu_email@ejemplo.com"

# Ejecutar sincronización
/opt/pg_sync/pg_sync.sh > "$LOG_FILE" 2>&1
EXIT_CODE=$?

# Si hubo errores, enviar email
if [ $EXIT_CODE -ne 0 ]; then
    SUBJECT="[ERROR] Sincronización PostgreSQL Falló"
    mail -s "$SUBJECT" "$EMAIL" < "$LOG_FILE"
fi

# Limpiar
rm -f "$LOG_FILE"

exit $EXIT_CODE

Usar en CRON:

50 7 * * * /opt/pg_sync/sync_with_alerts.sh >> /var/log/pg_sync/cron.log 2>&1

Monitoreo del Túnel SSH

Ver si el túnel SSH está activo:

bash 
# Ver PID del túnel
cat /var/lib/pg_sync/ssh_tunnel.pid

# Verificar si el proceso está corriendo
ps aux | grep ssh | grep 5433

# Ver puertos en uso
sudo lsof -i :5433
# Ver PID del túnel
cat /var/lib/pg_sync/ssh_tunnel.pid

# Verificar si el proceso está corriendo
ps aux | grep ssh | grep 5433

# Ver puertos en uso
sudo lsof -i :5433

Dashboard de Monitoreo (Opcional)

Crear dashboard simple:

bash 
sudo nano /opt/pg_sync/dashboard.sh
sudo nano /opt/pg_sync/dashboard.sh

Contenido:

bash 
#!/bin/bash

watch -n 5 '
clear
echo "═══════════════════════════════════════════════"
echo "  Dashboard de Sincronización PostgreSQL"
echo "═══════════════════════════════════════════════"
echo ""
date
echo ""
echo "--- Estado del Servicio CRON ---"
systemctl is-active cron
echo ""
echo "--- Última Ejecución ---"
ls -t /var/log/pg_sync/sync_*.log | head -1 | xargs basename
echo ""
echo "--- Resumen Última Ejecución ---"
ls -t /var/log/pg_sync/sync_*.log | head -1 | xargs tail -10 | grep -E "(Total|Exitosas|errores)"
echo ""
echo "--- Próxima Ejecución CRON ---"
# Calcular próxima ejecución...
echo "7:50 AM, 12:10 PM o 4:30 PM"
echo ""
echo "--- Túnel SSH ---"
if [ -f /var/lib/pg_sync/ssh_tunnel.pid ]; then
    PID=$(cat /var/lib/pg_sync/ssh_tunnel.pid)
    if ps -p $PID > /dev/null 2>&1; then
        echo "[OK] Activo (PID: $PID)"
    else
        echo "[ERROR] Inactivo"
    fi
else
    echo "[ERROR] No iniciado"
fi
'
#!/bin/bash

watch -n 5 '
clear
echo "═══════════════════════════════════════════════"
echo "  Dashboard de Sincronización PostgreSQL"
echo "═══════════════════════════════════════════════"
echo ""
date
echo ""
echo "--- Estado del Servicio CRON ---"
systemctl is-active cron
echo ""
echo "--- Última Ejecución ---"
ls -t /var/log/pg_sync/sync_*.log | head -1 | xargs basename
echo ""
echo "--- Resumen Última Ejecución ---"
ls -t /var/log/pg_sync/sync_*.log | head -1 | xargs tail -10 | grep -E "(Total|Exitosas|errores)"
echo ""
echo "--- Próxima Ejecución CRON ---"
# Calcular próxima ejecución...
echo "7:50 AM, 12:10 PM o 4:30 PM"
echo ""
echo "--- Túnel SSH ---"
if [ -f /var/lib/pg_sync/ssh_tunnel.pid ]; then
    PID=$(cat /var/lib/pg_sync/ssh_tunnel.pid)
    if ps -p $PID > /dev/null 2>&1; then
        echo "[OK] Activo (PID: $PID)"
    else
        echo "[ERROR] Inactivo"
    fi
else
    echo "[ERROR] No iniciado"
fi
'

Ejecutar:

bash 
sudo chmod +x /opt/pg_sync/dashboard.sh
sudo /opt/pg_sync/dashboard.sh
sudo chmod +x /opt/pg_sync/dashboard.sh
sudo /opt/pg_sync/dashboard.sh

Solución de Problemas

Errores Comunes

1. Error: "password authentication failed"

Síntoma:

plaintext 
[ERROR] No se pudo conectar a la base de datos local
[ERROR] No se pudo conectar a la base de datos local

Causas posibles:

  • Contraseña incorrecta
  • Usuario no existe
  • Contraseña con caracteres especiales sin URL encoding

Solución:

bash 
# Probar conexión manualmente
psql -h localhost -U usuario -d base_datos

# Si funciona manual pero no en script, revisar URL encoding
# Contraseña: $pass@123
# Debe ser: %24pass%40123

# Actualizar config.env
sudo nano /etc/pg_sync/config.env
# export LOCAL_DB_PASSWORD="%24pass%40123"
# Probar conexión manualmente
psql -h localhost -U usuario -d base_datos

# Si funciona manual pero no en script, revisar URL encoding
# Contraseña: $pass@123
# Debe ser: %24pass%40123

# Actualizar config.env
sudo nano /etc/pg_sync/config.env
# export LOCAL_DB_PASSWORD="%24pass%40123"

2. Error: "column does not exist"

Síntoma:

plaintext 
[ERROR] column "nombreusuaria" does not exist
HINT: Perhaps you meant to reference the column "nombreUsuaria"
[ERROR] column "nombreusuaria" does not exist
HINT: Perhaps you meant to reference the column "nombreUsuaria"

Causa: Case sensitivity - PostgreSQL es case-sensitive con comillas dobles.

Solución: El script ya maneja esto automáticamente con comillas dobles. Si aparece este error, verifica que estés usando la versión más reciente del script.

3. Error: "violates foreign key constraint"

Síntoma:

plaintext 
[ERROR] insert or update on table "X" violates foreign key constraint
[ERROR] insert or update on table "X" violates foreign key constraint

Causa: Sincronización en orden incorrecto (tabla hija antes que tabla padre).

Solución: El script detecta dependencias automáticamente. Si aparece este error:

bash 
# Ver orden detectado
cat /var/lib/pg_sync/table_dependencies.txt

# El script debería sincronizar en este orden automáticamente
# Ver orden detectado
cat /var/lib/pg_sync/table_dependencies.txt

# El script debería sincronizar en este orden automáticamente

4. Error: "No se pudo establecer el túnel SSH"

Síntoma:

plaintext 
[ERROR] No se pudo establecer el túnel SSH
[ERROR] No se pudo establecer el túnel SSH

Causas posibles:

  • Contraseña SSH incorrecta
  • Puerto SSH incorrecto
  • Firewall bloqueando
  • Host incorrecto

Solución:

bash 
# Probar conexión SSH manualmente
ssh -p 22 usuario@servidor.remoto.com

# Probar con sshpass
sshpass -p "tu_password" ssh -p 22 usuario@servidor.remoto.com "echo OK"

# Ver logs del sistema
sudo grep ssh /var/log/auth.log | tail -20
# Probar conexión SSH manualmente
ssh -p 22 usuario@servidor.remoto.com

# Probar con sshpass
sshpass -p "tu_password" ssh -p 22 usuario@servidor.remoto.com "echo OK"

# Ver logs del sistema
sudo grep ssh /var/log/auth.log | tail -20

5. Error: "sshpass: command not found"

Síntoma:

plaintext 
[ERROR] Dependencias faltantes: sshpass
[ERROR] Dependencias faltantes: sshpass

Solución:

bash 
sudo apt-get install sshpass
sudo apt-get install sshpass

6. CRON no ejecuta el script

Síntoma: El script funciona manual pero no vía CRON.

Diagnóstico:

bash 
# Ver logs de CRON del sistema
sudo grep CRON /var/log/syslog | tail -30

# Ver si CRON está corriendo
sudo systemctl status cron

# Ver tareas configuradas
sudo crontab -l
# Ver logs de CRON del sistema
sudo grep CRON /var/log/syslog | tail -30

# Ver si CRON está corriendo
sudo systemctl status cron

# Ver tareas configuradas
sudo crontab -l

Soluciones comunes:

bash 
# Script no tiene permisos de ejecución
sudo chmod +x /opt/pg_sync/pg_sync.sh

# CRON no está corriendo
sudo systemctl start cron
sudo systemctl enable cron

# Error de sintaxis en crontab
# Verificar que cada línea tenga el formato correcto
# Minuto Hora Día Mes DiaSemana Comando
# Script no tiene permisos de ejecución
sudo chmod +x /opt/pg_sync/pg_sync.sh

# CRON no está corriendo
sudo systemctl start cron
sudo systemctl enable cron

# Error de sintaxis en crontab
# Verificar que cada línea tenga el formato correcto
# Minuto Hora Día Mes DiaSemana Comando

7. Puerto del túnel ya en uso

Síntoma:

plaintext 
[WARNING] Ya existe un proceso en puerto 5433
[WARNING] Ya existe un proceso en puerto 5433

Causa: Ejecución anterior no cerró el túnel correctamente.

Solución:

bash 
# Ver procesos en el puerto
sudo lsof -i :5433

# Matar proceso
sudo kill $(lsof -t -i:5433)

# O usar otro puerto
sudo nano /etc/pg_sync/config.env
# export LOCAL_TUNNEL_PORT="5434"
# Ver procesos en el puerto
sudo lsof -i :5433

# Matar proceso
sudo kill $(lsof -t -i:5433)

# O usar otro puerto
sudo nano /etc/pg_sync/config.env
# export LOCAL_TUNNEL_PORT="5434"

Herramientas de Diagnóstico

Script de diagnóstico completo

Crear:

bash 
sudo nano /opt/pg_sync/diagnose.sh
sudo nano /opt/pg_sync/diagnose.sh

Contenido:

bash 
#!/bin/bash

echo "=== Diagnóstico del Sistema de Sincronización ==="
echo ""

echo "1. Verificando dependencias..."
for cmd in psql pg_dump sshpass; do
    if command -v $cmd &> /dev/null; then
        echo "  [OK] $cmd instalado"
    else
        echo "  [ERROR] $cmd NO instalado"
    fi
done
echo ""

echo "2. Verificando directorios..."
for dir in /etc/pg_sync /opt/pg_sync /var/log/pg_sync /var/lib/pg_sync; do
    if [ -d "$dir" ]; then
        echo "  [OK] $dir existe"
    else
        echo "  [ERROR] $dir NO existe"
    fi
done
echo ""

echo "3. Verificando archivos..."
if [ -f /etc/pg_sync/config.env ]; then
    echo "  [OK] config.env existe"
    echo "    Permisos: $(stat -c %a /etc/pg_sync/config.env)"
else
    echo "  [ERROR] config.env NO existe"
fi

if [ -f /opt/pg_sync/pg_sync.sh ]; then
    echo "  [OK] pg_sync.sh existe"
    echo "    Permisos: $(stat -c %a /opt/pg_sync/pg_sync.sh)"
else
    echo "  [ERROR] pg_sync.sh NO existe"
fi
echo ""

echo "4. Verificando servicio CRON..."
if systemctl is-active --quiet cron; then
    echo "  [OK] CRON está corriendo"
else
    echo "  [ERROR] CRON NO está corriendo"
fi

if sudo crontab -l &>/dev/null; then
    COUNT=$(sudo crontab -l | grep -c pg_sync)
    echo "  Tareas programadas: $COUNT"
else
    echo "  [ERROR] No hay crontab configurado"
fi
echo ""

echo "5. Verificando conectividad..."
source /etc/pg_sync/config.env 2>/dev/null
if [ -n "$SSH_HOST" ]; then
    echo "  Probando SSH a $SSH_HOST..."
    timeout 5 bash -c "cat < /dev/null > /dev/tcp/$SSH_HOST/$SSH_PORT" 2>/dev/null
    if [ $? -eq 0 ]; then
        echo "  [OK] Puerto SSH accesible"
    else
        echo "  [ERROR] Puerto SSH NO accesible"
    fi
fi
echo ""

echo "6. Logs recientes..."
if ls /var/log/pg_sync/sync_*.log &>/dev/null; then
    LAST_LOG=$(ls -t /var/log/pg_sync/sync_*.log | head -1)
    echo "  Último log: $(basename $LAST_LOG)"
    echo "  Fecha: $(stat -c %y $LAST_LOG | cut -d' ' -f1,2 | cut -d'.' -f1)"
    
    if grep -q "Con errores: 0" $LAST_LOG; then
        echo "  [OK] Última ejecución exitosa"
    else
        echo "  [ERROR] Última ejecución con errores"
    fi
else
    echo "  [INFO] No hay logs de ejecución"
fi

echo ""
echo "=== Fin del Diagnóstico ==="
#!/bin/bash

echo "=== Diagnóstico del Sistema de Sincronización ==="
echo ""

echo "1. Verificando dependencias..."
for cmd in psql pg_dump sshpass; do
    if command -v $cmd &> /dev/null; then
        echo "  [OK] $cmd instalado"
    else
        echo "  [ERROR] $cmd NO instalado"
    fi
done
echo ""

echo "2. Verificando directorios..."
for dir in /etc/pg_sync /opt/pg_sync /var/log/pg_sync /var/lib/pg_sync; do
    if [ -d "$dir" ]; then
        echo "  [OK] $dir existe"
    else
        echo "  [ERROR] $dir NO existe"
    fi
done
echo ""

echo "3. Verificando archivos..."
if [ -f /etc/pg_sync/config.env ]; then
    echo "  [OK] config.env existe"
    echo "    Permisos: $(stat -c %a /etc/pg_sync/config.env)"
else
    echo "  [ERROR] config.env NO existe"
fi

if [ -f /opt/pg_sync/pg_sync.sh ]; then
    echo "  [OK] pg_sync.sh existe"
    echo "    Permisos: $(stat -c %a /opt/pg_sync/pg_sync.sh)"
else
    echo "  [ERROR] pg_sync.sh NO existe"
fi
echo ""

echo "4. Verificando servicio CRON..."
if systemctl is-active --quiet cron; then
    echo "  [OK] CRON está corriendo"
else
    echo "  [ERROR] CRON NO está corriendo"
fi

if sudo crontab -l &>/dev/null; then
    COUNT=$(sudo crontab -l | grep -c pg_sync)
    echo "  Tareas programadas: $COUNT"
else
    echo "  [ERROR] No hay crontab configurado"
fi
echo ""

echo "5. Verificando conectividad..."
source /etc/pg_sync/config.env 2>/dev/null
if [ -n "$SSH_HOST" ]; then
    echo "  Probando SSH a $SSH_HOST..."
    timeout 5 bash -c "cat < /dev/null > /dev/tcp/$SSH_HOST/$SSH_PORT" 2>/dev/null
    if [ $? -eq 0 ]; then
        echo "  [OK] Puerto SSH accesible"
    else
        echo "  [ERROR] Puerto SSH NO accesible"
    fi
fi
echo ""

echo "6. Logs recientes..."
if ls /var/log/pg_sync/sync_*.log &>/dev/null; then
    LAST_LOG=$(ls -t /var/log/pg_sync/sync_*.log | head -1)
    echo "  Último log: $(basename $LAST_LOG)"
    echo "  Fecha: $(stat -c %y $LAST_LOG | cut -d' ' -f1,2 | cut -d'.' -f1)"
    
    if grep -q "Con errores: 0" $LAST_LOG; then
        echo "  [OK] Última ejecución exitosa"
    else
        echo "  [ERROR] Última ejecución con errores"
    fi
else
    echo "  [INFO] No hay logs de ejecución"
fi

echo ""
echo "=== Fin del Diagnóstico ==="

Ejecutar:

bash 
sudo chmod +x /opt/pg_sync/diagnose.sh
sudo /opt/pg_sync/diagnose.sh
sudo chmod +x /opt/pg_sync/diagnose.sh
sudo /opt/pg_sync/diagnose.sh

Reiniciar desde Cero

Si necesitas empezar de nuevo:

bash 
# 1. Detener cualquier túnel SSH activo
if [ -f /var/lib/pg_sync/ssh_tunnel.pid ]; then
    kill $(cat /var/lib/pg_sync/ssh_tunnel.pid) 2>/dev/null
fi

# 2. Eliminar timestamp (forzar sincronización completa)
sudo rm /var/lib/pg_sync/last_sync_timestamp.txt

# 3. Limpiar logs antiguos (opcional)
sudo rm /var/log/pg_sync/sync_*.log

# 4. Ejecutar sincronización
sudo /opt/pg_sync/pg_sync.sh
# 1. Detener cualquier túnel SSH activo
if [ -f /var/lib/pg_sync/ssh_tunnel.pid ]; then
    kill $(cat /var/lib/pg_sync/ssh_tunnel.pid) 2>/dev/null
fi

# 2. Eliminar timestamp (forzar sincronización completa)
sudo rm /var/lib/pg_sync/last_sync_timestamp.txt

# 3. Limpiar logs antiguos (opcional)
sudo rm /var/log/pg_sync/sync_*.log

# 4. Ejecutar sincronización
sudo /opt/pg_sync/pg_sync.sh

Conclusión

Resumen de lo Implementado

Has implementado un sistema completo de sincronización incremental que incluye:

  • Automatización completa con CRON (3 ejecuciones diarias)
  • Seguridad mediante túnel SSH cifrado
  • Inteligencia con detección automática de Foreign Keys
  • Robustez para manejar esquemas diferentes y case-sensitivity
  • Eficiencia con sincronización incremental (solo cambios)
  • Monitoreo con logs detallados y estadísticas

Checklist Final

  • Dependencias instaladas (postgresql-client, sshpass)
  • Directorios creados (/etc, /opt, /var/log, /var/lib)
  • Archivo de configuración creado y protegido
  • Script principal instalado con permisos correctos
  • Prueba manual exitosa
  • CRON configurado (7:50 AM, 12:10 PM, 4:30 PM)
  • Servicio CRON verificado y activo
  • Sistema de logs funcionando

Mantenimiento Recomendado

Diario:

  • Verificar que CRON esté corriendo: sudo systemctl status cron
  • Revisar logs de errores: grep ERROR /var/log/pg_sync/sync_$(date +%Y%m%d)*.log

Semanal:

  • Verificar tasa de éxito: sudo /opt/pg_sync/stats.sh
  • Revisar tamaño de logs: du -sh /var/log/pg_sync

Mensual:

  • Limpiar logs antiguos (si no configuraste logrotate)
  • Verificar conectividad SSH y PostgreSQL
  • Revisar orden de dependencias: cat /var/lib/pg_sync/table_dependencies.txt

Mejoras Futuras

Opcionales pero recomendadas:

  1. Monitoreo avanzado: Integrar con Grafana/Prometheus
  2. Notificaciones: Configurar Slack/Discord webhooks
  3. Dashboard web: Crear interfaz para ver estadísticas
  4. Compresión: Agregar compresión a la transferencia de datos
  5. Replicación bidireccional: Sincronizar en ambas direcciones
  6. Validación de integridad: Comparar checksums después de sincronizar

Recursos Adicionales

Documentación oficial:

Herramientas útiles:

Contacto y Soporte

Para problemas específicos:

  1. Ejecuta el script de diagnóstico: sudo /opt/pg_sync/diagnose.sh
  2. Revisa los logs: ls -t /var/log/pg_sync/sync_*.log | head -1 | xargs cat
  3. Verifica la sección "Solución de Problemas" de esta guía

Apéndices

Apéndice A: Comandos Rápidos de Referencia

bash 
# Instalación
sudo apt-get install sshpass postgresql-client
sudo mkdir -p /etc/pg_sync /opt/pg_sync /var/log/pg_sync /var/lib/pg_sync

# Configuración
sudo nano /etc/pg_sync/config.env
sudo chmod 600 /etc/pg_sync/config.env

# Script
sudo nano /opt/pg_sync/pg_sync.sh
sudo chmod +x /opt/pg_sync/pg_sync.sh

# Ejecución manual
sudo /opt/pg_sync/pg_sync.sh

# CRON
sudo crontab -e
# 50 7 * * * /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1
# 10 12 * * * /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1
# 30 16 * * * /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1

# Monitoreo
tail -f /var/log/pg_sync/cron.log
sudo systemctl status cron
# Instalación
sudo apt-get install sshpass postgresql-client
sudo mkdir -p /etc/pg_sync /opt/pg_sync /var/log/pg_sync /var/lib/pg_sync

# Configuración
sudo nano /etc/pg_sync/config.env
sudo chmod 600 /etc/pg_sync/config.env

# Script
sudo nano /opt/pg_sync/pg_sync.sh
sudo chmod +x /opt/pg_sync/pg_sync.sh

# Ejecución manual
sudo /opt/pg_sync/pg_sync.sh

# CRON
sudo crontab -e
# 50 7 * * * /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1
# 10 12 * * * /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1
# 30 16 * * * /opt/pg_sync/pg_sync.sh >> /var/log/pg_sync/cron.log 2>&1

# Monitoreo
tail -f /var/log/pg_sync/cron.log
sudo systemctl status cron

Apéndice B: Estructura Completa de Archivos

plaintext 
/
├── etc/
│   └── pg_sync/
│       └── config.env                    # Configuración (600)
├── opt/
│   └── pg_sync/
│       ├── pg_sync.sh                    # Script principal (755)
│       ├── stats.sh                      # Estadísticas (755)
│       └── diagnose.sh                   # Diagnóstico (755)
├── var/
│   ├── log/
│   │   └── pg_sync/
│   │       ├── sync_YYYYMMDD_HHMMSS.log  # Logs individuales
│   │       └── cron.log                  # Log agregado
│   └── lib/
│       └── pg_sync/
│           ├── last_sync_timestamp.txt   # Control de sincronización
│           ├── ssh_tunnel.pid            # PID del túnel
│           └── table_dependencies.txt    # Orden de dependencias
/
├── etc/
│   └── pg_sync/
│       └── config.env                    # Configuración (600)
├── opt/
│   └── pg_sync/
│       ├── pg_sync.sh                    # Script principal (755)
│       ├── stats.sh                      # Estadísticas (755)
│       └── diagnose.sh                   # Diagnóstico (755)
├── var/
│   ├── log/
│   │   └── pg_sync/
│   │       ├── sync_YYYYMMDD_HHMMSS.log  # Logs individuales
│   │       └── cron.log                  # Log agregado
│   └── lib/
│       └── pg_sync/
│           ├── last_sync_timestamp.txt   # Control de sincronización
│           ├── ssh_tunnel.pid            # PID del túnel
│           └── table_dependencies.txt    # Orden de dependencias

Apéndice C: Glosario de Términos

Término Definición
UPSERT Operación que inserta o actualiza según exista el registro
Túnel SSH Conexión cifrada que redirige tráfico a través de SSH
Foreign Key Restricción que crea dependencia entre tablas
Orden Topológico Secuencia que respeta dependencias (padres antes que hijos)
Case Sensitivity Distinción entre mayúsculas y minúsculas
URL Encoding Codificación de caracteres especiales para URLs
CRON Servicio de Linux para ejecutar tareas programadas
Sincronización Incremental Transferir solo registros modificados desde última sincronización
Connection String URI que contiene credenciales y parámetros de conexión
Session Replication Role Modo PostgreSQL que deshabilita triggers y FK
Volver a artículos