How to extract all info for a public address from a local full node's db?
Why this is hard
Bitcoin Core is chiefly a tool to synchronize with the current state of the network and to enforce the consensus rules with a self-owned piece of hardware. Beside being on the same page regarding the rules, this requires the node only to keep track of the ledger, the Unspent Transaction Output (UTXO) set. Bitcoin transactions need to be processed only once to that end.
The blockchain being now over 330 GiB, some people are actually choosing to run pruned nodes (that discard old blockchain data after processing it to calculate the UTXO set). Keeping additional indexes around with dozens of GiB in size is a special interest most users don't seem to care for.
In fact, Bitcoin Core does not feature a full address index out of the box. Originally maybe, because address reuse is considered a bad practice, later because other projects offer this, leaving Bitcoin Core developers feeling that they could focus on other things.
How you do it anyway
Bitcoin Core has an option to build a full transaction index -txindex (turning it on will require you to start over with synchronization and take about 30 GiB more space). This index is needed if you want to look up arbitrary transactions from the past. Other projects leverage this functionality and build additional indexing capability around it to offer Lightning Network support or Bitcoin explorers. I would recommend that you look into the Mempool Open Source Project or the Esplora Block Explorer. Either of these will allow you to run your own instance of a block explorer with APIs to look up blockchain data on basis of addresses. Note that the Esplora index will take about 1 TiB and apparently takes quite some time to build (H/T @Anonymous).