Try Documentalist,
my app that offers fast, offline access to 190+ programmer API docs.
Links
Libraries for Go
- https://github.com/mc2soft/pq-types : decoders for more PostgreSQL types
- https://github.com/go-reform/reform : ORM for go, supports postgres
- https://github.com/galeone/igor : GORM-like ORM for postgres
- https://github.com/jmoiron/sqlx, http://jmoiron.github.io/sqlx/ : sqlx provides a bit more functionality than sql package
- https://github.com/mgutz/dat : based on sqlx, has postgres-specific additions, query builder
- https://github.com/elgris/sqrl : query builder
- https://github.com/achiku/dgw : generates Golang struct from PostgreSQL metadata
- https://github.com/knq/xo : generate Golang structs and helper functions
- https://github.com/oelmekki/pgrebase : mange postgres codebase for functions, triggers and views
- https://github.com/vattle/sqlboiler : generates Go code from existing postgres database by querying the schema
- https://github.com/Tebro/gopsql
- https://github.com/russross/meddler Libraries for JavaScript:
- https://github.com/holdfenytolvaj/pogi/ : high-level wrapper over pg.js, written in TypeScript
- https://github.com/sweetiq/schemats : generates TypeScript definitions from schema
- https://github.com/russross/meddler : sql <-> struct helpers
- https://github.com/go-pg/pg : ORM focusing on Postgres
GUI browsers
- http://dbglass.web-pal.com/ : an ok db browser, electron
- http://www.sqltabs.com/ : electron app, react + node, a janky db explorer but a good start
Tools
- https://github.com/ankane/pghero, https://pghero.herokuapp.com/
- https://www.depesz.com/projects/
- http://www.yohz.com/dbdoc_details.htm : generates docs (PDF, HTML etc.) describing the schema of the database, easy to replicate
- http://www.yohz.com/siv_details.htm : SQL Image Viewer, easy to replicate
- https://github.com/lukasmartinelli/pgfutter : import csv and json into Postgres, in Go
- https://github.com/oelmekki/pgrebase : in Go, management of stored procedures
- https://github.com/SweetIQ/schemats : generates TypeScript definition for db schema
- https://github.com/nuveo/prest : REST api access to postgres db e.g.
/databases
returns list of databases, written in Go - https://github.com/ozum/pg-structure
- https://joelonsql.com/2016/12/10/pg_catalog-visualized/
- https://github.com/facetoe/pgtransform
- https://github.com/djrobstep/migra : in python, generates sql to change schema form db A to db B
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