Difference between revisions of "Postgres"
From Wasya Wiki
Line 45: | Line 45: | ||
=== postgres import sql === | === postgres import sql === | ||
psql databasename < data_base_dump | psql databasename < data_base_dump | ||
+ | |||
+ | === pesky buggy stuff === | ||
+ | <pre> | ||
+ | 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'; | ||
+ | </pre> |
Revision as of 21:19, 10 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
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';