Обсуждение: Fixing db after missing index
Hi, Yesterday I needed to do a big update to one of our databases, and as I always do with big updates, I first deleted the indices for the main table (it's faster this way). However, one index failed to delete cos the disk file was gone. Dunno what happened, but I suppose I brought that one on myself. Anyway, instead of being smart and doing 'touch index_file', I decided to mess around in the system tables. So I deleted that index from pg_class and pg_index. This helped, I could use the table again (it had become unusable), and recreated the indices. Despite it working correctly now, I still get a message relating to the old index when I do a vacuum. The first time I got an error message, before I messed around with pg_class and pg_index, the message included an oid number, which I have written down. I searched tru the other system tables, and foung one other reference to that number, in pg_attribute. Should I delete that row too? More generally, which tables reference an index? Maarten -- Maarten Boekhold, maarten.boekhold@tibcofinance.com TIBCO Finance Technology Inc. "Sevilla" Building Entrada 308 1096 ED Amsterdam, The Netherlands tel: +31 20 6601000 (direct: +31 20 6601066) fax: +31 20 6601005 http://www.tibcofinance.com
Hello all, I am having a problem regarding maintenance of my databases. I have four small db's and clients must use password autentication. The problem is that when I try to pg_dump any of them, I don't know how can I pass username and password. Shouldn't there be a command line option to do this? Environment variables are very unconvenient... The same problem regarding restoring the database. "cat xpto.dump | psql -e dbname" also asks for passwd... What solutions are you using? Please share them with me! Thanks a lot, Raul Raul Miguel Pinheiro de Carvalho ISR - Instituto de Sistemas e Robotica, Porto e-mail: rmpc@fe.up.pt
Raul Carvalho wrote: > > Hello all, > > I am having a problem regarding maintenance of my databases. I have four > small db's and clients must use password autentication. > > The problem is that when I try to pg_dump any of them, I don't know how > can I pass username and password. Shouldn't there be a command line option > to do this? Environment variables are very unconvenient... echo "username\npassword" | pg_dump -u .... > The same problem regarding restoring the database. "cat xpto.dump | psql > -e dbname" also asks for passwd... echo "username\npassword" | psql -u -f xpto.dump .... btw. if executing this from a script I find environment variables more convenient. Maarten -- Maarten Boekhold, maarten.boekhold@tibcofinance.com TIBCO Finance Technology Inc. "Sevilla" Building Entrada 308 1096 ED Amsterdam, The Netherlands tel: +31 20 6601000 (direct: +31 20 6601066) fax: +31 20 6601005 http://www.tibcofinance.com
that is exactly my point! It gives this error: (database: demo, user: demo, password: demo) $ echo "demo\ndemo" | pg_dump -u demo > r.dump Connection to database 'demo' failed. fe_sendauth: no password supplied Very strange... Raul Miguel Pinheiro de Carvalho ISR - Instituto de Sistemas e Robotica, Porto e-mail: rmpc@fe.up.pt On Mon, 17 Apr 2000, Maarten Boekhold wrote: > > > Raul Carvalho wrote: > > > > Hello all, > > > > I am having a problem regarding maintenance of my databases. I have four > > small db's and clients must use password autentication. > > > > The problem is that when I try to pg_dump any of them, I don't know how > > can I pass username and password. Shouldn't there be a command line option > > to do this? Environment variables are very unconvenient... > > echo "username\npassword" | pg_dump -u .... > > > The same problem regarding restoring the database. "cat xpto.dump | psql > > -e dbname" also asks for passwd... > > echo "username\npassword" | psql -u -f xpto.dump .... > > btw. if executing this from a script I find environment variables more > convenient. > > Maarten > > -- > > Maarten Boekhold, maarten.boekhold@tibcofinance.com > TIBCO Finance Technology Inc. > "Sevilla" Building > Entrada 308 > 1096 ED Amsterdam, The Netherlands > tel: +31 20 6601000 (direct: +31 20 6601066) > fax: +31 20 6601005 > http://www.tibcofinance.com >
Oh, I also tryed some of these: (echo "user\n"; echo "password\n") | pg_dump..... echo "user\npassword\n" | ..... It doesn't seem to work, though... Raul Miguel Pinheiro de Carvalho ISR - Instituto de Sistemas e Robotica, Porto e-mail: rmpc@fe.up.pt On Mon, 17 Apr 2000, Raul Carvalho wrote: > > that is exactly my point! > > It gives this error: (database: demo, user: demo, password: demo) > > $ echo "demo\ndemo" | pg_dump -u demo > r.dump > Connection to database 'demo' failed. > fe_sendauth: no password supplied > > Very strange... > > Raul Miguel Pinheiro de Carvalho > ISR - Instituto de Sistemas e Robotica, Porto > e-mail: rmpc@fe.up.pt > > On Mon, 17 Apr 2000, Maarten Boekhold wrote: > > > > > > > Raul Carvalho wrote: > > > > > > Hello all, > > > > > > I am having a problem regarding maintenance of my databases. I have four > > > small db's and clients must use password autentication. > > > > > > The problem is that when I try to pg_dump any of them, I don't know how > > > can I pass username and password. Shouldn't there be a command line option > > > to do this? Environment variables are very unconvenient... > > > > echo "username\npassword" | pg_dump -u .... > > > > > The same problem regarding restoring the database. "cat xpto.dump | psql > > > -e dbname" also asks for passwd... > > > > echo "username\npassword" | psql -u -f xpto.dump .... > > > > btw. if executing this from a script I find environment variables more > > convenient. > > > > Maarten > > > > -- > > > > Maarten Boekhold, maarten.boekhold@tibcofinance.com > > TIBCO Finance Technology Inc. > > "Sevilla" Building > > Entrada 308 > > 1096 ED Amsterdam, The Netherlands > > tel: +31 20 6601000 (direct: +31 20 6601066) > > fax: +31 20 6601005 > > http://www.tibcofinance.com > > > >
I've always done it by: pg_dump .... [enter] <blindly type username [enter] password [enter]> This works for me with the note that the dump file now has my username and password at the top of it. --Rainer > -----Original Message----- > From: pgsql-admin-owner@hub.org > [mailto:pgsql-admin-owner@hub.org]On Behalf Of Raul Carvalho > Sent: Tuesday, April 18, 2000 4:21 AM > To: Maarten Boekhold > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Maintenance > > > > Oh, I also tryed some of these: > > (echo "user\n"; echo "password\n") | pg_dump..... > echo "user\npassword\n" | ..... > > > It doesn't seem to work, though... > > Raul Miguel Pinheiro de Carvalho > ISR - Instituto de Sistemas e Robotica, Porto > e-mail: rmpc@fe.up.pt
Not very nice, but it worked :) Thanks, Raul Raul Miguel Pinheiro de Carvalho ISR - Instituto de Sistemas e Robotica, Porto e-mail: rmpc@fe.up.pt On Tue, 18 Apr 2000, Rainer Mager wrote: > I've always done it by: > > pg_dump .... [enter] > <blindly type username [enter] password [enter]> > > This works for me with the note that the dump file now has my username and > password at the top of it. > > --Rainer > > > > > -----Original Message----- > > From: pgsql-admin-owner@hub.org > > [mailto:pgsql-admin-owner@hub.org]On Behalf Of Raul Carvalho > > Sent: Tuesday, April 18, 2000 4:21 AM > > To: Maarten Boekhold > > Cc: pgsql-admin@postgresql.org > > Subject: Re: [ADMIN] Maintenance > > > > > > > > Oh, I also tryed some of these: > > > > (echo "user\n"; echo "password\n") | pg_dump..... > > echo "user\npassword\n" | ..... > > > > > > It doesn't seem to work, though... > > > > Raul Miguel Pinheiro de Carvalho > > ISR - Instituto de Sistemas e Robotica, Porto > > e-mail: rmpc@fe.up.pt > >
How about restoring the database if it has a password? TIA, Raul Raul Miguel Pinheiro de Carvalho ISR - Instituto de Sistemas e Robotica, Porto e-mail: rmpc@fe.up.pt On Tue, 18 Apr 2000, Rainer Mager wrote: > I've always done it by: > > pg_dump .... [enter] > <blindly type username [enter] password [enter]> > > This works for me with the note that the dump file now has my username and > password at the top of it. > > --Rainer > > > > > -----Original Message----- > > From: pgsql-admin-owner@hub.org > > [mailto:pgsql-admin-owner@hub.org]On Behalf Of Raul Carvalho > > Sent: Tuesday, April 18, 2000 4:21 AM > > To: Maarten Boekhold > > Cc: pgsql-admin@postgresql.org > > Subject: Re: [ADMIN] Maintenance > > > > > > > > Oh, I also tryed some of these: > > > > (echo "user\n"; echo "password\n") | pg_dump..... > > echo "user\npassword\n" | ..... > > > > > > It doesn't seem to work, though... > > > > Raul Miguel Pinheiro de Carvalho > > ISR - Instituto de Sistemas e Robotica, Porto > > e-mail: rmpc@fe.up.pt > >