Where is the user content stored in the database?

(kodab) #1

Hi guys,

I’m currently exploring the postgres discourse database for a migration to discourse.

Now I know that postgres holds the content of a discourse board, right? But I can’t find it.

If I create a new topic and call it “loremipsumlorem”, than I can’t find that string back in the postgres database. Where is it? Where are the categories stored? And the users?

Creating a basic import script, what do I need
(Jeff Wong) #2

Are you able to connect to the postgres instance at all? If this is your issue, keep in mind that under the recommended install, Discourse is hosted in a docker container, in which the postgres port isn’t exposed by default. You will have to proxy your way into the container first in order to connect.

(kodab) #3

Hey Jeff, yes I can connect to the database with HeidiSQ and view the content. :slight_smile:

I just can’t find the data that I’m searching for like the place where the topics are stored for example or the users… :frowning:

(Jeff Wong) #4

Ah, in that case, you mentioned this was for importing to discourse, correct? Where are you migrating from? There are a bunch of import scripts written out of the box that use the Discourse API in order to import existing data - that’s the recommended way to interact with discourse’s database.

Exploring and editing the database manually is generally discouraged as this can easily corrupt your install. Check out the existing importers here to see if any match what you’re trying to do.

(kodab) #5

I’m moving from a self created forum with a custom database structure. There is no import script for the forum that I use. :frowning:

I’ve looked into the importscripts and found out that the vanilla import scripts uses a .csv file. I can export my forum to a .csv file BUT… my .csv file is very clean and structured, the vanilla .csv file is not so it requires rly a lot of work to make my .csv file so messy that the vanilla importer takes it.

The other import scripts are not rly an option I think…

I now I wonder if I can migrate manualy by importing into the database but than I need to know where I have to import what. :slight_smile:

(Jeff Wong) #6

Rather than try and change the way your CSV is structured, maybe look into rewriting the vanilla import parser so that you can read your CSVs? That way you’re stacking the work with your own strengths (understanding your CSV format) rather than trying to dabble in the unknown (spelunking in Discourse’s DB schema).

If you’re intent on importing directly to postgres, I don’t have a good working knowledge of the schema to answer your question directly, sorry. I’m just throwing some alternative suggestions your way to solve your problem.

(kodab) #7

That is a great suggestion, I didnt thought about that so far. Thanks!

I have looked at the import script and, while I am a programmer, it’s not rlealy clear for me how the csv file is transformed by the vanilla importer script. It might be more clear when I dig deeper into it tho.

Thanks for your suggestion, I think that might your suvgestion might be a route I can take to migrate.

(Matt Palmer) #8

You really, really don’t want to put data into the database tables directly. Stick with using the ActiveRecord models, which take care of a whole pile of “bookkeeping” behind the scenes for you. That’s what (nearly) all of the existing importers do.

(kodab) #9

Thanks for the reaction Matt. I’m not sure what ActiveRecord models are but I think I’m going to use the vanilla_mysql.rb script and modify my database to match the script database. I have vanilla setup to reference to that database column names, thanks!

(Jay Pfaffman) #10

I wrote several importers before I knew what active record was. :slight_smile: