Thread: Re: CREATEDB Where ??

Peter Moscatt <pmoscatt@bigpond.net.au> wrote in message news:<4x9f7.126086$Xr6.689318@news-server.bigpond.net.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@bigpond.net.au> wrote in message
> news:<4x9f7.126086$Xr6.689318@news-server.bigpond.net.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@bigpond.net.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@bigpond.net.au> wrote in message
> > news:<4x9f7.126086$Xr6.689318@news-server.bigpond.net.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@postgresql.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

Peter Moscatt <pmoscatt@bigpond.net.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