Does a node have to be set to archival mode in order to run the indexer and populate a postgres database?
Yes, the indexer requires to be connected to an archival node.
This is because the indexer reada all the blocks of the blockchain, while a non-archival node only keeps the last 1,000 blocks.