Difference between revisions of "Postgres"
From Wasya Wiki
Line 42: | Line 42: | ||
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC; | FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC; | ||
</pre> | </pre> | ||
+ | |||
+ | === postgres import sql === | ||
+ | psql databasename < data_base_dump |
Revision as of 19:02, 4 September 2018
Contents
Install
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;
install utils
sudo apt-get purge postgresql-client-* # From: https://askubuntu.com/questions/831292/how-do-i-install-postgresql-9-6-on-any-ubuntu-version sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main" wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo apt-get update sudo apt-get install postgresql-9.6 -y
check size of each database
From: https://wiki-bsse.ethz.ch/display/ITDOC/Check+size+of+tables+and+objects+in+PostgreSQL+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;
postgres import sql
psql databasename < data_base_dump