Problem with SphinxQL on Ubuntu with postgres

Question

I want configure sphinxsearch on my server with Ubuntu 18.04 and postgres 10. I use this tut:

https://www.digitalocean.com/community/tutorials/how-to-install-and-configure-sphinx-on-ubuntu-16-04

My sphinx_conf:

source src1
{
  type          = pgsql

  sql_host      = localhost
  sql_user      = postgres
  sql_pass      = my_pass
  sql_db        = test
  sql_port      = 2000

  sql_query     = \
  SELECT id, group_id, (date_added::TIMESTAMP) AS date_added, title, content \
  FROM documents

  sql_attr_uint         = group_id
  sql_attr_timestamp    = date_added
}
index test1
{
  source            = src1
  path              = /var/lib/sphinxsearch/data/test1
  docinfo           = extern
}
searchd
{
  listen            = 9312:pgsql2000
  listen            = 2000:sphinx
  log               = /var/log/sphinxsearch/searchd.log
  query_log         = /var/log/sphinxsearch/query.log
  read_timeout      = 5
  max_children      = 30
  pid_file          = /var/run/sphinxsearch/searchd.pid
  seamless_rotate   = 1
  preopen_indexes   = 1
  unlink_old        = 1
  binlog_path       = /var/lib/sphinxsearch/data
}

When i try run psql on port 2000

psql --port 9312 -h0

I get:

'Connection refused'.

Postgres_conf:

...
listen_addresses = '*'
port = 2000
...

Somebody deal with it? It's possible to run sphinx with postgres?

Answer 1

  listen            = 9312:pgsql2000

is wrong. Sphinx doesn't provide a Postgres interface. Change pgsql2000 to mysql41 and use a mysql client to connect to it, not psql.

Since your goal seems to be to index by Sphinx from Postgres I can recommend this interactive tutorial https://play.manticoresearch.com/mysql/ It's about indexing from MySQL by Manticore Search / Sphinx, but should give you a hands-on experience and understanding of how it works. After that it should be easy to just change the type from "mysql" to "pgsql" in the source and it should be the same.