Differences

This shows you the differences between two versions of the page.

--- postgresql_createadatabase [2018/11/22 13:31] – created andonovj
+++ postgresql_createadatabase [2019/10/18 20:04] (current) – external edit 127.0.0.1
@@ Line 4: / Line 4: @@
-===Create user===
+====Create user====
+----
 In PostgreSQL, there are 2 kind of roles:
   - Role which can connect - Called Users
@@ Line 20: / Line 21: @@
 This command will create a role, which can login, thus User :)
-===Create database with owner===
+====Create database with owner====
+----
 Now we can create a database using this user:
 <sxh bash>
@@ Line 31: / Line 33: @@
-===Grant privileges===
+====Grant privileges====
+----
 Just because a user is owner of a database, this doesn't mean the user can select tables on that database, from all we know, this user might not even be able to connect to the database it owner: (It should be specified in pg_hba.conf) file.
@@ Line 45: / Line 48: @@
 P.S. Do not forget to update the pg_hba.conf and reload the profile so the user(ROLE) can connect to it :)
+====Get the size of all databases====
+----
+Easy way to get the size of databases is to execute the following query:
+<sxh bash>
+postgres=#
+SELECT database_name, pg_size_pretty(size)
+from
+      (SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname) AS size
+      FROM pg_database ORDER by size DESC) as ordered;
+       database_name       | pg_size_pretty
+---------------------------+----------------
+ DB1                       | 18 GB
+ DB2                       | 9826 MB
+ DB3                       | 6969 MB
+(3 rows)
+</sxh>