Home / Programming / postgresql edit
Try Documentalist, my app that offers fast, offline access to 190+ programmer API docs.

  • notes
    default port: 5432
    
    Config file on Ubuntu:
    /etc/postgresql/9.3/main/postgresql.conf - Ubuntu 14.04
    /etc/postgresql/9.4/main/postgresql.conf - Ubuntu 15.04
    
    Access config file on Ubuntu:
    /etc/postgresql/9.3/main/pg_hba.conf
    
    To enable those ports on ubuntu:
    sudo iptables -A INPUT -p tcp --dport 5432 -j ACCEPT
    
    
    http://rob.conery.io/2015/03/01/document-storage-gymnastics-in-postgres/
    http://big-elephants.com/2012-10/aggregations-with-pg-hstore/
    
    http://russ.garrett.co.uk/talks/postgres-gds/#/
    
    https://blog.lateral.io/2015/05/full-text-search-in-milliseconds-with-postgresql/, https://news.ycombinator.com/item?id=9512360
    
    https://www.digitalocean.com/community/tutorials/how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps--2
    
    psql commands:
    \l : list database
    \c $db : connect to database
    \dt : list tables
    
    http://www.postgresql.org/about/news/1587/ - 9.4.2, 9.3.7 have important fixes
    
    https://gocardless.com/blog/zero-downtime-postgres-migrations-the-hard-parts/, https://news.ycombinator.com/item?id=9611434
    
    http://www.postgresql.org/docs/9.3/static/populate.html
    https://www.digitalocean.com/community/tutorials/how-to-secure-postgresql-on-an-ubuntu-vps
    https://www.digitalocean.com/community/tutorials/how-to-backup-postgresql-databases-on-an-ubuntu-vps
    
    psql [option] [dbname [username]]
    -U $user : default is os user name
    -h $host
    -d $dbname : default is os user name
    -W : force password
    -w : no password
    
    Location of config files:
    /etc/postgresql/9.3/main - in Ubuntu 14.04
    
    http://www.postgresonline.com/journal/archives/20-The-Anatomy-of-PostgreSQL-Part-2-Database-Objects.html
    
    http://blog.andrebarbosa.co/upsert-on-postgres-9-5/
    http://www.craigkerstiens.com/2015/12/27/postgres-9-5-feature-rundown/
    http://www.craigkerstiens.com/2015/12/29/my-postgres-top-10-for-2016/
    http://rachbelaid.com/postgres-full-text-search-is-good-enough/
    
    
    psql -h banana-pepper-468.db.databaselabs.io -U postgres
    create database pagila_demo;
    \q
    psql -h banana-pepper-468.db.databaselabs.io -U postgres -d pagila_demo <pagila-schema.sql
    psql -h banana-pepper-468.db.databaselabs.io -U postgres -d pagila_demo <pagila-insert-data.sql
    psql -h banana-pepper-468.db.databaselabs.io -U postgres -d pagila_demo <pagila-data.sql
    
    psql -h banana-pepper-468.db.databaselabs.io -U postgres
    create role pagila_demo_user login password 'pagila_demo_user_pwd';
    GRANT CONNECT ON DATABASE pagila_demo TO pagila_demo_user;
    
    GRANT USAGE ON SCHEMA public TO pagila_demo_user;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO pagila_demo_user;
    
    GRANT USAGE ON SCHEMA information_schema TO pagila_demo_user;
    GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO pagila_demo_user;
    
    GRANT USAGE ON SCHEMA pg_catalog TO pagila_demo_user;
    GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO pagila_demo_user;
    
    
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON tables TO pagila_demo_user;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, USAGE ON sequences TO pagila_demo_user;
    
    ALTER DEFAULT PRIVILEGES IN SCHEMA information_schema GRANT SELECT ON tables TO pagila_demo_user;
    ALTER DEFAULT PRIVILEGES IN SCHEMA information_schema GRANT SELECT, USAGE ON sequences TO pagila_demo_user;
    
    select table_catalog, table_schema, table_name, table_type from information_schema.tables;
    
    
    SELECT datname, pid, usename, application_name FROM pg_stat_activity;
    SELECT pg_terminate_backend(12786);
    
    http://www.craigkerstiens.com/2013/01/10/more-on-postgres-performance/
    http://www.craigkerstiens.com/2012/10/01/understanding-postgres-performance/
    https://github.com/MediaMath/keryxlib
    http://www.monkeyandcrow.com/blog/hierarchies_with_postgres/, https://news.ycombinator.com/item?id=10875076
    https://github.com/joncrlsn/pgdiff
    https://pgexercises.com/
    http://bpicolo.github.io/docker/testing/2016/06/01/welcome-to-jekyll.html
    http://schinckel.net/2014/05/25/querying-json-in-postgres/
    https://www.postgresql.org/docs/9.4/static/functions-json.html
    http://stackoverflow.com/questions/24944347/postgresql-nested-json-querying
    https://wiki.duraspace.org/display/DSPACE/Idle+In+Transaction+Problem
    https://www.justwatch.com/blog/post/debugging-postgresql-performance-the-hard-way/
    http://www.calhoun.io/connecting-to-a-postgresql-database-with-gos-database-sql-package/ : postgresql and Go tutorial
    http://cs.mcgill.ca/~mxia3/2016/11/18/Statically-typed-PostgreSQL-queries-and-typescript-schemats/ : postgres and typescript

Feedback about page:

Feedback:
Optional: your email if you want me to get back to you:

Share on        

Need fast, offline access to 190+ programmer API docs? Try my app Documentalist for Windows