Postgres Error When Resetting Database

[SWeav02]

I'm setting up to run a large number of calculations and wanted to create a new database on DigitalOcean to keep everything seperate from our other cloud databases. @scott-materials made the new database and connection pool and I changed the NAME and PORT tags in the my_env-database.yaml file. When I went to run simmate database reset I recieved this error:

InternalError_: template database "template1" has a collation version mismatch
DETAIL:  The template database was created using collation version 2.34, but the operating system provides version 2.36.
HINT:  Rebuild all objects in the template database that use the default collation and run ALTER DATABASE template1 
REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

Any ideas for how to fix this? I didn't have much success when I googled the error.

[jacksund]

made the new database and connection pool and I changed the NAME and PORT tags in the my_env-database.yaml file

Only call simmate database reset using a direct database connection and not a pgbouncer pool connection. Calling this through the pool breaks the connection because you just deleted the database that you're connected to. To be extra safe, I'd suggest not making the database pool until after you reset your database.

I haven't seen your error before, but this is my first guess as to what went wrong. Let me know if this helps or I can do some more digging

[SWeav02]

I still got the same error when we switched to a direct connection, but I had the name wrong for the connection. My bad! However, that wasn't the only issue. The name for the connection is simmate-sam, but I had simmatesam with no dash. Now when I run simmate database reset it I get a syntax error near the "-". I tried putting it in double quotes (like "simmate-sam"), but still get the same error. Here's the last bit of the error:

│ /home/WarrenLab/miniconda3/envs/sams_badelf_env/lib/python3.10/site-packages/simmate/database/ut │
│ ilities.py:118 in reset_database                                                                 │
│                                                                                                  │
│   115 │   │                                                                                      │
│   116 │   │   # Build out database extensions and tables                                         │
│   117 │   │   db_name = DATABASES["default"]["NAME"]                                             │
│ ❱ 118 │   │   cursor.execute(f"DROP DATABASE IF EXISTS {db_name};")                              │
│   119 │   │   # BUG: if others are connected I could add 'WITH (FORCE)' above.                   │
│   120 │   │   # For now, I don't use this but should consider adding it for convenience.         │
│   121 │   │   # I think this is buggy with older versions of postgres (like RDkit),              │
│                                                                                                  │
│ ╭─────────────────────────────────────────── locals ───────────────────────────────────────────╮ │
│ │     apps_to_migrate = [                                                                      │ │
│ │                       │   'core_components',                                                 │ │
│ │                       │   'data_explorer',                                                   │ │
│ │                       │   'workflows',                                                       │ │
│ │                       │   'engine',                                                          │ │
│ │                       │   'admin',                                                           │ │
│ │                       │   'auth',                                                            │ │
│ │                       │   'contenttypes',                                                    │ │
│ │                       │   'sessions',                                                        │ │
│ │                       │   'messages',                                                        │ │
│ │                       │   'staticfiles',                                                     │ │
│ │                       │   ... +12                                                            │ │
│ │                       ]                                                                      │ │
│ │          connection = <connection object at 0x7f1afc6cf600; dsn: 'user=doadmin password=xxx  │ │
│ │                       dbname=postgres                                                        │ │
│ │                       host=db-postgresql-nyc1-simmate-do-user-9931465-0.b.db.ondigitalocean… │ │
│ │                       port=25060', closed: 0>                                                │ │
│ │              cursor = <cursor object at 0x7f1afc8467a0; closed: 0>                           │ │
│ │             db_name = 'simmate-sam'                                                          │ │
│ │ maintenance_db_name = 'postgres'                                                             │ │
│ │            psycopg2 = <module 'psycopg2' from                                                │ │
│ │                       '/home/WarrenLab/miniconda3/envs/sams_badelf_env/lib/python3.10/site-… │ │
│ │        use_prebuilt = None                                                                   │ │
│ │      using_postgres = True                                                                   │ │
│ │        using_sqlite = False                                                                  │ │
│ ╰──────────────────────────────────────────────────────────────────────────────────────────────╯ │
╰──────────────────────────────────────────────────────────────────────────────────────────────────╯
SyntaxError: syntax error at or near "-"
LINE 1: DROP DATABASE IF EXISTS simmate-sam;

Any ideas? I get the impression Postgres may not like dashes.

[jacksund]

the hyphen is a bug with 0.13.2 that I've fixed already and it's just waiting on a new release. If you use the dev install of simmate it should work with hyphens, but you can just delete them for now.

simmate/docs/change_log.md

Line 90 in 54cb125

- fix bug where hyphens aren't allowed in the database name

Meanwhile the missing hyphen name that you used and a direct database connection should have worked, so that means something is up with the database scott set up for you. Did he do anything funny to set things up? Maybe messing with the templates, copying in postgres files, or using a custom docker image?

When I get the chance, I'll go through the full guide for setting up a database and see if I can reproduce this error. But for now, it looks like an issue with how postgres is set up and not a simmate bug.

[SWeav02]

Still not sure what was going on with the missing hyphen name, but I switched to a dev install and things seem to be working fine. I kept the name with the hyphen since I don't have direct access to the DigitalOcean server to change things around and didn't want to wait until tomorrow when Scott has time. I don't think Scott did anything funny with setting things ups, just hit "create new database" and "create connection pool". I'm downloading third-party data now and if anything else funny happens related to that error I'll put it here.

[jacksund]

So I guess it's just broken in the last release (v0.13.2). I'm going to open a new "milestone" on github so we can get 0.14.0 out and fix all these postgres issues

[SWeav02]

Quick update:
Third-party data downloaded without too much issue. I've submitted the jobs I'm planning to run and started the workers. Everything seems to be running smoothly so far!