script db_dump using .pgpass on private RDS Instance-CodePudding

I have a Postgres RDS instance in a private subnet in my VPC. I use a VPN on an EC2 instance to connect from my local machine (Windows 11). I have a batch file with three different db_dump commands

pg_dump.bat

pg_dump -h rds_enpoint -U myuser -p 5432 -d db_1_name > C:\<path to db_1_dump.dump>

pg_dump -h rds_enpoint -U myuser -p 5432 -d db_2_name > C:\<path to db_2_dump.dump>

pg_dump -h rds_enpoint -U myuser -p 5432 -d db_3_name > C:\<path to db_3_dump.dump>

I want to execute this batch in a python script that pushes the .dump files to an s3 bucket (I can do this). When I execute pg_dump.bat, I have to enter the password for each command to execute.

I've been trying to figure out how to store the password file on my local Windows machine.

I've created the pgpass.conf file in app data

C:\Users\username\AppData\postgresql\postgresql.conf

with this

hostname:port:database:username:password

It still asks me for the password.

(base) PS C:\Users\username> pg_dump -h res_endpoint -U username -p 5432 -d db_1_name > C:\<path to db_1_dump.dump
>>
Password:

When I try to bypass the password

pg_dump -h res_endpoint -U username -p 5432 -d db_1_name -w > C:\<path to db_1_dump.dump

pg_dump: error: connection to server at "rds_endpoint" (ip), port 5432 failed: fe_sendauth: no password supplied

What am I doing wrong? I know there's a way to store a .pgpass file on the user's home directory. However, I don't know how to SSH into an rds instance and add a file.

Thank you!

CodePudding user response：

You got the name of the file wrong, and are missing an element of the path.

It will generally be:

C:\Users\username\AppData\Roaming\postgresql\pgpass.conf

Also, make sure the line endings in the file are not \r\n, as the \r will be considered part of the password.