SCInterface Database Optimizations and Tuning

From SCInterface Wiki

The following section provides some general advice for backing up, optimizing and tuning your SCM's PostgreSQL database. We would also highly recommend taking a look at the postgresql.org's website for more information on performing backups with PostgreSQL at http://www.postgresql.org/docs/7.3/static/backup.html.

Contents 1 Backing up your SCInterface PostgreSQL Database 2 Restoring your SCInterface PostgreSQL Database from Backup 3 Cleaning up the SCInterface PostgreSQL Database 4 Clear Out Old Log Entries

Backing up your SCInterface PostgreSQL Database

1. Stop the SCM
2. Run the following command as root where 20080101 is today's date for the filename.

   su postgres -c "pg_dump SCM_ServerDB -Fc -f db.20080101.dump"

3. Save the file in a secure location.

Restoring your SCInterface PostgreSQL Database from Backup

Assuming that the database dump was created using the command referenced in Backing up your SCInterface PostgreSQL Database:

1. If necessary, reinstall SCInterface and the SCM.
2. Make sure the version of PostgreSQL is the same as on the other system. To check, type:

   psql --version

3. Make sure that PostgreSQL is running and the SCM is stopped.
4. Run the following to restore your database:

   su postgres -c "psql SCM_ServerDB < db.20080101.dump"

5. If you are restoring this database on a different SCM or the hostname has changed, run the following to update the database:

   echo "UPDATE serverlist SET address='`hostname -f`' WHERE servertype='iGMServer';" | sudo -u postgres psql SCM_ServerDB

   1. More than likely your dbServer entries are also setup with the other hostname so run this command as well:

      echo "UPDATE serverlist SET address='localhost' WHERE servertype='dbServer';" | sudo -u postgres psql SCM_ServerDB

For more help with the postgreSQL database for the SCM, view the SCInterface_Troubleshooting_Guide.

Cleaning up the SCInterface PostgreSQL Database

The following command is useful for optimizing all of your PostgreSQL databases:

WARNING: The database won't accept connections from the SCM for like 10 seconds while running this command with -f (full) or -z (analyze) though, so you might only want to run this command during a slow period of time.

su postgres -c "vacuumdb -afzq"

Clear Out Old Log Entries

WARNING: Before proceeding, make sure that you have performed a backup of your database! SCInterface does not support manually accessing the PostgreSQL database outside of the web interface. Please contact SCInterface support directly at http://www.scinterface.com for more information.

Let's say you want to remove things from that's older than Jan 1 of this year:

1. Get the timestamp of Jan 1 of this year:

   date +%s -d 'Jan 1'

2. And you'll get a value such as this:

   1199174400

3. Next, run the following command to delete those log entries:

   echo "delete from results where timestamp < 1199174400" | su postgres -c "psql SCM_ServerDB"