Accessing Database

Installation
1

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?
1 Like
2

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

2 Likes
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

6 Likes
4

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

5

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

1 Like
6

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!

7

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.

1 Like
8

Thank you so much! :slight_smile:

6.34 GB !

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)

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

After that I followed these steps:

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)
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)959×965 134 KB

9

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
  1. 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
  1. 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.

1 Like