Do I need to execute "create extension pgcrypto" everytime?
pgcrypto is a regular Postgres extension. You have to install it once per database (not per DB cluster like you may have been assuming) to use it. Installation is permanent, surviving DB restarts.

If you want every new database in the same DB cluster to have it pre-installed, you can install it to the default template database template1 by connecting to it and installing it normally. Or install it in any other database and use that as template with:

CREATE DATABASE new_db TEMPLATE my_template;


See:


Creating UUID extension as the non super privileged user


One other thing that can go wrong: the search_path. See:


Is it recommended to install extensions into pg_catalog schema?
How does the search_path influence identifier resolution and the “current schema”


But if that was your problem you'd get warnings when trying to install the same extension a second time.

The only other exotic explanation I can think of: your DB might have been restored to a previous state. But you would certainly know of that, wouldn't you?

Or did you just connect to a different database by mistake? The "read replica" you mentioned?


  how to make this cryptographic functions available in such read-only environment?


If it's not replicated by your replication solution, you have to install it manually once.