Re: [ADMIN] Welcome to the pgsql-admin list!

2008-07-23 Thread Gibson Chimhamhiwa
Hi All, I have a simple table below and I want to be able to traverse it using SQL. Can somebody please advise me how I can do this in postgreSQL. CREATE TABLE grouping ( grouping_id int4 NOT NULL, version int4, parent_grouping_id int4, topic_topic_id int4, CONSTRAINT grouping_pkey PRIM

Re: [ADMIN] Welcome to the pgsql-admin list!

2008-07-23 Thread Kevin Grittner
>>> "Gibson Chimhamhiwa" <[EMAIL PROTECTED]> wrote: > I have a simple table below and I want to be able to traverse it using SQL. > Can somebody please advise me how I can do this in postgreSQL. > > CREATE TABLE grouping SELECT * FROM grouping; If that isn't what you're looking for, we need

[ADMIN] Query a list of tables

2008-07-23 Thread Campbell, Lance
What is the SQL to do the following: 1) Return a list of all possible SCHEMA names within a database. 2) For a given SCHEMA return the list of all table names. Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois

Re: [ADMIN] Query a list of tables

2008-07-23 Thread Scott Marlowe
On Wed, Jul 23, 2008 at 8:56 AM, Campbell, Lance <[EMAIL PROTECTED]> wrote: > What is the SQL to do the following: > > > > 1) Return a list of all possible SCHEMA names within a database. > > 2) For a given SCHEMA return the list of all table names. You can either do it with the INFORMAT

Re: [ADMIN] Query a list of tables

2008-07-23 Thread Gibson Chimhamhiwa
Hi All, I have a postgreSQl database and just wan to know how I can get to its data dictionary.

Re: [ADMIN] Query a list of tables

2008-07-23 Thread Campbell, Lance
Is there a list somewhere of the system catalog tables and examples of querying it for administrative reasons? -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 23, 2008 10:02 AM To: Campbell, Lance Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN]

Re: [ADMIN] Query a list of tables

2008-07-23 Thread Gibson Chimhamhiwa
Hi, Under Schema I have; information_schema pg_catalog pg_toast pg_toast_temp_1 pg_temp_1 and then public which contains the tables with data for my database. On Wed, Jul 23, 2008 at 5:11 PM, Campbell, Lance <[EMAIL PROTECTED]> wrote: > Is there a list somewhere of the system catalog tables an

[ADMIN] Catalog

2008-07-23 Thread Sergio Gabriel Rodriguez
Hi for all, is possible to get PostgreSQL Catalog EER diagram? where can I get it? regards Sergio Gabriel Rodriguez -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Query a list of tables

2008-07-23 Thread Milen A. Radev
Campbell, Lance написа: Is there a list somewhere of the system catalog tables and examples of querying it for administrative reasons? [...] http://www.postgresql.org/docs/current/static/information-schema.html http://www.postgresql.org/docs/current/static/catalogs.html This mostly means you n

Re: [ADMIN] Query a list of tables

2008-07-23 Thread Campbell, Lance
Solution: I found that by doing the following query you can get a list of all schemas or namespaces: select distinct schemaname from pg_tables where schemaname not in ('information_schema', 'pg_catalog'); In order to select all of the tables within a schema or namespace: select tabl

Re: [ADMIN] Catalog

2008-07-23 Thread Travis Smith
There are tools that allow you to reverse engineer schemas.. or to generate ERD's .. visio comes to mind. It works on odbc connections. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sergio Gabriel Rodriguez Sent: Wednesday, July 23, 2008 10:32 AM To

Re: [ADMIN] conditional logging based on client

2008-07-23 Thread Shane Ambler
Rajesh Kumar Mallah wrote: On Tue, Jul 22, 2008 at 5:54 PM, Achilleas Mantzios The original objective is that we should be able to know what all hand made SQLs have been executed in past (which can date back as long as 1 year even) You have to adjust log_statement in your postgresql.conf ho

Re: [ADMIN] conditional logging based on client

2008-07-23 Thread Scott Marlowe
2008/7/22 Rajesh Kumar Mallah <[EMAIL PROTECTED]>: > On Tue, Jul 22, 2008 at 5:54 PM, Achilleas Mantzios > <[EMAIL PROTECTED]> wrote: >> Στις Tuesday 22 July 2008 15:17:42 ο/η Rajesh Kumar Mallah έγραψε: >>> Hi, >>> >>> We want to log all SQLs that has been executed by using psql client. >>> we do

Re: [ADMIN] Query a list of tables

2008-07-23 Thread Igor Neyman
First you get to Postgresql docs, from there you get anywhere you want. Igor From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gibson Chimhamhiwa Sent: Wednesday, July 23, 2008 11:10 AM To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Query a lis

Re: [ADMIN] conditional logging based on client

2008-07-23 Thread Tom Lane
"Scott Marlowe" <[EMAIL PROTECTED]> writes: > 2008/7/22 Rajesh Kumar Mallah <[EMAIL PROTECTED]>: >> any hit on performance is not desired. >> is it possible to alter logging behaviour just for the >> psql session ? > Set log_statement='ddl' in your postgresql.conf and pgsql will log all > ddl that

[ADMIN] -O to remove owner

2008-07-23 Thread Marc Fromm
I am currently running these to commands in scripts to backup the postgresql databases. /usr/bin/pg_dumpall -ch 127.0.0.1 -U postgres | gzip > "$backup_dir/postgresql-all-$timeslot-databases.gz" /usr/bin/pg_dump $i -ch 127.0.0.1 -U postgres | gzip > "$backup_dir/postgresql-$i-$timeslot-databas