Thread: pg_dumpall storing multiple copies of DB's?
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
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
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
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
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
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
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
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
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
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
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" (
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
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
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
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
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
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).
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