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;"