PostgreSQL » History » Revision 3
Revision 2 (Marc Dequènes, 2019-01-01 08:17) → Revision 3/4 (Marc Dequènes, 2021-05-29 11:12)
{{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, right (and probably inherit rights too and without 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.