How to setup database locally

[megh-khaire]

Hello,

I'm facing some challenges while setting up the local database for the project. I've followed the setup instructions and successfully started the Docker container, but I'm encountering an issue where the error message says I need to upgrade the database to version 21.

From my understanding, I need to execute a series of SQL scripts to properly set up the database. Here's what I've gathered so far:

1. Run drop_and_create_tables.sql to create the necessary tables. (done)
2. Execute all the procedure.sql files located in the migration folder. (done)
3. Run create_indexes.sql, also located in the migration folder. (done)
4. Finally, apply all 27 patch files. (in progress)

However, I'm a bit confused about a few things:

1. Some of the patch files seem to have no SQL commands. Is that expected behavior?
2. The DB_MIGRATION.md mentions a completely different database that I have to import using Pentaho. What's the purpose of this database and the .sql that I'm executing for the migration.

Thanks!

[jimanderton]

Hi @megh-khaire,

Thank you for reaching out. You have run into some incomplete documentation that we need to clean up. The benmap database has grown quite large and it's not feasible to include all the data in the github repository. I will work with the team here to make a full database backup available and will share a download link with you soon.

[megh-khaire]

Hey @jimanderton

Thank you so much for replying and helping me with the data. It would be great if you could make the backup available.

In the meantime, are there any workarounds to get BenCloudServer working?

[jimanderton]

BenCloudServer won't work without many of the underlying input datasets. I have created a fresh backup of the v27 database that will work with the production code in the r0.5.0-release. You can download it from here: https://drive.google.com/file/d/14fnWKWIWlwOpp6NXmEsEgcfI5WCoPu1x/view?usp=sharing

Once downloaded and unzipped, follow these steps to create the new, blank database and load it from backup. (You might need to delete the previously created benmap database before following these steps.)

-- Create a blank database createdb -T template0 -h localhost -p 5432 -U benmap_system -W benmap

-- Load v27 database from backup psql -d benmap -h localhost -p 5432 -U postgres -W -f bencloud-dbv27-2024-10-29.sql

-- Run the following commands to ensure the benmap_system user can access the new database CREATE ROLE benmap_system WITH PASSWORD 'benmap' SUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN NOREPLICATION NOBYPASSRLS CONNECTION LIMIT -1; GRANT CONNECT ON DATABASE benmap TO benmap_system; GRANT USAGE ON SCHEMA data, grids, tiger, tiger_data, topology TO benmap_system; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA data, grids TO benmap_system; GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA data, grids TO benmap_system; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA data, grids TO benmap_system;

I hope this helps! Let us know how it goes.

[megh-khaire]

Thanks a lot @jimanderton I'll try this out and post an update by EOD today!

[megh-khaire]

Hey @jimanderton sorry for the long leave of absence. I'm not able to import the file into the database.

Here are few of the errors I get:

psql:/Users/megh/workspace/utech/BenCloudServer/bencloud-dbv27-2024-10-29-utf8.sql:1366501: error: invalid command \N
psql:/Users/megh/workspace/utech/BenCloudServer/bencloud-dbv27-2024-10-29-utf8.sql:1366502: error: invalid command \N
psql:/Users/megh/workspace/utech/BenCloudServer/bencloud-dbv27-2024-10-29-utf8.sql:1366503: error: invalid command \.
psql:/Users/megh/workspace/utech/BenCloudServer/bencloud-dbv27-2024-10-29-utf8.sql:1366512: ERROR:  syntax error at or near "1"
LINE 1: 1 1 2000 12 50 6 4 0 0 f 4 
        ^

It appears to be an encoding issue; I attempted converting to UTF-8, but no luck so far. Any suggestions?

[jimanderton]

Hi @megh-khaire I'm not sure what is causing that. The file I shared was dumped from my local PostgreSQL 11 database, which is UTF-8. It should load directly into a new, blank database using the commands I shared.

What process did you use to convert bencloud-dbv27-2024-10-29.sql to bencloud-dbv27-2024-10-29-utf8.sql?