IndigoSqlClient -- configuring for PostgreSQL

Posted on
Sun Jan 13, 2008 10:54 am
matt (support) offline
Site Admin
User avatar
Posts: 13660
Joined: Jan 27, 2003
Location: Texas

IndigoSqlClient -- configuring for PostgreSQL

IndigoSqlClient configuration for PostgreSQL

UPDATE: The IndigoSqlClient has been replaced in Indigo 5.1.2 by the SQL Logger Plugin, which provides additional functionality and several improvements. This forum thread has been locked, so please post any questions about the SQL Logger Plugin in a new thread.

Note that the old IndigoSqlClient client will be removed by the installer, and the old database format (table names and rows defined in the tables) is not compatible with the new plugin. If you are using the old IndigoSqlClient then please read over the documentation on the new plugin so you can make adjustments to your scripts/SQL to be compatible with the new plugin.


PREVIOUS CONFIGURATION DETAILS ON DEPRECATED SQL CLIENT:

The IndigoSqlClient can be used to efficiently save Indigo Device state changes, Variable value changes, and Event Log messages to either a SQLite database or a PostgreSQL database. This allows Indigo to integrate with other applications or systems, and allows for historical data tracking. Although this is a powerful and requested feature, it isn't for the non-technical. If you don't know what a PostgreSQL database is, then you should read some online tutorials before deciding if you need this functionality.

Configuring and using SQLite is easier than PostgreSQL, so if you do not have a specific need for PostreSQL, then you might want to read about how to use SQLite and Indigo.

System Requirements
  • Mac OS X 10.4.11 or greater
  • PostgreSQL 8.3.x or greater
  • Indigo Server 2.5 or greater
Installation and Configuration Instructions

I. Install PostgreSQL. PostgreSQLForMac.com is a good place to download a binary installer so you don't have to compile the project yourself. I downloaded the unified installer then ran the Server package, which includes both the server and some useful client/admin tools. I also recommend installing pgAdmin3, which is a very good database admin tool that runs on OS X (and several other platforms).

For installation of PostgreSQL, I followed the general instructions on Russ Brook's site. But they are a bit old and there were some steps I believe you can skip and other steps that are missing. Here is what I did (your mileage may vary -- I'm not a PostgreSQL expert!):

. . 1. After running the Server package installer, I added the path to its bin folder to my .bash_profile file:
Code: Select all
echo 'export PATH=$PATH:/Library/PostgreSQL8/bin' >> ~/.bash_profile

. . 2. Next we need to set the default character encoding to UTF-8. Indigo requires that your database use UTF-8 encoding, so if you skip this it may not work. You may not have to do this on Leopard, but it won't hurt to do it regardless. First we delete the database:
Code: Select all
sudo rm -R /Library/PostgreSQL8/data

And then recreate it using UTF-8:
Code: Select all
sudo mkdir /Library/PostgreSQL8/data
sudo chown postgres:postgres /Library/PostgreSQL8/data
sudo -u postgres initdb -E utf8 --locale=en_US.UTF-8 /Library/PostgreSQL8/data

Now all new databases will use UTF-8. Lastly, I had problems with the log folder and file not having the correct owner which prevented the postgres server from starting. The owner should be postgres, which is the user automatically created by the PostgreSQL installer. I fixed it by doing:
Code: Select all
sudo chown postgres:postgres /Library/PostgreSQL8/log
sudo chown postgres:postgres /Library/PostgreSQL8/log/PostgreSQL8.log

. . 3. You should now be able to start the server with the Service Manager application installed in your /Applications/PostgreSQL/ folder. You can see if your server is up and running by accessing it via the command line psql tool:
Code: Select all
psql -U postgres

Note that once the IndigoSqlClient starts and connects to the PostgreSQL database you will not be able to stop or restart PostgreSQL via the Service Manager application. You must first stop the Indigo Server, which will stop the IndigoSqlClient process.

II. Open configuration file to set your PostgreSQL connection settings:
Code: Select all
/Library/Application Support/Perceptive Automation/Indigo 2/IndigoSqlClient/IndigoSqlClient.conf

Change the database_type specified in the file to postgres. Presuming you installed the PostgreSQL server on the same Mac as your Indigo Server, leave the host as 127.0.0.1. The default username after running the installer above will be postgres and the password will be blank. You can leave the database name as-is, or give it the name of a database you have already created.

IndigoSqlClient, when it is started, will automatically create the database if it doesn't exist, and will automatically create the database tables.

III. Turn on the <em>Log Device state history to SQLite or PostgreSQL</em> checkbox inside the Start Local Server... dialog (available via the Indigo 2->Start Local Server... menu item).

Usage and Testing

When Indigo Server starts, it will now launch the IndigoSqlClient and begin logging Device state changes, Variable value changes, and optionally Event log messages. Check the Event Log window for any error messages from the IndigoSqlClient process.

Note the client does not attempt to purge old data from the database at any point, so you'll want to manually delete old entries occasionally.
Last edited by matt (support) on Mon Apr 14, 2008 3:33 pm, edited 11 times in total.

Posted on
Fri Apr 04, 2008 9:48 pm
gregjsmith offline
Posts: 882
Joined: Apr 01, 2003
Location: Rio Rancho, NM

(No subject)

When I got to this step:
Code: Select all
sudo -u postgres initdb -E utf8 --locale=en_US.UTF-8 /Library/PostgreSQL8/data

I get the error:
Code: Select all
sudo: initdb: command not found

Posted on
Fri Apr 04, 2008 9:51 pm
matt (support) offline
Site Admin
User avatar
Posts: 13660
Joined: Jan 27, 2003
Location: Texas

(No subject)

The folder path isn't getting set correctly for some reason. You can work around that by doing this first:
Code: Select all
cd /Library/PostgreSQL8/bin

Matt

Posted on
Fri Apr 04, 2008 9:58 pm
gregjsmith offline
Posts: 882
Joined: Apr 01, 2003
Location: Rio Rancho, NM

(No subject)

I'm in the directory, i can ls and see initdb in the list, but I still get the same error.

Posted on
Fri Apr 04, 2008 10:00 pm
hwitten offline
Posts: 521
Joined: Dec 26, 2007
Location: British Columbia

(No subject)

add the ./
i.e. ./initdb when you are in the directory

Posted on
Fri Apr 04, 2008 10:08 pm
gregjsmith offline
Posts: 882
Joined: Apr 01, 2003
Location: Rio Rancho, NM

(No subject)

It finally ran. I had this output:
Code: Select all
The database cluster will be initialized with locale en_US.UTF-8.
The default text search configuration will be set to "english".

fixing permissions on existing directory /Library/PostgreSQL8/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 10
selecting default shared_buffers/max_fsm_pages ... 400kB/20000
creating configuration files ... ok
creating template1 database in /Library/PostgreSQL8/data/base/1 ... FATAL:  could not create shared memory segment: Cannot allocate memory
DETAIL:  Failed system call was shmget(key=1, size=1777664, 03600).
HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 1777664 bytes), reduce PostgreSQL's shared_buffers parameter (currently 50) and/or its max_connections parameter (currently 13).
   The PostgreSQL documentation contains more information about shared memory configuration.
child process exited with exit code 1
initdb: removing contents of data directory "/Library/PostgreSQL8/data"

Posted on
Sat Apr 05, 2008 9:03 am
matt (support) offline
Site Admin
User avatar
Posts: 13660
Joined: Jan 27, 2003
Location: Texas

(No subject)

gregjsmith wrote:
It finally ran. I had this output:
Code: Select all
...
FATAL:  could not create shared memory segment: Cannot allocate memory
DETAIL:  Failed system call was shmget(key=1, size=1777664, 03600).

Interesting -- I didn't get that error on either PostgreSQL install I did. Russ's instructions have a comment about what to do in this case:
In OS X 10.3.9 and later, create a file named /etc/sysctl.conf, containing these variable assignments, and reboot:
Code: Select all
kern.sysv.shmmax=4194304
kern.sysv.shmmin=1
kern.sysv.shmmni=32
kern.sysv.shmseg=8
kern.sysv.shmall=1024

Regards,
Matt

Posted on
Mon Mar 15, 2010 7:09 pm
hwitten offline
Posts: 521
Joined: Dec 26, 2007
Location: British Columbia

Re: IndigoSqlClient -- configuring for PostgreSQL

Phew... I had forgotten about this thread. I was tearing my hair out as an upgrade to Snow Leopard destroyed my PostgreSQL setup. No matter what I tried I couldn't get the server started.

In the process I did a fresh install of Snow Leopard and finally succeeded getting PostgeSQL going after finding this thread again and following it to a tee. Adding remote client access proved a bit challenging but eventually I got all the pieces back together, Indigo, WeatherDisplay and new website, all on same Mini. Fam Room Mini connects via LAN for it's custom indigo/weather display.

Thanks again for the clear and precise directions.

Heinz
http://sunriselake.ca

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 0 guests