Postgres

DEV INSTALL

There may be one or more system postgres'es already installed. That's fine, just make sure they don't start.

sudo emacs -nw /etc/postgresql/(tab-tab)
sudo emacs -nw /etc/postgresql/10/main/start.conf # change auto > manual
sudo emacs -nw /etc/postgresql/9.6/main/start.conf # change auto > manual
sudo service postgres(tab-tab to determine if there are more than one) stop

use mh to install postgres and libpqxx to /usr/local

mh-install-postgres

set up a linux user named "postgres"

# On gentoo:
sudo useradd postgres
# On ubuntu:
sudo adduser postgres
# Put the password in private

set up a data folder where postgres will put all its data and its log

# Standardize on [/home/m/development/postgres], but always consider symlinking to a large/redundant location.
# Here, we are choosing to use a larger RAID drive.
# WARNING NTFS will not let you chown a folder (you'd have to mount the whole thing as the target owner).  
# So on viper, I had to use the tiny primary drive directly, no symlink to ~m/bigdata.  Fuck off.
mkdir /spiceflow/softraid/development/postgres
cd ~/development && ln -s /spiceflow/softraid/development/postgres postgres
sudo chown -R postgres:postgres postgres /spiceflow/softraid/development/postgres
sudo chmod g+x /spiceflow/softraid/development # and any other parent that is missing this bit, so postgres user can cd to its folder

# We must init the data folder as a postgres "database cluster"
su postgres -c '/usr/local/bin/initdb -D /home/m/development/postgres/data'

CONFIGURE

Great configuration tool here. Example result:

# DB Version: 10 # OS Type: linux # DB Type: web # Total Memory (RAM): 16 GB # CPUs num: 4 # Connections num: 1000 # Data Storage: hdd max_connections = 1000 shared_buffers = 4GB effective_cache_size = 12GB maintenance_work_mem = 1GB checkpoint_completion_target = 0.7 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 4 effective_io_concurrency = 2 work_mem = 2097kB min_wal_size = 1GB max_wal_size = 2GB max_worker_processes = 4 max_parallel_workers_per_gather = 2 max_parallel_workers = 4

UBUNTU INSTALL

I'm installing ubuntu default apt package to compare (something is locking up, and postgres is definitely on the watch list).

sudo apt install postgres postgres-contrib # on ubuntu 18.04, installs pg10 bleh

START SERVER

Use mh-start-postgres to start postgres (if the server is not already running):

mh-start-postgres

ADD A USER + DB

Use mh-add-postgres-db to add a user (aka role) and matching database. The user only has access to that db (best practice). This will prompt for pw.

mh-add-postgres-db [name]
# Add name+pw to private

Readable UNIX epoch

select to_timestamp((json->>'timestamp')::int) at time zone 'utc', * from stockquotes where (json->>'quote')::float < 0.0;

Yes it's that ridiculous. Yes it's really even worse than that.

NOSQL via JSON

BASICS

Add columns of type 'jsonb' (JSON binary).
Inject cells of any valid JSON format (big data!).
Then using special syntax, you can crack open the json inside a cell to query, create, update, delete:

   CREATE TABLE stocks (
     id integer NOT NULL,
     board_id integer NOT NULL,
     data jsonb
   );
   INSERT INTO stocks VALUES (1, 1, '{"symbol": "GOOG", "tags": ["hot", "trending"], "owned": true}, "quantity": 12');

   # returns count of data that HAS the field ingredients
   SELECT count(*) FROM stocks WHERE data ? 'tags';

   # ->> will return the property as integer or text (the parsed form of the attribute).
   SELECT * FROM stocks WHERE data->>'owned' = 'true';         # if data.owned == true

   # you may need to cooerce the type, sigh...
   SELECT * FROM stocks WHERE (data->>'quantity')::int > 5;

   # -> will return the attribute as a JSON object
   SELECT jsonb_array_elements_text(data->'tags') as tag FROM stocks WHERE id = 1;
   # returns the actual tag values, eg:
       tag
       --------------
       Improvements
       Office
       (2 rows)

PARTIAL UPDATE

INSERT INTO mytable(id, json) VALUES ('1', '{"name":"homey","set":{"some_prop": 99}}'); 
UPDATE SET json = 
    jsonb_set(mytable.json, array['set','some_prop'], '88', true)
where json->>'name' = 'homey';

UPSERTS

Upsert example, from SO:

INSERT INTO live_data(rec_id, control_data) VALUES ('1', '{"set":{"some_prop": 99}}'); 

INSERT INTO live_data(rec_id, control_data) 
VALUES ('1', '{"set":{"some_prop": 88}}')
ON CONFLICT (rec_id) DO 
UPDATE SET control_data = 
    jsonb_set(live_data.control_data, array['set','some_prop'], '88', true)
RETURNING *;

INDEXING

   # NEXT: EXPLORE INDEXING!
   https://blog.codeship.com/unleash-the-power-of-storing-json-in-postgres/