ubuntu20.04PostgreSQL Automatically backs up databases

This answer quotes CHatGPT oh

您可以使用cron定时任务来实现自动备份PostgreSQL数据库，具体步骤如下：
1. 创建备份脚本，例如：
#!/bin/bash
pg_dump -U postgres -Fc mydb > /path/to/backup/mydb_$(date +%!Y(MISSING)%!m(MISSING)%!d(MISSING)).dump
2. 使用crontab -e命令编辑定时任务，例如：
0 0 * * * /path/to/backup/backup.sh
3. 保存并退出，即可实现自动备份PostgreSQL数据库的功能。

By manual backup I mean this
pg_dump --dbname=test --format=tar --file=/pgbak/test5.tar

The following answers are based on ChatGPT and GISer Liu:

You can specify the backup format using the -F parameter of the pg_dump command, -F is followed by p , c , t , d . The backup files are in plain , custom , tar , and -F 0 formats. Optionally, you can use the custom format and compress the backup data. The compression method can be -F 2 or -F 3. The following is the modified backup script:

bash

#!/bin/bash
# Configuration
BACKUP_DIR="/path/to/backup/directory"
DB_USER="postgres"
DB_NAME="mydatabase"
 
# Generate filename
NOW=$(date +"%-%m-%d_%H-%M-%S")
FILE="$BACKUP_DIR/$NOW.dump"
 
# Backup database
pg_dump -U $DB_USER -F c -b -v -f $FILE $DB_NAME | gzip
 
# Delete backups older than 7 days
find $BACKUP_DIR -type f -name "*.dump.gz" -mtime +7 -exec rm {} \;

This script backs up the database in the custom format using the pg_dump command and pipes the backup data to the -F 2 command for compression. The resulting backup file name ends with -F 8. After the backup is complete, the script deletes all backup files with the extension -F 8, and the last modification time of the files exceeds 7 days.

Modified script:

#!/bin/bash

# Configuration
BACKUP_DIR="/path/to/backup/directory"
DB_USER="postgres"
DB_NAME="mydatabase"

# Generate filename
NOW=$(date +"%-%m-%d_%H-%M-%S")
FILE="$BACKUP_DIR/$NOW.sql.gz"

# Backup database and compress
pg_dump -U $DB_USER -d $DB_NAME | gzip > $FILE

# Delete backups older than 7 days
find $BACKUP_DIR -type f -mtime +7 -name "*.sql.gz" -delete

No matter root or postgres permission is set, an error is reported, I can manually back up,
Brain pain

#!/bin/bash
# Configuration
BACKUP_DIR="/mnt/pgbak"
DB_USER="postgres"
DB_NAME="postgres"
LOG_FILE="/mnt/log"
 
# Error handling
set -e
if [ ! -d "$BACKUP_DIR" ]; then
    echo "Error: Backup directory $BACKUP_DIR does not exist."
    exit 1
fi
 
# Redirect output to log file
exec >> "$LOG_FILE" 2>&1
 
# Generate filename
NOW=$(date +"%-%m-%d_%H-%M-%S")
FILE="$BACKUP_DIR/$NOW.sql.gz"
 
# Backup database
pg_dump -U $DB_USER -d $DB_NAME | gzip > "$FILE"
 
# Set file permissions
chmod 600 "$FILE"
 
# Delete backups older than 7 days
find $BACKUP_DIR -type f -mtime +7 -exec rm {} \;

This is the modified script

Improved the script, added error tolerance, error exit immediately, added log output, added compressed backup files, added file permissions

#!/bin/bash

# Configuration
BACKUP_DIR="/path/to/backup/directory"
DB_USER="postgres"
DB_NAME="mydatabase"
LOG_FILE="/path/to/logfile"

# Error handling
set -e
if [ ! -d "$BACKUP_DIR" ]; then
    echo "Error: Backup directory $BACKUP_DIR does not exist."
    exit 1
fi

# Redirect output to log file
exec >> "$LOG_FILE" 2>&1

# Generate filename
NOW=$(date +"%-%m-%d_%H-%M-%S")
FILE="$BACKUP_DIR/$NOW.sql.gz"

# Backup database
pg_dump -U $DB_USER -d $DB_NAME | gzip > "$FILE"

# Set file permissions
chmod 600 "$FILE"

# Delete backups older than 7 days
find $BACKUP_DIR -type f -mtime +7 -exec rm {} \;

To set the correct permissions, you can do the following:

Ensure that you have sufficient permissions to access the backup directory. You can use the ls - ld/path/to/backup/directory command to check the permission of the backup directory. If you are not the owner of the directory or do not have write permission, You can use sudo chown -r username: the username/path/to/backup/directory command to change the directory owner or use sudo chmod -r 755 The/path/to/backup/directory command to add write access.

Verify that you are running the backup script with the correct user permissions. If you are running backup scripts with different user permissions, you may not be able to access the backup directory or the PostgreSQL database. You can check the current user with the whoami command, switch to the PostgreSQL user with the sudo su - postgres command and run the backup script.

If your PostgreSQL database requires a password, make sure you have set the ~/.pgpass file. This file, which only you should have access to, contains your PostgreSQL database login information. You can run the chmod 0600 ~/.pgpass command to protect files.

Hopefully this information will help you set the correct permissions to run your automatic backup script.