Finding database names for overnight vacuum

I have a cron job which vacuums etc all of my databases but it does not
work with 7.1.2 because of the change in structure of .../data/base.

It currently says:

for q in /usr/local/pgsql/data/base/*; do
blaa
blaa
done

How does one get an equivalent list of all databases from 7.1.2?
(and where is it documented?)

Not sure where it's documented, but checkout contrib/oid2name in the
source ball.

- Brandon

----------------------------------------------------------------------------
b. palmer, bpalmer@crimelabs.net pgp:crimelabs.net/bpalmer.pgp5

Quoting Stephen Davies:

I have a cron job which vacuums etc all of my databases but it does not
work with 7.1.2 because of the change in structure of .../data/base.

It currently says:

for q in /usr/local/pgsql/data/base/*; do
blaa
blaa
done

How does one get an equivalent list of all databases from 7.1.2?
(and where is it documented?)

In an Oracle install, one has a file /etc/oratab (or something less
world-wide like /usr/share/oracle/oratab)
which lists the available Oracle instances. It also indicates which need to
be started at system boot -- ie. the
script which starts the Oracle instances at system boot checks this file for
which need to be started.

Someone recently posted a patch/contrib item to do this for Postgres.

A logical extension would be to have another field which indicates which
databases need to be VACUUMed
or VACUUM ANALYZEd when the approriate cron job executes.

Cheers,

Colin

Stephen Davies wrote:

G'day all.

I have a cron job which vacuums etc all of my databases but it does not
work with 7.1.2 because of the change in structure of .../data/base.

It currently says:

for q in /usr/local/pgsql/data/base/*; do
blaa
blaa
done

How does one get an equivalent list of all databases from 7.1.2?
(and where is it documented?)

psql -l

documented in psql man page

So for your purposes, you want:

$ psql -lt | awk '{print $1}' | grep -v template0
bray
junk
lfix
stjohns
template1

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"But I would not have you to be ignorant, brethren,
concerning them which are asleep, that ye sorrow not,
even as others which have no hope. For if we believe
that Jesus died and rose again, even so them also
which sleep in Jesus will God bring with him."
I Thessalonians 4:13,14

Out of curiosity, whats wrong with vacuumdb -a? ("Vacuum all
databases.")

have you tried "psql -l"?

David

bpalmer wrote:

Stephen Davies writes:

I have a cron job which vacuums etc all of my databases but it does not
work with 7.1.2 because of the change in structure of .../data/base.

vacuumdb --all

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

On Mon, 20 Aug 2001, David Ford wrote:

have you tried "psql -l"?

Not sure where it's documented, but checkout contrib/oid2name in the
source ball.

That's great, it tells me that there are databases and who owned them,
but not what folder name maps to which database. (IF he really wants to CD
to the folder).

- Brandon

----------------------------------------------------------------------------
b. palmer, bpalmer@crimelabs.net pgp:crimelabs.net/bpalmer.pgp5

On Mon, 20 Aug 2001, David Ford wrote:

have you tried "psql -l"?

Not sure where it's documented, but checkout contrib/oid2name in the
source ball.

That's great, it tells me that there are databases and who owned them,
but not what folder name maps to which database. (IF he really wants to CD
to the folder).

/contrib/oid2name

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026