PostgreSQL

Snippets #

Start docker #

Assumptions:

• There is a single .sql dump file located in $PGDATA/activeBkp which should be loaded into docker on first run
• There is a $PGDATA/pgdata directory which will be linked to the docker volume, so data is persisted
• There is no other postgres instance currently running on port 5432. If there is, change -p 5432:5432 to -p 5433:5432

export PROG=/path/to/your/projects/directory
export PGDATA=$PROG/__data/postgres
# replace USERNAME, PASSWORD
docker run --name local-pg -e POSTGRES_USER=USERNAME -e POSTGRES_PASSWORD=PASSWORD -p 5432:5432 -v $PGDATA/activeBkp:/docker-entrypoint-initdb.d -v $PGDATA/pgdata:/var/lib/postgresql/data -d postgres

Connect to local db using psql #

# replace DB_NAME, USERNAME
psql -h localhost -p 5432 -d DB_NAME -U USERNAME

Connect to remote psql from pod in kubernetes cluster #

# replace DB_NAME, 192.168.1.100, USERNAME
kubectl exec -it pod-name -- psql -U USERNAME -h 192.168.1.100 -d DB_NAME -W

Liquibase generation and first changeset #

Needed to add this sql command to the top of an auto-generated liquibase changeset

    CREATE SCHEMA IF NOT EXISTS SCHEMA_NAME; -- replace SCHEMA_NAME
    CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
    CREATE EXTENSION IF NOT EXISTS hstore;

Trigger / Function to auto update timestamp when row data is changed #

-- add function to auto modify updated_at when a row changes
CREATE OR REPLACE FUNCTION SCHEMA_NAME.modify_updated_at_timestamp() -- replace SCHEMA_NAME
RETURNS TRIGGER AS $$
BEGIN
   IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
      NEW.updated_at = now();
RETURN NEW;
ELSE
      RETURN OLD;
END IF;
END;
$$ language 'plpgsql';

-- add trigger on accounts table to call above function
CREATE OR REPLACE TRIGGER account_updated_trigger
    BEFORE UPDATE
    ON SCHEMA_NAME.accounts -- replace SCHEMA_NAME
    FOR EACH ROW
    EXECUTE PROCEDURE SCHEMA_NAME.modify_updated_at_timestamp(); -- replace SCHEMA_NAME

Go To References #

• Docker image
• Find where data is stored – Within psql: SHOW data_directory
• Create table - Postgres Tutorial

  CREATE TABLE SCHEMA_NAME.accounts ( -- replace SCHEMA_NAME
      user_id serial PRIMARY KEY,
      guid UUID DEFAULT public.uuid_generate_v1(),
      username VARCHAR ( 50 ) UNIQUE NOT NULL,
      password VARCHAR ( 50 ) NOT NULL,
      email VARCHAR ( 255 ) UNIQUE NOT NULL,
      is_active BOOLEAN DEFAULT TRUE,
      created_on TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(),
      updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(),
      last_login TIMESTAMP 
  );
  

• Create and call a function - PostgreSQL Docs – Call function with SELECT function_name(params)
• Date/Time Functions and Operations - PostgreSQL Docs – Useful snippets now() and current_date
• COALESCE function – COALESCE tries to ensure data inserted is not null by allowing some default values. Pass it a few arguments and it will return the first non-null value from that list. e.g. SELECT COALESCE (1,2); -- returns 1 e.g. SELECT COALESCE (NULL, 2, 1); -- returns 2
• INSERT data in a table –

    INSERT INTO SCHEMA_NAME.accounts(username, password, email, is_active)
        VALUES
            ('serj.tankian', '4banana.teracotta.banana.2teracotta.pie', '[email protected]', TRUE),
            ('darron.malakian', 'such.a.lonely.day', '[email protected]', TRUE),
            ('john.dolmayan', 'Bb$sBb$sBb$sBb$sBb$sBb$s', '[email protected]', TRUE),
            ('shavo.odadjian', 'brbr.deng.brbr.deng', '[email protected]', TRUE);
  

• Change user to superuser - Chartio – ALTER USER ROLE_NAME WITH SUPERUSER

Videos #