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;
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:
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
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/searchd.pid
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:
$ 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
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.