error en dump

From: "Diego Barreto" <diegob(at)allytech(dot)com>
To: <pgsql-es-ayuda(at)postgresql(dot)org>
Subject: error en dump
Date: 2007-04-12 17:11:43
Message-ID: E552B399B6E12C43B3B50FDCCD28419606B6DD@allytechnt.allytech.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-es-ayuda

se me rompio la base template0 y no se por donde empezar!
no pudo hacer dump, si veo el resto de las bases

Edit Database
template0
DBI connect failed : FATAL: database "template0" is not currently accepting connections



pg_dumpall > backup.total.2007-04-09-0701.sql
pg_dumpall: query failed: ERROR: more than one row returned by a subquery used as an expression
pg_dumpall: query was: SELECT usename, usesysid, passwd, usecreatedb, usesuper, valuntil, (usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template0')) AS clusterowner FROM pg_shadow

encontre esto y lo corri tal cual

UPDATE pg_database SET datallowconn = true WHERE datname = 'template0';
Next connect to template0 and perform the above repair procedures. Finally, do

-- re-freeze template0:
VACUUM FREEZE;
-- and protect it against future alterations:
UPDATE pg_database SET datallowconn = false WHERE datname = 'template0';

pero nada
tambien veo esto y no entiendo como puede ser, tengo dos template0 ...
se trato de recuperar un backup global no se si tendra que ver

template1=# select datname, age(datfrozenxid), datfrozenxid, current_date, current_time FROM pg_database;
select datname, age(datfrozenxid), datfrozenxid, current_date, current_time FROM pg_database;
datname | age | datfrozenxid | date | timetz
--------------+-------------+--------------+------------+--------------------
template0 | -2087841649 | 553 | 2007-04-12 | 14:02:28.529765-03
template1 | 1129148525 | 1077977675 | 2007-04-12 | 14:02:28.529765-03
sapo | 1129286418 | 1077839782 | 2007-04-12 | 14:02:28.529765-03
dbases | 1129285621 | 1077840579 | 2007-04-12 | 14:02:28.529765-03
template0 | 46029871 | 2161096329 | 2007-04-12 | 14:02:28.529765-03
datositios | 1129291048 | 1077835152 | 2007-04-12 | 14:02:28.529765-03
(6 rows)

SELECT oid,datname from pg_database;
oid | datname
---------+--------------
17229 | template0
1 | template1
56915 | sapo
61742 | dbases
17229 | template0
3291695 | datositios
(6 rows)

si alguien me puede dar alguna pista se lo agradezco, las bases estan funcionando actualmente, se puede acceder bien , consultar, editar, actualizar, eliminar, pero no se puede hacer dump, por el momento se hace un backup en frio , parando el motor y comprimiendo el directorio completo del postgres

aca esta la salida de un dump de una sola base

template1=# \q
[postgres(at)ns2 ~]$ pg_dump --file=dumppg.sql --format=c -v --compress=9 --data-only --no-owner --schema=public --host=localhost -U myuser --password dbases
Password:
pg_dump: reading schemas
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: more than one row returned by a subquery used as an expression
pg_dump: The command was: SELECT tableoid, oid, nspname, (select usename from pg_user where nspowner = usesysid) as usename, nspacl FROM pg_namespace
pg_dump: *** aborted because of error

gracias de antemano

saludos
diego

Responses

Browse pgsql-es-ayuda by date

  From Date Subject
Next Message Edwin Quijada 2007-04-12 17:19:56 Resta Horas y dias
Previous Message Juan Martínez 2007-04-12 16:48:52 Re: Hola Lista