Migrating the Jive SBS Database Step-by-Step

Introduction

This procedure walks you through migration of the Jive SBS 4.5 internal database to a new separate instance of PostgreSQL 8.4.  Use this walkthrough when upgrading from the initial standalone, test Jive SBS instance to a full production instance.  Though this walkthrough focuses on migration of a PostgreSQL database, the method that you will learn here can be applied to any type of Jive SBS database migration.  It will also work for Jive SBS version 4.0.  Performing the migration involves seven steps: 1) Create database dump files, 2) Create databases, 3) Create user, 4) Assign dump file ownership, 5) Import dump files, 6) Update Jive startup file, 7) Restart Jive, and 8) Test.  Only minimal comments and executable statements are presented here.  Note that each step is presented discretely - skip the exits and logouts if continuing from one step to another.  Good Luck!

Procedure

Step 1: Create Database Dump Files

On the Jive SBS machine:

sudo su - root

cd /usr/local/jive/postgres/bin

./pg_dump -U postgres sbs > /pathToYourHomeDirectory/jive_db_sbs.dump

./pg_dump -U postgres sbsanalytics > /pathToYourHomeDirectory/jive_db_sbsanalytics.dump

On the new PostgreSQL machine:

cp /pathToYourHomeDirectory/jive_db_sbs.dump /tmp/jive_db_sbs.dump

cp /pathToYourHomeDirectory/jive_db_sbsanalytics.dump /tmp/jive_db_sbsanalytics.dump

logout

Step 2: Create Databases

On the new PostgreSQL machine:

sudo su - root

su - postgres

createdb -T template0 sbs

createsb -T template0 sbs

exit

logout

Step 3: Create User

On the new PostgreSQL machine:

sudo su - root

su - postgres

psql postgres

CREATE USER sbs WITH PASSWORD 'password';

GRANT ALL PRIVILEGES ON DATABASE sbs TO sbs;

GRANT ALL PRIVILEGES ON DATABASE sbsanalytics TO sbs;

\q

exit

logout

Step 4: Assign Dump File Ownership

On the new PostgreSQL machine:

sudo su - root

chmod 755 /tmp/jive_db_sbs.dump

chown postgres /tmp/jive_db_sbs.dump

chmod 755 /tmp/jive_db_sbsanalytics.dump

chown postgres /tmp/jive_db_sbsanalytics.dump

logout

Step 5: Import Dump Files

On the new PostgreSQL machine:

sudo su - root

su - postgres

psql sbs < /tmp/jive_db_sbs.dump

psql sbsanalytics < /tmp/jive_db_sbsanalytics.dump

exit

logout

Step 6: Update the Jive Startup File

On the Jive SBS machine:

sudo su - root

cd /usr/local/jive/applications/sbs/home

cp ./jive_startup.xml ./jive_startup.xml.ORIGINAL

vi ./jive_startup.xml

Make the following edits:

<serverURL>jdbc:postgresql://[YourPostgresqlDomainName]:5432/sbs</serverURL>

<password>sbsAccountPassword</password>

:x

chmod 600 ./jive_startup.xml

logout

Step 7: Restart the Jive SBS

sudo su - root

service jive-application - restart

Step 8: Test

Open a browser and point it to your Jive SBS instance.

Summary

Congratulations! In this step-by-step walkthrough, you have been shown how to perform a migration of the Jive SBS 4.5 internal backend to a new and separate instance of PostgreSQL 8.4.  The method presented here has been successfully tested on both versions 4.0 and 4.5 of Jive SBS.  The method presented here focuses on PostgreSQL migration but may be applied to any other type of database migration.

Troubleshooting

If you attempt to perform an import of the dump files, but experience a "Permission Denied" error, perform the following checks:

• Verify that the account being used to perform the import (most likely the "postgres" account) has permission to navigate the directory path to the location of the dump files.
• Verify that the account used to perform the import (most likely the "postgres" account) has permission to read the dump files.

If, after performing this walkthrough, you test the Jive SBS instance and experience an HTTP Status 404 error, perform the following checks:

1. Verify that the password stated in the jive_startup.xml file is the same as that used to create the sbs user.
2. Verify the PostgreSQL server URL and port are correct.
3. Verify that the sbs user has been granted ALL PRIVILEGES to both the sbs and sbsanalytics databases.
4. Verify that the PostgreSQL postgresql.conf file has been configured to allow it to listen to connection attempts ("listen_addresses").
5. Verify that the port stated in the PostgreSQL postgresql.conf file is the same as that indicated in the jive_startup.xml file.
6. Verify that the PostgreSQL pg_hba.conf file has been configured to allow connections from the Jive SBS IP address.
7. Check the PostgreSQL log files located here: .../pgsql/data/pg_log/.
8. Check the Jive SBS logs located here: .../jive/var/logs/.

References

• Database: Frequently Asked Questions - How do I switch databases from, say, MySQL to Oracle? Jive SBS 4.0 Documentation
• Database Prerequisites, Jive SBS 4.0 Documentation
• Jive Database Server - Jive SBS 4.0 Documentation
• Problems migrating to new server, Jive Community Support Forums
• Operations Cookbook - Jive SBS 3.0 Administration

Notes

• Jive SBS 4.5 uses PostgreSQL 8.1 as its internal database.
• This walkthrough was performed on headless 64-bit CentOS 5.5 servers.
• This walkthrough was performed using 64-bit Jive SBS 4.5 and 64-bit PostgreSQL.