Allow switching user databases

[dmos62]

Tasks

Preview Give feedback

1. Make sure backend supports using multiple user databases at the same time centerofci/mathesar#2007
   ready type: meta work: backend +3
   
2. Design for switching between multiple databases centerofci/mathesar#172
   ready restricted: design team type: enhancement work: design +4
   
3. Implement frontend for switching between different databases centerofci/mathesar#173
   needs: unblocking type: enhancement work: frontend +3
   

Related:

• Support Multiple Database Servers, supply db and creds via params #2716
• Backup and restore data from Mathesar #1815

[dmos62]

This is not needed anymore for this cycle.

[pavish]

@dmos62

There are two existing issues (one for design, one for implementation) covering switching between databases:

• Design for switching between multiple databases #172
• Implement frontend for switching between different databases #173

This issue seems like a duplicate and I'd like to close it. Is there anything additional you'd like to handle in this issue that's not covered by the above?

[dmos62]

@pavish maybe let's keep this meta issue to track the whole of the feature? I replaced the design and frontend tasks with the issues you linked, and the backend issue is already linked.

[pavish]

@dmos62 Sure, that works, thanks.

[kgodey]

@pavish I think we should consider prioritizing this for 0.1.2 given #2716.

[pavish]

@kgodey I agree. It would also help with simplifying installation. I'll add it to 0.1.2 to prioritize it but I reckon it will take considerable time and testing. We can discuss this while discussing the plan for 0.1.2.

[dmos62]

@pavish maybe we should only have the owner of the feature assigned to this meta-ticket, and the people implementing the constinuent parts assigned to the respective sub-issues? At the moment all the people doing the sub-issues (except Mukesh) are assigned to this feature meta ticket.

[pavish]

@dmos62 We've so far been assigning everyone involved in a feature in the associated meta tickets, because sometimes we do not have sub-issues for all items in the meta ticket. I'm fine with it either way, if we only want to assign meta tickets to owners.

We don't have a well defined pattern for this yet. Perhaps you could discuss this with @rajatvijay.

[kgodey]

I think it makes sense for the owner to be assigned, since they're responsible for the meta-ticket, driving the work and creating sub-tickets as needed.

[dmos62]

Made sure that backend supports multiple dbs in dev mode (completed #2007). Should be ready for frontend implementation (when designs are ready).

@pavish The steps for setting up another db locally in dev mode, by cloning the default db:

1. Open a psql repl: sudo docker exec -it mathesar_db psql postgresql://mathesar:mathesar@mathesar_db:5432/mathesar -s
2. In the psql repl, kill connections to the default db: SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'mathesar' AND pid <> pg_backend_pid();
3. In the psql repl, clone the default db: CREATE DATABASE mathesar2 WITH TEMPLATE mathesar OWNER mathesar;
4. Add the right entry into your .env's MATHESAR_DATABASES config entry by running this sed command: sed -i '/MATHESAR_DATABASES=(/ s/$/,(mathesar_tables2|postgresql:\/\/mathesar:mathesar@mathesar_db:5432\/mathesar2)/' .env
5. Pick up the new configuration by restarting your docker compose setup: sudo docker compose --profile dev up

You should end up with a clone of your preexisting/default user database named mathesar2 in Postgres and mathesar_tables2 in Mathesar. Use GET http://localhost:8000/api/db/v0/databases/ to check.

[pavish]

Closing meta ticket since all items are handled.