Database backup

Anyone?

A. :-)

This question was expired because it remained in the 'Open' state without activity for the last 15 days.

Would really appreciate an answer if anyone knows - otherwise we're going to end up having to backup the whole VM.

Alan

We have to develop a plugin for Backup, see https://blueprints.launchpad.net/authpuppy/+spec/plugin-backup

That plugin concept sounds ideal - any thoughts as to when it may appear? In the meantime is there an easy way to at least backup the user DB on a manual basis?

This question was expired because it remained in the 'Open' state without activity for the last 15 days.

Sorry to raise this again, but our server crashed and now won't reboot properly. I've got a backup VM file of Ubuntu+AuthPuppy so can get a working version back, but it unfortunately means we've lost a month or so of user accounts.

Does anyone have an easy way to back up the AuthPuppy DB? Perhaps a way of emailing this on a regular basis using cron or suchlike? To backup the whole 4GB VM regularly seems a bit excessive given the small size of the DB involved!

Thanks!

A.

NB - also noted in Blueprint section - https://blueprints.launchpad.net/authpuppy/+spec/plugin-backup

This question was expired because it remained in the 'Open' state without activity for the last 15 days.

Bump - even just a manual way of backing up would be great!

I'm no sysadmin, so don't really know the science and good practices behind backing up databases, I can only refer you to the postgresql manual

http://www.postgresql.org/docs/8.3/static/backup-dump.html

I run a cron everyday with the pg_dump command and save the file somewhere.

Hi Alan,

You can use pg_dump. Below is a script that will call pg_dump for you, courtesy of wifidog (backup_database.sh).

You can use it as such "./backup_database /var/backup/myfile.dump".

Please tweak it to your needs. Hope it helps. Bye.

#!/bin/bash
DATABASE_NAME="yourdatabasename";
USERNAME="postgres";
FILENAME=$1;

if [ -z $FILENAME ] ; then
echo "You must specify a filename as the first argument"
exit 1
fi

cmd="pg_dump --blobs --file=$FILENAME --format=c -i -O -o -v --compress=3 -U $USERNAME $DATABASE_NAME"
echo $cmd
$cmd
retval=$?
if [[ $retval -ne 0 ]] ; then
    echo "Unable to dump the database"
    exit 1
fi

Many thanks for the help, gbastien and Wadih - will give it a go this week and report back!

Cheers

Alan

Apologies, probably just me being thick, but received the below error...

messwifi@ubuntu:~/Desktop$ ./backup_database.sh 20110201
./backup_database.sh: line 11: =pg_dump --blobs --file=20110201 --format=c -i -O -o -v --compress=3 -U authpuppy authpuppy: command not found

To recap, I did the following...
- Downloaded the .sh file from http://dev.wifidog.org/browser/branches/fsheedy/sql/backup_database.sh
- Changed the DB name & username to authpuppy
- Ran the command above

Do I need to do something to move into PostgreSQL command line mode or something first??

Cheers

Alan

Did you happen to loose the double quote " after the =?

$cmd="pg_dump --blobs --file=$FILENAME --format=c -i -O -o -v --compress=3 -U $USERNAME $DATABASE_NAME"

Otherwise, try to just run a simple 'pg_dump -V' at the command line. It should give you the version of the program. If you get a command not found error, then you don't have pg_dump installed.

Try to see which package you must install to get it. On Ubuntu, the following command should give you a choice of packages:
sudo apt-cache search pg_dump

Thanks for the reply, gbastien.

pg_dump -V works OK - output is "pg_dump (PostgreSQL) 8.4.5"

I've double-checked and the $cmd line is definitely as you have it, i.e.

$cmd="pg_dump --blobs --file=$FILENAME --format=c -i -O -o -v --compress=3 -U $USERNAME $DATABASE_NAME"

With double-quotes at either end of the line and no hard line break between the last two variables.

I then tried just taking the key code line out and running it separately...

messwifi@ubuntu:~/Desktop$ pg_dump --blobs --file=$20110201 --format=c -i -O -o -v --compress=3 -U $authpuppy $authpuppy

But received this error...
/usr/lib/postgresql/8.4/bin/pg_dump: option requires an argument -- 'U'
Try "pg_dump --help" for more information.

So near and yet so far!!

Any further thoughts appreciated on both why the scription won't work and also why the key code line won't work!

A.

Hi Alan,

Not sure what you mean by key code.

Please do this:

- In the script, keep the USERNAME as "postgres", and DATABASE_NAME as "authpuppy"
- Before running the script, switch user as postgres: "su postgres" (you need to be logged in as root)
- Run the following script: ./backup_database.sh /tmp/mydump.dump

Once you're happy with how things go, you can create a cron job. Since cron jobs run as root, you can impersonate the "postgres" user : /bin/sh postgres -c "/tmp/backup_database.sh /tmp/backup.dump".

Make sure file read/write permissions doesn't block postgres from writing to /tmp or from reading and executing backup_database. This is basic unix stuff, should be straightforward for you to figure out.

Good luck

One question though Alan, any particular reason you're using Postgres instead of MySQL ?

I do have postgres installed for Wifidog for historical reasons, but am definitely not doing the same for authpuppy. I've installed authpuppy on MySQL.

Just a thought, if you're more confortable with mysql, you should use it. I know mysql is simpler on multiple aspects, no need to understands schemas as much as postgres requires you to. Hell, we're hobbyists, not full time database administrators :) I'd rather spend my time on tweaking authpuppy than struggling with postgres.

Hi Wadih

Thanks for your comments.

I realised that I made a mistake in the code above - I'd left the $ sign in for the variables. I then repeated it a couple of times, trying both authpuppy and postgres as user...

messwifi@ubuntu:~/Desktop$ pg_dump --blobs --file=$20110201 --format=c -i -O -o -v --compress=3 -U authpuppy authpuppy
pg_dump: [archiver (db)] connection to database "authpuppy" failed: FATAL: Ident authentication failed for user "authpuppy"
pg_dump: *** aborted because of error

messwifi@ubuntu:~/Desktop$ pg_dump --blobs --file=$20110201 --format=c -i -O -o -v --compress=3 -U postgres authpuppy
pg_dump: [archiver (db)] connection to database "authpuppy" failed: FATAL: Ident authentication failed for user "postgres"
pg_dump: *** aborted because of error

When I said "key code" I was only referring to the line of code in the shell script that seemed to be the vital (or key) bit of it - the rest seems to be error-checking, etc.

Postrgres vs MySQL - mainly because I got the impression from the user documentation that this was the preferred DB to go with!

Thanks for any further tips! (also at some point when I finally get this working I'll need to work out what code to use to restore from a backup!)

Here's my next tip, switch to MySQL and use phpMyAdmin. This will offer you a graphical interface to backup and restore your database.

Hmm - OK I've now had a chance to look again and have found phpPgAdmin that provides a similar graphical interface to phpMyAdmin.

However although I can find the connection data I can't find the user data.

I have two databases showing up - postgres (5280kB) and authpuppy (6824kB). When I go into the authpuppy DB the only tables with any data in them in the public schema are ap_applicable_policies and connections. In the postgres DB all the tables appear empty.

Is this because I'm logged in as authpuppy? I tried to login with postgres (and pwd of postgres) but it said this wasn't allowed for security reasons...

Thanks for any suggestions - I'm trying to upgrade to the latest release of AuthPuppy but although the first instruction is to backup the database the only way I've managed to do that so far is to back up the entire virtual machine!!

*sigh* looks like the data is there after all in the ap_user table - it's just for some reason it displays a '0' for estimated row count! When I actually browsed the table (as opposed to looked at the structure) I could see the user data there! I've also managed to get it to do a manual export, although can't yet work out how I would re-import from a backup!

OK, so as mentioned before, I can get into my DB find phpPgAdmin with DB name authpuppy and my password and see all the date. However I still can't get the shell script running - until I can do that I can't set up the cron job either :-(

The shell script currently looks like this:
==== BEGINS ====
#!/bin/bash
DATABASE_NAME="authpuppy";
USERNAME="authpuppy";
FILENAME=$1;

if [ -z $FILENAME ] ; then
echo "You must specify a filename as the first argument"
exit 1
fi

$cmd "pg_dump --blobs --file=$FILENAME --format=c -O -o -v --compress=3 --username=$USERNAME -W $DATABASE_NAME"
echo $cmd
$cmd
retval=$?
if [[ $retval -ne 0 ]] ; then
    echo "Unable to dump the database"
    exit 1
fi
==== ENDS ====

When I run it I receive this error:

messwifi@ubuntu:~/Desktop$ ./backup_database.sh test
./backup_database.sh: line 11: pg_dump --blobs --file=test --format=c -O -o -v --compress=3 --username=authpuppy -W authpuppy: command not found

If I then try running just that single line of code direct at the prompt I get asked for my password. I enter this (and it's definitely the right one for user authpuppy as it's the only one that lets me in to phpPgAdmin) I get:

pg_dump: [archiver (db)] connection to database "authpuppy" failed: FATAL: Ident authentication failed for user "authpuppy"
pg_dump: *** aborted because of error

Anyone any thoughts?? I'd love to get this working so I could then run a cron to export the DB - I'd get it to export to a Dropbox folder and it would then be automatically backed up!

Thanks in advance for any help...

Give http://www.wisdombay.com/articles/article000013.htm a try...

-----Original Message-----
From: <email address hidden> [mailto:<email address hidden>] On Behalf Of Alan Robertson
Sent: 03 March 2011 7:46 PM
To: Robin Jones
Subject: Re: [Question #131331]: Database backup

Question #131331 on AuthPuppy changed:
https://answers.launchpad.net/authpuppy/+question/131331

Alan Robertson gave more information on the question:
OK, so as mentioned before, I can get into my DB find phpPgAdmin with DB name authpuppy and my password and see all the date. However I still can't get the shell script running - until I can do that I can't set up the cron job either :-(

The shell script currently looks like this:
==== BEGINS ====
#!/bin/bash
DATABASE_NAME="authpuppy";
USERNAME="authpuppy";
FILENAME=$1;

if [ -z $FILENAME ] ; then
echo "You must specify a filename as the first argument"
exit 1
fi

$cmd "pg_dump --blobs --file=$FILENAME --format=c -O -o -v --compress=3 --username=$USERNAME -W $DATABASE_NAME"
echo $cmd
$cmd
retval=$?
if [[ $retval -ne 0 ]] ; then
    echo "Unable to dump the database"
    exit 1
fi
==== ENDS ====

When I run it I receive this error:

messwifi@ubuntu:~/Desktop$ ./backup_database.sh test
./backup_database.sh: line 11: pg_dump --blobs --file=test --format=c -O -o -v --compress=3 --username=authpuppy -W authpuppy: command not found

If I then try running just that single line of code direct at the prompt I get asked for my password. I enter this (and it's definitely the right one for user authpuppy as it's the only one that lets me in to
phpPgAdmin) I get:

pg_dump: [archiver (db)] connection to database "authpuppy" failed: FATAL: Ident authentication failed for user "authpuppy"
pg_dump: *** aborted because of error

Anyone any thoughts?? I'd love to get this working so I could then run a cron to export the DB - I'd get it to export to a Dropbox folder and it would then be automatically backed up!

Thanks in advance for any help...

--
You received this question notification because you are an answer contact for AuthPuppy.