Ajuda com Backup Postgresql 14.

1. Ajuda com Backup Postgresql 14.

robsonmartins
(usa Ubuntu)

Enviado em 19/10/2023 - 22:30h

Boa noite senhores, poderiam me ajudar?

Eu possuia um script de backup que estava rodando no meu ubuntu server.(Este que foi roubado por assaltantes, sim, aqui no RJ tão roubando até servidor)
Ele era relacionado ao postgresql,
Ele comparava as bases que eu tenho, criava o backup de cada com a data, e excluia as mais antigas que 2 ou 3 dias.
Eu demorei um certo tempo para fazer funcionar, porém agora não me lembro como faço com os argumentos pra conseguir rodar o bash dentro do ambito do postgres.

Aqui está o link de onde eu tirei o bat: LINK https://www.emidioleite.com.br/2013/10/10/um-simples-script-para-backup-de-bases-postgres-sql-usando...

e aqui o ultimo arquivo que estava salvo:

find /opt/tomcat/Backup/Database -mtime +2 -exec rm {} \;

#!/bin/bash
# Location to place backups.
backup_dir="/opt/tomcat/Backup/Database/"
#String to append to the name of the backup files
backup_date=date +%d-%m-%Y
#Numbers of days you want to keep copie of your databases
number_of_days=3
databases=/usr/lib/postgresql/14/bin/psql -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d'
for i in $databases; do
if [ "$i" != "template0" ] && [ "$i" != "template1" ]; then
echo Dumping $i to $backup_dir$i\_$backup_date
/usr/lib/postgresql/14/bin/pg_dump -Fc $i > $backup_dir$i\_$backup_date
fi
done
find /opt/tomcat/Backup/Database -mtime +2 -exec rm {} \;

Lembro que tinha um bash que entrava no usuário postgres, e chamava esse bash.
Se alguém pudesse me dar uma luz facilitaria!

Graça e paz a todos!!

0 0

Quote

2. CHATGPT

robsonmartins
(usa Ubuntu)

Enviado em 19/10/2023 - 23:14h

Tentei a ajuda do chat gpt, ele me ajudou da seguinte maneira:

#!/bin/bash

# Define the backup directory
backup_dir="/path/to/backup/directory"

# Define the number of days to retain backups
retention_days=5

# Get the current date
current_date=$(date +%Y-%m-%d)

# Ensure the backup directory exists
mkdir -p "$backup_dir"

# Prompt for PostgreSQL username and password
read -p "Enter the PostgreSQL username: " pg_username
read -s -p "Enter the PostgreSQL password for user $pg_username: " pg_password
echo

# List all PostgreSQL databases (excluding system databases like "template0" and "template1")
databases=$(psql -U "$pg_username" -l -t -A | cut -d '|' -f 1 | grep -v -E 'template0|template1')

# Loop through the list of databases and perform backups
for db in $databases; do
# Create a backup file with the database name and the current date
backup_file="$backup_dir/$db-$current_date.sql"

# Perform the backup using pg_dump with the provided username and password
PGPASSWORD="$pg_password" pg_dump -U "$pg_username" "$db" > "$backup_file"

# Check if the backup was successful
if [ $? -eq 0 ]; then
echo "Backup of $db completed successfully."

# Delete backups older than $retention_days days
find "$backup_dir" -type f -name "$db-*" -mtime +$retention_days -exec rm {} \;
else
echo "Backup of $db failed."
fi
done

Porém quando eu rodo configurando usuário e senha ele me dá isso:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "gilsoncarioca"

estou rodando assim:

#!/bin/bash

# Define the backup directory
backup_dir="/opt/backup/Bases/"

# Define the number of days to retain backups
retention_days=5

# Get the current date
current_date=$(date +%Y-%m-%d)

# Ensure the backup directory exists
mkdir -p "$backup_dir"

# Prompt for PostgreSQL username and password
read -p "gilsoncarioca" pg_username
read -s -p "minhasenha" pg_password
echo

# List all PostgreSQL databases (excluding system databases like "template0" and "template1")
databases=$(psql -U "$pg_username" -l -t -A | cut -d '|' -f 1 | grep -v -E 'template0|template1')

# Loop through the list of databases and perform backups
for db in $databases; do
# Create a backup file with the database name and the current date
backup_file="$backup_dir/$db-$current_date.sql"

# Perform the backup using pg_dump with the provided username and password
PGPASSWORD="$pg_password" pg_dump -U "$pg_username" "$db" > "$backup_file"

# Check if the backup was successful
if [ $? -eq 0 ]; then
echo "Backup of $db completed successfully."

# Delete backups older than $retention_days days
find "$backup_dir" -type f -name "$db-*" -mtime +$retention_days -exec rm {} \;
else
echo "Backup of $db failed."
fi
done

Para rodar

bash nomedoarquivo.sh

1 0

Quote

3. Possivel solução:

robsonmartins
(usa Ubuntu)

Enviado em 20/10/2023 - 10:42h

Irei testar hoje:

ficou da seguinte maneira:
Criei um SH com o seguinte nome: Script_Backup.sh com o seguinte conteudo:

#!/bin/bash
# Location to place backups.
backup_dir="/opt/backup/Bases/"
#String to append to the name of the backup files
backup_date=`date +%d-%m-%Y`
#Numbers of days you want to keep copie of your databases
number_of_days=3
databases=`/usr/lib/postgresql/14/bin/psql -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d'`
for i in $databases; do
if [ "$i" != "template0" ] && [ "$i" != "template1" ]; then
echo Dumping $i to $backup_dir$i\_$backup_date
/usr/lib/postgresql/14/bin/pg_dump -Fc $i > $backup_dir$i\_$backup_date
fi
done

Depois criei um outro SH para chama-lo: ( Repare no comando su -c caminho / postgres, isso que fez a diferença

#!/bin/bash
su -c /opt/backup/Scripts/Script_Backup.sh postgres
rm -rf /opt/backup/Bases/p*
find /opt/backup/Bases/* -mtime +03 -exec rm {} \;
sleep 4m
reboot now

Resolvido!

0 0

Quote