[Serusers] PostgreSQL

[Greg Fausak]

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 at addaline.com
www.addaline.com




> -----Original Message-----
> From: serusers-bounces at lists.iptel.org 
> [mailto:serusers-bounces at lists.iptel.org] On Behalf Of Takefumi Naito
> Sent: Thursday, July 24, 2003 5:30 AM
> To: serusers at lists.iptel.org
> Subject: [Serusers] PostgreSQL
> 
> 
> 
> I want to use the PostgreSQL as a location database.
> 
> I checked out the README within "ser-0.8.11pre29/modules/postgres/",
> then I looked this line " The file createtables.txt contains 
> the table definitions and index definitions.", but couldn't find it.
> 
> So please can you teach me the way of setup to use PostgreSQL?
> 
> Thank you for your help in advance.
> Takefumi 
> 
> _______________________________________________
> Serusers mailing list
> serusers at lists.iptel.org
> http://lists.iptel.org/mailman/listinfo/serusers
>