如何修改PostgreSQL备份脚本仅备份test_database数据库

Got it, let's tweak this script so it only backs up your test_database instead of looping through every database. There are two clean approaches depending on whether you might need to add more databases later.

Approach 1: Directly Target the Database (Simplest & Most Efficient) #

This method cuts out the unnecessary database query and loop entirely, since we only care about one specific database. Here's the modified script:

#!/bin/bash 
# Location of the backup logfile. 
logfile="/home/erp/backups/logfile.log" 
#erp user 
user="antonp" 
# Location to place backups. 
backup_dir="/home/erp/backups" 
if [ ! -d $backup_dir ]; then 
 mkdir $backup_dir 
 chown $user:$user $backup_dir 
fi 
touch $logfile 
timeslot=`date +%d%m%y%H%M%S` 

# Replace the dynamic database query with your target database
target_db="test_database"

# Skip the loop and run operations directly on the target database
timeinfo=`date '+%T %x'` 
echo "Backup and Vacuum started at $timeinfo for time slot $timeslot on database: $target_db " >> $logfile 
su - postgres -c "vacuumdb -z -U postgres $target_db >/dev/null 2>&1" 
su - postgres -c "pg_dump $target_db --exclude-table-data=sale_order -U postgres | gzip > \"/tmp/openerp-$target_db-$timeslot-database.gz\"" 
cp /tmp/openerp-$target_db-$timeslot-database.gz $backup_dir/openerp-$target_db-$timeslot-database.gz 
chown $user:$user $backup_dir/openerp-$target_db-$timeslot-database.gz 
timeinfo=`date '+%T %x'` 
rm /tmp/openerp-$target_db-$timeslot-database.gz 
echo "Backup and Vacuum complete at $timeinfo for time slot $timeslot on database: $target_db " >> $logfile 

Key Changes: #

• Removed the line that queries all databases (databases=...) and replaced it with a hardcoded target_db variable pointing to test_database.
• Eliminated the for loop entirely since we don't need to iterate through multiple databases anymore.
• Updated all references to the loop variable $i to use $target_db instead.

Approach 2: Keep the Loop (For Future Expandability) #

If you think you might need to backup additional databases later, you can keep the existing loop structure but add a check to only process test_database:

#!/bin/bash 
# Location of the backup logfile. 
logfile="/home/erp/backups/logfile.log" 
#erp user 
user="antonp" 
# Location to place backups. 
backup_dir="/home/erp/backups" 
if [ ! -d $backup_dir ]; then 
 mkdir $backup_dir 
 chown $user:$user $backup_dir 
fi 
touch $logfile 
timeslot=`date +%d%m%y%H%M%S` 
databases=`sudo su - postgres -c "psql template1 -c '\l'|tail -n+4|cut -d'|' -f 1|sed -e '/^ *$/d'|sed -e '$ d'"` 

# Define your target database
target_db="test_database"

for i in $databases; do 
 # Only run backup if the current database matches your target (and skip system templates)
 if [ "$i" = "$target_db" ] && [ "$i" != "template0" ] && [ "$i" != "template1" ]; then 
 timeinfo=`date '+%T %x'` 
 echo "Backup and Vacuum started at $timeinfo for time slot $timeslot on database: $i " >> $logfile 
 su - postgres -c "vacuumdb -z -U postgres $i >/dev/null 2>&1" 
 su - postgres -c "pg_dump $i --exclude-table-data=sale_order -U postgres | gzip > \"/tmp/openerp-$i-$timeslot-database.gz\"" 
 cp /tmp/openerp-$i-$timeslot-database.gz $backup_dir/openerp-$i-$timeslot-database.gz 
 chown $user:$user $backup_dir/openerp-$i-$timeslot-database.gz 
 timeinfo=`date '+%T %x'` 
 rm /tmp/openerp-$i-$timeslot-database.gz 
 echo "Backup and Vacuum complete at $timeinfo for time slot $timeslot on database: $i " >> $logfile 
 fi 
done

Key Change: #

• Added [ "$i" = "$target_db" ] to the if condition inside the loop. This ensures the backup logic only runs when the loop hits test_database.

Recommendation #

Go with Approach 1 if you only ever need to backup test_database—it's faster and cleaner. Use Approach 2 if you anticipate adding more databases to the backup list later (you could even turn target_db into an array and check membership if needed).

内容的提问来源于stack exchange，提问作者Chaban33