Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

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.

share|improve this answer
    
I don't have enough rep to comment on your answer, Dezso, so I'll comment here - the only reason I included the -q tag is because the OP had it. I, also, see no reason for it. –  Joishi Bodio Sep 6 '13 at 20:51

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.

share|improve this answer

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.

share|improve this answer

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;"
share|improve this answer
    
How do you know that the OP doesn't want to list the template DBs? –  dezso Sep 19 '13 at 11:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.