Commands
======================

postgres -D /usr/local/var/postgres

sudo -u paulocheque createuser dev

sudo -u postgres createuser dev

createdb DATABASENAME

Select a random row
======================

    SELECT column FROM table
    ORDER BY RANDOM()
    LIMIT 1
    
DELETE with join
=====================

    DELETE FROM table as t using anothertable as x where t.id = x.refid and t.etc = etc;

    
    
Counter
======================

    select nextval('table')
    setval('table', value)

Dump/Restore
======================

    pg_dump -h localhost -p 5432 DBNAME > db.sql
    pg_dump -h localhost -p 5432 DBNAME -U username -W password > db.sql
    psql -h localhost DBNAME < db.sql

    pg_dump -Fc -h localhost -p 5432 DBNAME > db.bin.dump
    pg_restore -h localhost db.bin.dump

    pg_restore --no-owner --clean --exit-on-error --disable-triggers --data-only --host localhost --port 5432 --username             "paulocheque" --dbname "DBNAME" --no-password  --verbose db.bin.dump
    
    pg_dump -v -h [hostname] -p 5432 -U [user] -Fc [database] > [flename]
    pg_restore -d [database] [filename]


Monitoring
===============

http://www.postgresql.org/docs/9.3/static/monitoring-stats.html

    -- show active queries
    SELECT * FROM pg_stat_activity where state = 'active' order by query_start;
    SELECT * FROM pg_stat_activity order by query_start;

    -- show indexes information
    SELECT * FROM pg_stat_all_indexes;
    SELECT * FROM pg_stat_user_indexes;

    SELECT count(*) as cnt, usename FROM pg_stat_activity GROUP BY usename ORDER BY cnt DESC limit 5;
    
    -- DB
    SELECT version();
    SHOW server_version;
    SHOW server_version_num;
    SHOW all;
    SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database;
    select * from FROM pg_database;
    show ssl;
    SELECT * FROM pg_statio_user_tables;
    SELECT * FROM pg_stat_user_tables;
    SHOW rds.extensions;

https://github.com/pganalyze/pganalyze-collector

https://github.com/zalando/pg_view

https://github.com/zalando/PGObserver

https://pganalyze.com/docs/install/01_enabling_pg_stat_statements

http://bucardo.org/wiki/Check_postgres

    brew install check_postgres
    check_postgres --host=host --port 5432 --dbname db --dbuser user --dbpass pass --action=connection
    --action=connection
    --action=backends
    --action=dbstats