Home Assistant Add-on: PostgreSQL + TimescaleDB

[dsheen (Dan)]

Hi

has anyone found a solution to the problem that User "postgres" has no password assigned. after upgrade?

I can see the issue is closed on GH but the suggested solution seems to be to downgrade – which I not find satisfying.

The comment by @Expaso also stresses that all databases need to be listed in the plugin config. How would I know which ones these are?

I would appreciate if all steps are clearly spelled out.

I had a look for the /data/postgres directories but when I ssh into my HASSIO installation the /data directory does not include any such directories.

I had a look into the /addons directory but that is empty.

Help would be appreciated.

[dsheen (Dan)]

I managed to find the data folder by logging in via port 22222 (following these instructions: Debugging the Home Assistant Operating System | Home Assistant Developer Docs). Then found the DB files in /mnt/data/supervisor/addons/data/77b2833f_timescaledb.

Now that I can move the postgres data folder - how do I trigger either downgrade or restart the upgrade?

The addon is listed in version 2.0.1

[dsheen (Dan)]

I tried to restore a partial backup to version 1.1.6 but that failed with
'BackupManager.do_restore_partial' blocked from execution, system is not running - CoreState.FREEZE

Any ideas?

[Expaso]

Hi Dan!,

Just restart the 2.0.1 addon when you moved your postgres12 folder to postgres.
It will just try the upgrade again. Make sure you have listed all timescale capable databases in the config before starting the addon.

[dsheen (Dan)]

Yes, that works! Many thanks!

[skank]

Second attempt to try to update from 1.6
AGain, failures

Checking for presence of required libraries fatal
Your installation references loadable libraries that are missing from the
new installation. You can add these libraries to the new installation,
or remove the functions using them from the old installation. A list of
problem libraries is in the file:
loadable_libraries.txt
Failure, exiting
[12:42:31] ERROR: PostgreSQL could not upgrade! Please inspect any errors in the lines above!
[12:42:31] ERROR: Upgrade was not succesfull.
[cont-init.d] init.sh: exited 1.
[cont-finish.d] executing container finish scripts…
[cont-finish.d] 99-message.sh: executing…

            Oops! Something went wrong.

We are so sorry, but something went terribly wrong when
starting or running this add-on.

Be sure to check the log above, line by line, for hints.

[cont-finish.d] 99-message.sh: exited 0.
[cont-finish.d] done.
[s6-finish] waiting for services.
[s6-finish] sending all processes the TERM signal.
[s6-finish] sending all processes the KILL signal and exiting.

Any help would be appreciated, total new here

[rikm]

@poudenes Could you please share you backup procedure? Unfortunately I have no clue how to set it up.

[Maxobus]

I’m feeling dumb.
Trying to add repository https://github.com/Expaso/hassos-addons and getting “Invalid Add-on repository!”.
HassOS 2022.4.7

What I’m doing wrong?

[GeertV (Geert Verstrepen)]

Just installed the add-on and also pgAdmin4, and configured it in HA for recorder.
All seems to work fine, except lost energy dashboard, so thanks for this great job !
Altough I have some questions

• I changed pw of postrges user, but isn’t it better to create additional users for homeassistant,…? But which rights should they get ?
• Concerning backup, is this taken into account in the standard snapshots, or anything else needed ?
• I see following warning, is this an issue

[12:17:12] INFO: Upgrading PostGIS for database: 'dsmrreader'
2022-05-03 10:17:12.658 UTC [497] ERROR:  function postgis_extensions_upgrade() does not exist at character 8
2022-05-03 10:17:12.658 UTC [497] HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2022-05-03 10:17:12.658 UTC [497] STATEMENT:  SELECT PostGIS_Extensions_Upgrade();
ERROR:  function postgis_extensions_upgrade() does not exist
LINE 1: SELECT PostGIS_Extensions_Upgrade();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

• The old db file can be deleted ?
  Now I’ll dive into LTSS

[Maxobus]

Now it’s working.

[rikm]

I would really appreciate if anybody could share a backup & restore process with pgAdmin or pg_dump. I spent hours and hours without succeeding. If I want to restore a backup, I always end up with errors like this:

pg_restore: creating CONSTRAINT “public.ltss ltss_pkey”
pg_restore: from TOC entry 4566; 2606 18792 CONSTRAINT ltss ltss_pkey postgres
pg_restore: error: could not execute query: ERROR: ONLY option not supported on hypertable operations

[poudenes]

Hi All,

Is there a way to export or backup a timescaledB that can be imported into a normal postgresdB?
I want move my dB from timescaleDB to PosrgresdB on my NAS.

Backup is ok, but when import it I get errors that some

_timescaledb_cache
_timescaledb_catalog
_timescaledb_config
_timescaledb_internal
_timescaledb_experminental
_timescaledb_information

is not in the schemas.

The public is ok. And I can see al my old data when load it into the program that use the database.
But its not add information in the database.

[manonfgoo (mfg)]

Hi there,

thanks alot for the addon! I would like to use the statistic functions from the TimescaleDB Toolkit, is this included in the image? if not do you have any hints how to install it?

[tldp]

Same request here, I’d really like to use functions from the TimescaleDB Toolkit (e.g. time_weight)

[Expaso]

Hi Guys!

I’m looking into this, but it looks like there is currently no support for ARM architecture: toolkit support for armv7 docker containers · Issue #212 · timescale/timescaledb-toolkit (github.com)

Still, I will see what I can do… stay tuned. If I have any news, I will report back immediately.

[tldp]

Thanks! I tried to add the toolbox-build to your Dockerfile but faced many issues.
Had to resolve many dependency issues with the current Dockerfile (e.g. timescaledb-tune didn’t work anymore, some conflicts with pg versions and so on)

When I finally got the rust compiler to run within the container I faced issues with the timescaledb-toolbox build itself and gave up.

Happy to help if you need anything!

[Expaso]

hi @tldp ,

Thanks for the try! Yes, maintaining this dockerfile isn’t always smooth sailing…
It’s multi-arch, and sometimes the complete dependency-hell keeps me busy for days!

Like tonight!

affected/package: sys · Issue #55078 · golang/go (github.com)

[tldp]

Yes, I remember that one. Used the most recent go version (1.19.1) to resolve.

I’m currently not at home, but will send you my modified Dockerfile in the evening.
I hope it might help!

[tldp]

This is my modified Dockerfile. You have to set

BUILD_FROM=ghcr.io/hassio-addons/base/amd64:12.2.3

for the error you mentioned; there is no need then to hassle with go-versions

ARG BUILD_FROM=ghcr.io/hassio-addons/base/aarch64:11.1.1
ARG BUILD_ARCH=aarch64
###########################################
# Build TimeScaleDB tools binaries in separate image
###########################################
ARG GO_VERSION=1.14.0

FROM golang:${GO_VERSION}-alpine AS tools

RUN apk update && apk add --no-cache git \
    #&& go get github.com/timescale/timescaledb-tune/cmd/timescaledb-tune \
    && go get github.com/timescale/timescaledb-parallel-copy/cmd/timescaledb-parallel-copy \
    && go get github.com/timescale/timescaledb-backup/cmd/ts-dump \
    && go get github.com/timescale/timescaledb-backup/cmd/ts-restore \
    #&& go build -o /go/bin/timescaledb-tune -v github.com/timescale/timescaledb-tune/cmd/timescaledb-tune \
    && go build -o /go/bin/timescaledb-parallel-copy -v github.com/timescale/timescaledb-parallel-copy/cmd/timescaledb-parallel-copy \
    && go build -o /go/bin/ts-dump -v github.com/timescale/timescaledb-backup/cmd/ts-dump \
    && go build -o /go/bin/ts-restore -v github.com/timescale/timescaledb-backup/cmd/ts-restore

####################################################
# Build a latest timescaledb against postgres-12
# We use this to upgrade timescaleDB first
####################################################
FROM $BUILD_FROM as timescale-pg12
ENV TIMESCALEDB_VERSION 2.6.0

# Patch the include-path. For some reason, it won't get found by the c-compiler while builing timescale.
ENV C_INCLUDE_PATH /usr/include/postgresql/12/server

ENV POSTGRES_VERSION 12.12-r0
RUN apk add --no-cache \
    postgresql12=${POSTGRES_VERSION} \
    postgresql12-dev=${POSTGRES_VERSION}

# --------------------------------------
# Build TimescaleDB
# --------------------------------------
# Enable this if you only want the OSS parts
#ENV OSS_ONLY -DAPACHE_ONLY=1
RUN set -ex \
    && apk add --no-cache --virtual .fetch-deps \
                ca-certificates \
                git \
                openssl \
                openssl-dev \
                tar \
    && mkdir -p /build/ \
    && git clone https://github.com/timescale/timescaledb /build/timescaledb \
    \
    && apk add --no-cache --virtual .build-deps \
                coreutils \
                dpkg-dev dpkg \
                gcc \
                krb5-dev \
                libc-dev \
                make \
                cmake \
                util-linux-dev \
    \
    # Build current version
    && cd /build/timescaledb && rm -fr build \
    && git checkout ${TIMESCALEDB_VERSION} \
    && ./bootstrap -DREGRESS_CHECKS=OFF -DTAP_CHECKS=OFF -DGENERATE_DOWNGRADE_SCRIPT=ON -DWARNINGS_AS_ERRORS=OFF -DPROJECT_INSTALL_METHOD="docker"${OSS_ONLY} \
    && cd build && make install \
    && cd ~ \
    \
    && if [ "${OSS_ONLY}" != "" ]; then rm -f $(pg_config --pkglibdir)/timescaledb-tsl-*.so; fi \
    && apk del .fetch-deps .build-deps \
    && rm -rf /build 

####################################
# Now build image and copy in tools
####################################
FROM husselhans/hassos-addon-timescaledb-${BUILD_ARCH}:1.1.6 as addon-pg12
FROM $BUILD_FROM

ENV TIMESCALEDB_VERSION 2.6.0

# --------------------------------------
# Add PostgreSql 14
# --------------------------------------
ENV POSTGRES_VERSION 14.5-r0
RUN apk add --no-cache \
    postgresql14-jit=${POSTGRES_VERSION} \
    postgresql14=${POSTGRES_VERSION} \
    postgresql14-dev=${POSTGRES_VERSION}

# --------------------------------------
# Add previous PostgreSql 12 (for pg_upgrade reasons)
# --------------------------------------
RUN apk add --no-cache \
    postgresql12

# Create it's run directory
RUN mkdir -p /run/postgresql \
	&& chown -R postgres:postgres /run/postgresql \
    && mkdir -p /run/postgresql/extensions \
	&& chown -R postgres:postgres /run/postgresql/extensions

# Copy over the tools
COPY --from=tools /go/bin/* /usr/local/bin/
COPY --from=toolkit-build / /usr/local/bin

# --------------------------------------
# Build new TimescaleDB
# --------------------------------------
# Enable this if you only want the OSS parts
#ENV OSS_ONLY -DAPACHE_ONLY=1

RUN set -ex \
    && apk add --no-cache --virtual .fetch-deps \
                ca-certificates \
                git \
                openssl \
                openssl-dev \
                tar \
    && mkdir -p /build/ \
    && git clone https://github.com/timescale/timescaledb /build/timescaledb \
    \
    && apk add --no-cache --virtual .build-deps \
                coreutils \
                dpkg-dev dpkg \
                gcc \
                krb5-dev \
                libc-dev \
                make \
                cmake \
                util-linux-dev \
    \
    # Build current version
    && cd /build/timescaledb && rm -fr build \
    && git checkout ${TIMESCALEDB_VERSION} \
    && ./bootstrap -DREGRESS_CHECKS=OFF -DTAP_CHECKS=OFF -DGENERATE_DOWNGRADE_SCRIPT=ON -DWARNINGS_AS_ERRORS=OFF -DPROJECT_INSTALL_METHOD="docker"${OSS_ONLY} \
    && cd build && make install \
    && cd ~ \
    \
    && if [ "${OSS_ONLY}" != "" ]; then rm -f $(pg_config --pkglibdir)/timescaledb-tsl-*.so; fi \
    && apk del .fetch-deps .build-deps \
    && rm -rf /build 

# --------------------------------------
# Build pgAgent
# --------------------------------------
ENV PGAGENT_VERSION REL-4_2_2
RUN set -ex \
    && apk add --no-cache --virtual .fetch-deps \
                ca-certificates \
                git \
                openssl \
                openssl-dev \
                tar \
    && mkdir -p /build/ \
    && git clone https://github.com/postgres/pgagent /build/pgagent \
    && apk add --no-cache --virtual .build-deps \
                coreutils \
                gcc \
                make \
                cmake \
				build-base \
				boost-dev \
				openldap-dev \
    && cd /build/pgagent \
    && git checkout ${PGAGENT_VERSION} \
    && cmake . \
    && make && make install \
    && cd ~ \
    && apk del .fetch-deps .build-deps \
    && rm -rf /build \
	&& apk add --no-cache \
				boost-libs

# --------------------------------------
# Add Postgis and openexr
# --------------------------------------
ENV POSTGIS_VERSION 3.2.3-r0
RUN set -ex \
    && apk add --no-cache \
    --repository http://dl-cdn.alpinelinux.org/alpine/edge/main/ \
    json-c poppler
RUN set -ex \
    && apk -UvX http://dl-cdn.alpinelinux.org/alpine/edge/main \
    add --no-cache \
    --repository http://dl-cdn.alpinelinux.org/alpine/edge/community/ \
	-u postgis=${POSTGIS_VERSION} openexr 

# --------------------------------------------------------------------------------------------
# For upgrade reasons, copy over timescale and postgis extentions from the previous PG12 addon
# --------------------------------------------------------------------------------------------
# Copy old pre-compiled versions of postgresql extentions from the old plugin (never trust old alpine images ;)
COPY --from=addon-pg12 /usr/lib/postgresql/* /usr/lib/postgresql12/
COPY --from=addon-pg12 /usr/lib/libproj* /usr/lib/

# Copy new timescale version build against postgres-12, and copy over to postgresql12 directory on the new addon.
# Yes! I know it looks like we're copying over from the wrong postgresql version (14), but it's just a build-glitch
# using the wrong foldername (postgresql14 instead of 12).. sigh..not my fault.
COPY --from=timescale-pg12 /usr/lib/postgresql14/* /usr/lib/postgresql12/
# Dumb fix for a docker build: https://github.com/moby/moby/issues/37965
RUN true 

COPY --from=timescale-pg12 /usr/share/postgresql14/extension/* /usr/share/postgresql12/extension/
# Dumb fix for a docker build: https://github.com/moby/moby/issues/37965
RUN true

# Fixup a packaging problem with an Alpine Package..
RUN cp /usr/share/postgresql14/extension/postgis--unpackaged--3.2.3.sql /usr/share/postgresql14/extension/postgis--ANY--3.2.3.sql

# --------------------------------------
# Finish image
# --------------------------------------

# Add nano for debugging
RUN set -ex \
    && apk add --no-cache \
    nano

# Make sure that S6 is not so hard on our service startup/shutdown
ENV \
    S6_SERVICES_GRACETIME=50000

# Copy data
COPY rootfs /

WORKDIR /

This was my unsuccessful attempt to build the toolbox:

# TimescaleDB toolkit build
FROM rust:slim-buster as toolkit-build
RUN cargo install --version '=0.4.5' --force cargo-pgx
RUN cargo pgx init --pg14 pg_config
RUN git clone https://github.com/timescale/timescaledb-toolkit && \
	cd timescaledb-toolkit/extension && \
	cargo pgx install --release && \
	cargo run --manifest-path ../tools/post-install/Cargo.toml -- pg_config

[soepkip (Martin)]

Hi Expaso, thanks for maintaining this Add-on!

Question: can I delete the standard Homeassistant db that is created when this Add on is installed?

I don’t use that db as of yet, but I am not sure if I would ‘break’ anything by removing the default db?

(I have added a DSMR Reader db)