macOS Server Fix and Restore Broken Calendars and Contacts

macOS Server Fix and Restore Broken Calendars and Contacts.md

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