View unanswered posts | View active topics It is currently Thu Apr 24, 2014 2:32 pm



Reply to topic  [ 8 posts ] 
 IndigoSqlClient -- configuring for PostgreSQL 
Author Message
Site Admin
User avatar

Joined: Jan 27, 2003
Posts: 13243
Location: Texas
Post 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.



Sun Jan 13, 2008 10:54 am
Profile WWW

Joined: Apr 01, 2003
Posts: 881
Location: Rio Rancho, NM
Post 
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

_________________
Greg In The Desert, Home Automation on Always Very Busy


Fri Apr 04, 2008 9:48 pm
Profile WWW
Site Admin
User avatar

Joined: Jan 27, 2003
Posts: 13243
Location: Texas
Post 
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


Fri Apr 04, 2008 9:51 pm
Profile WWW

Joined: Apr 01, 2003
Posts: 881
Location: Rio Rancho, NM
Post 
I'm in the directory, i can ls and see initdb in the list, but I still get the same error.

_________________
Greg In The Desert, Home Automation on Always Very Busy


Fri Apr 04, 2008 9:58 pm
Profile WWW

Joined: Dec 26, 2007
Posts: 517
Location: British Columbia
Post 
add the ./
i.e. ./initdb when you are in the directory


Fri Apr 04, 2008 10:00 pm
Profile WWW

Joined: Apr 01, 2003
Posts: 881
Location: Rio Rancho, NM
Post 
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"

_________________
Greg In The Desert, Home Automation on Always Very Busy


Fri Apr 04, 2008 10:08 pm
Profile WWW
Site Admin
User avatar

Joined: Jan 27, 2003
Posts: 13243
Location: Texas
Post 
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


Sat Apr 05, 2008 9:03 am
Profile WWW

Joined: Dec 26, 2007
Posts: 517
Location: British Columbia
Post 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


Mon Mar 15, 2010 7:09 pm
Profile WWW
Display posts from previous:  Sort by  
Reply to topic   [ 8 posts ] 

Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group.   Template designed by STSoftware.