Postgres Pro
Downloads
Home   >   mailing lists

Thread: DB crashed and duplicated template0 db

DB crashed and duplicated template0 db

From
"Luki Rustianto"
Date:
Hi,

One of my customer's server goes down, I don't know yet how, but when
when they restart the machine I got this:
1. DB is working like usual
2. template0 and template1 database is *duplicated*
3. I can't do pg_dump, because error returned (see below)

How can I backup the database (other than template0 and template1) ?
How can this happen (duplicated db entry) ?

below is the log:

[korp@bum ~]$ psql -Utest -l
Password:
          List of databases
     Name     |  Owner   | Encoding
--------------+----------+-----------
 template0    | postgres | SQL_ASCII
 template0    | postgres | SQL_ASCII
 template1    | postgres | SQL_ASCII
 template1    | postgres | SQL_ASCII
 tmed      | test     | SQL_ASCII
 tmed_drei | test     | SQL_ASCII
(6 rows)

[korp@bum ~]$ pg_dump -x -O -Utest tmed > db.sql                       Password:
pg_dump: query to obtain list of schemas failed: ERROR:  more than one
row returned by a subquery used as an expression

[terakorp@bumel ~]$ psql -Utest teramed -c "SELECT COUNT(*) FROM
regpatient"    Password:
 count
--------
 135223
(1 row)

[terakorp@bumel ~]$ psql  --version
psql (PostgreSQL) 7.4.8
contains support for command-line editing


thx.

Re: DB crashed and duplicated template0 db

From
"Sibte Abbas"
Date:
On 7/6/07, Luki Rustianto <lukirus@gmail.com> wrote:
> Hi,
>
> One of my customer's server goes down, I don't know yet how, but when
> when they restart the machine I got this:
> 1. DB is working like usual
> 2. template0 and template1 database is *duplicated*
> 3. I can't do pg_dump, because error returned (see below)
>
> How can I backup the database (other than template0 and template1) ?
> How can this happen (duplicated db entry) ?
>
> below is the log:
>
> [korp@bum ~]$ psql -Utest -l
> Password:
>           List of databases
>      Name     |  Owner   | Encoding
> --------------+----------+-----------
>  template0    | postgres | SQL_ASCII
>  template0    | postgres | SQL_ASCII
>  template1    | postgres | SQL_ASCII
>  template1    | postgres | SQL_ASCII
>  tmed      | test     | SQL_ASCII
>  tmed_drei | test     | SQL_ASCII
> (6 rows)
>
> [korp@bum ~]$ pg_dump -x -O -Utest tmed > db.sql                       Password:
> pg_dump: query to obtain list of schemas failed: ERROR:  more than one
> row returned by a subquery used as an expression
>
> [terakorp@bumel ~]$ psql -Utest teramed -c "SELECT COUNT(*) FROM
> regpatient"    Password:
>  count
> --------
>  135223
> (1 row)
>
> [terakorp@bumel ~]$ psql  --version
> psql (PostgreSQL) 7.4.8
> contains support for command-line editing
>
>
> thx.
>

This can happen when your index is/are corrupted. Try issuing a
reindex command.

regards,
--
Sibte Abbas
EnterpriseDB http://www.enterprisedb.com

Re: DB crashed and duplicated template0 db

From
Tom Lane
Date:
"Luki Rustianto" <lukirus@gmail.com> writes:
> One of my customer's server goes down, I don't know yet how, but when
> when they restart the machine I got this:
> 1. DB is working like usual
> 2. template0 and template1 database is *duplicated*
> 3. I can't do pg_dump, because error returned (see below)

This looks a bit like transaction ID wraparound causing old dead
versions of rows to reappear as live.  What's the vacuuming policy
been on this database?

See recent discussion of a similar symptom here
http://archives.postgresql.org/pgsql-admin/2007-07/msg00037.php

            regards, tom lane