- [SQL](sql.md)
- <http://www.postgresguide.com/>
- <http://use-the-index-luke.com/de>
- <http://www.postgresql.org/docs/9.4/interactive/index.html>
- <https://www.pgcasts.com/episodes/1/generating-json-from-sql/>
- <https://pgexercises.com/>
- <http://rachbelaid.com/postgres-full-text-search-is-good-enough/>
- [Blogpost about PostgREST](https://blog.frankel.ch/poor-man-api/)
- <https://wiki.postgresql.org/wiki/First_steps>
- Create role:
```bash
su postgres
createuser --interactive
```
Connect to Database:
```bash
su - postgres
psql
CREATE USER xxx PASSWORD 'yyy';
\connect dbname;
SELECT * FROM tablename;
```
- ## Login Stuff
`psql -U youruser -h localhost -d yourdb`
Help: \?
List all Databases: \l
List Columns \dt
Quit pqsql \quit
Create a database
CREATE DATABASE testdb;
http://www.postgresql.org/docs/current/static/sql-insert.html
http://www.postgresql.org/docs/current/static/sql-update.html
Show all active users
select pid as process_id,
usename as username,
datname as database_name,
client_addr as client_address,
application_name,
backend_start,
state,
state_change
from pg_stat_activity
- <http://initd.org/psycopg/docs/usage.html>
- <https://wiki.postgresql.org/wiki/Psycopg2_Tutorial>
CREATE ROLE testdb_rw LOGIN;
GRANT ALL ON DATABASE testdb TO testdb_rw ;
ALTER role testdb_rw PASSWORD 'testdb_rw';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO testdb_rw ;
GRANT ALL ON ALL TABLES IN SCHEMA public TO testdb_rw ;
# Single DB
pg_dump dbname > dumpfile
pg_dump -F t tecmintdb > db-backup.tar
# All DBs
pg_dumpall > dumpfile
# Restoring
psql dbname < dumpfile
pg_restore -d dbname db-backup.tar