Thread: pg_dumpall storing multiple copies of DB's?

pg_dumpall storing multiple copies of DB's?

From
Bill McGonigle
Date:
Under what circumstances might pg_dumpall store several copies of the
database?  When I restore the database with psql < dumpfile I see 4-5
occurances of each database.  This is on 7.1.3.

Thanks,
-Bill


Re: pg_dumpall storing multiple copies of DB's?

From
Doug McNaught
Date:
Bill McGonigle <mcgonigle@medicalmedia.com> writes:

> Under what circumstances might pg_dumpall store several copies of the
> database?  When I restore the database with psql < dumpfile I see 4-5
> occurances of each database.  This is on 7.1.3.

Are you doing the restore into a "fresh" data area (just initdb'd)?
That's what pg_dumpall assumes you are going to do AFAICS.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

Re: pg_dumpall storing multiple copies of DB's?

From
Bill McGonigle
Date:
On Tuesday, February 26, 2002, at 05:39 , Doug McNaught wrote:

> Are you doing the restore into a "fresh" data area (just initdb'd)?
> That's what pg_dumpall assumes you are going to do AFAICS.

I hadn't done that, but I did drop the databases first.  The data is
stored multiple times in the backup, so the other problem is that this
problem gives me 1.8GB backups when I should have 300-400MB backups.

Thanks,
-Bill


Re: pg_dumpall storing multiple copies of DB's?

From
Doug McNaught
Date:
Bill McGonigle <mcgonigle@medicalmedia.com> writes:

> On Tuesday, February 26, 2002, at 05:39 , Doug McNaught wrote:
>
> > Are you doing the restore into a "fresh" data area (just initdb'd)?
> > That's what pg_dumpall assumes you are going to do AFAICS.
>
> I hadn't done that, but I did drop the databases first.  The data is
> stored multiple times in the backup, so the other problem is that this
> problem gives me 1.8GB backups when I should have 300-400MB backups.

Hmmm, that's very odd.  I'm guessing that something was screwed up in
the data area that you dumped--I haven't seen this happen.

Are all the copies identical?

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

Re: pg_dumpall storing multiple copies of DB's?

From
Bill McGonigle
Date:
On Tuesday, February 26, 2002, at 05:49 , Doug McNaught wrote:

> Hmmm, that's very odd.  I'm guessing that something was screwed up in
> the data area that you dumped--I haven't seen this happen.
>
> Are all the copies identical?
>

Good though - I'll whip up a script tomorrow to split the dump into its
duplicate(?) parts and diff them.

Just another datapoint, I did move my database server to a new machine
recently and the problem followed.  Of course, the database was restored
from a dump with the problem (which did succeed BTW, and the data does
appear to be correct).

-Bill


Re: pg_dumpall storing multiple copies of DB's?

From
Neil Conway
Date:
On Tue, 2002-02-26 at 18:00, Bill McGonigle wrote:
> Just another datapoint, I did move my database server to a new machine
> recently and the problem followed.  Of course, the database was restored
> from a dump with the problem (which did succeed BTW, and the data does
> appear to be correct).

What does this query give you?

SELECT datname FROM pg_database;

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC


Re: pg_dumpall storing multiple copies of DB's?

From
Tom Lane
Date:
Bill McGonigle <mcgonigle@medicalmedia.com> writes:
> Under what circumstances might pg_dumpall store several copies of the
> database?  When I restore the database with psql < dumpfile I see 4-5
> occurances of each database.  This is on 7.1.3.

pg_dumpall gets the list of databases to store using a query along the
lines of

SELECT ... FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid)

If you happen to have multiple entries in pg_shadow with the same
usesysid, then the SELECT will generate multiple rows for the matching
databases.

7.2 has a unique index on pg_shadow.usesysid, so hopefully this failure
mode is a thing of the past now.  In the meantime, get rid of the extra
pg_shadow entries and dump again.

            regards, tom lane

Re: pg_dumpall storing multiple copies of DB's?

From
Bill McGonigle
Date:
On Tuesday, February 26, 2002, at 08:08 , Neil Conway wrote:

> What does this query give you?
>
> SELECT datname FROM pg_database;
>

Just one of each db, and template0 and template1.  The flags appear to be
correct on them too.

-Bill


Re: pg_dumpall storing multiple copies of DB's?

From
Bill McGonigle
Date:
On Wednesday, February 27, 2002, at 12:08 , Tom Lane wrote:

> If you happen to have multiple entries in pg_shadow with the same
> usesysid, then the SELECT will generate multiple rows for the matching
> databases.
>
> 7.2 has a unique index on pg_shadow.usesysid, so hopefully this failure
> mode is a thing of the past now.  In the meantime, get rid of the extra
> pg_shadow entries and dump again.

Just checked pg_shadow, and each usesysid is unique.  In pg_database,
every datlastsysoid is the same, 18539 - is that OK?

-Bill


Re: pg_dumpall storing multiple copies of DB's?

From
Tom Lane
Date:
Bill McGonigle <mcgonigle@medicalmedia.com> writes:
> Just checked pg_shadow, and each usesysid is unique.

Drat, another perfectly good theory down the drain.

Please look in the pg_dumpall script to see the query it uses to get the
list of database names, and run that query by hand to see if you get
duplicates.  If so, can you figure out why?

> In pg_database, every datlastsysoid is the same, 18539 - is that OK?

Yes, that's expected.

            regards, tom lane

Re: pg_dumpall storing multiple copies of DB's?

From
Bill McGonigle
Date:
OK, I wrote a script to split the database backup where on '-- Selected
TOC Entries:' lines - from the head of each of those, this is
interesting.  If I'm reading this correctly, there are 3 copies of this
database 'studies' each with their own OID.

That feels not quite right.

-Bill


==> dboutput.1 <==
-- Selected TOC Entries:
--
\connect - postgres
--
-- TOC Entry ID 2 (OID 5187716)
--
-- Name: studies Type: TABLE Owner: postgres
--

CREATE TABLE "studies" (

==> dboutput.3 <==
-- Selected TOC Entries:
--
\connect - postgres
--
-- TOC Entry ID 2 (OID 7772205)
--
-- Name: studies Type: TABLE Owner: postgres
--

CREATE TABLE "studies" (

==> dboutput.4 <==
-- Selected TOC Entries:
--
\connect - postgres
--
-- TOC Entry ID 2 (OID 12941200)
--
-- Name: studies Type: TABLE Owner: postgres
--

CREATE TABLE "studies" (


database problem..

From
Petre Daniel
Date:
hey.. i am very new to database and stuff..
i got this old server,but very important one..
power outage,it crashed.. and now when i do \d it says no relations
found..
it seems that i lost the database..
and i just found out that no backup have been made
i only got the core file of around 2 mb..
any chance to get the db back?
thx a lot.

--
Petre L. Daniel,System Administrator
Canad Systems Pitesti Romania
http://www.cyber.ro email:support@cyber.ro
tel:+4048206200 +4048220044



Re: pg_dumpall storing multiple copies of DB's?

From
Tom Lane
Date:
Bill McGonigle <mcgonigle@medicalmedia.com> writes:
> OK, I wrote a script to split the database backup where on '-- Selected
> TOC Entries:' lines - from the head of each of those, this is
> interesting.  If I'm reading this correctly, there are 3 copies of this
> database 'studies' each with their own OID.
> That feels not quite right.

Indeed.  Did you perhaps have multiple pg_shadow entries matching the
database's datdba in the old installation?

            regards, tom lane

Re: database problem..

From
tony
Date:
On Tue, 2002-03-05 at 16:04, Petre Daniel wrote:
> hey.. i am very new to database and stuff..
> i got this old server,but very important one..
> power outage,it crashed.. and now when i do \d it says no relations
> found..
> it seems that i lost the database..
> and i just found out that no backup have been made
> i only got the core file of around 2 mb..
> any chance to get the db back?

Version of postgres?

Non trivial task, it took me several weeks of proding and coaxing don't
do anything hasty. Make a copy of your data directory on to another
machine. Play with the copy not the original.

If your postgresql version is older than 7.1 then say adieu to your data
now...

Cheers

Tony Grant

--
RedHat Linux on Sony Vaio C1XD/S
http://www.animaproductions.com/linux2.html
Macromedia UltraDev with PostgreSQL
http://www.animaproductions.com/ultra.html


Re: pg_dumpall storing multiple copies of DB's?

From
Bill McGonigle
Date:
On Tuesday, March 5, 2002, at 10:48 , Tom Lane wrote:

> Indeed.  Did you perhaps have multiple pg_shadow entries matching the
> database's datdba in the old installation?

It's possible the old system, the one I inherited, did have this
problem.  That's since been recycled, so it's hard to say.  A dump from
that system was loaded onto the current system.  The other pg_shadow
data arrived intact.  Unless 7.1.3 has code to prevent duplicates and
7.0 (old system) didn't I would suspect pg_shadow looks the same now as
it did then.

The current system, which only has one pg_shadow entry for the
database's datdba, is still producing the dumps with multiple copies.

When I run:

SELECT datname, coalesce(usename, (select usename from pg_shadow where
usesysid=(select datdba from pg_database where datname='template0'))),
pg_encoding_to_char(d.encoding), datistemplate, datpath FROM pg_database
d LEFT JOIN pg_shadow u ON (datdba = usesysid) WHERE datallowconn;

(from pg_dumpall) I only see one copy of the database.

Two questions come to mind:

How does pgsql handle this case?  Does it iterate over every OID?  Just
the first?  I don't appear to be suffering any data loss or corruption.

What's the best way to, a) determine the copy of the table to keep and
b) do a new dump with just that one?  pg_dump doesn't seem to accept any
options for OID's, except to ignore them.  Hmmm, as I typed that, it
occured to me that perhaps an OID-less dump would do the trick - on
import it would either duplicate an insert or give an error, depending
on the table constraints.

-Bill


Re: pg_dumpall storing multiple copies of DB's?

From
Tom Lane
Date:
Bill McGonigle <mcgonigle@medicalmedia.com> writes:
> ...  Unless 7.1.3 has code to prevent duplicates and
> 7.0 (old system) didn't I would suspect pg_shadow looks the same now as
> it did then.

There is a unique index to prevent duplicate usesysid in 7.2, but not in
7.1.  However, it doesn't look like 7.0's pg_dumpall did a join anyway,
so that's not the explanation.

> The current system, which only has one pg_shadow entry for the
> database's datdba, is still producing the dumps with multiple copies.
> When I run:
> SELECT datname, coalesce(usename, (select usename from pg_shadow where
> usesysid=(select datdba from pg_database where datname='template0'))),
> pg_encoding_to_char(d.encoding), datistemplate, datpath FROM pg_database
> d LEFT JOIN pg_shadow u ON (datdba = usesysid) WHERE datallowconn;
> (from pg_dumpall) I only see one copy of the database.

Most curious.  What exactly do you see from that query?  I'm wondering
if pg_dumpall's simplistic parsing of the output ("while read ...") is
getting fooled by embedded spaces or some such.

            regards, tom lane

Re: pg_dumpall storing multiple copies of DB's?

From
Bill McGonigle
Date:
On Tuesday, March 5, 2002, at 12:09 , Tom Lane wrote:

>> The current system, which only has one pg_shadow entry for the
>> database's datdba, is still producing the dumps with multiple copies.
>> When I run:
>> SELECT datname, coalesce(usename, (select usename from pg_shadow where
>> usesysid=(select datdba from pg_database where datname='template0'))),
>> pg_encoding_to_char(d.encoding), datistemplate, datpath FROM
>> pg_database
>> d LEFT JOIN pg_shadow u ON (datdba = usesysid) WHERE datallowconn;
>> (from pg_dumpall) I only see one copy of the database.
>
> Most curious.  What exactly do you see from that query?  I'm wondering
> if pg_dumpall's simplistic parsing of the output ("while read ...") is
> getting fooled by embedded spaces or some such.
>
>             regards, tom lane
>

Sending a copy of output off-list, since I'm security paranoid (the
archives, not the membership).


Re: database problem..

From
Jean-Michel POURE
Date:
Le Mardi 5 Mars 2002 17:13, tony a écrit :
> If your postgresql version is older than 7.1 then say adieu to your data
> now...

I experienced a crash on a Linux station (power-failure) and noticed that
some row-level locking prevented PostgreSQL from starting up.

After backup, you may need to start the single-user version of postgreSQL.
This will unlock rows :

1) Copy /var/lib/pgsql/data/ to another computer. The other computer should
run the exact same version of PostgreSQL. Otherwize, wait for a more detailed
help from this forum.

2) postgresql DATABASE_NAME will start PostgreSQL in single-user mode.

Can anyone confirm, I am not  familiar with database recovery.

/Jean-Michel POURE