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:pass@example.com:5432/dbname.

config.toml:

[database]

# url syntax: postgres://user:pass@example.com:5432/dbname, only necessary for production
url = "postgres://user:pass@example.com:5432/dbname" 

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

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

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

Last updated

Was this helpful?