postgresql commands

Some useful postgresql commands

#  start postgresql
/usr/lib/postgresql-9.0/bin/postgres -D /var/lib/postgresql/9.1/boom/ -c config_file=/etc/postgresql-9.0/postgresql.conf

#
# #psql dump:
pg_dump #host localhost #port 5555 #username "foo" #format tar #blobs #verbose #file "/home/tokhi/Documents/Castaclip/castaclip.master.130612.tmp" "verticals"
# # restore:
pg_restore #host localhost #port 5432 #username "foo" #dbname "verticals" #role "foo" #password #clean #verbose "/home/tokhi/verticals.db.backup"

 # login as a foo user:
 psql -Ufoo -h localhost
sudo -u postgres psql postgres
psql -Ucastdba -h localhost -d postgres

psql -Ufoo -h localhost -d postgres
 # create a user role:
CREATE ROLE verticals WITH LOGIN PASSWORD 'secret' CREATEDB;

 # create a user
CREATE USER foo WITH PASSWORD 'password'
# change user password
ALTER USER foo WITH PASSWORD 'foobar';
 # grant super user previlage:
ALTER USER foo WITH SUPERUSER;
# revoke
ALTER USER foo WITH NOSUPERUSER;
# connect access
GRANT CONNECT ON DATABASE postgres to foo;
# select access on schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO user;

 # creat database with a specific user:
 CREATE DATABASE verticals_development WITH ENCODING = 'UTF-8'
LC_CTYPE = 'en_US.utf8' LC_COLLATE = 'en_US.utf8' TEMPLATE template0 OWNER verticals;



 # JOINS #####

SELECT *, (
  SELECT array_agg(cac.tags.name) AS keywords
  FROM       cac.items_tags
  INNER JOIN cac.tags ON cac.tags.id = cac.items_tags.tag_id
  WHERE cac.items_tags.item_type = 'clip' 
    AND cac.items_tags.item_id   = cac.clips.id
) AS keywords
FROM       verticals_master.sites
INNER JOIN verticals_master.units     ON verticals_master.sites.unit_id = verticals_master.units.id 
INNER JOIN verticals_master.verticals ON verticals_master.verticals.id = verticals_master.units.vertical_id
INNER JOIN cac.clips                  ON cac.clips.id = verticals_master.sites.clip_id
WHERE      verticals_master.units.vertical_id = 96
;

List postgresql tables by size

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a order by total_bytes desc;

tokhi/postgres.md

Some useful postgresql commands