This usually does it for me:-
select *
from
-- information_schema.tables
pg_catalog.pg_tables
--WHERE
-- table_schema = 'public'
--ORDER BY
-- table_name
On 11/20/07, Kevin Kempter <[EMAIL PROTECTED]> wrote:
>
> Sorry, I meant to send this to the list:
>
>
> We have a handful of tables
Doesn't ownership also apply to views and stored procs...
On 11/1/07, Scott Marlowe <[EMAIL PROTECTED]> wrote:
>
> On 10/31/07, Jessica Richard <[EMAIL PROTECTED]> wrote:
> > I am trying to drop a user but it complained with the following message:
> >
> > ERROR: role "joe" cannot be dropped becau
Hi,
Please advise whether it is necessary for us to apply a later version of
Postgres in preparation for the new daylight savings times coming in to
effect in New Zealand at the end of September?
We are running PG 8.1.4 and 8.1.6.
FAQ's indicate that our one remaining system on 7.1.4 will use the o
On 7/31/07, Michael Fuhr <[EMAIL PROTECTED]> wrote:
>
> On Tue, Jul 31, 2007 at 10:35:25AM +1200, adey wrote:
> > -- CAUTION: this query may impact system performance as you are
> selecting
> > from system tables
>
> What difference are you expecting that to m
Try this query for a start, and add system tables to the join to find what
you want:-
-- displays locks with database name and username, but not table
-- CAUTION: this query may impact system performance as you are selecting
from system tables
select
relation
, c.relname
, u.usename
, pid
Did your cammand fail, or did it work, but the user can still create tables?
On 7/27/07, Anderson Alves de Albuquerque <[EMAIL PROTECTED]> wrote:
I need to use a user that can not have permission to create table in one
BD.
I use revoke, I tray with "revoke all on database XXX to|from
USERN
HP was providing CA (Continuous Access) software that was claimed to provide
WAN SAN replication by repeating IO in exactly the sequence it was generated
on the master, to the slave. SO while there was a delay, updates on the
slave would be sequentially intact, providing a good level of integrity
Try this:-
-- Query returns size of a database calculated from the no. 8k pages
allocated to tables
SELECT
-- *
sum( relpages*8/1024 ) as MB
FROM
pg_class
WHERE
-- relname != ''
-- relname = 'document'
relnamespace = '2200'
On 4/16/07, Trula Thomas <[EMAIL PROTECTED]> wrote:
Hello, would anyo
I found the following script useful; for calculating database size:-
-- Query returns size of a database calculated from the no. 8k pages
allocated to tables
SELECT
sum( relpages*8/1024 ) as MB
FROM
pg_class
WHERE
-- relname != ''
-- relname = 'tablename'
relnamespace = '2200'
On 4/17/07, Chuck
We had a similar conversion conflict some time back whe upgrading and found
using a search for anything not A-Z, 0-9, etc, was useful to find offensive
data and fix it.
On 4/8/07, Dan Harris <[EMAIL PROTECTED]> wrote:
Hello all:
I'm in a bit of a bind here. Today is my maintenance window for
If you do a pgdump -s will dump out schema only from the Postgres database.
Much of this will be usable in SQL server to at least create your basic
database and objects. You will then need to interpret the remianing more
complex structures and build them / adapt the DDL, to work in SQL Server.
If
The following query with the stats_command_string parameter turned on will
give you some of the SQL per transaction:-
SELECT
datid,
datname as "DB Name",
substr(procpid,1,6) as "Procpid",
substr(usesysid,1,5) as "UseSysid",
usename,
current_query as "SQL",
query_start
FROM
pg_stat_activity
order
?
>> Jim C. Nasby escreveu:>> >On Wed, Nov 08, 2006 at 09:29:28PM +1300, adey wrote:> >> >> >>Jim - is there a component that needs to be installed on the database
> >>server> >>with Postgres that PGAdmin communicates with please, or is only PGAdm
Is there not a parameter in Postgres to timeout idle connections?
On 11/11/06, imad <[EMAIL PROTECTED]> wrote:
I found it on postgresql archives:
http://archives.postgresql.org/pgsql-admin/2004-09/msg00261.phpThis post was by Tom Lane, I hope this is what you want.It will time out by itself eventua
Drop the index and recreate it in the new tablespace.
On 11/13/06, Alexander Burbello <[EMAIL PROTECTED]> wrote:
Hi,I have one tablespace with all my objects.I would like to separate indexes from other.
How can I select and move with SQL???Thank you for your help.---(end of
11/9/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
On Wed, Nov 08, 2006 at 09:29:28PM +1300, adey wrote:> Jim - is there a component that needs to be installed on the database server
> with Postgres that PGAdmin communicates with please, or is only PGAdmin> required?I know there's
Jim - is there a component that needs to be installed on the database server with Postgres that PGAdmin communicates with please, or is only PGAdmin required?
Adrian
On 11/8/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
On Tue, Nov 07, 2006 at 03:34:36PM -0200, Alexander Burbello wrote:> Where can I
I think the following query should list clustered indexes for you:-
select * from pg_indexwhere indisclustered = 't'
On 11/8/06, Mark Steben <[EMAIL PROTECTED]> wrote:
Thank you, Jeff. That worked like a champ. My initial problemHad to do with not understanding the concept of OID datatypes.
My
When last did you do a FULL vacuum and analyse the stats report at the end?
On 1/2/90, amar buwa <[EMAIL PROTECTED]> wrote:
Hi, My postgresql configuration ismax_locks_per_transaction = 256 max_connections = 200
shared_buffers = 6000 max_fsm_pages = 20 max_fsm_relations = 1500My server
I had a similar experience when we upgraded from 7.4 to 8.1.4.
When we attempted the restore in 8.1.4 / UTF8, it failed and told us the offending row. We edited the original database to correct the data and retried. There were several ofending rows, but fortunatley for the exact same value, so we c
Hi Tom,
Further to my request below, the following recommendation exists in the Postgres 8.1 documentation:-
http://www.postgresql.org/docs/8.1/interactive/routine-reindex.html
On 9/25/06, adey <[EMAIL PROTECTED]> wrote:
Thanks Tom.
How does Postgres handle page splits and redirecti
A good guide for a clustered index is:-
Will the index be unique, static and narrow.
If not, avoid clustered indexing as they require extra work on disk to maintain their sequence.
On 9/26/06, Chris Hoover <[EMAIL PROTECTED]> wrote:
I am looking to squeeze a bit more speed out of my database by cl
AIL PROTECTED]> wrote:
adey <[EMAIL PROTECTED]> writes:> I apologise if I am uninformed, but it is my understanding that REINDEX is
> still required even with the most regular of vacuums?REINDEX shouldn't be required at all under normal circumstances. regards, tom lane
I apologise if I am uninformed, but it is my understanding that REINDEX is still required even with the most regular of vacuums?
On 9/22/06, Jim Nasby <[EMAIL PROTECTED]> wrote:
On Sep 21, 2006, at 6:40 AM, Jose Manuel Garci a Valladolid wrote:> I have a PostgreSQL 8.1.4 server under Linux Red Hat
Does autovacuum replace the need for a FULL vacuum please (to recover free space, etc)?
On 8/10/06, Scott Marlowe <[EMAIL PROTECTED]> wrote:
On Wed, 2006-08-09 at 13:24, Joel Stevenson wrote:> Hi,>> I have a database that includes both highly transactional tables and
> archive tables - OLTP and OLA
Try:-
SELECT relname, relpages, CAST(reltuples AS numeric) , ( (relpages*8192)/1024 ) as MBFROM pg_class
...which will give you space used on disk for all data pages currently in use per table (assuming you have 8k data pages - the default).
On 8/9/06, Sharma;G.S. <[EMAIL PROTECTED]> wrote:
A vacuum full analyze might help.
On 8/8/06, Benjamin Krajmalnik <[EMAIL PROTECTED]> wrote:
Chris,I just finished running some benchmarks on an underpowered servercompared to the one I am running in production.
My initial tests were run on an ampty database, pg_xlog on the samespindle.Stored proced
How much RAM do you have, and what is your SHMMAX set to?
On 8/1/06, Javanesevn <[EMAIL PROTECTED]> wrote:
Hi all,My project ( web application) is using Postgre 8.1 and PHP 4.x (ADOlibrary to access database). And I would like to increase perfomance, so
I changed configuration follow this:shared_bu
t;[EMAIL PROTECTED]> wrote:
On Tue, 2006-08-01 at 23:09, adey wrote:> Please could someone tell me how to discover what is using all of my
> RAM?> I am trying to run a vacuum against Postgres, but it fails immediately> with:->> "ERROR: out of memory> DETAIL: Failed o
Please could someone tell me how to discover what is using all of my RAM?
I am trying to run a vacuum against Postgres, but it fails immediately with:-
"ERROR: out of memoryDETAIL: Failed on request of size 1073741820."
TOP shows the following:-
Mem: 4077544k total, 3897868k used, 179676
Thanks Tom - datfrozenxid has been the same for at least the last month on template0 and 1
On 7/31/06, Tom Lane <[EMAIL PROTECTED]> wrote:
adey <[EMAIL PROTECTED]> writes:> select datname, age(datfrozenxid), datfrozenxid, current_date, current_time
> FROM pg_database> If t
I have the discussion below in which it is advised that template0 should not be touched, however, my production v8.1.4 template0 and 1 database XID "age", determined with the following query, changes constantly:-
select datname, age(datfrozenxid), datfrozenxid, current_date, current_time FROM p
Try running pgstattuple on some of the tables that have not had vacuum full, and some of those that have. It will tell you how many dead tuples there are in the table, which is an indicator of how seriously the table needs a full vacuum.
Run IPCS on the Unix command line to see how much of your
If you manage to get "Jobs" working, please could you help me get mine going?
On 7/21/06, Shoaib Mir <[EMAIL PROTECTED]> wrote:
HI Michael,You can try using an addon like pgjobs (
http://gborg.postgresql.org/project/pgjobs/projdisplay.php)Thanks,
Shoaib Mir EnterpriseDB
On 7/21/06, Michael Gross
I had the same problem when upgrading v7.4 database to v8, SQL-ASCII to UTF8 - we had to replace single backslashes with double backslashes in the v7 database to get the data to display correctly, then dump and restore in v8 / UTF8. Have we done something wrong please?
On 7/22/06, Peter Eisentraut
We found corrupt data today in production that cannot be converted to UTF8. We are in the process of coorecting the data values. Thanks for your response.
On 7/19/06, Aaron Bono <[EMAIL PROTECTED]> wrote:
On 7/17/06, adey <[EMAIL PROTECTED]
> wrote:
We recently backed up ou
Many thanks to all of you that helped with this.
On 7/18/06, Scott Marlowe <[EMAIL PROTECTED]> wrote:
On Sat, 2006-07-15 at 02:37, adey wrote:> Please advise if there is a way to start a fresh Postgresql error /
> output log at regular intervals?> We are tuning a "new toprod
We recently backed up our production database (dumpall) on Postgres v7.4 and restored on v8.1.4. All of the tables restored except three. The tables were created, but their data wasn't applied. I can see the tables in the dump file, and no errors were reported in the restore. What am I missing plea
Please advise if there is a way to start a fresh Postgresql error / output log at regular intervals?
We are tuning a "new toproduction" system, so I have all the SQL logged, but the file gets too big and cumbersome, so I'd like to start a new one each morning without restarting Postgres.
The postgresql parameter explanation for work_mem say :-
"Another way to set this value is to monitor the Postgres temp files(in PGDATA/base/DB_OID/pgsql_tmp) and adjust sort_memupward if you see a lot of queries swapping from these temp files."
...but I can't find pgsql_tmp in my v8.1.4 instal
If you turn WAL logging off (via fsynch parameter in .conf), it is my understanding that you will no longer log database updates, and will therefore not be able to recover any uncommitted transactions in the event of failure, (which seems contradictory to database practice).
Gut feel to me says
Check the stats at the end of your vacuum to ensure your max_fsm_pages (free space map) is large enough. Also check work_mem and maintenance_work_mem are not running at defaults that may be too small. If you have many updates, increase the number of wal_buffers and checkpoint_segments.
On 7/11/06,
Can hba be manipulated in such a way to make Postgres "read only" to obtain a complete and full backup please, and if so, how?
On 5/19/06, Tom Lane <[EMAIL PROTECTED]> wrote:
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> Is that possible? I've checked the docs for 8.1, and am not finding> anyth
Please assist me in implementing this for the first time?
I have installed Postgres 8.1.4 on my laptop, including Admin, then accessed the database "postgres" from PGAdmin 1.4.2 and run the "pgagent.sql" script tp populate the database with the following result:-
NOTICE: CREATE TABLE / PRIMARY
Please could someone give me an idea of what pgpool is, and where I can research it?
I have run a search on postgresql.org and found many references, but they don't explain what it is, and it doesn't appear in the documentation index of the v8.1 manuals.
Please could you tell me where I must execute the following to disconnect idle transactions in Potgres:-
EXEC SQL DISCONNECT [connection-name];
I've tried variations of this syntax in PGAdmin, psql, and the Linus command line in both v8.1.4 and v7.4.2 - all of which fail.
COuld you also tell me
If autovacuum is to be provided with facilities for individual table and time window controls, that can be done currently using VACUUM TABLE FULL. Currently this must be scheduled in crontab - a task scheduler in PGAdmin would be very helpful to do this instead of crontab that usually requires sysa
Please could someone help me with my questions below?
On 3/25/06, adey <[EMAIL PROTECTED]> wrote:
Two questions in this regard please?
1) Is tuple theory not the root of this problem
2) Vacuum does much the same as a traditional database reorg, and online reorgs are a reality now
1
uum run by automated script.
-- Forwarded message --From: adey <[EMAIL PROTECTED]>Date: Mar 14, 2006 8:51 AMSubject: Notice Processor hook
To: Tom Lane <[EMAIL PROTECTED]>
Hi Tom,
A colleague found this entry from yourself in thescripts.com:-
"Adam H.Pendleton" &
In vacuum full output, indexes are listed as having been vacuumed along with their table.
Have I misinterpreted this message saying that vacuum is incapable of vacuuming indexes (and reindex is the only option to do so) please?
On 3/25/06, Tom Lane <[EMAIL PROTECTED]> wrote:
Peter Eisentraut <[EMA
Two questions in this regard please?
1) Is tuple theory not the root of this problem
2) Vacuum does much the same as a traditional database reorg, and online reorgs are a reality now
1) If I understand tuple theory correctly, copies of rows are created through normal Postgres processing, that exp
Please give me some guidance?
We are attempting many deletes in our production database for the first time, and we're getting nowhere fast.
The SQL runs for more than 12 hours to delete 2 million rows, and hasn't finished each time we've tried it as we've had to cancel it.
I have tried running quer
Please tell me where I can regularly find what the current generally available (not beta) version of Postgres is, and what the current version is?
Try this query that displays current activity:-
select datid, datname as "DB Name", substr(procpid,1,6) as "Procpid", substr(usesysid,1,5) AS "UseSysid", usename, current_query as SQL, query_startfrom
pg_stat_activityorder by procpid
You may need to turn on some of the parameters in p
That's only 1.8mb of RAM, so I can't see it being a problems if you have no RAM shortage on your server.
On 9/27/05, Gourish Singbal <[EMAIL PROTECTED]> wrote:
Thats a good Idea.
My output after full vaccum is :-
INFO: free space map: 9 relations, 114736 pages stored; 76592 total pages need
A "free" command on my linux server returns the following:-
total used free shared buffers cachedMem: 4077400 3958256 119144 0 104208 1551116-/+ buffers/cache: 2302932 1774468Swap: 284 4744 1995
Please tell me what I'm missing?
My stats Postgres 7.4 parameters are as follows:-
stats_start_collector = truestats_command_string = truestats_block_level = truestats_row_level = truestats_reset_on_server_start = true
After turning all of these on, I restarted Postgres, then ran a table query a
Please let me know if there is a way to determine when the Postmaster was last restarted?
This is for tuning. I have stats_reset_on_server_start turned on, but I don't know when last the Postmaster was started, so I can't determine the period my current system stats cover.
Has anyone found the book "PostgreSQL Essential Reference" by Barry Stinson useful in administering Postgresql please? I'm looking for a comprehensive reference rather than a history or introduction to databases and SQL, and would like to know how suitable this book would be.
Your opinion would
You could try the following query after enabling the
stats_command_string parameter in the postgresql.conf file:-
-- displays current activity in the database
-- postgresql.conf parameter stats_command_string populates the
current_query -- column
select datid,
datname as "DB Name",
60 matches
Mail list logo
