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

You need to create two config files, postgresql.conf and pg_hba.conf.

There is a great configuration tool here to generate postgresql.conf. 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

You must provide the pg_hba.conf file to specify access control. Copy it from other installs. Make sure to add any host-to-host access you need.

Here is the location to put the config files when installed from source with [mh-install-postgres]:

/home/m/development/postgres/data/[postgresql.conf,pg_hba.conf]

Start server

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

mh-start-postgres

Add a user + db

First, change the postgres user password:

sudo su postgres
psql
\password postgres
(Enter new password...)
\q

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/

Backup

To dump a database called mydb into a SQL-script file:

$ pg_dump mydb > db.sql

To reload such a script into a (freshly created) database named newdb:

$ psql -d newdb -f db.sql

See here for more.

Ubuntu default install

I tried installing ubuntu default apt package to compare (something is locking up, and postgres is definitely on the watch list). It was too much, never finished. Notes:

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

The config files go here:

/var/lib/postgresql/10/main/[postgresql.conf,pg_hba.conf]

Kill any running postgres. Start with:

 🌐 m@melange  [~/development/postgres] sudo -u postgres /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l pg10_ubuntu_logfile start