# Tendermint Postgres Indexer
Tendermint provides an option for node operators to index transactions and events (opens new window). Transactions and events can either be indexed in a kv
indexer or psql
indexer.
This document provides instructions for setting up a psql
indexer.
# Dependencies
- Docker
>=20.10
- Postgres
>=v14.5
- Tendermint
>=v0.34.15
# Repository Setup
The first step is to create a new repository that will be the home of the indexer.
mkdir indexer
cd indexer
git init
# Schema Setup
The next step will be creating the schema for the database. We will be using the tendermint schema available here (opens new window).
curl https://raw.githubusercontent.com/tendermint/tendermint/v0.34.15/state/indexer/sink/psql/schema.sql > schema.sql
The above schema creates three VIEWS
:
event_attributes
block_events
tx_events
# Docker Setup
The next step will be creating a Dockerfile
in the root of the repository with the following content:
FROM postgres:14.5-alpine
ENV POSTGRES_USER postgres
ENV POSTGRES_PASSWORD postgres
ENV POSTGRES_DB regen_events
COPY schema.sql /docker-entrypoint-initdb.d/
EXPOSE 5432
CMD ["postgres"]
# Postgres Setup
Now that we have the schema and dockerfile setup, the next step is building the docker image and starting the postgres server.
Build the docker image:
docker build -t "regen-events-indexer" .
Start the postgres server:
docker run -it -d -p 5432:5432 -e -v ./pg-volume:/var/lib/postgresql/data regen-events-indexer:latest
# Regen Setup
The next step is to test the indexer using a local test network.
The following documentation will help you get started:
# Postgres indexer configuration
To enable the indexer, go to [tx_index]
section in config.toml
file. You can find the config.toml
file in ~/.regen/config
.
Set indexer
to psql
:
indexer = "psql"
Set psql-conn
to the location of the postgres database:
psql-conn = "postgresql://postgres:postgres@127.0.0.1:5432/regen_events?sslmode=disable"
Start (or restart) the local testnet:
regen start
Perform some transactions on the local testnet.
# Performing Queries
WARNING
Searching is not enabled for the psql
indexer type via Tendermint's RPC and therefore the following queries will fail:
TxByEvents
RPC callTxByHash
RPC calltx-by-events
REST endpointtx-by-hash
REST endpoint
Log in to postgres container:
docker exec -tiu postgres <container-id> psql
Change database to regen_events
:
\c regen_events
List all database tables:
\dt
The above command should return the following output:
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | attributes | table | postgres
public | blocks | table | postgres
public | events | table | postgres
public | tx_results | table | postgres
(4 rows)
The following query will get all events from the ecocredit
module:
SELECT *
FROM event_attributes
WHERE type LIKE 'regen.ecocredit.%';
The following query will get all events from the data
module:
SELECT *
FROM event_attributes
WHERE type LIKE 'regen.data.%';
The following query will get all block events:
SELECT blocks.rowid as block_id, height, chain_id, type, key, composite_key, value
FROM blocks JOIN event_attributes ON (blocks.rowid = event_attributes.block_id)
WHERE event_attributes.tx_id IS NULL;
The following query will get all events from modules using legacy events (e.g. the bank
module):
SELECT DISTINCT tx_id
FROM attributes JOIN events ON events.rowid=attributes.event_id
WHERE attributes.value LIKE '%bank%';