Postgres Connection Hang

data.sql

--------------------------------------------------------------
-- Practical SQL: A Beginner's Guide to Storytelling with Data
-- by Anthony DeBarros

-- Chapter 11 Code Examples
--------------------------------------------------------------

-- Listing 11-1: Extracting components of a timestamp value using date_part()

SELECT
    date_part('year', '2019-12-01 18:37:12 EST'::timestamptz) AS "year",
    date_part('month', '2019-12-01 18:37:12 EST'::timestamptz) AS "month",
    date_part('day', '2019-12-01 18:37:12 EST'::timestamptz) AS "day",
    date_part('hour', '2019-12-01 18:37:12 EST'::timestamptz) AS "hour",
    date_part('minute', '2019-12-01 18:37:12 EST'::timestamptz) AS "minute",
    date_part('seconds', '2019-12-01 18:37:12 EST'::timestamptz) AS "seconds",
    date_part('timezone_hour', '2019-12-01 18:37:12 EST'::timestamptz) AS "tz",
    date_part('week', '2019-12-01 18:37:12 EST'::timestamptz) AS "week",
    date_part('quarter', '2019-12-01 18:37:12 EST'::timestamptz) AS "quarter",
    date_part('epoch', '2019-12-01 18:37:12 EST'::timestamptz) AS "epoch";

-- Bonus: Using the SQL-standard extract() for similar datetime parsing:

SELECT extract('year' from '2019-12-01 18:37:12 EST'::timestamptz) AS "year";

-- Listing 11-2: Three functions for making datetimes from components

-- make a date
SELECT make_date(2018, 2, 22);
-- make a time
SELECT make_time(18, 4, 30.3);
-- make a timestamp with time zone
SELECT make_timestamptz(2018, 2, 22, 18, 4, 30.3, 'Europe/Lisbon');

-- Bonus: Retrieving the current date and time

SELECT
    current_date,
    current_time,
    current_timestamp,
    localtime,
    localtimestamp,
    now();

-- Listing 11-3: Comparing current_timestamp and clock_timestamp() during row insert

CREATE TABLE current_time_example (
    time_id bigserial,
    current_timestamp_col timestamp with time zone,
    clock_timestamp_col timestamp with time zone
);

INSERT INTO current_time_example (current_timestamp_col, clock_timestamp_col)
    (SELECT current_timestamp,
            clock_timestamp()
     FROM generate_series(1,1000));

SELECT * FROM current_time_example;

-- Time Zones

-- Listing 11-4: Showing your PostgreSQL server's default time zone

SHOW timezone; -- Note: You can see all run-time defaults with SHOW ALL;

-- Listing 11-5: Showing time zone abbreviations and names

SELECT * FROM pg_timezone_abbrevs;
SELECT * FROM pg_timezone_names;

-- Filter to find one
SELECT * FROM pg_timezone_names
WHERE name LIKE 'Europe%';

-- Listing 11-6: Setting the time zone for a client session

SET timezone TO 'US/Pacific';

CREATE TABLE time_zone_test (
    test_date timestamp with time zone
);
INSERT INTO time_zone_test VALUES ('2020-01-01 4:00');

SELECT test_date
FROM time_zone_test;

SET timezone TO 'US/Eastern';

SELECT test_date
FROM time_zone_test;

SELECT test_date AT TIME ZONE 'Asia/Seoul'
FROM time_zone_test;


-- Math with dates!

SELECT '9/30/1929'::date - '9/27/1929'::date;
SELECT '9/30/1929'::date + '5 years'::interval;


-- Taxi Rides

-- Listing 11-7: Creating a table and importing NYC yellow taxi data

CREATE TABLE nyc_yellow_taxi_trips_2016_06_01 (
    trip_id bigserial PRIMARY KEY,
    vendor_id varchar(1) NOT NULL,
    tpep_pickup_datetime timestamp with time zone NOT NULL,
    tpep_dropoff_datetime timestamp with time zone NOT NULL,
    passenger_count integer NOT NULL,
    trip_distance numeric(8,2) NOT NULL,
    pickup_longitude numeric(18,15) NOT NULL,
    pickup_latitude numeric(18,15) NOT NULL,
    rate_code_id varchar(2) NOT NULL,
    store_and_fwd_flag varchar(1) NOT NULL,
    dropoff_longitude numeric(18,15) NOT NULL,
    dropoff_latitude numeric(18,15) NOT NULL,
    payment_type varchar(1) NOT NULL,
    fare_amount numeric(9,2) NOT NULL,
    extra numeric(9,2) NOT NULL,
    mta_tax numeric(5,2) NOT NULL,
    tip_amount numeric(9,2) NOT NULL,
    tolls_amount numeric(9,2) NOT NULL,
    improvement_surcharge numeric(9,2) NOT NULL,
    total_amount numeric(9,2) NOT NULL
);

COPY nyc_yellow_taxi_trips_2016_06_01 (
    vendor_id,
    tpep_pickup_datetime,
    tpep_dropoff_datetime,
    passenger_count,
    trip_distance,
    pickup_longitude,
    pickup_latitude,
    rate_code_id,
    store_and_fwd_flag,
    dropoff_longitude,
    dropoff_latitude,
    payment_type,
    fare_amount,
    extra,
    mta_tax,
    tip_amount,
    tolls_amount,
    improvement_surcharge,
    total_amount
   )
FROM '/tmp/yellow_tripdata_2016_06_01.csv'
WITH (FORMAT CSV, HEADER, DELIMITER ',');

CREATE INDEX tpep_pickup_idx
ON nyc_yellow_taxi_trips_2016_06_01 (tpep_pickup_datetime);

SELECT count(*) FROM nyc_yellow_taxi_trips_2016_06_01;

-- Listing 11-8: Counting taxi trips by hour

SELECT
    date_part('hour', tpep_pickup_datetime) AS trip_hour,
    count(*)
FROM nyc_yellow_taxi_trips_2016_06_01
GROUP BY trip_hour
ORDER BY trip_hour;

-- Listing 11-9: Exporting taxi pickups per hour to a CSV file

COPY
    (SELECT
        date_part('hour', tpep_pickup_datetime) AS trip_hour,
        count(*)
    FROM nyc_yellow_taxi_trips_2016_06_01
    GROUP BY trip_hour
    ORDER BY trip_hour
    )
TO '/tmp/hourly_pickups_2016_06_01.csv'
WITH (FORMAT CSV, HEADER, DELIMITER ',');

-- Listing 11-10: Calculating median trip time by hour

SELECT
    date_part('hour', tpep_pickup_datetime) AS trip_hour,
    percentile_cont(.5)
        WITHIN GROUP (ORDER BY
            tpep_dropoff_datetime - tpep_pickup_datetime) AS median_trip
FROM nyc_yellow_taxi_trips_2016_06_01
GROUP BY trip_hour
ORDER BY trip_hour;

-- Listing 11-11: Creating a table to hold train trip data

SET timezone TO 'US/Central';

CREATE TABLE train_rides (
    trip_id bigserial PRIMARY KEY,
    segment varchar(50) NOT NULL,
    departure timestamp with time zone NOT NULL,
    arrival timestamp with time zone NOT NULL
);

INSERT INTO train_rides (segment, departure, arrival)
VALUES
    ('Chicago to New York', '2017-11-13 21:30 CST', '2017-11-14 18:23 EST'),
    ('New York to New Orleans', '2017-11-15 14:15 EST', '2017-11-16 19:32 CST'),
    ('New Orleans to Los Angeles', '2017-11-17 13:45 CST', '2017-11-18 9:00 PST'),
    ('Los Angeles to San Francisco', '2017-11-19 10:10 PST', '2017-11-19 21:24 PST'),
    ('San Francisco to Denver', '2017-11-20 9:10 PST', '2017-11-21 18:38 MST'),
    ('Denver to Chicago', '2017-11-22 19:10 MST', '2017-11-23 14:50 CST');

SELECT * FROM train_rides;

-- Listing 11-12: Calculating the length of each trip segment

SELECT segment,
       to_char(departure, 'YYYY-MM-DD HH12:MI a.m. TZ') AS departure,
       arrival - departure AS segment_time
FROM train_rides;

-- Listing 11-13: Calculating cumulative intervals using OVER

SELECT segment,
       arrival - departure AS segment_time,
       sum(arrival - departure) OVER (ORDER BY trip_id) AS cume_time
FROM train_rides;

-- Listing 11-14: Better formatting for cumulative trip time

SELECT segment,
       arrival - departure AS segment_time,
       sum(date_part('epoch', (arrival - departure)))
           OVER (ORDER BY trip_id) * interval '1 second' AS cume_time
FROM train_rides;

deployment-postgres.sql

kind: Deployment
apiVersion: apps/v1
metadata:
  name: postgres
  namespace: gh-5539
spec:
  replicas: 1
  selector:
    matchLabels:
      name: postgres
  template:
    metadata:
      annotations:
        linkerd.io/inject: enabled
#        config.linkerd.io/skip-inbound-ports: "5432"
      labels:
        name: postgres
        deployment: postgres
    spec:
      #priorityClassName: high-priority
      volumes:
      - name: postgres-dbpath
        persistentVolumeClaim:
          # This disk must already exist.
          claimName: linkerd-postgres-pvc
          #fsType: ext4
      containers:
      - name: postgres-pod
        ports:
        - containerPort: 5432
          protocol: TCP
        imagePullPolicy: IfNotPresent
        image: postgres:12
        resources:
          requests:
            memory: "0.5Gi"
            cpu: "0.01"
          limits:
            memory: "1Gi"
            cpu: "2"
        env:
        - name: POSTGRES_USER
          value: "linkerd"
        - name: POSTGRES_PASSWORD
          valueFrom:
            secretKeyRef:
              name: postgres-secrets
              key: password
        readinessProbe:
          exec:
            command:
            - pg_isready
        volumeMounts:
        - name: postgres-dbpath
          mountPath: /tmp/lib/postgresql/data
      restartPolicy: Always
      dnsPolicy: ClusterFirst
      nodeSelector:
        cloud.google.com/gke-nodepool: 'default-pool'

deployment-psql.yml

kind: Deployment
apiVersion: apps/v1
metadata:
  name: psql
  namespace: gh-5539
spec:
  replicas: 1
  selector:
    matchLabels:
      name: psql
  template:
    metadata:
      annotations:
        linkerd.io/inject: enabled
#        config.linkerd.io/skip-inbound-ports: "5432"
      labels:
        name: psql
        deployment: psql
    spec:
      #priorityClassName: high-priority
      volumes:
      - name: psql-dbpath
        persistentVolumeClaim:
          # This disk must already exist.
          claimName: linkerd-postgres-pvc-1
          #fsType: ext4
      containers:
      - name: psql-pod
        ports:
        - containerPort: 5432
          protocol: TCP
        imagePullPolicy: IfNotPresent
        image: postgres:12
        resources:
          requests:
            memory: "0.5Gi"
            cpu: "0.01"
          limits:
            memory: "1Gi"
            cpu: "2"
        env:
        - name: POSTGRES_USER
          value: "linkerd"
        - name: POSTGRES_PASSWORD
          valueFrom:
            secretKeyRef:
              name: postgres-secrets
              key: password
        readinessProbe:
          exec:
            command:
            - pg_isready
        volumeMounts:
        - name: psql-dbpath
          mountPath: /tmp/lib/postgresql/data
      restartPolicy: Always
      dnsPolicy: ClusterFirst
      nodeSelector:
        cloud.google.com/gke-nodepool: 'default-pool'

gh-5539.yml

apiVersion: v1
kind: Namespace
metadata:
  name: gh-5539
---
kind: Service
apiVersion: v1
metadata:
  name: postgres
  labels:
    name: postgres
    namespace: gh-5539
    deployment: postgres
spec:
  ports:
  - port: 5432
  selector:
    name: postgres

load.sh

#!/bin/bash

set -e
# set -x

PSQL_POD=`kubectl get po -l deployment=psql -o jsonpath='{.items[0].metadata.name}'`
POSTGRES_POD=`kubectl get po -l deployment=postgres -o jsonpath='{.items[0].metadata.name}'`
DATA_FILE_NAME=yellow_tripdata_2016_06_01.csv
DATA_FILE_URL=https://github.com/anthonydb/practical-sql/raw/master/Chapter_11/yellow_tripdata_2016_06_01.csv
SQL_FILE_NAME=data.sql

if [ ! -f $DATA_FILE_NAME ]; then
  curl -sl $DATA_FILE_URL
else
  printf "Using existing datafile"
fi

echo ""
echo "====== head file data======"
head $DATA_FILE_NAME
echo "====== head file data======"
echo ""

if [ ! -f $DATA_FILE_NAME ]; then
  echo "Get the data file here: https://github.com/anthonydb/practical-sql/raw/master/Chapter_11/yellow_tripdata_2016_06_01.csv \n"
fi

echo ""
echo "copy $DATA_FILE_NAME to $POSTGRES_POD"
kubectl cp $DATA_FILE_NAME $POSTGRES_POD:/tmp -c postgres-pod

echo ""
echo "copy $SQL_FILE_NAME to $PSQL_POD"
kubectl cp $SQL_FILE_NAME $PSQL_POD:/tmp -c psql-pod

printf "\n\tFiles are loaded, if the pods are healthy exec into the psql pod with the command"
printf "\n\t\tkubectl exec -it \\"
printf "\n\t\t  \`kubectl get po -l deployment=postgres -o jsonpath='{.items[0].metadata.name}'\` \\"
printf "\n\t\t  -c psql-pod -- /bin/bash"
printf "\n\tthen run"
printf "\n\t\tpsql -U linkerd -h postgres < /tmp/data.sql\n"

pv.yml

apiVersion: v1
kind: PersistentVolume
metadata:
  name: linkerd-postgres-pv-1
  labels:
    type: local
spec:
  storageClassName: standard
  capacity:
    storage: 10Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: "/tmp/data"
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: linkerd-postgres-pvc-1
spec:
  storageClassName: standard
  accessModes:
  - ReadWriteOnce
  resources:
    requests:
      storage: 3Gi
---
apiVersion: v1
kind: PersistentVolume
metadata:
  name: linkerd-postgres-pv
  labels:
    type: local
spec:
  storageClassName: standard
  capacity:
    storage: 10Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: "/tmp/data"
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: linkerd-postgres-pvc
spec:
  storageClassName: standard
  accessModes:
  - ReadWriteOnce
  resources:
    requests:
      storage: 3Gi

setup.sh

#! /bin/bash

set -e
set -x

kubectl apply -f pv.yml -f gh-5539.yml

kubectl create secret generic postgres-secrets \
  -n gh-5539 \
  --from-literal=password=linkerd-test

kubectl apply -f deployment-psql.yml -f deployment-postgres.yml

kubectl get po -n gh-5539
kubectl get svc -n gh-5539
kubectl get secret -n gh-5539