Re: [GENERAL] using a postgres table as a multi-writer multi-updater queue

On 11/23/2015 4:41 AM, Chris Withers wrote: Hi All, I wondered if any of you could recommend best practices for using a postgres table as a queue. Roughly speaking, 100-200 workers will vomit rows and rates of a few hundres per second into the table leaving the status as new and then as many

[GENERAL] error messages not getting logged when running script from cron

I have a cron script that does the following: PGPASSWORD=$PGPASSWORD /opt/PostgreSQL/9.4/bin/pg_dump -t RECORDER -Fc $i -U postgres -Z0 | xz -9 > "$backup_dir/$i-$timeslot-database" xzcat "$backup_dir/$i-$timeslot-database" | /opt/PostgreSQL/9.4/bin/pg_restore -h $backupHost -U postgres -d

Re: [GENERAL] error messages not getting logged when running script from cron

On Fri, Nov 20, 2015 at 5:09 PM, anj patnaik wrote: > 0 20 * * * db_backup.sh 1> /dev/null 2>&1 | mail -s "backup cron" > myem...@comp.com > > I am re-directing stderr to stdout and then sending that to email. But you are redirecting stdout (and therefore also stderr) to

Re: [GENERAL] error messages not getting logged when running script from cron

anj patnaik writes: > My cron tab entry: > 0 20 * * * db_backup.sh 1> /dev/null 2>&1 | mail -s "backup cron" > myem...@comp.com > I am re-directing stderr to stdout and then sending that to email. Uh, read it again: you're redirecting stdout to /dev/null and then redirecting

Re: [GENERAL] error messages not getting logged when running script from cron

You could also try tweaking the following attached backup script. Caution, I wrote this quickly from a skeleton script and has not been tested. On Mon, Nov 23, 2015 at 3:21 PM, Tom Lane wrote: > anj patnaik writes: > > My cron tab entry: > > > 0 20 * * *

Re: [GENERAL] error messages not getting logged when running script from cron

On 11/23/2015 12:21 PM, Tom Lane wrote: >0 20 * * * db_backup.sh 1> /dev/null 2>&1 | mail -s "backup cron" >myem...@comp.com >I am re-directing stderr to stdout and then sending that to email. Uh, read it again: you're redirecting stdout to /dev/null and then redirecting stderr to go where

[GENERAL] current_query='IDLE" in pg_stat_activity

Hi , I am trying to check current running sql queries in postgres through pg_stat_activity .However, in the current_query column I could see only "IDLE" . I am not able to see the query . Thanks Paramjib Baruah

Re: [GENERAL] current_query='IDLE" in pg_stat_activity

On 23/11/2015 10:33, paramjib baruah wrote: I am trying to check current running sql queries in postgres through pg_stat_activity .However, in the current_query column I could see only "IDLE" . I am not able to see the query . That would suggest no query is running, what suggests to you that

[GENERAL] using a postgres table as a multi-writer multi-updater queue

Hi All, I wondered if any of you could recommend best practices for using a postgres table as a queue. Roughly speaking, 100-200 workers will vomit rows and rates of a few hundres per second into the table leaving the status as new and then as many workers as needed to keep up with the load

[GENERAL] Problems with pg_upgrade after change of unix user running db.

I got this error trying to upgrade one of our database clusters (happily in testing) from 9.2 to 9.4: Old and new cluster install users have different values for pg_authid.oid Important background here is that we used to run the database as the postgres unix user, but recently we had changed it

Re: [GENERAL] using a postgres table as a multi-writer multi-updater queue

Hello. On 23.11.2015 11:41, Chris Withers wrote: > Hi All, > > I wondered if any of you could recommend best practices for using a postgres > table as a queue. Roughly speaking, 100-200 workers will vomit rows and rates > of > a few hundres per second into the table leaving the status as new

Re: [GENERAL] [ADMIN] How to drop stats on table

Sridhar N Bamandlapally wrote: > the actual issue is, when > > 1. temp table is created with rows > 2. stats/analyze on table (tmp1) > 3. table dropped (tmp1) > > but in stats related catalog tables a blot is created What is a blot in this context? Are you talking about "bloat"? > In this

Re: [GENERAL] What could cause CREATE TEMP... "could not read block" error?

Chris Richards wrote: > Adrian is correct. This worked by itself whereas using it in the creation of > the temporary table > failed. > mdb-> SELECT pq.* FROM policyqueue AS pq > mdb-> JOIN seed_progress AS sp ON pq.id =sp.polidx; > > I checked the query Albe suggested; there were

Re: [GENERAL] current_query='IDLE" in pg_stat_activity

You have not specified your O/S or PostgreSQL version, but if you are running on a Linux O/S, you can run the attached current_queries.sh, which loops every 5 seconds by default. Note the usage options below: usage() { echo "Usage: $0 [-s -d -h -U -p -x -i]" echo "-x will exit after 1

Re: [GENERAL] ??: postgres cpu 100% need help

On Mon, Nov 23, 2015 at 9:30 AM, Kevin Grittner wrote: > On Mon, Nov 23, 2015 at 12:20 AM, 657985...@qq.com <657985...@qq.com> wrote: > >> [root@pg1 ~]# cat /sys/kernel/mm/transparent_hugepage/enabled >> [always] madvise never >> [root@pg1 ~]# cat

Re: [GENERAL] using a postgres table as a multi-writer multi-updater queue

On Mon, Nov 23, 2015 at 12:54 PM, John R Pierce wrote: > On 11/23/2015 2:41 AM, Chris Withers wrote: >> >> >> If it's totally wrong, how should I be looking to approach the problem? > > depending on where these queue entries are coming from, I'd considering > using a message

Re: [GENERAL] using a postgres table as a multi-writer multi-updater queue

On 11/23/2015 2:51 PM, Merlin Moncure wrote: On Mon, Nov 23, 2015 at 12:54 PM, John R Pierce wrote: > >depending on where these queue entries are coming from, I'd considering >using a message queueing system like AMS, MQseries, etc, rather than trying >to use a relational

[GENERAL] JSON path wild cards?

Is there a way to specify a wild card in a json path? For example I have the following json doc: [ {“a”:1,”b”: [ { “x”: 7,”y”:8,”z”:9} ] }, {“a”:2,”b”: [ { “x”: 4,”y”:5,”z”:6} ] }, … ] How do I write a select clause that can return the values for all b x values something like [{b:x}] that

Re: [GENERAL] JSON path wild cards?

On Tue, Nov 24, 2015 at 1:39 PM, Dennis wrote: > Is there a way to specify a wild card in a json path? No. > For example I have the following json doc: > > [ {“a”:1,”b”: [ { “x”: 7,”y”:8,”z”:9} ] }, {“a”:2,”b”: [ { “x”: 4,”y”:5,”z”:6} ] }, … ] > > How do I write a select

Re: [GENERAL] JSON path wild cards?

Thanks, weirdly I just stumbled on the idea when your reply came in, of using jsonb_array_elements function wrapped in a CTE that uses the containment operator within the CTE to select just the json docs I want and then I can select the specific json key values from each jsonb row/object

Re: [GENERAL] ??: postgres cpu 100% need help

On Mon, Nov 23, 2015 at 12:20 AM, 657985...@qq.com <657985...@qq.com> wrote: > [root@pg1 ~]# cat /sys/kernel/mm/transparent_hugepage/enabled > [always] madvise never > [root@pg1 ~]# cat /sys/kernel/mm/transparent_hugepage/defrag > [always] madvise never There's your problem. You need to set

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

On 11/23/15 5:12 AM, Benedikt Grundmann wrote: So I would love to know what the recommended way to go forward is. Ideally it avoids using the old postgres unix and database user (we want to completely get rid of it eventually, but if I have to do some additional one off work this time to get

Re: [GENERAL] using a postgres table as a multi-writer multi-updater queue

On 11/23/2015 2:41 AM, Chris Withers wrote: If it's totally wrong, how should I be looking to approach the problem? depending on where these queue entries are coming from, I'd considering using a message queueing system like AMS, MQseries, etc, rather than trying to use a relational

Re: [GENERAL] using a postgres table as a multi-writer multi-updater queue

On 11/23/15 6:12 AM, Ladislav Lenart wrote: I suggest an excellent read on this topic: http://www.depesz.com/2013/08/30/pick-a-task-to-work-on/ Highly recommended if you haven't read it yet. One thing it doesn't mention that you need to be aware of is the vacuum workload on a queue table.