Automating pg_dump by inserting user password automatically

Question

So I am trying to automate a powerbi database back-up using pg_dump -d powerbi -h HOST -U USER -n SCHEMA_NAME > dump.sql

When running this on the command line, I am prompted to enter a password where a back-up promptly follows. The resulting dump.sql file is created after a few seconds and is around 60MB in size.

Now, I want to automate this as I want to schedule this task as a monthly task. Automating it means that I have to enter the password automatically.

I am aware that PGPASSWORD = 'my_password' is a way of automatically assigning the password. However, when I run PGPASSWORD = 'my_password' pg_dump -d powerbi -h HOST -U USER -n SCHEMA_NAME > dump.sql

OR SET PGPASSWORD = 'my_password' pg_dump -d powerbi -h HOST -U USER -n SCHEMA_NAME > dump.sql the command executed but an empty dump.sql file is produced.

I tried also using SET PGPASSFILE = "C:\postgresql\pgpass.conf" but I obtain the same result.

Can anyone tell me why this happens and how I could actually generate a meaningful back-up file when using PGPASSWORD

Answer 1

Alright, I ended up having it work by running command SET PGPASSWORD='my_password' first (with no spaces as @jjanes indicated).

Then I ran the the pg_dump -d powerbi -h HOST -U USER -n SCHEMA_NAME > dump.sql once environment variable has been set.

Thanks for the help