DuckCorp » DuckCorp Infrastructure

{{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

For remote connections *TLS is required* .

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, inherit rights too and without 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.