Cosmos chain indexing in PostgreSQL

cosmos-psql.md

Reference

• PostgREST

Requirements

• cosmos-dump
• Docker

Instructions

1. Dump the chain data using the cosmos-dump tool

   # Dump block 1 to 10000 in JSON format in the ./data folder
   cosmos-dump extract some-grps-server:9090 -e 10000 -o ./data

2. Transform the extracted data to a TSV file

   # Transform the extracted data located in ./data and create `./tsv/transactions.tsv` and `./tsv/blocks.tsv`
   cosmos-dump export-tsv ./data ./tsv

3. Create a new (Docker) PostgreSQL database

   docker run -p 5432:5432 --name some-postgres -e POSTGRES_PASSWORD=foobar -d postgres
   

4. Create the init.sql file

   create schema api;
   
   CREATE TABLE api.blocks(
      id SERIAL PRIMARY KEY,
      data JSONB NOT NULL
   );
   
   CREATE TABLE api.transactions(
       id VARCHAR(64) PRIMARY KEY,
       data JSONB NOT NULL
   );
   
   create role web_anon nologin;
   grant usage on schema api to web_anon;
   grant select on api.blocks to web_anon;
   grant select on api.transactions to web_anon;

5. Initialize the database using the init.sql file

   psql -h localhost -d postgres -U postgres -f init.sql
   

6. Updaload the TSV data to the database

   psql -h localhost -d postgres -U postgres -c "\copy api.transactions from tsv/transactions.tsv csv quote e'\x01' delimiter e'\x09'"
   psql -h localhost -d postgres -U postgres -c "\copy api.blocks from tsv/blocks.tsv csv quote e'\x01' delimiter e'\x09'"
   

7. Retrieve the PostgreSQL docker container internal IP

   docker inspect some-postgres | jq -r '.[0].NetworkSettings.IPAddress'

8. Start the PostgREST (Docker) service on port 3000

   docker run --rm -p 3000:3000 \
     -e PGRST_DB_ANON_ROLE="web_anon" \
     -e PGRST_DB_SCHEMAS="api" \
     -e PGRST_DB_URI="postgres://postgres:foobar@[PSQL_DB_IP]/postgres" postgrest/postgrest

   where [PSQL_DB_IP] is the IP address of the PostgreSQL database container retrieved in the step above

You are now setup to execute REST queries!

REST

1. Return all transactions relating to the manifest1efd63aw40lxf3n4mhf7dzhjkr453axurm6rp3z address?

   http://localhost:3000/transactions?select=data->tx->body->messages&data->>tx=fts.manifest1efd63aw40lxf3n4mhf7dzhjkr453axurm6rp3z
   

2. Return the first 20 blocks

   http://localhost:3000/blocks?limit=20
   

3. Return the last 20 blocks

   http://localhost:3000/blocks?order=id.desc&limit=20