setting postgresql for db-sync

Question

I have followed the instructions bo build both the cardano-node and cardano-db-sync in the same folder.

I managed to fully sync the node, which is running when attempting to use dbsync.

When I try to follow the instructions to launch db-sync

PGPASSFILE=config/pgpass-mainnet cabal run cardano-db-sync -- \
    --config config/mainnet-config.yaml \
    --socket-path ../cardano-node/state-node-mainnet/node.socket \
    --state-dir ledger-state/
    --schema-dir schema/

I get the following error

[db-sync-node.Subscription:Error:51] [2022-03-09 21:22:29.03 UTC] Identity Connection Attempt Exception, destination LocalAddress "../cardano-node/state-node-mainnet/node.socket" exception: Network.Socket.connect: <socket: 22>: does not exist (Connection refused)
[db-sync-node.ErrorPolicy:Warning:6] [2022-03-09 21:22:29.03 UTC] IP LocalAddress "../cardano-node/state-node-mainnet/node.socket" ErrorPolicySuspendPeer (Just (ConnectionExceptionTrace Network.Socket.connect: <socket: 22>: does not exist (Connection refused))) 20s 20s

When I copy the lines from the postgresql setup command that seems to answer my issue:

~/cardano/cardano-db-sync$ PGPASSFILE=config/pgpass-mainnet scripts/postgresql-setup.sh --createdb

this error message is more telling

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "cexplorer" does not exist

I must admit I installed postgresql via sudo apt-get install and I don't know how to proceed properly from there. Do I have to set up a new datbase called cexplorer? should I create a new user for postgresql? for linux? should I edit permissions (i get a permission 600?

After running PGPASSFILE=config/pgpass-mainnet psql cexplorer i got the following error

WARNING: password file "config/pgpass-mainnet" has group or world access; permissions should be u=rw (0600) or less
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "cexplorer" does not exist

The documentation is so comprehensive and well done!, I don't understand why this very specific part is not in it (or I am not able to jauge my own incompetence;) )

---

edit: removed "mainnet" from the db-sync call parameters. Failing to find the .sql file from the restored snapshot lead the psql database to revert to genesis. --state-dir ledger-state/mainnet(wrong)

Answer 1

should I edit permissions (i get a permission 600?

Yes, you need to reduce the permission down to 600. In addition you might also want to look at few things from a default Postgres deployment (authentication, contents of your private file, [unrelated to this issue - tuning], etc.)

Also, a sample of instructions going through above for setup of Postgrest itself can be found here - ensure you update the folder structure as per your environment, and steps post setup of Postgres instance and building cardano-db-sync here.

Answer 2

I recently set up everything and it works, but I used nix and not cabal as I assume from your PGPASSFILE command that you used. Regarding the permissions, no, you do not need to do it, I had the same error and it doesn't affect anything. You can do it if you want to be sure. Your last error regarding the socket is also perfectly normal. The database cexplorer gets created automatically for mainnet the moment you start DB sync and since you are not at that point yet you get this error. After DB sync has started you can use

psql cexplorer

and access the database (have in mind the command psql is followed by a database name, if none is given, the name postgres is the default and a postgres database already exists).

It says about the socket being down anyway, but also make sure to have the socket online by using

pg_lsclusters
pg_ctlcluster <Ver. number(1st column)> main start

If your socket 5432 is down, that might also explain the initial errors you get.

Answer 3

Seems like there was two missing parts:

First, add a new user to postgres:

(from https://cardano-community.github.io/guild-operators)

echo $(whoami)
# <user>
sudo su postgres
psql

CREATE ROLE <user> SUPERUSER LOGIN;
ALTER USER <user> PASSWORD 'PasswordYouWant';
\q

Not sure where to specify the password, so I used my account password (yikes!)

Second, create the database

with the db-sync provided script (dbsync)

cd cardano-db-sync
scripts/postgresql-setup.sh --createdb

Third, define the communication socket

My db-sync parameters points to a socket, but cardano-node doesn't seem to open the socket by default. I added a the socket to the parameters when launching the node as follow:

cd cardano-node
cardano-node run --socket-path ./state-node-mainnet/node.socket

Then launched db-sync with the same exact parameters (other terminal)

cd cardano-db-sync
PGPASSFILE=config/pgpass-mainnet cabal run cardano-db-sync -- \
    --config config/mainnet-config.yaml \
    --socket-path ../cardano-node/state-node-mainnet/node.socket \
    --state-dir ledger-state/mainnet \
    --schema-dir schema/

note that both socket-path points to the same path

it works !

After a few minutes of verification and syncing the node created the socket!

Thanks for all your very helpful answers. My next steps will obviously be security and tweaking.