PostgreSQL Database Backend

A PostgreSQL databse is utilized to prevent duplicate DSP transactions by creating, getting, and updating service events as needed. An external database can be set by ensuring the node_env variable in the config.toml file is set to production. The database settings may be specified with the url variable, e.g., postgres://user:[email protected]:5432/dbname.

config.toml:

[database]

# url syntax: postgres://user:[email protected]:5432/dbname, only necessary for production
url = "postgres://user:[email protected]:5432/dbname" 

# production (uses above database_url for database)
node_env = "production"

How to install postgres on Ubuntu

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee  /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt -y install postgresql-12 postgresql-client-12

Setup database and user

sudo su - postgres
psql
CREATE DATABASE dsp;
CREATE USER dsp WITH ENCRYPTED PASSWORD 'Put-Some-Strong-Password-Here';
GRANT ALL PRIVILEGES ON DATABASE dsp to dsp;

Create vRAM IPFS Tables

CREATE TABLE IF NOT EXISTS blocks (key TEXT NOT NULL UNIQUE, data BYTEA);
CREATE INDEX IF NOT EXISTS blocks_key_text_pattern_ops_idx ON blocks (key text_pattern_ops);

Install Go and prereqs

GO_VERSION=1.19.4
wget https://go.dev/dl/go${GO_VERSION}.linux-amd64.tar.gz
rm -rf /usr/local/go && sudo tar -C /usr/local -xzf go${GO_VERSION}.linux-amd64.tar.gz
export PATH=$PATH:/usr/local/go/bin
rm ./go${GO_VERSION}.linux-amd64.tar.gz
rm -rf ./go
go install github.com/alanshaw/ipfs-ds-postgres@latest

Note if you are setting up the PostgreSQL server for vRAM where other users were previously using that service, you will need to see the replay contract section to replay all staked contracts to populate the DB with their data. Otherwise the db will not be able to fetch that info.

Run PostgreSQL server for IPFS

Systemd service

Run which go to see where your install location is, if it is not /usr/local/go/bin/go, update the ExecStart below with your path.

sudo -E su - -p
cat <<EOF > /lib/systemd/system/ipfs-ds-postgres.service
[Unit]
Description=IPFS PostgreSQL alternative backend
After=network.target
[Service]
User=root
# update working directory
# echo $(readlink -f `which setup-dsp` | xargs dirname)/../ipfs-ds-postgres/services/ipfs-ds-postgres
WorkingDirectory=/root/.nvm/versions/node/v16.19.0/lib/node_modules/@liquidapps/dsp/zeus_boxes/ipfs-ds-postgres/services/ipfs-ds-postgres
Environment="DATABASE_URL=postgres://user:password@domain/db"
# determine go install path with below command, if different than below, update
# which go
ExecStart=/usr/local/go/bin/go run main.go
KillMode=process
Restart=on-failure
[Install]
WantedBy=multi-user.target
EOF

systemctl start ipfs-ds-postgres
systemctl enable ipfs-ds-postgres
exit
sleep 3
systemctl status ipfs-ds-postgres

For how to wipe the database, see here