Upgrading from 0.9.3 to 1.1.3 or 1.1.6

Fri Jul 13 12:43:46 CEST 2007

Peter Nixon [Fri, Jul 13, 2007 at 12:57:32PM +0300]:
> On Fri 13 Jul 2007, Nico Schottelius wrote:
> > Hello!
> >
> > I tried to move our current freeradius 0.9.3 with mysql to a new machine
> > running either 1.1.3 or 1.1.6 with postgresql.
> >
> > Converting the data was no big deal.
> >
> > But then I recognized that the sql.conf has wrong quoting for postgresql
> > (was mysql specific). Corrected that.
> 
> Thats because you are supposed to use postgresql.conf with postgresql.

Also thought that, but when I installed it from ports (FreeBSD 6.2) I
get this:

[root at ddba017 /usr/local/etc/raddb]# ls 
acct_users              hints                   radiusd.conf.working
certs                   huntgroups              samples
clients.conf            old                     snmp.conf
dictionary              preproxy_users          sql.conf
eap.conf                proxy.conf              users
example.pl              radiusd.conf

So, perhaps the port is broken.

> > But now I've another problem: When testing, it showed that the queries
> > from 1.1.3 do not fit for the tables I've imported from 0.9.3.
> >
> > Question:
> >    - Can I convert the db automatically?
> >    - Are the changes somewhere documentated, so I could convert the db
> >      manually?
> 
> They are not, but if you can post your existing schema to the list we will 
> try to help you convert it.

That would be pretty good!

I attached pg_dump -s -U pgsql radius from the new server that imported
the old schema.

Nico

-- 
Think about Free and Open Source Software (FOSS).
http://nico.schottelius.org/documentations/foss/the-term-foss/

PGP: BFE4 C736 ABE5 406F 8F42  F7CF B8BE F92A 9885 188C
-------------- next part --------------
--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;

SET SESSION AUTHORIZATION 'pgsql';

--
-- TOC entry 4 (OID 2200)
-- Name: public; Type: ACL; Schema: -; Owner: pgsql
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;

SET SESSION AUTHORIZATION 'pgsql';

SET search_path = public, pg_catalog;

--
-- TOC entry 5 (OID 1443969)
-- Name: dictionary_id_seq; Type: SEQUENCE; Schema: public; Owner: pgsql
--

CREATE SEQUENCE dictionary_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

--
-- TOC entry 15 (OID 1443971)
-- Name: dictionary; Type: TABLE; Schema: public; Owner: pgsql
--

CREATE TABLE dictionary (
    id integer DEFAULT nextval('dictionary_id_seq'::text) NOT NULL,
    "Type" character varying(30),
    "Attribute" character varying(32),
    "Value" character varying(32),
    "Format" character varying(20),
    "Vendor" character varying(32)
);

--
-- TOC entry 6 (OID 1444215)
-- Name: hints_id_seq; Type: SEQUENCE; Schema: public; Owner: pgsql
--

CREATE SEQUENCE hints_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

--
-- TOC entry 16 (OID 1444217)
-- Name: hints; Type: TABLE; Schema: public; Owner: pgsql
--

CREATE TABLE hints (
    id integer DEFAULT nextval('hints_id_seq'::text) NOT NULL,
    "HintName" character varying(32) DEFAULT ''::character varying NOT NULL,
    "GroupName" character varying(32) DEFAULT ''::character varying NOT NULL
);

--
-- TOC entry 7 (OID 1444224)
-- Name: nas_id_seq; Type: SEQUENCE; Schema: public; Owner: pgsql
--

CREATE SEQUENCE nas_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

--
-- TOC entry 17 (OID 1444226)
-- Name: nas; Type: TABLE; Schema: public; Owner: pgsql
--

CREATE TABLE nas (
    id integer DEFAULT nextval('nas_id_seq'::text) NOT NULL,
    nasname character varying(128),
    shortname character varying(32),
    ipaddr character varying(15),
    "type" character varying(30),
    ports integer,
    secret character varying(60),
    community character varying(50),
    snmp character varying(10)
);

--
-- TOC entry 8 (OID 1444241)
-- Name: online_stats_id_seq; Type: SEQUENCE; Schema: public; Owner: pgsql
--

CREATE SEQUENCE online_stats_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

--
-- TOC entry 18 (OID 1444243)
-- Name: online_stats; Type: TABLE; Schema: public; Owner: pgsql
--

CREATE TABLE online_stats (
    id integer DEFAULT nextval('online_stats_id_seq'::text) NOT NULL,
    username character varying(30),
    tstamp timestamp without time zone,
    mailed integer
);

--
-- TOC entry 9 (OID 1445727)
-- Name: radacct_radacctid_seq; Type: SEQUENCE; Schema: public; Owner: pgsql
--

CREATE SEQUENCE radacct_radacctid_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

--
-- TOC entry 19 (OID 1445729)
-- Name: radacct; Type: TABLE; Schema: public; Owner: pgsql
--

CREATE TABLE radacct (
    "RadAcctId" integer DEFAULT nextval('radacct_RadAcctId_seq'::text) NOT NULL,
    "AcctSessionId" character varying(32) DEFAULT ''::character varying NOT NULL,
    "AcctUniqueId" character varying(32) DEFAULT ''::character varying NOT NULL,
    "UserName" character varying(64) DEFAULT ''::character varying NOT NULL,
    "Realm" character varying(64) DEFAULT ''::character varying,
    "NASIPAddress" character varying(15) DEFAULT ''::character varying NOT NULL,
    "NASPortId" integer,
    "NASPortType" character varying(32),
    "AcctStartTime" timestamp without time zone DEFAULT '2006-05-12 12:09:44'::timestamp without time zone,
    "AcctStopTime" timestamp without time zone DEFAULT '2006-05-12 12:09:44'::timestamp without time zone,
    "AcctSessionTime" integer,
    "AcctAuthentic" character varying(32),
    "ConnectInfo_start" character varying(32),
    "ConnectInfo_stop" character varying(32),
    "AcctInputOctets" integer,
    "AcctOutputOctets" integer,
    "CalledStationId" character varying(10) DEFAULT ''::character varying NOT NULL,
    "CallingStationId" character varying(10) DEFAULT ''::character varying NOT NULL,
    "AcctTerminateCause" character varying(32) DEFAULT ''::character varying NOT NULL,
    "ServiceType" character varying(32),
    "FramedProtocol" character varying(32),
    "FramedIPAddress" character varying(15) DEFAULT ''::character varying NOT NULL,
    "AcctStartDelay" integer,
    "AcctStopDelay" integer
);

--
-- TOC entry 10 (OID 1743315)
-- Name: radcheck_id_seq; Type: SEQUENCE; Schema: public; Owner: pgsql
--

CREATE SEQUENCE radcheck_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

--
-- TOC entry 20 (OID 1743317)
-- Name: radcheck; Type: TABLE; Schema: public; Owner: pgsql
--

CREATE TABLE radcheck (
    id integer DEFAULT nextval('radcheck_id_seq'::text) NOT NULL,
    "UserName" character varying(64) DEFAULT ''::character varying NOT NULL,
    "Attribute" character varying(32) DEFAULT ''::character varying NOT NULL,
    "Value" character varying(253) DEFAULT ''::character varying NOT NULL,
    op character(2)
);

--
-- TOC entry 11 (OID 1743364)
-- Name: radgroupcheck_id_seq; Type: SEQUENCE; Schema: public; Owner: pgsql
--

CREATE SEQUENCE radgroupcheck_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

--
-- TOC entry 21 (OID 1743366)
-- Name: radgroupcheck; Type: TABLE; Schema: public; Owner: pgsql
--

CREATE TABLE radgroupcheck (
    id integer DEFAULT nextval('radgroupcheck_id_seq'::text) NOT NULL,
    "GroupName" character varying(64) DEFAULT ''::character varying NOT NULL,
    "Attribute" character varying(32) DEFAULT ''::character varying NOT NULL,
    "Value" character varying(253) DEFAULT ''::character varying NOT NULL,
    op character(2)
);

--
-- TOC entry 12 (OID 1743374)
-- Name: radgroupreply_id_seq; Type: SEQUENCE; Schema: public; Owner: pgsql
--

CREATE SEQUENCE radgroupreply_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

--
-- TOC entry 22 (OID 1743376)
-- Name: radgroupreply; Type: TABLE; Schema: public; Owner: pgsql
--

CREATE TABLE radgroupreply (
    id integer DEFAULT nextval('radgroupreply_id_seq'::text) NOT NULL,
    "GroupName" character varying(64) DEFAULT ''::character varying NOT NULL,
    "Attribute" character varying(32) DEFAULT ''::character varying NOT NULL,
    "Value" character varying(253) DEFAULT ''::character varying NOT NULL,
    op character(2),
    prio integer DEFAULT 0 NOT NULL
);

--
-- TOC entry 13 (OID 1743403)
-- Name: radreply_id_seq; Type: SEQUENCE; Schema: public; Owner: pgsql
--

CREATE SEQUENCE radreply_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

--
-- TOC entry 23 (OID 1743405)
-- Name: radreply; Type: TABLE; Schema: public; Owner: pgsql
--

CREATE TABLE radreply (
    id integer DEFAULT nextval('radreply_id_seq'::text) NOT NULL,
    "UserName" character varying(64) DEFAULT ''::character varying NOT NULL,
    "Attribute" character varying(32) DEFAULT ''::character varying NOT NULL,
    "Value" character varying(253) DEFAULT ''::character varying NOT NULL,
    op character(2)
);

--
-- TOC entry 14 (OID 1743458)
-- Name: usergroup_id_seq; Type: SEQUENCE; Schema: public; Owner: pgsql
--

CREATE SEQUENCE usergroup_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

--
-- TOC entry 24 (OID 1743460)
-- Name: usergroup; Type: TABLE; Schema: public; Owner: pgsql
--

CREATE TABLE usergroup (
    id integer DEFAULT nextval('usergroup_id_seq'::text) NOT NULL,
    "UserName" character varying(64) DEFAULT ''::character varying NOT NULL,
    "GroupName" character varying(64) DEFAULT ''::character varying NOT NULL
);

--
-- TOC entry 36 (OID 1445745)
-- Name: radacct_UserName_idx; Type: INDEX; Schema: public; Owner: pgsql
--

CREATE INDEX "radacct_UserName_idx" ON radacct USING btree ("UserName");

--
-- TOC entry 33 (OID 1445746)
-- Name: radacct_FramedIPAddress_idx; Type: INDEX; Schema: public; Owner: pgsql
--

CREATE INDEX "radacct_FramedIPAddress_idx" ON radacct USING btree ("FramedIPAddress");

--
-- TOC entry 29 (OID 1445747)
-- Name: radacct_AcctSessionId_idx; Type: INDEX; Schema: public; Owner: pgsql
--

CREATE INDEX "radacct_AcctSessionId_idx" ON radacct USING btree ("AcctSessionId");

--
-- TOC entry 32 (OID 1445748)
-- Name: radacct_AcctUniqueId_idx; Type: INDEX; Schema: public; Owner: pgsql
--

CREATE INDEX "radacct_AcctUniqueId_idx" ON radacct USING btree ("AcctUniqueId");

--
-- TOC entry 30 (OID 1445749)
-- Name: radacct_AcctStartTime_idx; Type: INDEX; Schema: public; Owner: pgsql
--

CREATE INDEX "radacct_AcctStartTime_idx" ON radacct USING btree ("AcctStartTime");

--
-- TOC entry 31 (OID 1445750)
-- Name: radacct_AcctStopTime_idx; Type: INDEX; Schema: public; Owner: pgsql
--

CREATE INDEX "radacct_AcctStopTime_idx" ON radacct USING btree ("AcctStopTime");

--
-- TOC entry 34 (OID 1445751)
-- Name: radacct_NASIPAddress_idx; Type: INDEX; Schema: public; Owner: pgsql
--

CREATE INDEX "radacct_NASIPAddress_idx" ON radacct USING btree ("NASIPAddress");

--
-- TOC entry 25 (OID 1443974)
-- Name: dictionary_id_key; Type: CONSTRAINT; Schema: public; Owner: pgsql
--

ALTER TABLE ONLY dictionary
    ADD CONSTRAINT dictionary_id_key UNIQUE (id);

--
-- TOC entry 26 (OID 1444222)
-- Name: hints_id_key; Type: CONSTRAINT; Schema: public; Owner: pgsql
--

ALTER TABLE ONLY hints
    ADD CONSTRAINT hints_id_key UNIQUE (id);

--
-- TOC entry 27 (OID 1444229)
-- Name: nas_id_key; Type: CONSTRAINT; Schema: public; Owner: pgsql
--

ALTER TABLE ONLY nas
    ADD CONSTRAINT nas_id_key UNIQUE (id);

--
-- TOC entry 28 (OID 1444246)
-- Name: online_stats_id_key; Type: CONSTRAINT; Schema: public; Owner: pgsql
--

ALTER TABLE ONLY online_stats
    ADD CONSTRAINT online_stats_id_key UNIQUE (id);

--
-- TOC entry 35 (OID 1445743)
-- Name: radacct_RadAcctId_key; Type: CONSTRAINT; Schema: public; Owner: pgsql
--

ALTER TABLE ONLY radacct
    ADD CONSTRAINT "radacct_RadAcctId_key" UNIQUE ("RadAcctId");

--
-- TOC entry 37 (OID 1743323)
-- Name: radcheck_id_key; Type: CONSTRAINT; Schema: public; Owner: pgsql
--

ALTER TABLE ONLY radcheck
    ADD CONSTRAINT radcheck_id_key UNIQUE (id);

--
-- TOC entry 38 (OID 1743372)
-- Name: radgroupcheck_id_key; Type: CONSTRAINT; Schema: public; Owner: pgsql
--

ALTER TABLE ONLY radgroupcheck
    ADD CONSTRAINT radgroupcheck_id_key UNIQUE (id);

--
-- TOC entry 39 (OID 1743383)
-- Name: radgroupreply_id_key; Type: CONSTRAINT; Schema: public; Owner: pgsql
--

ALTER TABLE ONLY radgroupreply
    ADD CONSTRAINT radgroupreply_id_key UNIQUE (id);

--
-- TOC entry 40 (OID 1743411)
-- Name: radreply_id_key; Type: CONSTRAINT; Schema: public; Owner: pgsql
--

ALTER TABLE ONLY radreply
    ADD CONSTRAINT radreply_id_key UNIQUE (id);

--
-- TOC entry 41 (OID 1743465)
-- Name: usergroup_id_key; Type: CONSTRAINT; Schema: public; Owner: pgsql
--

ALTER TABLE ONLY usergroup
    ADD CONSTRAINT usergroup_id_key UNIQUE (id);

--
-- TOC entry 3 (OID 2200)
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: pgsql
--

COMMENT ON SCHEMA public IS 'Standard public schema';

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20070713/0c6394ba/attachment.pgp>