Backup PostgreSQL Schemas

I have been handed the responsibility to manage the backups of a PostgreSQL database. Easy enough you would say, but there is a catch.

  1. The owner wants backups to be kept of each individual schema within the database
  2. I have never managed a PostgreSQL DB before

So not being one to back down from such a challenge, I searched high and low all over the Internet, but either the Internet community isn’t interested in doing this or I am not looking in the right places. So here, with the help of fragments of scripts from around the web, I have worked out a solution which accomplishes task at hand. It is all bottled in the script below which I think is self-explanatory with the aid of a generous helping comments scattered throughout the file.

#Description:
#This script will create a compressed backup of the Genesis Postgres Db and store it on a predefined folder.
#Backups that are older than 30days will also be removed automatically

####### Make and change to directory where backups will be saved #######
BASE_DIR="/path/to/backup/folder"
YMD=$(date "+%Y-%m-%d")
DIR="$BASE_DIR/$YMD"
mkdir -p $DIR
cd $DIR

####### Full Postgres Backup #######
sudo -u postgres pg_dumpall | gzip -c > All_Db.sql.gz

####### Individual Schema Backups #######
# 1. Select individual schemas within the database and pipe the results into sed which does Step 2
# 2. Clean up the output from the SQL above
#     - Get rid of the empty spaces at the beginning of each line
#     - Remove the head and tail info from the file(Title, labels, etc)
for schema in $(sudo -u dbOwnerUN psql -d DBName -c "SELECT schemata.schema_name FROM information_schema.schemata;"|sed 's/^[ \t]*//;s/[ \t]*$//;1,2d;N;$!P;$!D;$d');
do
sudo -u dbOwnerUN pg_dump -Ft -n "$schema" DBName | gzip -c > "$schema".sql.gz
done

####### Delete backup files older than 30 days #######
OLD=$(find $BASE_DIR -type d -mtime +30)
if [ -n "$OLD" ] ; then
echo deleting old backup files: $OLD
echo $OLD | xargs rm -rfv
fi

As I said, this is my first attempt at anything like this so it may not be the best or easiest way of accomplishing this. (I am just sharing what I know and recording for my reference). So please post comments if you have a better solution.

….Questions are also welcomed

Use the Windows Key for the “Start” Menu in Ubuntu Linux – How-To Geek

Ubuntu has a dropdown menu at the top of the screen to launch applications, which is very similar to the way Windows has the start menu at the bottom of the screen. If you are a windows user new to Ubuntu, you may prefer to have the windows key launch the applications menu. Thankfully this is an easy thing to do in Ubuntu.

Go to the System \ Preferences \ Keyboard Shortcuts menu item:

Scroll down till you see the “Show the panel menu” item. Click in the Shortcut column, and when it changes to “New accelerator…”, hit the Windows Key. Click the close button. You’re done!

Now when you hit the windows key, the application menu will pop up. If you hit the right arrow key, you can go to the Places or System menu as well.

via Use the Windows Key for the “Start” Menu in Ubuntu Linux – How-To Geek.

Share/Bookmark

How to Tell What Version of Ubuntu You Are Running

Telling what version of Ubuntu you are running is extremely easy. You would commonly use this command to figure out if you are running Edgy after you upgraded from Dapper.

cat /etc/issue

Ubuntu edgy (development branch)

Note that the version numbers might change over time. I’m running the beta version so that’s what shows up when I run that command. Either way, it should be clear that you are running Edgy.

via How to Tell What Version of Ubuntu You Are Running – How-To Geek.

Share/Bookmark

Change or Reset Windows Password from a Ubuntu Live CD – How-To Geek

If you can’t log in even after trying your twelve passwords, or you’ve inherited a computer complete with password-protected profiles, worry not – you don’t have to do a fresh install of Windows. We’ll show you how to change or reset your Windows password from a Ubuntu Live CD.

This method works for all of the NT-based version of Windows – anything from Windows 2000 and later, basically. And yes, that includes Windows 7. Continue reading

Clean Up the New Ubuntu Grub2 Boot Menu – How-To Geek

Ubuntu adopted the new version of the Grub boot manager in version 9.10, getting rid of the old problematic menu.lst. Today we look at how to change the boot menu options in Grub2.

Grub2 is a step forward in a lot of ways, and most of the annoying menu.lst issues from the past are gone. Still, if you’re not vigilant with removing old versions of the kernel, the boot list can still end up being longer than it needs to be.

sshot-1

Note: You may have to hold the SHIFT button on your keyboard while booting up to get this menu to show. If only one operating system is installed on your computer, it may load it automatically without displaying this menu.

Remove Old Kernel Entries

The most common clean up task for the boot menu is to remove old kernel versions lying around on your machine. Continue reading