Difference between revisions of "Postgres"

From Wasya Wiki
Jump to: navigation, search
(Created page with "<pre> brew install postgresql brew services start postgresql psql postgres \du \q CREATE DATABASE yourdbname; CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass'; GRANT...")
 
Line 11: Line 11:
 
CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
 
CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
 
GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;
 
GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;
 +
</pre>
 +
 +
From: https://wiki-bsse.ethz.ch/display/ITDOC/Check+size+of+tables+and+objects+in+PostgreSQL+database
 +
 +
check size of each database:
 +
<pre>
 +
SELECT
 +
    pg_database.datname,
 +
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
 +
    FROM pg_database;
 +
</pre>
 +
 +
check size of each table:
 +
<pre>
 +
SELECT
 +
  relname as "Table",
 +
  pg_size_pretty(pg_total_relation_size(relid)) As "Size",
 +
  pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
 +
  FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
 
</pre>
 
</pre>

Revision as of 19:18, 28 August 2018

brew install postgresql
brew services start postgresql

psql postgres

\du
\q

CREATE DATABASE yourdbname;
CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;

From: https://wiki-bsse.ethz.ch/display/ITDOC/Check+size+of+tables+and+objects+in+PostgreSQL+database

check size of each database:

SELECT
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
    FROM pg_database;

check size of each table:

SELECT
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;