db-sync-node.db-sync stuck on delete

[HT-Moh]

OS Your OS: Ubuntu 20.04.5 LTS

Versions The db-sync version (eg cardano-db-sync --version): PostgreSQL version: 15 cardano-db-sync 13.0.5 - linux-x86_64 - ghc-8.10 git revision 8ad98d48e0068f3768d48e18fdcbe254037cba3b

Build/Install Method The method you use to build or install cardano-db-sync: https://cardano-community.github.io/guild-operators/Build/dbsync/

Run method The method you used to run cardano-db-sync (eg Nix/Docker/systemd/none): systemd Additional context

I restored successfully the sync db from snapshot scripts/postgresql-setup.sh --restore-snapshot then I run

cd $CNODE_HOME/scripts
export PGPASSFILE=$CNODE_HOME/priv/.pgpass
./dbsync.sh

One more thing I change the guild-db to another driver

#PGPASSFILE="${CNODE_HOME}/priv/.pgpass"                    # PGPass file containing connection information for the postgres instance
#DBSYNCBIN="${HOME}/.cabal/bin/cardano-db-sync-extended"    # Path for cardano-db-sync-extended binary, assumed to be available in $PATH
DBSYNC_STATE_DIR="/hd2/cnode/guild-db/backlog/ledger-state"     # Folder where DBSync instance will dump ledger-state files
DBSYNC_SCHEMA_DIR="/hd2/cnode/guild-db/schema"          # Path to DBSync repository's schema folder
#DBSYNC_CONFIG="${CNODE_HOME}/files/dbsync.json"            # Config file for dbsync instance
#SYSTEMD_PGNAME="postgresql"                                # Name for postgres instance, if changed from default

I changed the log severity to Debug /opt/cardano/cnode/files/dbsync.json

Problem Report

The issue it seems that the dbsync is stuck on delete query {"app":[],"at":"2022-10-20T09:23:42.66Z","data":{"kind":"Recv","msg":{"agency":"ServerAgency TokNext TokCanAwait","kind":"MsgRollForward"}},"env":":00000","host":"","loc":null,"msg":"","ns":["db-sync-node.ChainSync"],"pid":"483986","sev":"Debug","thread":"69"} {"app":[],"at":"2022-10-20T09:23:42.67Z","data":{"event":"Required subscriptions started","kind":"SubscriptionTrace"},"env":":00000","host":"","loc":null,"msg":"","ns":["db-sync-node.Subscription"],"pid":"483986","sev":"Notice","thread":"56"} {"app":[],"at":"2022-10-20T09:23:55.03Z","data":{"kind":"LogMessage","message":"Deleting 7875828 blocks up to slot 74044785"},"env":":00000","host":"","loc":null,"msg":"","ns":["db-sync-node"],"pid":"483986","sev":"Info","thread":"67"} {"app":[],"at":"2022-10-20T09:23:55.03Z","data":{"kind":"LogMessage","message":"SELECT \"block\".\"id\"\nFROM \"block\"\nWHERE \"block\".\"previous_id\" IS NULL\n; []"},"env":":00000","host":"","loc":null,"msg":"","ns":["db-sync-node.db-sync"],"pid":"483986","sev":"Debug","thread":"67"} {"app":[],"at":"2022-10-20T09:23:55.03Z","data":{"kind":"LogMessage","message":"SELECT \"id\" FROM \"block\" WHERE (\"previous_id\"=?) AND (\"epoch_no\" IS NOT NULL); [PersistInt64 1]"},"env":":00000","host":"","loc":null,"msg":"","ns":["db-sync-node.db-sync"],"pid":"483986","sev":"Debug","thread":"67"} {"app":[],"at":"2022-10-20T09:23:55.03Z","data":{"kind":"LogMessage","message":"DELETE FROM \"block\" WHERE \"id\"=? ; [PersistInt64 2]"},"env":":00000","host":"","loc":null,"msg":"","ns":["db-sync-node.db-sync"],"pid":"483986","sev":"Debug","thread":"67"}

[kderme]

There shouldn't be any rollback when you restore from a snapshot, especially such a big one. Was the snapshot restoration completed succesfully? Did you use the correct ledger state path, database name?

[HT-Moh]

The snapshot restoration was completed successfully, I can query the db The path of the ledger state is another partition /hd2/cnode/guild-db/backlog/ledger-state, I think I did the necessary changes on dbsync.sh

#PGPASSFILE="${CNODE_HOME}/priv/.pgpass"                    # PGPass file containing connection information for the postgres instance
#DBSYNCBIN="${HOME}/.cabal/bin/cardano-db-sync-extended"    # Path for cardano-db-sync-extended binary, assumed to be available in $PATH
DBSYNC_STATE_DIR="/hd2/cnode/guild-db/backlog/ledger-state"     # Folder where DBSync instance will dump ledger-state files
DBSYNC_SCHEMA_DIR="/hd2/cnode/guild-db/schema"          # Path to DBSync repository's schema folder
#DBSYNC_CONFIG="${CNODE_HOME}/files/dbsync.json"            # Config file for dbsync instance
#SYSTEMD_PGNAME="postgresql"                                # Name for postgres instance, if changed from default

however the folder /hd2/cnode/guild-db/backlog/ledger-state is empty.

[kderme]

however the folder /hd2/cnode/guild-db/backlog/ledger-state is empty.

It should have the ledger state restored from the snapshot

[rdlrt]

One more thing I change the guild-db to another driver

Did you also accordingly change the directory on restore-snapshot command?

[HT-Moh]

Ok found the ledger.state /hd2/cnode/guild-db/ledger-state instead of /hd2/cnode/guild-db/backlog/ledger-state I updated the dbsync.sh DBSYNC_STATE_DIR="/hd2/cnode/guild-db/ledger-state" # Folder where DBSync instance will dump ledger-state files but it seems it still not working

I set the logs to debug normally I should see all the queries

yes the command that I executed.

scripts/postgresql-setup.sh --restore-snapshot /hd2/tmp/db-sync-snapshot-schema-13-block-7875828-x86_64.tgz /hd2/cnode/guild-db/ledger-state

],"pid":"590769","sev":"Debug","thread":"61"}
{"app":[],"at":"2022-10-20T13:15:10.38Z","data":{"bearer":{"local":{"path":""},"remote":{"path":"/opt/cardano/cnode/sockets/node0.socket"}},"event":"Bearer Receive Header End: ts: 0xb94c5aa7 (MiniProtocolNum 5) ResponderDir len 12288","kind":"MuxTrace"},"env":"<unknown>:00000","host":"","loc":null,"msg":"","ns":["db-sync-node.Mux"],"pid":"590769","sev":"Debug","thread":"61"}
{"app":[],"at":"2022-10-20T13:15:10.38Z","data":{"bearer":{"local":{"path":""},"remote":{"path":"/opt/cardano/cnode/sockets/node0.socket"}},"event":"Bearer Receive Start: length 12288","kind":"MuxTrace"},"env":"<unknown>:00000","host":"","loc":null,"msg":"","ns":["db-sync-node.Mux"],"pid":"590769","sev":"Debug","thread":"61"}
{"app":[],"at":"2022-10-20T13:15:10.38Z","data":{"bearer":{"local":{"path":""},"remote":{"path":"/opt/cardano/cnode/sockets/node0.socket"}},"event":"Bearer Receive End: length 12288","kind":"MuxTrace"},"env":"<unknown>:00000","host":"","loc":null,"msg":"","ns":["db-sync-node.Mux"],"pid":"590769","sev":"Debug","thread":"61"}
{"app":[],"at":"2022-10-20T13:15:10.38Z","data":{"bearer":{"local":{"path":""},"remote":{"path":"/opt/cardano/cnode/sockets/node0.socket"}},"event":"Bearer Receive Header Start","kind":"MuxTrace"},"env":"<unknown>:00000","host":"","loc":null,"msg":"","ns":["db-sync-node.Mux"],"pid":"590769","sev":"Debug","thread":"61"}
{"app":[],"at":"2022-10-20T13:15:10.38Z","data":{"bearer":{"local":{"path":""},"remote":{"path":"/opt/cardano/cnode/sockets/node0.socket"}},"event":"Bearer Receive Start: length 8","kind":"MuxTrace"},"env":"<unknown>:00000","host":"","loc":null,"msg":"","ns":["db-sync-node.Mux"],"pid":"590769","sev":"Debug","thread":"61"}
{"app":[],"at":"2022-10-20T13:15:10.38Z","data":{"bearer":{"local":{"path":""},"remote":{"path":"/opt/cardano/cnode/sockets/node0.socket"}},"event":"Bearer Receive End: length 8","kind":"MuxTrace"},"env":"<unknown>:00000","host":"","loc":null,"msg":"","ns":["db-sync-node.Mux"],"pid":"590769","sev":"Debug","thread":"61"}

[HT-Moh]

somehow the postgress DB was stuck, I restarted the DB now the dbsync is working. Thanks