timescale/timescaledb-docker

getting started fails in docker

mxfactorial opened this issue · 1 comments

mxfactorial commented

  1. create run.sh with getting started steps:

    #!/bin/bash

docker run \
    --rm \
    -d \
    --name timescaledb \
    -e POSTGRES_PASSWORD=password \
    timescale/timescaledb:latest-pg13

sleep 2 # wait for postgres availability

docker exec timescaledb psql -U postgres -d postgres -c "CREATE DATABASE weather;"

sql="CREATE TABLE IF NOT EXISTS weather_metrics (
time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
timezone_shift int NULL,
city_name text NULL,
temp_c double PRECISION NULL,
feels_like_c double PRECISION NULL,
temp_min_c double PRECISION NULL,
temp_max_c double PRECISION NULL,
pressure_hpa double PRECISION NULL,
humidity_percent double PRECISION NULL,
wind_speed_ms double PRECISION NULL,
wind_deg int NULL,
rain_1h_mm double PRECISION NULL,
rain_3h_mm double PRECISION NULL,
snow_1h_mm double PRECISION NULL,
snow_3h_mm double PRECISION NULL,
clouds_percent int NULL,
weather_type_id int NULL
);

SELECT create_hypertable('weather_metrics','time');"

docker exec timescaledb psql -U postgres -d weather -c "${sql}"

docker exec timescaledb wget -O /tmp/weather_data.zip https://s3.amazonaws.com/assets.timescale.com/docs/downloads/weather_data.zip

docker exec timescaledb sh -c 'cd /tmp; unzip -j /tmp/weather_data.zip'

cmd="\copy weather_metrics (time, timezone_shift, city_name, temp_c, feels_like_c, temp_min_c, temp_max_c, pressure_hpa, humidity_percent, wind_speed_ms, wind_deg, rain_1h_mm, rain_3h_mm, snow_1h_mm, snow_3h_mm, clouds_percent, weather_type_id) from '/tmp/weather_data.csv' CSV HEADER;"

docker exec timescaledb psql -U postgres -d weather -c "${cmd}"

docker stop timescaledb

  2. chmod +x run.sh && ./run.sh

observed

d1694b0ede1a68757f5b8532bddc0da4ea68face595ae1fdaf6aaca271f64c2e
CREATE DATABASE
      create_hypertable
------------------------------
 (1,public,weather_metrics,t)
(1 row)

Connecting to s3.amazonaws.com (52.217.167.240:443)
saving to '/tmp/weather_data.zip'
weather_data.zip       1% |                                | 1036k  0:01:05 ETA
...
weather_data.zip     100% |********************************| 67.5M  0:00:00 ETA
'/tmp/weather_data.zip' saved
Archive:  /tmp/weather_data.zip
  inflating: weather_data.csv
  inflating: ._weather_data.csv
ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
CONTEXT:  COPY weather_metrics, line 2786118: "2009-11-26 00:00:00,-18000,New York,10.8,8.69,10.26,11.27,1015.0,87.0,2.6,70,0.3,,,,90,500"
mxfactorial commented

fixed with TS_TUNE_MEMORY=6GB docker run env var

  1. create run.sh with getting started steps:

    #!/bin/bash

docker run \
    --rm \
    -d \
    --name timescaledb \
    -e TS_TUNE_MEMORY=6GB \
    -e POSTGRES_PASSWORD=password \
    timescale/timescaledb:latest-pg13

sleep 2 # wait for postgres availability

docker exec timescaledb psql -U postgres -d postgres -c "CREATE DATABASE weather;"

sql="CREATE TABLE IF NOT EXISTS weather_metrics (
time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
timezone_shift int NULL,
city_name text NULL,
temp_c double PRECISION NULL,
feels_like_c double PRECISION NULL,
temp_min_c double PRECISION NULL,
temp_max_c double PRECISION NULL,
pressure_hpa double PRECISION NULL,
humidity_percent double PRECISION NULL,
wind_speed_ms double PRECISION NULL,
wind_deg int NULL,
rain_1h_mm double PRECISION NULL,
rain_3h_mm double PRECISION NULL,
snow_1h_mm double PRECISION NULL,
snow_3h_mm double PRECISION NULL,
clouds_percent int NULL,
weather_type_id int NULL
);

SELECT create_hypertable('weather_metrics','time');"

docker exec timescaledb psql -U postgres -d weather -c "${sql}"

docker exec timescaledb wget -O /tmp/weather_data.zip https://s3.amazonaws.com/assets.timescale.com/docs/downloads/weather_data.zip

docker exec timescaledb sh -c 'cd /tmp; unzip -j /tmp/weather_data.zip'

cmd="\copy weather_metrics (time, timezone_shift, city_name, temp_c, feels_like_c, temp_min_c, temp_max_c, pressure_hpa, humidity_percent, wind_speed_ms, wind_deg, rain_1h_mm, rain_3h_mm, snow_1h_mm, snow_3h_mm, clouds_percent, weather_type_id) from '/tmp/weather_data.csv' CSV HEADER;"

docker exec timescaledb psql -U postgres -d weather -c "${cmd}"

docker exec timescaledb psql -U postgres -d weather -c "\dt+"

docker exec timescaledb psql -U postgres -d weather -c "select count(*) from weather_metrics;"

docker stop timescaledb

  2. chmod +x run.sh && ./run.sh

observed

598b318be59ffa2f1f570a7d09dc92707bba9bc5e03dbd036127af7f5f5aaafe
CREATE DATABASE
      create_hypertable
------------------------------
 (1,public,weather_metrics,t)
(1 row)

Connecting to s3.amazonaws.com (52.217.93.206:443)
saving to '/tmp/weather_data.zip'
weather_data.zip       2% |                                | 1733k  0:00:38 ETA
...
weather_data.zip     100% |********************************| 67.5M  0:00:00 ETA
'/tmp/weather_data.zip' saved
Archive:  /tmp/weather_data.zip
  inflating: weather_data.csv
  inflating: ._weather_data.csv
COPY 3794998
                                  List of relations
 Schema |      Name       | Type  |  Owner   | Persistence |    Size    | Description
--------+-----------------+-------+----------+-------------+------------+-------------
 public | weather_metrics | table | postgres | permanent   | 8192 bytes |
(1 row)

  count
---------
 3794998
(1 row)

timescaledb