CREATEDB Where ??

I am pretty new to PostgreSQL so please bare with me :-)

When issuing the CREATEDB MyDb then creating some tables with CREATE
TABLE, I then go back and do a search for the file I have just created
(MyDb) but can't find the physical file.

Does one actually exist ??

Pete

Peter Moscatt <pmoscatt(at)bigpond(dot)net(dot)au> wrote in message news:<4x9f7(dot)126086$Xr6(dot)689318(at)news-server(dot)bigpond(dot)net(dot)au>...
> I am pretty new to PostgreSQL so please bare with me :-)
>
> When issuing the CREATEDB MyDb then creating some tables with CREATE
> TABLE, I then go back and do a search for the file I have just created
> (MyDb) but can't find the physical file.
>
> Does one actually exist ??
>
> Pete

Sure it does. The problem you are having is that since the
implementation of TOAST in PG 7.1, all of the db and table names are
represented by numbers in the physical file system
(usr/local/pgsql/data/base). So if you tried to do an 'ls' or 'find'
for the name of your database, it probably wouldn't show up. However,
just do a 'psql {db_name}' (where {db_name} is the name of your
database) and you'll see that everything is kosher.

To translate the oid numbers to their respective names, use the
oid2name function found in the /contrib under your Postgres source
code.

-Tony

Thanks Tony... yes that helps explain why I am not seeing what I expected
to see.

Right..... If I was developing an application, say with Python and I
needed to transport my created database and make it part of an installation
process (create a tar ball with all needed components), do I just include
the /usr/local/pgsql/data directory as part of my dist ??

Pete

Tony Reina wrote:

> Peter Moscatt <pmoscatt(at)bigpond(dot)net(dot)au> wrote in message
> news:<4x9f7(dot)126086$Xr6(dot)689318(at)news-server(dot)bigpond(dot)net(dot)au>...
>> I am pretty new to PostgreSQL so please bare with me :-)
>>
>> When issuing the CREATEDB MyDb then creating some tables with CREATE
>> TABLE, I then go back and do a search for the file I have just created
>> (MyDb) but can't find the physical file.
>>
>> Does one actually exist ??
>>
>> Pete
>
> Sure it does. The problem you are having is that since the
> implementation of TOAST in PG 7.1, all of the db and table names are
> represented by numbers in the physical file system
> (usr/local/pgsql/data/base). So if you tried to do an 'ls' or 'find'
> for the name of your database, it probably wouldn't show up. However,
> just do a 'psql {db_name}' (where {db_name} is the name of your
> database) and you'll see that everything is kosher.
>
> To translate the oid numbers to their respective names, use the
> oid2name function found in the /contrib under your Postgres source
> code.
>
> -Tony
>

Peter Moscatt <pmoscatt(at)bigpond(dot)net(dot)au> writes:

> I am pretty new to PostgreSQL so please bare with me :-)
>
> When issuing the CREATEDB MyDb then creating some tables with CREATE
> TABLE, I then go back and do a search for the file I have just created
> (MyDb) but can't find the physical file.
>
> Does one actually exist ??

Yes, they're named by OID (integer) in $PGDATA rather than by database
name. There's a reason for this so don't complain about it. ;)

-Doug
--
Free Dmitry Sklyarov!
http://www.freesklyarov.org/

We will return to our regularly scheduled signature shortly.

Yes and no :-). The files were created but all postgres data files are now
idententified by numbers (oids I think), so you will not find a file or
directory anywhere in your filesystem named "mydb", or "mytable".

----- Original Message -----
From: "Peter Moscatt" <pmoscatt(at)bigpond(dot)net(dot)au>
To: <pgsql-hackers(at)postgresql(dot)org>
Sent: Friday, August 17, 2001 9:05 AM
Subject: [HACKERS] CREATEDB Where ??

> I am pretty new to PostgreSQL so please bare with me :-)
>
> When issuing the CREATEDB MyDb then creating some tables with CREATE
> TABLE, I then go back and do a search for the file I have just created
> (MyDb) but can't find the physical file.
>
> Does one actually exist ??
>
> Pete
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl

Peter Moscatt <pmoscatt(at)bigpond(dot)net(dot)au> writes:

> Thanks Tony... yes that helps explain why I am not seeing what I expected
> to see.
>
> Right..... If I was developing an application, say with Python and I
> needed to transport my created database and make it part of an installation
> process (create a tar ball with all needed components), do I just include
> the /usr/local/pgsql/data directory as part of my dist ??

It would be a MUCH better idea instead to include an SQL script that's
run automatically by the install process.

It's more flexible and more robust against version skew etc.

-Doug
--
Free Dmitry Sklyarov!
http://www.freesklyarov.org/

We will return to our regularly scheduled signature shortly.

Hi Pete,

Would it be appropriate to do a SQL dump of the created database via
pg_dump, then reload it during the installation vi psql or the COPY
command? If you include the whole data/ subdirectory, you'll also get
the WAL logfiles and everything, which you probably don't need.

Of course, you'll need to create a process for updating the *.conf
(postgresql.conf, pg_ident.conf, pg_hba.conf) files correctly too.
Perl, sed, etc, would all be a starting point here.

Regards and best wishes,

Justin Clift

Peter Moscatt wrote:
>
> Thanks Tony... yes that helps explain why I am not seeing what I expected
> to see.
>
> Right..... If I was developing an application, say with Python and I
> needed to transport my created database and make it part of an installation
> process (create a tar ball with all needed components), do I just include
> the /usr/local/pgsql/data directory as part of my dist ??
>
> Pete
>
> Tony Reina wrote:
>
> > Peter Moscatt <pmoscatt(at)bigpond(dot)net(dot)au> wrote in message
> > news:<4x9f7(dot)126086$Xr6(dot)689318(at)news-server(dot)bigpond(dot)net(dot)au>...
> >> I am pretty new to PostgreSQL so please bare with me :-)
> >>
> >> When issuing the CREATEDB MyDb then creating some tables with CREATE
> >> TABLE, I then go back and do a search for the file I have just created
> >> (MyDb) but can't find the physical file.
> >>
> >> Does one actually exist ??
> >>
> >> Pete
> >
> > Sure it does. The problem you are having is that since the
> > implementation of TOAST in PG 7.1, all of the db and table names are
> > represented by numbers in the physical file system
> > (usr/local/pgsql/data/base). So if you tried to do an 'ls' or 'find'
> > for the name of your database, it probably wouldn't show up. However,
> > just do a 'psql {db_name}' (where {db_name} is the name of your
> > database) and you'll see that everything is kosher.
> >
> > To translate the oid numbers to their respective names, use the
> > oid2name function found in the /contrib under your Postgres source
> > code.
> >
> > -Tony
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

> Yes and no :-). The files were created but all postgres data files are now
> idententified by numbers (oids I think), so you will not find a file or
> directory anywhere in your filesystem named "mydb", or "mytable".

/contrib/oid2name in 7.1.X does the mapping.

>
> ----- Original Message -----
> From: "Peter Moscatt" <pmoscatt(at)bigpond(dot)net(dot)au>
> To: <pgsql-hackers(at)postgresql(dot)org>
> Sent: Friday, August 17, 2001 9:05 AM
> Subject: [HACKERS] CREATEDB Where ??
>
>
> > I am pretty new to PostgreSQL so please bare with me :-)
> >
> > When issuing the CREATEDB MyDb then creating some tables with CREATE
> > TABLE, I then go back and do a search for the file I have just created
> > (MyDb) but can't find the physical file.
> >
> > Does one actually exist ??
> >
> > Pete
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://www.postgresql.org/search.mpl
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Peter Moscatt <pmoscatt(at)bigpond(dot)net(dot)au> writes:
> Right..... If I was developing an application, say with Python and I
> needed to transport my created database and make it part of an installation
> process (create a tar ball with all needed components), do I just include
> the /usr/local/pgsql/data directory as part of my dist ??

No --- a tar dump of your directory will be quite useless to anyone else
on a different platform, and even those on the same platform would
likely not want to blow away their databases and replace 'em with yours.

Instead, use pg_dump to create an SQL script that can be loaded into an
existing database installation.

regards, tom lane