That looks right to me.
Did you start postgres with the tcp flag? (I think -i)?
Normally I believe it uses domain sockets, you have
To enable tcp/ip.
To be sure try psql:
Psql -h hostname -p 5432 -U user database
What do the log files say?
-g
-----Original Message-----
From: serusers-bounces(a)lists.iptel.org
[mailto:serusers-bounces@lists.iptel.org] On Behalf Of Takefumi Naito
Sent: Saturday, July 26, 2003 2:42 AM
To: serusers(a)lists.iptel.org
Subject: [Serusers] PostgreSQL
Thank you for your reply.
I tried, but location database hasn't run yet.
The machine I use are installed SER and PostgreSQL.
First, what I did is created a database "ser"
and created table "location", "subscriber" and "grp"
according to the way you teached me.
Then I did following procedure.
$psql ser
postgres=# create user seruser with password 'seruserpass';
postgres=# Grant all on table location to seruser;
postgres=# Grant all on table subscriber to seruser;
postgres=# Grant all on table grp to seruser;
In the ser.cfg file, I set db_url
"sql://seruser:seruserpass@localhost:5432/ser"
for three modules: userloc, group and auth_db.
Is that right?
Could you point my mistake out?
Best regards.
Takefumi
Hi,
Wow, I didn't think anybody was going to ever use it!
I've been running, in production, for several months.
The driver seems to work.
First, it goes without saying, but you have to get Postgres
installed on your machine, and perhaps two machines.
If you are running postgres on the same machine that is doing
the SER process, then one machine will do, otherwise, install
Postgres on the machine that runs the database, and on the machine
that compiles (and runs) SER.
Second, start the database and create a couple of tables:
CREATE TABLE subscriber (
phplib_id character varying(32) DEFAULT '' NOT NULL,
user_id character varying(100) DEFAULT '' NOT NULL,
"password" character varying(25) DEFAULT '' NOT NULL,
first_name character varying(25) DEFAULT '' NOT NULL,
last_name character varying(45) DEFAULT '' NOT NULL,
phone character varying(15) DEFAULT '' NOT NULL,
email_address character varying(50) DEFAULT '' NOT NULL,
datetime_created timestamp with time zone NOT NULL,
datetime_modified timestamp with time zone NOT NULL,
confirmation character varying(64) DEFAULT '' NOT NULL,
flag character(1) DEFAULT 'o' NOT NULL,
sendnotification character varying(50) DEFAULT '' NOT NULL,
greeting character varying(50) DEFAULT '' NOT NULL,
ha1 character varying(128) DEFAULT '' NOT NULL,
"domain" character varying(128) DEFAULT '' NOT NULL,
ha1b character varying(128) DEFAULT '' NOT NULL,
perms character varying(32),
allow_find character(1) DEFAULT '0' NOT NULL,
timezone character varying(128),
first_reg timestamp with time zone,
last_reg timestamp with time zone
);
CREATE UNIQUE INDEX subscriber_primary ON subscriber USING btree
("domain", user_id);
CREATE TABLE "location" (
user_id character varying(50) DEFAULT '' NOT NULL,
"domain" character varying(100) DEFAULT '' NOT NULL,
contact character varying(255) DEFAULT '' NOT NULL,
expires timestamp with time zone,
q numeric(10,2),
callid character varying(255),
cseq numeric(11,0),
last_modified timestamp with time zone DEFAULT now() NOT NULL,
replicate numeric(10,0),
state numeric(3,0)
);
CREATE INDEX location_primary ON "location" USING btree (user_id);
CREATE TABLE grp (
user_id character varying(50) NOT NULL,
grp character varying(50) NOT NULL,
last_modified timestamp with time zone DEFAULT now() NOT NULL,
"domain" character varying(128)
);
CREATE UNIQUE INDEX grp_primary ON grp USING btree (user_id, grp);
Create a user in the database, for instance:
Create user seruser with password 'seruserpass';
Grant permission on the tables to the newly created user:
Grant all on table location to seruser;
Grant all on table subscriber to seruser;
Grant all on table grp to seruser;
Ok, this is the basic database.
You need this line in the ser.cfg file:
loadmodule "/usr/ser/lib/ser/modules/postgres.so"
modparam("usrloc","db_mode",1)
modparam("usrloc","user_column","user_id")
modparam("usrloc","db_url",
"sql://seruser:seruserpass@db.host.name.or.ip:5432/dbname")
modparam("group","user_column","user_id")
modparam("group","db_url",
"sql://seruser:seruserpass@db.host.name.or.ip:5432/dbname")
modparam("auth_db","user_column","user_id")
modparam("auth_db","db_url",
"sql://seruser:seruserpass@db.host.name.or.ip:5432/dbname")
By the way, I had problems changing the column names.
I've got many
other
tables and applications that refer to the column names as
they were in
0.8.10. Since the column name change didn't
really affect
anything but
the
name, I left them the way they were. Changing them would be
too much work for me!
That is it. Oh, there is one other thing.
Your serctl program won't work. Use the serctl call serpgctl and
rename it serctl if you wish.
Good luck,
---greg
Greg Fausak
Addaline.com, Inc.
greg(a)addaline.com
www.addaline.com
_______________________________________________
Serusers mailing list
serusers(a)lists.iptel.org
http://lists.iptel.org/mailman/listinfo/serusers