000032635 - Moving PostgreSQL database on Series 3 Standalone Spectrum Enterprise appliance in RSA Security Analytics 10.5.x

Document created by RSA Customer Support Employee on Jun 30, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000032635
Applies ToRSA Product Set: Security Analytics
RSA Product/Service Type: SA Malware Analysis
RSA Version/Condition: 10.5.x
Platform: CentOS
O/S Version: 6
IssueDue to size of PostgreSQL, /var volume shows 100% utilization.
 
# df -hP /var
Filesystem                  Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-var   12G   12G   85M 100% /var

 
ResolutionMoving PostgreSQL database
The contents of
/var/lib/pgsql/9.1/data can be moved to another volume such as under /var/lib/database.
After this is done it is safest to perform a Spectrum database reindex.
Moving the Database
1. SSH to spectrum appliance as root.
2. Stop services.

stop rsaMalwareDevice
stop jettysrv
stop nwbroker

3. Edit /etc/fstab and comment out the following lines which mount under /var by adding # to the start of each line:
/dev/mapper/VolGroup00-var /var                    ext4    defaults        1 2
/dev/mapper/VolGroup01-apdb /var/lib/database                    xfs    defaults        1 2
/dev/mapper/VolGroup01-apps /var/lib/rsamalware                   xfs    defaults        1 2
/dev/mapper/VolGroup00-nwhome /var/netwitness         xfs     nosuid,noatime  1 2
/dev/mapper/VolGroup01-broker /var/netwitness/broker  xfs     nosuid,noatime  1 2
/dev/mapper/VolGroup00-vartmp /var/tmp                ext4    nosuid          1 2

4. Reboot OS to ensure all of the above mounts are not mounted.
The command used could be:

shutdown -r now

or simply
reboot

5. Edit /etc/fstab to comment volumes back in (i.e. remove # from start of lines edited in Step 3)
6. On OS load mount only the source and destination mounts (/var and /var/lib/database)

mount /var
mount /var/lib/database

7. Move database files.
mkdir -p /var/lib/database/data
cd /var/lib/pgsql/9.1/data
mv * /var/lib/database/data
cd ..
rmdir /var/lib/pgsql/9.1/data
ln -s /var/lib/database/data /var/lib/pgsql/9.1/data

8. Change ownership of files back to the service.
chown -R postgres:postgres /var/lib/database/data

9. Reboot appliance (same as Step 4).

Spectrum DB Re-index
If the spectrum PostgreSQL database has become corrupted due to conditions such as lack of space then you may need to perform a DB re-index.
It is recommended to perform a backup of the database directory prior to changing database.
PostgreSQL DB Backup

stop rsaMalwareDevice
stop jettysrv
service postgresql stop

Assuming db has been moved to under /var/lib/database as per above.
tar -cjphvf /root/postgresdb_backup.tar.bz2 /var/lib/database/data

Further sadocs backup reference: http://sadocs.emc.com/0_en-us/089_105InfCtr/215_SysAdm/BackupRest/MABupRecov
Restart the postgres service.

service postgresql stop

Methodology for  performing PostgreSQL database reIndex:
1. SSH to spectrum appliance as root.
2. Stop users of database e.g. Malware Analysis service.
3. Change to the postgres user using su.
4. Log into the psql database as the spectrum user.
5. Issue reindex command.
6. Either restart services or restart appliance OS.
The commands are as follows:

stop rsaMalwareDevice
stop jettysrv

#su postgres
bash-3.2$ psql -h localhost spectrum spectrum

    Run the following query to check database before state:
SELECT
d.datname as Name,
d.datistemplate::int AS datistemplate,
pg_catalog.pg_get_userbyid(d.datdba) as Owner,
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_database_size(d.datname)
ELSE -1
END as Size
FROM pg_catalog.pg_database d
order by
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_database_size(d.datname)
ELSE NULL
END desc nulls first;

spectrum=# REINDEX DATABASE spectrum;

     Run the same SELECT query as above to check database after state.
spectrum=# \q

Note: This REINDEX command may take an extremely long time depending on the size of the database.
Due to this, when running the command it may appear to be non responsive.

Attachments

    Outcomes