Thread: vacuumdb with cronjob needs password since 9.0?
Hi list, i always vaccumed my postgresql automatically with crontab, because autovacuum is not suitable for my applications. With version 8.2 it works perfect for me with this command line: 00 02 * * * postgres /usr/bin/vacuumdb -d gis -z But not with 9.0, because vacuumdb now wants to have the password to connect to the db. i did not find any options to send the password with the command line in vacuumdb!? Cheers Andreas -- Dipl. Geoökologe Andreas Laggner Institut für Agrarrelevante Klimaforschung (AK) des vTI Arbeitsgruppe Emissionsinventare Johann Heinrich von Thünen-Institut (vTI), Bundesforschungsinstitut für Ländliche Räume, Wald und Fischerei Institute of Agricultural Climate Research (AK) of the vTI Johann Heinrich von Thünen-Institute (vTI), Federal Research Institute for Rural Areas, Forestry and Fisheries Bundesallee 50 D-38116 Braunschweig Tel.: (+49) (0)531 596 2636 Fax : (+49) (0)531 596 2645 E-mail: andreas.laggner@vti.bund.de Homepage: http://www.vti.bund.de
Andreas Laggner <andreas.laggner@vti.bund.de> writes: > Hi list, > > i always vaccumed my postgresql automatically with crontab, because > autovacuum is not suitable for my applications. With version 8.2 it > works perfect for me with this command line: > > 00 02 * * * postgres /usr/bin/vacuumdb -d gis -z > > But not with 9.0, because vacuumdb now wants to have the password to > connect to the db. > i did not find any options to send the password with the command line > in vacuumdb!? Password on command line a bad habit anyway and especially for a possibly long running job like vacuumdb. Have a look at setting the pw in the .pgpass file for the invoking user. If you insist on doing it on cmd line; try; PGPASSWORD=foo vacuumdb ... HTH -- Jerry Sievers Postgres DBA/Development Consulting e: gsievers19@comcast.net p: 305.321.1144
On Thu, May 12, 2011 at 10:56:20AM +0200, Andreas Laggner wrote: > Hi list, > > i always vaccumed my postgresql automatically with crontab, because > autovacuum is not suitable for my applications. With version 8.2 it > works perfect for me with this command line: > > 00 02 * * * postgres /usr/bin/vacuumdb -d gis -z > > But not with 9.0, because vacuumdb now wants to have the password to > connect to the db. version has nothing to do with it. You had to change pg_hba.conf - most likely you changes "trust" authentication for local connections to something like md5 or password. it works exactly the same way in 8.2 as in 9.0 - if connection has "trust" authenticator - password is not necessary. > i did not find any options to send the password with the command > line in vacuumdb!? best options are to either use .pgpass file (described here: http://www.postgresql.org/docs/9.0/static/libpq-pgpass.html) or setup system in such way that postgres user can login locally without password, using ident authenticator. be warned though that ident, when not well configured, is a common source of problems - described for example here: http://www.depesz.com/index.php/2007/10/04/ident/ Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/
thank you depesz, your help was very useful! Am 12.05.2011 13:19, schrieb hubert depesz lubaczewski: > On Thu, May 12, 2011 at 10:56:20AM +0200, Andreas Laggner wrote: >> Hi list, >> >> i always vaccumed my postgresql automatically with crontab, because >> autovacuum is not suitable for my applications. With version 8.2 it >> works perfect for me with this command line: >> >> 00 02 * * * postgres /usr/bin/vacuumdb -d gis -z >> >> But not with 9.0, because vacuumdb now wants to have the password to >> connect to the db. > version has nothing to do with it. You had to change pg_hba.conf - most > likely you changes "trust" authentication for local connections to > something like md5 or password. > it works exactly the same way in 8.2 as in 9.0 - if connection has > "trust" authenticator - password is not necessary. > >> i did not find any options to send the password with the command >> line in vacuumdb!? > best options are to either use .pgpass file (described here: > http://www.postgresql.org/docs/9.0/static/libpq-pgpass.html) > or setup system in such way that postgres user can login locally without > password, using ident authenticator. be warned though that ident, when > not well configured, is a common source of problems - described for > example here: http://www.depesz.com/index.php/2007/10/04/ident/ > > Best regards, > > depesz > -- Dipl. Geoökologe Andreas Laggner Institut für Agrarrelevante Klimaforschung (AK) des vTI Arbeitsgruppe Emissionsinventare Johann Heinrich von Thünen-Institut (vTI), Bundesforschungsinstitut für Ländliche Räume, Wald und Fischerei Institute of Agricultural Climate Research (AK) of the vTI Johann Heinrich von Thünen-Institute (vTI), Federal Research Institute for Rural Areas, Forestry and Fisheries Bundesallee 50 D-38116 Braunschweig Tel.: (+49) (0)531 596 2636 Fax : (+49) (0)531 596 2645 E-mail: andreas.laggner@vti.bund.de Homepage: http://www.vti.bund.de