Executando uma função PostgreSQL periodicamente com o cron no Linux para eliminar sessões inativas

No PostgreSQL, pode ser útil executar determinadas tarefas periodicamente, como matar sessões inativas que estejam ociosas há mais de uma hora. Neste artigo, vamos explorar diferentes opções de agendamento no PostgreSQL para executar uma função personalizada em intervalos regulares. Além disso, abordaremos a importância de registrar as atividades da função em uma tabela de log.

Passo 1: Criando a função no PostgreSQL:

Primeiro, precisamos criar uma função no PostgreSQL que será executada periodicamente. Neste exemplo, vamos criar uma função chamada kill_inactive_sessions() para matar sessões inativas há mais de uma hora. Aqui está um exemplo de código SQL:

CREATE OR REPLACE FUNCTION kill_inactive_sessions() RETURNS VOID AS $$
DECLARE
  session RECORD;
BEGIN
  FOR session IN (SELECT * FROM pg_stat_activity WHERE state = 'idle' AND backend_type = 'client' AND now() - state_change >= interval '1 hour')
  LOOP
    EXECUTE 'SELECT pg_terminate_backend(' || session.pid || ')';
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Passo 2: Criando a tabela de log:

Para registrar as atividades da função, é útil criar uma tabela de log no PostgreSQL. A tabela de log irá armazenar informações sobre cada execução da função, como a data e hora da execução, as sessões que foram encerradas e quaisquer outras informações relevantes. Aqui está um exemplo de código SQL para criar uma tabela de log simples:

CREATE TABLE function_log (
  log_id SERIAL PRIMARY KEY,
  execution_time TIMESTAMP DEFAULT current_timestamp,
  sessions_killed INTEGER,
  additional_info TEXT
);

Passo 3: Registrando as atividades da função:

É importante registrar as atividades da função em uma tabela de log para fins de auditoria e monitoramento. Modifique a função kill_inactive_sessions() para incluir a inserção de registros na tabela de log. Aqui está um exemplo de como você pode fazer isso:

CREATE OR REPLACE FUNCTION kill_inactive_sessions() RETURNS VOID AS $$
DECLARE
  session RECORD;
  sessions_killed INTEGER := 0;
BEGIN
  FOR session IN (SELECT * FROM pg_stat_activity WHERE state = 'idle' AND backend_type = 'client' AND now() - state_change >= interval '1 hour')
  LOOP
    EXECUTE 'SELECT pg_terminate_backend(' || session.pid || ')';
    sessions_killed := sessions_killed + 1;
  END LOOP;

  INSERT INTO function_log (sessions_killed, additional_info)
  VALUES (sessions_killed, 'Execução da função kill_inactive_sessions()');
END;
$$ LANGUAGE plpgsql;

Neste exemplo, cada vez que a função kill_inactive_sessions() é executada, ela insere um registro na tabela de log function_log com o número de sessões encerradas e uma informação adicional.

Passo 4: Opções de agendamento:

Existem várias opções de agendamento disponíveis para executar uma função no PostgreSQL. Vamos discutir algumas delas:

1. Cron no Linux/Unix: O cron é uma ferramenta de agendamento integrada no sistema operacional Linux. É amplamente utilizado e permite agendar tarefas para serem executadas em horários específicos. Consulte a seção sobre “Configurando o agendamento do cron” no artigo anterior para saber como agendar a função kill_inactive_sessions() usando o cron.
2. Agendador de Tarefas no Windows: No Windows, você pode usar o Agendador de Tarefas para agendar a execução de scripts SQL ou comandos do PostgreSQL em horários específicos. O Agendador de Tarefas possui uma interface gráfica que permite definir tarefas agendadas de forma intuitiva.
3. pgAgent: O pgAgent é um agendador de tarefas de código aberto específico para o PostgreSQL. Ele é uma extensão do pgAdmin, uma ferramenta de administração gráfica para o PostgreSQL. O pgAgent permite agendar e executar tarefas no PostgreSQL em uma variedade de intervalos e horários.
4. Sistemas de orquestração: Você também pode usar sistemas de orquestração, como o Apache Airflow, o Kubernetes ou outras ferramentas de agendamento de tarefas, para agendar e executar comandos do PostgreSQL em um ambiente distribuído. Essas ferramentas geralmente possuem recursos avançados de agendamento e gerenciamento de fluxos de trabalho.

Passo 5: Registrando as atividades da função:

É importante registrar as atividades da função em uma tabela de log para fins de auditoria e monitoramento. Modifique a função kill_inactive_sessions() para incluir a inserção de registros na tabela de log. Consulte o exemplo fornecido na seção “Passo 4” do artigo anterior.

Passo 6: Configurando o agendamento do cron:

Agora, vamos configurar o agendamento do cron no Linux para executar a função kill_inactive_sessions() em intervalos regulares. O cron é uma ferramenta de agendamento integrada no sistema operacional Linux. Siga estas etapas para configurar o cron:

1. Abra o terminal no Linux.
2. Execute o comando crontab -e para abrir o arquivo de configuração do cron.
3. No arquivo de configuração, adicione uma nova linha para agendar a execução da função. Por exemplo, para executar a função a cada hora, adicione a seguinte linha:

0 * * * * psql -U seu_usuario -d seu_banco_de_dados -c 'SELECT kill_inactive_sessions();' >> /caminho/para/log.txt

Certifique-se de substituir seu_usuario, seu_banco_de_dados e /caminho/para/log.txt pelos valores apropriados para o seu ambiente. O comando psql é usado para executar a função no banco de dados.

4. Salve e feche o arquivo de configuração.

Agora, o cron irá executar a função kill_inactive_sessions() a cada hora, matando as sessões inativas há mais de uma hora.

Conclusão:

Neste artigo, exploramos diferentes opções de agendamento no PostgreSQL para executar uma função personalizada periodicamente. Demonstrei como criar uma função para matar sessões inativas, como configurar o agendamento usando o cron no Linux e como registrar as atividades da função em uma tabela de log. Além disso, mencionamos outras opções de agendamento, como o Agendador de Tarefas no Windows, o pgAgent e sistemas de orquestração. Ao personalizar a função e a tabela de log de acordo com suas necessidades específicas, você pode automatizar tarefas e manter um registro detalhado das atividades no seu banco de dados PostgreSQL.

Lembre-se de testar cuidadosamente todas as configurações e funcionalidades antes de aplicá-las em um ambiente de produção.