PostgreSQL

Links

- [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/)

First Steps

- <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`

• Important Commands

  • 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

Enable External Login

Python

- <http://initd.org/psycopg/docs/usage.html>
- <https://wiki.postgresql.org/wiki/Psycopg2_Tutorial>

• Users, Roles and Grants

  • Documentation
  • Grants

    • 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 ;

• Backup and Restore

  • https://www.postgresql.org/docs/current/backup-dump.html

  • # 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