[GENERAL] Is there a plugin/script for nagios that monitors pitr being up to djavascript:;ate?
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? Not sure why, it may have something to do with rsync on (wal log txfr), 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? > > Not sure why, it may have something to do with rsync on (wal log > > txfr), but not the point. > > PITRTools has a built-in Nagios notification system for pitr monitoring. > Basically it uses nsca+nagios+passive check feature. You may want to use > it, or use the logic in it: Hi Devrim, It's Escaping me where nagios is in the listing? I'm probably way off but more specifically if you could please? -rwxr-xr-x 1 root 1017 12167 May 4 15:02 cmd_archiver -rwxr-xr-x 1 root 1017 940 Jan 9 2009 cmd_archiver.ini.sample -rwxr-xr-x 1 root 1017 3915 Dec 26 2008 cmd_archiver.README -rwxr-xr-x 1 root 1017 15872 Apr 16 2009 cmd_standby -rwxr-xr-x 1 root 1017 1476 Feb 20 2009 cmd_standby.ini.sample -rwxr-xr-x 1 root 1017 3920 Feb 7 2009 cmd_standby.README -rwxr-xr-x 1 root 1017 992 Apr 21 2009 cmd_standby.sql
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, similar to what I see with \d ? I need to run this is a script, so \d isn't viable. I did a \df and looked around, but nothing popped out. Thanks in Advance ! Specify the table name where like.. SELECT table_schema,table_name,column_name,data_type FROM information_schema.columns WHERE table_name like 't%' and column_name = 'name_of_column_here' ORDER BY table_name ; table_schema |table_name | column_name | data_type ---+---+---+--- schema | table | column_name | character varying Chris
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 each record > contained 15kB bytea field. Auto-ANALYZE was running on that table > continuously (with statistics target 500). When we avoid the > auto-ANALYZE via UPDATE table set bytea_column = null; CLUSTER table; > the problem with ERROR: out of memory went away. Run pgtune against you configuration and it will tell you what is recommended. Do you really have 2048 connections to the box? If yes, maybe you need to run pgbouncer with 2048 connections into pgbouncer concentrator and 100 connections to postgres? Will reduce your resource used significantly. Chris
[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 want to have this database or all tables capable of read-only. > > ALTER DATABASE foo SET default_transaction_read_only = true; > > Easy to work around, but may be good enough for your purposes. > > - -- > Greg Sabino Mullane g...@turnstep.com > End Point Corporation http://www.endpoint.com/ > PGP Key: 0x14964AC8 201011161310 > http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 > -BEGIN PGP SIGNATURE- > > iEYEAREDAAYFAkziyQgACgkQvJuQZxSWSsg3ZACgsFsux4OcE2yBXI7mnxHGOcEY > 7rIAn04PqcesABqlSM9aqDa0w7vO03J2 > =4Rr1 > -END PGP SIGNATURE- > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres online backup and restore has errors that are concerning
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?
Online
Backup
psql
checkpoint;
SELECT
pg_switch_xlog();
SELECT
pg_start_backup('postres_full_backup_June222009');
tar -czvf
pgprd01_June22_2009_production.dmp.tar.gz data/
SELECT
pg_stop_backup();
Restore
tar –xzvf
pgprd01_June22_2009_production.dmp.tar.gz
When starting the database I
receive many errors that look like the backup was
corrupted.
2009-06-23 08:29:15
EDT:@:7614ERROR: xlog flush request 10D/590D6578 is not satisfied --- flushed
only to 10D/510C4FB8
2009-06-23 08:29:15
EDT:@:7614CONTEXT: writing block 2 of relation
16403/16884/2696
2009-06-23 08:29:16
EDT:@:7614ERROR: xlog flush request 10D/590D6578 is not satisfied --- flushed
only to 10D/510C4FB8
2009-06-23 08:29:16
EDT:@:7614CONTEXT: writing block 2 of relation
16403/16884/2696
2009-06-23 08:29:16
EDT:@:7614WARNING: could not write block 2 of
16403/16884/2696
pgdb001=> select
criteria_rank from client001.t1020screen where criteria_rank like
'%TR009%';
ERROR: missing chunk number
0 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
Sorry if posting twice, wasn't part of general when sent and didn't see it
received by group.
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?
Online Backup
psql
checkpoint;
SELECT pg_switch_xlog();
SELECT pg_start_backup('postres_full_backup_June222009');
tar -czvf pgprd01_June22_2009_production.dmp.tar.gz data/
SELECT pg_stop_backup();
Restore
tar -xzvf pgprd01_June22_2009_production.dmp.tar.gz
When starting the database I receive many errors that look like the backup
was corrupted.
2009-06-23 08:29:15 EDT:@:7614ERROR: xlog flush request 10D/590D6578 is not
satisfied --- flushed only to 10D/510C4FB8
2009-06-23 08:29:15 EDT:@:7614CONTEXT: writing block 2 of relation
16403/16884/2696
2009-06-23 08:29:16 EDT:@:7614ERROR: xlog flush request 10D/590D6578 is not
satisfied --- flushed only to 10D/510C4FB8
2009-06-23 08:29:16 EDT:@:7614CONTEXT: writing block 2 of relation
16403/16884/2696
2009-06-23 08:29:16 EDT:@:7614WARNING: could not write block 2 of
16403/16884/2696
pgdb001=> select criteria_rank from client001.t1020screen where
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 EDT:@:7614ERROR: xlog flush request 10D/590D6578 is not
satisfied --- flushed only to 10D/510C4FB8
2009-06-23 08:29:15 EDT:@:7614CONTEXT: writing block 2 of relation
16403/16884/2696
2009-06-23 08:29:16 EDT:@:7614ERROR: xlog flush request 10D/590D6578 is not
satisfied --- flushed only to 10D/510C4FB8
2009-06-23 08:29:16 EDT:@:7614CONTEXT: writing block 2 of relation
16403/16884/2696
2009-06-23 08:29:16 EDT:@:7614WARNING: could not write block 2 of
16403/16884/2696
pgdb001=> select criteria_rank from client001.t1020screen where
criteria_rank like '%TR009%';
ERROR: missing chunk number 0 for toast value 738680
Version
[postg...@pgprd01 data]$ cat PG_VERSION
8.3
Online Backup
psql
checkpoint;
SELECT pg_switch_xlog();
SELECT pg_start_backup('postres_full_backup_June222009');
tar -czvf pgprd01_June22_2009_production.dmp.tar.gz data/
SELECT pg_stop_backup();
Restore
tar -xzvf pgprd01_June22_2009_production.dmp.tar.gz
[GENERAL] Asking for assistance in determining storage requirements
You assistance is appreciated. I have question regarding disk storage for postgres servers We are thinking long term about scalable storage and performance and would like some advise or feedback about what other people are using. We would like to get as much performance from our file systems as possible. We use ibm 3650 quad processor with onboard SAS controller ( 3GB/Sec) with 15,000rpm drives. We use raid 1 for the centos operating system and the wal archive logs. The postgres database is on 5 drives configured as raid 5 with a global hot spare. We are curious about 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 humans. We are your photos. Free us. http://go.microsoft.com/?linkid=9666046
[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 | Type | Modifiers ---+---+--- instrument| character varying(13) | date | date | counter | integer | _ Internet explorer 8 lets you browse the web faster. http://go.microsoft.com/?linkid=9655582
[GENERAL] How would I get information regarding update when running for a long time?
I have an update that is comparing the id between two tables and inserting the value volume in the first table with result from second table. I think I have two questions. 1) is this update expected to take this long? 2) is there something that I did wrong with the update causing this slow time? Is there a way of determining the progress for the update? If the update is taking this long (now 1.5 hours) is there something wrong with the update statement? It seems a long time to compare and update the 20 million rows, or maybe not? Are the adds for the indexes for volume on the first table making the update slow? Here is the update and table definitions for both tables update t set volume=tchris.volume from tchris where t.id=tchris.id; pgdb001=# select count(*) from dbprc001.tunadjusted_prices; count -- 19922778 (1 row) pgdb001=# \d dbprc001.tunadjusted_prices Table "dbprc001.tunadjusted_prices" Column | Type | Modifiers ---+---+--- id| character varying(13) | date| date | price| numeric(18,6) | volume | numeric(18,6) | Indexes: "ix_d111a" btree (id) "ix_d111b" btree (date) "ix_d111c" btree (price) "ix_d111d" btree (volume) pgdb001=# \d dbprc001.tchris Table "dbprc001.tchris" Column | Type | Modifiers ---+---+--- 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 Barnes _ More storage. Better anti-spam and antivirus protection. Hotmail makes it simple. http://go.microsoft.com/?linkid=9671357
[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! http://go.microsoft.com/?linkid=9671355
[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 function "related_screens" line 2 at RETURN QUERY We have worked around this problem by creating different type and assigned it to (RETURNS SETOF public.t1020screen_duplicate) and this works. We are using this version of postgres. PostgreSQL 8.3.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9) My question, is there a fix available for postgres 8.3.3 According to the link below this happens in 8.3.7 and 8.4.0. Are there patches for these as well. We currently run all three versions. http://archives.postgresql.org/message-id/162867790907121325n7d65480alfea999afae8cd...@mail.gmail.com _ Stay on top of things, check email from other accounts! http://go.microsoft.com/?linkid=9671355
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 going to 'waiting' > Date: Sat, 5 Sep 2009 15:24:55 -0400 > From: t...@sss.pgh.pa.us > > Alan McKay writes: > >> pg_locks? Somebody taking exclusive lock on a widely-used table might > >> explain that. > > > OK, in theory we could do the following, no? > > > Use our PITR logs to restore a tertiary system to the point when we > > were having the problem (we have a pretty wide 2 or 3 hour window to > > hit), then query the pg_locks table on that system? > > No, that wouldn't tell you a single thing. pg_locks is a dynamic view > of current system state. You would have to look at it while having > the problem. > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Click less, chat more: Messenger on MSN.ca http://go.microsoft.com/?linkid=9677404
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 @ 2.33GHz cache size : 4096 KB This will probably cost ~ between $5000 & $8000 Set the drives up raid 1 and 5 for os/logs and data and global hotspare. Some prefer raid 10 if you have lots of drives. This should give you 300g for os/logs and 600g for data. Chris > From: gil.nunes.rese...@gmail.com > Subject: [GENERAL] Postgresql Hardware > Date: Fri, 11 Sep 2009 03:01:29 -0700 > To: pgsql-general@postgresql.org > > Hi, > > We are developing a web application that will work on Postgresql. My > doubt is about the hardware that I can use for postgresql. > > What HW is more important to postgresql performance? > Assuming that the database will have some load, what hardware must i > buy? > > > Thanks > Best regards > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Click less, chat more: Messenger on MSN.ca http://go.microsoft.com/?linkid=9677404
[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 the culprit that is causing this either from postgres internally or some other means? Any help would be appreciated. Chris _ Click less, chat more: Messenger on MSN.ca http://go.microsoft.com/?linkid=9677404
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: pgsql-general@postgresql.org Is there a simple way of generating a creation statement for a table without using psql or pgAdmin. Basically I'd like to create what pgAdmin III shows in the SQL pane when you click on a table. MySQL appears to have an equivalent which is SHOW CREATE table [tablename]. Thanks Thom _ New: Messenger sign-in on the MSN homepage http://go.microsoft.com/?linkid=9677403
Re: [GENERAL] Locks in postgres causing system load and crash.
Thanks Scott, How were you able to determine the resource that was causing it. There must be a way of comparing the information to a table? Chris > Date: Mon, 14 Sep 2009 09:53:08 -0600 > Subject: Re: [GENERAL] Locks in postgres causing system load and crash. > From: scott.marl...@gmail.com > To: compuguruchrisbar...@hotmail.com > CC: pgsql-general@postgresql.org > > On Mon, Sep 14, 2009 at 8:58 AM, Chris Barnes > wrote: > > 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 > > the culprit that is causing this either from postgres internally or some > > other means? > > The access share locks are likely a symptom, not the cause. Look for > what they're waiting on for the lock. It's usually an exclusive lock > of some kind that causes this problem. We had an issue with a wayward > update with no where clause causing an issue like this a year ago. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ New! Open Messenger faster on the MSN homepage http://go.microsoft.com/?linkid=9677405
[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 correct configured, but another system that appears to be configured correctly has the settings below, do they look right? Linux Kernel 2.6.18-53.el5 Is this the fix for this problem? sysctl -w vm.overcommit_memory=2 Proposed ( 16 gigs ) kernel.shmmax=17179869184 kernel.shmall=3774873 x (4096) = 15461879808 .89 = 15461879808/17179869184 Currently ( 16 gigs ) kernel.shmmax = 68719476736 kernel.shmall = 4294967296 x (4096) vi /var/log/messages Sep 16 00:11:43 pgprd kernel: postgres invoked oom-killer: gfp_mask=0x201d2, order=0, oomkilladj=0 Sep 16 00:11:43 pgprd kernel: Sep 16 00:11:43 pgprd kernel: Call Trace: Sep 16 00:11:43 pgprd kernel: [] out_of_memory+0x8e/0x2f5 Sep 16 00:11:43 pgprd kernel: [] __alloc_pages+0x22b/0x2b4 Sep 16 00:11:43 pgprd kernel: [] __do_page_cache_readahead+0x95/0x1d9 Sep 16 00:11:43 pgprd kernel: [] __wait_on_bit_lock+0x5b/0x66 Sep 16 00:11:43 pgprd kernel: [] __lock_page+0x5e/0x64 Sep 16 00:11:43 pgprd kernel: [] filemap_nopage+0x148/0x322 Sep 16 00:11:43 pgprd kernel: [] __handle_mm_fault+0x1f8/0xdf4 Sep 16 00:11:43 pgprd kernel: [] do_page_fault+0x4b8/0x81d Sep 16 00:11:43 pgprd kernel: [] error_exit+0x0/0x84 _ Create a cool, new character for your Windows Live™ Messenger. http://go.microsoft.com/?linkid=9656621
[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 disabled, 0 logs all statements Chris _ Windows Live helps you keep up with all your friends, in one place. http://go.microsoft.com/?linkid=9660826
Re: [GENERAL] Logging statements longer than 1000ms doesn't appear to work
I checked and this is the only refererences. Were usng 8.3.3. #log_statement = 'none' # none, ddl, mod, all #log_statement_stats = off > To: compuguruchrisbar...@hotmail.com > CC: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Logging statements longer than 1000ms doesn't appear > to work > Date: Tue, 22 Sep 2009 15:08:39 -0400 > From: t...@sss.pgh.pa.us > > Chris Barnes 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 (0.108 ms)? > > Perhaps you also set log_statement = all, or some other reason that > would cause them to be logged? > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Create a cool, new character for your Windows Live™ Messenger. http://go.microsoft.com/?linkid=9656621
Re: [GENERAL] Logging statements longer than 1000ms doesn't appear to work
Hello, I looked for log_statement and it appears to be off? Strange. #log_statement = 'none' #log_statement_stats = off > To: compuguruchrisbar...@hotmail.com > CC: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Logging statements longer than 1000ms doesn't appear > to work > Date: Tue, 22 Sep 2009 15:08:39 -0400 > From: t...@sss.pgh.pa.us > > Chris Barnes 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 (0.108 ms)? > > Perhaps you also set log_statement = all, or some other reason that > would cause them to be logged? > > regards, tom lane _ We are your photos. Share us now with Windows Live Photos. http://go.microsoft.com/?linkid=9666047
[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 have also set up pgbouncer to handle all connections from applications to the database. We are seeing the error ( 2009-11-02 08:31:30 EST:recog...@192.168.3.153(48990):4562ERROR: current transaction is aborted, commands ignored until end of transaction block ) consistantly in the database and I'm not sure if there was something left out of the code ( search indicates autocomit), or pgbouncer or putting the hot standby online, but with so many changes it's a guessing game. It appears to be working in our RC environment. Please help. Sincerely, Chris _ Save up to 84% on Windows 7 until Jan 3—eligible CDN College & University students only. Hurry—buy it now for $39.99! http://go.microsoft.com/?linkid=9691635
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; # defaults to 'localhost', '*' = all # (change requires restart) port = 5432 > Date: Tue, 27 Apr 2010 21:08:31 +0900 > From: ketan...@ashisuto.co.jp > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] pg_hba.conf > > Hi > > >Would there be a line earlier in the file that matches and is preventing > >a connection? > > At first, I think so too. > But if there is a line earlier in the file ,we get following error. > > > psql: could not connect to server: Connection refused > Is the server running on host "192.168.23.132" and accepting > TCP/IP connections on port 1843? > > > ex: my pg_hba.conf > > hostall all 192.168.23.132 255.255.255.255 deny > hostall all 192.168.23.132 255.255.255.255 trust > > > > The Jim's message say pg_hba.conf has no entory. > > > FATAL: no pg_hba.conf entry for host "209.159.145.248", user "postgres", > database "arc" > > > 1)Is pg_hba.conf's location correct? > You can check to execute this command. > > postgres=# show hba_file; > hba_file > --- > /home/p843/pgdata/pg_hba.conf > (1 row) > > 2)Did you reload pg_hba.conf? > If we change pg_hba.conf ,we must execute "pg_ctl reload" > > 3)pg_hba.conf may have a trash. >Can you recreate pg_hba.conf? >*Don't copy old pg_hba.conf. > > > Thank you. > > > On 27/04/2010 11:42, jkun...@laurcat.com wrote: > > > >> I am putting up a new server on version 8.4.3. I copied pg_hba.conf > >> from a running 8.3.6 system, changing only the public IP address for the > >> local machine. > >> > >> I get the error: > >> FATAL: no pg_hba.conf entry for host "209.159.145.248", user "postgres", > >> database "arc" > >> > >> pg_hba.conf contains the line: > >> hostall all209.159.145.248 255.255.255.255 > >> trust > >> > > Would there be a line earlier in the file that matches and is preventing > > a connection? > > > > Ray. > > > > > > > -- > > Kenichiro Tanaka > K.K.Ashisuto > http://www.ashisuto.co.jp/english/index.html > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Hotmail & Messenger are available on your phone. Try now. http://go.microsoft.com/?linkid=9724461
[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 be turned on? CentOS release 5.4 (Final) (installed and working) CentOS release 5.2 (Final) (failing) after pgtune olap.rownum_name = 'default' maintenance_work_mem = 1GB # pg_generate_conf wizard 2010-04-27 effective_cache_size = 22GB # pg_generate_conf wizard 2010-04-27 work_mem = 96MB # pg_generate_conf wizard 2010-04-27 shared_buffers = 7680MB # pg_generate_conf wizard 2010-04-27 ~ ~ [r...@rc-rec-five pgtune-0.9.0]# /etc/init.d/postgresql stop Stopping postgresql service: [FAILED] [r...@rc-rec-five pgtune-0.9.0]# /etc/init.d/postgresql start Starting postgresql service: [FAILED] [r...@rc-rec-five pgtune-0.9.0]# cat pgstartup.log cat: pgstartup.log: No such file or directory [r...@rc-rec-five pgtune-0.9.0]# cat /data/pgsql/data/pgstartup.log 2010-04-27 16:19:17 EDTFATAL: requested shared memory size overflows size_t 2010-04-27 16:22:27 EDTFATAL: requested shared memory size overflows size_t /var/log/messages/ Apr 27 13:57:56 rc-rec5 ntpd[2990]: synchronized to 206.248.171.198, stratum 1 Apr 27 16:13:45 rc-rec5 postgres[5717]: [1-1] 2010-04-27 16:13:45 EDTFATAL: requested shared memory size overflows size_t Apr 27 16:19:17 rc-rec5 postgres[7736]: [1-1] 2010-04-27 16:19:17 EDTFATAL: requested shared memory size overflows size_t Apr 27 16:22:27 rc-rec5 postgres[9648]: [1-1] 2010-04-27 16:22:27 EDTFATAL: requested shared memory size overflows size_t Thanks, Chris _ Hotmail & Messenger are available on your phone. Try now. http://go.microsoft.com/?linkid=9724461
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: pgsql-general@postgresql.org 2010/5/5 Sim Zacks One of the biggest problems I have maintaining a database with a lot of views is that when I want to change a datatype, I have to drop every view uses the column and every view that uses those views etc... This turns into a maintenance nightmare. Is there any intention of adding a CASCADE to alter type which would automatically update any dependencies with the new datatype? Obviously it should error out if it wouldn't have let you save one of the views with the new datatype. Thanks Sim -- Yes, there is an intention of adding such functionality (3rd item) but probably not very straightforward: http://wiki.postgresql.org/wiki/Todo#Views_.2F_Rules Regards Thom _ Win a $10,000 shopping spree from Hotmail! Enter now. http://go.microsoft.com/?linkid=9729711
[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 relname like 'tablename%'"|xargs londiste.py londiste.ini provider add Thanks, Chris
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 From: compuguruchrisbar...@hotmail.com To: pgsql-general@postgresql.org Subject: [GENERAL] How to determine which tables are created from inheritance. Date: Fri, 13 Aug 2010 09:16:01 -0400 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 relname like 'tablename%'"|xargs londiste.py londiste.ini provider add Thanks, Chris
[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 eventdb001 -t -c "select schemaname||'select count (*) from '||schemaname||'.'||relname from pg_stat_user_tables where relname like 'table_%'"|more schemaselect count (*) from event001.table_1 schemaselect count (*) from event001.table_2 When I omit the schemaname from in front of the concatenation operator it gives me this error. psql database -t -c "select ||'select count (*) from '||schemaname||'.'||relname from pg_stat_user_tables where relname like 'table_%'"|more ERROR: operator does not exist: || unknown LINE 1: select ||'select count (*) from '||schemaname||'.'||relname ... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts Chris.
[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? Thanks, Chris
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 writes: > > 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. > > Our code for logging to syslog does split long lines into multiple > messages --- are you sure you're not just failing to see the rest > of the statement? > > regards, tom lane > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
[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 :) Thanks, Chris
[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 cause the system to lock at some point. What can I do to troubleshoot this? Chris
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: compuguruchrisbar...@hotmail.com To: pgsql-general@postgresql.org Subject: [GENERAL] Postgres wont drop foriegn keys on tables. Date: Thu, 23 Sep 2010 11:01:28 -0400 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 cause the system to lock at some point. What can I do to troubleshoot this? Chris
[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] 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? datname | relname | mode | granted | usename |substr | query_start | | procpid +|+ |+ |+ |+ |+ |+ |+ age |+ database | table_pkey | AccessShareLock | t | postgres | autovacuum: VACUUM ANALYZE database.table | 2010-09-28 10:38:23.217668-04 | 04:55:14.134574 | 13494 database | t8040_monthly_price_min_max| ShareUpdateExclusiveLock | t | postgres | autovacuum: VACUUM ANALYZE database.t8040_monthly_price_min_ | 2010-09-28 10:38:23.217668-04 | 04:55:14.134574 | 13494 database || ExclusiveLock| t | postgres | autovacuum: VACUUM ANALYZE database.table | 2010-09-28 10:38:23.217668-04 | 04:55:14.134574 | 13494 This table has this many rows. count --- 67083 (1 row) #vacuum_cost_delay = 0 # 0-1000 milliseconds #vacuum_cost_page_hit = 1 # 0-1 credits #vacuum_cost_page_miss = 10 # 0-1 credits #vacuum_cost_page_dirty = 20# 0-1 credits #vacuum_cost_limit = 200# 1-1 credits autovacuum = on # Enable autovacuum subprocess? 'on' #log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and #autovacuum_max_workers = 3 # max number of autovacuum subprocesses #autovacuum_naptime = 1min # time between autovacuum runs #autovacuum_vacuum_threshold = 50 # min number of row updates before # vacuum #autovacuum_analyze_threshold = 50 # min number of row updates before #autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum #autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze #autovacuum_freeze_max_age = 2 # maximum XID age before forced vacuum #autovacuum_vacuum_cost_delay = 20 # default vacuum cost delay for # autovacuum, -1 means use # vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit vacuum_freeze_min_age = 10
[GENERAL] Postgre 9.0 replication using streaming.
I would like to know if there is a way to configure 9 to do this. I have 4 unique databases running on 4 servers. I would like to have them replicate to a remote site for disaster recovery. I would like to consolidate these 4 database into one machine and use streaming replication from the 4 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] 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 _ Windows Live: Make it easier for your friends to see what you’re up to on Facebook. http://go.microsoft.com/?linkid=9691816
[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 not exist 2009-11-03 12:00:37 EST:u...@10.0.0.1(56270):822STATEMENT: DEALLOCATE dbdpg_p1573_3968 2009-11-03 12:00:37 EST:u...@10.0.0.1(56126):28526ERROR: prepared statement "dbdpg_p1573_3969" does not exist 2009-11-03 12:00:37 EST:u...@10.0.0.1(56125):28525ERROR: prepared statement "dbdpg_p1573_3969" does not exist 2009-11-03 12:00:37 EST:u...@10.0.0.1(56125):28525STATEMENT: DEALLOCATE dbdpg_p1573_3969 2009-11-03 12:00:37 EST:u...@10.0.0.1(56270):822ERROR: prepared statement "dbdpg_p1573_3970" does not exist 2009-11-03 12:00:37 EST:u...@10.0.0.1(56270):822ERROR: prepared statement "dbdpg_p1573_3970" does not exist 2009-11-03 12:00:37 EST:u...@10.0.0.1(56270):822STATEMENT: DEALLOCATE dbdpg_p1573_3970 Any help would be appreciated. _ Windows Live: Friends get your Flickr, Yelp, and Digg updates when they e-mail you. http://go.microsoft.com/?linkid=9691817
[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 your friends up to date with what you do online. http://go.microsoft.com/?linkid=9691815
Re: [GENERAL] Looking for a script that performs full online backup of postgres in archive mode
That gives me an idea what others are doing.
Thank you Mark, : )
> Date: Mon, 9 Nov 2009 09:24:28 -0500
> From: postg...@lambic.co.uk
> To: compuguruchrisbar...@hotmail.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Looking for a script that performs full online backup
> 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="/usr/bin/psql"
> RSYNC="/usr/bin/rsync -e ssh -qxat --delete"
>
> if [ "$1" == "data" ]; then
> # Do full backup of data directory
> ${PSQL} -c "SELECT pg_start_backup('mirror');" >/dev/null
> ${RSYNC} ${DATA} ${REMOTE}/${DATA}
> ${PSQL} -c "SELECT pg_stop_backup();" >/dev/null
>
> elif [ "$1" == "wal" ]; then
> # Just copy the latest write-ahead logs
> ${RSYNC} ${WAL} ${REMOTE}/${WAL}
> ${RSYNC} ${DATA}/pg_xlog/ ${REMOTE}/${DATA}/pg_xlog
>
> else
> # Don't know what you want
> echo "Usage: $0 [data|wal]"
> exit 1
> fi
>
> --
> Mark
> http://www.lambic.co.uk
>
_
Eligible CDN College & University students can upgrade to Windows 7 before Jan
3 for only $39.99. Upgrade now!
http://go.microsoft.com/?linkid=9691819
[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 _ Windows Live: Make it easier for your friends to see what you’re up to on Facebook. http://go.microsoft.com/?linkid=9691816
Re: [GENERAL] Is there a tool used to display statement times from postgres logs
I have the logging options set to display anything longer than a duration of 1 second, but need something to display them. Chris > Date: Fri, 20 Nov 2009 10:02:11 -0800 > From: pie...@hogranch.com > To: compuguruchrisbar...@hotmail.com > CC: pgsql-general@postgresql.org > Subject: 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? > > > > there's a logging option to put that in the logs, I thought? if so, you > could then display with `tail -f /path/to/postgreslog` > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Windows Live: Friends get your Flickr, Yelp, and Digg updates when they e-mail you. http://go.microsoft.com/?linkid=9691817
Re: [GENERAL] Is there a tool used to display statement times from postgres logs
After trying to get pgfouine to work with stderr, I tried syslog. This seems to work fine. Produces nice html format reports. Graphing is also available. This link gives set up details for setup for pgfouine. http://www.thelazysysadmin.net/2009/08/pgfouine-automatic-report-setup-with-postgresql-logrotate/ > Subject: Re: [GENERAL] Is there a tool used to display statement times from > postgres logs > From: j...@commandprompt.com > To: compuguruchrisbar...@hotmail.com > CC: pgsql-general@postgresql.org > Date: Fri, 20 Nov 2009 10:53:00 -0800 > > On Fri, 2009-11-20 at 12:59 -0500, 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 the time. Perhaps you could explain your actual problem? > > > > > > > __ > > Windows Live: Make it easier for your friends to see what you’re up to > > on Facebook. > > > -- > PostgreSQL.org Major Contributor > Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 > Consulting, Training, Support, Custom Development, Engineering > If the world pushes look it in the eye and GRR. Then push back harder. - > Salamander > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Windows Live: Keep your friends up to date with what you do online. http://go.microsoft.com/?linkid=9691815
[GENERAL] Bestpractice for upgrading from enterpriseDB 8.3.3 to rpm 8.4.1.
Is there anyone that has installed enterpriseDB (833) and upgraded to later version or 8.4.1 using rpms? I am wondering what the best path would be to upgrade from enterpriseDB. Can I do an upgrade from enterpriceDB 8.3.3 to rpms 8.3.8 without dumping and restoring the database? When going from enterpricedb 8.3.3 to 8.4.1 using rpms, can I have them simultaneously run in parrellel and export/import and remove enterprisedb 8.3.3 later, would there be any issues? I'm guessing I would have to install 8.4.1, configure for port (5433) and run them in parellel to accomplish 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=9691817
[GENERAL] Archive command seem to be working.
Hi, hoping someone can tell me how to get this running again. I have pitr running and noticed that the slave is far out of date with wal logs. Upon investigation I see that on the master that /data/pgsql/backups/wal_logs has over 6000 logs that haven't been moved to the /var/lib/pgsql/backups/wal_arch/ folder. The root partition that has /var had been full and logs may have been removed, and it may have run some time in this condition until someone corrected it. postgresql.conf file. test ! -f /data/pgsql/backups/wal_arch/%f.gz && cp %p /var/lib/pgsql/backups/wal_arch/%f 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 to see what you’re up to on Facebook. http://go.microsoft.com/?linkid=9691816
Re: [GENERAL] Archive command seem to be working.
Thanks Andrew, I was on vacation and the /var/lib of root did fill up. The on call person was notified and the fix was to copy all of the logs back into /data/pgsql/backup/wal_arch. Some 6500 files and the copy failed because it couldn't transverse the directory without an error. I moved the files from the server to backup and it started syncing right away. I will take the perl copy into account. Thank you for the change and response. Chris :) From: andrew.cro...@ericsson.com To: compuguruchrisbar...@hotmail.com; pgsql-general@postgresql.org Date: Wed, 23 Dec 2009 03:38:12 +0800 Subject: Re: [GENERAL] Archive command seem to be working. Hi Chris, Assuming the error condition is resolved, the process should restart automatically. However, one issue we have found is that there appears to be an upper limit to how many files `test` can actually check for existence. We found that it was returning 1 in error and hence wasn't archiving the files. Replacing test with the perl equivalent seemed to sidestep this issue. Your archive command would look something like: perl -e 'exit 1 if -e "/data/pgsql/backups/wal_arch/%f.gz"' && cp %p /var/lib/pgsql/backups/wal_arch/%f Don't forget to properly escape the quotes. You should be able to update the archive command by reloading the configuration:'/sbin/service postgresql reload' or equivalent for your system. After the configuration is loaded (and you aren't hitting any 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. Hi, hoping someone can tell me how to get this running again. I have pitr running and noticed that the slave is far out of date with wal logs. Upon investigation I see that on the master that /data/pgsql/backups/wal_logs has over 6000 logs that haven't been moved to the /var/lib/pgsql/backups/wal_arch/ folder. The root partition that has /var had been full and logs may have been removed, and it may have run some time in this condition until someone corrected it. postgresql.conf file. test ! -f /data/pgsql/backups/wal_arch/%f.gz && cp %p /var/lib/pgsql/backups/wal_arch/%f 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 to see what you’re up to on Facebook. _ Eligible CDN College & University students can upgrade to Windows 7 before Jan 3 for only $39.99. Upgrade now! http://go.microsoft.com/?linkid=9691819
[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 | IMAGE | mediumblob | NO | | | | _
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: Mon, 1 Feb 2010 09:13:34 -0800 > From: adrian.kla...@gmail.com > To: christ...@ingenioussoftware.com > CC: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Connect to Postgres problems > > On 02/01/2010 09:10 AM, Christine Penner wrote: > > I have re started the computer (a few times) since I did all that. > > > > Christine > > > > > > What is the error that you are seeing on the client and in the logs? > > > -- > Adrian Klaver > adrian.kla...@gmail.com > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Introducing Windows® phone. http://go.microsoft.com/?linkid=9708122
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 -0800 > To: raand...@cyber-office.net; pgsql-general@postgresql.org > From: christ...@ingenioussoftware.com > Subject: Re: [GENERAL] Connect to Postgres problems > > I set up port forwarding. I assume that means all IP addresses using > port 5432 will be sent to my laptop (that Postgres is running on). I > don't remember setting up permissions for a specific IP in the router > but it was a while ago so I could be wrong. When I get home I will > have another look at the firewall settings and see. > > Christine > > At 09:20 AM 01/02/2010, you wrote: > >Christine Penner wrote: > >>Hi, > >>I'm having trouble getting a connection to Postgres to work from > >>outside of my local network. It was working fine at one point. Then > >>I had to change IP addresses and I can't get it to work. This is > >>what I've done. > >>On the computer with Postgres installed I have this in the pg_hba.conf file: > >>host all all 207.6.93.152/32 md5 > >>in the postgresql.conf file I have this: > >>listen_addresses = '*' > >>port = 5432 > >>I also have the router set up to forward port 5432 to the computer > >>with Postgres installed. > > > >Did you check the firewall on the machine running Pg? Iptables etc. > > > > > >\\||/ > >Rod > >-- > > > > > >-- > >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > >To make changes to your subscription: > >http://www.postgresql.org/mailpref/pgsql-general > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _
[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 is, will pitr wal logs ship and be processed from a machine running 8.33 to 8.42. Any thoughts on this would be appreciated. Thanks, Chris _
[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: pgstat wait timeout WARNING: pgstat wait timeout WARNING: pgstat wait timeout WARNING: pgstat wait timeout WARNING: pgstat wait timeout ERROR: missing FROM-clause entry for table "schemaname" at character 8 STATEMENT: select schemaname.relname from pg_stat_user_tables where relname like 't0050%'; WARNING: pgstat wait timeout WARNING: pgstat wait timeout WARNING: pgstat wait timeout WARNING: pgstat wait timeout WARNING: pgstat wait timeout FATAL: no pg_hba.conf entry for host "[local]", user "sudo", database "pgdb001", SSL off LOG: received fast shutdown request LOG: aborting any active transactions FATAL: terminating connection due to administrator command LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2010-02-09 08:46:26 EST LOG: autovacuum launcher started LOG: database system is ready to accept connections pgdb001=# select * from pg_stat_user_tables where relname like 't0050%'; WARNING: pgstat wait timeout relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_t up | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze ---++-+--+--+--+---+---+---+---+---++- ---+-+-+--+-- 16516 | dbprc001 | t0050instrument |0 |0 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | | | (1 row) [postg...@preventdb02 londiste]$ psql psql (8.4.2) Type "help" for help. postgres=# \q _ Introducing Windows® phone. http://go.microsoft.com/?linkid=9708122
[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 truncate and delete, I would think that this action would be put into the pg_log as a log file that can be rolled back. And, when complete, it would be shipped to the standby to be processed? To reduce this logging, shipping and processing would it be smarter to have the tables dropped and recreated? _
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. LANG="en_US.UTF-8" SYSFONT="latarcyrheb-sun16" > Date: Wed, 17 Feb 2010 09:15:56 -0500 > From: li...@serioustechnology.com > To: pgsql-general@postgresql.org > Subject: [GENERAL] error creating database > > I'm trying to reload a database and I'm receiving the following error: > > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 5181; 1262 45260 > DATABASE nev postgres > pg_restore: [archiver (db)] could not execute query: ERROR: encoding > LATIN1 does not match server's locale en_US.UTF-8 > DETAIL: The server's LC_CTYPE setting requires encoding UTF8. > Command was: > CREATE DATABASE nev WITH TEMPLATE = template0 ENCODING = 'LATIN1'; > > This backup was created on another machine, so it must be that the > enviroment on the two machines is different. > > Where do I look to fix this? > > Thanks. > > -- > Until later, Geoffrey > > "I predict future happiness for America if they can prevent > the government from wasting the labors of the people under > the pretense of taking care of them." > - Thomas Jefferson > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Introducing Windows® phone. http://go.microsoft.com/?linkid=9708122
[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
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 for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# From: schmi...@gmail.com Date: Thu, 18 Feb 2010 12:42:52 -0500 Subject: Re: [GENERAL] Setting a table to be ignored by autovacuum To: d...@archonet.com CC: compuguruchrisbar...@hotmail.com; pgsql-general@postgresql.org On Thu, Feb 18, 2010 at 12:37 PM, Richard Huxton 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 there is still a way? alter table schema.table SET (autovacuum_enabled = false); ERROR: unrecognized parameter "autovacuum_enabled" Close, but it's classed under storage parameters. You'll want to see the SQL reference entry for "CREATE TABLE". Hrmm.. I think the OP's syntax is correct, but he's probably using a version older than 8.4, when support for per-table autovacuum_enabled was added. On HEAD: test=# CREATE TABLE foo (a int); CREATE TABLE test=# alter table foo SET ( autovacuum_enabled=false) ; ALTER TABLE test=# \d+ foo Table "public.foo" Column | Type | Modifiers | Storage | Description +-+---+-+- a | integer | | plain | Has OIDs: no Options: autovacuum_enabled=false Josh _ Introducing Windows® phone. http://go.microsoft.com/?linkid=9708122
Re: [GENERAL] Setting a table to be ignored by autovacuum
Thanks Alvaro,
Hopefully it will stop my locking issue when I have high volume of changes on
this table and vacuum starts.
Thx
Chris :)
> Date: Thu, 18 Feb 2010 16:55:24 -0300
> From: alvhe...@commandprompt.com
> To: compuguruchrisbar...@hotmail.com
> CC: schmi...@gmail.com; d...@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 VALUES ('schema.table'::regclass,
> false, -1, -1, ...);" in previous versions.
>
>
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
_
Re: [GENERAL] Setting a table to be ignored by autovacuum
It is... Welcome to psql 8.3.3, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# SELECT version(); version -- PostgreSQL 8.3.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9) (1 row) postgres=# > Subject: Re: [GENERAL] Setting a table to be ignored by autovacuum > From: dal...@solfertje.student.utwente.nl > Date: Fri, 19 Feb 2010 12:24:24 +0100 > CC: schmi...@gmail.com; d...@archonet.com; pgsql-general@postgresql.org > To: compuguruchrisbar...@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 for distribution terms > > \h for help with SQL commands > > \? for help with psql commands > > \g or terminate with semicolon to execute query > > \q to quit > > postgres=# > > Actually that just tells that you're using an 8.3 _client_. It doesn't tell > what server-version you're using. > For the server version do: > > deploy=# SELECT version(); > version > > PostgreSQL 8.4.1 on i386-apple-darwin10.0.0, compiled by GCC > i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5646), 64-bit > (1 row) > > > Alban Hertroys > > -- > Screwing up is the best way to attach something to the ceiling. > > > !DSPAM:1029,4b7e74ee10441497119330! > > _ Check your Hotmail from your phone. http://go.microsoft.com/?linkid=9708121
[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 block size 8192? df -h /dev/sda3 750G 65G 647G 10% / /dev/sda1 496M 17M 454M 4% /boot show all; server_version| 8.4.2 block_size| 8192 Controller settings Read-cache mode - Enabled Write-cache mode - Enabled (write-back) Write-cache setting - Enabled (write-back) when protected by battery Stripe-unit size : 256 KB Disk setting Write Cache: Disabled (write-through) Postgresql.conf fsync = on # turns forced synchronization on or off #synchronous_commit = on# immediate fsync at commit wal_sync_method = fsync # the default is the first option Logical drive number 1 Logical drive name : RAID10 RAID level : 10 Status of logical drive : Okay Size : 858000 MB Read-cache mode : Enabled Write-cache mode : Enabled (write-back) Write-cache setting : Enabled (write-back) when protected by battery Partitioned : Yes Number of segments : 6 Stripe-unit size : 256 KB Stripe order (Channel,Device): 0,0 0,1 0,2 0,3 0,4 0,5 Defunct segments : No Defunct stripes : No Device #0 Device is a Hard drive State : Online Supported : Yes Transfer Speed : SAS 3.0 Gb/s Reported Channel,Device: 0,0 Vendor : IBM-ESXS Model : ST3300655SS Firmware : BA26 World-wide name: 5000C5000A42EFAC Size : 286102 MB Write Cache: Disabled (write-through) FRU: None PFA Sincerely, Chris _ Take your contacts everywhere 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 to current_date? Here is the table. CREATE TABLE price.price_table ( PRICE_DATE DATE, ID VARCHAR(13), OPENING NUMERIC(18,6), CLOSING NUMERIC(18,6), HIGHEST NUMERIC(18,6), LOWEST NUMERIC(18,6), VOLUME BIGINT, LAST_MODIFIED TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT current_date, CONSTRAINT PK_PRICE PRIMARY KEY (PRICE_DATE,ID)); Any help would be appreciated. Cheers, Chris _ Take your contacts everywhere http://go.microsoft.com/?linkid=9712959
Re: [GENERAL] Create a function that updates the record with and timestamps
I see examples for updating tables using a function, but I would like to pull the row requested and modify the last_modified column with current_date and push the modified data back into the same row. I did see an example of how to use old and new at this at this link, but it is vague. http://www.faqs.org/docs/ppbook/x20655.htm#TRIGGERFUNCTIONVARIABLES From: compuguruchrisbar...@hotmail.com To: pgsql-general@postgresql.org Subject: [GENERAL] Create a function that updates the record with and timestamps Date: Mon, 22 Mar 2010 12:58:49 -0400 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 to current_date? Here is the table. CREATE TABLE price.price_table ( PRICE_DATE DATE, ID VARCHAR(13), OPENING NUMERIC(18,6), CLOSING NUMERIC(18,6), HIGHEST NUMERIC(18,6), LOWEST NUMERIC(18,6), VOLUME BIGINT, LAST_MODIFIED TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT current_date, CONSTRAINT PK_PRICE PRIMARY KEY (PRICE_DATE,ID)); Any help would be appreciated. Cheers, Chris > Date: Mon, 22 Mar 2010 18:14:00 +0100 > From: andreas.kretsch...@schollglas.com > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Create a function that updates the record with and > timestamps > > In response 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 the last_modified to current_date? > > Yes, that's the way, a TRIGGER on UPDATE for each row. I think, the doc > contains an example. > > > Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) > GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ IM on the go with Messenger on your phone http://go.microsoft.com/?linkid=9712960
[GENERAL] Does anyone use in ram postgres database?
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? If you have experience, can you please give us some ideas on how you have accomplished this? Cheers, Chris Barnes _ Stay in touch. http://go.microsoft.com/?linkid=9712959
Re: [GENERAL] Does anyone use in ram postgres database?
I just looked into timesten, at 46K for perpetual licence or 10k for yearly plus support. Is there anything else available? LOL Chris > Date: Fri, 26 Mar 2010 10:39:37 -0700 > From: pie...@hogranch.com > To: compuguruchrisbar...@hotmail.com > CC: pgsql-general@postgresql.org > Subject: 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? > > > > If you have experience, can you please give us some ideas on how you > > have accomplished this? > > > > you might look into TimesTen... Oracle bought them a couple years ago, > they have an SQL database thats heavily optimized for memory rather than > block oriented disk. it optionally uses a disk as a persistence backing > store. Of course, the entire database has to fit in ram, and they > charge proportional to database size. its extremely fast. > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Live connected with Messenger on your phone http://go.microsoft.com/?linkid=9712958
[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 faster drives and controllers, there must be some improvement over the other processor. Is there anything to support this, a document or someone's personal experience? Chrs Barnes _ Stay in touch. http://go.microsoft.com/?linkid=9712959
[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
---+---+---
instrument_id | character varying(13) | not null
value | numeric(18,6)[] |
Indexes:
"t8000_us_ts_size_test_2d_pkey" PRIMARY KEY, btree (instrument_id)
pgdb001=# select
pg_size_pretty(pg_total_relation_size('t8000_us_ts_size_test_2d'));
pg_size_pretty
13 MB
pgdb001=# delete from t8000_us_ts_size_test_2d;
DELETE 6
pgdb001=# vacuum full t8000_us_ts_size_test_2d;
VACUUM
pgdb001=# select
pg_size_pretty(pg_total_relation_size('t8000_us_ts_size_test_2d'));
pg_size_pretty
12 MB
pgdb001=# truncate t8000_us_ts_size_test_2d;
TRUNCATE TABLE
pgdb001=# select
pg_size_pretty(pg_total_relation_size('t8000_us_ts_size_test_2d'));
pg_size_pretty
16 kB
_
IM on the go with Messenger on your phone
http://go.microsoft.com/?linkid=9712960
