[dak/master] 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.
---
setup/README | 28 ++--
setup/schema_68.sql | 498 ---------------------------------------------------
2 files changed, 14 insertions(+), 512 deletions(-)
diff --git a/setup/README b/setup/README
index bb100da..4b9acfb 100644
--- 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 4a00ffc..afe94e9 100644
--- a/setup/schema_68.sql
+++ b/setup/schema_68.sql
@@ -18,94 +18,9 @@ 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
--
@@ -128,176 +43,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 = '';
--
1.7.2.5
Reply to: