Accessing Database

[cmoi]

I am trying to access my database via a GUI (Psequel).

I forwarded the port setting from my container as such:

app.yml:

expose:
  <standard definitions>
  - "15432:5432" # PostgreSQL

Also changed my password as such:

./launcher enter app
su - postgres
psql
ALTER ROLE postgres WITH PASSWORD '<your password>';

And I am unable to access the database. Any suggestions?

[EricGT]

@cmoi

Any suggestions?

If you only need a static snap shot of the database then from https://<site>/admin/backups download a backup. It should be a *.tar.gz file and when uncompressed will be a *.sql file. Create a PostgreSQL database on another machine, which could even be your laptop, and then import the *.sql file.

Now you should be able to access the data all you want with any means that can connect to a PostgreSQL database.

I use the above but access the Discourse database in PostgreSQL via ODBC.

HTH

[cmoi]

ok good idea.
Actually I figured it out. It runs on port 5432 also in the container.
it should read:
expose:

• “5432:5432” # PostgreSQL

thanks

[Kimberly_Haniff]

Hi Everyone,

I am able to access my postgres db via pgadmin using the following steps:

• Remove the expose port code my your app.yml and rebuild the app.
• Go to your server management portal (i.e Digital Ocean, AWS etc). Create a firewall rule which opens port 5432.
• Using pgadmin SSH tab: sign into your server using the server address and credentials.

Let me know if this works for you.

Kind Regards,
Kimberly

[gassim (Al-Gassim Sharaf Addin)]

@EricGT never thought of it! Thank you!!

[gassim (Al-Gassim Sharaf Addin)]

Hey!

I wanted to double check with you please. When I export the dump.sql to a postgresql database, I end up with empty tables. It’s not clear why. Here are the steps that I follow after I download the backup file:

1. Open pgAdmin
2. Create a New Database
3. Open Query Tool
4. Use the ‘Open’ in the Query Tool and select the dump.sql file
5. Execute the Dump Script

It says that everything was successful but when I ‘view data’ in the tables, they are empty.

Additionally, it’s probably how the instance is managed but it seems that the users table is not included as well, but I need that table to know who did what.

Did I miss anything here? Thanks!

[nat (Natalie T)]

What’s the size of the dump.sql? It should be sizeable (a few MB at least). Can you take a look inside the file? e.g.

$ zgrep -i "CREATE TABLE public.users" dump.sql.gz
#output should be
> CREATE TABLE public.users (

If you don’t see this, the dump looks wrong.

Also, if you share your steps on how you are exporting the dump or paste your console output here, we can understand your issue better.

[gassim (Al-Gassim Sharaf Addin)]

Thank you so much!

@nat

What’s the size of the dump.sql? It should be sizeable (a few MB at least). Can you take a look inside the file? e.g.

6.34 GB !

@nat

$ zgrep -i "CREATE TABLE public.users" dump.sql.gz
#output should be
> CREATE TABLE public.users (

@nat

Also, if you share your steps on how you are exporting the dump or paste your console output here, we can understand your issue better.

To download the dump, I followed the steps suggested above by @EricGT:

@EricGT

https:///admin/backups download a backup. It should be a *.tar.gz file and when uncompressed will be a *.sql file.

After that I followed these steps:

@gassim

• Open pgAdmin
• Create a New Database
• Open Query Tool
• Use the ‘Open’ in the Query Tool and select the dump.sql file
• Execute the Dump Script

And then when I want to view the data, it shows:

[EricGT]

@gassim

It says that everything was successful but when I ‘view data’ in the tables, they are empty.

Just redid this to check.

1. Using the admin page https://<site>/admin/backup requested a download and followed the steps, there were several steps which included verification through an email and downloading a file.
2. The file downloaded was a gz file e.g. abc-2025-01-23-095947-v20250122131007.sql.gz. On Windows uncompressed the file using 7-zip which created a directory with the same name less the .gz on the end.

C:\Users\Groot\Downloads>dir *.sql.gz
01/23/2025  05:04 AM       407,213,170 abc-2025-01-23-095947-v20250122131007.sql.gz

C:\Users\Groot\Downloads>dir *.sql

01/23/2025  05:04 AM    <DIR>          abc-2025-01-23-095947-v20250122131007.sql

3. Using a Windows command prompt opened to the directory with the sql file to verify the sql file exist

C:\Users\Groot\Downloads\abc-2025-01-23-095947-v20250122131007.sql>dir

01/23/2025  05:04 AM     1,572,346,154 abc-2025-01-23-095947-v20250122131007.sql
               1 File(s)  1,572,346,154 bytes

4. Using same Windows command prompt used type command to list the start of the sql file.

type <file> /a | more

C:\Users\Groot\Downloads\abc-2025-01-23-095947-v20250122131007.sql>type "abc-2025-01-23-095947-v20250122131007.sql" /a | more

abc-2025-01-23-095947-v20250122131007.sql


--
-- PostgreSQL database dump
--

-- Dumped from database version 15.8 (Debian 15.8-1.pgdg110+1)
-- Dumped by pg_dump version 15.10 (Debian 15.10-1.pgdg120+1)

-- Started on 2025-01-23 09:59:47 UTC

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';

Hopefully this gets you to the point that you can use the SQL file with PGAdmin to import the data.

---

NB

When I posted about this ~5 years ago, the file downloaded file type was tar.gz it is now sql.gz. The only difference is that now one less uncompressession step is needed.

[gassim (Al-Gassim Sharaf Addin)]

Hi @EricGT

Thank you so much for your help! I followed all the same steps (with one extra since my file still has tar.gz). I reached the same result with the sql file:

--
-- PostgreSQL database dump
--
.........
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';

However, the issue is that when I use PgAdmin to get the data, all the tables are empty and the Users table is just missing.

[EricGT]

@gassim

PgAdmin

Sorry I can not help with PgAdmin. I do not use PgAdmin and would have to install it to try. Installing PgAdmin is not a step I care to take.

The one time I accessed an exported Discourse database was to use install PostgreSQL, odbc-postgresql and use iusql as noted here.

[gassim (Al-Gassim Sharaf Addin)]

Thank you so much for all your help! I really appreciate it. It turns out that I was trying to use the latest postgresql version whereas the dump.sql was from a previous version. I discovered this while trying to follow the guide you used. Thank you!