Nuke+Pave iCal Server in Lion Server

It is possible to remove all of the content from a Lion Calendar server using Postgres. To aid you in doing so, Apple has built out a couple of commands to make the process easier. This will nuke everything from the server and so is not something that should be lightly done. To do so, first stop the Calendar service in the Server application. Then let’s back up the database: pg_dump -U _postgres caldav -c -f /db_backups/caldav.sql Then run dropdb to remove the database itself: dropdb -U _postgres caldav Once the database is gone, run the calendar_bootstrap_database script (I prefer doing so verbosely): calendarserver_bootstrap_database -v Now you should be able to start a fresh, clean calendar database by starting up the Calendar service in the Server application again. Note, this is dangerous if you have production data. Also note that you cannot run the bootstrap until you delete the database.

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

Working with Postgres from the Command Line in Lion Server

Mac OS X Server 10.7, Lion Server, comes with a few substantial back-end changes. One of these is the move from SQLite3 to PostgreSQL for many of the back-end databases, including Wiki and Podcast Producer (collab), Webmail (roundcubemail), iCal Server and Address Book Server (caldav) and as the back-end to the newest service in Lion Server, Profile Manager (device_management). As such, it’s now important to be able to use PostgreSQL the way we once used SQLite3, when trying to augment the data that these databases contains, as there currently aren’t a lot of options for editing this data (aside from manually of course). Postgres has a number of commands that can be used to interact with databases. The most important is probably psql. Many of the other commands simply provide automated options to psql, and over time I’ve started using psql for most everything. For example, PostgreSQL comes with a command /user/bin/createuser. However, as it’s usually more verbose with errors, I like to use psql to do this. In Lion Server, the only user that can access the Postgres databases is _postgres, installed by default with Lion Server. Because a lot of commands require passwords and we might not always want to provide write access to the databases, we’re going to create a new SuperUser, called krypted with a password of daneel. To do so, we will have to use the _postgres user to invoke psql. Any time you want to invoke psql with a different user than the user you are currently logged in as, use the -U option. To define a database, use the -d option (device_management providing access to Profile Manager data, caldav to iCal Server data roundcubemail to WebMail data and collar to Wiki data). To string this together, for accessing the device_management database as _postgres: psql -U _postgres -d device_management To then create a new user called krypted with a password of daneel we’ll use the create option, defining a user as the type of object to create, followed by the user name and then with password followed by the password (single quoted) and then createuser; as follows: device_management=# create user krypted with password 'daneel' create user; Now that there’s a valid user, let’s see what else we can do. To see all of the tables, use d: device_management=# d As you can tell, there are a bunch of them. Run the help command to see a list of SQL commands that can be run and ? for a list of psql options. To put some SQL commands into action, we’re going to look at the tasks that have been performed by Profile Manager. These are stored in the tasks table (aptly named), so we’re going to run the following SQL query (note a space followed by a semi-colon is required at the end of this thing): device_management=# select * from "public"."tasks" limit 1000 offset 0 ; Or to make it a bit simpler if you don’t have a lot of data in there yet: device_management=# select * from "public"."tasks" ; After seeing the output, you’ll probably be a little appreciative of Apple’s formatting. Next, let’s look at dumping the databases. We’re going to create a folder on the root of the volume called db_backups first: sudo mkdir /db_backups This is where these backups will end up getting stored. We’ll continue using the _postgres user for now. To do our database dumps, we’re going to use pg_dump, located at /usr/bin. First, we’ll dump the device_management database (but first we’ll stop the service and after we’ll start it – all commands from here on out also assume you’re sudo’d): serveradmin stop devicemgr pg_dump -U _postgres device_management -c -f /db_backups/device_management.sql serveradmin start devicemgr And the other 3 (stopping and starting each in the process): serveradmin stop web pg_dump -U _postgres roundcubemail -c -f /db_backups/roundcubemail.sql serveradmin start web serveradmin stop wiki pg_dump -U _postgres collab -c -f /db_backups/collab.sql serveradmin start wiki serveradmin stop addressbook serveradmin stop calendar pg_dump -U _postgres caldav -c -f /db_backups/caldav.sql serveradmin start addressbook serveradmin start calendar I haven’t had any problems running the dumps with the services running, but it’s better safe than sorry I guess. I’d probably also add some logging and maybe dump the output of full status for each service to try and track if all is well with each. Any time a service didn’t fire back up I’d then build in a sanity check for that event. There’s also a database for postgres itself, so let’s back that up as well since we’re here: pg_dump -U _postgres postgres -c -f /db_backups/postgres.sql These can then be restored using psql with the -d option to define the database being restored into and the -f option to define the file being restored from. For example, to restore collab: psql -U _postgres -d collab -f /db_backups/collab The databases are all dumped daily using pg_dumpall. These are stored in /var/pgsql but can be changed using serveradmin settings (for example, to move them to /var/pgsql1): serveradmin settings postgres:dataDir = "/var/pgsql1" If you mess up the Profile Manager database (before you put any real data into it) you can always use the /usr/share/devicemgr/backend/ script to trash the database and start anew (although I’d just use a snapshot of a VM for all this and restore from that). You can also connect to Postgres remotely, or locally through a network socket (common in Apache uses) by adding a listener. To do so, we’ll need to restart the Postgres LaunchDaemon. First, back up the file, just in case: cp org.postgresql.postgres.plist org.postgresql.postgres.plist.OLD_CSE Then stop postgres: serveradmin stop postgres Then edit the org.postgresql.postgres.plist file to change the following line: listen_addresses= To read: listen_addresses= Then fire up postgres again: serveradmin start postgres And now let’s scan port 5432 (the default TCP and UDP port used for postgres) for localhost: /Applications/Utilities/Network 5432 5432 We could have used another IP address for the listen_addresses as well, but with that _postgres user not requiring a password it didn’t really seem prudent to do so. Once you’ve enabled a socket, you’ll then be able to use one of the many GUI tools to manage postgres. Navicat is available on the Mac App Store for $5 and PGnJ is a nice, easy to use, free one. There are tons of others, but I don’t spend a lot of time in a SQL GUI and so don’t need more than a cheap app will get me. One nice thing about most of these is that they help you to form SQL queries (or they help me). This can get really nice if you are, for example, trying to get some good reporting on Profile Manager (a feature it’s a bit light on right now). Finally, don’t do any of this stuff on a production box, except maybe if you want more than nightly backups unless you think pretty hard about what you’re doing and know the exact impact of doing something. If you were to edit the databases on a live boxen, then you can safely assume that with how all of the objects in those databases use GUIDs that you’re probably going to break something, if not bring the whole house of cards tumbling down.