testing-postgresql-persistent-data.md

Final work is here and a working SDL manifest

What's below on this page is outdated:

---

1. Init the DB with postgres:adminpass creds

docker network create pgnet1
docker volume create pgvol1
docker run -d --name db1 --hostname db1 --network pgnet1 -e POSTGRES_PASSWORD=adminpass -v pgvol1:/var/lib/postgresql/data postgres:14

2. Create a new DB "newdb", with newdb_user:userpass creds

docker run --rm -ti --network pgnet1 -e PGPASSWORD=adminpass -e PGUSER=postgres -e PGHOST=db1 -u postgres --entrypoint psql postgres:14

CREATE USER newdb_user;
CREATE DATABASE newdb;
GRANT ALL PRIVILEGES ON DATABASE newdb TO newdb_user;
ALTER USER newdb_user WITH PASSWORD 'userpass';

\l+
\q

3. Connect to the "newdb" with "newdb_user" and create a table called "company"

docker run --rm -ti --network pgnet1 -e PGPASSWORD=userpass -e PGUSER=newdb_user -e PGDATABASE=newdb -e PGHOST=db1 -u postgres --entrypoint psql postgres:14

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

\dt+
\q

4. Restart the db1

docker logs db1
docker restart db1
docker logs db1

5. Check the connection and the table again

docker run --rm -ti --network pgnet1 -e PGPASSWORD=userpass -e PGUSER=newdb_user -e PGDATABASE=newdb -e PGHOST=db1 -u postgres --entrypoint psql postgres:14

\dt+
\q

6. Tear everything down

docker rm -f db1
docker volume rm pgvol1
docker network rm pgnet1

Did 3 tests, only test3 worked in Akash.

test1. mount over /var/lib/postgresql
https://asciinema.org/a/qHPPEpcC24s8sctUsnI6lwq4M

test2. PGDATA=/var/lib/postgresql/data + mount over /var/lib/postgresql
https://asciinema.org/a/3Pf0DIkN3u0mo5neR9meJLIXR

test3. PGDATA=/var/lib/postgresql/data/pgdata1 + mount over /var/lib/postgresql/data
https://asciinema.org/a/OMjkatLwPDR61FbKwrdW0jMv4

Working Postgresql deployment variant for Akash Network

---
version: "2.0"

services:
  db:
    image: postgres:14
    env:
      - POSTGRES_PASSWORD=adminpass
      - PGDATA=/var/lib/postgresql/data/pgdata1
    params:
      storage:
        pgdata:
          mount: /var/lib/postgresql/data
    expose:
      # postgresql port
      - port: 5432
        as: 5432
        to:
          - global: true

profiles:
  compute:
    db:
      resources:
        cpu:
          units: 1.0
        memory:
          size: 1Gi
        storage:
          - size: 1Gi
          - name: pgdata
            size: 10Gi
            attributes:
              persistent: true
              class: beta3
  placement:
    akash:
      attributes:
        host: akash
      signedBy:
        anyOf:
          - "akash1365yvmc4s7awdyj3n2sav7xfx76adc6dnmlx63"
      pricing:
        db:
          denom: uakt
          amount: 10000

deployment:
  db:
    akash:
      profile: db
      count: 1

Working Postgresql deployment variant for Akash Network (ver2)

The issue initdb: error: directory "/var/lib/postgresql/data" exists but is not empty arise because when mounting a persistent storage directory in Akash (ceph -> rados -> nbd device) - leaves lost+found directory since it's a new FS mountpoint.

The workaround is to rmdir /var/lib/postgresql/data/lost+found before starting the docker-entrypoint.sh by overriding the entrypoint via the SDL's command & args as in the following working example:

---
version: "2.0"

services:
  db:
    image: postgres:14
    command:
      - "sh"
      - "-c"
    args:
      - "rmdir -v /var/lib/postgresql/data/lost+found >/dev/null 2>&1;
         exec /usr/local/bin/docker-entrypoint.sh postgres"
    env:
      - POSTGRES_PASSWORD=adminpass
    params:
      storage:
        pgdata:
          mount: /var/lib/postgresql/data
    expose:
      # postgresql port
      - port: 5432
        as: 5432
        to:
          - global: true

profiles:
  compute:
    db:
      resources:
        cpu:
          units: 1.0
        memory:
          size: 1Gi
        storage:
          - size: 1Gi
          - name: pgdata
            size: 10Gi
            attributes:
              persistent: true
              class: beta3
  placement:
    akash:
      attributes:
        host: akash
      signedBy:
        anyOf:
          - "akash1365yvmc4s7awdyj3n2sav7xfx76adc6dnmlx63"
      pricing:
        db:
          denom: uakt
          amount: 10000

deployment:
  db:
    akash:
      profile: db
      count: 1

Final work is here and a working SDL manifest