How can I directly edit Discourse database from a GUI?

I am looking for some sort of user friendly utility that can run SQL queries that can globally tidy up my current database containing posts and users imported from two different older forums - for example deleting topics, or adding certain tags to all posts with certain string in title, deleting users or changing access where user profiles don’t meet certain criteria etc.

Does anything like phpMyAdmin exist that can edit a Discourse administrators sql backup? Or even work with a live Discourse instance?.

I can see there is a plugin allowing queries within Discourse, but this seems not to allow changes to data?

4 Likes

Downloading a backup (without uploads) and restoring to a local instance PostgreSQL, that you can query with pgAdmin3 is your best bet.

6 Likes

Yes, the Data Explorer plug-in does not allow changes to be made. But I’d suggest starting with that anyway. You can run queries to identify if you do have problems and then run queries to select the records that need to be changed. And you will be protected from accidentally wrecking your database.

I’m assuming that you’re running in production already. so I’d be very wary of modifying the database without some sort of approval from the Discousrse team.

Once you are familiar with the Discourse database and you know the size of the problem, then you could consider your options for making the changes. You might find you can do it all in Discourse. Or you might have so few changes that the command line is fine.

1 Like

Thanks Falco!

I am a serious noob at using anything not involving pointing and clicking.

I have however managed to set up a local instance of Discourse (under windows 10) by following the official guides (without understanding much of anything), so presume somewhere there will be guidance for installing pgAdmin3 to the same place?

Silly question, but is this offline Discourse instance the thing I should be restoring the exported sql file to (or is there some other way to ‘restore’ a Discourse database backup file to PostgreSQL? ).
And how can I query it once restored? i.e. where and how do I point pgAdmin3 to the database in a running Discourse instance? where does a running discourse database physically reside? Does the fact that Discourse instance is running lock the database in some way?
Can’t seem to see any files obviously corresponding to a database amongst the files in my local server ~ /var/discourse directory

1 Like

Thanks Remah

I am self hosting Discourse so have no access to the Discourse team
Am setting up and running forum for a small community, purely on voluntary basis (i.e no budget), and am bringing in data from both MyBB and Yahoo groups forums - the latter of which has introduced for example a bunch of old administrative auto generated email notifications as topics in Discourse, which are pretty irrelevant in this context

It is likely I will need to test and make global changes in a sporadic incremental / ongoing basis, as I discover new issues - hence the desire to minimize learning curve and cli based anything!

3 Likes

I saw your domain name and checked you out first time so I somewhat understand your situation. I’m in Wellington, by the way, setting up and actively using a couple of private forums for charities.

Given your limited time and experience I’d suggest starting with the simplest and easiest to use tools first and only moving onto the next level if you are forced. There’s a high cost to mastering anything more than the first level which is just using Discourse itself. It can be incredibly powerful when used as designed.

  1. The Discourse GUI with the features that you will need to master anyway.

    • Get up to speed with what you need to use in Discourse. You might find that you can do 99% of what you need without going any further. So you might not go any further.

    • Prioritise actual visible problems.
      For example, your old email notifications might not be a big issue. If they are irrelevant then they will, as designed, drop out of any prominence in Discourse as more relevant topics are created. Simply getting your forum going properly is more important than solving every data error.
      So, how many of these automated email notifications have been converted to topics?

  2. The Date Explorer plug-in will be useful for you so it is the next step to a better understanding of the database and to identify what might need to be done. Later it will help you with reporting but it is not absolutely necessary.

    • Not being able to make changes is a safety net because a few years ago there were a lot of users wrecking their data with precipitate updates.

    • The queries you generate to identify the problem records are only a step away from SQL to modify the database.

  3. The final option is likely to be the command-line and scripts next to modify your database.

    • I’d rather run the risk of bad data on display than risk damaging the database by manually modifying it. That can create a time bomb because some data corruption might not be identified in a timely manner.

    • Using Data Explorer will give you query results which are real data examples and quantifiable numbers of records. You are more likely to get a correct answer from the team and other experts if they can understand your data and what you are trying to achieve. They can then advise you about the easiest and safest way to update the database.

    • Much of what you need is likely to be in topics already because other sites strike the same sort of problems.So you’ll just be copying someone else’s hard won knowledge.

6 Likes

This. Just don’t modify the database directly. You will regret it someday.

6 Likes

Thanks Remah

All sounds like sensible advice.
I might be able to crowd source a partial solution if I can convince some users to manually scan for and mark topics for deletion.

Right now the site on the domain is essentially a clean slate placeholder while a freelancer is fine tuning the import process from old forums (tweaking the import script for MyBB in particular, so that hopefully the custom user profile fields I had set up there will import along with users and their posts. Also hopefully parsing the MyCode in some MyBB posts properly (right now the formatting codes are visible)

I wasted a week fruitlessly attempting this import myself but just could not achieve a dev environment with all prerequisites in place that would allow me to do so from either a Windows 10 instance of Ubuntu or a DigitalOcean based instance, based on the official guide for use of the MyBB import script.

After painfully thrashing around, solving one error message after the next, I finally struck an absolute brick wall either way when it came to getting the SQL database to be accessible when executing the final ruby on rails command that would initiate the import.

Linux and Ruby both seem to be written by sadists, for masochists, both being astonishingly fragile and arcane. In such an environment, the odds of catastrophic problems when manipulating databases do seem high!

2 Likes

You have my sympathy.

Stick to being forum admin. :+1:

User friendliness has always been lacking in that environment. I think that the command line is king even more than it was 20 years ago.

But that’s why I like Discourse. The team make an effort to make core Discourse significantly friendlier. Unfortunately, migrating is a high-tech option.

2 Likes

It’s almost certainly better to do that from the Rails console.

1 Like

I guess that might depend on your benchmark for ‘better’?
In my case, being a noob, the lowest possible learning curve and minimal complexity of access for someone who does not normally have a development environment set up, and who knows nothing about Ruby, (or indeed Linux) would be high priorities. Might be different if I had some other reason to (and time to) get up to speed with these things … In a perfect world, there would be some sort of GUI local windows app that could interrogate my Digital Ocean hosted Discourse setup directly…

If you decide to not make the changes directly to the database, you might find some of the commands described in this topic helpful: Administrative Bulk Operations. For example, it gives details about how to tag topics from the rails console. The most important thing is to take a backup of your site’s database before running any commands.

6 Likes

My benchmark for “Better” is “much less likely to leave your database in a broken state and completely broken.” Since you’re a “noob” you don’t know which tables need to be updated when you do . . . something.

How ever you do it, make sure you make frequent backups.

1 Like

A valid point - I will try hard to find other ways in the first instance.
My ignorance is a large risk with either scenario, but is it true to say that conducting any database changes via Ruby will be safer than attempting the same changes using pg Admin4?

The risk of creating not immediately noticed damage has been alluded to for example - is there anything about one approach vs the other that can influence that risk?

In the back of my head, should I ever decide to risk it (after suitable backups taken) I envisaged a copy of pgAdmin4 running in my Digital Ocean droplet, that I could access directly via url in browser, rather than via cli console windows, and thus eliminating a couple of layers of complexity (am assuming here that this is even possible)

Pretty much. Ruby does a bunch of magic stuff to make sure that the Right Thing happens. For example, if you destroy (delete) something from a model, it knows when and what other stuff should be deleted. There’s plenty of “safe” stuff you can do with raw SQL, but I pretty much always do it in Rails if it’s possible.

4 Likes

Ah, that is good to know - thanks!

3 Likes

That looks potentially pretty useful - thanks!

2 Likes