Postgres

Usage

Access data via dbeaver-ce

Use dbeaver-ce to access any databases from anywhere.

Make a copy of a table

CREATE TABLE new_table AS SELECT * FROM existing_table (WHERE ...);

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.

Table sizes

More ridiculous postgres overengineered obfuscated NONSENSE:

SELECT nspname || '.' || relname AS "relation",
   pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
 FROM pg_class C
 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 WHERE nspname NOT IN ('pg_catalog', 'information_schema')
   AND C.relkind <> 'i'
   AND nspname !~ '^pg_toast'
 ORDER BY pg_total_relation_size(C.oid) DESC;

Optionally add LIMIT 5 or whatever.

Indexing

My discoveries:

• use single arrow not double on root of any ::bigint or other :: conversion! to avoid double-convert
• use :: on any where/order/join field!
• Always carefully precisely match index fields and query fields. Single-arrow that shit! Me being dumb in public

Things are working pretty well.

• NEXT: EXPLORE INDEXING further!

https://blog.codeship.com/unleash-the-power-of-storing-json-in-postgres/

JSONB Usage

Read this carefully!

• 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,
     data jsonb
   );
   INSERT INTO stocks VALUES (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)

Field(s) update

So easy with v12 syntax:

UPDATE mytable SET json = json || '{"order_id": ""}';
update prefint set json = json || '{"value":1}' where json->>'name' = 'LoggingLevel';

Field(s) delete

UPDATE mytable SET json = json - 'custom_order_id';

Upserts

Upsert example, from SO:

INSERT INTO accounts(id, json) VALUES ('1', '{##somejson##}'); 

INSERT INTO accounts(id, json) 
VALUES ('1', '{##somejson##}')
ON CONFLICT (id) DO 
UPDATE SET json = '{##somejson##}'
RETURNING *;

Fix null values

update autotradeparametersets set json = json || '{"analysis_percent_change": 0.0}' where (json->'analysis_percent_change')::text = 'null';

Install

You will need to manage your versions. Upgrading is painful, be informed before you start.

My install script

• You can either install the system postgres server:

mh-install-postgres

Or install the client so you can develop code that connects to a remote server:

mh-install-postgres-client

• The config files are here (created and stowed by the script). You should read and update them to adjust the performance settings to match the hardware.

These gets symlinked from config/common/etc:

/etc/postgresql/17/main/[postgresql.conf,pg_hba.conf].template

It is likely there is a config/ubuntu/machine/etc with actual config files, bc symlinks break postgres. Prolly had to do a shitton of bullshit to put them in place. Some day, improve stow for fuckign shit apps that won't allow symlinks.

/etc/postgresql/17/main/[postgresql.conf,pg_hba.conf]

Copied from:

/home/m/development/config/ubuntu/<hostname>/etc/postgresql/17/main/[postgresql.conf,pg_hba.conf]

Always change the postgres user's default password (you might get prompted for this during install):

sudo su postgres
psql
\password postgres
(Enter new password... add it to private)
\q

Start server

Server should be a standard service that starts on boot.

sudo service postgresql [status|etc..]

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

mh-start-postgres

Once a new server is started, verify you can connect to it via our LAN PGAdmin.

ABT postgres tools

• Set up databases - Note that [at setup] will take care of this for you...

mh-add-postgres-db at_live
mh-add-postgres-db at_test

• To open a shell:

at dpg
at ppg

Config

Configure remote access

See the actual config files for latest, but here's general notes.

postgresql.conf:

# MDM We must also LISTEN if we want to allow any remote connections.
# Also see pg_hba.conf for remote permission details.
listen_addresses = '*'

pg_hba.conf:

# MDM my lan connection
host all all abtdev1 scram-sha-256
host all all cast scram-sha-256

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

Backup

Look at [at import] and [at export], they do the work.

Upgrade

Upgrading DEMANDS that you export your data first, upgrade, then import it. You can skip that step by installing multiple versions of postgresql but that will be an even bigger nightmare. Besides, you might as well be backing up on occasion anyway.

• Create a dump of the database. The easiest way is just to use dbeaver-ce.
• Upgrade using the lastest apt repo and managing the versions in my-haus (see installation).

I have bits and pieces scattered all over due to shitty postgresql ugliness...

• setup_stow.sh puts template config files in place but it has to use the version number
• config/common/etc/postgres has template config files, and config/ubuntu/#machine#/etc/postgres has working config files

See the first troubleshooting tip when it inevitably breaks and nothing works. FUCK YOU POSTGRESQL DEVS.

Issues with 17

I backed up db with dbeaver-ce, but still had a lot of issues upgrading. I tried to update tooling where needed.

Two big issues with 17 upgrade were:

• i needed to call pg_createcluster myself (WHYYYY)
• i debugged a LOT of config issues by running postgres manually with:

       sudo -u postgres /usr/lib/postgresql/17/bin/postgres -d 3 -D /var/lib/postgresql/17/main -c config_file=/etc/postgresql/17/main/postgresql.conf
     that was the ONLY WAY I COULD GET ERRORS out of the fucking thing
     log: nope, journald: nope... FUCK YOU
     but after I fixed all the conf issues, it got MUCH WORSE
     the manual run worked, but the FUCKING UBUNTU SERVICE did not, WHY?
     finally i realized that:
       the conf files could NOT BE BEHIND A SYMLINK for some stupid hacky reason related to the service
       see here:
         https://askubuntu.com/questions/830346/postgresql-server-doesnt-start
     so i fixed setup_stow.sh to undo the symlinks after placing them
     it is UGLY as fuck and uses hardcoded version (17 as of today)
     I HATE POSTGRESQL

2025/06/25 AND IT BROKE AGAIN. After updating /etc/hosts or running setup_stow.sh or updating ubuntu or restarting postgresql or WHO THE FUCK knows... here's my raw notes dump.

- the next postgresql nightmare
 can't connect to positronic from abtdev1 or bitpost
   after runnign stow or restarting positronic or changing /etc/hosts
 why not!!
 * play with pg_hba.conf
   i ws able to get abtdev1 working by adding the IP (WHY did i have to?  TBD...)
 * why won't bitpost work?
   BEST WAY TO DIAGNOSE POSTRESQL ISSUES:
   ssh positronic
   mh-stop-postgres
   # RUN FROM COMMAND LINE IN DEBUG!
   # you get a FULL LOG, YAY
   sudo -u postgres /usr/lib/postgresql/17/bin/postgres -d 3 -D /var/lib/postgresql/17/main -c config_file=/etc/postgresql/17/main/postgresql.conf

     2025-06-25 09:19:18.753 EDT [8551] at_live@at_live LOG:  connection authenticated: identity="at_live" method=md5 (/etc/postgresql/17/main/pg_hba.conf:157)
     2025-06-25 09:19:18.753 EDT [8551] at_live@at_live LOG:  connection authorized: user=at_live database=at_live SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256)

     # SO THE AUTH IS WORKING

     # BUT THERE ARE PERMISSIONS ERRORS FOR THE USER!
     2025-06-25 09:19:18.757 EDT [8551] at_live@at_live STATEMENT:  CREATE INDEX IF NOT EXISTS prefint_json ON prefint USING gin ((json));
     2025-06-25 09:19:18.757 EDT [8551] at_live@at_live ERROR:  permission denied for schema public
     2025-06-25 09:19:18.757 EDT [8551] at_live@at_live STATEMENT:  CREATE INDEX IF NOT EXISTS prefint_json ON prefint USING gin ((json));

     # WHY WAS THIS WORKIGN BEFORE?  not sure, i guess i did not acutally have v17 running??

 * log in as root/admin and increase at_live permissions!
   aha!
   * testing with dbeaver-ce from cast
     looks like lan names should NOT have any suffix, now
     working!
   x testing abt from abtdev1
     * connects using only LAN name
     BUT...
     can't load int prefs, due to error in creating index
   x testing abt from bitpost
     * need to use hostname 'bitpost' not bitpost.com or anything else
     can't create index, due to permissions error
       Postgres addIndexesAsNeeded exception: ERROR:  permission denied for schema public
   * save pg_hba.conf using new stow method  
     1) manually copy in-use configs over templates
     2) chown to m:m if needed
     3) mh s Postgresql config saved
   * fix at_test_abtdev1
     sudo -u postgres psql -c "grant CREATE on database at_test_abtdev1 to at_test_abtdev1"
     bah i just needed to fix PrefInt which had "null" for quotes db (which means at db_refresh script failed)

     all is well again on abtdev1
   - fix bitpost permission
     - since postgresql v15, you must specifically grant permission to SCHEMA
       eitehr of these should do it:
         sudo -u postgres psql -c "grant all privileges on SCHEMA public to at_live"
         sudo -u postgres psql -c "grant USAGE, CREATE on SCHEMA public to at_live"
       FML, neither one fixed it
       * check
         SELECT grantee, privilege_type FROM information_schema.role_schema_grants WHERE schema_name = 'public' AND grantee = 'at_live'
         nope
       how is the grant targeting the right database, anyway?
       don't i need to grant CREATE to user on db?
       try it
         sudo -u postgres psql -c "grant CREATE on database at_live to at_live"
       nope, still getting:
         14:51:38 ERROR Postgres addIndexesAsNeeded exception: ERROR:  permission denied for schema public
       AHA HERE IS HOW YOU CHANGE DB TO THE RIGHT ONE!  FUCK you postgresql!!
         sudo -u postgres psql at_live
           \c at_live postgres
           grant CREATE on database at_live to at_live;

         do not forget the ending semicolon
         POSTGRESQL SUCKS SO SO SO SO SO SO MUCH

         taht did NOT WORK

         FUCK OFF

         THIS DID WORK, FINALLY:

         sudo -u postgres psql at_live
           \c at_live postgres
           grant all privileges on SCHEMA public to at_live;

 I MUST POST ONLINE MORE ABOUT...
   EVERYONE SHOULD BOYCOTT POSTGRESQL UNTIL IT IS FORKED AND REPAIRED BY PEOPLE THAT CARE ABOUT ZEROCONFIG.
   IT IS NOT OKAY THAT POSTGRESQL IS BROKEN OUT OF THE BOX.
   YOU ARE NOT SMART BECAUSE YOU GOT IT WORKING, YOU ARE STUPID FOR TOLERATING ITS POOR CONFIGURABILITY.

Troubleshooting

If the god damned thing won't run

It won't give you ANY fucking feedback as it's buried deep in the mines of Mordor.

USE THIS to see what is going wrong!

sudo -u postgres /usr/lib/postgresql/17/bin/postgres -d 3 -c config_file=/etc/postgresql/17/main/postgresql.conf

Can't connect

Check the log:

cat /var/log/postgresql/postgresql-17-main.log

Finding bad huge data

• I had bad data (huge string written to a json value) and found it by looking at the json length not the actual json (which was huge).

select id, length(json::text) from cycles;

Then I could clean up the rogue field with:

update cycles set json = json || '{"order_id": ""}';

Updating old passwords to use latest cipher

Cipher recommendation changed from md5 to scram-sha-256. Add this line to upgrade from md5 to scram:

sudo emacs -nw /etc/postgresql/12/main/postgresql.conf
   # MDM upgrade from md5 to scram                                          
   password_encryption = scram-sha-256

After upgrading and restarting postgres, old md5 passwords will start to fail. To update an old password, just retype them:

sudo su postgres
psql
\password ##username##

Old notes that may help

Postgres is a NASTY BEAST with SHITTY DEFAULT CONFIGS...

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

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.

Disabling multiple installs

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

On Ubuntu, watch out for multiple installs causing the postgres port to change from the default. If you have two previous postgres versions, version 12 will use port 5432+1+1 = 5434. This is very confusing. You can see what postgres versions are running on what port in Ubuntu with this:

pg_lsclusters

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]

OLD: PGAdmin is running on bitpost and can connect to any db on the LAN.