Thread: Reindex from command line linux
hi, which is the sentence to run a Database REINDEX from linux or from script or shell,etc. Thanks. Fabio Barón S. DBA, Plataforma Tecnologica Gerencia Corporativa de Tecnologia Tel : 4-37-61-61 Ext. 5123 Cel : 311 2-54-74-00 Aviso de confidencialidad: Este mensaje de correo electrónico contiene información estrictamente confidencial no susceptible de ser distribuida. Si usted no es el destinatario de este mensaje, por favor no publicarlo, copiarlo o tomar cualquier otro tipo de accion sobre esta transmision. Si recibio este mensaje por error, por favor notifiquenoslo y eliminelo lo antes posible. Warning of confidentiality: This message of electronic mail contains strictly confidential information nonsusceptible to be distributed. If you are not the adressee of this message, please do not publish it, copy it or take any other kind of action on this transmission. If you got this message by mistake, please notify it to us and eliminate it as soon as possible.
On Fri, Apr 22, 2005 at 11:04:03 -0500, FBaron@co.belcorp.biz wrote: > > > > > hi, which is the sentence to run a Database REINDEX from linux or from > script or shell,etc. I think you can use: psql -c 'REINDEX DATABASE database_name'
thanks Bruno :
I'm running the command with root
psql -c "REINDEX DATABASE Clibelcorpco FORCE"
psql: FATAL: La base de datos "root" no existe en el catalogo del
sistema.(Traslate : The "root" database doesn´t exsists)
I execute :
psql -d Clibelcorpco -c "REINDEX DATABASE Clibelcorpco FORCE"
ERROR: REINDEX DATABASE: Can be executed only on the currently open
database.
what do you think?
Fabio Barón S.
DBA, Plataforma Tecnologica
Gerencia Corporativa de Tecnologia
Tel : 4-37-61-61 Ext. 5123
Cel : 311 2-54-74-00
Bruno Wolff III
<bruno@wolff.to>
Para
22/04/2005 11:39 FBaron@co.belcorp.biz
a.m. cc
pgsql-admin@postgresql.org
Asunto
Re: Reindex from command line linux
On Fri, Apr 22, 2005 at 11:04:03 -0500,
FBaron@co.belcorp.biz wrote:
>
>
>
>
> hi, which is the sentence to run a Database REINDEX from linux or from
> script or shell,etc.
I think you can use:
psql -c 'REINDEX DATABASE database_name'
FBaron@co.belcorp.biz wrote: > > > > hi, which is the sentence to run a Database REINDEX from linux or from You could grab src/contrib/reindexdb or psql -U <user> <dbname> -c "reindex foo" > script or shell,etc. > > Thanks. > > > Fabio Barón S. > DBA, Plataforma Tecnologica > Gerencia Corporativa de Tecnologia > Tel : 4-37-61-61 Ext. 5123 > Cel : 311 2-54-74-00 > > > Aviso de confidencialidad: > Este mensaje de correo electrónico contiene información estrictamente > confidencial no susceptible de ser distribuida. Si usted no es el > destinatario de este mensaje, por favor no publicarlo, copiarlo o tomar > cualquier otro tipo de accion sobre esta transmision. Si recibio este > mensaje por error, por favor notifiquenoslo y eliminelo lo antes posible. > > Warning of confidentiality: > This message of electronic mail contains strictly confidential information > nonsusceptible to be distributed. If you are not the adressee of this > message, please do not publish it, copy it or take any other kind of action > on this transmission. If you got this message by mistake, please notify it > to us and eliminate it as soon as possible. > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedication Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
On Fri, 2005-04-22 at 11:36, FBaron@co.belcorp.biz wrote: > > > thanks Bruno : > I'm running the command with root > psql -c "REINDEX DATABASE Clibelcorpco FORCE" > psql: FATAL: La base de datos "root" no existe en el catalogo del > sistema.(Traslate : The "root" database doesn´t exsists) > > I execute : > > psql -d Clibelcorpco -c "REINDEX DATABASE Clibelcorpco FORCE" > ERROR: REINDEX DATABASE: Can be executed only on the currently open > database. > > what do you think? If this is a cron job you can either schedule it to be run by the postgres super user's crontab (su - postgres, crontab -e) OR you can use su - from root's crontab to run this as the postgres (or other) super user. su - postgres -c 'psql dbnamehere -c "reindex database"' I think I got the quoting right there, not guaranteed though. Note that you are using upper / lower case mixed. Inside the psql environment, unquoted identifiers are lower case. However, to psql, the identifier is used "as is". So, to access the Db1 database, you'd have to enter it correctly, but need no quotes: psql Db1 -c 'reindex database' I recommend just sticking to lower case unless you have a business rule that makes you use mixed case in identifiers. Life it much simpler that way.
On Fri, Apr 22, 2005 at 11:36:46 -0500, FBaron@co.belcorp.biz wrote: > > > > > thanks Bruno : > I'm running the command with root > psql -c "REINDEX DATABASE Clibelcorpco FORCE" > psql: FATAL: La base de datos "root" no existe en el catalogo del > sistema.(Traslate : The "root" database doesn´t exsists) You need to run it as the Postgres superuser instead of "root". > I execute : > > psql -d Clibelcorpco -c "REINDEX DATABASE Clibelcorpco FORCE" > ERROR: REINDEX DATABASE: Can be executed only on the currently open > database. > > what do you think? I am not sure what is happening in this case. It might be a case mismatch on the database name.
On Fri, Apr 22, 2005 at 11:36:46AM -0500, FBaron@co.belcorp.biz wrote: > > psql -d Clibelcorpco -c "REINDEX DATABASE Clibelcorpco FORCE" > ERROR: REINDEX DATABASE: Can be executed only on the currently open > database. If the database name isn't all lowercase then you'll have to quote it. psql -d Clibelcorpco -c 'REINDEX DATABASE "Clibelcorpco"' See "Identifiers and Key Words" in the "SQL Syntax" chapter of the documentation to learn more about quoted identifiers. See also the REINDEX documentation and make sure you understand what REINDEX DATABASE does and doesn't do. Also, FORCE appears to be obsolete as of 7.4. There's also the contrib/reindexdb utility, although I haven't used it so I don't know how well it works. -- Michael Fuhr http://www.fuhr.org/~mfuhr/