I want to use psql to list all of the databases on a Postgres server, to be parsed by a script. This command lists them:
psql -l -A -t
but the output shows an obvious issue: the records are separated by newlines, but also contain newlines.
$ psql -l -A -t
postgres|postgres|UTF8|en_CA.UTF-8|en_CA.UTF-8|
template0|postgres|UTF8|en_CA.UTF-8|en_CA.UTF-8|=c/postgres
postgres=CTc/postgres
template1|postgres|UTF8|en_CA.UTF-8|en_CA.UTF-8|=c/postgres
postgres=CTc/postgres
Using the -R option I can change the record separator, but it seems like no matter what I change it to, there's the risk of that string appearing in the data. Is it possible to instead tell psql to replace the newlines in the data with something else? (and then what if that string also appears in the data?)
I'd also tried to set the record separator to a null character with such sequences as -R '\000' and -R "\0", but it doesn't seem to interpret escape sequences in the parameter at all, and just uses the literal string \000 instead.
The other option I know of to list all databases is:
psql --quiet --no-align --tuples-only --dbname=postgres --username=postgres --host=127.0.0.1 --port=5432 --command="SELECT datname FROM pg_database"
but that requires me to give the password for the postgres user, so it's not desirable. Perhaps there's another way to get a list of the names of all databases?
Best Answer
I don't see how your two solutions are different... Since it still has to actually CONNECT to a database instance to see what databases are there, I'm willing to bet the only different is HOW you are connecting.
vs
Try instead
This should use the default connection settings (database and login as local account, local connection, 5432 port). If you really need to, then specify the user and database with -U postgres -d postgres .. but otherwise just leave the host and port unspecified.
I'm willing to bet the only reason psql -l -A -t isn't asking for a password is because you have that information in your .pgpass file.