equal
deleted
inserted
replaced
125 GRANT SELECT ON TABLE myschema.myt1, myschema.myt2 TO ro; |
125 GRANT SELECT ON TABLE myschema.myt1, myschema.myt2 TO ro; |
126 |
126 |
127 Granting default privileges for new objects:: |
127 Granting default privileges for new objects:: |
128 |
128 |
129 ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO rw_user; |
129 ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO rw_user; |
130 ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO rw_user; |
130 ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT ALL ON SEQUENCES TO rw_user; |
|
131 ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT EXECUTE ON FUNCTIONS TO rw_user; |
131 |
132 |
132 Revoke privileges and drop role |
133 Revoke privileges and drop role |
133 =============================== |
134 =============================== |
134 |
135 |
135 List roles:: |
136 List roles:: |
379 |
380 |
380 List of databases sizes:: |
381 List of databases sizes:: |
381 |
382 |
382 \l+ |
383 \l+ |
383 |
384 |
|
385 SELECT pg_database_size('mydb'); |
|
386 |
384 List tables sizes (and constraints):: |
387 List tables sizes (and constraints):: |
385 |
388 |
386 \d+ |
389 \d+ |
387 |
390 |
388 Table total size (with indexes):: |
391 Table total size (with indexes):: |
389 |
392 |
390 SELECT pg_size_pretty(pg_total_relation_size('schemaname.tablename')); |
393 SELECT pg_size_pretty(pg_total_relation_size('schemaname.tablename')); |
391 |
394 |
|
395 Table size with TOAST (without indexes):: |
|
396 |
|
397 SELECT pg_table_size('schemaname.tablename'); |
|
398 |
392 Sole table size (without indexes and other):: |
399 Sole table size (without indexes and other):: |
393 |
400 |
394 SELECT pg_size_pretty(pg_relation_size('schemaname.tablename')); |
401 SELECT pg_size_pretty(pg_relation_size('schemaname.tablename')); |
|
402 |
|
403 SELECT pg_relation_size(t.schemaname ||'.'|| t.tablename), t.* FROM pg_tables t; |
395 |
404 |
396 Largest table in the PostgreSQL database:: |
405 Largest table in the PostgreSQL database:: |
397 |
406 |
398 SELECT relname, relpages FROM pg_class ORDER BY relpages DESC; |
407 SELECT relname, relpages FROM pg_class ORDER BY relpages DESC; |
399 SELECT relname, pg_size_pretty(pg_relation_size(p.oid)) FROM pg_class ORDER BY relpages DESC; |
408 SELECT relname, pg_size_pretty(pg_relation_size(p.oid)) FROM pg_class ORDER BY relpages DESC; |
416 |
425 |
417 SELECT pg_column_size(5::smallint); |
426 SELECT pg_column_size(5::smallint); |
418 SELECT pg_column_size(5::int); |
427 SELECT pg_column_size(5::int); |
419 SELECT pg_column_size(5::bigint); |
428 SELECT pg_column_size(5::bigint); |
420 |
429 |
|
430 Largest index:: |
|
431 |
|
432 select pg_relation_size(oid), * from pg_class |
|
433 where relkind = 'i' |
|
434 order by pg_relation_size(oid) desc; |
|
435 |
|
436 https://www.postgresql.org/docs/9.6/functions-admin.html |
|
437 Database Object Management Functions. |
421 https://wiki.postgresql.org/wiki/Disk_Usage |
438 https://wiki.postgresql.org/wiki/Disk_Usage |
422 Finding the size of various object in your database. |
439 Finding the size of various object in your database. |
423 |
440 |
424 MVCC and transaction id |
441 MVCC and transaction id |
425 ======================= |
442 ======================= |