I have a handful of laptops that at times go off-site (off network) that need access to an in-network database hosted via PostgreSQL (for reasons that are not up to me VPN is not an option).
Currently, whenever a laptop is needing to be prepped to go off-site, while on the network, we backup the live Postgres server database onto the laptop, drop a locally hosted version of the database, and restore it with the live backup file. That way at the time of going off-site the various applications we use can run off that local Postgres database and it is as up-to-date as possible given the circumstance.
I am wanting to write a script (Windows: I was thinking PowerShell to operate in and out of the command line) that automates this process; download live backup, drop local (old) database, restore with new live backup. Backing up the live database via powershell/command line is not a problem. I have my pgpass.conf file saved to %AppData%\roaming\postgresql\pgpass.conf with the appropriate data that looks like this: server:port:database:user:password and currently it has the IP address of my live Postgres server in it. So I can run pg_dump in the command line with any prompt for a password and it works. However I then need to switch the server target to localhost and the only way I know how to do that is to include the -h tag in the dropdb and psql command arguments. However this prompts me to enter the password for my user and therefore kills any automation.
What would be the best way to go about this? Within the same script alter the pgpass.conf file to switch it back and forth between local and networked databases? Or is there some built in process on Postgres' end that makes it possible to use the username, db, and password fields of a pgpass.conf file but targeted at a specified server?
Any information would be appreciated!
Related info summarized:
pgpass.conf -> ..\%AppData%\roaming\postgresql
pg_dump database > "file location for backup .sql file" --- this successfully uses the pgpass.conf file
dropdb -U username -h localhost -d database_name --- this prompts for a password
psql -U username -h localhost -f "file location for backup .sql file" --- as does this
I need a way that automates the switching between server A and localhost for these three commands.