Thread: databases list to file

databases list to file

From
Joao Ferreira gmail
Date:
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



Re: databases list to file

From
"A. Kretschmer"
Date:
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

Re: databases list to file

From
Jeff Ross
Date:
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

Re: databases list to file

From
"postgres Emanuel CALVO FRANCO"
Date:
inside psql turn \o file
then \l



2008/10/9 Jeff Ross <jross@wykids.org>:
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: databases list to file

From
Steve Crawford
Date:
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