I apologize if this is the wrong group for this message, but I'm not
sure where else this would go.
I don't have a specific problem, but I would like to ask some questions
about how postgres works.
But first, some backfground info:
I have two identical servers each running postgres 6.5.1 and each has an
identical database called zipfind. This is a pretty static, mostly read
only database with 700,000 rows. A few days ago I got some updated
information for the database, 1,400,000 rows worth, almost double the
data in ascii format.
So, I got the new rows inserted with a perl script which read the ascii
file line by line and inserted the data. This took quite a while, in
fact, it took more than 24 hours. So, I decided I would update the
second database in a different way.
I realized I could pg_dump the new zipfind database, and read it back in
using psql on the other machine, but I decided to try it a little
differently, just to see what would happen.
What I tried was to move the actual data files in the data/base/zipfind
directory from the newly updated database directly to the machine still
in need of updating. I shutdown postmaster on the machine that I was
moving the files to, replaced all of the files in the zipfind directory
with the files from the machine with all the new rows, reset all the
permissions, and restarted postmaster.
The strange thing is, even though the old files were removed and
replaced with the new files using identical file names, psql seemed to
be reading data from the old database as if it had not been removed.
issuing a "select count(*) from zips;" returned the old row count 666730
instead of the new row count ca 1400000 ... if anything I expected to
get some kind of error ..not the old row count!
I checked the filesizes in the zipfind directory to make sure I hadn't
made a mistake while putting the new data in place. Everything was
correct. I then vacuumed the database and rechecked the file sizes. ..
the "zips" table entry now reported the old file size!
It occurred to me that there may be some system tables which were
causing the erratic behaviour, I searched for something relevant but
found nothing.
The only theory that I could come up with was that postgres latched on
to an inode for the original files ..but how it would keep that inode
info across daemon invocations seems a mystery to me.
Explanations appreciated!
Thanks,
Bryan
-----------== Posted via Newsfeeds.Com, Uncensored Usenet News ==---------- http://www.newsfeeds.com The
LargestUsenet Servers in the World!
------== Over 73,000 Newsgroups - Including Dedicated Binaries Servers ==-----
