|
1 .. -*- coding: utf-8; -*- |
|
2 |
|
3 ========== |
|
4 Postgre. |
|
5 ========== |
|
6 .. contents:: |
|
7 :local: |
|
8 |
|
9 Installing on Debian. |
|
10 ===================== |
|
11 |
|
12 Install and create new user and database:: |
|
13 |
|
14 $ sudo apt-get install postgresql postgresql-client |
|
15 $ sudo su - postgres |
|
16 % psql |
|
17 postgres=# CREATE USER "mypguser" WITH PASSWORD 'mypguserpass'; |
|
18 postgres=# CREATE DATABASE "mypgdatabase" OWNER "mypguser"; |
|
19 postgres=# \q |
|
20 |
|
21 Connect as user ``mypguser`` to new database:: |
|
22 |
|
23 $ su - mypguser |
|
24 $ psql mypgdatabase |
|
25 |
|
26 In order to create local host superuser:: |
|
27 |
|
28 $ sudo su - postgres |
|
29 $ createuser --superuser USER |
|
30 $ exit |
|
31 $ sudo -u USER psql |
|
32 |
|
33 .. |
|
34 |
|
35 https://wiki.debian.org/PostgreSql |
|
36 Debian wiki instructions. |
|
37 |
|
38 List databases, schemas and tables. |
|
39 =================================== |
|
40 |
|
41 Default database is ``postgres``. |
|
42 |
|
43 To list databases and database locales:: |
|
44 |
|
45 $ psql -U pgadmin -l |
|
46 |
|
47 or:: |
|
48 |
|
49 => SELECT datname FROM pg_database WHERE datistemplate = false; |
|
50 => \l |
|
51 |
|
52 To switch databases:: |
|
53 |
|
54 => \connect NAME |
|
55 |
|
56 Schemas:: |
|
57 |
|
58 => select schema_name from information_schema.schemata; |
|
59 => select nspname from pg_catalog.pg_namespace; |
|
60 => \dn * |
|
61 |
|
62 To list all tables in the current database:: |
|
63 |
|
64 => SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name; |
|
65 => \dt |
|
66 |
|
67 Set default schema. |
|
68 =================== |
|
69 :: |
|
70 |
|
71 set search_path to NAME; |
|
72 |
|
73 Database, table, index size. |
|
74 ============================ |
|
75 |
|
76 Database size:: |
|
77 |
|
78 SELECT pg_database_size('geekdb'); -- in bytes |
|
79 SELECT pg_size_pretty(pg_database_size('dbname')); |
|
80 |
|
81 List of databases sizes:: |
|
82 |
|
83 \l+ |
|
84 |
|
85 List tables sizes:: |
|
86 |
|
87 \d+ |
|
88 |
|
89 Table total size (with indexes):: |
|
90 |
|
91 SELECT pg_size_pretty(pg_total_relation_size('schemaname.tablename')); |
|
92 |
|
93 Sole table size (without indexes and other):: |
|
94 |
|
95 SELECT pg_size_pretty(pg_relation_size('schemaname.tablename')); |
|
96 |
|
97 Largest table in the PostgreSQL database:: |
|
98 |
|
99 SELECT relname, relpages FROM pg_class ORDER BY relpages DESC; |
|
100 |
|
101 Using psql client. |
|
102 ================== |
|
103 |
|
104 Using password file ``~/.pgpass``:: |
|
105 |
|
106 # comment |
|
107 hostname:port:database:username:password |
|
108 hostname:port:*:username:password |
|
109 hostname:*:*:username:password |
|
110 |
|
111 Connect by:: |
|
112 |
|
113 $ psql -U $USER -h $HOST $SCHEMA |
|
114 |
|
115 How to view execution plan:: |
|
116 |
|
117 EXPLAIN query; |
|
118 EXPLAIN ANALYZE query; |
|
119 |
|
120 How to redirect the output of query to a file:: |
|
121 |
|
122 \o output_file |
|
123 SELECT * FROM pg_class; |
|
124 |