Thread: Finding database names for overnight vacuum
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?) Cheers and thanks, Stephen Davies ======================================================================== Stephen Davies Consulting scldad@sdc.com.au Adelaide, South Australia. Voice: 08-8177 1595 Computing & Network solutions. Fax: 08-8177 0133
> 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
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.")
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of
> Stephen Davies
> Sent: Monday, 20 August 2001 7:33 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Finding database names for overnight vacuum
>
>
> 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?)
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
have you tried "psql -l"? David bpalmer wrote: >>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 > > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to majordomo@postgresql.org so that your >message can get through to the mailing list cleanly >
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