pgsqlbackup.sh 2.1 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859
  1. #!/bin/bash
  2. # Simple script to backup postgresql databases
  3. # Parent backup directory
  4. backup_parent_dir="/var/backups/postgresql"
  5. # PostgreSQL settings
  6. pg_host="HOST"
  7. pg_port="PORT"
  8. pg_user="USER"
  9. pg_password="PASSWD"
  10. # Check MySQL password
  11. # echo exit | mysql --user=${mysql_user} --password=${mysql_password} -B 2>/dev/null
  12. # if [ "$?" -gt 0 ]; then
  13. # echo "MySQL ${mysql_user} password incorrect"
  14. # exit 1
  15. # else
  16. # echo "MySQL ${mysql_user} password correct."
  17. # fi
  18. # Create backup directory and set permissions
  19. backup_date=`date +%Y_%m_%d_%H_%M`
  20. backup_dir="${backup_parent_dir}/${backup_date}"
  21. echo "Backup directory: ${backup_dir}"
  22. mkdir -p "${backup_dir}"
  23. chmod 644 "${backup_dir}"
  24. # Get postgresql databases
  25. pgsql_databases=`psql "host=$pg_host port=$pg_port user=$pg_user password=$pg_password" -At -c "select datname from pg_database where not datistemplate and datallowconn;"`
  26. # Backup and compress each database
  27. for database in $pgsql_databases
  28. do
  29. echo "Creating backup of \"${database}\" database"
  30. # mysqldump ${additional_mysqldump_params} --user=${mysql_user} --password=${mysql_password} ${database} | gzip > "${backup_dir}/${database}.sql.gz"
  31. # chmod 644 "${backup_dir}/${database}.sql.gz"
  32. set -o pipefail
  33. # if ! pg_dump -Fp -h "$pg_host" -U "$pg_user" "$database" | gzip > $backup_dir"/$database".sql.gz.in_progress; then
  34. # if ! pg_dump -Fp "host=$pg_host port=$pg_port user=$pg_user password=$pg_password" "$database" | gzip > $backup_dir"/$database".sql.gz.in_progress; then
  35. if ! pg_dump -Fp --dbname="postgresql://$pg_user:$pg_password@$pg_host:$pg_port/$database" | gzip > $backup_dir"/$database".sql.gz.in_progress; then
  36. echo "[!!ERROR!!] Failed to produce plain backup database $database" 1>&2
  37. else
  38. mv $backup_dir"/$database".sql.gz.in_progress $backup_dir"/$database".sql.gz
  39. fi
  40. set +o pipefail
  41. done
  42. # compress the folder
  43. # tar -zcvf "${backup_dir}.tar.gz" "${backup_dir}"
  44. # rm -rf "${backup_dir}"
  45. # Rotate backups
  46. # Delete files older than 30 days
  47. find $backup_parent_dir/ -type f -mtime +60 -delete;
  48. # Delete empty directories
  49. find $backup_parent_dir/ -type d -empty -delete;