Re: Missing Postgres Database

Hello all....

I'm doing some testing on a new server and there is no postgres
database. I can connect with the user postgres to the database
template1.

The version is 7.4.2

The initdb command creates template0 and template1. I've run initdb (as
user postgres) and initdb -D /var/lib/pgsql/data with the same results.

Any thoughts on how I can get out of this situation? Or am I doing
something wrong?

Thanks for any help (or even a direction to research) you can provide
me.....

Tim

On 10/4/07, Bruce, Tim <tim(dot)bruce(at)fiserv(dot)com> wrote:
> Hello all….
>
> I'm doing some testing on a new server and there is no postgres database. I
> can connect with the user postgres to the database template1.

Hi Tim,

I'm guessing the createdb command is what you're missing (examples at
the bottom):

http://www.postgresql.org/docs/7.4/interactive/app-createdb.html

Here is another example:

http://www.postgresql.org/docs/7.4/interactive/sql-createdatabase.html

I think that will get you a little bit further.

Regards,
Mark

From: Mark Wong [mailto:markwkm(at)gmail(dot)com]
Sent: Thursday, October 04, 2007 12:48 PM
To: Bruce, Tim
Cc: pdxpug(at)postgresql(dot)org
Subject: Re: [pdxpug] Missing Postgres Database

On 10/4/07, Bruce, Tim <tim(dot)bruce(at)fiserv(dot)com> wrote:
> Hello all....
>
> I'm doing some testing on a new server and there is no postgres
> database. I can connect with the user postgres to the database
template1.

Hi Tim,

I'm guessing the createdb command is what you're missing (examples at
the bottom):

http://www.postgresql.org/docs/7.4/interactive/app-createdb.html

Here is another example:

http://www.postgresql.org/docs/7.4/interactive/sql-createdatabase.html

I think that will get you a little bit further.

Regards,
Mark

Thanks Mark....

Based upon how the other servers were built, it appeared that the
postgres database was built (and populated with items) when it was
installed. Since I know almost nothing about PostgreSQL, but I get to
support it, I'm trying to figure out what should be there, what the
vendor provided and what I need to do to make it work correctly.

I guess I have more research to do to rebuild the postgres database with
these objects....or if they're even needed.

Tim

It seems like you have a nightmare to support...

One thing worth noting is that psql tries to log into a database with
the same name as the user running psql, but there is no normal reason
that anyone would have created a database called "postgres" in order
to match the user "postgres". It isn't necessary for normal
functioning at all, though the server wants to run as postgres
usually.

-W

On 10/4/07, Bruce, Tim <tim(dot)bruce(at)fiserv(dot)com> wrote:
> From: Webb Sprague [mailto:webb(dot)sprague(at)gmail(dot)com]
> Sent: Thursday, October 04, 2007 12:54 PM
> To: Bruce, Tim
> Subject: Re: [pdxpug] Missing Postgres Database
>
> >
> > The version is 7.4.2
>
> Why are you using such an old version?
>
> Also - the template databases are the only ones created automatically.
>
> -W
>
> ------
> Webb,
>
> That is one of the things I get to "fix" from the vendor. They also
> installed two different versions of the client utilities in different
> locations (and different accounts have access to one or the other). I'm
> trying to upgrade to a more recently patched version, but I need to
> build a "test" system that I can test my process against and then ensure
> there are no regressions in the vendor supplied code....
>
> Tim
>

Thanks for the help everyone - I think I've figured out what's going on.
Just to close the loop, I'll report what I've found.

When the initial install of Postgres occurs on SuSE Linux Enterprise
Server 9 (SLES 9) (I cannot confirm for other distributions), the
installation creates a database called "postgres" and populates it with
some initial objects. Whether they are needed or not, I'm not sure yet.

Subsequent installations (or re-installations), to include using initdb,
do not recreate this database (and other objects).

So this issue resulted from me testing my process and running initdb
multiple times (after deleting the data directory).

I can post later, the SQL for the postgres database (from SLES) if
anyone is interested in what is in there....assuming that my pg_dump
from another server includes all the same objects (what I'm trying to do
now).

Tim

-----Original Message-----
From: Webb Sprague [mailto:webb(dot)sprague(at)gmail(dot)com]
Sent: Thursday, October 04, 2007 1:14 PM
To: Bruce, Tim
Cc: Postgresql PDX_Users
Subject: Re: [pdxpug] Missing Postgres Database

It seems like you have a nightmare to support...

One thing worth noting is that psql tries to log into a database with
the same name as the user running psql, but there is no normal reason
that anyone would have created a database called "postgres" in order to
match the user "postgres". It isn't necessary for normal functioning at
all, though the server wants to run as postgres usually.

-W

On 10/4/07, Bruce, Tim <tim(dot)bruce(at)fiserv(dot)com> wrote:
> From: Webb Sprague [mailto:webb(dot)sprague(at)gmail(dot)com]
> Sent: Thursday, October 04, 2007 12:54 PM
> To: Bruce, Tim
> Subject: Re: [pdxpug] Missing Postgres Database
>
> >
> > The version is 7.4.2
>
> Why are you using such an old version?
>
> Also - the template databases are the only ones created automatically.
>
> -W
>
> ------
> Webb,
>
> That is one of the things I get to "fix" from the vendor. They also
> installed two different versions of the client utilities in different
> locations (and different accounts have access to one or the other).
> I'm trying to upgrade to a more recently patched version, but I need
> to build a "test" system that I can test my process against and then
> ensure there are no regressions in the vendor supplied code....
>
> Tim
>