Calendars and Contacts - resetting Contacts for duplicate entries

Hello all,

the following applies to El Capitan with Server 5.2 and Sierra with Server 5.3.1. Maybe, High Sierra behaves the same way, I simply don’t know yet. If you want to comprehend the operations outlined below, do it at your own risk, but be assured that there is no need to manipulate any of the server’s config files or directories.

It seems to be a common problem, that the contacts database of Server gets messed up with several duplicates of persons and groups, also. And things can get really bad, so that there is no connection of „Contacts“ of the client computers to the contacts service of „Server“ possible anymore. This is the case for some servers here. For the calendar service data is huge and doesn’ t make any trouble, we are looking for possibilities to keep calendar data intact but reset contacts data.

The records of both services are stored in the same PostgreSQL database. Therefore, deactivating „Server“ by dragging it out of folder „Applications“, deleting the „Calendar and Contacts“ folder, moving „Server“ back to „Applications“ and starting „Server“ to let it run the migration routine is not an option for this scenario - all events and reminders would be erased, too.

So, I was looking for a way to dive into the database, hoping to be able to clean it up someway.

First step was to ssh with admin credentials to the server

ssh ladmin@<serverHostnameOrIPAdressHere>

Then, if you try to get a list of databases in use you got an error message

server:/ ladmin$ psql -l

psql: could not connect to server: No such file or directory

Is the server running locally and accepting

connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

PostgreSQL tries to connect via TCP port 5432 if TCP connections are enabled (TCP is disabled by default) or via an Unix domain socket which only works locally. Here, the socket file is not existing. One may do a search by the „find“ command and one would find several files of that name - each for a particular PostgreSQL instance for several services:

server:/ ladmin$ sudo find / -name .s.PGSQL.5432 -ls

Password:

find: /dev/fd/pgsql_socket: No such file or directory

find: /dev/fd/pgsql_socket: No such file or directory

27031032 0 srwxrwx--- 1 _devicemgr _devicemgr 0 Nov 17 10:32 /Library/Server/ProfileManager/Config/var/PostgreSQL/.s.PGSQL.5432

27031066 0 srwxrwx--- 1 _xserverdocs _xserverdocs 0 Nov 17 10:34 /Library/Server/ServerDocs/Database.xpg/sockets/.s.PGSQL.5432

27141725 0 srwxrwx--- 1 _teamsserver _teamsserver 0 Nov 17 11:17 /Library/Server/Wiki/PostgresSocket/.s.PGSQL.5432

27031457 0 srwxrwx--- 1 _calendar _calendar 0 Nov 17 10:33 /private/var/run/caldavd/PostgresSocket/.s.PGSQL.5432

The desired username of the instance of interest is „_calendar“ (without quotes) and the path to the socket file is /private/var/run/caldavd/PostgresSocket/.s.PGSQL.5432

To make the psql command find the socket file one may create a symbolic link in the default directory but this is not necessary for the following and it won’t help establishing connections to the other instances (If you desperately want to create this link, here is the command - but I do not recommend! „ln -s /private/var/run/caldavd/PostgresSocket/.s.PGSQL.5432 /var/pgsql_socket/„ without quotes)

With known username and socket path you can expand the psql command - getting another kind of error: „Permission denied“:

server:/ ladmin$ psql -U _calendar -h /private/var/run/caldavd/PostgresSocket/ -l

psql: could not connect to server: Permission denied

Is the server running locally and accepting

connections on Unix domain socket "/private/var/run/caldavd/PostgresSocket//.s.PGSQL.5432"?

This is because user „ladmin“ has no permission to use the desired resources. But „root“ has. So use „sudo“:

server:/ ladmin$ sudo psql -U _calendar -h /private/var/run/caldavd/PostgresSocket/ -l

Password:

List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges

-----------+--------+----------+---------+-------+----------------------

caldav | caldav | UTF8 | C | C |

postgres | caldav | UTF8 | C | C |

template0 | caldav | UTF8 | C | C | =c/caldav +

| | | | | caldav=CTc/caldav

template1 | caldav | UTF8 | C | C | caldav=CTc/caldav +

| | | | | _calendar=CTc/caldav+

| | | | | =c/caldav

(4 rows)

Here you are! This output matches the corresponding section of the output of the command

sudo /Applications/Server.app/Contents/ServerRoot/usr/sbin/calendarserver_diagnose

So now one can connect to the Calendar and Contacts-database named „caldav“:

server:/ ladmin$ sudo psql -U _calendar -h /private/var/run/caldavd/PostgresSocket/ -d caldav

psql (9.4.6)

Type "help" for help.

caldav=# \d

List of relations

Schema | Name | Type | Owner

--------+-------------------------------+----------+--------

public | abo_foreign_members | table | caldav

public | abo_members | table | caldav

public | addressbook_home | table | caldav

public | addressbook_home_metadata | table | caldav

public | addressbook_object | table | caldav

public | addressbook_object_kind | table | caldav

public | addressbook_object_revisions | table | caldav

public | apn_purging_work | table | caldav

(… pressing spacebar to scroll through the list …)

public | inbox_cleanup_work | table | caldav

public | inbox_remove_work | table | caldav

public | instance_id_seq | sequence | caldav

public | job | table | caldav

public | job_seq | sequence | caldav

public | migrated_home_cleanup_work | table | caldav

public | migration_cleanup_work | table | caldav

caldav=# \q

server:/ ladmin$

use

caldav=# \q

to close the connection and quit psql client.

The goal now is to find duplicate contacts and groups or to reset the whole address book database without touching exiting events and reminders.

I found the commandline not very convenient to look at the content of the fields and looked for GUI alternative. I tried „pgAdmin 4“, read somewhere, that it only can connect to the PostgreSQL-server via TCP. I tried several modifications of several instances of the files postgresql.conf, pg_hba.conf, database.yml - all to no avail - TCP was not working. „pgAdmin 4“ was not able to connect.

„pgAdmin 4“ spit the same error messages like the CLI command „psql“ did before. So I tried to use „pgAdmin 4“ the same way I finally had success with when I was working in terminal. I enabled „root“ as login user (not a good idea, normally - I know), logged into the server from the login screen as „root“, and configured a „pgAdmin 4“ new server connection in the following manner:

Host name/address: /private/var/run/caldavd/PostgresSocket

Port: 5432

Maintenance database: caldav

Username: _calendar

Actually, „pgAdmin 4“ uses similar methods to call the PostgreSQL server as „psql“ does. The „-h“ parameter stands for „Hostname“ and works with either the server hostname or its IP address (assuming TCP ist working - which is not here!) or with a Unix domain socket file - but only locally! The port number is default for PostgreSQL.

With this settings „pgAdmin 4“ (on the server itself) was able to connect to the internal Calendar and Contacts-database!

Now I am at the point where I have to decide what to do with the database’s content. It seems, the field „addressbook_home“ contains information of the users to connect to the service whereas „addressbook_object“ contains all the vCard information of the contacts?!

Is anyone experienced in manipulation these tables’ contents?

Regards from Hamburg

Message was edited by: Olaf Seifert