Thread: psql \l problem

psql \l problem

From
"Joe DeBrunner"
Date:
Hello,

For some reason I can no longer see all of the databases in Postgresql. When
I am in psql and type \l  I only see template1. I can \c to connect to the
databases but they will not list. Same thing with tables. The tables are
there, but I cannot \dt to list them.

Any idea what might cause this?

I'm running version 7.0.3-2 on RedHat 7. BTW - I'm working on upgrading to
7.1 but the rpm dependencies are killing me!

Thanks,

Joe



Re: psql \l problem

From
Tom Lane
Date:
"Joe DeBrunner" <jdebrunner@NO_SPAMyahoo.com> writes:
> For some reason I can no longer see all of the databases in Postgresql. When
> I am in psql and type \l  I only see template1. I can \c to connect to the
> databases but they will not list. Same thing with tables. The tables are
> there, but I cannot \dt to list them.
> Any idea what might cause this?

You probably deleted the pg_shadow entries for the other databases'
owners.

            regards, tom lane

What may be the url we need to use to connect the Postgres Database with Java

From
"Moovarkku Mudhalvan"
Date:
Hi friends,
    Greetings. if we use the oracle as backend with Java programming we use
the url like

    jdbc.oracle.thin@ipaddress
like this do we have any url need to be used in Postgres if so let me know.

The Driver i got it from the Package...

Thanks
Mudhalvan M.M


Re: What may be the url we need to use to connect the Postgres Database with Java

From
"Nick Fankhauser"
Date:
 jdbc.oracle.thin@ipaddress
> like this do we have any url need to be used in Postgres if so
> let me know.

jdbc:postgresql:database
jdbc:postgresql://host/database
jdbc:postgresql://host:port/database



if left out, host defaults to localhost- this is where you put your IP
address

if left out, port defaults to 5432

For example:

jdbc:postgresql://199.199.199.199/test

will connect you to the database named "test" on the host "199.199.199.199"

You will also need to have the database running with tcpip_socket set to "1"
or true in the postgresql.conf file, and set up authorization in pg_hba.conf
(search on these filenames in the interactive documentation for more.

-Nick

--------------------------------------------------------------------------
Nick Fankhauser  nickf@ontko.com  Phone 1.765.935.4283  Fax 1.765.962.9788
Ray Ontko & Co.     Software Consulting Services     http://www.ontko.com/


Re: psql \l problem

From
"Joe DeBrunner"
Date:
Tom,

Thanks for the reply.

I checked the pg_shadow table and all of the users I have added are still
there. Can't tell if the usesysid for each of them has changed or not. Is
there any way to determine the database owners id as listed in pg_database?
I've tried using the postgres id and granting superuser to one of the ids
but still can't list anything on the other databases. Other than being able
to connect to them there is nothing to indicate they exist anywhere.

I have also used phpPgAdmin to try to find some trace of the databases. No
luck unless I manually type the database in the URL. Then it lists all of
the details. For example, if I use type in db_details.php?server=1&db=horde
I can see details of the database. It lists postgres as the owner, but
postgres can't see it in a normal psql session or in a normal phpPgAdmin
view. Also, it says the database has no tables.

Another example, I can "\c states" to connect to an unlisted database and
then "select * from statename" to list the contents of the table, but it
won't list the table using \dt or using phpPgAdmin.

Can't think of any reason the ids would have changed or been deleted and
then readded.

I just hand entered the missing databases into pg_database and can now see
the databases, but they don't have any tables. Very confusing!

Joe

"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:1218.1002916236@sss.pgh.pa.us...
> "Joe DeBrunner" <jdebrunner@NO_SPAMyahoo.com> writes:
> > For some reason I can no longer see all of the databases in Postgresql.
When
> > I am in psql and type \l  I only see template1. I can \c to connect to
the
> > databases but they will not list. Same thing with tables. The tables are
> > there, but I cannot \dt to list them.
> > Any idea what might cause this?
>
> You probably deleted the pg_shadow entries for the other databases'
> owners.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



Re: What may be the url we need to use to connect the Postgres Database with Java

From
"Moovarkku Mudhalvan"
Date:
Hi Nick,

   Greetings. Thanks for ur mail.

    Permission denied when i go for the backup. The steps i am doing is

1. Logins as root
2. su postgres
3. pg_dump Databasename redirected to file name...

   Let me know why i am getting this error

Thanks
Mudhalvan M.M

----- Original Message -----
From: "Nick Fankhauser" <nickf@ontko.com>
To: "Moovarkku Mudhalvan" <mudhalvan@vasunas.com>;
<pgsql-admin@postgresql.org>
Sent: Monday, October 15, 2001 6:44 PM
Subject: Re: [ADMIN] What may be the url we need to use to connect the
Postgres Database with Java


> jdbc.oracle.thin@ipaddress
> > like this do we have any url need to be used in Postgres if so
> > let me know.
>
> jdbc:postgresql:database
> jdbc:postgresql://host/database
> jdbc:postgresql://host:port/database
>
>
>
> if left out, host defaults to localhost- this is where you put your IP
> address
>
> if left out, port defaults to 5432
>
> For example:
>
> jdbc:postgresql://199.199.199.199/test
>
> will connect you to the database named "test" on the host
"199.199.199.199"
>
> You will also need to have the database running with tcpip_socket set to
"1"
> or true in the postgresql.conf file, and set up authorization in
pg_hba.conf
> (search on these filenames in the interactive documentation for more.
>
> -Nick
>
> --------------------------------------------------------------------------
> Nick Fankhauser  nickf@ontko.com  Phone 1.765.935.4283  Fax 1.765.962.9788
> Ray Ontko & Co.     Software Consulting Services     http://www.ontko.com/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly


Re: What may be the url we need to use to connect the Postgres Database with Java

From
"Nick Fankhauser"
Date:
>     Permission denied when i go for the backup. The steps i am doing is
>
> 1. Logins as root
> 2. su postgres
> 3. pg_dump Databasename redirected to file name...
>
>    Let me know why i am getting this error


Maybe the postgres user does not have permission to create a file in this
directory.

If this is true, the error you are getting would be from the operating
system, not from pg_dump.

To test this, try the same steps, but instead of starting pg_dump, do:
"touch test" to see if the postgres user can create a file. If you get an
error it is simply a directory permission problem.

-Nick

--------------------------------------------------------------------------
Nick Fankhauser  nickf@ontko.com  Phone 1.765.935.4283  Fax 1.765.962.9788
Ray Ontko & Co.     Software Consulting Services     http://www.ontko.com/


Convertion of Oracle Database to Postgresql

From
"Moovarkku Mudhalvan"
Date:
Hi

   Greetings. Thanks i was able to take the Backup. As u said it is only
problem with the OS permission to that particular folder.
    Now i am into new thing .

    Did any one aware of getting entire database from Oracle to Postgresql.

    I got some link through the Google referring some perl programming but i
don't have that much experience on perl programming can u please any one go
throught it and let me know
http://www.samse.fr/GPL/ora2pg/ora2pg.html

    Other than this if some body have any other ideas let me know.

Thanks
Mudhalvan M.M


Re: psql \l problem

From
Tom Lane
Date:
"Joe DeBrunner" <jdebrunner@NO_SPAMyahoo.com> writes:
> I checked the pg_shadow table and all of the users I have added are still
> there. Can't tell if the usesysid for each of them has changed or not. Is
> there any way to determine the database owners id as listed in pg_database?

select * from pg_database

            regards, tom lane