PostgreSQL¶

Authentication¶

3 methods:

• access via UNIX socket for all accounts
• access via local role database for local accounts
• access via PAM for non-local accounts

Local Accounts¶

These are accounts needed for system services (user services should use secondary accounts defined in the LDAP instead).

Creation:

• create a normal role with login right (and probably inherit right too)
• add the role in the special ''local-roles'' role members

Beware all roles directly or indirectly members of the local-roles role (indirect members == members of members of members...) will have to authenticate via their local hash and not via PAM!

PAM Accounts¶

Creation:

• create a normal role with login right (and probably inherit right too) with no password

That's all !

Managing Team Work¶

Create:

• create a normal role without login right and password
• add user roles as member of this role
• give this role rights to the parts the team is working on

Beware not to use this role for login, or you'll probably end-up adding it to the local-roles and all PAM users not able to login anymore (see remark in the Local Accounts chapter)!

Storage sizes¶

Databases sizes¶

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;

Tables sizes in current database¶

 SELECT nspname, relname,
 pg_size_pretty(tablesize+indexsize+toastsize+toastindexsize) AS totalsize
 FROM
 (SELECT ns.nspname, cl.relname, pg_relation_size(cl.oid) AS tablesize,
 COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint
 FROM pg_index WHERE cl.oid=indrelid), 0) AS indexsize,
 CASE WHEN reltoastrelid=0 THEN 0
 ELSE pg_relation_size(reltoastrelid)
 END AS toastsize,
 CASE WHEN reltoastrelid=0 THEN 0
 ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct
 WHERE ct.oid = cl.reltoastrelid))
 END AS toastindexsize
 FROM pg_class cl, pg_namespace ns
 WHERE cl.relnamespace = ns.oid
 AND ns.nspname NOT IN ('pg_catalog', 'information_schema')
 AND cl.relname IN
 (SELECT table_name FROM information_schema.tables
 WHERE table_type = 'BASE TABLE')) ss
 ORDER BY tablesize+indexsize+toastsize+toastindexsize DESC;

PostgreSQL interrupted recovery¶

If you got something like this in your logs:

2011-07-15 16:23:12 CEST LOG:  database system was interrupted while in recovery at 2008-03-21 00:02:22 CET
2011-07-15 16:23:12 CEST HINT:  This probably means that some data is corrupted and you will have to use the last backup for recovery.
2011-07-15 16:23:12 CEST LOG:  database system was not properly shut down; automatic recovery in progress
2011-07-15 16:23:12 CEST LOG:  consistent recovery state reached at 28/55780068
2011-07-15 16:23:12 CEST LOG:  redo starts at 28/55780068
2011-07-15 16:23:12 CEST WARNING:  specified item offset is too large
2011-07-15 16:23:12 CEST CONTEXT:  xlog redo insert: rel 1663/16387/17911; tid 76/212
2011-07-15 16:23:12 CEST PANIC:  btree_insert_redo: failed to add item
2011-07-15 16:23:12 CEST CONTEXT:  xlog redo insert: rel 1663/16387/17911; tid 76/212
2011-07-15 16:23:12 CEST LOG:  startup process (PID 2981) was terminated by signal 6: Aborted
2011-07-15 16:23:12 CEST LOG:  aborting startup due to startup process failure

If you don't care about loosing uncommited data:

su - postgres
/usr/lib/postgresql/9.0/bin/pg_resetxlog -f 9.0/main/

Running first without -f can give hints about the situation.

Dumping, reiniting, and reloading data is recommended.