Re: [ADMIN] how to view all database,tables,users
Apart from select statements you can use the following commands on psql
prompt -
\d [NAME] describe table, index, sequence, or view
\d{t|i|s|v|S} [PATTERN] (add + for more detail)
list tables/indexes/sequences/views/system tables
\da [PATTERN] list aggregate functions
\db [PATTERN] list tablespaces (add + for more detail)
\dc [PATTERN] list conversions
\dClist casts
\dd [PATTERN] show comment for object
\dD [PATTERN] list domains
\df [PATTERN] list functions (add + for more detail)
\dg [PATTERN] list groups
\dn [PATTERN] list schemas (add + for more detail)
\do [NAME] list operators
\dllist large objects, same as \lo_list
\dp [PATTERN] list table, view, and sequence access privileges
\dT [PATTERN] list data types (add + for more detail)
\du [PATTERN] list users
\l list all databases (add + for more detail)
\z [PATTERN] list table, view, and sequence access privileges (same as
\dp)
Hope this would answer your query.
From: Mondi Ravi [EMAIL PROTECTED]
To: pgsql-admin@postgresql.org
Subject: [ADMIN] how to view all database,tables,users
Date: Wed, 23 May 2007 14:38:58 +0545
i can't find out command to show all databases,tables,users and
integrity constraints relationships;
yours sincerely
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
_
Like the way Microsoft Office Outlook works? Youll love Windows Live
Hotmail.
http://imagine-windowslive.com/hotmail/?locale=en-usocid=TXT_TAGHM_migration_HM_mini_outlook_0507
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [ADMIN] [GENERAL] OIDs - file objects, are damaged by PostgreSQL.
Purusothaman A wrote: Richard Huxton, Thanks for your detailed reply. I am maintaining various database of same kind in postgresql. Here I have shown various corrupted last line of output of select * from pg_largeobject where oid = xx; in 5 databases. I have used '\o e:\\filename.xml' before executing query and inspected the output in that file. Kindly look at the end of line in all 5 outputs. First 3 output shows few missing characters. But last 2 output is the perfect one. 1. Output of SFRS2 database: 2. Output of SFRS1 database: 3. Output of FASP_AVT database: Let's have a look at the last few bytes of these three rows. For example, below is a very short file that finishes with endNL. SELECT loid,pageno,length(data),encode(data, 'hex') FROM pg_largeobject WHERE loid=340161 AND pageno=0; loid | pageno | length | encode +++ 340161 | 0 | 31 | 54686973206973206120746573742066696c650a6c696e6520320a656e640a (1 row) It doesn't seem to be random garbage at the end of your rows, so lets see what it is. Oh - things we can rule out: 1. Index corruption. We're not missing the last row. Adding the length check above will show us if the row is truncated or full of spaces. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [ADMIN] [GENERAL] OIDs - file objects, are damaged by PostgreSQL.
Richard Huxton, In my system also its 2048 bytes chunk. The below output shows clearly that the last chunk differs in its length. You might have noticed in my previous mail that the string /haarcascade_frontalface_default\015\012/opencv_storage\015\012 is missing some characters in SFRS2, SFRS1 and FASP_AVT database outputs. Have a look at it, In this mail I have bolded the corrucpted part. sfrs2= select loid, pageno, length(data) from pg_largeobject where loid = 101177 and pageno = 630; loid | pageno | length ++ 101177 |630 |181 (1 row) sfrs1= select loid, pageno, length(data) from pg_largeobject where loid = 41642 and pageno = 630; loid | pageno | length ---++ 41642 |630 |193 (1 row) fasp_avt= select loid, pageno, length(data) from pg_largeobject where loid = 101800 and pageno = 630; loid | pageno | length ++ 101800 |630 |193 (1 row) sfrs= select loid, pageno, length(data) from pg_largeobject where loid = 24038 and pageno = 630; loid | pageno | length ---++ 24038 |630 |205 (1 row) fasp_test= select loid, pageno, length(data) from pg_largeobject where loid = 106310 and pageno = 630; loid | pageno | length ++ 106310 |630 |205 (1 row) 1. Output of SFRS2 database: 101177 |630 | ight_val/_/_/trees\015\012 stage_threshold- 2.9928278923034668/stage_threshold\015\012 parent23/parent\015\012 next-1/next/_/stages/haarcascade_frontalface_defau (631 rows) 2. Output of SFRS1 database: - 41642 |630 | ight_val/_/_/trees\015\012 stage_threshold- 2.9928278923034668 /stage_threshold\015\012 parent23/parent\015\012 next-1/next/_/stages /haarcascade_frontalface_default\015\012/openc (631 rows) 3. Output of FASP_AVT database: -- 101800 |630 | ight_val/_/_/trees\015\012 stage_threshold- 2.9928278923034668/stage_threshold\015\012 parent23/parent\015\012 next-1/next/_/stages /haarcascade_frontalface_default\015\012/openc (631 rows) 4. Output of SFRS database: (not yet corrupted) -- 24038 |630 | ight_val/_/_/trees\015\012 stage_threshold- 2.9928278923034668/stage_threshold\015\012 parent23/parent\015\012 next-1/next/_/stages /haarcascade_frontalface_default\015\012/opencv_storage\015\012 (631 rows) 5. Output of FASP_TEST database: (Not yet corrupted) 106310 |630 | ight_val/_/_/trees\015\012 stage_threshold- 2.9928278923034668/stage_threshold\015\012 parent23/parent\015\012 next-1/next/_/stages /haarcascade_frontalface_default\015\012/opencv_storage\015\012 (631 rows) Have you noticed the bolded strings? What could have caused to damage that part? Is it bug in Posstgresql or is they any way to solve this problem. Thanks. Regards, Purusothaman A On 5/24/07, Richard Huxton [EMAIL PROTECTED] wrote: Purusothaman A wrote: Richard Huxton, Thanks for your detailed reply. I am maintaining various database of same kind in postgresql. Here I have shown various corrupted last line of output of select * from pg_largeobject where oid = xx; in 5 databases. I have used '\o e:\\filename.xml' before executing query and inspected the output in that file. Kindly look at the end of line in all 5 outputs. First 3 output shows few missing characters. But last 2 output is the perfect one. 1. Output of SFRS2 database: 2. Output of SFRS1 database: 3. Output of FASP_AVT database: Let's have a look at the last few bytes of these three rows. For example, below is a very short file that finishes with endNL. SELECT loid,pageno,length(data),encode(data, 'hex') FROM pg_largeobject WHERE loid=340161 AND pageno=0; loid | pageno | length | encode +++ 340161 | 0 | 31 | 54686973206973206120746573742066696c650a6c696e6520320a656e640a (1 row) It doesn't seem to be random garbage at the end of your rows, so lets see what it is. Oh - things we can rule out: 1. Index corruption. We're not missing the last row. Adding the length check above will show us if the row is truncated or full of spaces. -- Richard Huxton Archonet Ltd -- http://PurusothamanA.wordpress.com/
Re: [ADMIN] [GENERAL] OIDs - file objects, are damaged by PostgreSQL.
Purusothaman A wrote: Richard Huxton, In my system also its 2048 bytes chunk. The below output shows clearly that the last chunk differs in its length. You might have noticed in my previous mail that the string /haarcascade_frontalface_default\015\012/opencv_storage\015\012 is missing some characters in SFRS2, SFRS1 and FASP_AVT database outputs. Have a look at it, In this mail I have bolded the corrucpted part. Yep, spotted that. Hence asking for the length, and it looks like... loid | pageno | length ++ 101177 |630 |181 41642 |630 |193 101800 |630 |193 24038 |630 |205 The data has just been truncated rather than corrupted. Is it bug in Posstgresql or is they any way to solve this problem. Well, something is setting the length too short on these entries. Can you tell me whether the following statements are all correct? 1. Each database is on a separate machine (that would rule out a hardware problem) 2. All systems are running on Windows 2000/XP/2003. 3. All systems are version 8.2.4 (if not, please give details) 4. You upload the data with lo_import (once) and download it with lo_export (many times) and don't alter it in-between. 5. Where the data has been truncated, you know for a fact you downloaded it OK before (or do you just suspect it was OK?) If you're not changing the data, and you know it was OK at some point then there are only two things I can think of: 1. A hardware problem (which we might rule out above) 2. A bug in PostgreSQL's vacuum code Nothing else should be writing to those blocks. If it looks like a bug in vacuum, we can try to reproduce it, and also examine the actual contents of the on-disk files (to see if the data is there on the disk or not). I'll also copy this message over to the hackers list and see what the developers have to say. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[ADMIN] Role based database access
Hello! I face a problem here with restricting access to databases to the owners of the database (Postgres 8.1.4). We would like any postgres user to have database access restricted to their own databases only. This is so, as we use postgres for educational purposes. I looked into pg_hba.conf, but unfortunately, when restricting database access in the database field, the NAME(!) of the database is concerned, which we do not restrict. Any user can create as many databases as needed, and the name may be choosen freely. The 'user' field does not help in this either. So is there any chance to achieve what we need without revoking the 'create database' permission and pre-creating the databases for all our users? Thanks, Thomas Mack ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Role based database access
Thomas Mack wrote: Hello! I face a problem here with restricting access to databases to the owners of the database (Postgres 8.1.4). We would like any postgres user to have database access restricted to their own databases only. This is so, as we use postgres for educational purposes. I looked into pg_hba.conf, but unfortunately, when restricting database access in the database field, the NAME(!) of the database is concerned, which we do not restrict. Any user can create as many databases as needed, and the name may be choosen freely. In the database name you can specify @file, which points to a file containing a list of database names that the pg_hba.conf lines applies to. One idea is to have the database creation routine put a line into that file (which would be specific to each user). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[ADMIN] Performance Monitor
How to monitor PgSQL performance, like JConsole for Java Virtual Machine? Usage of memory, CPU, etc. Thank you, Jair.
[ADMIN] Can't drop tables ERROR: cache lookup failed for relation 411727
Hello All, I have been attempting to move our database to our new database server but i have encountered a few problems along the way. One of the errors that I encountered was the error below when trying to drop a table so I could re-build it. ERROR: cache lookup failed for relation 411727 Never having seen this error I turned to Google to try and determine what the problem is and found this entry from our previous Web Dev/DBA about a slony issue. Unfortunatly for us he has moved on to better things which means i can't quiz him on what happened and what he done to resolve it if he indeed was able to. Having read through the thread it seems to be the same or a similar issue http://www.thescripts.com/forum/thread423390.html I need to be able to determine whats causing this, This will be the second time the Database has moved since and while the errors mentioned in the above thread where on a test Database we did have slony ver 1 running on our live database and I am wondering if this could be responsible for the errors esspecially as after the last DBA left company no one appears to know what happened with slony (I started as he left there was nothing in the handover as I'd never seen postgres before). So basically I need to find a way to figure out what the errors are and find a way to resolve them, I only noticed this when testing and over night build of our database tables and one that usually takes 35 mins took 2 hours 10 minutes which was unacceptable. DB: PostgreSQL 8.1.9 OS: Red Hat Enterprise 5 Thanks In advance David Phillips Cromwell Tools Ltd Leicester ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] anonymous block in Postgres - Hello World
Abraham, Danny [EMAIL PROTECTED] writes: This code is my first like Oracle anonymous blocl. It does not go = through. There are no anonymous blocks in Postgres --- you must create a function. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] anonymous block in Postgres - Hello World
Coming from Oracle world, I also was missing the ability to execute anonymous blocks. So I wrote this function: CREATE OR REPLACE FUNCTION exec_plpgsql_block(exec_string text) RETURNS BOOLEAN AS $THIS$ DECLARE lRet BOOLEAN; BEGIN EXECUTE 'CREATE OR REPLACE FUNCTION any_block() RETURNS VOID AS $$ ' || exec_string || ' $$LANGUAGE PLPGSQL;' ; PERFORM any_block(); RETURN TRUE; END; $THIS$LANGUAGE PLPGSQL; to which I pass my anonymous block as a parameter. As you can see, this function creates/replaces on the fly function any_block() and executes it. Pretty simple solution. Igor Neyman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Thursday, May 24, 2007 11:12 AM To: Abraham, Danny Cc: pgsql-admin@postgresql.org; Devrim GÜNDÜZ Subject: Re: [ADMIN] anonymous block in Postgres - Hello World Abraham, Danny [EMAIL PROTECTED] writes: This code is my first like Oracle anonymous blocl. It does not go = through. There are no anonymous blocks in Postgres --- you must create a function. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Can't drop tables ERROR: cache lookup failed for relation 411727
Fuzzygoth [EMAIL PROTECTED] writes: I have been attempting to move our database to our new database server but i have encountered a few problems along the way. One of the errors that I encountered was the error below when trying to drop a table so I could re-build it. ERROR: cache lookup failed for relation 411727 If this database has been a slony slave in the past, then this could indeed be slony's fault. You'd get better answers about that (and about how to deal with it) on the slony mail lists --- see http://lists.slony.info/mailman/listinfo regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] anonymous block in Postgres - Hello World
Igor Neyman [EMAIL PROTECTED] writes: CREATE OR REPLACE FUNCTION exec_plpgsql_block(exec_string text) RETURNS BOOLEAN AS $THIS$ DECLARE lRet BOOLEAN; BEGIN EXECUTE 'CREATE OR REPLACE FUNCTION any_block() RETURNS VOID AS $$ ' || exec_string || ' $$LANGUAGE PLPGSQL;' ; PERFORM any_block(); RETURN TRUE; END; $THIS$LANGUAGE PLPGSQL; That hasn't failed for you yet? It will the first time you use $$ in the argument. Use quote_literal() please ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[ADMIN] Abnormal storage consumption with autovacuum enabled
I'm using 8.1.4 on linux. Autovacuum is enable with these settings: stats_start_collector = on stats_row_level = on autovacuum = on autovacuum_naptime = 60 autovacuum_vacuum_threshold = 1000 autovacuum_analyze_threshold = 500 Data stored in the database didn't groove very much in the last months (pg_dump still generates a 4GB compressed file), but data files now occupies 20 GB on disk. I know that a vacuum full or a full dump/restore is needed to reclaim filesystem space. However, my question is: how this could have been happened? I'm quite sure that data in tables never reached that size, during normal operations. Could be autovacuum not working properly? How to diagnose or make sure that autovacuum is doing its job? Thanks in advance! Nicola ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Role based database access
Am Donnerstag, 24. Mai 2007 15:57 schrieben Sie: Thomas Mack wrote: Hello! I face a problem here with restricting access to databases to the owners of the database (Postgres 8.1.4). We would like any postgres user to have database access restricted to their own databases only. This is so, as we use postgres for educational purposes. I looked into pg_hba.conf, but unfortunately, when restricting database access in the database field, the NAME(!) of the database is concerned, which we do not restrict. Any user can create as many databases as needed, and the name may be choosen freely. In the database name you can specify @file, which points to a file containing a list of database names that the pg_hba.conf lines applies to. One idea is to have the database creation routine put a line into that file (which would be specific to each user). Ok, this looks reasonable. It probably also means, postgres cannot really restrict database access based on the ownership, which is not nice in this case. But probably no one cares in 'real world' situations. Thanks, Thomas Mack TU Braunschweig, Institut für Informationssysteme ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[ADMIN] Developer help
Anup Gosh from India wrote: Sir, There is a .jar file,a dump file,a .sql file.My question is that how i create a database in PosrgreSQL8.1 in windowsXP platform.Please explain it clearly step by step. Thankin You Anup Ghosh anup_ghosh1232003 ( at ) yahoo ( dot ) co ( dot ) in 1. Go to http://www.wampserver.com/en/ 2. Download newest WAMP 3. Read installation instructions 4. Install 5. Read manual 6. Ready! _ Live.nl Zoek via Live.nl en ervaar het verschil. Test het NU, klik hier!
Re: [ADMIN] Abnormal storage consumption with autovacuum enabled
Nicola Mauri [EMAIL PROTECTED] writes: Data stored in the database didn't groove very much in the last months (pg_dump still generates a 4GB compressed file), but data files now occupies 20 GB on disk. I know that a vacuum full or a full dump/restore is needed to reclaim filesystem space. However, my question is: how this could have been happened? What do you have max_fsm_pages set to? If it's too small, you'll get bloat regardless of whether autovac is doing its thing. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Role based database access
Thomas Mack wrote: Hello! I face a problem here with restricting access to databases to the owners of the database (Postgres 8.1.4). We would like any postgres user to have database access restricted to their own databases only. This is so, as we use postgres for educational purposes. I looked into pg_hba.conf, but unfortunately, when restricting database access in the database field, the NAME(!) of the database is concerned, which we do not restrict. Any user can create as many databases as needed, and the name may be choosen freely. The 'user' field does not help in this either. So is there any chance to achieve what we need without revoking the 'create database' permission and pre-creating the databases for all our users? Thanks, Thomas Mack Does not the sameuser database name work for you in pg_hba.conf? I.e. you can only log into the database that bears your name. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] Role based database access
Hello, I'm not sure why wish not to pre-create a database for each student and limiting them to that database with the no create database priv. Depending on why you want to do that, I think you might be able to give each user a schema. I think access to schemas has to be explicitly granted. We've had students create other schemas instead of using public and it really causes problems when they want to grant users access to their databases. In your case, this might work to your advantage. Carol Walter On May 24, 2007, at 2:31 PM, Scott Marlowe wrote: Thomas Mack wrote: Hello! I face a problem here with restricting access to databases to the owners of the database (Postgres 8.1.4). We would like any postgres user to have database access restricted to their own databases only. This is so, as we use postgres for educational purposes. I looked into pg_hba.conf, but unfortunately, when restricting database access in the database field, the NAME(!) of the database is concerned, which we do not restrict. Any user can create as many databases as needed, and the name may be choosen freely. The 'user' field does not help in this either. So is there any chance to achieve what we need without revoking the 'create database' permission and pre-creating the databases for all our users? Thanks, Thomas Mack Does not the sameuser database name work for you in pg_hba.conf? I.e. you can only log into the database that bears your name. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] Role based database access
Thomas Mack wrote: Hello! I face a problem here with restricting access to databases to the owners of the database (Postgres 8.1.4). We would like any postgres user to have database access restricted to their own databases only. This is so, as we use postgres for educational purposes. I looked into pg_hba.conf, but unfortunately, when restricting database access in the database field, the NAME(!) of the database is concerned, which we do not restrict. Any user can create as many databases as needed, and the name may be choosen freely. The 'user' field does not help in this either. So is there any chance to achieve what we need without revoking the 'create database' permission and pre-creating the databases for all our users? Thanks, Thomas Mack Does not the sameuser database name work for you in pg_hba.conf? I.e. you can only log into the database that bears your name. Well no. As soon as they own more than one database, or when they use a different database name, it does not work out anymore. And yes, we can force them by some means or the other to just use one database with the correct name. But it would be nice, if we wouldn't have to. The work around with a custom 'createdb' command looks nice at first, but as one can use 'create database' as an SQL command, it does not really help out. So currently, I think I will leave it with 'sameuser' or similar, and let the students know about the database name. Unfortunately, the pgAdmin III (version 1.4) likes to pop up a message for any database, the user does not have access to. So this will keep them a little busy on pgadmin3 startup, but anyway, they don't have to use this tool. I was initially thinking, there 'should' be some way to limit the access in the desired way. So now I know, there is not. Schemas might be a way out of the problem, but it's a little late now, to force them using different schemas and take care of access control themselves. Maybe next year. Thomas Mack TU Braunschweig, Institut für Informationssysteme ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Role based database access
[EMAIL PROTECTED] writes: I was initially thinking, there 'should' be some way to limit the access in the desired way. So now I know, there is not. Well, if you were using 8.2 then the CONNECT privilege would help... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
