Improve logs when importing postgres archive

[mijoco-btc]

Describe the bug
Database in incorrect state after restoring stacks-blockchain-api event data from postgres dump file.

To Reproduce
Steps to reproduce the behavior:

1. download testnet-stacks-blockchain-api-7.8.2-20240322.gz
2. pg_restore --username postgres --verbose --jobs 4 --dbname stacks_blockchain_api /tmp/stacks-blockchain-api-pg-15-7.8.2-20240322.dump
3. inspect the resulting data

Expected behavior

The restore takes several minutes and finishes without error and the size of the database (testnet) is 15G. However the tables contain no data. (Note I had to create the database for pg_restore to run however the dump file attempts to recreate the db);

Console log

CREATE DATABASE stacks_blockchain_api WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8' OWNER = 'postgres';

\l
stacks_blockchain_api | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
SELECT pg_size_pretty( pg_database_size('stacks_blockchain_api') );
 pg_size_pretty
----------------
 15 GB

select count(*) as numb_blocks from blocks;
 numb_blocks
------
    1

select count(*) as numb_p4_events from pox4_events;
 numb_p4_events
------
    0

Environment:

• OS: Ubuntu 23.10 mantic
• Stacks-blockchain-api: 7.8.2 (testnet)
• Postgres: 15 (7.8.2)
• psql (PostgreSQL) 15.6 (Ubuntu 15.6-1.pgdg23.10+1)
• pg_restore (PostgreSQL) 15.6 (Ubuntu 15.6-1.pgdg23.10+1)

** Additional Info **

Possibly related to #1908

[csgui]

Hello @radicleart !

Probably you are using a wrong db schema. Or even not connected to the stacks-blockchain-api when querying the db.

Please do the following, inside psql command line:

\c stacks-blockchain-api;

and run the queries again using stacks_blockchain_api as the schema. For instance:

select count (*) from stacks_blockchain_api.blocks;

Thanks.

[mijoco-btc]

So there is no database \c stacks-blockchain-api ?

but i can now see the blocks with the underscores..

select count (*) from stacks_blockchain_api.blocks;
152881

I currently have .env with

PG_APPLICATION_NAME=stacks-blockchain-api
PG_DATABASE=stacks_blockchain_api

should they be the same ?

[mijoco-btc]

Thanks for the pointer.

I must have the API misconfigured somehow as the api reports zero blocks from my end point and logs the below - I'll clear the stacks data and restart everything from a clean build - will close this later!

{"level":"error","time":"2024-03-25T13:13:13.097Z","pid":155820,"hostname":"leibniz.brightblock.org","name":"stacks-blockchain-api","component":"core-api","err":{"type":"Error","message":"DB does not contain a parent block at height 151830 with index_hash 0xbb3259af0798f792b47425873a50185e72b1fcf6dedd45816498dc7a4b422ac9","stack":"Error: DB does not contain a parent block at height 151830 with index_hash 0xbb3259af0798f792b47425873a50185e72b1fcf6dedd45816498dc7a4b422ac9\n    at PgWriteStore.handleReorg (/mnt/bitcoin-testnet/stacks-testnet/stacks-blockchain-api-7.8.2/src/datastore/pg-write-store.ts:2865:15)\n    at processTicksAndRejections (node:internal/process/task_queues:95:5)\n    at /mnt/bitcoin-testnet/stacks-testnet/stacks-blockchain-api-7.8.2/src/datastore/pg-write-store.ts:187:7\n    at scope (/mnt/bitcoin-testnet/stacks-testnet/stacks-blockchain-api-7.8.2/node_modules/@hirosystems/api-toolkit/node_modules/postgres/cjs/src/index.js:259:18)\n    at Function.begin (/mnt/bitcoin-testnet/stacks-testnet/stacks-blockchain-api-7.8.2/node_modules/@hirosystems/api-toolkit/node_modules/postgres/cjs/src/index.js:242:14)\n    at PgWriteStore.update (/mnt/bitcoin-testnet/stacks-testnet/stacks-blockchain-api-7.8.2/src/datastore/pg-write-store.ts:185:5)\n    at handleBlockMessage (/mnt/bitcoin-testnet/stacks-testnet/stacks-blockchain-api-7.8.2/src/event-stream/event-server.ts:358:3)\n    at observeEvent (/mnt/bitcoin-testnet/stacks-testnet/stacks-blockchain-api-7.8.2/src/event-stream/event-server.ts:759:7)\n    at run (/mnt/bitcoin-testnet/stacks-testnet/stacks-blockchain-api-7.8.2/node_modules/p-queue/dist/index.js:163:29)"},"msg":"error processing core-node /new_block"}

[mijoco-btc]

but i can now see the blocks with the underscores..

select count (*) from stacks_blockchain_api.blocks;
152881

Should the following be the same - I dropped and recreated the db before starting ? Am wondering if my postgres is misconfigured (i'm new to postgres db).

postgres=# \c stacks_blockchain_api;
You are now connected to database "stacks_blockchain_api" as user "postgres".
stacks_blockchain_api=# select count (*) from stacks_blockchain_api.blocks;
 count
--------
 152881
(1 row)

stacks_blockchain_api=# select count (*) from blocks;
 count
-------
     1
(1 row)

[mijoco-btc]

Closing as user error.

[mijoco-btc]

Reopening to document the route cause of this was not having one or both of the below environment vars. Without them the API creates a blank/unnamed schema reads the first block it receives into this schema and then gets stuck on the error DB does not contain a parent block at height 151693 when passed a block from the stacks node.

PG_SCHEMA=stacks_blockchain_api
PG_APPLICATION_NAME=stacks_blockchain_api

The archive doc implies to start the stacks node before the api node which seems to cause the missing parent block error. The stacks node data postgres data should both be unpacked / loaded but the API should be started before the node.