Alternative to list command in postgres for scripting

Question

Unfortunatly psql -l uses linewraps

example see output and regard the "access" column

                                          List of databases
          Name           |   Owner    | Encoding |   Collate   |    Ctype    |   Access privileges
-------------------------+------------+----------+-------------+-------------+-----------------------
 firstdb                 | postgres   | UTF8     | de_DE.UTF-8 | de_DE.UTF-8 |
 secnddb                 | scnduser   | UTF8     | de_DE.UTF-8 | de_DE.UTF-8 |
 thrddb                  | scnduser   | UTF8     | de_DE.UTF-8 | de_DE.UTF-8 |
 postgres                | postgres   | UTF8     | de_DE.UTF-8 | de_DE.UTF-8 |
 template0               | postgres   | UTF8     | de_DE.UTF-8 | de_DE.UTF-8 | =c/postgres          +
                         |            |          |             |             | postgres=CTc/postgres
 template1               | postgres   | UTF8     | de_DE.UTF-8 | de_DE.UTF-8 | =c/postgres          +
                         |            |          |             |             | postgres=CTc/postgres
(6 rows)

hint even with some option, I can't get that gone:

$ psql -Atlqn
firstdb|postgres|UTF8|de_DE.UTF-8|de_DE.UTF-8|
secnddb|scnduser|UTF8|de_DE.UTF-8|de_DE.UTF-8|
thrddb|scnduser|UTF8|de_DE.UTF-8|de_DE.UTF-8|
postgres|postgres|UTF8|de_DE.UTF-8|de_DE.UTF-8|
template0|postgres|UTF8|de_DE.UTF-8|de_DE.UTF-8|=c/postgres
postgres=CTc/postgres
template1|postgres|UTF8|de_DE.UTF-8|de_DE.UTF-8|=c/postgres
postgres=CTc/postgres

Question Is there another way to get the list of databases in the same way \list prints it so I can use it in scripts for parsing with e.g. awk?

Craig Ringer · Accepted Answer · 2014-01-21 14:01:27Z

2

Interesting issue. You're being bitten by hard linewraps in ACL entries. Those aren't the only place they can appear btw, just the most common.

Use a null-byte recordsep

Rather than trying to avoid the newlines, why not use a different record separator? A null byte (\0) can be good; that's what the -0 option is for. It's only useful if your client can deal with null bytes, though; good for xargs -0, not good for lots of other stuff. The handy thing about a null byte is that it won't appear in psql's output otherwise so there's no risk of conflict. gawk does support null-separated records, though it's woefully underdocumented.

Try, e.g:

psql -Atlqn -0  | gawk -vRS=$'\0' '{ gsub("\n", " "); print }

which replaces newlines in database names (yes, they can appear there!), ACL entries, etc with a space.

Use a different recordsep

Alternately, use -R, e.g. -R '!' or -R '--SEPARATOR--' or whatever is easy for you to parse and not likely to appear in the output.

Query the catalogs yourself, escaping strings

Depending on the information you need, you can instead query the catalogs or information_schema directly, too. You'll still have to deal with funny chars, so you may want a regexp to escape any funny business.

Newlines and shell metacharacters

Beware that you still have to deal with unexpected newlines; consider what happens when some @#$@ does this:

 CREATE DATABASE "my
 database";

Yes, that's a legal database name. So are both of:

 CREATE DATABASE "$(rm -rf /gladthisisnotroot);";

 CREATE DATABASE "$(createuser -l -s my_haxxor -W top_secret)"

Yes, both are legal database names. Yes, that's really, really bad if you don't escape your shell metacharacters properly, you made the mistake of running your shell scripts as root or the postgres user, and they're not feeling nice.

edited Jan 21, 2014 at 14:01

answered Jan 21, 2014 at 13:46

Craig Ringer

329k84 gold badges743 silver badges821 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

childno͡.de Over a year ago

psql 9.1.1 seems not to support -0 it's supported from 9.2+ postgresql.org/docs/9.2/static/app-psql.html but thanks, yes this works: psql -Atlqn -F\| -R'~~SEP~~' | awk 'BEGIN{ FS="|"; RS="~~SEP~~";}{ print "nl:"$0 }'

Craig Ringer · Accepted Answer · 2014-01-21 13:41:27Z

2

All relevant data is in pg_database and pg_user see http://www.postgresql.org/docs/current/static/catalog-pg-database.html

select pg_database.datname,pg_user.usename,pg_encoding_to_char(pg_database.encoding),pg_database.datcollate,pg_database.datctype,pg_database.datacl from pg_database,pg_user WHERE pg_database.datdba = pg_user.usesysid;

on shell, wrapped in psql command:

psql -AStnq -c "select [...]"

returns correct formatted

template1|postgres|UTF8|de_DE.UTF-8|de_DE.UTF-8|{=c/postgres,postgres=CTc/postgres}
template0|postgres|UTF8|de_DE.UTF-8|de_DE.UTF-8|{=c/postgres,postgres=CTc/postgres}
postgres|postgres|UTF8|de_DE.UTF-8|de_DE.UTF-8|
firstdb|postgres|UTF8|de_DE.UTF-8|de_DE.UTF-8|
secnddb|scnduser|UTF8|de_DE.UTF-8|de_DE.UTF-8|
thrddb|scnduser|UTF8|de_DE.UTF-8|de_DE.UTF-8|

edited Jan 21, 2014 at 13:41

Craig Ringer

329k84 gold badges743 silver badges821 bronze badges

answered Jan 21, 2014 at 13:26

childno͡.de

4,8744 gold badges37 silver badges60 bronze badges

1 Comment

Craig Ringer Over a year ago

This won't protect you from a newline (or worse) in datname though.

Collectives™ on Stack Overflow

Alternative to list command in postgres for scripting

2 Answers 2

Use a null-byte recordsep

Use a different recordsep

Query the catalogs yourself, escaping strings

Newlines and shell metacharacters

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Use a null-byte recordsep

Use a different recordsep

Query the catalogs yourself, escaping strings

Newlines and shell metacharacters

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related