[GENERAL] I want to create a read only database for a specified user.

Hello, I have a postgres user, that was used to create a database with some 1500+ tables. I now want to have this database or all tables capable of read-only. Is there an easy way of doing this? I am running 8.4.4. on Linux Centos 5 Chris Barnes Recognia Inc.

Re: [GENERAL] I want to create a read only database for a specified user.

Thanks Greg, just what I asked for! Chris From: g...@turnstep.com To: pgsql-general@postgresql.org Subject: Re: [GENERAL] I want to create a read only database for a specified user. Date: Tue, 16 Nov 2010 18:10:33 + -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I now

Re: [GENERAL] ERROR: Out of memory - when connecting to database

Date: Mon, 8 Nov 2010 20:05:23 +0100 From: k...@comgate.cz To: pgsql-general@postgresql.org Subject: Re: [GENERAL] ERROR: Out of memory - when connecting to database Replaying to my own mail. Maybe we've found the root cause: In one database there was a table with 200k records where

[GENERAL] Is there a plugin/script for nagios that monitors pitr being up to djavascript:;ate?

), but not the point. Any help would be appreciated... Chris Barnes

Re: [GENERAL] Is there a plugin/script for nagios that monitors pitr being up to djavascript:;ate?

On Fri, 2010-10-22 at 08:37 -0400, Chris Barnes wrote: Is the a plugin or script that will allow pitr to be monitored and trigger an alarm when the pitr master/slave databases get out of sync? The reason I'm asking, I have had one or four of pitr'd slaves get out of sync twice

Re: [GENERAL] pg view of table columns needed for scripting

From: dave.gauth...@intel.com To: pgsql-general@postgresql.org Date: Fri, 22 Oct 2010 09:14:01 -0700 Subject: [GENERAL] pg view of table columns needed for scripting Hi: Is there something like a pg_xxx view that I can use to get the column names and data types of a table,

[GENERAL] Postgre 9.0 replication using streaming.

masters at out local collocation site. Is there a way configure postgres 9.0 to have 4 unique streaming connections from this one machine with the 4 databases to the 4 databases on 4 machines. Thanks for your reply, Chris Barnes CBIT Inc.

[GENERAL] Autovacuum settings between systems

I am using 8.4.2 on centos 5.4 2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux I have autovacuum processes that appear to have been running most of the day. There aren't any messages in the log, but there must be something wrong for it to take this long?

[GENERAL] Creating a column interger with default to not null

I am confused with what this is telling me. I have a table and I am trying to add a new column with constraint not null. What am I missing? Chris pgdb001= alter table schema.table add COLUMN column_name integer not null; ERROR: column column_name contains null values

[GENERAL] Postgres wont drop foriegn keys on tables.

I am having an odd problem that I have seen before. It usually clears itself after I restart postgres. I am attempting to drop an foreign key on a table and it sits for hours and doesn't drop or put anything into the log. Killing the alter puts an error in, but it doesn't time out and it

Re: [GENERAL] Postgres wont drop foriegn keys on tables.

Sorry, I am running the following. [postg...@pgprd01 pgcheck]$ psql --version psql (PostgreSQL) 8.4.2 contains support for command-line editing [postg...@pgprd01 pgcheck]$ uname -a Linux system.name.com 2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux From:

[GENERAL] logging postgres to syslog on centos, truncates the postgres message.

Hoping someone can help me with this problem. I am logging postgres to syslog on a CentOS release 5.4 (Final) ( 2.6.18-164.el5 ). When I look for an update statement it appears to be truncated and missing the whole statement. Is there a quick way to resolve this? Is it linux or postgres?

Re: [GENERAL] logging postgres to syslog on centos, truncates the postgres message.

On separate lines it is, thanks Tom. To: compuguruchrisbar...@hotmail.com CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] logging postgres to syslog on centos, truncates the postgres message. Date: Thu, 9 Sep 2010 10:39:10 -0400 From: t...@sss.pgh.pa.us Chris Barnes

[GENERAL] upgrade postgresql from 8.4.x to 8.4.4

Is there a procedure to upgrade from 8.4.x to 8.4.4 or am I over thinking it? I'm hoping I can just yum upgrade postgresql and have it just upgrade it without having to dump and restore the whole db. It would be nice if I had to only bounce postgresql to start using the newer version :)

[GENERAL] Using concatenation operator

I have a (stupid) question regarding using concatenation operator. I would like to get the list of tables from the database and output the select count(*) for each of them I don't want the schema name proceeding the select, how can I omit without receiving the error below. psql

[GENERAL] How to determine which tables are created from inheritance.

I am using londiste and would like to add tables for partitioned tables only. I.E. exclude the parent. I am currently using the select below. What postgres catalog table would I query to see this information? psql database -t -c select schemaname||'.'||relname from pg_stat_user_tables where

Re: [GENERAL] How to determine which tables are created from inheritance.

I have answered my own question. After some poking around I was able to find what I was looking for. I have posted for future reference. select relname,relid from pg_stat_user_tables where relid in (select inhrelid from pg_inherits) and relname like 'table%' order by relname; Thanks, Chris

Re: [GENERAL] alter table alter type CASCADE

It has been some years since I worked with Oracle, doesn't Oracle recompile the view when the object it references changes in structure? Send From: thombr...@gmail.com Date: Wed, 5 May 2010 10:12:34 +0100 Subject: Re: [GENERAL] alter table alter type CASCADE To: s...@compulab.co.il CC:

Re: [GENERAL] pg_hba.conf

I've had problems before with the listen_addresses and had to set it accordingly. Wouldn't accept connections locally. listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses;

[GENERAL] Errors starting postgres after initializing new database

I have just initialized a database with no errors, used the postgresql.conf file from another system running 8.4.2. Attempted to start and got the fatal error below. I than ran pgtune and got the same error. I am not sure what the problem is? Is there more detailed logging than below, or can

[GENERAL] Running vacuum after delete does not remove all space allocated

I have deleted the rows in a table and vacuumed full, there appears to be space allocated that after a truncate it removes. Why is this? \d t8000_us_ts_size_test_2d Table dbprc001.t8000_us_ts_size_test_2d Column | Type | Modifiers

[GENERAL] Processor speed relative to postgres transactions per second

We have two camps that think that the speed of cpu processors is/aren't relative to the number of transactions that postgres that can performed per second. I am of the opinion that is we throw the faster processors at the database machine, there will be better performance. Just like

Re: [GENERAL] Does anyone use in ram postgres database?

: Re: [GENERAL] Does anyone use in ram postgres database? Chris Barnes wrote: We are testing in memory postgres database and have questions about configuring the ram mount point and whether there is great gains in setting it up this way? Are there any considerations for postgres

[GENERAL] Does anyone use in ram postgres database?

accomplished this? Cheers, Chris Barnes _ Stay in touch. http://go.microsoft.com/?linkid=9712959

[GENERAL] Create a function that updates the record with and timestamps

I would like to have postgres update the last_modified column with the current_date on an update of the record. I am not sure if there is a very simple way of doing this? Or, do I need to create a function and a trigger to call the row and update with new data and set the last_modified

Re: [GENERAL] Create a function that updates the record with and timestamps

to Chris Barnes : I would like to have postgres update the last_modified column with the current_date on an update of the record. I am not sure if there is a very simple way of doing this? Or, do I need to create a function and a trigger to call the row and update with new data and set

[GENERAL] Raid 10 settings for optimal postgres performance?

I have just configured our disks to raid10 from raid5. The raid 10 is now 6 drives giving us 750G use by OS and postgres. What is the preferred setup for postgres concerning cache settings on the controller and disks and the preferred block size allocated for the disks when postgres uses

Re: [GENERAL] Setting a table to be ignored by autovacuum

...@hotmail.com On 18 Feb 2010, at 18:47, Chris Barnes wrote: Right you are, I'm due to upgrade end of month on this system. Here I was thinking 8.4. Sorry for the spam. Chris [postg...@pgprd01:~/pgcheck]$ psql Welcome to psql 8.3.3, the PostgreSQL interactive terminal. Type: \copyright

[GENERAL] Setting a table to be ignored by autovacuum

Hi, I'm trying to have this table ignored by the autovacuum process. It wasn't created with this in mind, hoping there is still a way? Thanks, Chris alter table schema.table SET (autovacuum_enabled = false); ERROR: unrecognized parameter autovacuum_enabled

Re: [GENERAL] Setting a table to be ignored by autovacuum

: compuguruchrisbar...@hotmail.com; pgsql-general@postgresql.org On Thu, Feb 18, 2010 at 12:37 PM, Richard Huxton d...@archonet.com wrote: On 18/02/10 17:20, Chris Barnes wrote: I'm trying to have this table ignored by the autovacuum process. It wasn't created with this in mind, hoping

Re: [GENERAL] Setting a table to be ignored by autovacuum

...@archonet.com; pgsql-general@postgresql.org Subject: Re: [GENERAL] Setting a table to be ignored by autovacuum Chris Barnes escribió: Right you are, I'm due to upgrade end of month on this system. Here I was thinking 8.4. Sorry for the spam. You can INSERT INTO pg_autovacuum

Re: [GENERAL] error creating database

This depends on your OS. If you are running (linux) redhat or centos you would edit this file /etc/sysconfig/i18n and change your locale to, for example. Save it and reboot. There are probably ways around this when creating the database, but we install our OS with this in mind.

[GENERAL] Truncate and delete adds wal logs for slave to process.

I have a question regaring delete truncate versus a drop of the tables and recreating it. We have a database that gets recreated each week that is 31 GB in size. The way that it is currently being done is to truncate all of the tables. I would like to confirm. Because both

[GENERAL] Warning when selecting column from pg_stat_user_tables.

I have this error when selecting from the pg_stat_user_tables. I restarted postgres and the error went away. Has anyone else seen this error? Chris Barnes [postg...@preventdb02 londiste]$ cat /data/pgsql/data/pg_log/postgresql-Tue.log WARNING: pgstat wait timeout WARNING

[GENERAL] Postgres wal shipping from 8.33 to 8.42.

We are trying to minimize our downtime in production to upgrade from 8.33 to 8.42. What we would like to be able to do is this: Upgrade the slave we are currently shipping to, to version 8.4.2. Continue to pitr to this server. And switch over and then upgrade the master. My question

Re: [GENERAL] Connect to Postgres problems

You should be able to telnet to the port and get a response back as in the exmple below. Of course substitude the ip for the database. [postg...@pgprd01 londiste]$ telnet 127.0.0.1 5432 Trying 127.0.0.1... Connected to localhost.localdomain (127.0.0.1). Escape character is '^]'. Date:

Re: [GENERAL] Connect to Postgres problems

Telnet is usually installed by default on windows or unix box, telnet to the database box should work. telnet 207.6.93.IP 5432 should work Make sure that windows firewall and antivirus software firewall are temporarily disabled to test. Date: Mon, 1 Feb 2010 09:49:49

[GENERAL] Equivalent of mysql type mediablob in postgres?

I would like to move a table that is used to store images from mysql to postgres. The only stumbling I may encounter, may be switching from mysql blob to something in postgres. We store chart images in a mysql medium blob type. How can I store these in postgres? Chris |

Re: [GENERAL] Archive command seem to be working.

other system limitations) it should begin archiving the files again. Cheers, Andrew From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Chris Barnes Sent: terça-feira, 22 de dezembro de 2009 17:03 To: Postgres General Postgres General Subject

[GENERAL] Archive command seem to be working.

Is there a way to restart a service without taking the database down/up? Any help would be appreciated. Cheers, Chris Barnes _ Windows Live: Make it easier for your friends

[GENERAL] Bestpractice for upgrading from enterpriseDB 8.3.3 to rpm 8.4.1.

this? Any suggestions would be appreciated. Cheers, Chris Barnes _ Windows Live: Friends get your Flickr, Yelp, and Digg updates when they e-mail you. http://go.microsoft.com/?linkid

[GENERAL] Is there a tool used to display statement times from postgres logs

Would someone have a tool that displays statement execution times/stats from the standard output from postgres logs? I have attempted pgfouine but not had sucess with the log format. Does anyone use pgfouine or have something that works for them? Chris

Re: [GENERAL] Is there a tool used to display statement times from postgres logs

: [GENERAL] Is there a tool used to display statement times from postgres logs Chris Barnes wrote: Would someone have a tool that displays statement execution times/stats from the standard output from postgres logs? there's a logging option to put that in the logs, I thought? if so, you

Re: [GENERAL] Is there a tool used to display statement times from postgres logs

, Chris Barnes wrote: Would someone have a tool that displays statement execution times/stats from the standard output from postgres logs? I have attempted pgfouine but not had sucess with the log format. Does anyone use pgfouine or have something that works for them? Chris All

[GENERAL] Looking for a script that performs full online backup of postgres in archive mode

Would anyone in the postgres community have a shell script that performs a full online backup of postgres? Any help would be appreciated. _ Windows Live: Keep

Re: [GENERAL] Looking for a script that performs full online backup of postgres in archive mode

of postgres in archive mode On Mon, Nov 09, 2009 at 09:15:03AM -0500, Chris Barnes wrote: Would anyone in the postgres community have a shell script that performs a full online backup of postgres? Here's roughly what we do: REMOTE=foo DATA=/srv/pgdata WAL=/var/lib/pgsql/wal-archive PSQL

[GENERAL] Bench marking performance or experience using Solid State Disk Drives (SSD) with postgres

Does anyone use solid state drives for postgres? Has there been any benchmark that states whether mechanical disk drives out perform solid state drives? Is there any benefit, they are quite expensive. Chris Barnes

[GENERAL] postgres logs indicate errors with prepared statements, since pgbouncer was installed.

We are using pgbouncer and seeing these errors in the postgresql logs. I don't believe pgbouncer allows for server prepared statements, so why would I see anything in the logs at all? 2009-11-03 12:00:37 EST:u...@10.0.0.1(56270):822ERROR: prepared statement dbdpg_p1573_3968 does

[GENERAL] 2009-11-02 08:31:30 EST:u...@host(48990):4562ERROR: current transaction is aborted, commands ignored until end of transaction block

I hope someone can help me determine what is going on with my database. We have released code this weekend to our application and I have switched over to my hot standby that has been tuned and modified to raid10. It is up and appeared come on line and pitr is running to the standby (ok) We

[GENERAL] Logging statements longer than 1000ms doesn't appear to work

I've have set the parameter in my postgresql.conf file and have restarted postgres. When reviewing the log file I am finding that all of the statements are being logged (0.108 ms)? Is there some other parameter that I have missed? log_min_duration_statement = 1000 # -1 is

Re: [GENERAL] Logging statements longer than 1000ms doesn't appear to work

doesn't appear to work Date: Tue, 22 Sep 2009 15:08:39 -0400 From: t...@sss.pgh.pa.us Chris Barnes compuguruchrisbar...@hotmail.com writes: I've have set the parameter in my postgresql.conf file and have restarted postgres. When reviewing the log file I am finding that all

Re: [GENERAL] Logging statements longer than 1000ms doesn't appear to work

: Tue, 22 Sep 2009 15:08:39 -0400 From: t...@sss.pgh.pa.us Chris Barnes compuguruchrisbar...@hotmail.com writes: I've have set the parameter in my postgresql.conf file and have restarted postgres. When reviewing the log file I am finding that all of the statements are being logged

[GENERAL] oom ( kernel: postgres invoked oom-killer: gfp_mask=0x201d2, order=0, oomkilladj=0 )

I am having a problem with the system freezing when the system is very busy. I have found the entry oom-killer in our messages log. I would like to confirm that the proper way of dealing with this is to set the sysctl parameter below. Also, the kernel parameter for shmmax and shmall are not

[GENERAL] Locks in postgres causing system load and crash.

We have a situation where the database locks escalate and load causes problems or the system crashes in some circumstances. We have munin installed and notice that the locks (access share locks) climbed to 2.7k. I'm wondering what or how I can get a snapshot of the table(s) and perhaps

Re: [GENERAL] Reverse-engineering table creation statements

pg_dump --schema-only --schema=SCHEMA --table=TABLE produces creation script. Chris http://www.postgresql.org/docs/8.0/interactive/backup.html From: thombr...@gmail.com Date: Mon, 14 Sep 2009 16:15:23 +0100 Subject: [GENERAL] Reverse-engineering table creation statements To:

Re: [GENERAL] Locks in postgres causing system load and crash.

...@gmail.com To: compuguruchrisbar...@hotmail.com CC: pgsql-general@postgresql.org On Mon, Sep 14, 2009 at 8:58 AM, Chris Barnes compuguruchrisbar...@hotmail.com wrote: We have a situation where the database locks escalate and load causes problems or the system crashes in some circumstances

Re: [GENERAL] Postgresql Hardware

Purchase solid equipment and fairly current machines. We buy referbished system at a fraction of the cost of new. For example; IBM 3650 with 8 x 300g SAS drives and controller, 4 slot dual with the following specs. 16 gb memory. model name : Intel(R) Xeon(R) CPU E5345

Re: [GENERAL] PG connections going to 'waiting'

Is the any way to look at the statistics on the name of table, length and type over a period of time? Or, would we have to use munin and capture these stats for analysis later? Chris To: alan.mc...@gmail.com CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] PG connections

[GENERAL] Postgres bug #4907 : stored procedures and changed tables

We have run into postgres bug #4907 : stored procedures and changed tables To say, we have created a function and made changes to the table and the procedure no longer works giving the error below ERROR: structure of query does not match function result type CONTEXT: PL/pgSQL

[GENERAL] Pgbench tool download

I am looking for pgbench. Is there a good source from which I can download the most current version? your help is appeciated, Chris _ Stay on top of things, check email from other accounts!

[GENERAL] How would I get information regarding update when running for a long time?

---+---+--- id| character varying(13) | date| date | volume | numeric(18,6) | Indexes: ix_dchrisa btree (id) ix_dchrisb btree (date) ix_dchrisd btree (volume) Thanks for any help Chris

[GENERAL] Create (function, procedure) and trigger to increment a counter

I have a table usage, I would like to create a (function or procedure) called by the trigger to increment column counter after an update. Can someone lend me a hand with the process behind creating this function,procedure and trigger. Table public.usage Column |

[GENERAL] Asking for assistance in determining storage requirements

using SAN with fiber channel hba and if anyone else uses this technology. We would also like to know if people have preference to the level of raid with/out striping. Sincerely, Chris Barnes Recognia Inc. Senior DBA _ Attention all

[GENERAL] Postgres online backup and restore has errors that are concerning

for toast value 738680 Version [postg...@pgprd01 data]$ cat PG_VERSION 8.3 Chris Barnes _ Windows Live helps you keep up with all your friends, in one place. http://go.microsoft.com/?linkid=9660826

[GENERAL] Postgres online backup and restore

criteria_rank like '%TR009%'; ERROR: missing chunk number 0 for toast value 738680 Version [postg...@pgprd01 data]$ cat PG_VERSION 8.3 Chris Barnes

[GENERAL] Problems with postgres online backup - restore

I started an online backup of postgres, tar'd my data folder, copy to usb drive in production and restored it into my RC environment. Have I missed something important? When starting the database I receive many errors that look like the backup was corrupted. 2009-06-23 08:29:15