March 2016 – Database Consultant

PostgreSQL study notes – psql

Features

non-interactive usage
command history (up/down arrow)
tab completion (sans Windows)
commands terminate with semi-colon and can wrap lines
defaults to supplying currently logged-in username as pg use

Tasks

explore ‘psql’
a. ‘psql –version’ returns version of postgresql client
b. ‘psql -l -U postgres’ lists installed db’s then exits.
postgreSQL installs 3 default db’s
1. ‘postgres’ – management db. contains user accounts, global settings, etc
2. ‘template0’ – vanilla read-only db
3. ‘template1’ – changable copy of template0, used as template for new db’s
c. ‘psql’ with no options enters interactive mode (duh)
a hash-mark ending the interactive-prompt denotes a ‘superuser’ eg: ‘postgres=#’
d. ‘\h’ – returns sql-specific help eg: ‘ALTER TABLE …’
‘\h create’ filters above to just ‘CREATE …’ commands
e. ‘\?’ – returns ‘psql’ specific help eg: ‘\?’ ie: usable metasequences ie shortcuts
f. ‘\l’ – returns list of DBs. ‘\l+’ additionally returns DB sizes
g. ‘\du[+]’ – returns list of users with access to postgresql.
h. ‘\!’ open shell from session (‘exit’ from shell = back to psql)
i. ‘\! [command] – runs command in shell non-interactively and returns to psql.
j. ‘\i filename’ – execute command(s) in the file ie psql or sql commands
k, multiple commands can be run on one line. Separate with space, terminate with semi-colon (eg: ‘\l \du;’)
L: ‘\c’ – connect to another database or host eg: ‘\c template1’
m. ‘\d’ list tables\views etc in current DB, ‘\dS’ list system tables, ‘\dS+’ list system tables with sizes.
n, \q quit
c. ‘psql –help’ (or ‘psql -?’) psql option switches & defaults (short version then long version) eg: -U (username – short version), -l (list – short version), –version (long version) …

PostgreSQL study notes – Installation

Download from Enterprise DB, gui is the one to go for, remember xhost on linux

1. Install. For prod you should indicate that data files are stored independently of the source tree. A ‘Cluster’ is not a classic Cluster IE: a Server Cluster, just means all the databases on this particular box.

2. Explore the footprint of the install. ‘\\bin\ contains utilities like psql.exe (terminal monitor). \\data\ contains all databases and 3x config files & pg_log/ (log files), pg_xlog/ (write ahead log folder). postmaster.opts (startup options)

3. Provide access to internal docs via web-browser bookmark eg: file:///C:/Program%20Files/PostgreSQL/9.5/doc/postgresql/html/index.html

4. Add \\bin file to path – for psql.exe etc (eg: C:\Program Files\PostgreSQL\9.5\bin). posqlgresql clients default to submitting the current logged-in users name as the DB user name (eg: “psql” without -U will assum user is windows-user).

5. Add system variable ‘PGUSER=postgres’ workaround so psql etc wont try to login to utilities as o/s-user

More thoughts against Triggers

For Rob & Karl – Triggers run outside of transactions. An insert that fires a trigger may be rolled back, but the trigger rolls on.

Triggers introduce a long-term maintenance headache. You can read a stored-procedure from top to bottom and imagine you understand what it does. But unless you examine every tables it touches – you don’t. Little bits of code may be running silently which augment or even reverse some of the logic within the stored-procedure.

Triggers are used by lazy developers to ‘bolt on’ new features to applications, rather than track-down all the code that could insert/update/delete from a table and add the code (or a link to it) there.

This would be forgivable if the application code was closed or propitiatory, but never when the application is open to the application developer, who just cannot be bothered to integrate code changes properly, and cares not-a-jot about long-term maintenance headaches (slow breaths, slow breaths :))

PostgreSQL study notes – Features

Object Relational Database Management System (ORDBMS) objects (eg: tables) can be related in a Hierarchy: Parent -> Child
Transactional RDBMS: SQL statements have implicit: BEGIN; COMMIT: statements. SQL statements may also have explicit BEGIN COMMIT statements
developed at UC Berkeley like along with bsd-unix
One process per connection: master process = “postmaster” auto-spawns per new connection
Processed (pid’s) use one cpu-core per connection: o/s may spawn new connections on a different cpu-core. no cross-core queries
Multiple helper processes, which appear as ‘postgres’ instances, always running eg: stats collector, background writer (protect agains sudden failure ), auto-vacuum (cleanup/ space reclaimer), wal sender (that’s write ahead log)
max db size: unlimited – limited by available storage.(terabytes, perabytes, exabytes)
max table size: 32tb – stored as multiple 1gb files – changable (could be prob for some o/s’s)
max row size: 400gb
max column size: 1gb (per row ie: per field)
max indexes per table: unlimited
max identifier length (db objects – tables, columns, triggers, functions@ 63 bytes. this is extensible via source code
default listener tcp port 5432. so may install postgresql as non-privilaged user
users are distinct from o/s users
users are authenticated globally (per server), then assigned permissions per database.
inheratance. tables lower in hierachy may inherit columns from heigher tables (ie parents) so long as no contraints eg foreign keys.
case insensative commands – without double quotes (eg: select * from syslog;)
case sensative commands – with double quotes – (eg: select * from “syslog”;)
three primary config files, located in postgres-root A.pg_hba.conf (host based access) B.postgresql.conf general settings C. pg_ident.conf – user mappings
integrated log rotation (config by age or size)

Capitalization

Scripting languages are wonderful things. They use subsets of English and are therefore easy to learn (EG: update, delete, get, put).

However capitalization is totally redundant. A capital letter marks the beginning of a sentence, but scripts do not use sentences.

One alphabet is enough (a to z) who needs another one (A to Z) that is completely equivalent?

Imagine responding to the adhoc query “Please email me a list of most ordered items over Christmas” with “in what font?” lol

Although … one trick that I have used over the years, with having two alphabets, is to temporarily change the capitalization of text to double-check for myself that Replication etc is actually working (before the days of tracer-token poo sticks). Changing the data look without changing the data value.

Another sneaky trick is making mass changes to data whilst adding a flag (to only the changed data). For example changing every field containing ‘unsubscribe’ to ‘uNsubscribe’, or ‘yes’to ‘yEs’.

And then repeating with un-flagged fields until only ‘unsuscribe’ or ‘Yep’ remain (lol).

This (typical DBA belt & braces) method almost always guarantees you will not induce any unintended processing errors further down the line, as the data always remains the same length, type and meaning.