Обсуждение: Renaming a DB
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
"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
"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
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
<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
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
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
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 > ?
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
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
------------------------------------------------------------
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
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
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