krypted.com

Tiny Deathstars of Foulness

A common task when scaling databases is to reindex tables within the database. This process makes lookups faster and databases run butter. Reindexing becomes a pretty easy step before or after backing up the database as a general housekeeping step. To backup the database, you’ll use the pg_dump command, defining the user with -U and then the database with -d. In the case of Profile Manager, the database is device_management. Given that data is distributed across a lot of tables in the device_management database, the below script will backup the device_management database and then reindex each of the tables.

If you follow previous articles to enable the Postgres user, you would use the enabled user to access the database, editing the pguser variable to set that user. The paths to the binaries have also been made variables. This same concept could also be used with the collab, caldav, postgres and potentially roundcubemail databases, according to which databases and more specifically tables are causing systems to run slower as they grow.

pguser=krypted
psql=/usr/bin/psql
pg_dump=/usr/bin/pg_dump
backuplocation=/ServerBackup/device_management.sql
$pg_dump -U $pguser device_management -c -f $backuplocation
$psql -U $pguser -d device_management -c "REINDEX table public.apn_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.auto_join_profiles;"
$psql -U $pguser -d device_management -c "REINDEX table public.cal_dav_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.cal_sub_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.card_dav_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.certificate_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.cfprefs_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.data_files;"
$psql -U $pguser -d device_management -c "REINDEX table public.device_groups;"
$psql -U $pguser -d device_management -c "REINDEX table public.device_groups_devices;"
$psql -U $pguser -d device_management -c "REINDEX table public.devices;"
$psql -U $pguser -d device_management -c "REINDEX table public.devices_provisioning_profiles;"
$psql -U $pguser -d device_management -c "REINDEX table public.directory_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.dock_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.dock_knob_sets_system_applications;"
$psql -U $pguser -d device_management -c "REINDEX table public.email_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.energy_saver_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.enet_addresses;"
$psql -U $pguser -d device_management -c "REINDEX table public.exchange_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.general_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.group_mappings;"
$psql -U $pguser -d device_management -c "REINDEX table public.ichat_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.interface_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.ios_application_library_item_relations;"
$psql -U $pguser -d device_management -c "REINDEX table public.ios_applications;"
$psql -U $pguser -d device_management -c "REINDEX table public.knob_sets_profiles;"
$psql -U $pguser -d device_management -c "REINDEX table public.lab_sessions;"
$psql -U $pguser -d device_management -c "REINDEX table public.ldap_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.login_item_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.login_item_knob_sets_system_applications;"
$psql -U $pguser -d device_management -c "REINDEX table public.login_window_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.mac_restrictions_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.mac_restrictions_knob_sets_system_applications;"
$psql -U $pguser -d device_management -c "REINDEX table public.mac_restrictions_knob_sets_widgets;"
$psql -U $pguser -d device_management -c "REINDEX table public.mcx_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.members_profiles;"
$psql -U $pguser -d device_management -c "REINDEX table public.mobility_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.parental_controls_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.printers;"
$psql -U $pguser -d device_management -c "REINDEX table public.printers_printing_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.printing_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.privacy_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.profiles;"
$psql -U $pguser -d device_management -c "REINDEX table public.provisioning_profiles;"
$psql -U $pguser -d device_management -c "REINDEX table public.restrictions_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.scep_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.schema_migrations;"
$psql -U $pguser -d device_management -c "REINDEX table public.sessions;"
$psql -U $pguser -d device_management -c "REINDEX table public.settings;"
$psql -U $pguser -d device_management -c "REINDEX table public.software_update_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.system_applications;"
$psql -U $pguser -d device_management -c "REINDEX table public.tasks;"
$psql -U $pguser -d device_management -c "REINDEX table public.user_groups;"
$psql -U $pguser -d device_management -c "REINDEX table public.users;"
$psql -U $pguser -d device_management -c "REINDEX table public.vpn_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.web_clip_knob_sets;"
$psql -U $pguser -d device_management -c "REINDEX table public.widgets;"
$psql -U $pguser -d device_management -c "REINDEX table public.wifi_knob_sets;"

February 2nd, 2012

Posted In: iPhone, Mac OS X, Mac OS X Server, Mass Deployment

Tags: , , , , , , , , , , ,

  • Pingback: Moving Managed Preferences to Profiles | Krypted.com()

  • Justin Tyler Moore

    Excellent post! What would these commands be under El Cap?

  • Justin Tyler Moore

    I found this to work:
    sudo reindexdb devicemgr_v2m0 -U _devicemgr -h /Library/Server/ProfileManager/Config/var/PostgreSQL -e

  • Justin Tyler Moore

    and this backs up the file to the desktop:
    sudo pg_dump -f ~/Desktop/pmdump devicemgr_v2m0 -U _devicemgr -h /Library/Server/ProfileManager/Config/var/PostgreSQL