equal
deleted
inserted
replaced
81 ALTER USER me WITH SUPERUSER; |
81 ALTER USER me WITH SUPERUSER; |
82 ALTER USER me WITH CREATEDB CREATEROLE LOGIN; |
82 ALTER USER me WITH CREATEDB CREATEROLE LOGIN; |
83 |
83 |
84 https://www.postgresql.org/docs/current/sql-createrole.html |
84 https://www.postgresql.org/docs/current/sql-createrole.html |
85 ``CREATE ROLE``. |
85 ``CREATE ROLE``. |
|
86 |
|
87 Granting selects:: |
|
88 |
|
89 GRANT SELECT ON ALL TABLES IN SCHEMA public TO user2; |
86 |
90 |
87 Change password |
91 Change password |
88 =============== |
92 =============== |
89 :: |
93 :: |
90 |
94 |
219 |
223 |
220 SELECT * FROM pg_constraint; |
224 SELECT * FROM pg_constraint; |
221 SELECT * FROM pg_constraint WHERE conrelid = (SELECT oid FROM pg_class WHERE relname LIKE 'tblName'); |
225 SELECT * FROM pg_constraint WHERE conrelid = (SELECT oid FROM pg_class WHERE relname LIKE 'tblName'); |
222 select * from pg_constraint where conname like '%conName%'; |
226 select * from pg_constraint where conname like '%conName%'; |
223 |
227 |
|
228 Show constraints and columns to given table:: |
|
229 |
|
230 select |
|
231 c.constraint_name, |
|
232 x.table_schema as schema_name, x.table_name, x.column_name, |
|
233 y.table_schema as foreign_schema_name, y.table_name as foreign_table_name, y.column_name as foreign_column_name |
|
234 from information_schema.referential_constraints c |
|
235 join information_schema.key_column_usage x |
|
236 on x.constraint_name = c.constraint_name |
|
237 join information_schema.key_column_usage y |
|
238 on y.ordinal_position = x.position_in_unique_constraint |
|
239 and y.constraint_name = c.unique_constraint_name |
|
240 where y.table_name = 'sa_pnr' |
|
241 order by c.constraint_name, x.ordinal_position |
|
242 |
224 https://www.postgresql.org/docs/current/catalog-pg-constraint.html |
243 https://www.postgresql.org/docs/current/catalog-pg-constraint.html |
225 pg_constraint. |
244 pg_constraint. |
|
245 https://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys |
|
246 List table foreign keys. |
226 |
247 |
227 List functions:: |
248 List functions:: |
228 |
249 |
229 \df |
250 \df |
230 |
251 |
263 |
284 |
264 List of databases sizes:: |
285 List of databases sizes:: |
265 |
286 |
266 \l+ |
287 \l+ |
267 |
288 |
268 List tables sizes:: |
289 List tables sizes (and constraints):: |
269 |
290 |
270 \d+ |
291 \d+ |
271 |
292 |
272 Table total size (with indexes):: |
293 Table total size (with indexes):: |
273 |
294 |
287 SELECT p.relname, t.relname, pg_size_pretty(pg_relation_size(p.oid)), p.relpages |
308 SELECT p.relname, t.relname, pg_size_pretty(pg_relation_size(p.oid)), p.relpages |
288 FROM pg_class p |
309 FROM pg_class p |
289 LEFT JOIN pg_class t ON t.reltoastrelid = p.oid |
310 LEFT JOIN pg_class t ON t.reltoastrelid = p.oid |
290 ORDER BY p.relpages DESC; |
311 ORDER BY p.relpages DESC; |
291 |
312 |
|
313 Largest TOAST:: |
|
314 |
|
315 select tbl.relname, toast.relname, toast.relpages |
|
316 from pg_class tbl, pg_class toast |
|
317 where tbl.reltoastrelid = toast.oid |
|
318 order by toast.relpages desc; |
|
319 |
292 Size of types:: |
320 Size of types:: |
293 |
321 |
294 SELECT pg_column_size(5::smallint); |
322 SELECT pg_column_size(5::smallint); |
295 SELECT pg_column_size(5::int); |
323 SELECT pg_column_size(5::int); |
296 SELECT pg_column_size(5::bigint); |
324 SELECT pg_column_size(5::bigint); |
311 ====== |
339 ====== |
312 |
340 |
313 Stats on vacuum oprtations per table:: |
341 Stats on vacuum oprtations per table:: |
314 |
342 |
315 select * from pg_stat_user_tables; |
343 select * from pg_stat_user_tables; |
|
344 |
|
345 Table statistics |
|
346 ================ |
|
347 |
|
348 Full table statistics is in ``pg_statistics`` table which is accessible only by superuser. |
|
349 |
|
350 Human readable stats are in ``pg_stats`` view. |
316 |
351 |
317 Using psql client |
352 Using psql client |
318 ================= |
353 ================= |
319 |
354 |
320 Using password file ``~/.pgpass``:: |
355 Using password file ``~/.pgpass``:: |
324 hostname:port:*:username:password |
359 hostname:port:*:username:password |
325 hostname:*:*:username:password |
360 hostname:*:*:username:password |
326 |
361 |
327 Connect by:: |
362 Connect by:: |
328 |
363 |
329 $ psql -U $USER -h $HOST $SCHEMA |
364 $ psql -U $USER -h $HOST -p $PORT $SCHEMA |
330 |
365 |
331 How to view execution plan:: |
366 How to view execution plan:: |
332 |
367 |
333 EXPLAIN query; |
368 EXPLAIN query; |
334 EXPLAIN ANALYZE query; |
369 EXPLAIN ANALYZE query; |