Backups

From OpenSimulator

(Redirected from Backup-PostgreSQL)
Jump to: navigation, search
Languages Languages: 

En.png English

Contents

General

Needless to say, if you place any value in the contents of your OpenSimulator installation, then making backups of that data is essential.

There are two major approaches to backing up OpenSimulator data. The first is to take a backup of the entire OpenSimulator database and configuration files. The second is to take archives (OAR and IAR) of individual regions and user inventories and copies of configuration files.

Other OpenSimulator data, such as cache files or script engine compiled DLLs, do not need to be backed up as they will be recreated from other data (e.g. script LSL) if they do not exist.

Database approach

This involves

  1. Taking backups of your database files.
  2. Taking backups of your configuration files (OpenSim.ini, Robust.ini if running in grid mode, etc.).

Taking backups of database files

This varies depending on the database that you are using (SQLite, MySQL, etc.). You will need to backup every database used by OpenSimulator. In standalone mode, this will be a single database. In grid mode, this may be a different database for each simulator (depending on how you have configured OpenSimulator) and the database used by the ROBUST service, as well as any third party services you are using such as external groups.

SQLite

Since SQLite saves its databases as local files, it's fairly simple to back it up.

  • Stop OpenSimulator
  • Copy all of the *.db files out of the /bin directory (assuming that you haven't changed this in the OpenSim.ini file) to wherever you want them saved.

MySQL

You can backup the MySQL database to a set of SQL commands with the following command:

Run a terminal console (in Windows make sure you run it as administrator)

mysqldump -u<username> -p<password> -r <location> <databasename>

Example, assuming that your username is admin, and your password is secret (Linux/Mac example): 

mysqldump -uadmin -psecret -r /home/Johnny/OpenSimBackups/OpensimBackup.sql opensim

For Windows (testing needed!), this would be: 

mysqldump -uadmin -psecret -r %HOMEPATH%\My Documents\OpenSimBackups\OpensimBackup.sql opensim

Note that there is NO space between -u and the username, and between -p and the password

You will not be prompted for your password, this makes this command suitable to be used for unattended backups.

For a simulator, the ideal approach is to make a backup whilst it is not running, since it's currently possible for partial data to be saved if other operations are occurring simultaneously (e.g. a backup may save only some parts of a linkset if it occurs whilst the linkset it being persisted). However, if one is comfortable with this risk, it is safe to backup a live simulator as only that particular linkset would be affected.

Timestamped Backups

Making a single backup is good. Making daily backups is even better! However, using the command above, it will keep reusing that one file, making it impossible to go back to an earlier date, if needed.

So what we want, is to give the backup a unique, and meaningful name. Here is how:

Linux 
mv /home/Johnny/OpenSimBackups/OpensimBackup.sql /home/Johnny/OpenSimBackups/OpensimBackup_$(date +%Y-%m-%d-%H.%M.%S).sql

Instead of issuing this command after the backup has been made, you can append it to the backup command by adding a pipe symbol (|) to the first command. This will let you use the entire line in a cron job.

Windows

<THIS SECTION NEEDS TO BE TESTED!!!> 

move %HOMEPATH%\My Documents\OpenSimBackups\OpensimBackup.sql %HOMEPATH%\My Documents\OpenSimBackups\OpensimBackup%DATE%_%TIME%.sql

Automatic Daily Backups

Linux

Add something like this to crontab for a weekly/daily/hourly run. (“crontab -e” to edit, “man crontab” to understand how to edit) 

mysqldump -uuser -ppass dbname | gzip > /tmp/mydb_`date +%Y%m%d_%H%M%S`.sql.gz

Crontab should e-mail you and errors in backup if it fails. (If your *nix is set up correctly)

To verify the backup gunzip it and look inside the .sql-file, it should contain a DB-dump. For example by executing: 

zcat /tmp/mydb_YYYYMMDD_HHmmSS.sql.gz | more

To restore a given db: 

cat /tmp/mydb_YYYYMMDD_HHmmSS.sql.gz | gunzip | mysql -uuser -ppass dbname

Instead of using crontab, you can use some graphical tools.

  • In GNOME, use gnome-schedule to set a scheduled job.
  • In KDE, use the Task Scheduler from your System Settings panel.
Windows

The same as above can be done in Windows, but rotating the date can't be done in batch scripting afaik. I do however have a script for Windows I wrote for my OpenSimulator.

This is a script for taking a full MySQL backup on Windows (all databases). It required ActiveState Perl and 7zip command line to work. It works equally well for Linux, but a few adjustments to paths in header needs to be made.

ActiveState Perl: http://www.activestate.com/activeperl/
7-Zip Command line: http://www.7-zip.org/download.html 

use strict;
use locale;
#
# MySQL Backup Script by Tedd Hansen, tedd@konge.net, feb 2010
#
# THIS SCRIPT REQUIRES PERL INSTALLED. FOR WINDOWS USE ACTIVESTATE PERL.
#
# (1)
# This script requires a backup-user to be created on the database.
# Execute this: GRANT ALL on *.* to 'backup-user'@'localhost' identified by 'ZeBackup0perat00r'; 

# (change user and password at start of script))
#
# (2)
# To schedule script in Windows you must create a user with log on locally rights like Administrator has.
# DO NOT USE ADMINISTRATOR USER. If you do then backup will stop working next time we change password.
# Set startup path to the location of script, and schedule script to execute every 5 hours for example.
#


# Where to put backup
my $DIR_ROOT = "E:\\Backup";
my $DIR_BACKUP = "$DIR_ROOT\\MySQL_Backup";

# What to name backup
# Note: !DB! will be replaced with database name, !DATE! with current date yyyyMMdd and !TIME! with current time HHmmss
my $FILETEMPLATE = "MySQL !DB! !DATE! !TIME!.sqldump.7z";

my $MYSQL_USER="backup-user";
my $MYSQL_PASSWORD="ZeBackup0perat00r";
my $MYSQL_HOST="localhost";

# Full path to binary files we need
my $BIN_7Z = "$DIR_ROOT\\7za.exe";
my $BIN_MYSQLDUMP = "E:\\MySQL\\bin\\mysqldump.exe";
my $BIN_MYSQL = "E:\\MySQL\\bin\\mysql.exe";
my $CMD_LIST_DB = "echo show databases; | $BIN_MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -h $MYSQL_HOST";

# How long before we delete it?
my $KEEP_HISTORY_FOR_DAYS = 30;

# We are piping MySQLDump output directly into 7z for compression. 7z writes to filesystem.
#
# Note: !DB! and !OUTFILE! will be replaced with database name and filename for output file
# -t7z = 7zip format
# -mx5 = Medium compression: 0 = none , 9 = ultra .. Note: Increasing compression will use more CPU, and we want to reserve CPU for customers... :)
# -ms = Create solid archive
# -mmt = Multithreading
# -mo=PPMd = PPMd algorithm (good for text, and we have only text)
my $BACKUP_ARGS = "-u $MYSQL_USER -p$MYSQL_PASSWORD -h $MYSQL_HOST !DB! | $BIN_7Z a -y -t7z -mx7 -ms -mmt -si !OUTFILE!";

#####################
## START OF SCRIPT ##
### ### 
my %DBDONE = {};

# Attempt to create missing dirs
mkdir "$DIR_ROOT" unless (-d $DIR_ROOT);
mkdir "$DIR_BACKUP" unless (-d $DIR_BACKUP);

# Check that we got all we need
die "ERROR: ROOT DIR $DIR_ROOT DOES NOT EXIS!\n" unless (-d $DIR_ROOT);
die "ERROR: BACKUP DIR $DIR_BACKUP DOES NOT EXIS!\n" unless (-d $DIR_BACKUP);
die "ERROR: 7Z DOES NOT EXIST AT $BIN_7Z!\n" unless (-f $BIN_7Z);
die "ERROR: MYSQLDUMP DOES NOT EXIST AT $BIN_MYSQLDUMP!\n" unless (-f $BIN_MYSQLDUMP);
die "ERROR: MYSQLDUMP DOES NOT EXIST AT $BIN_MYSQL!\n" unless (-f $BIN_MYSQL);

# Delete old backups
print "Removing backups older than $KEEP_HISTORY_FOR_DAYS days.\n";
CleanupOldies();

# Make new backup
print "Backing up all MySQL databases on $MYSQL_HOST...\n";
open(DBLIST, "$CMD_LIST_DB |");
while (my $dbname = <DBLIST>) {
chomp $dbname;
BackupDatabase($dbname);
}
close(DBLIST);

sub CleanupOldies
{
# Get what time we should delete files at
$KEEP_HISTORY_FOR_DAYS = 30 if ($KEEP_HISTORY_FOR_DAYS < 1);

my ($dsec,$dmin,$dhour,$dmday,$dmon,$dyear) = localtime(time - ($KEEP_HISTORY_FOR_DAYS * 24 * 60 * 60));
my $DDATE = "0000".($dyear+1900).":00".($dmon+1).":00$dmday";
my $DTIME = "00$dhour:00$dmin:00$dsec";
$DDATE =~ s/^0*([^:]{4}):0*([^:]{2}):0*([^:]{2}).*/$1$2$3/;
$DTIME =~ s/^0*([^:]{2}):0*([^:]{2}):0*([^:]{2}).*/$1$2$3/;
my $CLEANTIME = $DDATE.$DTIME;

opendir(BDIR, "$DIR_BACKUP");
while (my $bfile = readdir(BDIR)) 
{
# Does filename match?
if ($bfile =~ m/(\d{8})\s+(\d{6})/) {
my $bfiledate = $1.$2;
# Is it old?
if ($bfiledate < $CLEANTIME) {
print "Deleting old backup file: $bfile\n";
unlink "$DIR_BACKUP\\$bfile";
}
}
}
closedir(BDIR);
}

sub BackupDatabase
{
my $dbname = shift;

# Only process each DB once
return if ($DBDONE{lc($dbname)});
$DBDONE{lc($dbname)} = 1;

# Get current date
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);
my $DATE = "0000".($year+1900).":00".($mon+1).":00$mday";
my $TIME = "00$hour:00$min:00$sec";
$DATE =~ s/^0*([^:]{4}):0*([^:]{2}):0*([^:]{2}).*/$1$2$3/;
$TIME =~ s/^0*([^:]{2}):0*([^:]{2}):0*([^:]{2}).*/$1$2$3/;

# Get output filename
my ($backup_filename) = $FILETEMPLATE;
$backup_filename =~ s/!DB!/$dbname/i;
$backup_filename =~ s/!DATE!/$DATE/i;
$backup_filename =~ s/!TIME!/$TIME/i;
my ($backupfile) = "$DIR_BACKUP\\$backup_filename";

# Get output command
my ($cmd_mysqldump) = "$BIN_MYSQLDUMP $BACKUP_ARGS";
$cmd_mysqldump =~ s/!DB!/$dbname/i;
$cmd_mysqldump =~ s/!OUTFILE!/\"$backupfile\"/i;

#
# Make backup...
#
print "* Backing up database '$dbname'\nTo file: $backupfile\n";
print `$cmd_mysqldump`;
my ($dsec,$dmin,$dhour,$dmday)=localtime(time);
my $doneTime = "00".($dmday - $mday).":00".($dhour - $hour).":00".($dmin - $min).":00".($dsec - $sec);
$doneTime =~ s/^0*([^:]{2}):0*([^:]{2}):0*([^:]{2}):0*([^:]{2}).*/$1d $2:$3:$4/;
print "Backup of $dbname done in $doneTime.\n\n";

}

PostgreSQL

I recently had to do a backup and restore of my PostgreSQL database. Here, for the benefit of the other PostgreSQL user are my notes. I did my backup and restore because I was updating the database software.

  • Shutdown OpenSimulator
  • Use the pg_dump command to backup the database.
    • In my case, I did this as the postgres user. It should work as the OpenSimulator (or whatever user owns the OpenSimulator database) user.
    • pg_dump -b -Fc -C -f opensim.db.backup opensim
    • The -b option requests that large objects are included in the dump. This would be things like textures and sounds.
    • The -Fc option creates the backup file as a gzipped tar file
    • The -C option includes commands to create the database when restoring
    • The -f option specifies the name of the output file. In this case opensim.db.backup
    • There are a number of other options that can be used. For example, if you're doing the backup on a different machine, the -h option allows you to specify the machine that the database is running on.
  • Copy the opensim.db.backup file to a CD-ROM or wherever you want it saved.
  • Do what you need to do. In my case, it was upgrading the PostgreSQL software
  • While PostgreSQL was shutdown, I saved a tar of its /data directory to make it easier to recover if the upgrade went bad.
  • I also took copies of the configuration files in the /data directory so that I didn't have to recreate them.
  • Delete the /data directory
  • Initialize the new database
    • The command is initdb -D <path to /data directory>
  • Start the new PostgreSQL software (it will complain about data formats if you try this before you delete the old /data and run initdb)
  • Create the opensim user
  • Restore the data using pg_restore
    • pg_restore -C -d template1 opensim.db.backup
    • Read the PostgreSQL documentation for more information.

In my case, it seemed to work fine. After I started up OpenSim, I logged in and found everything as it should be. I would really recommend trying this first on a test Sim that you didn't care about. Also study the documentation before trying this. Your setup may be different than mine.

MS SQL

<unknown>

Taking backups of configuration files

The following files need to be saved:

  • OpenSim.ini
  • All files in config-include/
  • All region .xml or .ini files in Regions/
  • Robust.ini if you are running in grid mode OR Robust.HG.ini if you are running in grid mode with Hypergrid enabled.

Archive approach

This approach takes archive files (OARs and IARs) instead of backing up the database directly.

It is much more cumbersome than backing up the entire database as

  1. It produces much larger files if assets are referenced in more than one region or user inventory.
  2. It is very cumbersome to backup any more than a couple of user inventories this way.

Nevertheless, this approach can still be useful if you don't run a region (but can ask an administrator to save an IAR) or run a region but not the central grid services (as in the case of osgrid).

In this approach, you will need to

  1. Save an archive for every region and user inventory that you want to backup. See the OpenSimulator Archives (OARs) and Inventory Archives pages for more details.
  2. If you control any regions, you will also want to save their configuration files in the same way that you would save them when doing an entire database backup. Please see the section above for more details on this.

Example automated backup scripts

These examples below enable you to make a full-backup using OpenSimulator command tools.

Backing Up WIN

  • Console Scripts:
  • Backup & Restore console scripts
  • Batch File:
  • Full Backup Archiving for use with Scheduler or Manually initiated

Backing Up LINUX

  • Console Scripts:
  • Backup & Restore console scripts

Autobackup Module

There is an experimental Autobackup region module that will automatically save OARs at specified intervals. This is not yet well documented and should be considered experimental.

Other things to backup

If you've used any third party addons to OpenSimulator then be sure to back those up as well. For instance,

  • If you are using a web interface such as Wifi and have made any customizations, be sure to back up those files.
  • If you are using a third party services such as external Flotsam groups, you may need to backup that database separately.
Retrieved from "http://opensimulator.org/index.php?title=Backups&oldid=39279#PostgreSQL"
Personal tools
General
About This Wiki