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

Links
Libraries for Go
GUI browsers
Tools
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:

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