A Newbie's Primer to postgreSQL - Mailing list pgsql-novice
From | Hugh Esco |
---|---|
Subject | A Newbie's Primer to postgreSQL |
Date | |
Msg-id | 5.1.0.14.0.20021123134434.024e7170@petra.cagreens.org Whole thread Raw |
List | pgsql-novice |
My friend who built my last three computers and has generally been helpful to me as I have mounted the learning curve into linux, mySQL, perl and probably other subjects has deflected my questions about postgreSQL so far. This is a project for a later day in his mind. I just wrote him a note I thought would be useful for folks on this list about what I've learned in my first few weeks at this. Hope you find it useful, too. -- Hugh Esco <my friend>: I know you plan to tackle the learning curve for postgreSQL in January. I've been at it now, pretty non-stop (without benefit of buying a book), for at least three weeks. Let me clue you in on a couple of items that might ramp you up the learning curve, some. MySQL is to MySQLFront, as PostgreSQL is to http://www.pgadmin.org for Win32 and phpPgAdmin at SourceForge (Apache / php -- cross Platform) and other linux, mac and win tools linked from: http://ems-hitech.com/pgsqlutils/download.phtml http://gborg.postgresql.org/browse.php?81 The lists offered at the www.postgresql.org (.com?) site are very helpful and include many developers who regularly take the time to help us newbies along. I have learned a lot from them and am archiving the traffic there on my machine for later reference. Also available there (including the .pdf's I downloaded and I'm sure other formats) are a number of distinct useful manuals for administrators, developers, programmers and users, plus one called reference and another called tutorial. I've read most of the Admin guide. Haven't cracked the others yet. I also found the note below very helpful. Hope this might get you started in good style. -- Hugh Esco >Subject: Re: [ADMIN] How are postgreSQL database files structured? >From: Oliver Elphick <olly@lfix.co.uk> >To: Hugh Esco <hesco@greens.org> >Cc: pgsql-admin@postgresql.org > >On Sat, 2002-11-23 at 04:12, Hugh Esco wrote: > > > But when I take a look at /usr/local/pgsql/data/base, I am baffled by its > > contents. I hear that the directory at: /usr/local/pgsql/data/base/1 is > > the template1 database. But I do not know where the template0 database > > is. And I certainly do not understand how testtest gets named 16555, much > > less what the numbered directories and files subordinate to that directory > > have to do with anything real in the world. > >The files in $PGDATA/base are named by the oid of the database record in >pg_database, like this: > >postgres@linda:~$ cd $PGDATA >postgres@linda:/usr1/postgres/data$ ls -l base >total 33 >drwx------ 2 postgres postgres 2048 Nov 23 04:01 1 >drwx------ 2 postgres postgres 2048 Nov 23 04:00 1063179 >drwx------ 2 postgres postgres 2048 Nov 23 04:00 1063190 >drwx------ 2 postgres postgres 3072 Nov 23 04:00 1063237 >drwx------ 2 postgres postgres 3072 Nov 23 04:00 1063515 >drwx------ 2 postgres postgres 3072 Nov 23 04:01 1064226 >drwx------ 2 postgres postgres 3072 Nov 23 04:01 1064465 >drwx------ 2 postgres postgres 2048 Nov 18 22:39 16975 >drwx------ 2 postgres postgres 2048 Nov 23 04:00 16976 >drwx------ 2 postgres postgres 3072 Nov 23 04:00 16983 >drwx------ 3 postgres postgres 6144 Nov 23 04:00 3884888 >drwx------ 2 postgres postgres 2048 Nov 23 04:01 4989386 >postgres@linda:/usr1/postgres/data$ psql template1 >Welcome to psql 7.3rc1, the PostgreSQL interactive terminal. >... >template1=# select oid,datname from pg_database order by oid; > oid | datname >---------+---------------- > 1 | template1 > 16975 | template0 > 16976 | NEUROMR > 16983 | accounts > 1063179 | comanagers > 1063190 | genealogy > 1063237 | junk > 1063515 | lfix > 1064226 | sql_ledger > 1064465 | stjohns > 3884888 | bray > 4989386 | space database >(12 rows) > >Similarly, inside $PGDATA/base/<dbdir>, relation files are named by >their oids in pg_class: > >stjohns=# select oid,relname from pg_class where oid > 1000000 and oid < >2000000 order by oid limit 5; > oid | relname >---------+------------------------ > 1064466 | members > 1064470 | pg_toast_1064466 > 1064472 | pg_toast_1064466_index > 1064473 | members_pkey > 1064475 | perms >(5 rows) > >stjohns=# \! cd $PGDATA/base/1064465 ; ls 10* | head -5 >1064466 >1064470 >1064472 >1064473 >1064475 > > >pg_class has a column, relfilenode, which almost invariably has the same >value as the oid; I'm not sure whether, should they differ, the file >would be named relfilenode or oid, though I would presume the former. > >-- >Oliver Elphick Oliver.Elphick@lfix.co.uk >Isle of Wight, UK http://www.lfix.co.uk/oliver >GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C > ======================================== > "The LORD is nigh unto all them that call upon him, to > all that call upon him in truth." > Psalms 145:18
pgsql-novice by date: