Thread: Viewing Database Scheme
I'm trying to help the XRMS developers add postgres support ('cause that's
what I use). They've done well so far with help from other postgres users,
but now I've been asked to help getting the indices correct.
When installing the application I specified the database name as 'contacts'
(not very innovative or clever, but descriptive). When I open the database
with 'psql contacts' and ask to have the tables dumped (with \d), they go
streaming by on the display. Of course, the bash 'tee' or 'less' commands
don't work to allow me to capture the stream to a file or page through the
output.
I've looked in the 8.1 pdf manual and the Douglas*2 book without spotting
the command I need to get a list of all tables and their fields. When I've
developed postgres applications I know the schema so this has not been an
issue before.
Please pass me a clue stick on how to view all the tables in this
application.
TIA,
Rich
--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
use pg_dump --schema-only
---------- Original Message -----------
From: Rich Shepard <rshepard@appl-ecosys.com>
To: pgsql-general@postgresql.org
Sent: Sat, 28 Jan 2006 10:14:05 -0800 (PST)
Subject: [GENERAL] Viewing Database Scheme
> I'm trying to help the XRMS developers add postgres support ('cause that's
> what I use). They've done well so far with help from other postgres users,
> but now I've been asked to help getting the indices correct.
>
> When installing the application I specified the database name as 'contacts'
> (not very innovative or clever, but descriptive). When I open the database
> with 'psql contacts' and ask to have the tables dumped (with \d), they go
> streaming by on the display. Of course, the bash 'tee' or 'less' commands
> don't work to allow me to capture the stream to a file or page through the
> output.
>
> I've looked in the 8.1 pdf manual and the Douglas*2 book without spotting
> the command I need to get a list of all tables and their fields. When I've
> developed postgres applications I know the schema so this has not been an
> issue before.
>
> Please pass me a clue stick on how to view all the tables in this
> application.
>
> TIA,
>
> Rich
>
> --
> Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
> Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
> <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
------- End of Original Message -------
Can you use redirection.
On 1/28/06, Rich Shepard <rshepard@appl-ecosys.com> wrote:
> I'm trying to help the XRMS developers add postgres support ('cause that's
> what I use). They've done well so far with help from other postgres users,
> but now I've been asked to help getting the indices correct.
>
> When installing the application I specified the database name as 'contacts'
> (not very innovative or clever, but descriptive). When I open the database
> with 'psql contacts' and ask to have the tables dumped (with \d), they go
> streaming by on the display. Of course, the bash 'tee' or 'less' commands
> don't work to allow me to capture the stream to a file or page through the
> output.
>
> I've looked in the 8.1 pdf manual and the Douglas*2 book without spotting
> the command I need to get a list of all tables and their fields. When I've
> developed postgres applications I know the schema so this has not been an
> issue before.
>
> Please pass me a clue stick on how to view all the tables in this
> application.
>
> TIA,
>
> Rich
>
> --
> Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
> Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
> <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
--
Ezra Taylor
Rich Shepard <rshepard@appl-ecosys.com> writes: > When installing the application I specified the database name as 'contacts' > (not very innovative or clever, but descriptive). When I open the database > with 'psql contacts' and ask to have the tables dumped (with \d), they go > streaming by on the display. Of course, the bash 'tee' or 'less' commands > don't work to allow me to capture the stream to a file or page through the > output. You can either use 'pg_dump --schema-only' as another poster suggested, or use the '\o' command in psql. -Doug
Rich Shepard <rshepard@appl-ecosys.com> writes:
> When I open the database
> with 'psql contacts' and ask to have the tables dumped (with \d), they go
> streaming by on the display. Of course, the bash 'tee' or 'less' commands
> don't work to allow me to capture the stream to a file or page through the
> output.
Not sure why you say "of course" there. \d output is properly paginated
for me, and I believe for most people. What platform are you on, and
what do you have environment variable PAGER set to? Is the output of
plain old SELECT commands paginated for you?
> I've looked in the 8.1 pdf manual and the Douglas*2 book without spotting
> the command I need to get a list of all tables and their fields.
There is not a single command; you use queries against the system
catalogs for purposes like this. The "system catalogs" chapter of
the manual gives the details, but you can get a leg up by looking
at the queries psql uses for whatever form of \d seems closest to
what you want. Start psql with -E option to make it echo the
queries it's using.
regards, tom lane
On Sat, 28 Jan 2006, Tom Lane wrote: > Not sure why you say "of course" there. \d output is properly paginated > for me, and I believe for most people. What platform are you on, and > what do you have environment variable PAGER set to? Is the output of > plain old SELECT commands paginated for you? Tom, Running Slackware-10.2 with 'less' as the pager. Here's what I'm seeing: contacts=# \d | less \d: extra argument "less" ignored I can, however, run '\dt' and have it page normally. But, I cannot write that output to a file using redirection or the tee command: contacts=# \dt > xrms.tables No matching relations found. \dt: extra argument "xrms.tables" ignored and if I enter contacts=# \dt | tee > xrms.tables I see ... public | user_preference_type_options | table | rshepard public | users | table | rshepard (57 rows) \dt: extra argument "tee" ignored \dt: extra argument ">" ignored \dt: extra argument "xrms.tables" ignored Now, quitting postgres and reinvoking psql does fix the scroll-too-far problem. But, something's not quite correct here; to wit: contacts=# pg_dump --schema-only > xrms.txt; contacts-# ; ERROR: syntax error at or near "pg_dump" at character 1 LINE 1: pg_dump > There is not a single command; you use queries against the system catalogs > for purposes like this. The "system catalogs" chapter of the manual gives > the details, but you can get a leg up by looking at the queries psql uses > for whatever form of \d seems closest to what you want. Start psql with -E > option to make it echo the queries it's using. I'll be sure to read that section. The \dt and \di commands show me what I want, but I cannot redirect output to a file. What am I still missing, please? Thanks, Rich -- Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic" <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
On Sat, 28 Jan 2006, Doug McNaught wrote:
> You can either use 'pg_dump --schema-only' as another poster
> suggested, or use the '\o' command in psql.
When I try 'pg_dump --schema-only' I get a continuation prompt, even with a
semicolon at the end of the command line. A second semicolon produces this:
contacts=# pg_dump --schema-only;
contacts-# ;
ERROR: syntax error at or near "pg_dump" at character 1
LINE 1: pg_dump
^
If I use the \o command (with or without a file name) I get the command
prompt and no results.
Rich
--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
On Sat, 28 Jan 2006, Ezra Taylor wrote: > Can you use redirection. No. I get an error message. contacts=# \dt > xrms.tables No matching relations found. \dt: extra argument "xrms.tables" ignored Rich -- Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic" <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
On Jan 28, 2006, at 3:20 PM, Rich Shepard wrote: > contacts=# \d | less > \d: extra argument "less" ignored You can't do this via the psql prompt. A simple "\d" will output to the screen, automatically using your $PAGER if the output is too long to fit on your screen. > I can, however, run '\dt' and have it page normally. But, I > cannot write > that output to a file using redirection or the tee command: > > contacts=# \dt > xrms.tables > No matching relations found. > \dt: extra argument "xrms.tables" ignored Again, you can't use redirection via the psql prompt. But you can do it via your shell command line: $ psql -c "\dt" > xrms.tables Alternatively, you can use psql's "\o [FILE]" command to redirect query results to a file: contacts=# \o /tmp/xrms.tables contacts=# \dt contacts=# That'll send all output to /tmp/xrms.tables. > I'll be sure to read that section. The \dt and \di commands show > me what I > want, but I cannot redirect output to a file. What am I still missing, > please? You should also read the psql man page and the output of psql's "\h" command. eric
On Sat, 28 Jan 2006, Eric B. Ridge wrote: > You can't do this via the psql prompt. A simple "\d" will output to the > screen, automatically using your $PAGER if the output is too long to fit on > your screen. Eric, That's what I assumed; perhaps I misunderstood Tom Lane's "what do you mean 'ofcourse'?". > Again, you can't use redirection via the psql prompt. But you can do it > via your shell command line: > > $ psql -c "\dt" > xrms.tables Well, that doesn't seem to be working here, either: [rshepard@salmo ~]$ psql -c contacts "\dt" > xrms.tables psql: FATAL: database "\dt" does not exist [rshepard@salmo ~]$ psql "-c contacts \dt" > xrms.tables psql: FATAL: database "rshepard" does not exist [rshepard@salmo ~]$ psql -c contacts psql: FATAL: database "rshepard" does not exist > Alternatively, you can use psql's "\o [FILE]" command to redirect query > results to a file: > > contacts=# \o /tmp/xrms.tables > contacts=# \dt > contacts=# > > That'll send all output to /tmp/xrms.tables. This creates the file, but it's empty. I'm curious what's gone wrong here. Nothing seems to be working as it should. > You should also read the psql man page and the output of psql's "\h" command. I've done both and tried various combinations of syntax. For example: [rshepard@salmo ~]$ psql -d contacts -c pg_dump -o xrms.tables ERROR: syntax error at or near "pg_dump" at character 1 LINE 1: pg_dump All I get are error messages. Thanks, Rich -- Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic" <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
On Jan 28, 2006, at 4:12 PM, Rich Shepard wrote: Please keep replies on the mailing list. <snip> >> Again, you can't use redirection via the psql prompt. But you can >> do it >> via your shell command line: >> >> $ psql -c "\dt" > xrms.tables > > Well, that doesn't seem to be working here, either: > > [rshepard@salmo ~]$ psql -c contacts "\dt" > xrms.tables > psql: FATAL: database "\dt" does not exist > > [rshepard@salmo ~]$ psql "-c contacts \dt" > xrms.tables > psql: FATAL: database "rshepard" does not exist > > [rshepard@salmo ~]$ psql -c contacts > psql: FATAL: database "rshepard" does not exist That's because you've used the wrong syntax. $ psql contacts -c "\dt" > xrms.tables This is why I suggested you read the psql man page. >> Alternatively, you can use psql's "\o [FILE]" command to redirect >> query results to a file: > This creates the file, but it's empty. > > I'm curious what's gone wrong here. Nothing seems to be working > as it > should. Likely, the output is buffered. Did you try quitting psql (via \q) before checking the contents of the file. >> You should also read the psql man page and the output of psql's >> "\h" command. > > I've done both and tried various combinations of syntax. For > example: > > [rshepard@salmo ~]$ psql -d contacts -c pg_dump -o xrms.tables > ERROR: syntax error at or near "pg_dump" at character 1 > LINE 1: pg_dump Dude, "pg_dump" is not a psql command, nor is it a SQL command. It's a command-line program. You run it from your shell: $ pg_dump --schema-only pg_dump > xrms-schema.dmp > All I get are error messages. You continually do the wrong things. Read the man pages. Seriously. eric
On Jan 28, 2006, at 4:20 PM, Eric B. Ridge wrote: > Dude, "pg_dump" is not a psql command, nor is it a SQL command. > It's a command-line program. You run it from your shell: > > $ pg_dump --schema-only pg_dump > xrms-schema.dmp pardon my type-o. This should read: $ pg_dump --schema-only contacts > xrms-schema.dmp eric
Rich Shepard <rshepard@appl-ecosys.com> writes:
> On Sat, 28 Jan 2006, Eric B. Ridge wrote:
>
>> Again, you can't use redirection via the psql prompt. But you can do it
>> via your shell command line:
>> $ psql -c "\dt" > xrms.tables
>
> Well, that doesn't seem to be working here, either:
>
> [rshepard@salmo ~]$ psql -c contacts "\dt" > xrms.tables
> psql: FATAL: database "\dt" does not exist
>
> [rshepard@salmo ~]$ psql "-c contacts \dt" > xrms.tables
> psql: FATAL: database "rshepard" does not exist
>
> [rshepard@salmo ~]$ psql -c contacts
> psql: FATAL: database "rshepard" does not exist
Eric left off the database argument (which defaults to your user
name), which was a little misleading, but his syntax does work:
doug@blinky:~$ psql -c '\dt' gateway
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+----------
...
Redirecting to a file is left as an exercise to the reader.
>> Alternatively, you can use psql's "\o [FILE]" command to redirect
>> query results to a file:
>> contacts=# \o /tmp/xrms.tables
>> contacts=# \dt
>> contacts=#
>> That'll send all output to /tmp/xrms.tables.
>
> This creates the file, but it's empty.
When I do this, after exiting 'psql' the file is populated. It may be
a buffering issue, as another poster has said. What you can also do
is close the file by setting output back to the terminal:
gateway=# \o /tmp/foo
gateway=# \dt
gateway=# \!cat /tmp/foo <--- empty at this point
gateway=# \o <--- switch output to the terminal
gateway=# \!cat /tmp/foo <--- now it's there
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+----------
I'm really surprised that you managed to think 'pg_dump --schema_only'
is an SQL command. It's listed nowhere in the SQL syntax reference,
and is listed in the manual as one of the utility commands that are
run from the shell.
Your flailing about, randomly trying different argument combinations,
suggests that you aren't understanding what you read, and aren't
bothering to try to understand the error messages you get. You could
have figured out the first issue, certainly, by reading manpages and
error messages. The second one is a bit tricky unless you understand
Unix stdio buffering, which I wouldn't necessarily expect. So I'll
give you that one. :)
-Doug
Rich Shepard wrote:
> I'm trying to help the XRMS developers add postgres support ('cause
> that's
> what I use). They've done well so far with help from other postgres users,
> but now I've been asked to help getting the indices correct.
Hi Rich. Seems once again we're meeting in the same places. The
Internet sure seems small sometimes.
I saw all the other posts but you might look at dbwrench ( a Java
application ). Does a lot more than what you need ( plus after 30 days
costs approx. $150 ) but our lead programmer is using it to reverse
engineer a MySQL database he shoe-horned into PostgreSQL. He loves it.
It has several output formats.
<snip />
Rod
--
On Sat, 28 Jan 2006, Eric B. Ridge wrote: > That's because you've used the wrong syntax. > > $ psql contacts -c "\dt" > xrms.tables > > This is why I suggested you read the psql man page. Well, the man page installed shows the -c option is to specify one command, but when I try that: [rshepard@salmo ~]$ psql -c "\dt" > xrms.tables psql: FATAL: database "rshepard" does not exist That's why I tried specifying the database name. > Likely, the output is buffered. Did you try quitting psql (via \q) before > checking the contents of the file. Yes. > Dude, "pg_dump" is not a psql command, nor is it a SQL command. It's a > command-line program. You run it from your shell: > > $ pg_dump --schema-only pg_dump > xrms-schema.dmp [rshepard@salmo ~]$ pg_dump --schema-only pg_dump > xrms-schema.dmp pg_dump: [archiver (db)] connection to database "pg_dump" failed: FATAL: database "pg_dump" does not exist However, as the pg_dump man page specifies, the database name needs to be listed on the coammand line. So, $ pg_dump -s contacts > xrms.tables works just fine. I'm still curious why I cannot do this within psql. Rich -- Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic" <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
On Sat, 28 Jan 2006, Roderick A. Anderson wrote: > Hi Rich. Seems once again we're meeting in the same places. The Internet > sure seems small sometimes. Hello, again, Rod. > I saw all the other posts but you might look at dbwrench (a Java > application). Does a lot more than what you need (plus after 30 days costs > approx. $150) but our lead programmer is using it to reverse engineer a > MySQL database he shoe-horned into PostgreSQL. He loves it. There has been someone else working on this effort to move XRMS from strictly MySQL to PostgreSQL, and he's been at it longer. But, it's almost there now. The install script generated an error message with every create statement, but the tables were created anyway. But, the indices are wrong and the dates have screwy values. I printed the web page as a .ps file and sent that to the developers. They told me what the most likely cause was, so I'll go through the install.xml file and make their recommended changes, then re-run the install script. So this is very close to working and it's a one-shot deal for me. At OSCON last summer I spoke with the SugarCRM sales folks. They said that they received many requiests to add PostgreSQL support, but management wants to stick with MySQL. You'll recall from the other mail list that I had a time getting MySQL properly installed and configured just to run this one application. After trying it for a while I discovered that I really didn't like it. I think XRMS will do much better. On the other side of this thread, the command line pg_dump worked with the proper syntax. I think it was the two original replies (one with a command line solution, the other with a psql solution) that confused me. Why none of the psql commands are working for me I don't know. But, pg_dump works just fine. Thanks to everyone. Rich -- Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic" <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863