In some cases, we have to take PostgreSQL schema level backup. it’s like a logical backup where it just backup the schema objects like tables, indexes, and procedures. These backups are used in the condition where you need to restore just one schema backup, not the whole database. For daily backup, you can create a shell or bash script and schedule it using crontab or windows scheduler.
Script For Schema Backup
#!/bin/bash
mv /tmp/db_list.log /tmp/db_list.log_old
psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;" > /tmp/db_list.log ; sed -i '/^$/d' /tmp/db_list.log
file=/tmp/db_list.log
#schema_name
#DB
date=$(date '+%Y-%m-%d')
bkp_path=/var/lib/pgsql
for DB in `cat $file`
do
echo "#################Starting the backup as on ${date} for ${DB}"
pg_dump -U postgres -v -n '*' -N 'pg_toast' -N 'information_schema' -N 'pg_catalog' $DB -Ft -f ${bkp_path}/${DB}_schema_bkp_${date}.tar > `${bkp_path}/${DB}_schema_bkp_${date}.log)`
echo "#################Backup Complited as on ${date} for ${DB}"
done
echo "listing files older than 15 Days"
find ${bkp_path} -name "*schem*.tar" -type f -mtime +1 > ${bkp_path}/old_file_list.log
find ${bkp_path} -name "*.log" -type f -mtime +1 > ${bkp_path}/old_file_list.log
echo "deleting backup/log file older than 15 Days"
find ${bkp_path} -name "*.tar" -type f -mtime +15 > ${bkp_path}/old_file_list.log -delete
In this script, we have excluded system schemas like ‘information_schema’ and many more. Please remove ‘-N and schema Name if you need a backup system schema.’ It will start including the schema backup in the tar file.
How to execute the Script ?
chmod 777 backup_schema.sh
./backup_schema.sh
