Indexer DB: error connecting to postgres

1

Hi, have you solved the problem? My bug is that index cannot connect to postgresql database: **error":“connecting to postgres: failed to connect to host=127.0.0.1 user=postgres database=algorand: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)”,“level”:“error”,“msg”:“could not init db, connecting to postgres: failed to connect to host=127.0.0.1 user=postgres database=algorand: dial error (dial tcp 127.0.0.1:5432: connect: connection refused)”,“time”:“2023-06-07T02:18:05+08:00”}

2

Can you provide the full command you use to start the indexer?

Inside the command, you should have something like
-P "host=127.0.01 user=... password=... dbname=... sslmode=..." or equivalent.

You can test whether this parameter is valid by running:

psql "host=127.0.01 user=... password=... dbname=... sslmode=..."

If you have an error there, it means that your parameters is invalid, postgres is not started, or something else prevents connection to postgres (e.g., permission issues).
I would recommend using Google / Stackoverflow to guide you in a solution to the problem.

If the above command works but the indexer does not start (and you ran both on the same computer), there may be an issue specific to the indexer, which we can help debug and fix.

3

Thanks, I finally solved the database connection problem, and the console is like this:

image
image842×294 4.57 KB

Is it normal( but I did’t see anything added in the database)?
Should I keep it running?

4

This looks good. Now, you need to wait until the indexer reaches the current round (that you can see e.g., on https://algoexplorer.io)

5

thank you! but the processing speed is so slow, 12 hours for 2.4 million blocks on my computer. By this speed, it will take a week to get the whole chain.

May I ask what content the indexer is processing? because the postgresql database is empty after processing so many blocks, and the indexer data directory is only 28MB.

6

The first blocks of the blockchain have very few transactions, so they’re not contributing much to the size of the indexer DB (contrary to algod, the indexer does not keep the certificate in each block, and the certificate is what takes the most of space for low number of transactions).
Following blocks have many more transactions and you will see an increase in space use.
You will also see a decrease in speed…

Your speed seems a little low, but not abnormally low I think.
The main culprits are:

  • disk too slow
  • algod node latency is too high

As a comparison, I’ve started re-indexing MainNet on May 31st (7 days ago), and I’m at round 17,430,870. Algod and postgres is on the same machine and I’m indexing both TestNet and MainNet, with the same Postgres 13 DB. Empty blocks (0 txn) usually take less than 20ms for indexing, closer to 15ms (which is faster than what you have).