I am trying to access my database via a GUI (Psequel). I forwarded the port setting from my container as such: app.yml: expose: - "15432:5432" # PostgreSQL Also changed my password as such: ./launcher enter app su - postgres psql ALTER ROLE postgres WITH PASSWORD ' '; And I am unable to access the database. Any suggestions?

[image] cmoi: Any suggestions? If you only need a static snap shot of the database then from https:// /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

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

@EricGT never thought of it! Thank you!! :slight_smile:

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: 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 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!

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.

Thank you so much! :slight_smile: [image] 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. 6.34 GB ! [image] Natalie T: $ zgrep -i "CREATE TABLE public.users" dump.sql.gz #output should be > CREATE TABLE public.users ( [The image shows a line of SQL code on a terminal, attempting to use a file named "dump.sql" to express a CREATE TABLE statement. (Captioned by AI)] [image] Natalie T: 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: [image] 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: [image] Al-Gassim Sharaf Addin: 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: [The image shows a PostgreSQL pgAdmin application interface displaying "no rows" under the "admin_notices" table after executing a query for the first 100 rows. (Captioned by AI)]

[image] Al-Gassim Sharaf Addin: It says that everything was successful but when I ‘view data’ in the tables, they are empty. Just redid this to check. Using the admin page https:// /admin/backup requested a download and followed the steps, there were several steps which included verification through an email and downloading a file. 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 abc-2025-01-23-095947-v20250122131007.sql 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 Using same Windows command prompt used type command to list the start of the sql file. type /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.

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.

[image] Al-Gassim Sharaf Addin: 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.

Accessing Database

Product Support Self-hosting

cmoi (cmoi ) June 7, 2020, 11:03pm 3

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

Topic		Replies	Views
Export all data of all posts Product Support	3	4044	September 20, 2021
How to access the discourse database? Self-hosting	13	17562	March 10, 2022
Discourse restore with pg_dump Self-hosting	2	1085	October 22, 2018
Browse the Postgres database Feature	5	1222	January 20, 2022
Exporting data from version 0.9.4 installed through bitnami Marketplace unsupported-install , backups	4	2322	April 6, 2016

Accessing Database

Related topics