Thread: Renaming a DB

Renaming a DB

From
"Mendola Gaetano"
Date:
Hi all,
I'm running a Postgres 7.3.3 and I had the necessity to
rename a DB, I did this by updating the pg_database
table.
If I perform psql -l  I'm able to see that is reported the
correct name ( I guess because a select on pg_database
table is performed ) but if I try to connect to the DB
after the renaming I obtain the error:

psql: FATAL:  Database "new_name" does not exist in
the system catalog.

however trying after a while ( without do nothing ) I'm
able to connect to "new_name" database, is like the names
of database are cached somewhere and this cache expire
after a while.

Any idea ?


Regards
Gaetano Mendola


Re: Renaming a DB

From
Tom Lane
Date:
"Mendola Gaetano" <mendola@bigfoot.com> writes:
> I'm running a Postgres 7.3.3 and I had the necessity to
> rename a DB, I did this by updating the pg_database
> table.
> If I perform psql -l  I'm able to see that is reported the
> correct name ( I guess because a select on pg_database
> table is performed ) but if I try to connect to the DB
> after the renaming I obtain the error:
> psql: FATAL:  Database "new_name" does not exist in
> the system catalog.

Try vacuuming pg_database (you can do this from any database,
it need not be the one that is having the problem).

            regards, tom lane

Re: Renaming a DB

From
"Mendola Gaetano"
Date:
"Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> "Mendola Gaetano" <mendola@bigfoot.com> writes:
> > I'm running a Postgres 7.3.3 and I had the necessity to
> > rename a DB, I did this by updating the pg_database
> > table.
> > If I perform psql -l  I'm able to see that is reported the
> > correct name ( I guess because a select on pg_database
> > table is performed ) but if I try to connect to the DB
> > after the renaming I obtain the error:
> > psql: FATAL:  Database "new_name" does not exist in
> > the system catalog.
>
> Try vacuuming pg_database (you can do this from any database,
> it need not be the one that is having the problem).

A normal vacuum was not enough I did a vacuum full and seems
working.

Thank you
Gaetano Mendola


Re: Renaming a DB

From
JEANARTHUR@EUROVOX.FR
Date:
Hi,

I have in my logs the following lines :

jui  7 10:40:07 poseidon logger: FATAL:  Non-superuser connection
limit exceeded
jui  7 10:40:44 poseidon logger: LOG:  pq_recvbuf: unexpected EOF
on client connection
jui  7 10:40:53 poseidon last message repeated 23 times



What does it means ? Particulary the "pg_recvbuf" line ?

when this happen, DB is very very very slow and I have to restart it...

Version is 7.3.2 on a dual 2Ghz Xenon with 1go RAM, SCSI RAID 1
drives, Red Hat 8.0
Buffers are set to -B90846
Num of postgres is -N255
And sort meme is -S131072

There are a lot of updates and select on this DB.
Numbers of transaction/sec is approx. 60.

We do a vacuum -v -a -z every 12 hours
and a full vacuum each week.

Thanks for your help !


Jean-Arthur Silve
EuroVox
4, Place Félix Eboué
75583 Paris Cedex 12
T : +33 1 44670505
F : +33 1 44670519





Re: Renaming a DB

From
"Mendola Gaetano"
Date:
<JEANARTHUR@EUROVOX.FR> wrote:
> Hi,
>
> I have in my logs the following lines :
>
> jui  7 10:40:07 poseidon logger: FATAL:  Non-superuser connection
> limit exceeded
> jui  7 10:40:44 poseidon logger: LOG:  pq_recvbuf: unexpected EOF
> on client connection
> jui  7 10:40:53 poseidon last message repeated 23 times

I think is time to increase your max_connections settings.


Regards
Gaetano Mendola





Explain

From
JEANARTHUR@EUROVOX.FR
Date:
Hi,

when I do an explain on a certain query, I have this answer :


                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Aggregate  (cost=100017927.48..100017927.48 rows=1 width=8)
   ->  Seq Scan on stats_daily_2003
(cost=100000000.00..100017927.47 rows=1 width=8)
         Filter: ((id_compte = 29075) AND (periode = '07-07-2003'::date))
(3 rows)


Well, I don"t really undestand the meaning of

cost=100000000.00..100017927.47

and

cost=100017927.48..100017927.48

I guess this result is relatively bad.

The same query on a similar table but with an index give :

                                                 QUERY PLAN
--------------------------------------------------------------------------------------------
----------------
 Aggregate  (cost=9.60..9.60 rows=1 width=8)
   ->  Index Scan using ap_stats_daily_2003_save on
stats_daily_2003_save  (cost=0.00..9.59 rows=2 width=8)
         Index Cond: ((periode = '07-07-2003'::date) AND (id_compte =
29075))
(3 rows)

Could you confirm me the second explain is "better" than the first one
?





Jean-Arthur Silve
EuroVox
4, Place Félix Eboué
75583 Paris Cedex 12
T : +33 1 44670505
F : +33 1 44670519





Re: Explain

From
Sam Barnett-Cormack
Date:
Yes - the lower the number, the faster the query *should* run. It's all
a bit heuristic, and the two values at each stage are lower and higher
cumulative estimates.

The main difference is the 'index scan' versus 'seq scan' - anything you
are going to do such scans on really should have an index, otherwise big
tables get very very slow.

On Mon, 7 Jul 2003 JEANARTHUR@EUROVOX.FR wrote:

> Hi,
>
> when I do an explain on a certain query, I have this answer :
>
>
>                                       QUERY PLAN
> --------------------------------------------------------------------------------------
>  Aggregate  (cost=100017927.48..100017927.48 rows=1 width=8)
>    ->  Seq Scan on stats_daily_2003
> (cost=100000000.00..100017927.47 rows=1 width=8)
>          Filter: ((id_compte = 29075) AND (periode = '07-07-2003'::date))
> (3 rows)
>
>
> Well, I don"t really undestand the meaning of
>
> cost=100000000.00..100017927.47
>
> and
>
> cost=100017927.48..100017927.48
>
> I guess this result is relatively bad.
>
> The same query on a similar table but with an index give :
>
>                                                  QUERY PLAN
> --------------------------------------------------------------------------------------------
> ----------------
>  Aggregate  (cost=9.60..9.60 rows=1 width=8)
>    ->  Index Scan using ap_stats_daily_2003_save on
> stats_daily_2003_save  (cost=0.00..9.59 rows=2 width=8)
>          Index Cond: ((periode = '07-07-2003'::date) AND (id_compte =
> 29075))
> (3 rows)
>
> Could you confirm me the second explain is "better" than the first one
> ?
>
>
>
>
>
> Jean-Arthur Silve
> EuroVox
> 4, Place Félix Eboué
> 75583 Paris Cedex 12
> T : +33 1 44670505
> F : +33 1 44670519
>
>
>
>
>
> ---------------------------(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
>

--

Sam Barnett-Cormack
Software Developer                           |  Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk)  |  Lancaster University

Re: Explain

From
Stephan Szabo
Date:
On Mon, 7 Jul 2003 JEANARTHUR@EUROVOX.FR wrote:

> Hi,
>
> when I do an explain on a certain query, I have this answer :
>
>
>                                       QUERY PLAN
> --------------------------------------------------------------------------------------
>  Aggregate  (cost=100017927.48..100017927.48 rows=1 width=8)
>    ->  Seq Scan on stats_daily_2003
> (cost=100000000.00..100017927.47 rows=1 width=8)
>          Filter: ((id_compte = 29075) AND (periode = '07-07-2003'::date))
> (3 rows)
>
>
> Well, I don"t really undestand the meaning of
>
> cost=100000000.00..100017927.47
>
> and
>
> cost=100017927.48..100017927.48
>
> I guess this result is relatively bad.

This probably actually implies that you've done something like set
enable_seqscan=off at some point which makes the costs of seqscans very high
to discourage them. In any case, this plan is still expected to be much
more expensive than the below even when not taking that into account.  You
may also want to do explain analyze to get real world numbers rather than
just the estimates.

> The same query on a similar table but with an index give :
>                                                  QUERY PLAN
> --------------------------------------------------------------------------------------------
> ----------------
>  Aggregate  (cost=9.60..9.60 rows=1 width=8)
>    ->  Index Scan using ap_stats_daily_2003_save on
> stats_daily_2003_save  (cost=0.00..9.59 rows=2 width=8)
>          Index Cond: ((periode = '07-07-2003'::date) AND (id_compte =
> 29075))
> (3 rows)
>
> Could you confirm me the second explain is "better" than the first one
> ?




Re: Explain

From
JEANARTHUR@EUROVOX.FR
Date:
Thanks for your answer !

Yes, I set up enable_seqscan = off !

Thanks !

On 7 Jul 2003 at 8:46, Stephan Szabo wrote:

>
> On Mon, 7 Jul 2003 JEANARTHUR@EUROVOX.FR wrote:
>
> > Hi,
> >
> > when I do an explain on a certain query, I have this answer :
> >
> >
> >                                       QUERY PLAN
> > --------------------------------------------------------------------------------------
> >  Aggregate  (cost=100017927.48..100017927.48 rows=1 width=8)
> >    ->  Seq Scan on stats_daily_2003
> > (cost=100000000.00..100017927.47 rows=1 width=8)
> >          Filter: ((id_compte = 29075) AND (periode = '07-07-2003'::date))
> > (3 rows)
> >
> >
> > Well, I don"t really undestand the meaning of
> >
> > cost=100000000.00..100017927.47
> >
> > and
> >
> > cost=100017927.48..100017927.48
> >
> > I guess this result is relatively bad.
>
> This probably actually implies that you've done something like set
> enable_seqscan=off at some point which makes the costs of seqscans very high
> to discourage them. In any case, this plan is still expected to be much
> more expensive than the below even when not taking that into account.  You
> may also want to do explain analyze to get real world numbers rather than
> just the estimates.
>
> > The same query on a similar table but with an index give :
> >                                                  QUERY PLAN
> > --------------------------------------------------------------------------------------------
> > ----------------
> >  Aggregate  (cost=9.60..9.60 rows=1 width=8)
> >    ->  Index Scan using ap_stats_daily_2003_save on
> > stats_daily_2003_save  (cost=0.00..9.59 rows=2 width=8)
> >          Index Cond: ((periode = '07-07-2003'::date) AND (id_compte =
> > 29075))
> > (3 rows)
> >
> > Could you confirm me the second explain is "better" than the first one
> > ?
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


Jean-Arthur Silve
EuroVox
4, Place Félix Eboué
75583 Paris Cedex 12
T : +33 1 44670505
F : +33 1 44670519





Ask for Web hosting information ...

From
centeno
Date:
Hi:

Somebody knows a site in Mexico or USA that offer web hosting service, based on postgresql ?

thanks in advanced?

Best regards, Joselo

------------------------------------------------------------
ISC Jose Luis Orduña Centeno
Tecnologías de Información
CIATEQ A.C. Centro de Tecnología Avanzada
Calzada Retablo #150 Colonia Fovissste
Querétaro, Qro. C.P. 76150
Teléfonos
Querétaro, Qro.
Conmutador  +52 (442) 2112600 extensión 617
Directo     +52 (442) 2112617
Fax         +52 (442) 2169963
            +52 (442) 2112609
Villahermosa, Tab.
            +52 (993) 3168363
            +52 (993) 3168364
http://ciateq.mx/~centeno
mailto:centeno@ciateq.mx
------------------------------------------------------------



Re: Renaming a DB

From
Bruce Momjian
Date:
Tom Lane wrote:
> "Mendola Gaetano" <mendola@bigfoot.com> writes:
> > I'm running a Postgres 7.3.3 and I had the necessity to
> > rename a DB, I did this by updating the pg_database
> > table.
> > If I perform psql -l  I'm able to see that is reported the
> > correct name ( I guess because a select on pg_database
> > table is performed ) but if I try to connect to the DB
> > after the renaming I obtain the error:
> > psql: FATAL:  Database "new_name" does not exist in
> > the system catalog.
>
> Try vacuuming pg_database (you can do this from any database,
> it need not be the one that is having the problem).

Tom, why was a VACUUM FULL required?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Renaming a DB

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom, why was a VACUUM FULL required?

I doubt that it was; a VACUUM would have sufficed to fix the tuple
commit bits.  What I forgot to recommend was a CHECKPOINT to make
sure the fixed pages got dumped to disk.  (Until they're written
out to the kernel, a new backend would still see the old pages
when it's doing the GetRawDatabaseInfo cruft.)

I think a VAC FULL would have flushed dirty pages, but a plain VACUUM
wouldn't, so VAC FULL is a second way of getting the result.

            regards, tom lane

Re: Renaming a DB

From
Bruce Momjian
Date:
Oh, I forgot that the backends are reading the raw disk files to find
database names.  Thanks.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom, why was a VACUUM FULL required?
>
> I doubt that it was; a VACUUM would have sufficed to fix the tuple
> commit bits.  What I forgot to recommend was a CHECKPOINT to make
> sure the fixed pages got dumped to disk.  (Until they're written
> out to the kernel, a new backend would still see the old pages
> when it's doing the GetRawDatabaseInfo cruft.)
>
> I think a VAC FULL would have flushed dirty pages, but a plain VACUUM
> wouldn't, so VAC FULL is a second way of getting the result.
>
>             regards, tom lane
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073