Installing onto server.africafocus.org:

To initialize the database, you should run initdb as the "pgsql" user
(if you want to use your locale for collation, edit ~pgsql/.profile
and set the locale to your preference first).

Example:

  su -l pgsql -c initdb

  You can then start PostgreSQL by running:

    /usr/local/etc/rc.d/010.pgsql.sh start

    For postmaster settings, see ~pgsql/data/postgresql.conf
    For more tips, read ~pgsql/post-install-notes


Then after initializing:

Success. You can now start the database server using:

    /usr/local/bin/postmaster -D /usr/local/pgsql/data
or
    /usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

RDS user-and-role best practices:
https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/
May well apply just as well to non-Postgresql databases
  To share a schema
      create user u with password 'pwd';
      grant connect on database d to u;  -- skip for RedShift
      create role y;  -- use 'group' instead of 'role' for RedShift
      create schema y;
      grant usage on schema s to u;
      alter user u set search_path to s;  -- set user's default schema
      create table s.t;
      grant SELECT[, INSERT, DELETE, UPDATE] on s.t TO u;

# UPDATES BY BLAINE
Fri Nov  8 14:19:00 EST 2002

Damned it, why didn't I take notes when I worked with this a couple months
ago!!

Local connections use UNIX domain sockets.

For rpm install, default location for databases is /var/lib/pgsql/data.
Main config file is /var/lib/pgsql/data/postgresql.conf.
Authentication file is /var/lib/pgsql/data/pg_hba.conf.

postgresql.conf:
    tcpip_socket = true    # if you need any networking.  Port 5432 dflt.
    virtual_host = '192.168.102.152'  # Set listen addr.

pg_hba.conf:
    For some reason "local... sameuser" is letting me connect to any
     database, not just the db with name same as my user name
    To specify any network sources, use "anyaddr 0.0.0.0", e.g.:
     1.2.3.4 0.0.0.0
    md5 doesn't work from Linux Odbc client, but does from Windows
    Authentication method "ident" uses a SO_PEERCRED request to ascertain
     the user's login name.  Network accesses use the ident daemon.
    Authentication method "ident" requires an AUTH_ARGUMENT.
    Note that "sameruser" in the "DATABASE" field means that a user can
     connect only to the database of his own user name; whereas the
     "sameuser" argument of "ident" authentication just means to do no
     mapping of user name (i.e. Postgres name is == requesting user's name)
    User name (at least for password/md5 methods) is just the account that
     the user attempts to use.  Independent of any OS account.

HTML docs have A LOT of holes in them on the Administration side.
file:///usr/share/doc/postgresql-7.2.1/html/index.html
There's even an SQL reference manual towards the bottom.

Default database which is created automatically is called "template1".

psql 
    Must give -h hostname arg to avoid the UNIX ident check to verify you
     are logged in as the user given on command-line.
    Typically start up like "psql -h hostname -U blaine dbname".
    Notice the subtle change in prompt.  Normal promptlines have equal-sign
     -something (like => or =#).  Continuation prompts have hyphen-something.
    By default, will connect to a DB with name the same as your user name.
    The "template1" database should always be there.
    The welcome banner has damned good tips!
    Particularly important commands
    \q: quit
    \l: list all databases
    \dS: list all system table names  (e.g. the user table name)
    \dt: list all table names
    \du: list all user names

    \set AUTOCOMMIT true    -- For any psql variable
    \unset AUTOCOMMIT       -- For any psql variable

Very useful command.
    ALTER USER x WITH PASSWORD 'y';

To list all passwords:
    SELECT * FROM pg_shadow;

JDBC:  Driver is type 4.
To use TCP/IP, set POSTGRES_OPTIONS to "-i" in /etc/sysconfig/postgresql,
enable appropriate ACLs in /var/lib/pgsql/data/pg_hba.conf, and restart
postgresql server.

DB IMPORTS:  Temporarily relax authentication by adding
    local all all trust
before other lines in pg_hba.conf.  This is so that "connect - USERNAME"
commands in the DB dump file will work.


To tell when mods were last made, check update time of the transaction
logs at /var/lib/pgsql/data/pg_xlog/*.


UPDATE FOR v. 8.0.1.
    Automatically installs a template0 AND template1 instance.

pg_dump
    Useful switches
        -D:  Portable output (runs slower)
        -n <SCHEMANAME>:  Output only these objects (regardless of refs)
        -O:  Omit ownership commands.  When read in, will get default 
             ownerships (current owner or public unless you modify the file).
        -h <HOSTNAME>
        -U <USERNAME>
    Example
        pg_dump -f /tmp/gmsdev01.sql -D -n gmsdev01 -O -h icfsun1 -U gmsdev01 icfgms01


OLD VERSIONS OF PG

    Have no superuser option to alter user / grant; nor the createuser script.
    Must do UPDATE pg_shadow SET usesuper = 't' WHERE usename = 'x';

    Use 'groups' instead of roles.  \g (new have \dr?).

Default character encoding is set on a per-database basis.  You can see the
encodings for existing databases with "psql -l".