How to access the discourse database?

installation
1

Hi all,

This is probably a stupid question but how can I access the database?
Is there something equivalent to phpadmin (or another GUI) that I can use to view and edit the database of discourse? If not, then what is the best way to access/view/edit it?

2 Likes
2

Install the Data Explorer Plugin and have at it.

If you want to do postgres command line stuff, youā€™ll need to enter the container to do that using the Docker enter command.

5 Likes
4

Hi, Docker enter, but whatā€™s next?

I tried psql but got psql: FATAL: role "root" does not exist

5

postgres runs under the postgres user, per:

https://github.com/discourse/discourse_docker/blob/master/templates/postgres.template.yml#L40

You want to sudo postgres psql discourse to hit the right user and databaseā€¦

4 Likes
6

I see , thank you! @sam

7

In case anyone else is confused the actual command you need to run inside the container is:

sudo -u postgres psql discourse

i.e. ā€˜Run psql discourse as the postgres user.ā€™

24 Likes
10

sudo -u postgres psql discourse

This lists the 156 tables:

discourse=# \dt

But basic queries return nothing:

discourse=# select * from users limit 1
discourse-#

This is a working site, so Iā€™m sure there is data. What am I doing wrong?

1 Like
11

same problem as mark above

12
  1. Use the data-explorer plugin if at all possible - it comes with some bonus safety checks and a schema explorer.
  2. You need to terminate commands in the psql console with a ; semicolon.
7 Likes
13

Can you please explain this process, I am a newbie on containers and would love to start playing with my postgres DB instead of having the container be a big black box to me :slight_smile:

1 Like
14

Here ya go, just for you @Divert :

Assuming you are in OOTB single container mode in the standard supported setup:

cd /var/discourse
./launcher enter app
su discourse
psql discourse

Now, if all was running properly, you are in the DB as the discourse user connected to the discourse DB ready to issue PostgreSQL and SQL commands.

Then, if you want to list all the tables in the DB, you can:

\dt

If you want to list all tables with the word ā€œsettingā€, then you can:

\dt *setting*

If you find a table of interest, say the users table, you can examine the table structure, like so:

\d users

If you wish (for example, and not an efficient example at that) to examine the first 10 users (ordered by id descending, only 10); you can just:

SELECT * FROM users ORDER BY id desc LIMIT 10;

Anyway, you get the idea. If you are good at SQL, itā€™s a piece-of-cake.

Everyone here will recommend you play around on a staging setup (where if you break things you can just rebuild and start over) and have fun!

Donā€™t play around on a production system with real users, until you are very comfortable with the tech (maybe have mastered the basics at least) and always make full backups first :slight_smile:

Have fun @Divert

Note: This kind of ā€œplaying aroundā€ or ā€œself learningā€ in the DB is not the topic of support at meta, so. you are basically on your own. Enjoy!

16 Likes
15

@neounix Thank you so much for this answer, you did go the extra mile and I really appreciate it. :smile:

5 Likes
16

Is there some way to connect to Discourse DB (docker self-hosted install) using some SQL Client (like PSequel for mac)?

17

Aside from using Docker, which Iā€™m unfamiliar with (and donā€™t want to corrupt the DB) are there SQL commands I could run through the Data Explorer plugin to show the names of the databases I can access in Discourse?