databases list to file

am Thu, dem 09.10.2008, um 15:13:58 +0100 mailte Joao Ferreira gmail folgendes:

Hello all,

I need to print to a file a simple list of all the databases on my
postgresql.

I need to do this from a shell script to be executed without human
intervention

I guess something like:

su postgres -c 'psql ...whatever.... > /tmp/my_databases.txt'

but I don't know exactly to what extent I can format the output of this
in order to simplify the parser that will read that file.

any suggestions ?

psql -l > /tmp/my_databases.txt

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

Joao Ferreira gmail wrote:

Hello all,

I need to print to a file a simple list of all the databases on my
postgresql.

I need to do this from a shell script to be executed without human
intervention

I guess something like:

su postgres -c 'psql ...whatever.... > /tmp/my_databases.txt'

but I don't know exactly to what extent I can format the output of this
in order to simplify the parser that will read that file.

any suggestions ?

thank you

joao

psql -tc 'select datname from pg_database' template1

inside psql turn \o file
then \l

2008/10/9 Jeff Ross <jross@wykids.org>:

postgres Emanuel CALVO FRANCO wrote:

inside psql turn \o file
then \l

I need to print to a file a simple list of all the databases on my
postgresql.

I need to do this from a shell script to be executed without human
intervention...
...
but I don't know exactly to what extent I can format the output of this
in order to simplify the parser that will read that file.

psql -tc 'select datname from pg_database' template1

psql ...connection options... --no-align --tuples-only -l
steve|steve|UTF8
foo|steve|UTF8
template0|postgres|UTF8
template1|postgres|UTF8

If you only want the names and use the query suggested above, you can
add "where not datistemplate" to eliminate template0 and template1 from
your list.

You can format at will in-line using heredoc format:
psql <<EOS | your_output_parser
\pset format unaligned
\pset tuples_only
\pset fieldsep '\t'
...
select ...
EOS

(Note: in scripts I usually run psql with the --no-psqlrc option and set
the options myself so the script will be portable regardless of the
contents of the .psqlrc file).

Use the -E option to psql to see the queries it runs behind-the-scenes
in response to the various \l, \d, etc. commands then modify the query
to suit.

Cheers,
Steve