Postgres
From Wasya Wiki
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
pesky buggy stuff
sudo su postgres psql update pg_database set datistemplate=false where datname='template1'; drop database Template1; create database template1 with owner=postgres encoding='UTF-8' lc_collate='en_US.utf8' lc_ctype='en_US.utf8' template template0; update pg_database set datistemplate=true where datname='template1';
postgres=# create database template0 TEMPLATE postgres; ERROR: database "template0" already exists postgres=# create database template1 TEMPLATE postgres; CREATE DATABASE postgres=#
another try:
UPDATE pg_database SET datallowconn = TRUE where datname = 'template0'; \c template0 UPDATE pg_database SET datistemplate = FALSE where datname = 'template1'; drop database template1; create database template1 with template = template0 encoding = 'UNICODE' LC_CTYPE = 'en_US.UTF-8' LC_COLLATE = 'C'; UPDATE pg_database SET datistemplate = TRUE where datname = 'template1'; \c template1 UPDATE pg_database SET datallowconn = FALSE where datname = 'template0';