# 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"
```

### [How to install postgres on Ubuntu](https://computingforgeeks.com/install-postgresql-12-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

```bash
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
```

{% hint style="warning" %}
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](https://docs.liquidapps.io/liquidapps-documentation/dapp-service-providers/dsps/dsp-maintenance/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.
{% endhint %}

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

<pre class="language-bash"><code class="lang-bash">sudo -E su - -p
cat &#x3C;&#x3C;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
<strong>[Install]
</strong>WantedBy=multi-user.target
EOF

systemctl start ipfs-ds-postgres
systemctl enable ipfs-ds-postgres
exit
sleep 3
systemctl status ipfs-ds-postgres
</code></pre>

{% hint style="info" %}
For how to wipe the database, see [here](https://docs.liquidapps.io/liquidapps-documentation/dapp-service-providers/dsps/setup-dsp/postgresql-database-backend/how-to-wipe-local-database)
{% endhint %}