# 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](/liquidapps-documentation/dapp-service-providers/dsps/dsp-maintenance/replay-contract.md) 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](/liquidapps-documentation/dapp-service-providers/dsps/setup-dsp/postgresql-database-backend/how-to-wipe-local-database.md)
{% endhint %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.liquidapps.io/liquidapps-documentation/dapp-service-providers/dsps/setup-dsp/postgresql-database-backend.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.