I can restore a whole db using shell commands like
sudo -u postgres psql -c "DROP DATABASE mynewdbname with (FORCE);"
sudo -u postgres psql -c "CREATE DATABASE mynewdbname;"
sudo -u postgres pg_restore -d mynewdbname /home/user/restore_20230824.sql -v
Than I need to execute some grants. Actually I am executing these manually, from shell, every single time
sudo -u postgres psql
mynewdbname
GRANT ALL ON schema name_of_schema TO application_user_name;
GRANT ALL ON ALL TABLES IN schema name_of_schema TO application_user_name;
and a lot of others
I tried to execute command from normal shell user but they failed
sudo -u postgres psql -c "GRANT ALL ON schema name_of_schema TO application_user_name;"
ERROR: schema "name_of_schema " does not exist
But schema name_of_schema
exists, I think it’s because i’ve not selected the db. So I tried
sudo -u postgres psql -c "c mynewdbname; GRANT ALL ON schema name_of_schema TO application_user_name;"
But it fails with a cryptic error
connect: invalid integer value "ON" for connection option "port"
Briefly: how can I execute command against a postgres db from shell?
If usefull, I’m working under a Debian 12. I’ve full access to it, but I’d like to execute all commands from normal user because these commands must occasionally executed from not-so-intelligent users [monkeys co-workers]
2
Answers
I add my 2 cents
I can execute a whole .sql file against a pc, from shell doing
Like the
psql
documentation describes:But the best solution is to avoid the backslash command at all and connect to the correct database right away: