From: Ansgar Burchardt Date: Wed, 9 May 2012 14:47:40 +0000 (+0200) Subject: setup/README: update for postgresql-9.1 X-Git-Url: https://git.decadent.org.uk/gitweb/?p=dak.git;a=commitdiff_plain;h=4dab90faf5d04a94cc6f0a35f7d5e8e6a11921c2 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 bb100da4..4b9acfb4 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 4a00ffc4..afe94e9b 100644 --- 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 = '';