A Newbie's Primer to postgreSQL - Mailing 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