Thread: stupid question
Maybe this sounds stupid, but, where is the actual database file? I'm sure there's a file that is the actual DB, but what/where? --Bryan
On Fri, 26 Dec 2003, Bryan Irvine wrote:
> Maybe this sounds stupid, but, where is the actual database file? I'm
> sure there's a file that is the actual DB, but what/where?
nope ... depending on what version of postgresql you are running, the
newer ones store it in ${PGDATA}/base/<OID>, under which, there are
indivicudal files for system tables, indices and tables themselves ...
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
--On Friday, December 26, 2003 10:07:52 -0800 Bryan Irvine <bryan.irvine@kingcountyjournal.com> wrote: > Maybe this sounds stupid, but, where is the actual database file? I'm > sure there's a file that is the actual DB, but what/where? > all the files under the data/ directory are the database. LER > --Bryan > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Attachment
> On Fri, 26 Dec 2003, Bryan Irvine wrote:
>
>>Maybe this sounds stupid, but, where is the actual database file? I'm
>>sure there's a file that is the actual DB, but what/where?
>
>
> nope ... depending on what version of postgresql you are running, the
> newer ones store it in ${PGDATA}/base/<OID>, under which, there are
> indivicudal files for system tables, indices and tables themselves ...
Is there a Postgress documentation section somewhere that
would tell me the following:
1) the exact correspondence of the files and tables, indices etc.
2) what are the safe filesystem operations on those files
(individual file backup/recovery, moving of files
between different postgress computers/installations etc.?
TIA.
Roger
Hello, fellow myrealbox user!
On Dec 31, 2003, at 4:09 PM, rhkelly wrote:
>> On Fri, 26 Dec 2003, Bryan Irvine wrote:
>>
>>> Maybe this sounds stupid, but, where is the actual database file?
>>> I'm
>>> sure there's a file that is the actual DB, but what/where?
>> nope ... depending on what version of postgresql you are running, the
>> newer ones store it in ${PGDATA}/base/<OID>, under which, there are
>> indivicudal files for system tables, indices and tables themselves ...
>
> Is there a Postgress documentation section somewhere that
> would tell me the following:
>
> 1) the exact correspondence of the files and tables, indices etc.
>
> 2) what are the safe filesystem operations on those files
> (individual file backup/recovery, moving of files
> between different postgress computers/installations etc.?
Not to be contrary, but why would you want to know this? The beauty of
using a DBMS is to take care of all of that. Unless you're doing some
kind of serious database recovery, I'd say you're better of leaving the
/data directory alone and let PostgreSQL handle that for you. My
admittedly inexperienced opinion is that I'm much more likely to do
serious damage to the system trying to back up individual files rather
than the database (or database cluster) as a whole. And for moving an
database between PostgreSQL installations or backup, I'd use the
pg_dump/pg_restore commands, which are used with a running postmaster.
That said, I have heard of people shutting down the postmaster and
using cp to copy the whole directory.
Sorry if this comes off a little strong. I don't mean to imply your
questions are stupid. It comes from having more trust in PostgreSQL
(and its developers) to handle these things than anyone other than a
very experienced PostgreSQL DBA to mess with anything in the /data
directory. And for what you've described, I think there are safer, more
reliable methods available.
Michael Glaesemann
grzm myrealbox com
Michael Glaesemann wrote: > Hello, fellow myrealbox user! > > ...rhkelly wrote: > >>> >>>> Maybe this sounds stupid, but, where is the actual database file? >>>> I'm sure there's a file that is the actual DB, but what/where? >>> >> Is there a Postgress documentation section somewhere that >> would tell me the following: >> >> 1) the exact correspondence of the files and tables, indices etc. >> >> 2) what are the safe filesystem operations on those files >> (individual file backup/recovery, moving of files >> between different postgress computers/installations etc.? > > > Not to be contrary, but why would you want to know this? Thanks for your comments Michael; to answer your question: So that I can put the system into some 'partial/previous' state in a manner which Postgress itself does not support and with a high degree of confidence that my actions had the desired effect (and no catastrophic side-effects :). I felt it would be a misappropriation of the attention and patience of the list to get into details; I only humbly asked for help with the documentation in a thread that was 'on topic' ;) i.e., something I thought *ought to be* documented (and - at least as fas as item '1' above is concerned - for puposes other than mine!), but was unable to find. I'll keep looking. Roger
rhkelly <rhkelly@myrealbox.com> writes:
> Michael Glaesemann wrote:
>>> 2) what are the safe filesystem operations on those files
>>> (individual file backup/recovery, moving of files
>>> between different postgress computers/installations etc.?
>>
>> Not to be contrary, but why would you want to know this?
> So that I can put the system into some 'partial/previous'
> state in a manner which Postgress itself does not support
You can't. The connections between data files, xlog, and clog
are sufficiently critical that you cannot hope to take some files
out of an installation and put them into another one (nor into
an earlier or later state of the same installation).
regards, tom lane
Tom Lane wrote: > rhkelly <rhkelly@myrealbox.com> writes: > >>Michael Glaesemann wrote: >>>>1) the exact correspondence of the files and tables... >>>>2) what are the safe filesystem operations... >>>Not to be contrary, but why would you want to know this? > >>So that I can put the system into some 'partial/previous' >>state in a manner which Postgress itself does not support > > You can't. The connections between data files, xlog, and clog... May I suggest we take two steps back... I was simply inquiring about what I consider to be an indispensible element of documentation for *any* application - i.e., the content and format of the files in which the application stores the data. I was unable to find it and asked for help. Roger
rhkelly <rhkelly@myrealbox.com> writes: > I was simply inquiring about what I consider to be an indispensible > element of documentation for *any* application - i.e., the content > and format of the files in which the application stores the data. [ Raises eyebrow... ] I'm not aware of any complex application in which file format details are considered an indispensable part of the user-level documentation. Do Word, Photoshop, Outlook, etc provide that info? Where you want to be looking is at the developer docs, eg http://developer.postgresql.org/docs/postgres/page.html and at the source code --- probably src/include/storage/bufpage.h src/include/access/htup.h are good starting points. regards, tom lane
Tom Lane wrote: > rhkelly <rhkelly@myrealbox.com> writes: > >>I was simply inquiring about what I consider to be an indispensible >>element of documentation... > [ Raises eyebrow... ] I'm not aware of any complex application in which > file format details are considered an indispensable part of the user-level > documentation. Do Word, Photoshop, Outlook, etc provide that info? Which could explain why I don't use either Word or Outlook, and why I store the images I operate on with Photoshop as .png's :) But seriously - thank you: your poinetrs below are *much* appreciated. kind regards, RK > Where you want to be looking is at the developer docs, eg > http://developer.postgresql.org/docs/postgres/page.html > and at the source code --- probably > src/include/storage/bufpage.h > src/include/access/htup.h > are good starting points.
On Jan 1, 2004, at 11:37 AM, rhkelly wrote: > Michael Glaesemann wrote: > >> Hello, fellow myrealbox user! >> ...rhkelly wrote: >>>> >>>>> Maybe this sounds stupid, but, where is the actual database file? >>>>> I'm sure there's a file that is the actual DB, but what/where? >>>> > >>> Is there a Postgress documentation section somewhere that >>> would tell me the following: >>> >>> 1) the exact correspondence of the files and tables, indices etc. >>> >>> 2) what are the safe filesystem operations on those files >>> (individual file backup/recovery, moving of files >>> between different postgress computers/installations etc.? >> Not to be contrary, but why would you want to know this? > > Thanks for your comments Michael; No problem. I hope you found them helpful. > to answer your question: > > So that I can put the system into some 'partial/previous' > state in a manner which Postgress itself does not support > and with a high degree of confidence that my actions had > the desired effect (and no catastrophic side-effects :). > I felt it would be a misappropriation of the attention and > patience of the list to get into details; I've found these lists very helpful in the past. Often list members will ask for additional information to hopes of finding a solution. I can't recall any complaints of boredom or posts wasting people's time. I don't think you have anything to fear :) On the contrary, developers in particular and members in general like to hear about how people are using PostgreSQL, what they'd like to do with PostgreSQL, and what people are doing to accomplish what they want. If you were to go into details, you'd probably find people giving you additional help. As for myself, I probably won't be able to help, but I'm definitely curious as to what you're working on. Care to share? :) > I only humbly > asked for help with the documentation in a thread that was > 'on topic' ;) i.e., something I thought *ought to be* > documented (and - at least as fas as item '1' above is > concerned - for puposes other than mine!), but was unable > to find. I'll keep looking. What you're looking to do is much more developer level than user level. I'm guessing you're going to get pretty deep into the code, much further than I have any experience with. In another post, it looks like Tom Lane pointed you towards the relevant files, or at least a place where you can get your feet wet. Good luck! Michael Glaesemann grzm myrealbox com