macOS Server Fix and Restore Broken Calendars and Contacts

My Calendars and Contacts broke after a macOS Server upgrade. This is what I had to do to fix everything.

Backup all data in as many ways as possible

Time Machine and bootable Carbon Copy Clone of server

Server: Export per-user calendar and contacts items

Also see the PostgreSQL query and dump cpmmands below. The command calendarserver_export didn't work for me because the Calendar and Contacts database was broken after an upgrade of macOS Server.

mkdir -p ~/Downloads/Calendar\ and\ Contacts\ Backup/username
sudo /Applications/Server.app/Contents/ServerRoot/usr/sbin/calendarserver_export --debug -u username --calendars -d ~/Downloads/Calendar\ and\ Contacts\ Backup/username
sudo /Applications/Server.app/Contents/ServerRoot/usr/sbin/calendarserver_export --debug -u username --contacts -d ~/Downloads/Calendar\ and\ Contacts\ Backup/username

PostgreSQL dump of the Calendar and Contacts database

This is a complete hack to access the database tables and calendar .ics items directly. Fortunately, it worked for me.

Server.app>Calendar> Turn off, if this even works. Repeat for Contacts.
sudo serveradmin status calendar
sudo serveradmin status addressbook

The idea here is to figure out the PostgreSQL calendar_object.calendar_resource_id for the desired user, then save all the corresponding .ics files. Some combination of the commands pg_dump, psql, and Python's psycopg2 library are helpful.

# PostgreSQL server
sudo rm /Library/Server/Calendar\ and\ Contacts/Data/Database.xpg/cluster.pg/postmaster.pid
sudo -u _calendar /Applications/Server.app/Contents/ServerRoot/usr/bin/postgres_real -D /Library/Server/Calendar\ and\ Contacts/Data/Database.xpg/cluster.pg

# pg_dump
sudo -u _calendar /Applications/Server.app/Contents/ServerRoot/usr/bin/pg_dump -h localhost -U caldav caldav -f ./caldav.sql

# psql inspection
\d+ calendar_home
select * from calendar_home;
\d+ calendar_object
select * from calendar_object;

# psycopg2
sudo -u _calendar ipython3

import psycopg2 as pg
conn = pg.connect("host='localhost' user='caldav' dbname='caldav'")
cur = conn.cursor()
cur.execute('select * from calendar_object where calendar_resource_id = 1769;')
rows = cur.fetchall()
for row in rows:
    with open(row[2],'w') as fd:
        fd.write(row[3])

cur.execute('select * from calendar_object;')
rows = cur.fetchall()
crids = set([r[1] for r in rows])
print(crids)

Client(s): Backup and export per-user Calendar and Contacts

On each client and user account: make a copy of the raw Calendars and Contacts folders:

mkdir -p ~/Downloads/Library/Calendars
mkdir -p ~/Downloads/Library/Application\ Support/AddressBook
rsync -a ~/Library/Calendars/ ~/Downloads/Library/Calendars/
rsync -a ~/Library/Application\ Support/AddressBook ~/Downloads/Library/Application\ Support/AddressBook/

# locate specific calendars with known events
find ~/Downloads/Library/Calendars -type f -name '*.ics' -exec fgrep -li keyword_to_search_for {} ''

Use the Calendar.app and Contacts.app apps to export data. Export the data, not (necessarily) the "Archive" export option, which specifies specific server account UUIDs.

Calendar> Select server calendar, then: Calendar>File>Export>Export… and save the file in ~/Downloads
Contacts> Select all server contacts, then: Contacts>File>Export>Export vCard… and save the file in ~/Downloads

Reset the server's Calendar and Contacts configuration and data folder to default settings

Server.app> Turn off Calendar and Contacts, or
- sudo serveradmin stop calendar
- sudo serveradmin stop addressbook
Quit Server.app
sudo mv /Library/Server/Calendar\ and\ Contacts ~/Downloads/Calendar\ and\ Contacts\ -\ orig
Reboot server
sudo mkdir -m 700 /Library/Server/Calendar\ and\ Contacts && sudo chown _calendar:_calendar /Library/Server/Calendar\ and\ Contacts
Wait a few seconds. Server.app will (should) recreate the default configuration and data within /Library/Server/Calendar\ and\ Contacts
- ls -ld /Library/Server/Calendar\ and\ Contacts
- ls -l /Library/Server/Calendar\ and\ Contacts
Reboot server
Server.app> Turn on Calendar and contacts, or
- sudo serveradmin start calendar
- sudo serveradmin start addressbook

Client(s): Re-import all backed up data

Just to start with a clean slate, use System Preferences>Internet Accounts> Delete calendars and contacts accounts with –, then delete the contents of the Calendars and Contacts folders:

rm -fr ~/Library/Calendars/* ~/Library/Application\ Support/AddressBook/*

Finally, add the accounts back with System Preferences>Internet Accounts>+, then import backed up data:

Calendar>File>Import… import the exported file from above
Contacts>File>Import… import the exported vCard file from above

Use Finder to open any *.ics files that this process missed and import the item into Calendar.

Backup script to avoid these issues next time

umask 077
# Ensure /usr/bin/python and /opt/local/bin/gpg are found in the path
PATH="/usr/bin:/opt/local/bin:/opt/local/sbin:$PATH"

OSX_SERVER_BACKUP=/private/var/backups/osx_server_backup
TS=`date ''+%F''`
ODBACKUP_PASSWD=mypassword

if [ ! -d "$OSX_SERVER_BACKUP" ]; then
        mkdir -m 700 -p "$OSX_SERVER_BACKUP"
fi

# Remove archives older than one year
find $OSX_SERVER_BACKUP -mindepth 1 -mtime +365 -exec /bin/rm {} ';'

# Calendars and Contacts backup
rm -fr /tmp/caldav_$TS
mkdir -p -m 700 /tmp/caldav_$TS/calendars
mkdir -p -m 700 /tmp/caldav_$TS/contacts
/Applications/Server.app/Contents/ServerRoot/usr/sbin/calendarserver_export --all --calendars --directory=/tmp/caldav_$TS/calendars
/Applications/Server.app/Contents/ServerRoot/usr/sbin/calendarserver_export --all --contacts --directory=/tmp/caldav_$TS/contacts
( cd /tmp ; tar cjpf - ./caldav_$TS | /opt/local/bin/gpg -o - -c --batch --passphrase $ODBACKUP_PASSWD > ./caldav_$TS.tbz.gpg )
cp -p /tmp/caldav_$TS.tbz.gpg $OSX_SERVER_BACKUP
rm -fr /tmp/caldav_$TS /tmp/caldav_$TS.tbz.gpg

essandess/macOS Server Fix and Restore Broken Calendars and Contacts.md