Backup-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 OpenSim
• Use the pg_dump command to backup the database.
  • In my case, I did this as the postgres user. It should work as the OpenSim (or whatever user owns the OpenSim 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.