Skip to content

PostgreSQL⚓︎

Starting point to learn about this open source relational database through Ditto's setup

Resource: PostgreSQL.org

What you need⚓︎

terminal

VPS

Move to the directory where your database is installed. These examples are part of the process of learning about Ditto, a way to setup a nostr community in a VPS. However, they may be useful for postgresql database use in general.

The database directory for ditto is /opt/ditto

navigate to /opt/ditto, if not there already
cd /opt/ditto

create⚓︎

create user⚓︎

sudo -u postgres createuser -P yourusername
enter ditto user password
Enter password for new role:
Retype:

create database⚓︎

sudo -u postgres createdb yourdatabase -O yourusername

update⚓︎

update your database password⚓︎

sudo -u yourusername psql yourdatabase
that will log you in to your database
psql (version (Ubuntu)
Type "help" for help.

yourdatabase=>
change password by adding it between the single quotation marks
ALTER USER yourusername WITH PASSWORD 'yournewdatabasepassword';
message from terminal
ALTER ROLE
close database
\q

access database⚓︎

the standard name for postgresql databases is postgres

access your database, it will request a password
sudo -u yourusername psql yourdatabase
a window opens where you can enter postgresql commands
psql ((Ubuntu version))
Type "help" for help.

yourdatabase=#

config file⚓︎

find config file, you need superuser access
SHOW config_file;
message from terminal
 config_file               
-----------------------------------------
 /etc/file_route.conf
(1 row)

postgres=#
quit
\q

make changes to config file⚓︎

I followed this process to try to fix error in ditto VPS setup. It may also be useful for changing access port numbers.

source: CURSO VPS - Instalando PostgresQL en Ubuntu y configurar el acceso remoto

copy file route from your terminal found in the step before
sudo nano /etc/file_route.conf
before
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
after: added non-commented line for listen_addresses with * instead of localhost
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
listen_addresses = '*'         
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)

save changes Ctrl + O, then enter exit config window with Ctrl + X

Always restart after making changes to config files.

restart for changes to be applied
sudo systemctl restart postgresql

Since I used this process to troubleshoot ditto, I then checked if ditto was running:

systemctl status ditto

not running, same error

Modifying the PostgreSQL Client Authentication Configuration File

Route copied from config file

again from /opt/ditto NOT as dittouser
sudo nano /etc/...pg_hba.conf
PostgreSQL Client Authentication Configuration File window
# PostgreSQL Client Authentication Configuration File
# ===================================================
...
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

change IPv4 localhost IP to 0.0.0.0/0
# IPv4 local connections:
host    all             all             0.0.0.0/0            md5
save changes Ctrl + O, then enter exit config window with Ctrl + X

restart for changes to be applied
sudo systemctl restart postgresql

check if ditto is running

systemctl status ditto
same error
● ditto.service - Ditto
     Loaded: loaded (/etc/systemd/system/ditto.service; enabled; vendor preset: enabled)
     Active: failed (Result: exit-code) since 
    Process: 64502 ExecStart=/usr/local/bin/deno task start (code=exited, status=1/FAILURE)
   Main PID: 64502 (code=exited, status=1/FAILURE)

same error

log in as dittodbuser⚓︎

log in as dittouser to dittodb

navigate to /opt/ditto, if not there already
cd /opt/ditto
log in as ditto, if not already
sudo su dittodbuser
enter your server user password
[sudo] password for yourserverusername:

sudo -u dittodbuser
message from terminal
usage: sudo -h | -K | -k | -V
usage: sudo -v [-AknS] [-g group] [-h host] [-p prompt] [-u user]
 ...

psql
message from terminal
psql (Ubuntu version)
Type "help" for help.

ditto=> help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
dittodbuser=>
list databases
\l
message from terminal
 List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 dittodb     | dittodbuser    | ...   | ...  | ... | 
 postgres  | postgres | ...   | ...   | ... | 
 template0 | postgres | ...   | ...   | ... | ...

(3 rows)

You can have multiple databases but need to make sure you point to the one you want in your .env file.

quit
\q

Getting a lot of non-parsed characters and a few mentions of fatal errors, but nothing specific that would point to a possible fix. I'll review later in more detail.

delete database⚓︎

you must the owner to delete
DROP DATABASE databasename;