Thread: Fixing db after missing index

Fixing db after missing index

From
Maarten Boekhold
Date:
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

Maintenance

From
Raul Carvalho
Date:
 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


Re: Maintenance

From
Maarten Boekhold
Date:

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

Re: Maintenance

From
Raul Carvalho
Date:
 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
>


Re: Maintenance

From
Raul Carvalho
Date:
 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
> >
>
>


RE: Maintenance

From
"Rainer Mager"
Date:
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


RE: Maintenance

From
Raul Carvalho
Date:
 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
>
>


RE: Maintenance

From
Raul Carvalho
Date:
 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
>
>