Project

General

Profile

PostgreSQL » History » Version 3

Marc Dequènes, 2021-05-29 11:12

1 2 Marc Dequènes
{{toc}}
2
3 1 Marc Dequènes
h1. PostgreSQL
4
5
h2. Authentication
6
7
3 methods:
8
* access via UNIX socket for all accounts
9
* access via local role database for local accounts
10
* access via PAM for non-local accounts
11
12
h3. Local Accounts
13
14
These are accounts needed for system services (user services should use secondary accounts defined in the LDAP instead).
15
16
Creation:
17
* create a normal role with login right (and probably inherit right too)
18
* add the role in the special ''local-roles'' role members
19
20
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!
21
22
h3. PAM Accounts
23
24
Creation:
25 3 Marc Dequènes
* create a normal role with login right, inherit rights too and without password
26 1 Marc Dequènes
27
That's all !
28
29
h3. Managing Team Work
30
31
Create:
32
* create a normal role without login right and password
33
* add user roles as member of this role
34
* give this role rights to the parts the team is working on
35
36
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)!
37
38
h2. Storage sizes
39
40
h3. Databases sizes
41
42
<pre>
43
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
44
</pre>
45
46
h3. Tables sizes in current database
47
48
<pre>
49
 SELECT nspname, relname,
50
 pg_size_pretty(tablesize+indexsize+toastsize+toastindexsize) AS totalsize
51
 FROM
52
 (SELECT ns.nspname, cl.relname, pg_relation_size(cl.oid) AS tablesize,
53
 COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint
54
 FROM pg_index WHERE cl.oid=indrelid), 0) AS indexsize,
55
 CASE WHEN reltoastrelid=0 THEN 0
56
 ELSE pg_relation_size(reltoastrelid)
57
 END AS toastsize,
58
 CASE WHEN reltoastrelid=0 THEN 0
59
 ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct
60
 WHERE ct.oid = cl.reltoastrelid))
61
 END AS toastindexsize
62
 FROM pg_class cl, pg_namespace ns
63
 WHERE cl.relnamespace = ns.oid
64
 AND ns.nspname NOT IN ('pg_catalog', 'information_schema')
65
 AND cl.relname IN
66
 (SELECT table_name FROM information_schema.tables
67
 WHERE table_type = 'BASE TABLE')) ss
68
 ORDER BY tablesize+indexsize+toastsize+toastindexsize DESC;
69
</pre>
70
71
h2. PostgreSQL interrupted recovery
72
73
If you got something like this in your logs:
74
<pre>
75
2011-07-15 16:23:12 CEST LOG:  database system was interrupted while in recovery at 2008-03-21 00:02:22 CET
76
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.
77
2011-07-15 16:23:12 CEST LOG:  database system was not properly shut down; automatic recovery in progress
78
2011-07-15 16:23:12 CEST LOG:  consistent recovery state reached at 28/55780068
79
2011-07-15 16:23:12 CEST LOG:  redo starts at 28/55780068
80
2011-07-15 16:23:12 CEST WARNING:  specified item offset is too large
81
2011-07-15 16:23:12 CEST CONTEXT:  xlog redo insert: rel 1663/16387/17911; tid 76/212
82
2011-07-15 16:23:12 CEST PANIC:  btree_insert_redo: failed to add item
83
2011-07-15 16:23:12 CEST CONTEXT:  xlog redo insert: rel 1663/16387/17911; tid 76/212
84
2011-07-15 16:23:12 CEST LOG:  startup process (PID 2981) was terminated by signal 6: Aborted
85
2011-07-15 16:23:12 CEST LOG:  aborting startup due to startup process failure
86
</pre>
87
88
If you don't care about loosing uncommited data:
89
<pre>
90
su - postgres
91
/usr/lib/postgresql/9.0/bin/pg_resetxlog -f 9.0/main/
92
</pre>
93
Running first without -f can give hints about the situation.
94
95
Dumping, reiniting, and reloading data is recommended.