Backing Up and Reindexing The Profile Manager Database in Lion Server

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

Using ServerBackup to Backup Lion Servers

ServerBackup is a new command included in Lion Server, located in the /usr/sbin/ServerBackup directory. The ServerBackup command is used to backup the server settings for services running on a Lion Server. The command is pretty easy and straight forward to use, but does require you to be using Time Machine in order to actually run.

In the most basic form, ServerBackup is invoked to run a backup using the backup command. Commands are prefixed with a -cmd followed by the actual command. As you might be able to guess, the commandlet to fire off a backup is backup. The backup command requires a -source option which will almost always be the root of the boot volume (/):

/usr/sbin/ServerBackup -cmd backup -source /

The data backed up begins in a .ServerBackups directory on the root of the host running Time Machine. Once the backup is complete the data is moved over to the actual Time Machine volume, using a path of:

/Volumes/<TimeMachine_volume_name>/Backups.backupd/<hostname>/<date>/<GUID>/<Source_Volume_Name>/.ServerBackups

The output of a backup should look similar to the following:

2012-02-01 10:05:17.888 ServerBackup[15716:107] Error encountered creating ServerMetaDataBackupFolder at path := /.ServerBackups!
*** nextPath := 40-openDirectory.plist
*** nextPath := 45-serverSettings.plist
*** nextPath := 46-postgresql.plist
*** nextPath := 55-sharePoints.plist
*** nextPath := 65-mailServer.plist
*** nextPath := 70-webServer.plist
2012-02-01 10:05:18.480 ServerBackup[15716:107] SRC := /etc/apache2/
DST := /.ServerBackups/webServer
Failed to copy /etc/apache2/ to /.ServerBackups/webServer/etc/apache2; ret -> 0
2012-02-01 10:05:18.483 ServerBackup[15716:107] SRC := /etc/certificates/
DST := /.ServerBackups/webServer
Failed to copy /etc/certificates/ to /.ServerBackups/webServer/etc/certificates; ret -> 0
*** nextPath := 75-iChatServer.plist
*** nextPath := com.apple.ServerBackup.plist
curServicePath := /.ServerBackups/openDirectory/openDirectory.browse.plist
WARNING: Service openDirectory folder does not exist for browsing.
curServicePath := /.ServerBackups/serverSettings/serverSettings.browse.plist
WARNING: Service serverSettings folder does not exist for browsing.
curServicePath := /.ServerBackups/postgresql/postgresql.browse.plist
WARNING: Service postgresql folder does not exist for browsing.
curServicePath := /.ServerBackups/sharePoints/sharePoints.browse.plist
WARNING: Service sharePoints folder does not exist for browsing.
curServicePath := /.ServerBackups/mailServer/mailServer.browse.plist
WARNING: Service mailServer folder does not exist for browsing.
curServicePath := /.ServerBackups/webServer/webServer.browse.plist
WARNING: Service webServer folder does not exist for browsing.
curServicePath := /.ServerBackups/iChatServer/iChatServer.browse.plist
WARNING: Service iChatServer folder does not exist for browsing.

There are usually a lot of warnings, as any given server might not be in use on the server. There is a postBackupComplete commandlet that is supposed to remove the .ServerBackups directory following the backups; however, the default behavior seems to be to remove the directory without requiring that option.

You can then view the backup snapshots by path (they can also be viewed by cd’ing straight into them):

/usr/sbin/ServerBackup -cmd list

To delete a snapshot from the list shown (where <PATH> is a path from the output of list):

/usr/sbin/ServerBackup -cmd purgeSnapShot -path <PATH>

The backup files themselves are actually the service name followed by a .conf extension; however, the data in the configuration files are just the output of a serveradmin settings of the service, such as what you would get from the following:

serveradmin settings afp > afp.conf

For running services, there’s also a .status file (personally, I’d prefer a .fullstatus file instead if I had my druthers). While all services are exported, and can be manually restored by flipping that > from the above command to a <, some services can also be restored using the services commandlet. To see a list of services that are backed up specifically and can be granularly installed as an option:

/usr/sbin/ServerBackup -cmd services

To restore:

/usr/sbin/ServerBackup -cmd restore -path /Volumes/VOLUMENAME/Backups.backupdb/HOSTNAME/SNAPSHOT -target /

To restore a specific service (for example, the iCal Server):

/usr/sbin/ServerBackup -cmd restoreService -path /Volumes/VOLUMENAME/Backups.backupdb/HOSTNAME/SNAPSHOT -target / -service

Currently, ServerBackup is not included in the daily, nightly or monthly periodic scripts and it does not back up actual data, just settings, so if you’re going to rely on it, you might need to automate server settings backups as needed. The ServerBackup command does a few pretty cool things. However, there is a lot more work needed to get it to be holistic. We’ve been working on scripts for similar tasks for a long time. For more information on that see sabackup.sourceforge.net (although we’re likely to relocate it to github soon). For more information on ServerBackup itself, see the help page (no man page as of yet):

/usr/sbin/serverbackup -help

To see what version that ServerBackup is using (not actually very helpful but can be used to programatically verify ServerBackup is using the latest version):

/usr/sbin/ServerBackup -cmd version

Supposedly there is a prefs command, but I have yet to actually get it to do anything:

/usr/sbin/ServerBackup -cmd prefs

Finally, if you are scripting this stuff, don’t forget quotes (as you might have a space in the hostname). Also, a quick sanity check to determine size and make sure there’s available capacity using the size command let, which only outputs the required space for a ServerBackup backup:

/usr/sbin/ServerBackup -cmd size

Preflighting slapconfig

Mac OS X Server uses the slapconfig command to promote Open Directory Masters and Replicas. In Lion, there is less and less dependency on slapconfig as not all of the aspects of an Open Directory environment are known throughout the system when performing LDAP operations through the command line (e.g. using -createldapmasterandadmin or -create. For example, if you use the tried and true -destroyldapserver option, the Server.app will no longer be able to promote a new Master and you’ll need to use Server Admin to create and then destroy that Master again in order for Server.app to be OK with your configuration changes.

But there are things we’ll still want to use slapconfig for. One of the better things is to actually check the environment to make sure that it is suitable for being an Open Directory server. For starters, let’s check the version of slapconfig:

/usr/sbin/slapconfig -ver

The version should be 1.2 or higher. However, as with Apache and a few other services, Apple has forked the build from the open source community, so let’s also look at the Apple Version of slapconfig. This is done using a hidden option: -appleversion. To run this, just run the option with slapconfig as follows:

/usr/sbin/slapconfig -appleversion

Then, let’s look at running slapconfig to check that the machine is suitable to be a Master. The command to do so is another hidden option, -preflightmaster. The -preflightmaster option uses the same syntax as -createldapmasterandadmin (and should at this point always be used as a sanity check prior to running -createldapmasterandadmin). Syntax as follows, where positions 1, 2 and 3 are the short name, long name and UID of the initial directory admin account:

/usr/sbin/slapconfig -preflightmaster diradmin "Directory Administrator" 1050

The slapconfig command can also be used to preflight a replica prior to promotion. The syntax there is the same as the -createreplica syntax, used as follows, assuming the master has an IP address of 172.16.2.23:

/usr/sbin/slapconfig -preflightreplica 172.16.2.23 diradmin

Additionally, there are other hidden options for handling all of the certificates that get created, deleted and managed as part of the Open Directory creation process (e.g. -addcaforreplica and -restorerootca), Kerberos (e.g. -cankerberize) as well as handling relays (e.g. -getrelayconfig).

Removing Apps from Profile Manager Using Postgres

There aren’t any options in Lion Server’s Profile Manager to remove applications. There are a number of environments where this can be annoying. For example, if you are upgrading or maybe just accidentally upload an app that you don’t want people to see for the rest of the existence of the Profile Manager server. To see which applications have been installed and which have each id:

psql -U krypted -d device_management -c "select * from public.ios_applications limit 1000 offset 0;"

The above command is a standard psql command, as shown in a previous article I worked on in a previous post. But this time I’m injecting the SQL query into the psql command using the -c option. This expands to output a list of each row in the iOS_applications table. Once you see which apps have which unique id’s, you can then remove entries using their  identifiers (this time we’re throwing in a delete instead of select using the -c):

psql -U krypted -d device_management -c "delete from public.ios_applications where id=2;"

Simply re-run without any constraints around your SQL query to clear out all of the application. For example:

psql -U krypted -d device_management -c “delete from public.ios_applications”

This works for most of the tables within Profile Manager. This allows you to clear out any information stored in its own table, such as printers, tasks, sessions, widgets, etc.

Note: you’re not going to remove apps from devices just because you cleared them from the table.

Underworld: Awakening Trailer

Watching this trailer the past week makes me very happy, so I thought I’d share!

Paper.li Test Embed

Free MacWorld Exhibit Code and iFan Pass Savings

As usual, there are a lot of great events going on at MacWorld | iWorld. If you’re interested in joining us in a couple of weeks in San Francisco for what I’m sure will be a great conference, then you can use my speaker codes to do so. To do so, during the registration process enter a PRIORITY CODE of: BNB35106

This will give 100 FREE Exhibit Only Passes OR $15.00 OFF an iFan Pass. This code is unique to me, so other speakers have codes as well. The code will stop offering free exhibit passes once the 100th person registers for this. The $15.00 savings off an iFan pass will continue through the show.

I hope to see you there!

The Elements of Style

Strunk & White’s Elements of Style is one of the best works explaining the rules of writing in the English language that has ever existed (and I’m pretty sure that sentence broke at least five of those rules). I’ve given this book to many a budding writer over the years. I’ve also recently noticed that it’s now all over the Internet, for free. For example, Bartleby has posted the 1918 edition of Elements of Style here.

If you haven’t read Elements of Style then I strongly recommend it. It’s short, concise and explains why that apostrophe goes in that one spot as opposed to the other. If you want to be a writer, this is one of your first stops on your journey.

Removing A Domain Name From A Google Search

When you are searching Google, you can restrict your search to a specific domain. For example, if you would like to find a page with the pattern “man touch” on krypted.com then you can constrain a Google search using the site: operator. The search dialog box would then read:

"man touch" site:krypted.com

But if you don’t find my posts helpful then you can remove the domain name from your Google searches, done by running the same, but with a “-” in front of the domain name, which given the above search inverted would be:

"man touch" site:-krypted.com

The resultant URL is then: http://www.google.com/search?q=site:-krypted.com. To take this a step further, you could also use this awesome application called glims from http://www.machangout.com to actually change your default search site from the standard Google.com search to the above URL and eliminate a given domain name from all future searches.

For more on the the available operators: https://sites.google.com/site/gwebsearcheducation/advanced-operators.

lsregister: How Files Are Handled in Mac OS X

The lsregister command is used to query and manage the Launch Services database, or the database that is used to determine the default application used to open files of various types. lsregister is part of Core Services, and stored in /System/Library/Frameworks/CoreServices.framework/Versions/A/Frameworks/LaunchServices.framework/Versions/A/Support. To see the options available to lsregister, run the command with no operators:

/System/Library/Frameworks/CoreServices.framework/Versions/A/Frameworks/LaunchServices.framework/Versions/A/Support/lsregister

You can dump the database to the screen using the -dump option:

/System/Library/Frameworks/CoreServices.framework/Versions/A/Frameworks/LaunchServices.framework/Versions/A/Support/lsregister -dump

You can then grep the database or redirect the output into a text file for parsing:

/System/Library/Frameworks/CoreServices.framework/Versions/A/Frameworks/LaunchServices.framework/Versions/A/Support/lsregister -dump > dump.txt

Sometimes applications don’t open with a given file type. When this happens, you can quickly and easily check if the problem has to do with the launchservices database. To do so run the open command and define the application (using the -a option) followed by the app and then the file. For example, to open an XML file called daneel.xml in TextWrangler (assuming your working directory contains bob.xml):

open -a TextWrangler.app bob.xml

You can force an application to re-register file types for that application using the -f option followed by the application path. For example, to re-register Xcode:

/System/Library/Frameworks/CoreServices.framework/Versions/A/Frameworks/LaunchServices.framework/Versions/A/Support/lsregister -f /Developer/Applications/Xcode.app

You can also unregister a specific application using the -u option. To unregister Xcode you would use the -u option:

/System/Library/Frameworks/CoreServices.framework/Versions/A/Frameworks/LaunchServices.framework/Versions/A/Support/lsregister -u /Developer/Applications/Xcode.app

The lsregister command is actually just a front-end management tool for the ~/Library/Preferences/com.apple.LaunchServices.plist file. The file’s contents can be read (in an unparsed form) using defaults:

defaults read ~/Library/Preferences/com.apple.LaunchServices

The launchservices database is also responsible for determining whether a file type is quarantined by default (and those files that are quarantined throw a message to users when opened for the first time). To disable such a feature:

defaults write com.apple.LaunchServices LSQuarantine -bool NO

The database can become pretty large and unwieldy. There are applications registered in the local domain, system domain and each user’s domain. You can always clear these out using the following command, which also recursively rebuilds based on the output of a -lint option:

/System/Library/Frameworks/CoreServices.framework/Versions/A/Frameworks/LaunchServices.framework/Versions/A/Support/lsregister -kill -r -domain local -domain system -domain user

To check the progress:

/System/Library/Frameworks/CoreServices.framework/Versions/A/Frameworks/LaunchServices.framework/Versions/A/Support/lsregister -v

To set a specific application to open a file type, use the application’s domain out of the -dump output in an LSHandlerRoleAll and the file extension in an LSHandlerContentType in the LSHandlers array of com.apple.LaunchServices, as follows (to change txt for Text Edit – aka com.apple.textedit):

defaults write com.apple.LaunchServices LSHandlers -array '{ LSHandlerContentType = "txt"; LSHandlerRoleAll = "com.apple.textedit"; }';

You can also set the default application for a network protocol (e.g. smb://, rdp://, vnc://, http:// and https://). Because the options for lsregister leave one wanting in some ways (the commands to set file types to a specific application are a bit overly complicated one could argue), there is an awesome front end app from Andrew Mortensen, aptly called duti, available at http://duti.sourceforge.net/index.php. With duti installed, the command to set the default browser for http would be:

/usr/local/bin/duti -s com.apple.safari http

Note: When working with lsregister, one should first clear the state for that application: http://krypted.com/mac-os-x/controlling-saved-application-states.

Finally, there’s a lot that Launch Services does and is involved in. For more information on LaunchServices, check out the Apple developer library information here.

Active Directory (39)
Articles and Books (72)
Business (93)
certifications (11)
cloud (9)
Consulting (57)
FileMaker (7)
Final Cut Server (44)
Football (65)
Home Automation (9)
Interviewing (24)
iPhone (72)
Kerio (19)
Mac OS X (697)
Mac OS X Server (519)
Mac Security (306)
Mass Deployment (224)
Microsoft Exchange Server (42)
MobileMe (9)
Network Infrastructure (62)
Network Printing (3)
On the Road (57)
personal (128)
public speaking (40)
sites (135)
Social Networking (28)
SQL (9)
Time Machine (5)
Travel (10)
Ubuntu (94)
Uncategorized (42)
Unix (149)
VMware (43)
Windows Server (89)
Windows XP (102)
WordPress (33)
Xsan (143)

WP Cumulus Flash tag cloud by Roy Tanck and Luke Morton requires Flash Player 9 or better.

• Recent Posts

  • Backing Up and Reindexing The Profile Manager Database in Lion Server
  • Using ServerBackup to Backup Lion Servers
  • Preflighting slapconfig
  • Removing Apps from Profile Manager Using Postgres
  • Underworld: Awakening Trailer
  • Paper.li Test Embed
  • Free MacWorld Exhibit Code and iFan Pass Savings
  • The Elements of Style
  • Removing A Domain Name From A Google Search
  • lsregister: How Files Are Handled in Mac OS X

• Categories

  • Active Directory
  • Articles and Books
  • Business
  • certifications
  • cloud
  • Consulting
  • FileMaker
  • Final Cut Server
  • Football
  • Home Automation
  • Interviewing
  • iPhone
  • Kerio
  • Mac OS X
  • Mac OS X Server
  • Mac Security
  • Mass Deployment
  • Microsoft Exchange Server
  • MobileMe
  • Network Infrastructure
  • Network Printing
  • On the Road
  • personal
  • public speaking
  • sites
  • Social Networking
  • SQL
  • Time Machine
  • Travel
  • Ubuntu
  • Uncategorized
  • Unix
  • VMware
  • Windows Server
  • Windows XP
  • WordPress
  • Xsan

• Archives

  • February 2012
  • January 2012
  • December 2011
  • November 2011
  • October 2011
  • September 2011
  • August 2011
  • July 2011
  • June 2011
  • May 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • October 2010
  • September 2010
  • August 2010
  • July 2010
  • June 2010
  • May 2010
  • April 2010
  • March 2010
  • February 2010
  • January 2010
  • December 2009
  • November 2009
  • October 2009
  • September 2009
  • August 2009
  • July 2009
  • June 2009
  • May 2009
  • April 2009
  • March 2009
  • February 2009
  • January 2009
  • December 2008
  • November 2008
  • October 2008
  • September 2008
  • August 2008
  • July 2008
  • June 2008
  • May 2008
  • April 2008
  • March 2008
  • February 2008
  • January 2008
  • December 2007
  • November 2007
  • October 2007
  • September 2007
  • August 2007
  • July 2007
  • June 2007
  • May 2007
  • April 2007
  • March 2007
  • February 2007
  • January 2007
  • December 2006
  • November 2006
  • October 2006
  • September 2006
  • August 2006
  • July 2006
  • June 2006
  • May 2006
  • April 2006
  • March 2006
  • February 2006
  • January 2006
  • December 2005
  • November 2005
  • October 2005
  • September 2005
  • August 2005
  • July 2005
  • June 2005
  • May 2005
  • April 2005
  • March 2005
  • February 2005
  • January 2005
  • December 2004

• Blogroll

  • Development Blog
  • Documentation
  • Plugins
  • Suggest Ideas
  • Support Forum
  • Themes
  • WordPress Planet

• Meta

  • Log in
  • Entries RSS
  • Comments RSS