A quick follow up to my post here. Although connecting to a MySQL database for storage of user account credentials and sync data is (very) briefly discussed on the official server setup page here, there aren’t any docs for doing the same using PostgreSQL.

Getting basic connectivity up and running to a PostgreSQL database is pretty easy. I’m not going to cover PostgreSQL setup here, so will assume you already have a server and database good to go.

My Sync server is running in an Ubuntu 10.04 x86 VirtualBox VM. In Ubuntu, we need to install the “postgresql-server-dev-8.4” package and dependencies – you can do this using Synaptic Package Manager for example. (This satisfies the error about the “pg_config” executable being missing that we would otherwise get in the subsequent steps.)

Next, we install the psycopg PostgreSQL adapter for Python, which will enable the Sync server to connect to the PostgreSQL database. Note that we install this using the Sync server utilities (in the server-full/bin directory), and not the Ubuntu package manager:

dave@Ubuntu-FirefoxSync:~/server-full$ sudo ./bin/easy_install psycopg2
Searching for psycopg2
Reading http://pypi.python.org/simple/psycopg2/
Reading http://initd.org/projects/psycopg2
Best match: psycopg2 2.4.2
Downloading http://initd.org/psycopg/tarballs/PSYCOPG-2-4/psycopg2-2.4.2.tar.gz
Processing psycopg2-2.4.2.tar.gz
Running psycopg2-2.4.2/setup.py -q bdist_egg --dist-dir /tmp/easy_install-SQwqs0/psycopg2-2.4.2/egg-dist-tmp-RtnRce
no previously-included directories found matching 'doc/src/_build'
zip_safe flag not set; analyzing archive contents...
psycopg2.tests.types_basic: module references __file__
Adding psycopg2 2.4.2 to easy-install.pth file

Installed /home/dave/server-full/lib/python2.6/site-packages/psycopg2-2.4.2-py2.6-linux-i686.egg
Processing dependencies for psycopg2
Finished processing dependencies for psycopg2

Finally, we make the relevant changes in our Firefox Server sync.conf file to point to our database, specifically, the storage and auth sections. In this example, my database is called syncdb, located on the server with the IP address of 192.168.1.4, and the database connection credentials are the user syncdbadmin with the password password:

[storage]
backend = syncstorage.storage.sql.SQLStorage
sqluri = postgresql://syncdbadmin:password@192.168.1.4/syncdb
standard_collections = false
use_quota = true
quota_size = 5120
pool_size = 100
pool_recycle = 3600
reset_on_return = true
display_config = true
create_tables = true

[auth]
backend = services.auth.sql.SQLAuth
sqluri = postgresql://syncdbadmin:password@192.168.1.4/syncdb
pool_size = 100
pool_recycle = 3600
create_tables = true
fallback_node = http://localhost:5000/

Note that contrary to what is written in the official Sync setup guide at the time of this blog entry, the correct driver name to use for the sqluri value is postgresql (i.e.as written above), not postgres as stated in the official docs.

See also: http://www.wenks.ch/fabian/mozilla-custom-sync-server/