Project

General

Profile

PostgreSQL » History » Revision 2

Revision 1 (Marc Dequènes, 2018-05-02 18:03) → Revision 2/4 (Marc Dequènes, 2019-01-01 08:17)

{{toc}} 

 h1. PostgreSQL 

 h2. 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 

 h3. 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! 

 h3. PAM Accounts 

 Creation: 
 * create a normal role with login right (and probably inherit right too) with no password 

 That's all ! 

 h3. 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)! 

 h2. Storage sizes 

 h3. Databases sizes 

 <pre> 
 SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database; 
 </pre> 

 h3. Tables sizes in current database 

 <pre> 
  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; 
 </pre> 

 h2. PostgreSQL interrupted recovery 

 If you got something like this in your logs: 
 <pre> 
 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 
 </pre> 

 If you don't care about loosing uncommited data: 
 <pre> 
 su - postgres 
 /usr/lib/postgresql/9.0/bin/pg_resetxlog -f 9.0/main/ 
 </pre> 
 Running first without -f can give hints about the situation. 

 Dumping, reiniting, and reloading data is recommended.