psql, getting a list of databases to be parsed by a script

Question

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?

Answer 1

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.

psql -l -A -t

vs

psql --quiet --no-align --tuples-only --dbname=postgres --username=postgres --host=127.0.0.1 --port=5432 --command="SELECT datname FROM pg_database"

Try instead

psql -q -A -t -c "SELECT datname FROM pg_database"

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.

Answer 2

The second solution asks for the password quite possibly because you connect to the database differently. I suppose you are on some Unix-like system - in this case the first command will connect through a Unix socket, the second one through TCP. If you simply say (mostly like Joishi Bodio suggested but I don't see here the merit of the -q flag)

psql -A -t -c "SELECT datname FROM pg_database"

it should return you what you wanted.

Answer 3

Go one step further and really list only the Databases you want in the Backup so that you can run them through a bash script straight up:

psql -At -c "SELECT datname FROM pg_database WHERE datistemplate = false;"

Answer 4

I would go with "SELECT datname FROM pg_database", and have a user setup to login without password from the one machine with read only access to one db to perform the query.

The other option is to alter your parsing. The final column is access privileges that may contain newlines between entries. Well if a line only contains one field which contains an = and a / then ignore that line.