Skip to main content
  1. Refs/

PostgreSQL

·3 mins

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 #

Videos #




comments powered by Disqus