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:firstname.lastname@example.org/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:email@example.com/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.