Just some notes about PostgreSQL running on Gentoo.

  • Initial config file: /etc/conf.d/postgresql-9.2
    After having set the options in here run "emerge --config dev-db/postgresql-server:9.2" to generate the rest of the configuration.
  • The rest of the configuration is generated in "/etc/postgresql-9.2/".
    Initial installation:
    • Start PostgreSQL with "/etc/init.d/postgresql-9.2 start".
    • Login with "psql -U postgres"
    • Change the password with the command "\password".
    • Exit with the command "\q".
    • Edit the file "/etc/postgresql-9.2/pg_hba.conf" and set all the lines mentioning "trust" to "md5".
    • Restart PostgreSQL with "/etc/init.d/postgresql-9.2" restart
  • Connect to PostgreSQL:
    "psql -U [username] -d [databasename]"
  • Get general help:
    "\?"
  • List SQL help:
    "\h"
  • Show help for a specific SQL command:
    "\h [sql command]
  • Quit psql:
    "\q"
  • List all databases:
    "\l"
  • Create a database:
    "createdb -U [adminuser] [databasename]"
  • Delete a databse:
    "dropdb -U [adminuser] [databasename]"
  • Connect to a database:
    "\c [databasename]"
  • List all schemas:
    "\dn"
  • Create a new schema:
    "create schema [schemaname];"
    "create schema [schemaname] authorization [futureschemaowner];"
  • Change the schema owner:
    "alter schema [schemaname] owner to [newowner];"
  • Show search path:
    "show search_path;"
  • Set the search path:
    "set search_path to [mydb],public;"
  • Create a user:
    "createuser -U [existing_username]--no-createdb --pwprompt --no-createrole --no-superuser [new_username]"
  • Data types
  • List all tables, views and sequences:
    "\d"
  • Cfsda

To list all indexes and their columns, sorted by table name, index name and column order in the index:
=====================
SELECT idx.indrelid::regclass as table,
        i.relname as index,
       i.relowner as index_owner,
       am.amname as index_type,
       idx.indkey as index_keys,
       ARRAY(
       SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
       FROM generate_subscripts(idx.indkey, 1) as k
       ORDER BY k
       ) as index_columns,
       idx.indexprs IS NOT NULL as indexprs,
       idx.indpred IS NOT NULL as indpred
FROM   pg_index as idx
JOIN   pg_class as i
ON     i.oid = idx.indexrelid
JOIN   pg_am as am
ON     i.relam = am.oid
where i.relname like '%CHANGEME_TABLENAME%'
order by 1,2,5
;
=====================