setup/README: update for postgresql-9.1

Refer to the current postgresql-9.1 packages. Also use the extension versions
of debversion and plpgsql and drop them from the schema definition.

diff --git a/setup/README b/setup/README
index bb100da48184738306d8c2340cd615f6bf04c816..4b9acfb4cbd8dd0864728f966683284e3bb5e257 100644 (file)
--- a/setup/README
+++ b/setup/README
@@ -2,7 +2,7 @@ Initialising a dak database schema
 ==================================
 
 The following packages are needed for the database:
- * postgresql-9.0 postgresql-client-9.0 postgresql-9.0-debversion
+ * postgresql-9.1 postgresql-client-9.1 postgresql-9.1-debversion
 and the following packages for dak itself:
  * python-psycopg2 python-sqlalchemy python-apt gnupg dpkg-dev lintian
    binutils-multiarch python-yaml less python-ldap python-pyrss2gen python-rrdtool
@@ -19,10 +19,21 @@ The following roles are assumed to exist:
 
 For the purposes of this document, we'll be working in /srv/dak
 
-Set up the dak user on both the system and in postgres:
+Set up the dak user:
 # sudo addgroup --system ftpmaster
 # sudo adduser --system dak --ingroup ftpmaster --shell /bin/bash
-# sudo -u postgres createuser -s dak
+
+Create postgres roles and database:
+# sudo -u postgres psql
+  CREATE USER dak CREATEROLE;
+  CREATE ROLE ftpmaster;
+  CREATE ROLE ftpteam WITH ROLE ftpmaster;
+  CREATE ROLE ftptrainee WITH ROLE ftpmaster, ftpteam;
+
+  CREATE DATABASE projectb WITH OWNER dak TEMPLATE template0 ENCODING 'SQL_ASCII';
+  \c projectb
+  CREATE EXTENSION IF NOT EXISTS plpgsql;
+  CREATE EXTENSION IF NOT EXISTS debversion;
 
 Set up the dak directory:
 # sudo mkdir /etc/dak
@@ -37,17 +48,6 @@ Create a symlink to /srv/dak/dak.conf in /etc/dak
 Become the dak user:
 # sudo -u dak -s -H
 
-Create the additional roles:
-# createuser -S -R -D ftpmaster
-# createuser -S -R -D ftpteam
-# createuser -S -R -D ftptrainee
-# psql -d projectb -c "ALTER GROUP ftpteam ADD USER ftpmaster"
-# psql -d projectb -c "ALTER GROUP ftptrainee ADD USER ftpmaster"
-# psql -d projectb -c "ALTER GROUP ftptrainee ADD USER ftpteam"
-
-Create an empty database with SQL_ASCII encoding:
-# createdb -T template0 -E SQL_ASCII -O dak projectb
-
 Import the schema.  We redirect STDOUT to /dev/null as otherwise it's
 impossible to see if something fails.
 # psql -1 -f current_schema.sql -d projectb >/dev/null

diff --git a/setup/schema_68.sql b/setup/schema_68.sql
index 4a00ffc4f44cf8e94b37312063a79a9f14e6a996..afe94e9b474bc85334c12c50f33017c55ed4e373 100644 (file)
--- a/setup/schema_68.sql
+++ b/setup/schema_68.sql
@@ -18,93 +18,8 @@ CREATE SCHEMA audit;
 
 ALTER SCHEMA audit OWNER TO dak;
 
---
--- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: dak
---
-
-CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;
-
-
-ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO dak;
-
 SET search_path = public, pg_catalog;
 
---
--- Name: debversion; Type: SHELL TYPE; Schema: public; Owner: postgres
---
-
-CREATE TYPE debversion;
-
-
---
--- Name: debversionin(cstring); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION debversionin(cstring) RETURNS debversion
-    LANGUAGE internal IMMUTABLE STRICT
-    AS $$textin$$;
-
-
-ALTER FUNCTION public.debversionin(cstring) OWNER TO postgres;
-
---
--- Name: debversionout(debversion); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION debversionout(debversion) RETURNS cstring
-    LANGUAGE internal IMMUTABLE STRICT
-    AS $$textout$$;
-
-
-ALTER FUNCTION public.debversionout(debversion) OWNER TO postgres;
-
---
--- Name: debversionrecv(internal); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION debversionrecv(internal) RETURNS debversion
-    LANGUAGE internal STABLE STRICT
-    AS $$textrecv$$;
-
-
-ALTER FUNCTION public.debversionrecv(internal) OWNER TO postgres;
-
---
--- Name: debversionsend(debversion); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION debversionsend(debversion) RETURNS bytea
-    LANGUAGE internal STABLE STRICT
-    AS $$textsend$$;
-
-
-ALTER FUNCTION public.debversionsend(debversion) OWNER TO postgres;
-
---
--- Name: debversion; Type: TYPE; Schema: public; Owner: postgres
---
-
-CREATE TYPE debversion (
-    INTERNALLENGTH = variable,
-    INPUT = debversionin,
-    OUTPUT = debversionout,
-    RECEIVE = debversionrecv,
-    SEND = debversionsend,
-    CATEGORY = 'S',
-    ALIGNMENT = int4,
-    STORAGE = extended
-);
-
-
-ALTER TYPE public.debversion OWNER TO postgres;
-
---
--- Name: TYPE debversion; Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON TYPE debversion IS 'Debian package version number';
-
-
 --
 -- Name: bin_associations_id_max(); Type: FUNCTION; Schema: public; Owner: dak
 --
@@ -127,176 +42,6 @@ CREATE FUNCTION binaries_id_max() RETURNS integer
 
 ALTER FUNCTION public.binaries_id_max() OWNER TO dak;
 
---
--- Name: debversion(character); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION debversion(character) RETURNS debversion
-    LANGUAGE internal IMMUTABLE STRICT
-    AS $$rtrim1$$;
-
-
-ALTER FUNCTION public.debversion(character) OWNER TO postgres;
-
---
--- Name: debversion_cmp(debversion, debversion); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION debversion_cmp(version1 debversion, version2 debversion) RETURNS integer
-    LANGUAGE c IMMUTABLE STRICT
-    AS '$libdir/debversion', 'debversion_cmp';
-
-
-ALTER FUNCTION public.debversion_cmp(version1 debversion, version2 debversion) OWNER TO postgres;
-
---
--- Name: FUNCTION debversion_cmp(version1 debversion, version2 debversion); Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON FUNCTION debversion_cmp(version1 debversion, version2 debversion) IS 'Compare Debian versions';
-
-
---
--- Name: debversion_eq(debversion, debversion); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION debversion_eq(version1 debversion, version2 debversion) RETURNS boolean
-    LANGUAGE c IMMUTABLE STRICT
-    AS '$libdir/debversion', 'debversion_eq';
-
-
-ALTER FUNCTION public.debversion_eq(version1 debversion, version2 debversion) OWNER TO postgres;
-
---
--- Name: FUNCTION debversion_eq(version1 debversion, version2 debversion); Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON FUNCTION debversion_eq(version1 debversion, version2 debversion) IS 'debversion equal';
-
-
---
--- Name: debversion_ge(debversion, debversion); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION debversion_ge(version1 debversion, version2 debversion) RETURNS boolean
-    LANGUAGE c IMMUTABLE STRICT
-    AS '$libdir/debversion', 'debversion_ge';
-
-
-ALTER FUNCTION public.debversion_ge(version1 debversion, version2 debversion) OWNER TO postgres;
-
---
--- Name: FUNCTION debversion_ge(version1 debversion, version2 debversion); Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON FUNCTION debversion_ge(version1 debversion, version2 debversion) IS 'debversion greater-than-or-equal';
-
-
---
--- Name: debversion_gt(debversion, debversion); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION debversion_gt(version1 debversion, version2 debversion) RETURNS boolean
-    LANGUAGE c IMMUTABLE STRICT
-    AS '$libdir/debversion', 'debversion_gt';
-
-
-ALTER FUNCTION public.debversion_gt(version1 debversion, version2 debversion) OWNER TO postgres;
-
---
--- Name: FUNCTION debversion_gt(version1 debversion, version2 debversion); Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON FUNCTION debversion_gt(version1 debversion, version2 debversion) IS 'debversion greater-than';
-
-
---
--- Name: debversion_hash(debversion); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION debversion_hash(debversion) RETURNS integer
-    LANGUAGE c IMMUTABLE STRICT
-    AS '$libdir/debversion', 'debversion_hash';
-
-
-ALTER FUNCTION public.debversion_hash(debversion) OWNER TO postgres;
-
---
--- Name: debversion_larger(debversion, debversion); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION debversion_larger(version1 debversion, version2 debversion) RETURNS debversion
-    LANGUAGE c IMMUTABLE STRICT
-    AS '$libdir/debversion', 'debversion_larger';
-
-
-ALTER FUNCTION public.debversion_larger(version1 debversion, version2 debversion) OWNER TO postgres;
-
---
--- Name: debversion_le(debversion, debversion); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION debversion_le(version1 debversion, version2 debversion) RETURNS boolean
-    LANGUAGE c IMMUTABLE STRICT
-    AS '$libdir/debversion', 'debversion_le';
-
-
-ALTER FUNCTION public.debversion_le(version1 debversion, version2 debversion) OWNER TO postgres;
-
---
--- Name: FUNCTION debversion_le(version1 debversion, version2 debversion); Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON FUNCTION debversion_le(version1 debversion, version2 debversion) IS 'debversion less-than-or-equal';
-
-
---
--- Name: debversion_lt(debversion, debversion); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION debversion_lt(version1 debversion, version2 debversion) RETURNS boolean
-    LANGUAGE c IMMUTABLE STRICT
-    AS '$libdir/debversion', 'debversion_lt';
-
-
-ALTER FUNCTION public.debversion_lt(version1 debversion, version2 debversion) OWNER TO postgres;
-
---
--- Name: FUNCTION debversion_lt(version1 debversion, version2 debversion); Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON FUNCTION debversion_lt(version1 debversion, version2 debversion) IS 'debversion less-than';
-
-
---
--- Name: debversion_ne(debversion, debversion); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION debversion_ne(version1 debversion, version2 debversion) RETURNS boolean
-    LANGUAGE c IMMUTABLE STRICT
-    AS '$libdir/debversion', 'debversion_ne';
-
-
-ALTER FUNCTION public.debversion_ne(version1 debversion, version2 debversion) OWNER TO postgres;
-
---
--- Name: FUNCTION debversion_ne(version1 debversion, version2 debversion); Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON FUNCTION debversion_ne(version1 debversion, version2 debversion) IS 'debversion not equal';
-
-
---
--- Name: debversion_smaller(debversion, debversion); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION debversion_smaller(version1 debversion, version2 debversion) RETURNS debversion
-    LANGUAGE c IMMUTABLE STRICT
-    AS '$libdir/debversion', 'debversion_smaller';
-
-
-ALTER FUNCTION public.debversion_smaller(version1 debversion, version2 debversion) OWNER TO postgres;
-
 --
 -- Name: dsc_files_id_max(); Type: FUNCTION; Schema: public; Owner: dak
 --
@@ -548,249 +293,6 @@ $$;
 
 ALTER FUNCTION public.trigger_override_update() OWNER TO dak;
 
---
--- Name: >; Type: OPERATOR; Schema: public; Owner: postgres
---
-
-CREATE OPERATOR > (
-    PROCEDURE = debversion_gt,
-    LEFTARG = debversion,
-    RIGHTARG = debversion,
-    COMMUTATOR = <,
-    NEGATOR = >=
-);
-
-
-ALTER OPERATOR public.> (debversion, debversion) OWNER TO postgres;
-
---
--- Name: OPERATOR > (debversion, debversion); Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON OPERATOR > (debversion, debversion) IS 'debversion greater-than';
-
-
---
--- Name: max(debversion); Type: AGGREGATE; Schema: public; Owner: postgres
---
-
-CREATE AGGREGATE max(debversion) (
-    SFUNC = debversion_larger,
-    STYPE = debversion,
-    SORTOP = >
-);
-
-
-ALTER AGGREGATE public.max(debversion) OWNER TO postgres;
-
---
--- Name: <; Type: OPERATOR; Schema: public; Owner: postgres
---
-
-CREATE OPERATOR < (
-    PROCEDURE = debversion_lt,
-    LEFTARG = debversion,
-    RIGHTARG = debversion,
-    COMMUTATOR = >,
-    NEGATOR = >=
-);
-
-
-ALTER OPERATOR public.< (debversion, debversion) OWNER TO postgres;
-
---
--- Name: OPERATOR < (debversion, debversion); Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON OPERATOR < (debversion, debversion) IS 'debversion less-than';
-
-
---
--- Name: min(debversion); Type: AGGREGATE; Schema: public; Owner: postgres
---
-
-CREATE AGGREGATE min(debversion) (
-    SFUNC = debversion_smaller,
-    STYPE = debversion,
-    SORTOP = <
-);
-
-
-ALTER AGGREGATE public.min(debversion) OWNER TO postgres;
-
---
--- Name: space_separated_list(text); Type: AGGREGATE; Schema: public; Owner: dak
---
-
-CREATE AGGREGATE space_separated_list(text) (
-    SFUNC = space_concat,
-    STYPE = text,
-    INITCOND = ''
-);
-
-
-ALTER AGGREGATE public.space_separated_list(text) OWNER TO dak;
-
---
--- Name: <=; Type: OPERATOR; Schema: public; Owner: postgres
---
-
-CREATE OPERATOR <= (
-    PROCEDURE = debversion_le,
-    LEFTARG = debversion,
-    RIGHTARG = debversion,
-    COMMUTATOR = >=,
-    NEGATOR = >
-);
-
-
-ALTER OPERATOR public.<= (debversion, debversion) OWNER TO postgres;
-
---
--- Name: OPERATOR <= (debversion, debversion); Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON OPERATOR <= (debversion, debversion) IS 'debversion less-than-or-equal';
-
-
---
--- Name: <>; Type: OPERATOR; Schema: public; Owner: postgres
---
-
-CREATE OPERATOR <> (
-    PROCEDURE = debversion_ne,
-    LEFTARG = debversion,
-    RIGHTARG = debversion,
-    COMMUTATOR = <>,
-    NEGATOR = =
-);
-
-
-ALTER OPERATOR public.<> (debversion, debversion) OWNER TO postgres;
-
---
--- Name: OPERATOR <> (debversion, debversion); Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON OPERATOR <> (debversion, debversion) IS 'debversion not equal';
-
-
---
--- Name: =; Type: OPERATOR; Schema: public; Owner: postgres
---
-
-CREATE OPERATOR = (
-    PROCEDURE = debversion_eq,
-    LEFTARG = debversion,
-    RIGHTARG = debversion,
-    COMMUTATOR = =,
-    NEGATOR = <>
-);
-
-
-ALTER OPERATOR public.= (debversion, debversion) OWNER TO postgres;
-
---
--- Name: OPERATOR = (debversion, debversion); Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON OPERATOR = (debversion, debversion) IS 'debversion equal';
-
-
---
--- Name: >=; Type: OPERATOR; Schema: public; Owner: postgres
---
-
-CREATE OPERATOR >= (
-    PROCEDURE = debversion_ge,
-    LEFTARG = debversion,
-    RIGHTARG = debversion,
-    COMMUTATOR = <=,
-    NEGATOR = <
-);
-
-
-ALTER OPERATOR public.>= (debversion, debversion) OWNER TO postgres;
-
---
--- Name: OPERATOR >= (debversion, debversion); Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON OPERATOR >= (debversion, debversion) IS 'debversion greater-than-or-equal';
-
-
---
--- Name: debversion_ops; Type: OPERATOR CLASS; Schema: public; Owner: postgres
---
-
-CREATE OPERATOR CLASS debversion_ops
-    DEFAULT FOR TYPE debversion USING btree AS
-    OPERATOR 1 <(debversion,debversion) ,
-    OPERATOR 2 <=(debversion,debversion) ,
-    OPERATOR 3 =(debversion,debversion) ,
-    OPERATOR 4 >=(debversion,debversion) ,
-    OPERATOR 5 >(debversion,debversion) ,
-    FUNCTION 1 debversion_cmp(debversion,debversion);
-
-
-ALTER OPERATOR CLASS public.debversion_ops USING btree OWNER TO postgres;
-
---
--- Name: debversion_ops; Type: OPERATOR CLASS; Schema: public; Owner: postgres
---
-
-CREATE OPERATOR CLASS debversion_ops
-    DEFAULT FOR TYPE debversion USING hash AS
-    OPERATOR 1 =(debversion,debversion) ,
-    FUNCTION 1 debversion_hash(debversion);
-
-
-ALTER OPERATOR CLASS public.debversion_ops USING hash OWNER TO postgres;
-
-SET search_path = pg_catalog;
-
---
--- Name: CAST (character AS public.debversion); Type: CAST; Schema: pg_catalog; Owner: 
---
-
-CREATE CAST (character AS public.debversion) WITH FUNCTION public.debversion(character);
-
-
---
--- Name: CAST (public.debversion AS character); Type: CAST; Schema: pg_catalog; Owner: 
---
-
-CREATE CAST (public.debversion AS character) WITHOUT FUNCTION AS ASSIGNMENT;
-
-
---
--- Name: CAST (public.debversion AS text); Type: CAST; Schema: pg_catalog; Owner: 
---
-
-CREATE CAST (public.debversion AS text) WITHOUT FUNCTION AS IMPLICIT;
-
-
---
--- Name: CAST (public.debversion AS character varying); Type: CAST; Schema: pg_catalog; Owner: 
---
-
-CREATE CAST (public.debversion AS character varying) WITHOUT FUNCTION AS IMPLICIT;
-
-
---
--- Name: CAST (text AS public.debversion); Type: CAST; Schema: pg_catalog; Owner: 
---
-
-CREATE CAST (text AS public.debversion) WITHOUT FUNCTION AS ASSIGNMENT;
-
-
---
--- Name: CAST (character varying AS public.debversion); Type: CAST; Schema: pg_catalog; Owner: 
---
-
-CREATE CAST (character varying AS public.debversion) WITHOUT FUNCTION AS ASSIGNMENT;
-
-
 SET search_path = audit, pg_catalog;
 
 SET default_tablespace = '';