How to use Sphinx with Postgresql on ubuntu 10.10

Summary: In Previous article we have explain how to install sphinx search on ubuntu with postgresql support and in this article we are trying to explain how to use the sphinx search with postgresql.

Step-1. Create database in postgres to set sphinx search on it.

createdb -U postgres test

Step-2. Login to the database and create table and insert data in the table
psql -U postgres test
test=# create table test (id integer, text text);
 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;
Step-3. Configure sphinx (replace vim with your editor of choice):$ cd /usr/local/etc
$ sudo cp sphinx-min.conf.dist sphinx.conf
$ sudo vim sphinx.confThese 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
mem_limit                               = 32M

port                                    = 5432
        log                                     = /var/log/searchd.log
        query_log                               = /var/log/query.log
        read_timeout                    = 5
        max_children                    = 30
        pid_file                                = /var/log/
        max_matches                             = 1000
        seamless_rotate                 = 1
        preopen_indexes                 = 0
        unlink_old                              = 1

Step-4. 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

Step-5. Run searchd:
$ searchd

$ 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
1. ‘world’: 1 documents, 1 hits

Displaying matches:
1. document=1, weight=1
Step-6. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *