[Serusers] Re: postgres.so question

[Greg Fausak]

I looked around, I cannot find the create script.
I know I wrote the initial copy...a mystery.

Anyway, the postgres module is not 'completely' done.
It is working in production at our place, but there are
some missing parts.

For example, I had to hack my copy of serctl for the db specific
stuff.

As far as creating the tables, I don't have the script.  I'll
cobble together the commands here.   I note that many of the
tables are now missing; phonebook, pending, event, active_sessions,
acc, silo, realm, uri, server_monitoring and server_monitoring_ul.
I also note that the column names seem to change with each release
of SER.  I don't know if my tables listed here will reflect those
changes...but they can be controlled via ser.cfg, ie:

modparam("usrloc","db_mode",1)
modparam("usrloc","user_column","user_id")
modparam("usrloc","db_url",
         "sql://USERNAME:PASSWORD@postgres.hostname.com:5432/DBNAME")


Just because the tables are missing doesn't mean that the
modules that use them won't work under postgres, it simply
means that I haven't used them yet and therefore haven't created
the table and tested the functionality of that module.

I use the SER/postgres combination for subscriber and location
and that is it.  I have about 3000 current users, the postgres module
is hit relatively hard, and it hasn't experienced any problems.  Memory
usage is steady and I go months between restarts (which are necessary
when reconfiguring ser.cfg).

There is one issue with the postgres module that I experience, if you
restart the postgres database you will have to restart SER.

Good Luck,
---greg


CREATE TABLE public.subscriber
(
   phplib_id varchar(32) NOT NULL DEFAULT '',
   user_id varchar(100) NOT NULL DEFAULT '',
   password varchar(25) NOT NULL DEFAULT '',
   first_name varchar(25) NOT NULL DEFAULT '',
   last_name varchar(45) NOT NULL DEFAULT '',
   phone varchar(15) NOT NULL DEFAULT '',
   email_address varchar(50) NOT NULL DEFAULT '',
   datetime_created timestamptz NOT NULL,
   datetime_modified timestamptz NOT NULL,
   confirmation varchar(64) NOT NULL DEFAULT '',
   flag char(1) NOT NULL DEFAULT 'o',
   sendnotification varchar(50) NOT NULL DEFAULT '',
   greeting varchar(50) NOT NULL DEFAULT '',
   ha1 varchar(128) NOT NULL DEFAULT '',
   domain varchar(128) NOT NULL DEFAULT '',
   ha1b varchar(128) NOT NULL DEFAULT '',
   perms varchar(32),
   allow_find char(1) NOT NULL DEFAULT '0',
   timezone varchar(128),
   first_reg timestamptz,
   last_reg timestamptz
) WITH OIDS;

CREATE TABLE public.reserved
(
   user_id char(100) NOT NULL DEFAULT ''
) WITH OIDS;
CREATE TABLE public.missed_calls
(
   sip_from varchar(128) NOT NULL DEFAULT '',
   sip_to varchar(128) NOT NULL DEFAULT '',
   sip_status varchar(128) NOT NULL DEFAULT '',
   sip_method varchar(16) NOT NULL DEFAULT '',
   i_uri varchar(128) NOT NULL DEFAULT '',
   o_uri varchar(128) NOT NULL DEFAULT '',
   sip_callid varchar(128) NOT NULL DEFAULT '',
   user_id varchar(64) NOT NULL DEFAULT '',
   time timestamptz NOT NULL,
   timestamp timestamptz NOT NULL
) WITH OIDS;
CREATE TABLE public.location
(
   user_id varchar(50) NOT NULL DEFAULT '',
   domain varchar(100) NOT NULL DEFAULT '',
   contact varchar(255) NOT NULL DEFAULT '',
   expires timestamptz,
   q numeric(10,2),
   callid varchar(255),
   cseq numeric(11),
   last_modified timestamptz NOT NULL DEFAULT now(),
   replicate numeric(10),
   state numeric(3)
) WITH OIDS;
CREATE TABLE public.grp
(
   user_id varchar(50) NOT NULL,
   grp varchar(50) NOT NULL,
   last_modified timestamptz NOT NULL DEFAULT now(),
   domain varchar(128)
) WITH OIDS;
CREATE TABLE public.config
(
   attribute varchar(32) NOT NULL,
   value varchar(128) NOT NULL,
   user_id varchar(100) NOT NULL DEFAULT '',
   modified timestamptz
) WITH OIDS;

CREATE TABLE public.domain
(
   did varchar(32) NOT NULL,
   pdid varchar(32),
   dname varchar(60) NOT NULL,
   ddesc varchar(255) NOT NULL
) WITH OIDS;
CREATE TABLE public.aliases
(
   user_id varchar(50) NOT NULL,
   contact varchar(255) NOT NULL,
   last_modified timestamptz NOT NULL DEFAULT now(),
   expires timestamptz NOT NULL,
   q numeric(10,2),
   callid varchar(255),
   cseq int4,
   replicate int4,
   stage int4,
   state int4
) WITH OIDS;






sip wrote:

> hi, greg.
> where  i can find ser_postgres.sh
> for create postgres db ?
> or db structure ?
>   
> thanks