- Table of contents
- access via UNIX socket for all accounts
- access via local role database for local accounts
- access via PAM for non-local accounts
For remote connections TLS is required .
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!
- create a normal role with login right, inherit rights too and without 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)!
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.