initdb.sh

PostgreSQL7.4.6 for Linux

        '********'::text as passwd, \
        valuntil, \
        useconfig \
    FROM pg_shadow;

CREATE VIEW pg_rules AS \
    SELECT \
        N.nspname AS schemaname, \
        C.relname AS tablename, \
        R.rulename AS rulename, \
        pg_get_ruledef(R.oid) AS definition \
    FROM (pg_rewrite R JOIN pg_class C ON (C.oid = R.ev_class)) \
        LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) \
    WHERE R.rulename != '_RETURN';

CREATE VIEW pg_views AS \
    SELECT \
        N.nspname AS schemaname, \
        C.relname AS viewname, \
        pg_get_userbyid(C.relowner) AS viewowner, \
        pg_get_viewdef(C.oid) AS definition \
    FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) \
    WHERE C.relkind = 'v';

CREATE VIEW pg_tables AS \
    SELECT \
        N.nspname AS schemaname, \
        C.relname AS tablename, \
        pg_get_userbyid(C.relowner) AS tableowner, \
        C.relhasindex AS hasindexes, \
        C.relhasrules AS hasrules, \
        (C.reltriggers > 0) AS hastriggers \
    FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) \
    WHERE C.relkind = 'r';

CREATE VIEW pg_indexes AS \
    SELECT \
        N.nspname AS schemaname, \
        C.relname AS tablename, \
        I.relname AS indexname, \
        pg_get_indexdef(I.oid) AS indexdef \
    FROM pg_index X JOIN pg_class C ON (C.oid = X.indrelid) \
         JOIN pg_class I ON (I.oid = X.indexrelid) \
         LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) \
    WHERE C.relkind = 'r' AND I.relkind = 'i';

CREATE VIEW pg_stats AS \
    SELECT \
        nspname AS schemaname, \
        relname AS tablename, \
        attname AS attname, \
        stanullfrac AS null_frac, \
        stawidth AS avg_width, \
        stadistinct AS n_distinct, \
        CASE 1 \
            WHEN stakind1 THEN stavalues1 \
            WHEN stakind2 THEN stavalues2 \
            WHEN stakind3 THEN stavalues3 \
            WHEN stakind4 THEN stavalues4 \
        END AS most_common_vals, \
        CASE 1 \
            WHEN stakind1 THEN stanumbers1 \
            WHEN stakind2 THEN stanumbers2 \
            WHEN stakind3 THEN stanumbers3 \
            WHEN stakind4 THEN stanumbers4 \
        END AS most_common_freqs, \
        CASE 2 \
            WHEN stakind1 THEN stavalues1 \
            WHEN stakind2 THEN stavalues2 \
            WHEN stakind3 THEN stavalues3 \
            WHEN stakind4 THEN stavalues4 \
        END AS histogram_bounds, \
        CASE 3 \
            WHEN stakind1 THEN stanumbers1[1] \
            WHEN stakind2 THEN stanumbers2[1] \
            WHEN stakind3 THEN stanumbers3[1] \
            WHEN stakind4 THEN stanumbers4[1] \
        END AS correlation \
    FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid) \
         JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum) \
         LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) \
    WHERE has_table_privilege(c.oid, 'select');

REVOKE ALL on pg_statistic FROM public;

CREATE VIEW pg_stat_all_tables AS \
    SELECT \
            C.oid AS relid, \
            N.nspname AS schemaname, \
            C.relname AS relname, \
            pg_stat_get_numscans(C.oid) AS seq_scan, \
            pg_stat_get_tuples_returned(C.oid) AS seq_tup_read, \
            sum(pg_stat_get_numscans(I.indexrelid)) AS idx_scan, \
            sum(pg_stat_get_tuples_fetched(I.indexrelid)) AS idx_tup_fetch, \
            pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins, \
            pg_stat_get_tuples_updated(C.oid) AS n_tup_upd, \
            pg_stat_get_tuples_deleted(C.oid) AS n_tup_del \
    FROM pg_class C LEFT JOIN \
         pg_index I ON C.oid = I.indrelid \
         LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) \
    WHERE C.relkind = 'r' \
    GROUP BY C.oid, N.nspname, C.relname;

CREATE VIEW pg_stat_sys_tables AS \
    SELECT * FROM pg_stat_all_tables \
    WHERE schemaname IN ('pg_catalog', 'pg_toast');

CREATE VIEW pg_stat_user_tables AS \
    SELECT * FROM pg_stat_all_tables \
    WHERE schemaname NOT IN ('pg_catalog', 'pg_toast');

CREATE VIEW pg_statio_all_tables AS \
    SELECT \
            C.oid AS relid, \
            N.nspname AS schemaname, \
            C.relname AS relname, \
            pg_stat_get_blocks_fetched(C.oid) - \
                    pg_stat_get_blocks_hit(C.oid) AS heap_blks_read, \
            pg_stat_get_blocks_hit(C.oid) AS heap_blks_hit, \
            sum(pg_stat_get_blocks_fetched(I.indexrelid) - \
                    pg_stat_get_blocks_hit(I.indexrelid)) AS idx_blks_read, \
            sum(pg_stat_get_blocks_hit(I.indexrelid)) AS idx_blks_hit, \
            pg_stat_get_blocks_fetched(T.oid) - \
                    pg_stat_get_blocks_hit(T.oid) AS toast_blks_read, \
            pg_stat_get_blocks_hit(T.oid) AS toast_blks_hit, \
            pg_stat_get_blocks_fetched(X.oid) - \
                    pg_stat_get_blocks_hit(X.oid) AS tidx_blks_read, \
            pg_stat_get_blocks_hit(X.oid) AS tidx_blks_hit \
    FROM pg_class C LEFT JOIN \
            pg_index I ON C.oid = I.indrelid LEFT JOIN \
            pg_class T ON C.reltoastrelid = T.oid LEFT JOIN \
            pg_class X ON T.reltoastidxid = X.oid \
            LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) \
    WHERE C.relkind = 'r' \
    GROUP BY C.oid, N.nspname, C.relname, T.oid, X.oid;

CREATE VIEW pg_statio_sys_tables AS \
    SELECT * FROM pg_statio_all_tables \
    WHERE schemaname IN ('pg_catalog', 'pg_toast');

CREATE VIEW pg_statio_user_tables AS \
    SELECT * FROM pg_statio_all_tables \
    WHERE schemaname NOT IN ('pg_catalog', 'pg_toast');

CREATE VIEW pg_stat_all_indexes AS \
    SELECT \
            C.oid AS relid, \
            I.oid AS indexrelid, \
            N.nspname AS schemaname, \
            C.relname AS relname, \
            I.relname AS indexrelname, \
            pg_stat_get_numscans(I.oid) AS idx_scan, \
            pg_stat_get_tuples_returned(I.oid) AS idx_tup_read, \
            pg_stat_get_tuples_fetched(I.oid) AS idx_tup_fetch \
    FROM pg_class C JOIN \
            pg_index X ON C.oid = X.indrelid JOIN \
            pg_class I ON I.oid = X.indexrelid \
            LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) \
    WHERE C.relkind = 'r';

CREATE VIEW pg_stat_sys_indexes AS \
    SELECT * FROM pg_stat_all_indexes \
    WHERE schemaname IN ('pg_catalog', 'pg_toast');

CREATE VIEW pg_stat_user_indexes AS \
    SELECT * FROM pg_stat_all_indexes \
    WHERE schemaname NOT IN ('pg_catalog', 'pg_toast');

CREATE VIEW pg_statio_all_indexes AS \
    SELECT \
            C.oid AS relid, \
            I.oid AS indexrelid, \
            N.nspname AS schemaname, \
            C.relname AS relname, \
            I.relname AS indexrelname, \
            pg_stat_get_blocks_fetched(I.oid) - \
                    pg_stat_get_blocks_hit(I.oid) AS idx_blks_read, \
            pg_stat_get_blocks_hit(I.oid) AS idx_blks_hit \
    FROM pg_class C JOIN \
            pg_index X ON C.oid = X.indrelid JOIN \
            pg_class I ON I.oid = X.indexrelid \
            LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) \
    WHERE C.relkind = 'r';

CREATE VIEW pg_statio_sys_indexes AS \
    SELECT * FROM pg_statio_all_indexes \
    WHERE schemaname IN ('pg_catalog', 'pg_toast');

CREATE VIEW pg_statio_user_indexes AS \
    SELECT * FROM pg_statio_all_indexes \
    WHERE schemaname NOT IN ('pg_catalog', 'pg_toast');

CREATE VIEW pg_statio_all_sequences AS \
    SELECT \
            C.oid AS relid, \
            N.nspname AS schemaname, \
            C.relname AS relname, \
            pg_stat_get_blocks_fetched(C.oid) - \
                    pg_stat_get_blocks_hit(C.oid) AS blks_read, \
            pg_stat_get_blocks_hit(C.oid) AS blks_hit \
    FROM pg_class C \
            LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) \
    WHERE C.relkind = 'S';

CREATE VIEW pg_statio_sys_sequences AS \
    SELECT * FROM pg_statio_all_sequences \
    WHERE schemaname IN ('pg_catalog', 'pg_toast');

CREATE VIEW pg_statio_user_sequences AS \
    SELECT * FROM pg_statio_all_sequences \
    WHERE schemaname NOT IN ('pg_catalog', 'pg_toast');

CREATE VIEW pg_stat_activity AS \
    SELECT \
            D.oid AS datid, \
            D.datname AS datname, \
            pg_stat_get_backend_pid(S.backendid) AS procpid, \
            pg_stat_get_backend_userid(S.backendid) AS usesysid, \
            U.usename AS usename, \
            pg_stat_get_backend_activity(S.backendid) AS current_query, \
            pg_stat_get_backend_activity_start(S.backendid) AS query_start \
    FROM pg_database D, \
            (SELECT pg_stat_get_backend_idset() AS backendid) AS S, \
            pg_shadow U \
    WHERE pg_stat_get_backend_dbid(S.backendid) = D.oid AND \
            pg_stat_get_backend_userid(S.backendid) = U.usesysid;

CREATE VIEW pg_stat_database AS \
    SELECT \
            D.oid AS datid, \
            D.datname AS datname, \
            pg_stat_get_db_numbackends(D.oid) AS numbackends, \
            pg_stat_get_db_xact_commit(D.oid) AS xact_commit, \
            pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback, \
            pg_stat_get_db_blocks_fetched(D.oid) - \
                    pg_stat_get_db_blocks_hit(D.oid) AS blks_read, \
            pg_stat_get_db_blocks_hit(D.oid) AS blks_hit \
    FROM pg_database D;

CREATE VIEW pg_locks AS \
    SELECT * \
    FROM pg_lock_status() AS L(relation oid, database oid, \
	transaction xid, pid int4, mode text, granted boolean);

CREATE VIEW pg_settings AS \
    SELECT * \
    FROM pg_show_all_settings() AS A \
    (name text, setting text, context text, vartype text, \
     source text, min_val text, max_val text);

CREATE RULE pg_settings_u AS \
    ON UPDATE TO pg_settings \
    WHERE new.name = old.name DO \
    SELECT set_config(old.name, new.setting, 'f');

CREATE RULE pg_settings_n AS \
    ON UPDATE TO pg_settings \
    DO INSTEAD NOTHING;

GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;

EOF
if [ "$?" -ne 0 ]; then
    exit_nicely
fi
echo "ok"

$ECHO_N "loading pg_description... "$ECHO_C
(
  cat <<EOF
    CREATE TEMP TABLE tmp_pg_description ( \
	objoid oid, \
	classname name, \
	objsubid int4, \
	description text) WITHOUT OIDS;
    COPY tmp_pg_description FROM '$POSTGRES_DESCR';
    INSERT INTO pg_description SELECT \
	t.objoid, c.oid, t.objsubid, t.description \
    FROM tmp_pg_description t, pg_class c WHERE c.relname = t.classname;
EOF
) \
	| "$PGPATH"/postgres $PGSQL_OPT template1 > /dev/null || exit_nicely
echo "ok"

# Create pg_conversion and support functions
$ECHO_N "creating conversions... "$ECHO_C
grep -v '^DROP CONVERSION' $datadir/conversion_create.sql | "$PGPATH"/postgres $PGSQL_OPT template1 > /dev/null || exit_nicely
echo "ok"

# Set most system catalogs and built-in functions as world-accessible.
# Some objects may require different permissions by default, so we
# make sure we don't overwrite privilege sets that have already been
# set (NOT NULL).
$ECHO_N "setting privileges on built-in objects... "$ECHO_C
(
  cat <<EOF
    UPDATE pg_class SET relacl = '{"=r/\\\\"$POSTGRES_SUPERUSERNAME\\\\""}' \
        WHERE relkind IN ('r', 'v', 'S') AND relacl IS NULL;
    UPDATE pg_proc SET proacl = '{"=X/\\\\"$POSTGRES_SUPERUSERNAME\\\\""}' \
        WHERE proacl IS NULL;
    UPDATE pg_language SET lanacl = '{"=U/\\\\"$POSTGRES_SUPERUSERNAME\\\\""}' \
        WHERE lanpltrusted;
    GRANT USAGE ON SCHEMA pg_catalog TO PUBLIC;
    GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;
EOF
) \
	| "$PGPATH"/postgres $PGSQL_OPT template1 > /dev/null || exit_nicely
echo "ok"

$ECHO_N "creating information schema... "$ECHO_C
"$PGPATH"/postgres $PGSQL_OPT -N template1 > /dev/null < "$datadir"/information_schema.sql || exit_nicely
(
  # Format version number to format required by information schema (09.08.0007abc).
  major_version=`echo $VERSION | sed 's/^\([0-9]*\).*/00\1/;s/.*\(..\)$/\1/'`
  minor_version=`echo $VERSION | sed 's/^[0-9]*\.\([0-9]*\).*/00\1/;s/.*\(..\)$/\1/'`
  micro_version=`echo $VERSION | sed -e 's/^[0-9]*\.[0-9]*\.\([0-9]*\).*/0000\1/' -e 't L' -e 's/.*/0000/;q' -e ': L' -e 's/.*\(....\)$/\1/'`
  letter_version=`echo $VERSION | sed 's/^.*[0-9]\([^0-9]*\)$/\1/'`
  combined_version="$major_version.$minor_version.$micro_version$letter_version"

  echo "UPDATE information_schema.sql_implementation_info SET character_value = '$combined_version' WHERE implementation_info_name = 'DBMS VERSION';"

  echo "COPY information_schema.sql_features (feature_id, feature_name, sub_feature_id, sub_feature_name, is_supported, comments) FROM '$datadir/sql_features.txt';"
) \
	| "$PGPATH"/postgres $PGSQL_OPT template1 > /dev/null || exit_nicely
echo "ok"

$ECHO_N "vacuuming database template1... "$ECHO_C

"$PGPATH"/postgres $PGSQL_OPT template1 >/dev/null <<EOF
ANALYZE;
VACUUM FULL FREEZE;
EOF
if [ "$?" -ne 0 ]; then
    exit_nicely
fi
echo "ok"

$ECHO_N "copying template1 to template0... "$ECHO_C

"$PGPATH"/postgres $PGSQL_OPT template1 >/dev/null <<EOF
CREATE DATABASE template0;

UPDATE pg_database SET \
	datistemplate = 't', \
	datallowconn = 'f' \
    WHERE datname = 'template0';

-- We use the OID of template0 to determine lastsysoid

UPDATE pg_database SET datlastsysoid = \
    (SELECT oid::int4 - 1 FROM pg_database WHERE datname = 'template0');

-- Explicitly revoke public create-schema and create-temp-table privileges
-- in template1 and template0; else the latter would be on by default

REVOKE CREATE,TEMPORARY ON DATABASE template1 FROM public;
REVOKE CREATE,TEMPORARY ON DATABASE template0 FROM public;

-- Finally vacuum to clean up dead rows in pg_database

VACUUM FULL pg_database;
EOF
if [ "$?" -ne 0 ]; then
    exit_nicely
fi
echo "ok"


##########################################################################
#
# FINISHED

echo
echo "Success. You can now start the database server using:"
echo ""
echo "    $PGPATH/postmaster -D $PGDATA"
echo "or"
# (Advertise -l option here, otherwise we have a background
#  process writing to the terminal.)
echo "    $PGPATH/pg_ctl -D $PGDATA -l logfile start"
echo

exit 0