Using PostgreSQL in a grain

[Troy Farrell]

In yesterday's community meeting, I learned that using PostgreSQL inside a Sandstorm grain is still considered a dark art, so I decided to share the secret.

As an aside, for entertainment purposes, I asked ChatGPT to help me make PostgreSQL run inside a Sandstorm grain.  It effectively told me that it was impossible.  AI isn't magic.  It's mostly an amazing search engine at this point.  If the knowledge isn't public, it probably doesn't can't figure it out… yet.

Here is a demonstration of Django and PostgreSQL as a Sandstorm application:

https://caravanserai.sandcats.io/shared/9XzAhG0cPgj-nlRMhirTNSWZ4XgBLvbX56aT7fPVA3S/troyjfarrell/django-postgresql-in-sandstorm

Questions are welcome.  There are a few loose ends that need to be cleaned up in this code.  D♥S is there, but is commented out because I haven't updated it to work with the current version of Django.  Django Debug Toolbar wasn't working for another reason, which I don't recall at this moment.

Troy

[Dan Krol]

Looking now. Start on packaging Hockeypuck, and it actually uses postgres! How fitting.

BTW Troy did you forget to set a branch or something? I had to `git checkout 26dda81b26` after cloning to see anything.

--
You received this message because you are subscribed to the Google Groups "Sandstorm Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sandstorm-de...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sandstorm-dev/6752dd6c-68ef-46cf-9a93-ab2f605100a1n%40googlegroups.com.

[Dan Krol]

I got the Django welcome page. I'm doing it on Qubes so I had to change the `vagrant` username to `user`. I know that's the vagrant spk username during build so that make sense. But is there any specific reason you use it for the postgres user (i.e. while running)?

Also fwiw could you add a license? Since we'll be copying your code :-D

[Jacob Weisz]

I am glad I'm not the only one who had difficulty cloning the repository. I thought I was just being too much of a git noob.

--

  Jacob Weisz

  goo...@jacobweisz.com

To view this discussion on the web visit https://groups.google.com/d/msgid/sandstorm-dev/CAAWRcS8uUzLqhapdpvxDar8M6sffAJXB4r_RQ7aZkLyp3aaVDg%40mail.gmail.com.

[Troy Farrell]

> BTW Troy did you forget to set a branch or something? I had to `git checkout 26dda81b26` after cloning to see anything.

When I created the repository, I cloned the empty repository but pushed to a different branch, so a git clone was giving you an empty commit.  I think it's fixed now.

> I got the Django welcome page. I'm doing it on Qubes so I had to change the `vagrant` username to `user`.
> I know that's the vagrant spk username during build so that make sense. But is there any specific reason
> you use it for the postgres user (i.e. while running)?

I don't think that the user name matters while running.  I probably didn't think too much about it.  The main thing will be that the name needs to be the same everywhere, so calling it 'sandstorm' or 'user' is probably fine.

> Also fwiw could you add a license? Since we'll be copying your code :-D

See README.md.

[Dan Krol]

Troy, silly question, but are you able to restart your grain and have it come back up? It breaks for me if I do that. "database system was interrupted" shows up in the logs when I load the grain the second time.

I noticed this when packaging Hockeypuck (a go app), but I went back and confirmed it on the Django app, so I think it's fundamentally postgres/Sandstorm related. Seems like it's not gracefully shutting down.

Details that may accidentally be relevant:

* I'm doing this with spk on my Qubes machine. I renamed the vagrant user to "user".

* When I went back and confirmed it, I didn't do the python build process you have, I just used the one on my system to save time.

--
You received this message because you are subscribed to the Google Groups "Sandstorm Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sandstorm-de...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sandstorm-dev/30178462-150d-41d6-8464-b897bbb75755n%40googlegroups.com.

[Troy Farrell]

Starting and restarting the grain works for me.  Please share your log.  Here's what I see in my log:

** SANDSTORM SUPERVISOR: Starting up grain. Sandbox type: userns

waiting for PostgreSQL to be available at /var/run/postgresql/.s.PGSQL.5432
waiting for PostgreSQL to be available at /var/run/postgresql/.s.PGSQL.5432
2024-02-01 19:25:57.522 GMT [17] LOG:  starting PostgreSQL 16.1 (Debian 16.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2024-02-01 19:25:57.524 GMT [17] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-02-01 19:25:57.533 GMT [22] LOG:  database system was interrupted; last known up at 2024-02-01 19:23:40 GMT
found PostgreSQL socket at /var/run/postgresql/.s.PGSQL.5432
2024-02-01 19:25:58.052 GMT [22] LOG:  database system was not properly shut down; automatic recovery in progress
2024-02-01 19:25:58.056 GMT [22] LOG:  redo starts at 0/19641A8
2024-02-01 19:25:58.056 GMT [22] LOG:  invalid record length at 0/19641E0: expected at least 24, got 0
2024-02-01 19:25:58.056 GMT [22] LOG:  redo done at 0/19641A8 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2024-02-01 19:25:58.062 GMT [20] LOG:  checkpoint starting: end-of-recovery immediate wait
2024-02-01 19:25:58.075 GMT [20] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.004 s, sync=0.001 s, total=0.015 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB; lsn=0/19641E0, redo lsn=0/19641E0
2024-02-01 19:25:58.089 GMT [17] LOG:  database system is ready to accept connections
2024-02-01 19:25:58.110 GMT [27] ERROR:  database "sandstorm-app" already exists
2024-02-01 19:25:58.110 GMT [27] STATEMENT:  CREATE DATABASE "sandstorm-app" OWNER vagrant ENCODING 'UTF-8' TEMPLATE template0 LOCALE 'en_US.UTF-8';
createdb: error: database creation failed: ERROR:  database "sandstorm-app" already exists
Applying database migrations...
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions
Running migrations:
  No migrations to apply.
Removing stale content types...
Removing expired user sessions...
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
INFO:     Started server process [35]
INFO:     Waiting for application startup.
INFO:     ASGI 'lifespan' protocol appears unsupported.
INFO:     Application startup complete.
INFO:     Uvicorn running on unix socket /var/run/uvicorn.sock (Press CTRL+C to quit)
found Uvicorn socket at /var/run/uvicorn.sock
INFO:      - "GET / HTTP/1.0" 200 OK
** SANDSTORM SUPERVISOR: Grain shutdown requested.
** SANDSTORM SUPERVISOR: Starting up grain. Sandbox type: userns
waiting for PostgreSQL to be available at /var/run/postgresql/.s.PGSQL.5432
waiting for PostgreSQL to be available at /var/run/postgresql/.s.PGSQL.5432
2024-04-10 14:04:45.486 GMT [17] LOG:  starting PostgreSQL 16.1 (Debian 16.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2024-04-10 14:04:45.488 GMT [17] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-04-10 14:04:45.502 GMT [22] LOG:  database system was interrupted; last known up at 2024-02-01 19:25:58 GMT
found PostgreSQL socket at /var/run/postgresql/.s.PGSQL.5432
2024-04-10 14:04:46.074 GMT [24] FATAL:  the database system is starting up
2024-04-10 14:04:46.088 GMT [25] FATAL:  the database system is starting up
createdb: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  the database system is starting up
Applying database migrations...
2024-04-10 14:04:46.106 GMT [22] LOG:  database system was not properly shut down; automatic recovery in progress
2024-04-10 14:04:46.114 GMT [22] LOG:  redo starts at 0/1964258
2024-04-10 14:04:46.114 GMT [22] LOG:  invalid record length at 0/1964290: expected at least 24, got 0
2024-04-10 14:04:46.114 GMT [22] LOG:  redo done at 0/1964258 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2024-04-10 14:04:46.133 GMT [20] LOG:  checkpoint starting: end-of-recovery immediate wait
2024-04-10 14:04:46.150 GMT [20] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.005 s, sync=0.001 s, total=0.021 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB; lsn=0/1964290, redo lsn=0/1964290
2024-04-10 14:04:46.158 GMT [17] LOG:  database system is ready to accept connections
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions
Running migrations:
  No migrations to apply.
Removing stale content types...
Removing expired user sessions...
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
Waiting for Uvicorn to be available at /var/run/uvicorn.sock
INFO:     Started server process [36]
INFO:     Waiting for application startup.
INFO:     ASGI 'lifespan' protocol appears unsupported.
INFO:     Application startup complete.
INFO:     Uvicorn running on unix socket /var/run/uvicorn.sock (Press CTRL+C to quit)
found Uvicorn socket at /var/run/uvicorn.sock
INFO:      - "GET / HTTP/1.0" 200 OK
INFO:      - "GET / HTTP/1.0" 200 OK

[Dan Krol]

I tried creating an spk and loading it onto my Sandstorm instance on a Linode. That one I was able to reload a grain several times without problem.

I try the spk on my sandstorm dev server in my dev Qube, and it breaks.

I was going to send it to you but I'm guessing it won't break for you. So I wonder what's going on there. I'll play with memory to see if that somehow makes the difference.

On Wed, Apr 10, 2024 at 4:11 PM Dan Krol <orbl...@gmail.com> wrote:

Okay, I ran it again. Note that since last time, I've restarted the Qubes VM I'm developing in and reinstalled Sandstorm. Maybe that's relevant here.

This time it seems I was able to reload the grain a few times successfully. It finally broke, though it seemed to spontaneously recover. Then I tried reloading again and it broke and stayed broke. That's the log I've provided.

--

You received this message because you are subscribed to the Google Groups "Sandstorm Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sandstorm-de...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sandstorm-dev/e45b2fa7-d1ec-4afe-ab60-bdc460cb9019n%40googlegroups.com.

[Dan Krol]

Okay, I ran it again. Note that since last time, I've restarted the Qubes VM I'm developing in and reinstalled Sandstorm. Maybe that's relevant here.

This time it seems I was able to reload the grain a few times successfully. It finally broke, though it seemed to spontaneously recover. Then I tried reloading again and it broke and stayed broke. That's the log I've provided.

--

You received this message because you are subscribed to the Google Groups "Sandstorm Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sandstorm-de...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sandstorm-dev/e45b2fa7-d1ec-4afe-ab60-bdc460cb9019n%40googlegroups.com.