Thread: very concerning, tables hopped from one database to another
I'm a bit anxious at the moment. Things have been going just fine for a
couple months and this afternoon Something Bad (tm) happenend.
Summary. I had 8 databases, I created another one for 9. I started
psql and created a table, created a user, and granted permissions. I
then did a \z to check up on things. Lo and behold, I have 3/4 of the
tables from another database moved into here! I disconnect, reconnect,
yep, still there. I connect to the other database and those tables are
missing. I'm absolutely positive that these tables were in 'hmzbook'
before this. I triple checked and examined a dated pg_dump from a few
days ago and 'hmzbook' was correct.
$ psql -l
Password:
List of databases
Name | Owner
-----------------+-----------
gayladb | gayla
hmzbook | hmz
horde | postgres
pg_auth | postgres
sendmail | mailadmin
sendmailexample | mailadmin
template0 | postgres
template1 | postgres
(8 rows)
$ createdb sysinfo_archive
Password:
CREATE DATABASE
$ psql -d sysinfo_archive
Password:
Welcome to psql, the PostgreSQL interactive terminal.
...
sysinfo_archive=# create table sysinfo ( timestamp timestamp default
'now', host inet not null, component varchar not null, node varchar not
null, identity varchar not null, value int8 default 0 );
CREATE
sysinfo_archive=# insert into sysinfo
(host,component,node,identity,value) values
('1.2.3.4','network','eth0','bytes in','1234');
INSERT 26374 1
sysinfo_archive=# create user mouse with encrypted password 'xxxx';
CREATE USER
sysinfo_archive=# grant select,insert on sysinfo to mouse;
GRANT
sysinfo_archive=# \z
Access privileges for database "sysinfo_archive"
Table | Access privileges
--------------------------+-------------------------------
autthoughts |
guestbook |
guestbook_id_seq |
id_seq |
iquotes |
iquotes_id_seq |
ircfriends |
ircfriends_id_seq |
ircfriendsfriends_id_seq |
picture |
profile |
sysinfo | {=,postgres=arwdRxt,mouse=ar}
temporary |
(13 rows)
What the heck just happened and short of dumping these tables out and
importing them back into the other database...
a) how can I fix this and
b) how can I prevent this from happening again?
Very concerned,
David
...
> What the heck just happened and short of dumping these tables out and
> importing them back into the other database...
Most likely, you accidentally added the tables to template1, which then
propagates to any new databases added after that.
Clean up template1, then clean up your existing databases, and you
should be back to normal for any new ones. dump/reload is not required,
except to get tables into databases which need them.
- Thomas
David Ford <david+cert@blue-labs.org> writes:
> Summary. I had 8 databases, I created another one for 9. I started
> psql and created a table, created a user, and granted permissions. I
> then did a \z to check up on things. Lo and behold, I have 3/4 of the
> tables from another database moved into here! I disconnect, reconnect,
> yep, still there. I connect to the other database and those tables are
> missing. I'm absolutely positive that these tables were in 'hmzbook'
> before this. I triple checked and examined a dated pg_dump from a few
> days ago and 'hmzbook' was correct.
PG version?
There was a bug in a prerelease 7.0 or 7.1 (forget which) whereby the
buffer manager sometimes forgot to check the database ID of a dirty
block, but I've not heard of any such cases lately.
regards, tom lane
Yes, they are in template1. This then begs the question, why are they not in the hmzbook database? All the tables were added via psql -f /tmp/dump.hmzbook which has one connect line in it. \connect hmzbook xxxx. David Thomas Lockhart wrote: >... > > >>What the heck just happened and short of dumping these tables out and >>importing them back into the other database... >> >> > >Most likely, you accidentally added the tables to template1, which then >propagates to any new databases added after that. > >Clean up template1, then clean up your existing databases, and you >should be back to normal for any new ones. dump/reload is not required, >except to get tables into databases which need them. > > - Thomas > >
David Ford <david+cert@blue-labs.org> writes:
> This then begs the question, why are they not in the hmzbook database?
> All the tables were added via psql -f /tmp/dump.hmzbook which has one
> connect line in it. \connect hmzbook xxxx.
What would happen if that \connect command failed for some reason?
(hmzbook not there, wrong user, authentication failure, ...)
regards, tom lane
On Sun, 21 Apr 2002 19:10:19 -0400 "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > David Ford <david+cert@blue-labs.org> writes: > > This then begs the question, why are they not in the hmzbook database? > > All the tables were added via psql -f /tmp/dump.hmzbook which has one > > connect line in it. \connect hmzbook xxxx. > > What would happen if that \connect command failed for some reason? > (hmzbook not there, wrong user, authentication failure, ...) AFAICT psql should bail out and not continue to restore the dump -- which is probably the most reasonable behavior, anyway. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
I'm not sure, but being that there was only one connect statement, and 1/4 of the tables were there, I have no idea what went wrong. I imported it by hand so I should have noticed if anything was amiss. David Neil Conway wrote: >On Sun, 21 Apr 2002 19:10:19 -0400 >"Tom Lane" <tgl@sss.pgh.pa.us> wrote: > > >>David Ford <david+cert@blue-labs.org> writes: >> >> >>>This then begs the question, why are they not in the hmzbook database? >>> All the tables were added via psql -f /tmp/dump.hmzbook which has one >>>connect line in it. \connect hmzbook xxxx. >>> >>> >>What would happen if that \connect command failed for some reason? >>(hmzbook not there, wrong user, authentication failure, ...) >> >> > >AFAICT psql should bail out and not continue to restore the dump -- >which is probably the most reasonable behavior, anyway. > >Cheers, > >Neil > > >
David Ford <david+cert@blue-labs.org> writes:
> I'm not sure, but being that there was only one connect statement, and
> 1/4 of the tables were there, I have no idea what went wrong. I
> imported it by hand so I should have noticed if anything was amiss.
Do you find the expected data in the tables --- both the ones that
were where you expected, and the ones that were not? Do the tables
pg_dump cleanly in both cases?
If so, I've got to conclude it was some kind of pilot error. I can
imagine bugs that would cause rows to get copied from one database's
pg_class to another's (cf the aforementioned buffer management error).
But for a "clean" transfer you'd need that to happen simultaneously for
rows in pg_class, pg_attribute, and other places. And make the rows
disappear from the source database, which that old buffer bug did *not*
do. And cause the physical files holding the data to move from one
database's subdirectory to another. This is getting pretty far beyond
the bounds of credibility ...
regards, tom lane
I have to agree that it was pilot error, but I can't for the life of me understand how 1/4 of the tables went into the right db and the others into template1. I saved changed data, droped the hmzbook db, recreated it and ran psql -U hmz -f db.hmzbook and it put all the tables into hmzbook as it should have and template1 remained clean. If the db.hmzbook had more than one \connect in it or something, I wouldn't hesitate to have considered password/pilot error. It's never happened before and postgres is one of the most stable software packages I have ever used. As to your questions, yes the data was found as expected and table dumps were clean. In my opinion, this has to be marked up to pilot error as the most reasonable answer with some as yet unknown reason for the split in tables. Perhaps the socket blew up and psql reconnected to template1? David Tom Lane wrote: >David Ford <david+cert@blue-labs.org> writes: > > >>I'm not sure, but being that there was only one connect statement, and >>1/4 of the tables were there, I have no idea what went wrong. I >>imported it by hand so I should have noticed if anything was amiss. >> >> > >Do you find the expected data in the tables --- both the ones that >were where you expected, and the ones that were not? Do the tables >pg_dump cleanly in both cases? > >If so, I've got to conclude it was some kind of pilot error. I can >imagine bugs that would cause rows to get copied from one database's >pg_class to another's (cf the aforementioned buffer management error). >But for a "clean" transfer you'd need that to happen simultaneously for >rows in pg_class, pg_attribute, and other places. And make the rows >disappear from the source database, which that old buffer bug did *not* >do. And cause the physical files holding the data to move from one >database's subdirectory to another. This is getting pretty far beyond >the bounds of credibility ... > > regards, tom lane > >
David Ford <david+cert@blue-labs.org> writes:
> ... I can't for the life of me understand how 1/4 of the tables went
> into the right db and the others into template1.
I don't have an explanation for that, but it does seem odd.
> Perhaps the socket blew up and psql reconnected to template1?
I think psql will attempt automatic reconnection after a connection
failure in some cases, but AFAIK it should always reconnect to the last
database in use. (Hmm, but if it had originally connected to template1,
and the \connect to hmzbook was inside a script, maybe the reconnect
would go to template1? Peter, can you give us any insight on the
possible behaviors there?)
That still leaves us wondering why the connection failure, but at least
it seems like a possible avenue to an explanation how some of the tables
created by the script wound up in a different database than the others.
regards, tom lane
Tom Lane writes: > > Perhaps the socket blew up and psql reconnected to template1? > > I think psql will attempt automatic reconnection after a connection > failure in some cases, but AFAIK it should always reconnect to the last > database in use. (Hmm, but if it had originally connected to template1, > and the \connect to hmzbook was inside a script, maybe the reconnect > would go to template1? Peter, can you give us any insight on the > possible behaviors there?) psql only attempts to reconnect to anything if the session is interactive. Otherwise psql exits right there if the connection went bad (independent of ON_ERROR_STOP). -- Peter Eisentraut peter_e@gmx.net