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
#3. Enter the Host Name/IP or AWS RDS endpoint name.
#4. Once you have added it successfully. Open and try to access the remote database.
#5. Create a database for testing
#6. Give database name and click on ‘save‘.
#7. Once created successfully it will ve visible under your connection name.
#8. Create a table under the newly created database.
#9. You can see the newly created table under the database ‘thedbadmin’.
I hope you like this article.
Please comment if you have any doubt. Like our 
