Automatizando backup no Postgres com PowerShell

👊😎 Saudações, galera!

Estou trabalhando em um projeto full-stack em que o SGBD Posgres está sendo executado em um servidor Windows Server.

É um sistema multitentant, ou seja, vários clientes, em que cada um deles possui uma base de dados.

Obviamente, precisamos fazer o backup de cada uma dessas bases de dados, mantendo cada cópia de segurança em um arquivo. Assim, criei um script integrando diversas ferramentas com o objetivo de automatizar essa operação. Vejamos:

• PowerShell: interface de linha de comando destinada a administradores de sistemas, além de ser uma poderosa linguagem de scripts;
• pg_dump: utilitário de linha de comando do Postgres para criar backup;
• Agendador de tarefas: feramenta do Windows que permite agendar a execução periódica de programas e scripts.

1 – Verificando o PowerShell

A versão do PowerShell que usei para fazer esse script é a 7.x. Por se tratar de um script simples, no contexto do PowerShell, que não usa nada específico da versão, deve funcionar também na versão antiga, Windows PowerShell 5.x.

Ao abrir o console do PowerShell, será exibida a versão atual. Para saber mais informações, digite o comando Get-Host:

2 – Verificando o Postgres

Para verificar a versão do Postgres, tenha certeza de que o serviço dele está ativo. Depois, basta digitar o comando pqsl –version.

3 – Política de Execução do PowerShell

A política de execução são regras do PowerShell usadas para controlar a execução de arquivos .ps1 (scripts). Elas não impedem a execução de comandos interativos no console — apenas scripts e arquivos de configuração.

Digite o comando Get-ExecutionPolicy para verificar a política atual.

Essas são as políticas de execução do PowerShell.

Para a execução do script de backup proposto aqui, vamos usar Unrestricted. Para isso, use o comando Set-ExecutionPolicy Unrestricted – para o comando ser executado, é necessário executar o console do PowerShell como Administrador.

4 – Enfim, o Script!

O script a seguir usa o utilitário pg_dump do próprio Postgres para fazer backup dos bancos de dados.

#caminho do binário do posgres
$pgBinPath = "$env:ProgramFiles\PostgreSQL\17\bin"

# Caminho para salvar os backups
$backupPath = "C:\_backup"
# cria a pasta, caso não exista
New-Item -ItemType Directory -Force -Path $backupPath | Out-Null

# Credenciais do Banco de dados
$bdHost = "localhost"
$bdPort = 5432
$bdUser = "postgres"
$bdPassword = "postdba"

# Define uma variável de ambiente para evitar prompt a cada chamada do comando pg_dump
$env:PGPASSWORD = $bdPassword

# conecta-se ao postgres para obter os nomes dos bancos, exceto os padrões
$dbList = & "$pgBinPath\psql.exe" -h $bdHost -p $bdPort -U $bdUser -d postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname NOT IN ('postgres');"

# Limpa espaços e divide em array
$dbNames = $dbList -split "`n" | ForEach-Object { $_.Trim() } | Where-Object { $_ }

# itera sobre o array de nomes, executando o pg_dump em cada um deles
foreach ($db in $dbNames) {
    $date = Get-Date -Format "yyyyMMdd_HHmmss"
    $fileName = "$backupPath\$db-$date.sql"
    & "$pgBinPath\pg_dump.exe" -h $bdHost -p $bdPort -U $bdUser -F p -d $db -f $fileName
    Write-Host "Backup feito para a base: $db"
}

#exclui os arquivos gerados há mais de 2 dias
$targetDate = (Get-Date).AddDays(-2).Date
 Get-ChildItem -Path $backupPath -File |
    Where-Object { $_.LastWriteTime.Date -eq $targetDate } | Remove-Item

# Limpa variável de ambiente
Remove-Item Env:\PGPASSWORD

Salve o script em qualquer pasta, com o tipo .ps1 (PowerShell Script).

Veja aqui alguns comandlets do PowerShell que são extremamente úteis para desenvolvedores.

5 – Agendando execução automática

No Windows, o aplicativo Agendador de Tarefas (Task Scheduler) é o responsável por permitir execuções automáticas de processos, aplicações e scripts.

Os gatilhos de execução podem ser horários determinados ou até mesmo eventos do Sistema Operacional.

No nosso caso, vamos criar uma tarefa para executar o script de backup duas vezes ao dia.

1 – Abra o gerenciador de tarefas e clique em Create Task (Criar Tarefa)

2 – Coloque um nome para a tarefa. A descrição é opcional. Em Security Options (Opções de Segurança), selecione se a tarefa será executada somente quanto o usuário estiver logado ou não.

3 – Clique na Aba Trigger (Gatilho), e depois, clique no botão New Trigger,

4 – Depois, precisamos configurar o gatilho para que seja executado diariamente. Note que você pode configurar vários gatilhos, dependendo de com foi a decisão de execução.

No exemplo a seguir, configuramos um gatilho diário, com uma data específica para iniciar.

Depois de clicar em OK, vamos clicar na aba Actions e depois no botão New.

Agora vamos configurar a ação, ou seja, o que será executado. No nosso caso, será um script PowerShell, então, veja a figura a seguir:

• Action: Start a program
• Program/Script: o executável do PowerShell
• Ad arguments: o script ps1 que será executado.

Depois disso, clique em OK.

Agora que a tarefa foi criada, você pode executá-la para testar se tudo está OK. Para isso, volte no início, procure sua tarefa na lista de tarefas, clique com o botão direito nela e clique em Run (Executar).

6 – Protegendo a senha com o arquivo pgpass.conf

Uma recomendação de segurança é não expor a senha do Postgres diretamente no Script. Para isso, podemos usar o arquivo pgpass.conf.

Quando usamos um comando do Postgres, como o pg_dump ou pg_restore, ele procura automaticamente as senhas necessárias nesse arquivo.

No Windows, por padrão, esse arquivo fica em %APPDATA%\postgresql\pgpass.conf. O APPDATA é uma variável de ambiente que aponta para a pasta <NomeDeUsuário>\AppData\Roaming\postgresql\pgpass.conf

No Linux esse arquivo fica em ~/.pgpass

Caso o arquivo não exista, ele pode ser criado.

O arquivo pgpass deve ter a seguinte estrutura:

host:port:database:username:password

É possível configurar várias linhas, ou seja, vários bancos de dados. Por exemplo:

localhost:5432:empresa01:admin:123456
localhost:5432:empresa02:admin:999999
192.168.0.122:5433:empresaXYZ:admin:admin123

Também podemos usar caracteres curinga. No exemplo a seguir, definimos que podemos nos conectar a todas as bases de dados com o mesmo nome de usuário e senha:

localhost:5432:*:admin:senha_mestre

Uma das vantagens de usar o pgpass.conf, além de não expor a senha diretamente no script, é o fato de que você pode determinar nele quais bancos de dados serão feitos o backup. No meu caso, escolhi duas bases de dados locais, então meu arquivo ficou assim:

localhost:5432:tenant_002:postgres:postdba
localhost:5432:bookstore:postgres:postdba

Aqui convém destacar que o arquivo pgpass.conf é padrão do Postgres, mas poderíamos usar um outro arquivo de senhas, segundo um padrão que nós mesmos definíssemos. Poderíamos, por exemplo, usar um arquivo em formato json.

7 – Script adaptado para o arquivo pgpass.conf

Como não estamos mais criando uma variável de ambiente configurada em tempo de execução do script, precisamos adaptá-lo. Veja como ele ficou no código comentado a seguir:

#caminho do binário do posgres
$pgBinPath = "$env:ProgramFiles\PostgreSQL\17\bin"

# Caminho para salvar os backups
$backupPath = "C:\_backup"
# cria a pasta, caso não exista
New-Item -ItemType Directory -Force -Path $backupPath | Out-Null

# Pega as linhas do pgpass.conf
$lines = Get-Content "$env:APPDATA\postgresql\pgpass.conf"

# cria uma lista de objeto customizado com as configurações de cada linha
$entries = $lines | ForEach-Object {
    # a separação das partes é ao encontrar dois pontos (:)
    $parts = $_ -split ":"
    # pegamos todas as partes para criar a lista, mas poderia ser somente algumas
    [PSCustomObject]@{
        Host     = $parts[0]
        Port     = $parts[1]
        Database = $parts[2]
        Username = $parts[3]
        Password = $parts[4]
    }
}

# itera sobre a lista, executando o pg_dump para cada banco de dados
foreach ($db in $entries){
    # pega a data atual
    $date = Get-Date -Format "yyyyMMdd_HHmmss"
    # cria o nome do arquivo
    $fileName = "$backupPath\$($db.Database)-$date.sql"
    # faz o pg_dump
    & "$pgBinPath\pg_dump.exe" -h $db.Host -p $db.Port -U $db.Username -F p -d $db.Database -f $fileName
}

#exclui os arquivos gerados há mais de 2 dias
$targetDate = (Get-Date).AddDays(-2).Date
 Get-ChildItem -Path $backupPath -File |
    Where-Object { $_.LastWriteTime.Date -eq $targetDate } | Remove-Item

Conclusão

De fato, poderíamos criar várias tarefas e vários scripts, cada um faria o backup de um base de dados específico, sem a necessidade usar o PowerShell.

Entretanto, gerenciar dezenas de scripts pode se tornar uma tarefa difícil e que toma muito tempo. Sendo assim, a integração com o PowerShell ajuda em muitos cenários, ainda mais considerando o uso do arquivo pgpass.conf, que possibilita determinar as senhas para diversas bases de dados.

👊😎 Espero ter ajudado! Até a próxima!