PostgreSQL

  ## start
  $ psql

  ## open database
  $ psql --host=${host} --dbname=${database} --username=${user} --password

  ## exit
  ~$ postgres=# \q

Installation

  $ sudo apt-get install postgresql postgresql-contrib

Databases

  ## create
  ~$ postgres=# CREATE DATABASE ${database};

  ## list
  ~$ postgres=# \l

  ## URL
  ## DATABASE_URL=postgresql://${user}:${password}@postgres:5432/drkiq?encoding=utf8&pool=5&timeout=5000

  ## select database
  ~$ postgres=# \c ${database};

  ## backup
  PGPASSWORD="${password}" pg_dump -c -d ${database} -U ${user} -h localhost > dump_`date +%Y-%m-%d`.sql

  ## restore
  $ psql -d ${database} -f dump_`date +%Y-%m-%d`.sql
  $ psql -f db.out postgres

  ## save table to file
  ~$ postgres=# COPY checkins TO '/tmp/checkins_db.csv' DELIMITER ',' CSV HEADER;

Users

  ## create
  ~$ postgres=# CREATE USER ${user} WITH PASSWORD '${password}';

  ## grant privileges
  ~$ postgres=# GRANT ALL PRIVILEGES ON DATABASE ${database} to ${user};

  ## show users
  ~$ postgres=# \du

  ## rename user
  ~$ postgres=# ALTER USER ${user} RENAME TO ${new_user};

  ## change password
  ~$ postgres=# ALTER USER ${user} WITH PASSWORD '${new_pswd}';

  ## test
  $ psql --username=${user} --password

  ## forgot password
    $ sudo nano /etc/postgresql/13/main/pg_hba.conf
     < /etc/postgresql/13/main/pg_hba.conf >
      local all all                   trust
      host  all postgres 127.0.0.1/32 trust
      host  all all           ::1/128 trust
    $ sudo /etc/init.d/postgresql restart
    $ psql -h localhost -U postgres
    ~$ postgres=# ALTER USER postgres with password 'new_password';

  ## user authentication
  $ sudo nano /etc/postgresql/13/main/pg_hba.conf
      local all postgres         md5
      local all all              md5
      host  all all 127.0.0.1/32 md5
      host  all all ::1/128      md5

Setup

  ## change listen addresses
  $ sudo nano /etc/postgresql/13/main/postgresql.conf
      < etc/postgresql/13/main/postgresql.conf >
          listen_addresses = '*'

DOCKER AND PSQL:

  Starting an instance:
      # docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres
      $ docker run --name image_db_1 -e POSTGRES_PASSWORD=pg1234 -d --volumes-from postgres-data postgres
      # volumes-from: mount /var/lib/postgresql/data from postgres-data volume
      # docker run --name local-pg -p 5432:5432 -d postgres
      # docker run --name sw_db_backup -p 5432:5432 -d postgres:13

  Connect to it from an application:
      #$ docker run --name some-app --link some-postgres:postgres -d application-that-uses-postgres
  Connect to it via psql:
      #$ docker run -it --rm --link some-postgres:postgres postgres psql -h postgres -U postgres
      ! working !
      $ docker run -it --link image_db_1:postgres --net the_app --rm postgres:13 sh -c 'exec psql -h postgres -p 5432 -U postgres'

ERROR:

  # missing templates
  $ create database template0 TEMPLATE postgres;
  $ update pg_database set datistemplate=true  where datname='template0';

  Cannot link to image_db_1, as it does not belong to the default network
      $ docker network ls
          NETWORK ID          NAME                       DRIVER              SCOPE
          39da4dfdd2f1        bridge                     bridge              local
          1457d75d42a7        host                       host                local
          b30a51375e67        none                       null                local
          d7ca18692329        the_app                    bridge              local
          0836c4154d76        app_test                   bridge              local
      $ docker run -it --link image_db_1:postgres --net the_app  --rm postgres:13

  Open a shell prompt:
      ! working !
      $ docker ps
      CONTAINER ID    IMAGE                   COMMAND     CREATED         STATUS
      3c1add52fdad4   postgres:13         "docker-entrypoint.sh"  48 seconds ago  Up
      PORTS               NAMES
      0.0.0.0:5432->5432/tcp image_db_1
      $ docker exec -it 3c1add52fdad4 sh
      $ su - postgres
      $ psql
      $ \l
      $ \c prod_db
      $ \dt
  Deleting volumes:
      $ docker run --rm -v /var/lib/postgresql/data postgres:13

  =================================
  Data in db_1 is updated in real time.
  Data in pg_data_1 is updated after committing.
      $ docker ps
      CONTAINER ID    IMAGE                   COMMAND     CREATED         STATUS
      2d17d374f665    sw_db_image:v1      "docker-entrypoint.sh"  48 seconds ago  Up
      PORTS               NAMES
      5432/tcp image_db_1

      # make sure POSTGRES_PORT_5432_TCP_ADDR = postgres and POSTGRES_PORT_5432_TCP_PORT = 5432
      $ docker run -it --link image_db_1:postgres --rm postgres:13 sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres -d prod_db'
      $ docker run -it --link image_db_1:postgres --rm postgres:13 sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres -d prod_db'

      # list databases
      $ \l
      $ \c prod_db
      $ \dt
      $ select * from users;

      $ docker commit -m "message" 2d17d374f665 sw_db_image:v2

  Restart table index with 1:
      $ psql
      # get the sequence name
      $ SELECT pg_get_serial_sequence('users','id');
      pg_get_serial_sequence
      -----------------------
      public.users_id_seq

      $ ALTER SEQUENCE 'users_id_seq' RESTART WITH 1;

BACKUPS:

  # $ docker exec -t image_db_1 pg_dumpall -c -U postgres > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql
  $ docker exec -t image_db_1 pg_dumpall -c -U postgres > ../_backups/dump_`date +%Y-%m-%d`.sql
  # restore
  $ docker start sw_db_backup
  $ cat dump_06-02-2017_15_55_49.sql | docker exec -i sw_db_backup psql -U postgres
  $ docker run -it --link sw_db_backup:postgres --rm postgres:13 sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres -d prod_db'

BACKUPS FROM HEROKU:

  DATABASE_URL=postgres://arpgybtfkjuqur:osiTmzUq8tlscexAz6M_eaIb3N@ec2-54-243-200-159.compute-1.amazonaws.com:5432/dcjgjfffk4vdc0
  # download the sql dump
  $ pg_dump --host=ec2-54-243-200-159.compute-1.amazonaws.com --port=5432 --username=arpgybtfkjuqur --password --dbname=dcjgjfffk4vdc0 > output.sql
  password: osiTmzUq8tlscexAz6M_eaIb3N

RESTORE FROM HEROKU:

  $ cat output.sql | docker exec -i image_db_1 psql -U postgres -d prod_db
  $ docker ps
  CONTAINER ID    IMAGE                   COMMAND     CREATED         STATUS
  9e4f0fc31113    postgres:13         "docker-entrypoint.sh"  48 seconds ago  Up
  PORTS               NAMES
  0.0.0.0:5432->5432/tcp image_db_1
  $ docker exec -it 9e4f0fc31113 sh
  $ su - postgres
  $ psql
  $ \dt

OTHER COMMANDS:

  # adding password
  $ CREATE USER user WITH PASSWORD 'user_password';

  # adding privileges
  $ GRANT ALL PRIVILEGES ON DATABASE db to user;

  # revoking privileges
  $ REVOKE ALL PRIVILEGES ON DATABASE db FROM user;

  # deleting user
  $ DROP USER user;

  # delete row
  $ DELETE FROM table1 WHERE name = 'something';

  # create database
  $ CREATE DATABASE db_name

  # delete database
  # change a value
  $ UPDATE pg_database SET datistemplate='false' WHERE datname='template0';
      UPDATE 1
  $ DROP DATABASE template0;
      DROP DATABASE

  # superuser privileges
      # common errors:
          # - must be owner of extension plpgsql
          # - must be owner of schema public
  $ ALTER ROLE user SUPERUSER;

  # change table owenership
  $ ALTER TABLE table OWNER TO user;

  # change password
  $ \password user

  # sort database
  psql > select * from my_table where user = 'Username1' order by ID desc limit 2.
  
  # update a column for all rows in table
  psql > update "table_name" set col_name = 'val';

Updating PostgresSQL version:

  $ sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main"
  $ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
  $ sudo apt-get update
  $ sudo apt-get install postgresql-13

Changing postgresql port:

  $ sudo nano /etc/postgresql/13/main/postgresql.conf

Changing pg_dump version:

  $ find / -name pg_dump -type f 2>/dev/null
      /usr/lib/postgresql/13/bin/pg_dump

Backup:

# $ PGPASSWORD="db_password" /usr/lib/postgresql/13/bin/pg_dump --host=xxxxx.us-east-1.rds.amazonaws.com --port=5432 --username=user1 --dbname=db1 > /home/user1/Documents/_backups/db1/db_dump_`date +%Y_%m_%d`.sql
ssh xxx@xxxxxxx "PGPASSWORD="db_password" /usr/lib/postgresql/13/bin/pg_dump \
-h xxxxx.us-east-1.rds.amazonaws.com -p 5432 -U user1 \
-F c -b -v -f "/home/user1/Documents/_backups/db1/db_dump_`date +%Y_%m_%d`.sql" db1"

Restore

# psql -d db_data1 -f db_dump_`date +%Y_%m_%d`.sql
# pg_restore -d db1 -U user1 -C /home/user1/Documents/_backups/db1/db_dump_`date +%Y_%m_%d`.sql
PGPASSWORD="db_password" pg_restore --clean --host=localhost --dbname=db1 --username=user1 /home/user1/Documents/_backups/db1/db_dump_`date +%Y_%m_%d`.sql

count:

  psql: select count(*) from table;

query:

  SELECT column_name, count(*) from public.relation_name GROUP BY column_name ORDER BY column_name;
  example:
      SELECT program, COUNT(*) FROM public.all_hotels GROUP BY program ORDER BY program;
  example:
      SELECT * from table_name where column_name = 'some_value';

columns:

  psql: \d+ table_name;

  ## update value at ${column} in ${table}
  $ update ${table} set ${column}=md5('${new_value}') where ${column}='${some_value}';

options:

  # expanded display
  \x on

misc:

  # clear table (and reset index)
  truncate table "table_name" restart identity cascade;

Table of Contents

PostgreSQL

Installation

Databases

Users

Setup