How to use Sphinx with Postgresql on ubuntu 10.10

1 createdb -U postgres test
2  psql -U postgres test
3 test=# create table test (id integer, text text);
4 insert into test (text) values ('This is a test.');
  insert into test (text) values ('I have another thing to test.');
  -- A user with a password is required.
create user test with password 'test';
alter table test owner to test;
\q
.........................................................................
Configure sphinx (replace vim with your editor of choice):

$ cd /usr/local/etc
$ sudo cp sphinx-min.conf.dist sphinx.conf
$ sudo vim sphinx.conf
 
These values worked for me. I left configuration for indexer and searchd unchanged:
source src1
{
  type = pgsql
  sql_host = localhost
  sql_user = test
  sql_pass = test
  sql_db = test
  sql_port = 5432
  sql_query = select id, text from test
  sql_query_info = SELECT * from test WHERE id=$id
}
 Index test1
{
  source = src1
  path = /var/data/test1
  docinfo = extern
  charset_type = utf-8 
} 




indexer
{
        mem_limit                               = 32M
}
 
searchd
{
        port                                    = 5432
        log                                     = /var/log/searchd.log
        query_log                               = /var/log/query.log
        read_timeout                    = 5
        max_children                    = 30
        pid_file                                = /var/log/searchd.pid
        max_matches                             = 1000
        seamless_rotate                 = 1
        preopen_indexes                 = 0
        unlink_old                              = 1
}

Reindex:
 Now go to terminal and run the below commands
$ sudo mkdir /var/data
$ sudo indexer --all


if it create some error of SELECT in line 16 in sphinx.conf then go to the and comment the line because the terminal only support the info_query. 
It will create files in /var/data




Run searchd:
$ searchd

Play:
$ search world
Sphinx 0.9.9-release (r2117)
Copyright (c) 2001-2009, Andrew Aksyonoff

using config file '/usr/local/etc/sphinx.conf'...
index 'test1': query 'world ': returned 1 matches of 1 total in 0.000 sec
words:
1. 'world': 1 documents, 1 hits

Displaying matches:
1. document=1, weight=1
 To reindex your database after inserting more data. run the command

indexer --rotate --all
it reindex your database.

This is all about the Postgresql with Phinx, for mysql you will have alot of materials on web.

Hope it will help.
Regards:
imran shahmeer