Using psql in Automation Scripts (Non Interactive Usage of psql)

When using command-line tools in the scripts, interactive tools enjoy breaking the automation and asking for some information. Most of the interactive commands have non-interactive modes you can use. I recently discovered how to use default PostgreSQL client in such a way.

If a file name .pgpass is found at users home directory, psql client can read the password from this file. Syntax of the file is simple

    hostname:port:database:username:password

Multiple databases and servers can be added to this file. There are also some permission requirements to keep things safe. It won't work without proper permission

    chmod 600 ~/.pgpass

This file is only to keep the password, even though the hostname , port and db info is in the file, only the password is read from the file if user attempts to connect same hostname , port and db.