Re: [GENERAL] Weird performance difference
On Oct 20, 2017, at 3:31 PM, Justin Pryzby <pry...@telsasoft.com> wrote:
>
> On Fri, Oct 20, 2017 at 03:08:26PM -0800, Israel Brewster wrote:
>> Summary: the following query takes around 12 seconds on my test machine. On
>> my production machine, it's at half an hour and counting. What's going on?
>>
>> which, when run on my test server, has this explain analyze output:
>> https://explain.depesz.com/s/4piv <https://explain.depesz.com/s/4piv>.
>> Around 12 second runtime, which isn't too bad (in the grand scheme of
>> things), although there is probably room for improvement.
>
> Are these cast to ::date cast is really needed (Alternately, do you have an
> index on column::date ?)
> |WHERE outtime::date>='2017-01-01'
> |ON outtime::date BETWEEN oag_schedules.startdate
> |AND outtime::date BETWEEN oag_batches.eff_from
Possibly not, but outtime is a timestamp while startdate and eff_from are
simple date fields. I was being explicit :-) and no, I don't have an index on
column::date, that's certainly something I can try to optimize things.
> The problem is clearly here:
>
> Merge Join (cost=30,604.12..31,301.12 ROWS=1 width=76) (actual
> time=1,153.883..9,812.434 ROWS=3,420,235 loops=1)
>Merge Cond: oag_schedules.flightnum)::text) =
> (legdetail.flightnum)::text) AND ((oag_schedules.origin)::text =
> (legdetail.legfrom)::text))
>Join Filter: (((legdetail.outtime)::date >= oag_schedules.startdate) AND
> ((legdetail.outtime)::date <= COALESCE(oag_schedules.enddate,
> 'infinity'::date)) AND (date_part('isodow'::text,
> ((legdetail.outtime)::date)::timestamp without time zone) = ANY
> ((oag_schedules.frequency)::double precision[])))
>ROWS REMOVED BY JOIN FILTER: 6822878
>
> Can you send "explain" (not explain analyze) for the production server?
Not for the full query - it only just completed, after 70 minutes or so, and I
wasn't running under EXPLAIN ANALYZE. Running with a shorter date range of only
7 days, as you suggest below: https://explain.depesz.com/s/r80j
<https://explain.depesz.com/s/r80j>
I notice that this is a COMPLETELY different query plan from what I got on my
test machine, but I'm not sure if that's a good thing or not. The end result is
obviously not.
>
> And \d for those tables.
Sure:
flightlogs=# \d legdetail
Table "public.legdetail"
Column | Type | Modifiers
+--+
id | integer | not null default
nextval('legdetail_id_seq'::regclass)
logid | integer |
flightnum | character varying(32)|
legfrom| character varying(6) |
legto | character varying(6) |
pax| integer | default 0
cargo | integer | default 0
legdate| date | default now()
outtime| timestamp with time zone | not null
ontime | timestamp with time zone |
offtime| timestamp with time zone |
intime | timestamp with time zone |
blocktime | interval |
flighttime | interval |
mail | integer | default 0
csom | character(1) |
nonrevpax | smallint |
legsic | character varying(128) |
nonrevfrt | integer |
ti | boolean | default false
Indexes:
"legdetail_pkey" PRIMARY KEY, btree (id)
"csom_idx" btree (csom)
"flightnum_idx" btree (flightnum)
"legdate_idx" btree (legdate)
"legfrom_idx" btree (legfrom)
"legto_idx" btree (legto)
"logid_idx" btree (logid)
"outtime_idx" btree (outtime)
Foreign-key constraints:
"logfk" FOREIGN KEY (logid) REFERENCES logs(id)
Triggers:
legdetail_audit AFTER UPDATE ON legdetail FOR EACH ROW WHEN (old.* <>
new.*) EXECUTE PROCEDURE changeaudit()
update_hours_trigger AFTER INSERT OR DELETE OR UPDATE ON legdetail FOR EACH
ROW EXECUTE PROCEDURE update_hours()
update_logtime_trigger AFTER INSERT OR DELETE OR UPDATE ON legdetail FOR
EACH ROW EXECUTE PROCEDURE update_logtime()
update_status_legs AFTER INSERT OR DELETE OR UPDATE ON legdetail FOR EACH
ROW EXECUTE PROCEDURE acstatupdate_loc()
flightlogs=# \d oag_batches
Foreign table "public.oag_batches"
Column | Type | Modifiers | FDW Options
--+--+---+--
batchid | uuid | not null | (column_name 'batchid')
name | character varying(256) | not null
[GENERAL] Weird performance difference
Summary: the following query takes around 12 seconds on my test machine. On my production machine, it's at half an hour and counting. What's going on?Details:As a first stab at getting some data I need, I've developed the following SQL query:SELECT legfrom, count(*) as totaldeps, count(*) filter (where depdly<='00:00:00'::interval) as d0, count(*) filter (where depdly<='00:05:00'::interval) as d5, count(*) filter (where depdly<='00:14:00'::interval) as d14, count(*) filter (where depdly<='00:30:00'::interval) as d30, count(*) filter (where legto=destination) as totalarriv, count(*) filter (where arrdly<='00:00:00'::interval AND legto=destination) as a0, count(*) filter (where arrdly<='00:05:00'::interval AND legto=destination) as a5, count(*) filter (where arrdly<='00:14:00'::interval AND legto=destination) as a14, count(*) filter (where arrdly<='00:30:00'::interval AND legto=destination) as a30, count(*) filter (where legto!=destination) as divFROM(SELECT distinct on (legdetail.flightnum, legfrom, outtime) legdetail.flightnum, legfrom, legto, outtime, intime, depart_time, arrival_time, destination, outtime::time-oag_schedules.depart_time as depdly, intime::time-oag_Schedules.arrival_time as arrdlyFROM oag_batchesNATURAL JOIN oag_schedulesINNER JOIN legdetail ON outtime::date BETWEEN oag_schedules.startdate AND coalesce(oag_schedules.enddate,'infinity') AND outtime::date BETWEEN oag_batches.eff_from AND coalesce(oag_batches.eff_to,'infinity') AND extract(isodow from outtime::date)=ANY(frequency) AND oag_schedules.flightnum::text=legdetail.flightnum AND oag_schedules.origin=legdetail.legfromWHERE outtime::date>='2017-01-01' AND legdetail.flightnum~E'^[0-9]*$' AND legdetail.flightnum::integer < 8000 AND csom='S'ORDER BY legdetail.flightnum, legfrom, outtime, oag_batches.eff_from DESC) s1GROUP BY legfromORDER BY legfrom;which, when run on my test server, has this explain analyze output: https://explain.depesz.com/s/4piv. Around 12 second runtime, which isn't too bad (in the grand scheme of things), although there is probably room for improvement.oag_batches and oag_schedules are postgres_fdw foreign tables in the same database cluster (different DB same host/server instance). oag_schedules has about 80,000 rows, legdetail has about 60,000 rows, and oag_batches has 125 rows, so while we are dealing with a fair amount of data, it's nothing extreme - I have tables with billions of rows that it handles just fine.With the query comfortably running on my test machine, I went ahead and tried it out on my production machine. And I waited. And waited. And waited some more. As of the writing of this line, pg_stat_activity shows that the query has been running for over 30 minutes. It also shows a status of "active", and nothing under the wait_event column. A second entry in pg_stat_activity shows the foreign data wrapper connection, with a state of "idle in transaction" and again nothing under the wait_event column. Top shows the two processes using around 62% and 45% CPU, with the rest of machine being 96% idle, so they are doing *something*, but not even enough to max out one of the cores, nor is the machine as a whole maxed out. The PostgreSQL log shows plenty of activity (and none of the users of the database are complaining), so apparently the database as a whole is working fine - it's just this one query that's not completing. Further, iotop shows no significant disk access - read is a solid 0.00 B/s, and write is only around 80 K/s, so it's not like it's waiting on disk i/o.Both test server and production are running PostgreSQL 9.6, although my test server is at 9.6.1 while production is at 9.6.5 (huh, production got ahead of test. I'll have to fix that. Oh well), and I did a dump from production to test using -C just before starting work on the query, so the datasets are pretty closely matched. The production server is configured to take full(er) advantage of the hardware than my test database, so if anything I would expect it to be faster. Hardware differences obviously can have an effect on the query speed, but I wouldn't think a difference of 12 seconds to over half an hour (and counting). So what could explain this huge discrepancy?One difference I can think of is that my production database is replicating (streaming replication), but since this is just a select, I can't think why that would make a difference... any thoughts? ---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701;
Re: [GENERAL] sub-select with multiple records, columns
On Jun 19, 2017, at 12:29 PM, Thomas Kellerer <spam_ea...@gmx.net> wrote: > > Israel Brewster schrieb am 19.06.2017 um 22:17: >> SELECT >> ... >> (SELECT >> array_agg(to_json(row(notedate,username,note))) >> FROM sabrenotes >> INNER JOIN users ON author=users.id >> WHERE ticket=sabretickets.id ) notes >> FROM tickets >> WHERE ... >> The only problem with this query is that the notes aren't sorted. Of >> course, simply adding an ORDER BY clause to the sub-select doesn't >> work - it throws an error about needing to use notedate in a GROUP BY >> clause or aggregate function. Is there some way I can get sorting as >> well here? Of course, I could just run a second query to get the >> notes, and combine in code, but that's no fun... :-) > > You can supply an ORDER BY to an aggregate function: > > array_agg(to_json(row(notedate,username,note)) order by ...) Thanks (and to David G. Johnston). Didn't realize I could do that, but it makes perfect sense. > > I have to admit, that I fail to see the the advantage of an array of JSON > objects, rather then having a single json with the elements inside. > > json_object_agg() or json_agg() might be better suited for this. You may be right. Actually, my first thought (and the ideal here) was to simply have an array of rows or the like. That is, wind up with a data structure where I could in my code do something like record['notes']['username'], or perhaps record['notes'][1]. However, while I didn't get any errors when I tried that, the parsing of the results fell apart at some point in the chain - I wound up with strings containing a bunch of escaped and double-escaped quotes and the like. Adding the to_json simply converted the rows to json strings, which I can work with easily enough. Since I do still have to parse the json anyway, perhaps making the entire array be a single json object that I could parse once would be a better approach. > > Thomas > > > > > > > > -- > 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] sub-select with multiple records, columns
I have two tables, a ticket table and a notes table, set up where each ticket can have multiple notes. I'm trying to come up with a query that returns the ticket fields as well as a field that is an array type field with the values being json-encoded note records. I've come up with the following subselect query, which works:SELECT ... (SELECT array_agg(to_json(row(notedate,username,note))) FROM sabrenotes INNER JOIN users ON author=users.id WHERE ticket=sabretickets.id ) notesFROM ticketsWHERE ...The only problem with this query is that the notes aren't sorted. Of course, simply adding an ORDER BY clause to the sub-select doesn't work - it throws an error about needing to use notedate in a GROUP BY clause or aggregate function. Is there some way I can get sorting as well here? Of course, I could just run a second query to get the notes, and combine in code, but that's no fun... :-) ---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
Re: [GENERAL] Postgresql out-of-memory kill
On Feb 1, 2017, at 1:45 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > > Israel Brewster <isr...@ravnalaska.net> writes: >> So just a bit ago I ran into a bit of excitement when the kernel decided >> to kill one of my postmaster processes due to an out-of-memory issue, > > Fun :-( > >> So a single postmaster process was using over 72GB of ram. > > No, the kernel was blaming it for 72GB, which is an entirely different > statement. The Linux OOM killer makes some assumptions that are > ludicrously wrong for Postgres: not only does it blame a parent process > for the total memory consumption of all its children, but if the children > share a large shared memory segment, *it counts the shared memory segment > over again for each child*. At least this was true last I looked; > perhaps very recent kernels are a bit less insane about shared memory. > In any case, the core problem is blaming the parent process for the > sins of a child. > > Now the PG postmaster itself consumes very little memory, and this is > quite unlikely to suddenly go wrong because it doesn't do very much. > A child backend process might go crazy, but what you want to happen then > is for the OOM killer to kill the child process not the postmaster. > That will still result in a database crash/restart scenario, but as long > as the postmaster is alive everything should recover automatically. > > Your problem, then, is that the OOM killer is egregiously and with malice > aforethought killing the wrong process. > > The usual fix for this is to configure things so that the postmaster is > excluded from OOM kill but its children aren't. See > https://www.postgresql.org/docs/current/static/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT > (but be sure to consult the page for your PG version, as we've changed > the support mechanism for that in the past.) > > If you're using a vendor-supplied packaging of PG and it doesn't have some > easy way to turn on this behavior, complain to the vendor ... > > regards, tom lane > Thanks for the explanation. This is a CentOS 6 box, kernel 2.6.32-642.11.1.el6.x86_64, running the PostgreSQL supplied Postgres 9.6.1, so hopefully the information on that page applies. I'll mess around with modifying the init.d script to exclude the postmaster process. Thanks again! --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql out-of-memory kill
So just a bit ago I ran into a bit of excitement when the kernel decided to kill one of my postmaster processes due to an out-of-memory issue, which would have been fine, except that the problem was then compounded by Pacemaker attempting to restart postgresql, but only managing to get as far as stopping the primary and failing to promote the secondary, leaving me with nothing. Not fun for a mission-critical database, but luckily I was notified of the issue nearly immediately, and was able to get everything back up and running quickly (after a few moments of panic).In any case the root problem here was the out-of-memory issue. The logs show this:Feb 1 11:58:34 fai-dbs1 kernel: Out of memory: Kill process 26316 (postmaster) score 837 or sacrifice childFeb 1 11:58:34 fai-dbs1 kernel: Killed process 26316, UID 26, (postmaster) total-vm:72328760kB, anon-rss:55470760kB, file-rss:4753180kBSo a single postmaster process was using over 72GB of ram. Obviously I have something in my config tuned too high. The question is "what"? The machine has 64 GB of RAM, and I want postgresql to be able to use as much of that as it wants (since the machine is dedicated to postgresql), but obviously it needs to be more limited than what I have.From my config:max_connections = 300shared_buffers = 14GB # Roughly 1/4 of 64GB, as per https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serverwork_mem = 75MBNot sure what else in there would play a role. I was seeing some of this in the postgresql logs shortly before the issue arose:2017-02-01 11:58:02.074 AKST > LOG: checkpoints are occurring too frequently (12 seconds apart)2017-02-01 11:58:02.074 AKST > HINT: Consider increasing the configuration parameter "max_wal_size".I was thinking perhaps the work_mem was the issue, but if my understanding and calculations are correct, that would mean I had more than 800 simultaneous sorts/queries going on at the time, which seems quite improbable, given the relatively light load on this database. At the moment, for example, a query on pg_stat_activity reveals only 11 entries.What am I missing here? ---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
Re: [GENERAL] pg_dump Conflict with recovery
On Jan 23, 2017, at 10:06 AM, Jerry Sievers <gsiever...@comcast.net> wrote: > > Israel Brewster <isr...@ravnalaska.net> writes: > >> I have a backup strategy that in part consists of doing pg_dump s on my >> various databases. In order to hopefully reduce/prevent operational >> slow-down as a result of the >> backup, I do the dumps from my secondary server, configured as a hot standby >> with streaming replication. >> >> In general this works fine, but one of my databases has now grown to the >> point that often as not I get the following when trying to dump the database: >> >> ERROR: canceling statement due to conflict with recovery >> DETAIL: User was holding a relation lock for too long. >> >> As I understand it, this is due to the pg_dump taking longer than the >> max_standby_streaming_delay of 180s, and as such could be easily fixed by >> upping that value in the >> config. But is that the "right" fix? Or is there a "better" way? > > "Best" way depends on your needs... > > You can pause your standby and/or configure settings like the one you > mentioned to tolerate the dump conflicting with replication by > allowing the standby to lag rather than issuing an cancel. > > select pg_xlog_replay_pause(); > -- dump here > select pg_xlog_replay_resume(); > > The above will of course guarantee that your slave lags vs fiddling with > the max delay settings and being then subject to possibly moving target > in terms of dump duration and upstream system behavior. Sounds reasonable. Allows for however long the dump process needs without allowing for runaway queries in the general case, as long as I make sure to implement things in a way that makes sure the pg_xlog_replay_resume() is always called, no matter what happens (not that I've had any failures, I just tend to be a bit paranoid about this system). Thanks! --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- > > > >> --- >> Israel Brewster >> Systems Analyst II >> Ravn Alaska >> 5245 Airport Industrial Rd >> Fairbanks, AK 99709 >> (907) 450-7293 >> --- >> >> >> > > -- > Jerry Sievers > Postgres DBA/Development Consulting > e: postgres.consult...@comcast.net > p: 312.241.7800
[GENERAL] pg_dump Conflict with recovery
I have a backup strategy that in part consists of doing pg_dump s on my various databases. In order to hopefully reduce/prevent operational slow-down as a result of the backup, I do the dumps from my secondary server, configured as a hot standby with streaming replication.In general this works fine, but one of my databases has now grown to the point that often as not I get the following when trying to dump the database:ERROR: canceling statement due to conflict with recoveryDETAIL: User was holding a relation lock for too long.As I understand it, this is due to the pg_dump taking longer than the max_standby_streaming_delay of 180s, and as such could be easily fixed by upping that value in the config. But is that the "right" fix? Or is there a "better" way? ---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
Re: [GENERAL] Improve PostGIS performance with 62 million rows?
> On Jan 9, 2017, at 1:54 PM, Kevin Grittner <kgri...@gmail.com> wrote: > > On Mon, Jan 9, 2017 at 11:49 AM, Israel Brewster <isr...@ravnalaska.net> > wrote: > >> [load of new data] > >> Limit (cost=354643835.82..354643835.83 rows=1 width=9) (actual >> time=225998.319..225998.320 rows=1 loops=1) > >> [...] I ran the query again [...] > >> Limit (cost=354643835.82..354643835.83 rows=1 width=9) (actual >> time=9636.165..9636.166 rows=1 loops=1) > >> So from four minutes on the first run to around 9 1/2 seconds on the second. >> Presumably this difference is due to caching? > > It is likely to be, at least in part. Did you run VACUUM on the > data before the first run? If not, hint bits may be another part > of it. The first access to each page after the bulk load would > require some extra work for visibility checking and would cause a > page rewrite for the hint bits. That could be - I had planned to run a VACUUM ANALYZE after creating the indexes, but forgot. By the time I got around to running the second query, autovacuum should have kicked in and done it for me. > > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company -- 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] Improve PostGIS performance with 62 million rows?
361..1.318 rows=233 loops=1) Filter: (short_line IS NOT NULL) Rows Removed by Filter: 1 -> Index Scan using location_gist_idx on data (cost=0.68..356423.07 rows=5 width=41) (actual time=0.372..41.126 rows=685 loops=233) Index Cond: (location && _st_expand(segments.short_line, '100'::double precision)) Filter: ((segments.short_line && _st_expand(location, '100'::double precision)) AND _st_dwithin(location, segments.short_line, '100'::double precision, true)) Rows Removed by Filter: 8011 Planning time: 0.941 ms Execution time: 9636.285 ms(20 rows)So from four minutes on the first run to around 9 1/2 seconds on the second. Presumably this difference is due to caching? I would have expected any caches to have expired by the time I made the second run, but the data *is* static, so I guess not. Otherwise, I don't know how to explain the improvement on the second run - the query plans appear identical (at least to me). *IS* there something else (for example, auto vacuum running over the weekend) that could explain the performance difference?Assuming this performance difference *is* due to caching, that brings up a couple of questions for me:1) Is there any way to "force" PostgreSQL to cache the data? Keep in mind that the database is close to a couple of hundred Gigs of data, so there is no way it can all fit in RAM.2) In lieu of forcing a cache (which is probably not going to work well, even if possible), what could I do to help ensure that performance is closer to the 9 second mark than the 4 minute mark in general? For example, would it be likely to make a significant difference if I was to add a couple of larger SSD's to hold this data and put them in a stripe RAID (rather than the mirrored 7200 RPM platter drives it is on now)? Since the data is static, loosing the data due to drive failure is of little concern to me. Or would adding more RAM (and tweaking PostgreSQL settings) to be able to increase the cache size help more, even though there would still not be enough to cache everything?In the end, the low resolution data is probably good enough, and I may be able to come up with some sort of method to use them both - i.e. return a result quickly from the low resolution dataset, while simultaneously firing off the same request to the high resolution dataset, and returning that result when ready, or only using the high-resolution data set when explicitly requested. So having to wait four minutes on occasion for a result from the high-resolution set may not be an issue. That said, it would be nice to know all the options I can present to my boss :-)
---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD
VERSION:3.0
N:Brewster;Israel;;;
FN:Israel Brewster
ORG:Frontier Flying Service;MIS
TITLE:PC Support Tech II
EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com
TEL;type=WORK;type=pref:907-450-7293
item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701;
item1.X-ABADR:us
CATEGORIES:General
X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson
END:VCARD
On Jan 5, 2017, at 1:55 PM, Israel Brewster <isr...@ravnalaska.net> wrote:On Jan 5, 2017, at 1:38 PM, Rémi Cura <remi.c...@gmail.com> wrote:Hey,1 sec seems really good in this case,and I'm assuming you tuned postgres so the main index fits into ram (work_mem and all other stuff).You could avoid a CTE by mixing both cte.WITH pts AS ( SELECT (pt).geom, (pt).path[1] as vert FROM ST_DumpPoints( ST_Segmentize( ST_GeographyFromText('SRID=4326;LINESTRING(-150.008056 61.179167,-156.77 71.285833)'), 600 )::geometry ) as pt) SELECT elevation FROM data INNER JOIN (SELECT ST_MakeLine(ARRAY[a.geom, b.geom]) as short_line FROM pts a INNER JOIN pts b ON a.vert=b.vert-1 AND b.vert>1) segmentsON ST_DWithin(location, segments.short_line, 600)ORDER BY elevation DESC limit 1;Then you could remove the useless and (potentially explosive if you have large number of dump points) inner join on points : "FROM pts a INNER JOIN pts b " You could simply use a window function to generate the segments, like in here.The idea is to dump points, order them by path, and then link each point with the previous one (function lag).Assuming you don't want to use the available function,this would be something like : WITH segments AS (SELECT ST_MakeLine( lag((pt).geom , 1, NULL) OVER (ORDER BY (pt).path) ,(pt).geom) AS short_line FROM ST_DumpPoints( ST_Segmentize( ST_GeographyFromText('SRID=4326;LINESTRING(-150.008056 61.179167,-156.77 71.285833)'), 600 )::geometry ) as pt) SELECT elevation FROM data ,segmen
Re: [GENERAL] Improve PostGIS performance with 62 million rows?
On Jan 5, 2017, at 1:38 PM, Rémi Cura <remi.c...@gmail.com> wrote:
>
> Hey,
> 1 sec seems really good in this case,
> and I'm assuming you tuned postgres so the main index fits into ram (work_mem
> and all other stuff).
>
> You could avoid a CTE by mixing both cte.
>
> WITH pts AS (
> SELECT (pt).geom, (pt).path[1] as vert
> FROM
> ST_DumpPoints(
> ST_Segmentize(
> ST_GeographyFromText('SRID=4326;LINESTRING(-150.008056
> 61.179167,-156.77 71.285833)'),
> 600
> )::geometry
> ) as pt
> )
> SELECT elevation
> FROM data
> INNER JOIN (SELECT
> ST_MakeLine(ARRAY[a.geom, b.geom]) as short_line
> FROM pts a
> INNER JOIN pts b
> ON a.vert=b.vert-1 AND b.vert>1) segments
> ON ST_DWithin(location, segments.short_line, 600)
> ORDER BY elevation DESC limit 1;
>
>
> Then you could remove the useless and (potentially explosive if you have
> large number of dump points) inner join on points :
> "FROM pts a
> INNER JOIN pts b "
>
> You could simply use a window function to generate the segments, like in here
> <https://github.com/Remi-C/_utilities/blob/master/postgis/rc_DumpSegments.sql#L51>.
> The idea is to dump points, order them by path, and then link each point with
> the previous one (function lag).
> Assuming you don't want to use the available function,
> this would be something like :
>
>
>
> WITH segments AS (
> SELECT ST_MakeLine( lag((pt).geom , 1, NULL) OVER (ORDER BY (pt).path)
> ,(pt).geom) AS short_line
> FROM ST_DumpPoints(
> ST_Segmentize(
> ST_GeographyFromText('SRID=4326;LINESTRING(-150.008056
> 61.179167,-156.77 71.285833)'),
> 600
> )::geometry
> ) as pt
> )
> SELECT elevation
> FROM data ,segments
> WHERE segments.short_line IS NOT NULL --the first segment is null by design
> (lag function)
> AND ST_DWithin(location, segments.short_line, 600) = TRUE
> ORDER BY elevation DESC
> limit 1;
>
>
> I don't know if you can further improve this query after that,
> but I'll guess it would reduce your time and be more secure regarding scaling.
>
>
> if you want to further improve your result,
> you'll have to reduce the number of row in your index,
> that is partition your table into several tables !
>
> This is not easy to do with current postgres partitionning methods as far as
> I know
> (partitionning is easy, automatic efficient query is hard).
>
> Another way would be to reduce you requirement, and consider that in some
> case you may want less details in the altimetry, which would allow you to use
> a Level Of Detail approach.
>
> Congrats for the well explained query/problem anyway !
> Cheers,
> Rémi-C
Ooooh, nice use of a window function - that change right there cut the
execution time in half! I was able to shave off a few hundreds of a second more
but tweaking the ST_Segmentize length parameter up to 5,000 (still have to play
with that number some), so execution time is now down to the sub-300ms range.
If I reduce the radius I am looking around the line, I can additionally improve
the time to around 200 ms, but I'm not sure that will be an option. Regardless,
300ms is rather impressive, I think. Thanks!
>
> 2017-01-05 23:09 GMT+01:00 Paul Ramsey <pram...@cleverelephant.ca
> <mailto:pram...@cleverelephant.ca>>:
> Varying the segment length upwards might have a salutary effect for a while,
> as the efficiency improvement of fewer inner loops battles with the
> inefficiency of having more points selected by the index filter. Worth an
> experiment.
>
> P
>
> On Thu, Jan 5, 2017 at 1:00 PM, Israel Brewster <isr...@ravnalaska.net
> <mailto:isr...@ravnalaska.net>> wrote:
>
>> On Jan 5, 2017, at 10:38 AM, Paul Ramsey <pram...@cleverelephant.ca
>> <mailto:pram...@cleverelephant.ca>> wrote:
>>
>> Yes, you did. You want a query that spits out a tupleset of goemetries (one
>> each for each wee segment), and then you can join that set to your main
>> table using st_dwithin() as the join clause.
>> So start by ditching the main table and just work on a query that generates
>> a pile of wee segments.
>
> Ahhh, I see you've done this sort of thing before
> (http://blog.cleverelephant.ca/2015/02/breaking-linestring-into-segments.html
> <http://blog.cleverelephant.ca/2015/02/breaking-linestring-into-segments.html>)
> :-)
>
> So following that advice I came up with the following query:
>
> WITH dump AS (SELECT
> ST_DumpPoints(
> ST_Segmenti
Re: [GENERAL] Improve PostGIS performance with 62 million rows?
Ah, yes indeed. Upping the segment length to 1,000 brings the execution time down to 642 ms, and further upping it to 10,000 brings the execution time down again to 442.104 ms. I'll have to play around with it and see where the minimum is. Would that be likely to vary depending on initial path length?
---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD
VERSION:3.0
N:Brewster;Israel;;;
FN:Israel Brewster
ORG:Frontier Flying Service;MIS
TITLE:PC Support Tech II
EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com
TEL;type=WORK;type=pref:907-450-7293
item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701;
item1.X-ABADR:us
CATEGORIES:General
X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson
END:VCARD
On Jan 5, 2017, at 1:09 PM, Paul Ramsey <pram...@cleverelephant.ca> wrote:Varying the segment length upwards might have a salutary effect for a while, as the efficiency improvement of fewer inner loops battles with the inefficiency of having more points selected by the index filter. Worth an experiment.POn Thu, Jan 5, 2017 at 1:00 PM, Israel Brewster <isr...@ravnalaska.net> wrote:On Jan 5, 2017, at 10:38 AM, Paul Ramsey <pram...@cleverelephant.ca> wrote:Yes, you did. You want a query that spits out a tupleset of goemetries (one each for each wee segment), and then you can join that set to your main table using st_dwithin() as the join clause.So start by ditching the main table and just work on a query that generates a pile of wee segments.Ahhh, I see you've done this sort of thing before (http://blog.cleverelephant.ca/2015/02/breaking-linestring-into-segments.html) :-)So following that advice I came up with the following query:WITH dump AS (SELECT ST_DumpPoints( ST_Segmentize( ST_GeographyFromText('SRID=4326;LINESTRING(-150.008056 61.179167,-156.77 71.285833)'), 600 )::geometry ) as pt),pts AS ( SELECT (pt).geom, (pt).path[1] as vert FROM dump)SELECT elevation FROM data INNER JOIN (SELECT ST_MakeLine(ARRAY[a.geom, b.geom]) as short_line FROM pts a INNER JOIN pts b ON a.vert=b.vert-1 AND b.vert>1) segmentsON ST_DWithin(location, segments.short_line, 600)ORDER BY elevation DESC limit 1;Which yields the following EXPLAIN ANALYZE (https://explain.depesz.com/s/RsTD): QUERY PLAN Limit (cost=11611706.90..11611706.91 rows=1 width=4) (actual time=1171.814..1171.814 rows=1 loops=1) CTE dump -> Result (cost=0.00..5.25 rows=1000 width=32) (actual time=0.024..1.989 rows=1939 loops=1) CTE pts -> CTE Scan on dump (cost=0.00..20.00 rows=1000 width=36) (actual time=0.032..4.071 rows=1939 loops=1) -> Sort (cost=11611681.65..11611768.65 rows=34800 width=4) (actual time=1171.813..1171.813 rows=1 loops=1) Sort Key: data.elevation DESC Sort Method: top-N heapsort Memory: 25kB -> Nested Loop (cost=0.55..11611507.65 rows=34800 width=4) (actual time=0.590..1167.615 rows=28408 loops=1) -> Nested Loop (cost=0.00..8357.50 rows=1665 width=64) (actual time=0.046..663.475 rows=1938 loops=1) Join Filter: (a.vert = (b.vert - 1)) Rows Removed by Join Filter: 3755844 -> CTE Scan on pts b (cost=0.00..22.50 rows=333 width=36) (actual time=0.042..0.433 rows=1938 loops=1) Filter: (vert > 1) Rows Removed by Filter: 1 -> CTE Scan on pts a (cost=0.00..20.00 rows=1000 width=36) (actual time=0.000..0.149 rows=1939 loops=1938) -> Index Scan using location_gix on data (cost=0.55..6968.85 rows=1 width=36) (actual time=0.085..0.256 rows=15 loops=1938) Index Cond: (location && _st_expand((st_makeline(ARRAY[a.geom, b.geom]))::geography, '600'::double precision)) Filter: (((st_makeline(ARRAY[a.geom, b.geom]))::geography && _st_expand(location, '600'::double precision)) AND _st_dwithin(location, (st_makeline(ARRAY[a.geom, b.geom]))::geography, '600'::double precision, true)) Rows Removed by Filter: 7 Planning time: 4.318 ms Execution time: 1171.994 ms(22 rows)So not bad. Went from 20+ seconds to a little over 1 second. Still noticeable for a end user, but defiantly usable - and like mentioned, that's a worst-case scenario
Re: [GENERAL] Improve PostGIS performance with 62 million rows?
> On Jan 5, 2017, at 10:38 AM, Paul Ramsey <pram...@cleverelephant.ca> wrote:
>
> Yes, you did. You want a query that spits out a tupleset of goemetries (one
> each for each wee segment), and then you can join that set to your main table
> using st_dwithin() as the join clause.
> So start by ditching the main table and just work on a query that generates a
> pile of wee segments.
Ahhh, I see you've done this sort of thing before
(http://blog.cleverelephant.ca/2015/02/breaking-linestring-into-segments.html
<http://blog.cleverelephant.ca/2015/02/breaking-linestring-into-segments.html>)
:-)
So following that advice I came up with the following query:
WITH dump AS (SELECT
ST_DumpPoints(
ST_Segmentize(
ST_GeographyFromText('SRID=4326;LINESTRING(-150.008056
61.179167,-156.77 71.285833)'),
600
)::geometry
) as pt
),
pts AS (
SELECT (pt).geom, (pt).path[1] as vert FROM dump
)
SELECT elevation
FROM data
INNER JOIN (SELECT
ST_MakeLine(ARRAY[a.geom, b.geom]) as short_line
FROM pts a
INNER JOIN pts b
ON a.vert=b.vert-1 AND b.vert>1) segments
ON ST_DWithin(location, segments.short_line, 600)
ORDER BY elevation DESC limit 1;
Which yields the following EXPLAIN ANALYZE (https://explain.depesz.com/s/RsTD
<https://explain.depesz.com/s/ukwc>):
QUERY PLAN
Limit (cost=11611706.90..11611706.91 rows=1 width=4) (actual
time=1171.814..1171.814 rows=1 loops=1)
CTE dump
-> Result (cost=0.00..5.25 rows=1000 width=32) (actual time=0.024..1.989
rows=1939 loops=1)
CTE pts
-> CTE Scan on dump (cost=0.00..20.00 rows=1000 width=36) (actual
time=0.032..4.071 rows=1939 loops=1)
-> Sort (cost=11611681.65..11611768.65 rows=34800 width=4) (actual
time=1171.813..1171.813 rows=1 loops=1)
Sort Key: data.elevation DESC
Sort Method: top-N heapsort Memory: 25kB
-> Nested Loop (cost=0.55..11611507.65 rows=34800 width=4) (actual
time=0.590..1167.615 rows=28408 loops=1)
-> Nested Loop (cost=0.00..8357.50 rows=1665 width=64) (actual
time=0.046..663.475 rows=1938 loops=1)
Join Filter: (a.vert = (b.vert - 1))
Rows Removed by Join Filter: 3755844
-> CTE Scan on pts b (cost=0.00..22.50 rows=333
width=36) (actual time=0.042..0.433 rows=1938 loops=1)
Filter: (vert > 1)
Rows Removed by Filter: 1
-> CTE Scan on pts a (cost=0.00..20.00 rows=1000
width=36) (actual time=0.000..0.149 rows=1939 loops=1938)
-> Index Scan using location_gix on data (cost=0.55..6968.85
rows=1 width=36) (actual time=0.085..0.256 rows=15 loops=1938)
Index Cond: (location &&
_st_expand((st_makeline(ARRAY[a.geom, b.geom]))::geography, '600'::double
precision))
Filter: (((st_makeline(ARRAY[a.geom, b.geom]))::geography
&& _st_expand(location, '600'::double precision)) AND _st_dwithin(location,
(st_makeline(ARRAY[a.geom, b.geom]))::geography, '600'::double precision, true))
Rows Removed by Filter: 7
Planning time: 4.318 ms
Execution time: 1171.994 ms
(22 rows)
So not bad. Went from 20+ seconds to a little over 1 second. Still noticeable
for a end user, but defiantly usable - and like mentioned, that's a worst-case
scenario query. Thanks!
Of course, if you have any suggestions for further improvement, I'm all ears :-)
---
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
-------
>
> On Thu, Jan 5, 2017 at 11:36 AM, Israel Brewster <isr...@ravnalaska.net
> <mailto:isr...@ravnalaska.net>> wrote:
> On Jan 5, 2017, at 8:50 AM, Paul Ramsey <pram...@cleverelephant.ca
> <mailto:pram...@cleverelephant.ca>> wrote:
>>
>> The index filters using bounding boxes. A long, diagonal route will have a
>> large bounding box, relative to the area you actually care about (within a
>> narrow strip of the route). Use ST_Segmentize() to add points to your route,
>> ST_DumpPoints() to dump those out as point and ST_MakeLine to generate new
>> lines from those points, each line very short. The maximum index
>> effectiveness will come when your line len
Re: [GENERAL] Improve PostGIS performance with 62 million rows?
On Jan 5, 2017, at 8:50 AM, Paul Ramsey <pram...@cleverelephant.ca> wrote:
>
> The index filters using bounding boxes. A long, diagonal route will have a
> large bounding box, relative to the area you actually care about (within a
> narrow strip of the route). Use ST_Segmentize() to add points to your route,
> ST_DumpPoints() to dump those out as point and ST_MakeLine to generate new
> lines from those points, each line very short. The maximum index
> effectiveness will come when your line length is close to your buffer width.
>
> P
Ok, I think I understand the concept. So attempting to follow your advice, I
modified the query to be:
SELECT elevation
FROM data
WHERE
ST_DWithin(
location,
(SELECT ST_MakeLine(geom)::geography as split_line
FROM (SELECT
(ST_DumpPoints(
ST_Segmentize(
ST_GeographyFromText('SRID=4326;LINESTRING(-150.008056
61.179167,-156.77 71.285833)'),
600
)::geometry
)).geom
) s1),
600
)
ORDER BY elevation DESC limit 1;
It took some fiddling to find a syntax that Postgresql would accept, but
eventually that's what I came up with. Unfortunately, far from improving
performance, it killed it - in running the query, it went from 22 seconds to
several minutes (EXPLAIn ANALYZE has yet to return a result). Looking at the
query execution plan shows, at least partially, why:
QUERY PLAN
--
Limit (cost=17119748.98..17119748.98 rows=1 width=4)
InitPlan 1 (returns $0)
-> Aggregate (cost=17.76..17.77 rows=1 width=32)
-> Result (cost=0.00..5.25 rows=1000 width=32)
-> Sort (cost=17119731.21..17171983.43 rows=20900890 width=4)
Sort Key: data.elevation DESC
-> Seq Scan on data (cost=0.00..17015226.76 rows=20900890 width=4)
Filter: st_dwithin(location, $0, '600'::double precision)
(8 rows)
So apparently it is now doing a sequential scan on data rather than using the
index. And, of course, sorting 20 million rows is not trivial either. Did I do
something wrong with forming the query?
-------
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
---
>
> On Thu, Jan 5, 2017 at 9:45 AM, Israel Brewster <isr...@ravnalaska.net
> <mailto:isr...@ravnalaska.net>> wrote:
> I have a database (PostgreSQL 9.6.1) containing 62,702,675 rows of latitude
> (numeric), longitude(numeric), elevation(integer) data, along with a PostGIS
> (2.3.0) geometry column (location), running on a CentOS 6.8 box with 64GB RAM
> and a RAID10 SSD data drive. I'm trying to get the maximum elevation along a
> path, for which purpose I've come up with the following query (for one
> particular path example):
>
> SELECT elevation FROM data
>
>
>
>
> WHERE ST_DWithin(location,
> ST_GeographyFromText('SRID=4326;LINESTRING(-150.008056 61.179167,-156.77
> 71.285833)'), 600)
>
>
> ORDER BY elevation
> LIMIT 1;
>
> The EXPLAIN ANALYZE output of this particular query
> (https://explain.depesz.com/s/heZ <https://explain.depesz.com/s/heZ>) shows:
>
>
>
> QUERY PLAN
>
>
> --
> Limit (cost=4.83..4.83 rows=1 width=4) (actual time=22653.840..22653.842
> rows=1 loops=1)
>-> Sort (co
[GENERAL] Improve PostGIS performance with 62 million rows?
I have a database (PostgreSQL 9.6.1) containing 62,702,675 rows of latitude (numeric), longitude(numeric), elevation(integer) data, along with a PostGIS (2.3.0) geometry column (location), running on a CentOS 6.8 box with 64GB RAM and a RAID10 SSD data drive. I'm trying to get the maximum elevation along a path, for which purpose I've come up with the following query (for one particular path example):SELECT elevation FROM data WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326;LINESTRING(-150.008056 61.179167,-156.77 71.285833)'), 600) ORDER BY elevation LIMIT 1;The EXPLAIN ANALYZE output of this particular query (https://explain.depesz.com/s/heZ) shows: QUERY PLAN -- Limit (cost=4.83..4.83 rows=1 width=4) (actual time=22653.840..22653.842 rows=1 loops=1) -> Sort (cost=4.83..4.83 rows=1 width=4) (actual time=22653.837..22653.837 rows=1 loops=1) Sort Key: elevation DESC Sort Method: top-N heapsort Memory: 25kB -> Index Scan using location_gix on data (cost=0.42..4.82 rows=1 width=4) (actual time=15.786..22652.041 rows=11081 loops=1) Index Cond: (location && '010220E61002002C11A8FE41C062C0DFC2BAF1EE964E40713D0AD7A39863C086C77E164BD25140'::geography) Filter: (('010220E61002002C11A8FE41C062C0DFC2BAF1EE964E40713D0AD7A39863C086C77E164BD25140'::geography && _st_expand(location, '600'::double precision)) AND _st_dwithin(location, '010220E61002002C11A8FE41C062C0DFC2BAF1EE964E40713D0AD7A39863C086C77E164BD25140'::geography, '600'::double precision, true)) Rows Removed by Filter: 4934534 Planning time: 0.741 ms Execution time: 22653.906 ms(10 rows)So it is using the index properly, but still takes a good 22 seconds to run, most of which appears to be in the Index Scan.Is there any way to improve this, or is this going to be about as good as it gets with the number of rows being dealt with? I was planning to use this for a real-time display - punch in a couple of points, get some information about the route between, including maximum elevation - but with it taking 22 seconds for the longer routes at least, that doesn't make for the best user experience.It's perhaps worth noting that the example above is most likely a worst case scenario. I would expect the vast majority of routes to be significantly shorter, and I want to say the shorter routes query much faster [testing needed]. That said, the faster the better, even for short routes :-)
---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD
VERSION:3.0
N:Brewster;Israel;;;
FN:Israel Brewster
ORG:Frontier Flying Service;MIS
TITLE:PC Support Tech II
EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com
TEL;type=WORK;type=pref:907-450-7293
item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701;
item1.X-ABADR:us
CATEGORIES:General
X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson
END:VCARD
Re: [GENERAL] Index impact on update?
> On Jan 4, 2017, at 8:08 AM, Paul Ramsey <pram...@cleverelephant.ca> wrote:
>
> You'd be better off forcing the table to write in bulk with something like
>
> CREATE TABLE mynewtable AS
> SELECT *, geography(ST_SetSRID(ST_MakePoint(lng, lat), 4326)) AS geog
> FROM myoldtable;
>
> Then index the new table, rename, etc. Bulk update will, in addition to being
> slow, use 2x the amount of space on disk, as all the old tuples are left
> behind from the update until you cluster or vacuum full the table.
>
> P
>
Well, that was definitely way faster. Created the table with the additional
column in about 10 minutes rather than 19+ hours. Now to see how long it takes
to generate the indexes :-)
Thanks again!
---
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
---
>
>
> On Wed, Jan 4, 2017 at 8:59 AM, Israel Brewster <isr...@ravnalaska.net
> <mailto:isr...@ravnalaska.net>> wrote:
> Short version:
> Do indexes impact the speed of an UPDATE, even when the indexed columns
> aren't changing?
>
> Details:
> I have a table containing geographical data (Latitude, longitude, and
> elevation) with 406,833,705 records. The Latitude and Longitude columns are
> indexed. In order to better utilize the data, I've been looking into PostGIS,
> and decided I wanted to add a "Location" column with PostGIS type
> "GEOGRAPHY(point)". I then tried to populate it from the existing
> latitude/longitude data using the following query:
>
> UPDATE data SET
> location=ST_GeographyFromText('SRID=4326;POINT('||lng::text||'
> '||lat::text||')');
>
> I expected this update to take quite a while, since it has 406 million rows
> to update, but at this point it's been over 19 hours since I started the
> query, and it still hasn't completed.
>
> I'm wondering if the presence of the indexes could be slowing things down
> even though the indexed columns aren't being updated? Would I be better off
> canceling the update query, dropping the indexes, and trying again? Or is
> more likely that the update query is "almost" done, and it would be better to
> just let it run it's course? Or is there an even better option, such as
> perhaps exporting the data, adding the additional column in a text editor,
> and re-importing the data with a COPY command?
>
> Thanks for any feedback/advice you can offer!
> ---
> Israel Brewster
> Systems Analyst II
> Ravn Alaska
> 5245 Airport Industrial Rd
> Fairbanks, AK 99709
> (907) 450-7293 <tel:(907)%20450-7293>
> ---
>
>
>
>
>
>
Re: [GENERAL] Index impact on update?
On Jan 4, 2017, at 8:08 AM, Paul Ramsey <pram...@cleverelephant.ca> wrote:
>
> You'd be better off forcing the table to write in bulk with something like
>
> CREATE TABLE mynewtable AS
> SELECT *, geography(ST_SetSRID(ST_MakePoint(lng, lat), 4326)) AS geog
> FROM myoldtable;
>
> Then index the new table, rename, etc. Bulk update will, in addition to being
> slow, use 2x the amount of space on disk, as all the old tuples are left
> behind from the update until you cluster or vacuum full the table.
>
> P
Thanks for the suggestion, info, and MUCH nicer looking syntax (perhaps more
efficient as well?) for populating the column. I'll give it a shot, and see how
it goes!
-------
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
---
>
>
>
> On Wed, Jan 4, 2017 at 8:59 AM, Israel Brewster <isr...@ravnalaska.net
> <mailto:isr...@ravnalaska.net>> wrote:
> Short version:
> Do indexes impact the speed of an UPDATE, even when the indexed columns
> aren't changing?
>
> Details:
> I have a table containing geographical data (Latitude, longitude, and
> elevation) with 406,833,705 records. The Latitude and Longitude columns are
> indexed. In order to better utilize the data, I've been looking into PostGIS,
> and decided I wanted to add a "Location" column with PostGIS type
> "GEOGRAPHY(point)". I then tried to populate it from the existing
> latitude/longitude data using the following query:
>
> UPDATE data SET
> location=ST_GeographyFromText('SRID=4326;POINT('||lng::text||'
> '||lat::text||')');
>
> I expected this update to take quite a while, since it has 406 million rows
> to update, but at this point it's been over 19 hours since I started the
> query, and it still hasn't completed.
>
> I'm wondering if the presence of the indexes could be slowing things down
> even though the indexed columns aren't being updated? Would I be better off
> canceling the update query, dropping the indexes, and trying again? Or is
> more likely that the update query is "almost" done, and it would be better to
> just let it run it's course? Or is there an even better option, such as
> perhaps exporting the data, adding the additional column in a text editor,
> and re-importing the data with a COPY command?
>
> Thanks for any feedback/advice you can offer!
> ---
> Israel Brewster
> Systems Analyst II
> Ravn Alaska
> 5245 Airport Industrial Rd
> Fairbanks, AK 99709
> (907) 450-7293 <tel:(907)%20450-7293>
> ---
>
>
>
>
>
>
[GENERAL] Index impact on update?
Short version:Do indexes impact the speed of an UPDATE, even when the indexed columns aren't changing?Details:I have a table containing geographical data (Latitude, longitude, and elevation) with 406,833,705 records. The Latitude and Longitude columns are indexed. In order to better utilize the data, I've been looking into PostGIS, and decided I wanted to add a "Location" column with PostGIS type "GEOGRAPHY(point)". I then tried to populate it from the existing latitude/longitude data using the following query:UPDATE data SET location=ST_GeographyFromText('SRID=4326;POINT('||lng::text||' '||lat::text||')');I expected this update to take quite a while, since it has 406 million rows to update, but at this point it's been over 19 hours since I started the query, and it still hasn't completed. I'm wondering if the presence of the indexes could be slowing things down even though the indexed columns aren't being updated? Would I be better off canceling the update query, dropping the indexes, and trying again? Or is more likely that the update query is "almost" done, and it would be better to just let it run it's course? Or is there an even better option, such as perhaps exporting the data, adding the additional column in a text editor, and re-importing the data with a COPY command?Thanks for any feedback/advice you can offer!
---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD
VERSION:3.0
N:Brewster;Israel;;;
FN:Israel Brewster
ORG:Frontier Flying Service;MIS
TITLE:PC Support Tech II
EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com
TEL;type=WORK;type=pref:907-450-7293
item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701;
item1.X-ABADR:us
CATEGORIES:General
X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson
END:VCARD
[GENERAL] WAL File archive time
Simple question: are WAL files archived when full, or when recycled? That is, are the WAL archive files "up-to-date" other than the current WAL file, or will the archives always be wal_keep_segments behind?---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
Re: [GENERAL] Backup "Best Practices"
On Nov 29, 2016, at 8:12 AM, Israel Brewster <isr...@ravnalaska.net> wrote: > > On Nov 28, 2016, at 10:04 PM, Jeff Janes <jeff.ja...@gmail.com > <mailto:jeff.ja...@gmail.com>> wrote: >> >> On Mon, Nov 28, 2016 at 2:50 PM, Israel Brewster <isr...@ravnalaska.net >> <mailto:isr...@ravnalaska.net>> wrote: >> >>> - What is the "best" (or just a good) method of keeping the WAL archives >>> under control? Obviously when I do a new basebackup I can "cleanup" any old >>> files that said backup doesn't need, >>> >>> You have said you might be interested in doing PITR. So you want to delay >>> the cleanup so as to not compromise that ability. You need to develop a >>> policy on how far back you want to be able to do a PITR. >>> >>> >>> but how do I know what those are? >>> >>> pg_archivecleanup -n /mnt/server/archiverdir >>> 00010010.0020.backup >> >> Ok, but where does that "00010010.0020.backup" come >> from? I mean, I can tell it's a WAL segment file name (plus a backup label), >> but I don't have anything like that in my WAL archives, even though I've run >> pg_basebackup a couple of times. >> >> I get one file like that for every pg_basebackup I run. Could your >> archive_command be doing something to specifically short-circuit the writing >> of those files? Like testing the length of %p or %f? > > My archive command is simply a copy - straight out of the examples given in > the documentation, actually. Only test I do is to make sure the file doesn't > exist before running the copy > >> Do I have to call something to create that file? Some flag to pg_basebackup? >> At the moment I am running pg_basebackup such that it generates gziped tar >> files, if that makes a difference. >> >> >> That is how I run it as well. I don't think there is a flag to >> pg_basebackup which even allows you to bypass the creation of those files. >> You are looking in the WAL archive itself, correct? Not somewhere in a >> listing of the base.tar.gz file? > > I am looking at the WAL archive itself. One thing that just occurred to me: > in my testing, I've been running the base backup from the secondary slave > server. Perhaps that makes a difference? I know the slave itself doesn't > archive WAL files, but I would have expected the master to get the message a > backup was being run and do any needed archiving itself. So to test, I ran a base backup from my primary server rather than the secondary - and the .backup file WAS indeed created in the WAL archive directory. So I guess that means I have to run base backups from the primary server. Are there any performance implications to doing this that I should be aware of? Something that would imply I need to make sure to run the backup during lull periods? --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- > >> >> Cheers, >> >> Jeff
Re: [GENERAL] Backup "Best Practices"
On Nov 28, 2016, at 10:20 PM, Thomas Kellerer <spam_ea...@gmx.net> wrote: > > Israel Brewster schrieb am 28.11.2016 um 23:50: >>> >>> pg_archivecleanup -n /mnt/server/archiverdir >>> 00010010.0020.backup >> >> Ok, but where does that "00010010.0020.backup" >> come from? I mean, I can tell it's a WAL segment file name (plus a >> backup label), but I don't have anything like that in my WAL >> archives, even though I've run pg_basebackup a couple of times. Do I >> have to call something to create that file? Some flag to >> pg_basebackup? At the moment I am running pg_basebackup such that it >> generates gziped tar files, if that makes a difference. > > The .backup file will be inside the tar file if I'm not mistaken Oh, ok - thanks. I'll check on that. Although since I am generating gziped tar files, that's not the easiest thing in the world - I'll have to decompress the file first, then extract the .backup file. Still, worth looking into I suppose :-) --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- > > > > > -- > 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] Backup "Best Practices"
On Nov 28, 2016, at 10:04 PM, Jeff Janes <jeff.ja...@gmail.com> wrote: > > On Mon, Nov 28, 2016 at 2:50 PM, Israel Brewster <isr...@ravnalaska.net > <mailto:isr...@ravnalaska.net>> wrote: > >> - What is the "best" (or just a good) method of keeping the WAL archives >> under control? Obviously when I do a new basebackup I can "cleanup" any old >> files that said backup doesn't need, >> >> You have said you might be interested in doing PITR. So you want to delay >> the cleanup so as to not compromise that ability. You need to develop a >> policy on how far back you want to be able to do a PITR. >> >> >> but how do I know what those are? >> >> pg_archivecleanup -n /mnt/server/archiverdir >> 00010010.0020.backup > > Ok, but where does that "00010010.0020.backup" come from? > I mean, I can tell it's a WAL segment file name (plus a backup label), but I > don't have anything like that in my WAL archives, even though I've run > pg_basebackup a couple of times. > > I get one file like that for every pg_basebackup I run. Could your > archive_command be doing something to specifically short-circuit the writing > of those files? Like testing the length of %p or %f? My archive command is simply a copy - straight out of the examples given in the documentation, actually. Only test I do is to make sure the file doesn't exist before running the copy > Do I have to call something to create that file? Some flag to pg_basebackup? > At the moment I am running pg_basebackup such that it generates gziped tar > files, if that makes a difference. > > > That is how I run it as well. I don't think there is a flag to pg_basebackup > which even allows you to bypass the creation of those files. You are looking > in the WAL archive itself, correct? Not somewhere in a listing of the > base.tar.gz file? I am looking at the WAL archive itself. One thing that just occurred to me: in my testing, I've been running the base backup from the secondary slave server. Perhaps that makes a difference? I know the slave itself doesn't archive WAL files, but I would have expected the master to get the message a backup was being run and do any needed archiving itself. > > Cheers, > > Jeff
Re: [GENERAL] Backup "Best Practices"
On Nov 28, 2016, at 2:13 PM, John R Pierce <pie...@hogranch.com> wrote: > > On 11/28/2016 2:50 PM, Israel Brewster wrote: >>> >>> Why wouldn't the streaming replica salvage that? Are they expected to fail >>> together? Is the NFS share onto which you want to store your basebackup >>> and WAL also expected to fail together with them? >> >> That's why I specified *total* failure. If only the primary dies, that's a >> simple cutover to the secondary, and not a total failure - no need to go to >> backups of any sort under that scenario :-) I'm thinking more along the >> lines of server room burns down, natural disaster, etc - something that >> causes a total failure of the db system, necessitating recovery from an >> offsite backup. > > your base backups and WAL archive should be replicated offsite, then. > > Obviously :-) > > -- > john r pierce, recycling bits in santa cruz
Re: [GENERAL] Backup "Best Practices"
On Nov 25, 2016, at 1:00 PM, Jeff Janes <jeff.ja...@gmail.com> wrote: > > On Wed, Nov 23, 2016 at 10:16 AM, Israel Brewster <isr...@ravnalaska.net > <mailto:isr...@ravnalaska.net>> wrote: > I was wondering if someone could inform me about, or point me to an online > article about best practices for backing up a postgresql database cluster? At > the moment, I have two servers running with streaming replication for > failover purposes, and then I do nightly database dumps for recovery/backup > purposes. However, I feel this approach is lacking in a couple areas I can > think of: > > - If a total failure occurs towards the end of the day, we could potentially > loose a whole days worth of data. > > Why wouldn't the streaming replica salvage that? Are they expected to fail > together? Is the NFS share onto which you want to store your basebackup and > WAL also expected to fail together with them? That's why I specified *total* failure. If only the primary dies, that's a simple cutover to the secondary, and not a total failure - no need to go to backups of any sort under that scenario :-) I'm thinking more along the lines of server room burns down, natural disaster, etc - something that causes a total failure of the db system, necessitating recovery from an offsite backup. > > Similar argument for user error - there is no way to "undo" a catastrophic > user data error without going all the way back to the previous day > - Less-than-ideal recovery under some scenarios. Since each database in the > cluster is dumped individually, this is good should only *one* database need > to be restored, but could get tedious should the entire cluster need to be > restored. > > To mitigate these issues, I am thinking of supplementing the individual dumps > with a full base backup and WAL archiving to a NFS share. This should enable > (relatively) quick/easy recovery from backup, plus the ability to do PIT > Recovery. I do have a few questions with this approach, however: > > - How do I figure out how often I should take a full base backup? I know this > will depend on the amount of traffic my database is doing, and how long I am > willing to wait for WAL files to be replayed - the more WAL files needing > replayed, the longer recovery will take - but is there some rule of thumb > that I can use to calculate how often I need a new base backup? Perhaps based > on the number of WAL files? > > You have to try it and see. Different types of wal records will take > different amounts of time to re-play, so there is no rule of thumb. It would > depend on the type of traffic you have in your database. And it could be > limited by a single CPU, or by IO. If the restore_command needs to restore > the WAL from a remote server, it is very likely to be limited by the latency > of doing that. In fact, this is often the bottleneck even if it is restoring > from the local server, at least if archival is often driven by > archive_timeout. > > When I need to re-clone production to get a fresh server to use for dev or > testing, I do so using almost exactly the same method I would use for > restoring production from a disaster (restore from most recent basebackup, > then recovery from WAL archive). So I know how long it takes for the > recovery to happen based on true experience, and I take a new basebackup when > that length of time starts to annoy me. Gotcha. Guess I'll have to start running some tests :-) > > - What is the "best" (or just a good) method of keeping the WAL archives > under control? Obviously when I do a new basebackup I can "cleanup" any old > files that said backup doesn't need, > > You have said you might be interested in doing PITR. So you want to delay the > cleanup so as to not compromise that ability. You need to develop a policy > on how far back you want to be able to do a PITR. > > > but how do I know what those are? > > pg_archivecleanup -n /mnt/server/archiverdir > 00010010.0020.backup Ok, but where does that "00010010.0020.backup" come from? I mean, I can tell it's a WAL segment file name (plus a backup label), but I don't have anything like that in my WAL archives, even though I've run pg_basebackup a couple of times. Do I have to call something to create that file? Some flag to pg_basebackup? At the moment I am running pg_basebackup such that it generates gziped tar files, if that makes a difference. > > > - Should I be looking at any other backup methods in addition to/instead of > the basebackup/WAL archive scheme? > > > You may want to consider pg_receivexlog to maintain your WAL archive, rather > than archive_comma
Re: [GENERAL] Backup "Best Practices"
On Nov 23, 2016, at 10:31 AM, John R Pierce <pie...@hogranch.com> wrote: > > On 11/23/2016 11:20 AM, Israel Brewster wrote: >> How do I determine which those are? Just based on the timestamp if the WAL >> file, such that I could do something like take the timestamp of the last >> basebackup and delete all WAL files older than that? Or is there a better >> way? > > there's a tool, barman, I've never used but its supposed to greatly simplify > this whole process... > > http://www.pgbarman.org/ <http://www.pgbarman.org/> Definitely looks like something to try. Thanks! --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- > > -- > john r pierce, recycling bits in santa cruz > > > > -- > 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] Backup "Best Practices"
---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD On Nov 23, 2016, at 9:42 AM, John R Pierce <pie...@hogranch.com> wrote:On 11/23/2016 10:16 AM, Israel Brewster wrote:To mitigate these issues, I am thinking of supplementing the individual dumps with a full base backup and WAL archiving to a NFS share. This should enable (relatively) quick/easy recovery from backup, plus the ability to do PIT Recovery. I do have a few questions with this approach, however:if you do the base backup periodically (daily? weekly? monthly? kinda depends on your workload, data volume, and available space on your WAL archive server)- How do I figure out how often I should take a full base backup? I know this will depend on the amount of traffic my database is doing, and how long I am willing to wait for WAL files to be replayed - the more WAL files needing replayed, the longer recovery will take - but is there some rule of thumb that I can use to calculate how often I need a new base backup? Perhaps based on the number of WAL files?frequency of doing this is entirely dependent on your data volume, how long the backups take, and your tolerance for restore times. wal archive recoveries are generally quite fast, but of course if there's millions of wal files to recover, that would take a long time. note, too, 9.6 has some major performance enhancements in how checkpoints are written, which should speed things up overall.Backups (using pg_basebackup to a tar file, gziped) take about 45 minutes. Part of the goal here is to minimize restore time in a disaster recovery scenario, so *fairly* low tolerance for restore times (say, after getting the base backup in place, another 5 minutes or less to get it up-and-running). My difficulty is that I don't know if that translates into two WAL files or two thousand. But that was really my question - is there some way to know how many WAL files are too many, and by extension how often I need to do a new base backup? Obviously how quickly I get to that point of "too many" WAL files could vary widely, but if I could get some idea of what that line is, or at least how to figure out that line, that would really help with planning.From what you said, it sounds like I can go for a while, and allow quite a few WAL files to pile up (especially with 9.6, which I will be moving to shortly) between base backups without creating too large a recovery delay. So I'm thinking I may try weekly or even monthly base backups and see how that goes.- What is the "best" (or just a good) method of keeping the WAL archives under control? Obviously when I do a new basebackup I can "cleanup" any old files that said backup doesn't need, but how do I know what those are?- Should I be looking at any other backup methods in addition to/instead of the basebackup/WAL archive scheme?I would keep at least 2 base backups and all wal files since the start of the oldest base backup. Good thinking. I'll look into implementing that.when you create a new base backup, delete the oldest, and all wal archive files up to the start of the following one. How do I determine which those are? Just based on the timestamp if the WAL file, such that I could do something like take the timestamp of the last basebackup and delete all WAL files older than that? Or is there a better way?the reason for keeping at least two is so you always have a recovery point if something catastrophic happens during the backup process.note that having base backups plus wal archives allows PiTR too, point in time recovery. say you discover a bad script updated a ton of bad data into your database last tuesday at 3pm. you can restore the preceding base backup, then recover up to tuesday just before this event.Exactly - that's one of the primary reasons I'm taking this approach.Thanks again for the info!-- john r pierce, recycling bits in santa cruz-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Backup "Best Practices"
I was wondering if someone could inform me about, or point me to an online article about best practices for backing up a postgresql database cluster? At the moment, I have two servers running with streaming replication for failover purposes, and then I do nightly database dumps for recovery/backup purposes. However, I feel this approach is lacking in a couple areas I can think of:- If a total failure occurs towards the end of the day, we could potentially loose a whole days worth of data. Similar argument for user error - there is no way to "undo" a catastrophic user data error without going all the way back to the previous day- Less-than-ideal recovery under some scenarios. Since each database in the cluster is dumped individually, this is good should only *one* database need to be restored, but could get tedious should the entire cluster need to be restored.To mitigate these issues, I am thinking of supplementing the individual dumps with a full base backup and WAL archiving to a NFS share. This should enable (relatively) quick/easy recovery from backup, plus the ability to do PIT Recovery. I do have a few questions with this approach, however:- How do I figure out how often I should take a full base backup? I know this will depend on the amount of traffic my database is doing, and how long I am willing to wait for WAL files to be replayed - the more WAL files needing replayed, the longer recovery will take - but is there some rule of thumb that I can use to calculate how often I need a new base backup? Perhaps based on the number of WAL files?- What is the "best" (or just a good) method of keeping the WAL archives under control? Obviously when I do a new basebackup I can "cleanup" any old files that said backup doesn't need, but how do I know what those are?- Should I be looking at any other backup methods in addition to/instead of the basebackup/WAL archive scheme?Thanks for any information! ---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
Re: [GENERAL] Streaming replication failover/failback
On Nov 18, 2016, at 5:48 AM, Jehan-Guillaume de Rorthais <iog...@free.fr> wrote: > > On Thu, 17 Nov 2016 08:26:59 -0900 > Israel Brewster <isr...@ravnalaska.net <mailto:isr...@ravnalaska.net>> wrote: > >>> On Nov 16, 2016, at 4:24 PM, Adrian Klaver <adrian.kla...@aklaver.com> >>> wrote: >>> >>> On 11/16/2016 04:51 PM, Israel Brewster wrote: >>>> I've been playing around with streaming replication, and discovered that >>>> the following series of steps *appears* to work without complaint: >>>> >>>> - Start with master on server A, slave on server B, replicating via >>>> streaming replication with replication slots. >>>> - Shut down master on A >>>> - Promote slave on B to master >>>> - Create recovery.conf on A pointing to B >>>> - Start (as slave) on A, streaming from B >>>> >>>> After those steps, A comes up as a streaming replica of B, and works as >>>> expected. In my testing I can go back and forth between the two servers >>>> all day using the above steps. >>>> >>>> My understanding from my initial research, however, is that this >>>> shouldn't be possible - I should need to perform a new basebackup from B >>>> to A after promoting B to master before I can restart A as a slave. Is >>>> the observed behavior then just a "lucky fluke" that I shouldn't rely >>> >>> You don't say how active the database is, but I going to say it is not >>> active enough for the WAL files on B to go out for scope for A in the time >>> it takes you to do the switch over. >> >> Yeah, not very - this was just in testing, so essentially no activity. So >> between your response and the one from Jehan-Guillaume de Rorthais, what I'm >> hearing is that my information about the basebackup being needed was >> obsoleted with the patch he linked to, and as long as I do a clean shutdown >> of the master, and don't do too much activity on the *new* master before >> bringing the old master up as a slave (such that WAL files are lost) > > Just set up wal archiving to avoid this (and have PITR backup as a side > effect). Good point. Streaming replication may not *need* WAL archiving to work, but having it can provide other benefits than just replication. I'll have to look more into the PITR backup though - that's something that sounds great to have, but I have no clue, beyond the concept, how it works. :-) --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org > <mailto:pgsql-general@postgresql.org>) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > <http://www.postgresql.org/mailpref/pgsql-general>
Re: [GENERAL] Streaming replication failover/failback
On Nov 16, 2016, at 11:39 PM, Jehan-Guillaume de Rorthais <iog...@free.fr> wrote: > > On Wed, 16 Nov 2016 15:51:26 -0900 > Israel Brewster <isr...@ravnalaska.net <mailto:isr...@ravnalaska.net>> wrote: > >> I've been playing around with streaming replication, and discovered that the >> following series of steps *appears* to work without complaint: >> >> - Start with master on server A, slave on server B, replicating via streaming >> replication with replication slots. >> - Shut down master on A >> - Promote slave on B to master >> - Create recovery.conf on A pointing to B >> - Start (as slave) on A, streaming from B >> >> After those steps, A comes up as a streaming replica of B, and works as >> expected. In my testing I can go back and forth between the two servers all >> day using the above steps. >> >> My understanding from my initial research, however, is that this shouldn't be >> possible - I should need to perform a new basebackup from B to A after >> promoting B to master before I can restart A as a slave. Is the observed >> behavior then just a "lucky fluke" that I shouldn't rely on? > > No, it's not a "lucky fluke". > > See > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=985bd7d49726c9f178558491d31a570d47340459 > > <https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=985bd7d49726c9f178558491d31a570d47340459> > > The only thing you should really pay attention is that the standby was in > Streaming Rep when you instructed the master to shut down, and that it stays > connected until the full stop of the master. > > If you really want to check everything, use pg_xlogdump on the standby and > make > sure the standby received the "shutdown checkpoint" from the master and wrote > it in its WAL. > >> Or is it expected behavior and my understanding about the need for a new >> basebackup is simply off? > > This is expected, but taking a new basebackup was a requirement for some time. > >> Does the new pg_rewind feature of 9.5 change things? If so, how? > > pg_rewind helps if your standby was not connected when you lost/stopped your > master. It reverts the last transactions the master received and that was not > streamed to the promoted standby. Ah, ok. So kinda an emergency recovery tool then? One step before resorting to backups? In any case, it sounds like it's not something I should need in a *normal* failover scenario, where the master goes down and the slave gets promoted. Thanks for the information! --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- > > Regards, > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org > <mailto:pgsql-general@postgresql.org>) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > <http://www.postgresql.org/mailpref/pgsql-general>
Re: [GENERAL] Streaming replication failover/failback
> On Nov 16, 2016, at 4:24 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > On 11/16/2016 04:51 PM, Israel Brewster wrote: >> I've been playing around with streaming replication, and discovered that >> the following series of steps *appears* to work without complaint: >> >> - Start with master on server A, slave on server B, replicating via >> streaming replication with replication slots. >> - Shut down master on A >> - Promote slave on B to master >> - Create recovery.conf on A pointing to B >> - Start (as slave) on A, streaming from B >> >> After those steps, A comes up as a streaming replica of B, and works as >> expected. In my testing I can go back and forth between the two servers >> all day using the above steps. >> >> My understanding from my initial research, however, is that this >> shouldn't be possible - I should need to perform a new basebackup from B >> to A after promoting B to master before I can restart A as a slave. Is >> the observed behavior then just a "lucky fluke" that I shouldn't rely > > You don't say how active the database is, but I going to say it is not active > enough for the WAL files on B to go out for scope for A in the time it takes > you to do the switch over. Yeah, not very - this was just in testing, so essentially no activity. So between your response and the one from Jehan-Guillaume de Rorthais, what I'm hearing is that my information about the basebackup being needed was obsoleted with the patch he linked to, and as long as I do a clean shutdown of the master, and don't do too much activity on the *new* master before bringing the old master up as a slave (such that WAL files are lost), then the above failover/failback procedure is perfectly fine to rely on in production - I don't have to worry about there being any hidden gotchas like the new slave not *really* replicating or something. Thanks! --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- > >> on? Or is it expected behavior and my understanding about the need for a >> new basebackup is simply off? Does the new pg_rewind feature of 9.5 >> change things? If so, how? >> >> Thanks for your time! >> --- >> Israel Brewster >> Systems Analyst II >> Ravn Alaska >> 5245 Airport Industrial Rd >> Fairbanks, AK 99709 >> (907) 450-7293 >> --- >> >> >> >> >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org > <mailto:pgsql-general@postgresql.org>) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > <http://www.postgresql.org/mailpref/pgsql-general>
[GENERAL] Streaming replication failover/failback
I've been playing around with streaming replication, and discovered that the following series of steps *appears* to work without complaint:- Start with master on server A, slave on server B, replicating via streaming replication with replication slots.- Shut down master on A- Promote slave on B to master- Create recovery.conf on A pointing to B- Start (as slave) on A, streaming from BAfter those steps, A comes up as a streaming replica of B, and works as expected. In my testing I can go back and forth between the two servers all day using the above steps.My understanding from my initial research, however, is that this shouldn't be possible - I should need to perform a new basebackup from B to A after promoting B to master before I can restart A as a slave. Is the observed behavior then just a "lucky fluke" that I shouldn't rely on? Or is it expected behavior and my understanding about the need for a new basebackup is simply off? Does the new pg_rewind feature of 9.5 change things? If so, how?Thanks for your time! ---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
Re: [GENERAL] Determining server load
On Sep 27, 2016, at 11:16 AM, John R Pierce <pie...@hogranch.com> wrote: > > On 9/27/2016 12:06 PM, Israel Brewster wrote: >> That helps for one-time stat collection, but as I mentioned in my original >> message, since connections may not last long, I could be getting close to, >> or even hitting, my connection limit while still getting values back from >> those that show plenty of connections remaining, depending on how often I >> checked. >> >> I guess what would be ideal in my mind is that whenever Postgresql logged an >> opened/closed connection, it also looked the *total* number of open >> connections at that time. I don't think that's possible, however :-) > > if you stick pgbouncer in front of postgres (with a pool for each > user@database), I believe you CAN track the max connections via pgbouncer's > pool stats. Ahh! If so, that alone would be reason enough for using pgbouncer. Thanks! --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- > > > -- > john r pierce, recycling bits in santa cruz > > > > -- > 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
Re: [GENERAL] Determining server load
--- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD > On Sep 27, 2016, at 10:48 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > On 09/27/2016 11:40 AM, Israel Brewster wrote: >> On Sep 27, 2016, at 9:55 AM, John R Pierce <pie...@hogranch.com> wrote: >>> >>> On 9/27/2016 9:54 AM, Israel Brewster wrote: >>>> >>>> I did look at pgbadger, which tells me I have gotten as high as 62 >>>> connections/second, but given that most of those connections are probably >>>> very short lived that doesn't really tell me anything about concurrent >>>> connections. >>> >>> Each connection requires a process fork of the database server, which is >>> very expensive. you might consider using a connection pool such as >>> pgbouncer, to maintain a fixed(dynamic) number of real database >>> connections, and have your apps connect/disconnect to this pool. >>> Obviously, you need a pool for each database, and your apps need to be >>> 'stateless' and not make or rely on any session changes to the connection >>> so they don't interfere with each other. Doing this correctly can make an >>> huge performance improvement on the sort of apps that do (connect, >>> transaction, disconnect) a lot. >> >> Understood. My main *performance critical* apps all use an internal >> connection pool for this reason - Python's psycopg2 pool, to be exact. I >> still see a lot of connects/disconnects, but I *think* that's psycopg2 >> recycling connections in the background - I'm not 100% certain how the pools >> there work (and maybe they need some tweaking as well, i.e. setting to >> re-use connections more times or something). The apps that don't use pools >> are typically data-gathering scripts where it doesn't mater how long it >> takes to connect/write the data (within reason). > > http://initd.org/psycopg/docs/pool.html > > "Note > > This pool class is mostly designed to interact with Zope and probably not > useful in generic applications. " > > Are you using Zope? You'll notice that note only applies to the PersistentConnectionPool, not the ThreadedConnectionPool (Which has a note saying that it can be safely used in multi-threaded applications), or the SimpleConnectionPool (which is useful only for single-threaded applications). Since I'm not using Zope, and do have multi-threaded applications, I'm naturally using the ThreadedConnectionPool :-) > >> >> That said, it seems highly probable, if not a given, that there comes a >> point where the overhead of handling all those connections starts slowing >> things down, and not just for the new connection being made. How to figure >> out where that point is for my system, and how close to it I am at the >> moment, is a large part of what I am wondering. >> >> Note also that I did realize I was completely wrong about the initial issue >> - it turned out it was a network issue, not a postgresql one. Still, I think >> my specific questions still apply, if only in an academic sense now :-) >> >> --- >> Israel Brewster >> Systems Analyst II >> Ravn Alaska >> 5245 Airport Industrial Rd >> Fairbanks, AK 99709 >> (907) 450-7293 >> --- >> >> >>> >>> >>> >>> -- >>> john r pierce, recycling bits in santa cruz >>> >>> >>> >>> -- >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >> >> >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > > -- > 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
Re: [GENERAL] Determining server load
On Sep 27, 2016, at 10:55 AM, Melvin Davidson <melvin6...@gmail.com> wrote: > > > > On Tue, Sep 27, 2016 at 2:46 PM, Israel Brewster <isr...@ravnalaska.net > <mailto:isr...@ravnalaska.net>> wrote: >> >I'm still curious as to how I can track concurrent connections, ... >> >> Have you considered enabling the following in postgresql.conf? >> log_connections=on >> log_disconnections=on >> >> It will put a bit of a bloat in you postgres log, but it will all allow you >> extract connects/disconnects over a time range. That should allow you >> to determine concurrent connections during that that. > > I do have those on, and I could write a parser that scans through the logs > counting connections and disconnections to give a number of current > connections at any given time. Trying to make it operate "in real time" would > be interesting, though, as PG logs into different files by day-of-the-week > (at least, with the settings I have), rather than into a single file that > gets rotated out. I was kind of hoping such a tool, such as pgbadger (which, > unfortunately, only seems to track connections per second and not consecutive > connections), already existed, or that there was some way to have the > database itself track this metric. If not, well, I guess that's another > project :) > > --- > Israel Brewster > Systems Analyst II > Ravn Alaska > 5245 Airport Industrial Rd > Fairbanks, AK 99709 > (907) 450-7293 <tel:%28907%29%20450-7293> > --- > >> >> >> Melvin Davidson >> I reserve the right to fantasize. Whether or not you >> wish to share my fantasy is entirely up to you. > > > Does this help? > > --Total concurrent connections > SELECT COUNT(*) > FROM pg_stat_activity; > > --concurrent connections by user > SELECT usename, >count(*) > FROM pg_stat_activity > GROUP BY 1 > ORDER BY 1; > > --concurrent connections by database > SELECT datname, >usename, >count(*) > FROM pg_stat_activity > GROUP BY 1, 2 > ORDER BY 1, 2; > > -- database connections by user > SELECT usename, >datname, >count(*) > FROM pg_stat_activity > GROUP BY 1, 2 > ORDER BY 1, 2; > > -- > Melvin Davidson > I reserve the right to fantasize. Whether or not you > wish to share my fantasy is entirely up to you. That helps for one-time stat collection, but as I mentioned in my original message, since connections may not last long, I could be getting close to, or even hitting, my connection limit while still getting values back from those that show plenty of connections remaining, depending on how often I checked. I guess what would be ideal in my mind is that whenever Postgresql logged an opened/closed connection, it also looked the *total* number of open connections at that time. I don't think that's possible, however :-) --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 ---
Re: [GENERAL] Determining server load
On Sep 27, 2016, at 10:55 AM, Jonathan Vanasco <postg...@2xlp.com> wrote: > > > On Sep 27, 2016, at 2:46 PM, Israel Brewster wrote: > >> I do have those on, and I could write a parser that scans through the logs >> counting connections and disconnections to give a number of current >> connections at any given time. Trying to make it operate "in real time" >> would be interesting, though, as PG logs into different files by >> day-of-the-week (at least, with the settings I have), rather than into a >> single file that gets rotated out. I was kind of hoping such a tool, such as >> pgbadger (which, unfortunately, only seems to track connections per second >> and not consecutive connections), already existed, or that there was some >> way to have the database itself track this metric. If not, well, I guess >> that's another project :) > > There are a lot of postgres configs and server specific tools... but on the > application side and for general debugging, have you looked at statsd ? > https://github.com/etsy/statsd <https://github.com/etsy/statsd> > > it's a lightweight node.js app that runs on your server and listens for UDP > signals, which your apps can emit for counting or timing. We have a ton of > Python apps logging to it, including every postgres connection open/close and > error. The overhead of clients and server is negligible. When combined with > the graphite app for browsing data via charts, it becomes really useful at > detecting issues with load or errors stemming from a deployment -- you just > look for spikes and cliffs. We even use it to log the volume of INSERTS vs > SELECTS vs UPDATES being sent to postgres. > > The more services/apps you run, the more useful it gets, as you can figure > out which apps/deployments are screwing up postgres and the exact moment > things went wrong. > That sounds quite promising. I'll look into it. Thanks! --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 ---
Re: [GENERAL] Determining server load
> >I'm still curious as to how I can track concurrent connections, ... > > Have you considered enabling the following in postgresql.conf? > log_connections=on > log_disconnections=on > > It will put a bit of a bloat in you postgres log, but it will all allow you > extract connects/disconnects over a time range. That should allow you > to determine concurrent connections during that that. I do have those on, and I could write a parser that scans through the logs counting connections and disconnections to give a number of current connections at any given time. Trying to make it operate "in real time" would be interesting, though, as PG logs into different files by day-of-the-week (at least, with the settings I have), rather than into a single file that gets rotated out. I was kind of hoping such a tool, such as pgbadger (which, unfortunately, only seems to track connections per second and not consecutive connections), already existed, or that there was some way to have the database itself track this metric. If not, well, I guess that's another project :) --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- > > > Melvin Davidson > I reserve the right to fantasize. Whether or not you > wish to share my fantasy is entirely up to you.
Re: [GENERAL] Determining server load
On Sep 27, 2016, at 9:55 AM, John R Pierce <pie...@hogranch.com> wrote: > > On 9/27/2016 9:54 AM, Israel Brewster wrote: >> >> I did look at pgbadger, which tells me I have gotten as high as 62 >> connections/second, but given that most of those connections are probably >> very short lived that doesn't really tell me anything about concurrent >> connections. > > Each connection requires a process fork of the database server, which is very > expensive. you might consider using a connection pool such as pgbouncer, to > maintain a fixed(dynamic) number of real database connections, and have your > apps connect/disconnect to this pool.Obviously, you need a pool for each > database, and your apps need to be 'stateless' and not make or rely on any > session changes to the connection so they don't interfere with each other. > Doing this correctly can make an huge performance improvement on the sort of > apps that do (connect, transaction, disconnect) a lot. Understood. My main *performance critical* apps all use an internal connection pool for this reason - Python's psycopg2 pool, to be exact. I still see a lot of connects/disconnects, but I *think* that's psycopg2 recycling connections in the background - I'm not 100% certain how the pools there work (and maybe they need some tweaking as well, i.e. setting to re-use connections more times or something). The apps that don't use pools are typically data-gathering scripts where it doesn't mater how long it takes to connect/write the data (within reason). That said, it seems highly probable, if not a given, that there comes a point where the overhead of handling all those connections starts slowing things down, and not just for the new connection being made. How to figure out where that point is for my system, and how close to it I am at the moment, is a large part of what I am wondering. Note also that I did realize I was completely wrong about the initial issue - it turned out it was a network issue, not a postgresql one. Still, I think my specific questions still apply, if only in an academic sense now :-) --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- > > > > -- > john r pierce, recycling bits in santa cruz > > > > -- > 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
Re: [GENERAL] Determining server load
On Sep 27, 2016, at 10:07 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > On 09/27/2016 09:54 AM, Israel Brewster wrote: >> I have a Postgresql (9.4.6) cluster that hosts several databases, used >> by about half-a-dozen different in-house apps. I have two servers set up >> as master-slave with streaming replication. Lately I've been running >> into an issue where one of the apps periodically can't connect to the >> db. The problem is always extremely short lived (less than a minute), >> such that by the time I can look into it, there is no issue. My >> *suspicion* is that I am briefly hitting the max connection limit of my >> server (currently set at 100). If so, I can certainly *fix* the issue >> easily by increasing the connection limit, but I have two questions >> about this: > > What does your Postgres log show around this time? So in looking further, I realized the actual error I was getting was "no route to host", which is obviously a networking issue and not a postgres issue - could not connect was only the end result. The logs then, of course, show normal operation. That said, now that I am thinking about it, I'm still curious as to how I can track concurrent connections, with the revised goal of simply seeing how heavily loaded my server really is, and when tools such as pgpool or the pgbouncer that another user mentioned start making sense for the number of connections I am dealing with. Thanks. --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- > >> >> 1) Is there some way I can track concurrent connections to see if my >> theory is correct? I know I can do a count(*) on pg_stat_activity to get >> the current number of connections at any point (currently at 45 BTW), >> but aside from repeatedly querying this at short intervals, which I am >> afraid would put undue load on the server by the time it is frequent >> enough to be of use, I don't know how to track concurrent connections. >> >> I did look at pgbadger, which tells me I have gotten as high as 62 >> connections/second, but given that most of those connections are >> probably very short lived that doesn't really tell me anything about >> concurrent connections. >> >> 2) Is increasing the connection limit even the "proper" fix for this, or >> am I at a load point where I need to start looking at tools like pgpool >> or something to distribute some of the load to my hot standby server? I >> do realize you may not be able to answer that directly, since I haven't >> given enough information about my server/hardware/load, etc, but answers >> that tell me how to better look at the load over time and figure out if >> I am overloaded are appreciated. >> >> For reference, the server is running on the following hardware: >> >> 2x 8-core Xeon E5-2630 v3 2.4 GHz processor (so plenty of horsepower there) >> 32 GB Ram total, currently with 533144k showing as "free" and 370464k of >> swap used >> 371 GB SSD RAID 10 (currently only using 40GB of space) >> Dual Gigabit ethernet >> >> Thanks for any advice that can be provided! >> --- >> Israel Brewster >> Systems Analyst II >> Ravn Alaska >> 5245 Airport Industrial Rd >> Fairbanks, AK 99709 >> (907) 450-7293 >> --- >> >> >> >> >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > > -- > 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] Determining server load
I have a Postgresql (9.4.6) cluster that hosts several databases, used by about half-a-dozen different in-house apps. I have two servers set up as master-slave with streaming replication. Lately I've been running into an issue where one of the apps periodically can't connect to the db. The problem is always extremely short lived (less than a minute), such that by the time I can look into it, there is no issue. My *suspicion* is that I am briefly hitting the max connection limit of my server (currently set at 100). If so, I can certainly *fix* the issue easily by increasing the connection limit, but I have two questions about this:1) Is there some way I can track concurrent connections to see if my theory is correct? I know I can do a count(*) on pg_stat_activity to get the current number of connections at any point (currently at 45 BTW), but aside from repeatedly querying this at short intervals, which I am afraid would put undue load on the server by the time it is frequent enough to be of use, I don't know how to track concurrent connections.I did look at pgbadger, which tells me I have gotten as high as 62 connections/second, but given that most of those connections are probably very short lived that doesn't really tell me anything about concurrent connections.2) Is increasing the connection limit even the "proper" fix for this, or am I at a load point where I need to start looking at tools like pgpool or something to distribute some of the load to my hot standby server? I do realize you may not be able to answer that directly, since I haven't given enough information about my server/hardware/load, etc, but answers that tell me how to better look at the load over time and figure out if I am overloaded are appreciated.For reference, the server is running on the following hardware:2x 8-core Xeon E5-2630 v3 2.4 GHz processor (so plenty of horsepower there)32 GB Ram total, currently with 533144k showing as "free" and 370464k of swap used 371 GB SSD RAID 10 (currently only using 40GB of space)Dual Gigabit ethernetThanks for any advice that can be provided! ---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
Re: [GENERAL] "Keyed" sequence?
On Apr 28, 2016, at 11:21 AM, David G. Johnston <david.g.johns...@gmail.com> wrote: > > On Thu, Apr 28, 2016 at 12:09 PM, Israel Brewster <isr...@ravnalaska.net > <mailto:isr...@ravnalaska.net>> wrote: > > > > > It'll kill your performance, but if aesthetics are that important to you... > > They're not *that* important. I was just asking if there was a way to do this > easily. > > While the performance argument might be true it is heavily dependent upon > concurrency. I'm doubting a PO system in the typical company has enough > concurrency, and is sensitivity enough to small delays, that giving up the > benefit of sequential numbering would be a worthwhile trade-off. I'm thinking the same - especially considering that we aren't exactly a huge company. > > David J. > --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 ---
Re: [GENERAL] "Keyed" sequence?
--- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD > On Apr 28, 2016, at 11:01 AM, Vik Fearing <v...@2ndquadrant.fr> wrote: > > On 04/28/2016 08:48 PM, Israel Brewster wrote: >>> >>> On Apr 28, 2016, at 10:39 AM, Vik Fearing <v...@2ndquadrant.fr> wrote: >>> >>> What would be the point of this? Why not just one sequence for all >>> departments? >> >> continuity and appearance, not to mention simple logical progression. In >> this case, the sequence is being used to generate a PO number. Company style >> indicates that a PO number is a department code followed by a unique number. >> With one sequence for all departments, you could (will) end up with >> discontinuous PO numbers in any given department. It would be nice if, after >> issuing PO number 15-1, the next PO in department 15 was 2, if for no other >> reason than the accounting department could easily see that they aren't >> missing any. With one sequence, there will quite likely not be a PO number 2 >> for any given department, so that department has no easy way to keep track >> of their PO's based on PO number. > > You're not guaranteed that even with individual sequences. True, in the event that an insert fails or the like, there would be a gap of one number. However, with a single sequence you are guaranteed to have gaps, and guaranteed that they will be significant. > > What' you're looking for is a gapless sequence, which is best simulated > with a table. In your case, I'd just add a column to your existing > departments table holding the next number to use. Yeah, that looks like it could be the way to go. Thanks. > > It'll kill your performance, but if aesthetics are that important to you... They're not *that* important. I was just asking if there was a way to do this easily. > -- > Vik Fearing +33 6 46 75 15 36 > http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support > > > -- > 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
Re: [GENERAL] "Keyed" sequence?
> > On Apr 28, 2016, at 10:39 AM, Vik Fearing <v...@2ndquadrant.fr> wrote: > > On 04/28/2016 08:30 PM, Israel Brewster wrote: >> This is probably crazy talk, but in Postgresql is there any way to have >> a "keyed" sequence? That is, I have a table with a list of departments. >> While *relatively* static, technically speaking this is a dynamic list - >> someone certainly could decide to add a department, or combine two >> departments into one new one, whatever. In any case, I would ideally >> like to have a sequence per department, but since the list is dynamic >> it's not as simple as creating 33 independent sequences and then having >> a lookup table somewhere, although I guess I could implement something >> of the sort with triggers. > > What would be the point of this? Why not just one sequence for all > departments? continuity and appearance, not to mention simple logical progression. In this case, the sequence is being used to generate a PO number. Company style indicates that a PO number is a department code followed by a unique number. With one sequence for all departments, you could (will) end up with discontinuous PO numbers in any given department. It would be nice if, after issuing PO number 15-1, the next PO in department 15 was 2, if for no other reason than the accounting department could easily see that they aren't missing any. With one sequence, there will quite likely not be a PO number 2 for any given department, so that department has no easy way to keep track of their PO's based on PO number. > -- > Vik Fearing +33 6 46 75 15 36 > http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support > > > -- > 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] "Keyed" sequence?
This is probably crazy talk, but in Postgresql is there any way to have a "keyed" sequence? That is, I have a table with a list of departments. While *relatively* static, technically speaking this is a dynamic list - someone certainly could decide to add a department, or combine two departments into one new one, whatever. In any case, I would ideally like to have a sequence per department, but since the list is dynamic it's not as simple as creating 33 independent sequences and then having a lookup table somewhere, although I guess I could implement something of the sort with triggers.Is there any elegant way of accomplishing this? Something like SELECT nextval('department_seq['ops'])?
---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD
VERSION:3.0
N:Brewster;Israel;;;
FN:Israel Brewster
ORG:Frontier Flying Service;MIS
TITLE:PC Support Tech II
EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com
TEL;type=WORK;type=pref:907-450-7293
item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701;
item1.X-ABADR:us
CATEGORIES:General
X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson
END:VCARD
[GENERAL] Pattern match against array elements?
Is there any way to do a pattern match against the elements of an array in postgresql (9.4 if the version makes a difference)? I have a grouped query that, among other things, returns an array of values, like: SELECT lognum, array_agg(flightnum) as flightnums FROM logs GROUP BY lognum; Where the flightnum field is a varchar containing either a text string or a three-or-four digit number. Now say I want to select all logs that have a flight number starting with an '8' (so '800' or '8000' series flights). My first thought was to do something like this: SELECT * FROM (SELECT lognum,array_agg(flightnum) as flightnums FROM logs GROUP BY lognum) s1 WHERE '8%' like ANY(flightnums); But while this doesn't give an error, it also doesn't return any results. I'm guessing that this is because the wildcard is on the left of the operator, and needs to be on the right. Of course, turning it around to be: WHERE ANY(flightnum) like '8%' gives me a syntax error. So is there any way I can run this query such that I get any rows containing a flight number that starts with an 8 (or whatever)? --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- -- 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] Pattern match against array elements?
In the general case, that might work, however in my actually use case the inner SQL query (and underlying table structure) is rather more complicated, making it so I really want the WHERE clause in an outside query, as in my (non-functional) example. Just to clarify, the actual structure of the query is more like this:SELECT FROM (SELECT(SELECT array_agg(flightnum) FROM legdetails WHERE logid=logs.id) as flightnums --this is where the array comes from that I want to filter on. FROM logs ) s1WHERE ORDER BY So the query is noticeably different than the one I original gave, but the end result is the same: an array in an inner query, and trying to filter based on the contents of the array. Sorry if I confused the issue by trying to simplify the concept too much. ---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD On Oct 12, 2015, at 10:14 AM, dinesh kumar <dineshkuma...@gmail.com> wrote:On Mon, Oct 12, 2015 at 10:58 AM, Israel Brewster <isr...@ravnalaska.net> wrote:Is there any way to do a pattern match against the elements of an array in postgresql (9.4 if the version makes a difference)? I have a grouped query that, among other things, returns an array of values, like: SELECT lognum, array_agg(flightnum) as flightnums FROM logs GROUP BY lognum; Where the flightnum field is a varchar containing either a text string or a three-or-four digit number. Now say I want to select all logs that have a flight number starting with an '8' (so '800' or '8000' series flights). My first thought was to do something like this: SELECT * FROM (SELECT lognum,array_agg(flightnum) as flightnums FROM logs GROUP BY lognum) s1 WHERE '8%' like ANY(flightnums); But while this doesn't give an error, it also doesn't return any results. I'm guessing that this is because the wildcard is on the left of the operator, and needs to be on the right. Of course, turning it around to be: WHERE ANY(flightnum) like '8%' gives me a syntax error. So is there any way I can run this query such that I get any rows containing a flight number that starts with an 8 (or whatever)? Are you looking for this ? SELECT lognum array_agg(flightnum) FROM logs WHERE flightnum ~ '^8' GROUP BY lognum; --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Regards,Dineshmanojadinesh.blogspot.com
Re: [GENERAL] Pattern match against array elements?
On Oct 12, 2015, at 10:39 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > > Jeff Janes <jeff.ja...@gmail.com> writes: >> On Mon, Oct 12, 2015 at 10:58 AM, Israel Brewster <isr...@ravnalaska.net> >> wrote: >>> My first thought was to do something like this: >>> >>> SELECT * FROM (SELECT lognum,array_agg(flightnum) as flightnums FROM logs >>> GROUP BY lognum) s1 WHERE '8%' like ANY(flightnums); >>> >>> But while this doesn't give an error, it also doesn't return any results. >>> I'm guessing that this is because the wildcard is on the left of the >>> operator, and needs to be on the right. > >> Right. The LIKE operator does not have a commutator by default. (And if >> you created one for it, it could not use an index in this case.) > > Well, it couldn't use an index anyway, given that the query as written > wants to collect groups if *any* member is LIKE '8%', rather than > restricting the data to such flightnums before aggregation occurs. > > Personally I'd suggest building a commutator operator (just need a > one-liner SQL or plpgsql function as infrastructure) and away you go. That could work. I'll look into that. > >> I think you're best bet is to do a subquery against the unaggregated table. > >> select * from aggregated a where exists >> (select 1 from unaggregated ua where a.lognum=ua.lognum and flightnum >> like '8%') > > That would work too, but not sure about performance relative to the other > way. > > regards, tom lane --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- -- 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] Pattern match against array elements?
---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD On Oct 12, 2015, at 11:50 AM, Jeff Janes <jeff.ja...@gmail.com> wrote:On Mon, Oct 12, 2015 at 11:39 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:Jeff Janes <jeff.ja...@gmail.com> writes: > On Mon, Oct 12, 2015 at 10:58 AM, Israel Brewster <isr...@ravnalaska.net> > wrote: >> My first thought was to do something like this: >> >> SELECT * FROM (SELECT lognum,array_agg(flightnum) as flightnums FROM logs >> GROUP BY lognum) s1 WHERE '8%' like ANY(flightnums); >> >> But while this doesn't give an error, it also doesn't return any results. >> I'm guessing that this is because the wildcard is on the left of the >> operator, and needs to be on the right. > Right. The LIKE operator does not have a commutator by default. (And if > you created one for it, it could not use an index in this case.) Well, it couldn't use an index anyway, given that the query as written wants to collect groups if *any* member is LIKE '8%', rather than restricting the data to such flightnums before aggregation occurs.I was jumping ahead a bit here. I was thinking of the case where the aggregation query was made a materialized view with a gin index on the aggregated column. It is not obvious that a "scalar operator ANY(flightnums)" can't use an index on array_column, but it can't. My interest was more in the % operator from pg_trgm, but also the normal text = operator would be nice to use here (as opposed to the much uglier <@ or @> in which the scalar needs to be wrapped into a degenerate array.) Personally I'd suggest building a commutator operator (just need a one-liner SQL or plpgsql function as infrastructure) and away you go.Right, something like:create function like_rev (text, text) returns boolean as $$ select $2 like $1 $$ language SQL;create operator (procedure = like_rev, leftarg=text, rightarg=text);...which actually works perfectly for my use case. No, it doesn't use an index, however even the worst case scenario on my data, where that is the ONLY criteria given, "only" takes about 10 seconds. Yes, that is a "long" time, however a) 99% of the time there will be other criteria used as well, drastically reducing the result set and speeding the query, and b) the query is used as part of a report generator, for which there isn't really a problem if the user has to wait a few seconds. You can explicitly specify the commutator but it doesn't seem to be necessary to do so:create operator (procedure = like_rev, leftarg=text, rightarg=text,commutator = ~~ ); > I think you're best bet is to do a subquery against the unaggregated table. > select * from aggregated a where exists > (select 1 from unaggregated ua where a.lognum=ua.lognum and flightnum > like '8%') That would work too, but not sure about performance relative to the other way.In my experience, if the subselect can use an index on the LIKE and if '8%' is rare, then using the subselect will be vastly better. And if it is indexable or not rare, it is still likely to be better, or at least not worse by much. Disaggregating every array for every row to do the ANY is pretty inefficient. Particularly if you are not using a materialized view, and so have to first aggregate it.Of course good enough is good enough, so if scalar ANY(array) is good enough...Exactly. I think I could make the sub-select work, with some tweaking, and as it could well improve performance noticeably I may well spend some time on it, but the commutator operator "just works" and integrates quite nicely with my existing query structure.Thanks for the help! Cheers,Jeff
[GENERAL] Sensitivity to drive failure?
How sensitive is PostgreSQL to a failure that causes it to loose a single tablespace, while the rest of the database cluster is still accessible? Will it continue running, allowing access to databases that aren't in the missing tablespace, or will it crash (or similar)?I have one rather large database in my cluster containing non-critical statistical data. Stuff that's nice to have, but if I were to loose it, I probably won't even bother trying to restore from backup - I'd just start over collecting it again. As it takes up a lot of space, I am planning to move it off of my main (raid 10) drive, and on to an external. As this data is non-critical, I was thinking of having that external just be a simple single drive, but that leads to my question - should that one drive fail, that portion of the database will obviously disappear. Will Postgress continue to function, serving up the rest of the database that is on the RAID and simply throwing errors whenever something tries to access the missing database? Or will loosing that drive with the one database bring down the entire cluster? If the latter, then I obviously need to think about doing a RAID for that extra drive as well. Thanks. ---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
Re: [GENERAL] Postgresql HA questions
Thanks. I'd taken some stabs at setting up Corosync/Pacemaker, but could never get the PostgreSQL portion to work properly. One difficulty is that I am using named replication slots, which don't appear to be supported. The other issue is that the system tended to try to start my secondary as primary, which of course doesn't work to bring up the old primary as secondary (at least, that's my understanding. Let me know if that's wrong).I'll take another stab at it given the steps outlined in the presentation you posted, and given your success - I must just be doing something wrong here. ---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD On Sep 25, 2015, at 1:46 AM, Steve Pritchard <steve.pritch...@bto.org> wrote:Israel,I can't answer all your questions, but we've just set up a HA pair with Hot Standby using Corosync/Pacemaker. However we haven't deployed this 'live' yet. We originally found a presentation from The PostgreSQL Conference PostgreSQL High Availability with Corosync/Pacemaker, and then bought the book PostgreSQL 9.0 High Performance (a sample chapter is available as a PDF).All working out well in testing at the moment.Steve PritchardBritish Trust for OrnithologyOn 23 September 2015 at 17:36, Israel Brewster <isr...@ravnalaska.net> wrote:With my application servers, I have a system set up using corosync and pacemaker that allows for seamless fail-over between the two machines, with the IP address and all services moving smoothly between the two at will. Ideally, I would have a similar setup with my database servers, so the applications never even know that there was a switch. Is this possible with Postgresql at all? Does it make a difference that at least one app has an "always on" connection to the DB Server?
[GENERAL] Postgresql HA questions
I have a couple of questions regarding setting up and administering a high availability Postgresql "cluster". At the moment the setup I have consists of two servers set up with streaming replication using the named replication slots feature of Postgresql 9.4 (currently running 9.4.1). Let me know if more details are needed about the current setup So, on to my questions:- Is there any way to do zero-downtime updates to Postgres? At first I was thinking I could just update the secondary, fail over to it, update the primary, and fail back, but then I realized that this would be much more complicated than my first thought, since I would have to either a) find a way to point all my apps that use the database to the secondary while updating, or b) re-ip the machines. Add to that the difficulty with the fail-back, and it seems like it would be a simpler solution to simply take down the primary server (and all my companies apps) for the (assumedly short) duration of the update.With my application servers, I have a system set up using corosync and pacemaker that allows for seamless fail-over between the two machines, with the IP address and all services moving smoothly between the two at will. Ideally, I would have a similar setup with my database servers, so the applications never even know that there was a switch. Is this possible with Postgresql at all? Does it make a difference that at least one app has an "always on" connection to the DB Server?- When using named replication slots, I know the primary server will hold on to as many WAL files as the secondary needs to be able to recover to a consistent state, which runs the risk of filling up the hard drive with WAL files if the secondary is off-line for an extended period of time. As such, I have a system set up to monitor the number of WAL files on both the primary and secondary machines. Watching that, I have on occasion seen the number hit as high as 300 some odd files on the secondary, although it always comes back down fairly quickly (on the day I saw the count up at 378, it was back down to 30 within 10 minutes). At the moment I have 13 WAL files, even though my wall_keep_segments parameter is set to 5. Interestingly (to me at least) I have not seen this same sort of variation on the primary - it generally stays right around 13 files. Is this sort of fluctuation normal, or should I be looking for some sort of an issue?-What is the best way to monitor that the two servers are actually staying in sync? At the moment, I am looking at the value of the "active" field in the pg_replication_slots table. Is this sufficient to be sure replication is taking place properly, or should I do something like run a query on a frequently updated table and make sure the results are identical?Thanks for any suggestions and information you can provide! ---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
Re: [GENERAL] Group by range in hour of day
On Mar 17, 2015, at 9:05 AM, David G. Johnston david.g.johns...@gmail.com wrote: On Tuesday, March 17, 2015, Israel Brewster isr...@ravnalaska.net mailto:isr...@ravnalaska.net wrote: On Mar 17, 2015, at 8:09 AM, Paul Jungwirth p...@illuminatedcomputing.com javascript:; wrote: test= select h, count(*) from start_end, generate_series(0, 23) as s(h) where h between extract(hour from start_time) and extract(hour from end_time) group by h order by h; h | count +--- 8 | 2 9 | 3 10 | 2 11 | 2 Note if you always want all 24 rows with a count of 0 when appropriate (which seems common in reports with tables or plots), you can just tweak the above query to use a left join: FROM generate_series(0, 23) AS s(h) LEFT OUTER JOIN start_end ON h BETWEEN ... Paul Right, thanks. That makes sense. So next question: how do I get the active time per hour from this? To use the same example that came up with this result set: Which is why you do not (only?) want to convert your data to hour-of-day but want to create timestamp end points. Then you simply do timestamp subtraction to get durations which you can then sum together. Well, it's not QUITE that simple. For example, row id 3 which starts at 08:00:00 and ends at 11:45:00 in the example. If I have a timestamp endpoint of 10:00:00 for the 9 hour, and I just do simple timestamp subtraction, I'll get an interval of 2 (10:00:00 - 08:00:00), which is not correct since there can't be more than an hour in any given hour. Similarly for the 11 hour and either of the two matching rows - since they end during the hour in question (row 2 only contributes 5 minutes), I'd actually need to subtract the end_time from the start point in that case to get the time. That said, the concept is sound, and I am fairly sure I can make it work using a case when statement to handle the various permutations of starting before and or ending after the hour in question. I'll work on that, but if there is a more elegant solution, I'm all ears :-) David J.
Re: [GENERAL] Group by range in hour of day
On Mar 17, 2015, at 9:30 AM, Paul Jungwirth p...@illuminatedcomputing.com wrote: So next question: how do I get the active time per hour from this? I think you just SUM() over the intersection between each hourly window and each event, right? This might be easiest using tsrange, something like this: Sounds reasonable. I've never worked with range values before, but it does seem appropriate here. SUM(extract(minutes from (tsrange(start_time, end_time) tsrange(h, h + interval '1 hour'))::interval)) I think you'll have to implement ::interval yourself though, e.g. here: http://dba.stackexchange.com/questions/52153/postgresql-9-2-number-of-days-in-a-tstzrange Gotcha Also as mentioned you'll have to convert h from an integer [0,23] to a timestamp, but that seems pretty easy. Assuming start_time and end_time are UTC that's just adding that many hours to UTC midnight of the same day. Some weird edge cases to be careful about: activities that cross midnight. Activities that last more than one full day, e.g. start 3/15 and end 3/17. Right. And I will run into some of those (at least the crossing midnight), so I'll keep an eye out. --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- Paul -- 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
Re: [GENERAL] Group by range in hour of day
On Mar 17, 2015, at 1:41 PM, Marc Mamin m.ma...@intershop.de wrote:
On Tue, Mar 17, 2015, at 11:30 AM, Adrian Klaver wrote:
On 03/17/2015 10:57 AM, Israel Brewster wrote:
On Mar 17, 2015, at 9:30 AM, Paul Jungwirth
p...@illuminatedcomputing.com wrote:
So next question: how do I get the active time per hour from this?
I think you just SUM() over the intersection between each hourly window
and each event, right? This might be easiest using tsrange, something
like this:
Sounds reasonable. I've never worked with range values before, but it
does seem appropriate here.
SUM(extract(minutes from (tsrange(start_time, end_time) tsrange(h,
h + interval '1 hour'))::interval))
I think you'll have to implement ::interval yourself though, e.g. here:
http://dba.stackexchange.com/questions/52153/postgresql-9-2-number-of-days-in-a-tstzrange
Gotcha
My take on this is using CASE.
Rough sketch:
WHEN
date_trunc('hour', end_time) h
THEN
end_time - start_time
ELSE
(date_trunc('hour', start_time) + interval '1 hr') - start_time
as
active_time
Aah, should be
WHEN
date_trunc('hour', end_time) h + 1
THEN
end_time - start_time
ELSE
(date_trunc('hour', start_time) + interval '1 hr') - start_time
as
active_time
Here another approach while building an hourly serie for each start/end
pair, truncated to the hours:
create temp table t (s timestamptz, e timestamptz);
insert into t select '2015-03-16 08:15:00','2015-03-16 09:35:00';
insert into t select '2015-03-16 09:15:00','2015-03-16 11:05:00';
insert into t select '2015-03-16 08:00:00','2015-03-16 11:45:00';
insert into t select '2015-03-17 15:15:00','2015-03-18 11:45:00';
insert into t select '2015-03-17 20:15:00','2015-03-18 11:45:00';
insert into t select '2015-03-17 21:15:00','2015-03-18 10:10:00';
insert into t select '2015-03-18 23:30:00','2015-03-19 01:30:00';
SELECT ser, SUM(
case when e - ser interval '1 hour' then e-ser --end interval
when s = ser then interval '1 hour' - (s - ser) --start interval
else interval '1 hour'
end ) as time_tot
FROM
(select e,s,
generate_series(date_trunc('hour',s), date_trunc('hour',e), '1 hour')
ser
from t
)foo
group by ser
order by 1
regards,
Marc Mamin
I missed the case when the start and end points are in the same hour:
SELECT ser, SUM(
case when e - ser interval '1 hour' then e - greatest(ser,s) --end
interval or se in same hour
when s = ser then interval '1 hour' - (s - ser) --start interval
else interval '1 hour'
end ) as time_tot
FROM
(select e,s,
generate_series(date_trunc('hour',s), date_trunc('hour',e), '1 hour')
ser
from t
)foo
group by ser
order by 1
Marc
That you all for the suggestions. I think I have it working now, using CASE
statements similar to these. I'll have to spend some time playing around with
the tsrange suggestions as well, since I think it could end up being cleaner
and safer (especially, as mentioned, for any cases where there may be date
changes involved), but at least I now have a functioning query I can tweak.
Thanks again!
---
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
---
--
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
Re: [GENERAL] Group by range in hour of day
BEGIN:VCARD
VERSION:3.0
N:Brewster;Israel;;;
FN:Israel Brewster
ORG:Frontier Flying Service;MIS
TITLE:PC Support Tech II
EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com
TEL;type=WORK;type=pref:907-450-7293
item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701;
item1.X-ABADR:us
CATEGORIES:General
X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson
END:VCARD
On Mar 16, 2015, at 3:46 PM, Adrian Klaver adrian.kla...@aklaver.com wrote:
On 03/16/2015 04:16 PM, Israel Brewster wrote:
On Mar 16, 2015, at 2:22 PM, David G. Johnston
david.g.johns...@gmail.com mailto:david.g.johns...@gmail.com wrote:
On Mon, Mar 16, 2015 at 3:13 PM, Adrian Klaver
adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.comwrote:
On 03/16/2015 02:57 PM, Israel Brewster wrote:
I have a table with two timestamp columns for the start time
and end
time of each record (call them start and end).I'm trying to
figure out
if there is a way to group these records by hour of day,
that is the
record should be included in the group if the hour of the day
for the
group falls anywhere in the range [start,end]. Obviously each
record may
well fall into multiple groups under this scenario.
The goal here is to figure out, for each hour of the day, a)
what is the
total number of active records for that hour, and b) what is
the total
active time for those records during the hour, with an
ultimate goal
of figuring out the average active time per record per hour.
So, for simplified example, if the table contained three records:
start | end
--__---
2015-03-15 08:15 | 2015-03-15 10:45
2015-03-15 09:30 | 2015-03-15 10:15
2015-03-15 10:30 | 2015-03-15 11:30
Then the results should break out something like this:
hour | count | sum
-
8 |1 | 0.75
9 |2 | 1.5
10 |3 | 1.5
11 |1 | 0.5
I can then easily manipulate these values to get my ultimate
goal of the
average, which would of course always be less than or equal to
1. Is
this doable in postgress? Or would it be a better idea to
simply pull
the raw data and post-process in code? Thanks.
Do not have an answer for you, but a question:
What version of Postgres are you on?
This will help determine what tools are available to work with.
The following will give you endpoints for your bounds. Version is
important since range types could be very useful in this situation -
but you'd still need to generate the bounds info regardless.
SELECT *
FROM
(SELECT * FROM generate_series('2015-03-15'::timestamptz,
'2015-03-16'::timestamptz, '1 hour'::interval) start (start_ts)) AS s
CROSS JOIN
(SELECT end_ts + '1 hour'::interval AS end_ts FROM
generate_series('2015-03-15'::timestamptz, '2015-03-16'::timestamptz,
'1 hour'::interval) e (end_ts)) AS e
You would join this using an ON condition with an OR (start BETWEEN
[...] OR end BETWEEN [...]) - range logic will be better and you may
want to adjust the upper bound by negative 1 (nano-second?) to allow
for easier = logic if using BETWEEN.
Thanks, that is very helpful, but are you sure CROSS JOIN is what you
wanted here? using that, I get a 625 row result set where each row from
the first SELECT is paired up with EVERY row from the second select. I
would think I would want the first row of the first SELECT paired up
with only the first row of the second, second row of the first paired
with the second row of the second, etc - i.e. 24 start and end bounds.
Or am I missing something?
Given this:
test= select * from start_end ;
id | start_time |end_time
++
1 | 2015-03-16 08:15:00-07 | 2015-03-16 09:35:00-07
2 | 2015-03-16 09:15:00-07 | 2015-03-16 11:05:00-07
3 | 2015-03-16 08:00:00-07 | 2015-03-16 11:45:00-07
using Pauls hints I got:
test= select * from start_end, generate_series(0, 23) as s(h) where h
between extract(hour from start_time) and extract(hour from end_time) ;
id | start_time |end_time| h
+++
1 | 2015-03-16 08:15:00-07 | 2015-03-16 09:35:00-07 | 8
3 | 2015-03-16 08:00:00-07 | 2015-03-16 11:45:00-07 | 8
1 | 2015-03-16 08:15:00-07 | 2015-03-16 09:35:00-07 | 9
2 | 2015-03-16 09:15:00-07 | 2015-03-16 11:05:00-07 | 9
3 | 2015-03-16 08:00:00-07 | 2015-03-16 11:45:00-07 | 9
2 | 2015-03-16 09:15:00-07 | 2015-03-16 11:05:00-07 | 10
3 | 2015-03-16 08:00:00-07 | 2015
Re: [GENERAL] Group by range in hour of day
On Mar 17, 2015, at 8:09 AM, Paul Jungwirth p...@illuminatedcomputing.com wrote: test= select h, count(*) from start_end, generate_series(0, 23) as s(h) where h between extract(hour from start_time) and extract(hour from end_time) group by h order by h; h | count +--- 8 | 2 9 | 3 10 | 2 11 | 2 Note if you always want all 24 rows with a count of 0 when appropriate (which seems common in reports with tables or plots), you can just tweak the above query to use a left join: FROM generate_series(0, 23) AS s(h) LEFT OUTER JOIN start_end ON h BETWEEN ... Paul Right, thanks. That makes sense. So next question: how do I get the active time per hour from this? To use the same example that came up with this result set: Given this: test= select * from start_end ; id | start_time |end_time ++ 1 | 2015-03-16 08:15:00-07 | 2015-03-16 09:35:00-07 2 | 2015-03-16 09:15:00-07 | 2015-03-16 11:05:00-07 3 | 2015-03-16 08:00:00-07 | 2015-03-16 11:45:00-07 The specified query returns this: h | count +--- 8 | 2 9 | 3 10 | 2 11 | 2 Which is an excellent start, but I also need one more column, which is the total active time per hour. So given the intermediate result of this: id | start_time |end_time| h +++ 1 | 2015-03-16 08:15:00-07 | 2015-03-16 09:35:00-07 | 8 3 | 2015-03-16 08:00:00-07 | 2015-03-16 11:45:00-07 | 8 1 | 2015-03-16 08:15:00-07 | 2015-03-16 09:35:00-07 | 9 2 | 2015-03-16 09:15:00-07 | 2015-03-16 11:05:00-07 | 9 3 | 2015-03-16 08:00:00-07 | 2015-03-16 11:45:00-07 | 9 2 | 2015-03-16 09:15:00-07 | 2015-03-16 11:05:00-07 | 10 3 | 2015-03-16 08:00:00-07 | 2015-03-16 11:45:00-07 | 10 2 | 2015-03-16 09:15:00-07 | 2015-03-16 11:05:00-07 | 11 3 | 2015-03-16 08:00:00-07 | 2015-03-16 11:45:00-07 | 11 I'd want a final result of this: h | count | sum +--- 8 | 2| 1.75 (or 1:45:00 or whatever) 9 | 3 | 2.33 (2:20:00) 10 | 2 | 2.00 (2:00:00) 11 | 2 | 0.83 (0:50:00) Where the 1:45 in the 8 hour is based on 45 minutes from row id 1 [8:15-9:00) plus the full hour [08:00-9:00) from row id 3, the hour 9 value is based on the amount of rows 1,2 and 3 that fall within the 9 hour, etc. --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- -- 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
Re: [GENERAL] Group by range in hour of day
On Mar 16, 2015, at 2:22 PM, David G. Johnston david.g.johns...@gmail.com
wrote:
On Mon, Mar 16, 2015 at 3:13 PM, Adrian Klaver adrian.kla...@aklaver.com
mailto:adrian.kla...@aklaver.com wrote:
On 03/16/2015 02:57 PM, Israel Brewster wrote:
I have a table with two timestamp columns for the start time and end
time of each record (call them start and end).I'm trying to figure out
if there is a way to group these records by hour of day, that is the
record should be included in the group if the hour of the day for the
group falls anywhere in the range [start,end]. Obviously each record may
well fall into multiple groups under this scenario.
The goal here is to figure out, for each hour of the day, a) what is the
total number of active records for that hour, and b) what is the total
active time for those records during the hour, with an ultimate goal
of figuring out the average active time per record per hour.
So, for simplified example, if the table contained three records:
start | end
-
2015-03-15 08:15 | 2015-03-15 10:45
2015-03-15 09:30 | 2015-03-15 10:15
2015-03-15 10:30 | 2015-03-15 11:30
Then the results should break out something like this:
hour | count | sum
-
8 |1 | 0.75
9 |2 | 1.5
10 |3 | 1.5
11 |1 | 0.5
I can then easily manipulate these values to get my ultimate goal of the
average, which would of course always be less than or equal to 1. Is
this doable in postgress? Or would it be a better idea to simply pull
the raw data and post-process in code? Thanks.
Do not have an answer for you, but a question:
What version of Postgres are you on?
This will help determine what tools are available to work with.
The following will give you endpoints for your bounds. Version is important
since range types could be very useful in this situation - but you'd still
need to generate the bounds info regardless.
SELECT *
FROM
(SELECT * FROM generate_series('2015-03-15'::timestamptz,
'2015-03-16'::timestamptz, '1 hour'::interval) start (start_ts)) AS s
CROSS JOIN
(SELECT end_ts + '1 hour'::interval AS end_ts FROM
generate_series('2015-03-15'::timestamptz, '2015-03-16'::timestamptz, '1
hour'::interval) e (end_ts)) AS e
You would join this using an ON condition with an OR (start BETWEEN [...] OR
end BETWEEN [...]) - range logic will be better and you may want to adjust
the upper bound by negative 1 (nano-second?) to allow for easier = logic
if using BETWEEN.
Thanks, that is very helpful, but are you sure CROSS JOIN is what you wanted
here? using that, I get a 625 row result set where each row from the first
SELECT is paired up with EVERY row from the second select. I would think I
would want the first row of the first SELECT paired up with only the first row
of the second, second row of the first paired with the second row of the
second, etc - i.e. 24 start and end bounds. Or am I missing something?
---
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
---
David J.
[GENERAL] Group by range in hour of day
I have a table with two timestamp columns for the start time and end time of each record (call them start and end).I'm trying to figure out if there is a way to group these records by "hour of day", that is the record should be included in the group if the hour of the day for the group falls anywhere in the range [start,end]. Obviously each record may well fall into multiple groups under this scenario.The goal here is to figure out, for each hour of the day, a) what is the total number of "active" records for that hour, and b) what is the total "active" time for those records during the hour, with an ultimate goal of figuring out the average active time per record per hour.So, for simplified example, if the table contained three records:start |end-2015-03-15 08:15 | 2015-03-15 10:452015-03-15 09:30 | 2015-03-15 10:152015-03-15 10:30 | 2015-03-15 11:30Then the results should break out something like this:hour | count | sum-8| 1| 0.759| 2| 1.510 | 3| 1.511 | 1| 0.5I can then easily manipulate these values to get my ultimate goal of the average, which would of course always be less than or equal to 1. Is this doable in postgress? Or would it be a better idea to simply pull the raw data and post-process in code? Thanks. ---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
Re: [GENERAL] Group by range in hour of day
On Mar 16, 2015, at 2:16 PM, Paul Jungwirth p...@illuminatedcomputing.com wrote: I have a table with two timestamp columns for the start time and end time of each record (call them start and end).I'm trying to figure out if there is a way to group these records by hour of day, I think you can do this by selecting `FROM generate_series(0, 23) s(h)` and then joining to your table based on `h BETWEEN start AND end`. Whenever I need to write a time-series aggregate query I reach for generate_series. Mostly that's so I have output rows even when COUNT(*) would be 0, but here it also means that a row from your data can feed into multiple output rows. I could probably write this out in more detail if you like, but that's the short version. :-) I think I can work with that :-) Hadn't considered doing a join there, so that's a new approach I can investigate. Thanks! Good luck! Paul -- 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
Re: [GENERAL] Group by range in hour of day
On Mar 16, 2015, at 2:13 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 03/16/2015 02:57 PM, Israel Brewster wrote: I have a table with two timestamp columns for the start time and end time of each record (call them start and end).I'm trying to figure out if there is a way to group these records by hour of day, that is the record should be included in the group if the hour of the day for the group falls anywhere in the range [start,end]. Obviously each record may well fall into multiple groups under this scenario. The goal here is to figure out, for each hour of the day, a) what is the total number of active records for that hour, and b) what is the total active time for those records during the hour, with an ultimate goal of figuring out the average active time per record per hour. So, for simplified example, if the table contained three records: start | end - 2015-03-15 08:15 | 2015-03-15 10:45 2015-03-15 09:30 | 2015-03-15 10:15 2015-03-15 10:30 | 2015-03-15 11:30 Then the results should break out something like this: hour | count | sum - 8 |1 | 0.75 9 |2 | 1.5 10 |3 | 1.5 11 |1 | 0.5 I can then easily manipulate these values to get my ultimate goal of the average, which would of course always be less than or equal to 1. Is this doable in postgress? Or would it be a better idea to simply pull the raw data and post-process in code? Thanks. Do not have an answer for you, but a question: What version of Postgres are you on? This will help determine what tools are available to work with. Oh, right. Of course. I'm on 9.4.0 --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- -- Adrian Klaver adrian.kla...@aklaver.com -- 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] Server statistics monitoring?
On Jan 29, 2015, at 1:20 PM, Michael Heaney mhea...@jcvi.org wrote: On 1/29/2015 5:12 PM, Israel Brewster wrote: I'm working on setting up a new PostgreSQL database server, and would like to be able to monitor a number of statistics on it, such as: number of connections number of queries query times etc. All these stats are easily available, either from the pg_stat_statements view (which I have enabled) and the like, or the log file (turn on log connects/disconnects), and I figured there would be a plethora of options available for monitoring these things. However, in searching around so far all I've found are a couple of hosted solutions. Granted, they look fairly nice, but we'd much prefer to keep this in-house. Paid solutions are fine, as long as the cost is reasonable, but we do want full-control. Before I spend my time writing my own solution, is there anything out there that can make nice graphs of various postgresql metrics? Thanks. Take a look at PoWA: http://dalibo.github.io/powa/ I've downloaded but haven't installed it yet, so can't give you any feedback on its performance or usability. Thanks. I'll give it a shot. --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- -- Michael Heaney JCVI
[GENERAL] Server statistics monitoring?
I'm working on setting up a new PostgreSQL database server, and would like to be able to monitor a number of statistics on it, such as:number of connectionsnumber of queriesquery timesetc.All these stats are easily available, either from the pg_stat_statements view (which I have enabled) and the like, or the log file (turn on log connects/disconnects), and I figured there would be a plethora of options available for monitoring these things. However, in searching around so far all I've found are a couple of hosted solutions. Granted, they look fairly nice, but we'd much prefer to keep this in-house. Paid solutions are fine, as long as the cost is reasonable, but we do want full-control. Before I spend my time writing my own solution, is there anything out there that can make nice graphs of various postgresql metrics? Thanks. ---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
[GENERAL] Stored procedure workflow question
Currently, when I need to create/edit a stored procedure in Postgresql, my workflow goes like the following:- Create/edit the desired function in my "DB Commands" text file- Copy and paste function into my development database- Test- repeat above until it works as desired- Copy and paste function into my production DB.To edit an existing function, the workflow is basically the same as above, but I first have to find the function in my file.This whole workflow just feels kludgy to me. Is there a better way? Or is that essentially the recommended procedure? Thanks. ---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
Re: [GENERAL] Yosemite (OSX 10.0) problems with Postgresql
On Oct 20, 2014, at 4:26 AM, Jerry Levan jerry.le...@gmail.com wrote: 2) SystemStarter is no longer available. Gulp… I am not a very good plist creator. Is there a fairly generic plist I can edit to specify my locations of the software bits so I can have postgresql started at boot time? I'm not familiar with SystemStarter, but here is the plist file that I've been using on several of my machines to start my own copy of postgres. Modified from the copy that comes with apple's server.app. This file goes in /Library/LaunchDaemons, and then can be loaded with the command sudo launchctl load -w /Library/LaunchDaemons/whatever you named the file.plist substitute load with unload to stop postgres. ?xml version=1.0 encoding=UTF-8? !DOCTYPE plist PUBLIC -//Apple Computer//DTD PLIST 1.0//EN http://www.apple.com/DTDs/PropertyList-1.0.dtd; plist version=1.0 dict keyLabel/key stringorg.postgresql.postgres-mine/string keyProgramArguments/key array string/usr/local/pgsql/bin/postmaster/string string-D/string string/usr/local/pgsql/data/string string-h/string string*/string /array keyRunAtLoad/key true/ keyUserName/key string_postgres/string /dict /plist --- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- Thanks, Jerry -- 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
Re: [GENERAL] Processor usage/tuning question
--- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 --- BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD On Oct 10, 2014, at 1:04 PM, Jim Nasby jim.na...@bluetreble.com wrote: On 10/8/14, 3:17 PM, Israel Brewster wrote: Except that the last data point received is still valid as the aircraft's current location, even if it came in several hours ago - which we may well care about. That is, some users may need to see where a given aircraft (or the entire fleet) is, even if an aircraft hasn't updated in a while. That said, I did discuss this with my higher-ups, and got the ok to take it down to four hours. Note that in your explain output nothing is filtering by time at all; are you sure you posted the right explain? I don't think PostgreSQL is going to be able to reason very effectively about a ROW_NUMBER() in a inner table and then a row=5 in the outer one being equivalent to a LIMIT query for which it could walk an index and then stopping once it finds 5 of them. Does this need to issued as a single query? Why not issue 55 different queries? It seems like the client is likely going to need to pick the returned list back out by tail number anyway, so both the client and the server might be happier with separate queries. Good point. Doing that brought the entire execution time down to around 60ms. Just ever so slightly better than the ~1200ms I was getting before. :-) I just have an unreasonable aversion to making multiple queries. I guess this is a prime example of why I need to overcome that :-) Do you actually need the last 5 points? If you could get away with just the most recent point, SELECT DISTINCT ON might do a better job of this in a single query. At the moment, unfortunately yes - I have to do some calculations based on the past few data points. At some point I should be able to re-work the system such that said calculations are done when the points are saved, rather than when they are retrieved, which would be beneficial for a number of reasons. However, until I can get that done I need multiple points here. As for the concern about issuing multiple queries, if you code this into a database function it should still be quite fast because there won't be any round-trip between your application and the database. I've had phenomenally bad luck with coding queries into database functions. I had a number of functions written at one point that allowed me to do things like select table.function, other_column FROM table - until I noticed that said queries ran significantly slower than just doing the query I had encoded in the function as a sub-query instead. I was doing these same sub-queries in a bunch of different places, so I figured it would clarify things if I could just code them into a DB function that I called just like a column. It's been a while since I looked at those, however, so I can't say why they were slow. This usage may not suffer from the same problem. Something else to consider is having a second table that only keeps the last X aircraft positions. I would do this by duplicating every insert into that table via a trigger, and then have a separate process that ran once a minute to delete any records other than the newest X. Because that table would always be tiny queries against it should be blazing fast. Do note that you'll want to vacuum that table frequently, like right after each delete. Indeed. I think I'm happy with the performance of the multiple queries, but this would doubtless be the best option (from a performance standpoint), as the table would be small and my select would be essentially SELECT * FROM TABLE, with a potential WHERE ... IN... clause. Thanks for all the help! -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com -- 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] Sync production DB with development?
I am currently doing periodic syncs of one of my production databases to my development database using the command pg_dump -ch production host database name | psql database name, run on my development server. This works well enough, but as the size of the production database grows, this command is, for obvious reasons, getting progressively slower (a full sync like this currently takes about 35 seconds). Is there a better way? Something that will only transfer records that are different on the production server, like rsync does for files perhaps?My main concern here is the time it takes to sync, given that the database size will only continue growing as time passes (unless I start implementing an archive at some point). The current database has two years worth of records. I would assume that the time the sync takes would grow roughly linearly with the number of records, so I could easily be over a minute of sync time in another two years. I would really rather not have to wait several minutes every time I want to update my development data. ---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
Re: [GENERAL] Processor usage/tuning question
---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD
VERSION:3.0
N:Brewster;Israel;;;
FN:Israel Brewster
ORG:Frontier Flying Service;MIS
TITLE:PC Support Tech II
EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com
TEL;type=WORK;type=pref:907-450-7293
item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701;
item1.X-ABADR:us
CATEGORIES:General
X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson
END:VCARD
On Oct 8, 2014, at 11:34 AM, Jeff Janes jeff.ja...@gmail.com wrote:On Tue, Oct 7, 2014 at 12:06 PM, israel isr...@eraalaska.net wrote:
Thank you all for the advice. It looks like the load is due to a query that is taking around 1300ms to complete - a query that is run by every client connected (probably half a dozen or so, although I don't have specific numbers), every fifteen seconds or so. As you can imagine, that keeps the server rather busy :-) Specifically, it looks like the time is due to a sort (PARTITION BY tail ORDER BY pointtime DESC) that operates on around 100,000 rows.
The lovely details:
The query in question is the following:
SELECT *
FROM (SELECT tail, to_char(pointtime,'MM/DD/ HH24:MI:SS'), lat,lng,altitude,heading,speed,source,pointtime,
ROW_NUMBER() OVER (PARTITION BY tail ORDER BY pointtime DESC) as row
FROM data
WHERE tail in (list of about 55 values or so) and pointtime='timestamp of 24 hours prior to current UTC time'::timestamp) s1
WHERE s1.row=5
ORDER BY tail, pointtime DESC
In english, it boils down to get the five most recent data points for each listed tail number. I look at the last 24 hours of data because it is quite possible that a tail number may have no recent data points.How many different tail numbers do you have in the last 24 hours? Based on the numbers you provide, it sounds like the list of 55 tail numbers is pretty much all of them that it could expect to find anyway.In this case yes - that 55 or so is everything (worst case, but fairly common scenario). The system is set up such that the user can select which tail number(s) they want to see, thus the tail in list construct. It's just that they often select all tail numbers.
One obvious optimization is to look at a smaller time range. This will definitely speed up the query, but at the risk of not getting any data points for one or more of the requested tail numbers (there is already this risk, but looking back 24 hours keeps it fairly small for us).But then, do you care? If you haven't heard from an airplane in 24 hours, it seems like either you don't care, or you care very very much and don't need the database to remind you.Except that the last data point received is still valid as the aircraft's current location, even if it came in several hours ago - which we may well care about. That is, some users may need to see where a given aircraft (or the entire fleet) is, even if an aircraft hasn't updated in a while. That said, I did discuss this with my higher-ups, and got the ok to take it down to four hours.
The table description:
tracking=# \d data
Table "public.data"
Column | Type | Modifiers
---+-+---
id| bigint | not null default nextval('data_id_seq'::regclass)
tail | character varying(16) | not null
timerecp | timestamp without time zone | not null default now()
altitude | integer |
pointtime | timestamp without time zone |
lat | numeric(7,5)| not null
lng | numeric(8,5)| not null
speed | integer |
heading | integer |
source | character varying(64) |
syncd | boolean | default false
Indexes:
"data_pkey" PRIMARY KEY, btree (id)
"pointtime_idx" btree (pointtime)
"syncd_idx" btree (syncd)
"tail_idx" btree (tail)
"tailtime_idx" btree (tail, pointtime DESC)
"timerecp_idx" btree (timerecp)
tracking=#
Adding the two-column sorted index didn't seem to affect the query time much.I don't think PostgreSQL is going to be able to reason very effectively about a ROW_NUMBER() in a inner table and then a row=5 in the outer one being equivalent to a LIMIT query for which it could walk an index and then stopping once it finds 5 of them.Does this need to issued as a single query? Why not issue 55 different queries? It seems like the client is likely going to need to pick the returned list back out by tail number anyway, so both the client and the server might be happier with separate queries.Good point. Doing that brought the entire execution time down to around 60ms. Just ever so slightly better than the ~1200ms I was getting before. :-) I just have an unreasonable aversion to making multiple queries. I guess this is a prime example of why I need to overcome that :-)
The table current contains 1303951 rows, and
Re: [GENERAL] Sync production DB with development?
On Oct 8, 2014, at 9:30 AM, Emanuel Calvo emanuel.ca...@2ndquadrant.com wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA512 El 08/10/14 a las 14:01, Israel Brewster escibió: I am currently doing periodic syncs of one of my production databases to my development database using the command pg_dump -ch production host database name | psql database name, run on my development server. This works well enough, but as the size of the production database grows, this command is, for obvious reasons, getting progressively slower (a full sync like this currently takes about 35 seconds). Is there a better way? Something that will only transfer records that are different on the production server, like rsync does for files perhaps? You can setup a streaming server, however I wont' recommend to sync from a production server. No, that wouldn't work well, because I need full access to my development server, and I need to be able to NOT have it in sync while I am working on it. Usually there is no need to have *all* the data from prod to development. Both environments should be isolated for security reasons. Agreed. and no, I don't need all the data. But pg_dump doesn't give me an option to, say, only grab the last week of data. Other thing is to implement a QA server, streaming from the master or taking a nightly snapshot with pg_basebackup. I think it could be more than enough. A QA server is great, and nightly snapshots are probably fine for that, however it doesn't help with my development server, I don't think. Actually, doing pg_dump | psql could take more time than pg_basebackup. My main concern here is the time it takes to sync, given that the database size will only continue growing as time passes (unless I start implementing an archive at some point). The current database has two years worth of records. I would assume that the time the sync takes would grow roughly linearly with the number of records, so I could easily be over a minute of sync time in another two years. I would really rather not have to wait several minutes every time I want to update my development data. Which is the entire size of your production cluster? At the moment, only about 538MB. Which I realize isn't all that large in the grand scheme of databases. - -- - -- Emanuel Calvo http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services Bs. As., Argentina (GMT-3) -BEGIN PGP SIGNATURE- Version: GnuPG/MacGPG2 v2.0.18 (Darwin) Comment: GPGTools - http://gpgtools.org iQIcBAEBCgAGBQJUNXSfAAoJEIBeI/HMagHmUHwQALpvwm44MJnDPOIGiPInZNRB 7DghWZD/JY4HbO1sKFMneXTJuNAbnNgE3MVyPnuqBnji1X0GyyDb0/NHW7yee7kd 3g+/FcryUAdY0WLuiuezcvc92eMrQTqE0eVEkM8WhlwFdmog3vbln1BeOYdMN/MF mrOjCBZRPYbIHwk6+hGN+C/OvE4e6n91+kN+J00Ga3+oD+LZxUZTzjY2ywh5d7yd HYsu2V2S508IQVISfoOR/skWWIWGr43ouzyPauJ/lSu/H0Jgor9c0ZjBeQUu6hsh N6i48yY80VRDC1F5EoEg5NVzzaPcye7r3IgEp2Yx3dsvVw+sF71P/1dqJNg9K1I/ GplwEVn0Uq7X5ld8crMMqmzLdGCQXXl1CJlLa6g593SzhjWsqeC+Fj1bFJnspNB6 xrEolLC1r+ZG7DTIRr4fmomk702/X8NDJPd4FcJ8G5lOm3keJz3yygmrP1X6GVYt UPj4p+DnwlikQqDIbLAzSAKoj/pGyya2GtadUwqQkzLYGELTNU2UR5yVcwIr/Wi5 oMA5iNqxHwrZoPzMaFQzvK+e2IHQSe/IH1EVqrqtOy+FqQLvNgABtrBrEjcy3JLq 2jL0tA9yNuixncv6JBe8kFXJ7+gwpdrGG69YGSq74B1/IzoMsWo9L+eIv1x4YnBw xDGMjU/lhV7A9MagRZa6 =g73R -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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Processor usage/tuning question
I have a Postgresql 9.3.5 server running on CentOS 6.5. In looking at some stats today, I saw that it was handling about 4-5 transactions/second (according to theSELECT sum(xact_commit+xact_rollback) FROM pg_stat_database; query), and an instance of the postmaster process was consistently showing 40%-80% utilization to handle this. I didn't think anything of that (the machine has plenty of capacity) until I mentioned it to a friend of mine, who said that utilization level seemed high for that many transactions. So if that level of utilization IS high, what might I need to tune to bring it down to a more reasonable level?Some details:Processors: 2x4core 2.5 GHz XeonTotal Memory: 16GBHard Disk: SSD raid 10wa value from top is typically 0.0%, sometimes up to 0.1%The database consists (primary) of a single table with 5 indexes and 11 columns. The majority of transactions are probably single-row inserts (live location data from numerous aircraft). Current record count is1,282,706, and kept fairly static on a day-to-day basis by a cleanup routine that runs each night and deletes old records (if that makes a difference). This database is streamed to a secondary hot read-only spare using streaming replication. The replica is using less than 1% processor on average.I have followed the directions here:https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server however I'm sure there are nuances I missed, or values that could be better selected for my usage case.Some current postgres config values:shared_buffers: 4GBeffective_cache_size: 12GBNot sure what other information I should provide, so let me know what useful data I missed! ---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
Re: [GENERAL] Mysterious DB reset
PUBLIC; postgres-2014-03-07 04:40:03.317 AKST-0LOG: statement: REVOKE ALL ON SCHEMA public FROM postgres; postgres-2014-03-07 04:40:03.325 AKST-0LOG: statement: GRANT ALL ON SCHEMA public TO postgres; postgres-2014-03-07 04:40:03.333 AKST-0LOG: statement: GRANT ALL ON SCHEMA public TO PUBLIC; postgres-2014-03-07 04:40:03.342 AKST-0LOG: disconnection: session time: 0:00:00.935 user=postgres database=tracking host=[local] So that definitely answers what is happening: Every morning at 4:40, the tables get dropped an re-created. Wow. I KNOW I never wrote a script to do that! I'm sort of thinking I somehow have a pg_dump with a -c flag (or perhaps the file from such) that is being loaded in, but I still don't know how or why. Closer though. Thanks for the suggestions! --- Israel Brewster Computer Support Technician II Era Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x7293 --- BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD On Mar 6, 2014, at 10:34 AM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 03/06/2014 11:09 AM, Israel Brewster wrote: On Mar 6, 2014, at 10:03 AM, Adrian Klaver adrian.kla...@aklaver.com wrote: Are all the scripts running from one machine? If so, have you checked that the times are set correctly on the various machines? Three different machines (due to OS requirements), but yeah the times all appear to be correct. So it appears you will have to wait to see what the logging reports. Should have mentioned that you need to make sure you do something like pg_ctl reload on the Postgres server to get the postgressql.conf changes to take effect. Another thought. Might not be bad idea to grep your scripts for ALTER, SEQUENCE or any other relevant keywords. Just in case something slipped in you where not aware of. Make sense? Probably not the best setup, but then that's what happens when you figure out stuff for yourself rather than having formal training :-) I'm DEFINITELY open to suggestions :-) 'Makes sense' is context sensitive. It really depends on what you want to achieve. My procedure is to define the end result first and then work backwards from there. Good point. However, I was asking more in the general did I explain it well enough to be understood rather than in the is it a sensible setup aspect. The rest of the sentence was just me being insecure :-D I understood the basics of what you are doing. The details probably can wait pending the log information. Hey, all of us are beginners/learning in something. --- Israel Brewster Computer Support Technician II Era Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x7293 --- -- 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] Mysterious DB reset
On Mar 6, 2014, at 1:25 PM, Steve Crawford scrawf...@pinpointresearch.com wrote: On 03/06/2014 09:33 AM, Israel Brewster wrote: For starters, this happened again this morning (no data prior to 4:45 am and sequence reset), so whatever is going on appears to be reoccurring. Also, I forgot to mention if it is significant: this is running on slackware liunux 14.0 Also odd is that my cleanup script runs at 1am. I have records of there being new data in the database up to 3:51am, but the oldest record currently in the DB is from 4:45am (as specified by the default of now() on the column). So I know records were added after my delete command ran, but before this reset occurred. A shot in the dark... Have you searched /etc/crontab, root's crontab, PostgreSQL's crontab and the crontabs of any automatic scripts that connect. I'm not sure about Slackware but Red Hat and Centos run the cron.daily scripts at (wait for it...) just after 4am. Good shot - you nailed it! I found a rouge script in /etc/crontab.daily that ran the following line at 4:40am: /usr/local/pgsql/bin/pg_dump -h domain name of localhost -U tracking -cs | /usr/local/pgsql/bin/psql -U postgres tracking It must have been left over from before I got streaming replication up and working, when this box was still the backup server and not primary -i.e. the domain name in the first half wasn't for the local machine until I swapped machines. Apparently when you do a pg_dump with the -c flag from a server to itself, it does the clean before reading the data, resulting in a new empty database. Thanks again for all the suggestions! Some of the default daily scripts like logrotate can have side effects like restarting the service that writes to the log file being rotated. Cheers, Steve -- 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
Re: [GENERAL] Mysterious DB reset
--- Israel Brewster Computer Support Technician II Era Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x7293 --- BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD On Mar 7, 2014, at 12:07 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 03/07/2014 11:08 AM, Israel Brewster wrote: On Mar 6, 2014, at 1:25 PM, Steve Crawford scrawf...@pinpointresearch.com wrote: On 03/06/2014 09:33 AM, Israel Brewster wrote: For starters, this happened again this morning (no data prior to 4:45 am and sequence reset), so whatever is going on appears to be reoccurring. Also, I forgot to mention if it is significant: this is running on slackware liunux 14.0 Also odd is that my cleanup script runs at 1am. I have records of there being new data in the database up to 3:51am, but the oldest record currently in the DB is from 4:45am (as specified by the default of now() on the column). So I know records were added after my delete command ran, but before this reset occurred. A shot in the dark... Have you searched /etc/crontab, root's crontab, PostgreSQL's crontab and the crontabs of any automatic scripts that connect. I'm not sure about Slackware but Red Hat and Centos run the cron.daily scripts at (wait for it...) just after 4am. Good shot - you nailed it! I found a rouge script in /etc/crontab.daily that ran the following line at 4:40am: /usr/local/pgsql/bin/pg_dump -h domain name of localhost -U tracking -cs | /usr/local/pgsql/bin/psql -U postgres tracking It must have been left over from before I got streaming replication up and working, when this box was still the backup server and not primary -i.e. the domain name in the first half wasn't for the local machine until I swapped machines. Apparently when you do a pg_dump with the -c flag from a server to itself, it does the clean before reading the data, resulting in a new empty database. Thanks again for all the suggestions! No, -c just tells pg_dump to output clean commands and really only has meaning when you do a plain text dump as above. pg_dump does not clean the data from the running server on its own. What did the cleaning was immediately piping to output of the dump to psql. So basically you created a plain text dump file and fed it back to the server and the dump file included commands to clean out objects. If you had not used -c you would have gotten a bunch of duplicate something errors. Right, but I ended up with no data. So what I was getting at was that because I had the -c, which output clean commands, those clean commands were executed before pg_dump dumped the data. You are absolutely correct in saying that it was only executed at all because it was piped to psql, I wasn't claiming otherwise :-) However, the sequence of events clearly is: 1) pg_dump outputs clean commands. The pipe to psql runs these, deleting the tables/data/etc. 2) pg_dump outputs the rebuild commands, which are piped to psql, which rebuilds the tables/etc 3) pg_dump tries to dump the data, but there is none (other than the default starting data in the sequences) due to steps 1 and 2 already having been executed (by the pipe to psql) on the same database that pg_dump is running on. Sorry for not being clear :-) Some of the default daily scripts like logrotate can have side effects like restarting the service that writes to the log file being rotated. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Adrian Klaver adrian.kla...@aklaver.com -- 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
Re: [GENERAL] Mysterious DB reset
For starters, this happened again this morning (no data prior to 4:45 am and sequence reset), so whatever is going on appears to be reoccurring. Also, I forgot to mention if it is significant: this is running on slackware liunux 14.0 On Mar 5, 2014, at 1:00 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 03/05/2014 10:22 AM, Israel Brewster wrote: My first thought was Oh, I must have a typo in my cleanup routine, such that it is deleting all records rather than only those a week old, and it's just that no one has noticed until now. So I looked at that, but changing the delete to a select appeared to produce the proper results, in that no records were selected: Well it would, if the records only go back to 4 AM this morning. In other words if no records exist before 4 AM today, no records exist before 7 days ago also or am I missing something? If the delete is correct, you are absolutely right. My first theory, however, was that I made a typo, and the delete was deleting ALL records, in which case changing it to a select would select all records. As it did not, that seems to confirm the delete is correct, and therefore not the problem. A sequence is just a special table. So what does SELECT * from the sequence show? tracking= SELECT * FROM data_id_seq; sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---++-+--+-+---+-+-+---+--- data_id_seq | 1184 | 1 |1 | 9223372036854775807 | 1 | 1 | 16 | f | t Also odd is that my cleanup script runs at 1am. I have records of there being new data in the database up to 3:51am, but the oldest record currently in the DB is from 4:45am (as specified by the default of now() on the column). So I know records were added after my delete command ran, but before this reset occurred. I am not sure what you are calling the 'reset'? Did something happen between 3:51 AM and 4:45 AM? Yes: All my data was deleted and the sequence reset to 1. Also not sure why you call the 4:45 AM record the oldest, when you say you can identify records from 3:51 AM? As I mentioned, I archive the records to permanent storage. This archive process happens every hour (for various reasons). That is how I know we had records for 3:51 am: they exist in the permanent archive. However, they don't exist in the local database any more. So my question is, aside from someone going in and mucking about in the wee hours of the morning, what could possibly cause this behavior? What sort of event could cause all data to be deleted from the table, and the sequence to be reset? Especially while there is an active connection? Thanks for any ideas, however wild or off the wall :-) What is in the Postgres/system logs for the time period(s) you mention? The postgres log has a lot of errors in it, some of which MAY explain the issue. For example: cp: cannot create regular file '/mnt/pglogs/000100040094': Permission denied LOG: archive command failed with exit code 1 DETAIL: The failed archive command was: test ! -f /mnt/pglogs/000100040094 cp pg_xlog/000100040094 /mnt/pglogs/000100040094 WARNING: transaction log file 000100040094 could not be archived: too many failures LOG: received smart shutdown request LOG: autovacuum launcher shutting down LOG: shutting down LOG: database system is shut down However, there are no timestamps on any of the entries (can I fix that?), so I don't know if those are current entries, or from back before I got the mount for the logs working. At this time, the mount point IS working correctly, and from what I can tell so is the archive command. The latest entry is from yesterday (modify date on the log shows Mar 5, 9:21, when I was messing with it yesterday), however, so there are no entries from this morning when it happened again. I don't see anything of interest in the syslog or messages log. --- Israel Brewster Computer Support Technician II Era Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x7293 --- -- Adrian Klaver adrian.kla...@aklaver.com -- 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] Mysterious DB reset
On Mar 5, 2014, at 10:01 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Israel Brewster wrote: So my question is, aside from someone going in and mucking about in the wee hours of the morning, what could possibly cause this behavior? What sort of event could cause all data to be deleted from the table, and the sequence to be reset? Especially while there is an active connection? Thanks for any ideas, however wild or off the wall :-) Is this running off a NFS mount or something? I'm wondering about the filesystem getting unmounted and an empty copy of the database being in the mount point. Good thought - I hadn't thought of a file system level reset. Unfortunately the data folder is on the root partition, which is a standard SATA hard drive, and the date on the data folder shows February 27th, so apparently the folder itself at least hasn't been re-created. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general --- Israel Brewster Computer Support Technician II Era Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x7293 --- BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD -- 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] Mysterious DB reset
On Mar 5, 2014, at 10:01 AM, Thom Brown t...@linux.com wrote:On 5 March 2014 18:22, Israel Brewster isr...@eraalaska.net wrote: I have a Postgresql 9.2.4 database containing real-time tracking data for our aircraft for the past week (theoretically). It is populated by two different processes: one that runs every few minutes, retrieving data from a number of sources and storing it in the DB, and one that has an "always on" connection to the DB streaming data into the database in realtime (often several records per second). To keep the database size manageable I have a cron job that runs every evening to delete all records that are more than a week old, after archiving a subset of them in permanent storage. This morning my boss e-mailed me, complaining that only a couple of aircraft were showing up in the list (SELECT distinct(tail) FROM data being the command that populates the list). Upon looking at the data I saw that it only went back to 4am this morning, rather than the week I was expecting. My first thought was "Oh, I must have a typo in my cleanup routine, such that it is deleting all records rather than only those a week old, and it's just that no one has noticed until now". So I looked at that, but changing the delete to a select appeared to produce the proper results, in that no records were selected: DELETE FROM data WHERE pointtimenow() AT TIME ZONE 'UTC'-interval '7 days';Then I noticed something even more odd. My database has an id column, which is defined as a SERIAL. As we all know, a serial is a monotonically increasing number that is not affected by deletes. However, the oldest record in my database, from 4am this morning, had an id of 1. Even though I KNOW there was data in the system yesterday. Even if my DELETE command was wrong and deleted ALL records, that shouldn't have reset the SERIAL column to 1! I also know that I have not been in the database mucking around with the sequence value - to be completely honest, I don't even know the exact command to reset it - I'd have to google it if I wanted to. Also odd is that my cleanup script runs at 1am. I have records of there being new data in the database up to 3:51am, but the oldest record currently in the DB is from 4:45am (as specified by the default of now() on the column). So I know records were added after my delete command ran, but before this reset occurred. So my question is, aside from someone going in and mucking about in the wee hours of the morning, what could possibly cause this behavior? What sort of event could cause all data to be deleted from the table, and the sequence to be reset? Especially while there is an active connection? Thanks for any ideas, however wild or off the wall :-) That is odd. Even if it were an unlogged table, and there was a crash, the sequence wouldn't reset, and even if it was running in a very long-running transaction held open by a buggy connection pooler, the sequence would still progress as it's immune to the effects of transactions.Not famillar with a logged vs unlogged table (still learning all the features of PostgreSQL), but as you said the sequence resetting is rather odd. So if all the data went missing, and the sequence reset, the only thing I can think of is:Someone ran:TRUNCATE data RESTART IDENTITY;Considering that I'm the only one in the company that knows SQL at all beyond a simple single-table select (I keep having to explain joins and how they aren't evil to the other programmer here), not likely :-) or someone restored the table structure from a backup that deleted the original table.Now that's a thought...Maybe my backup routine is working backwards (pushing from the backup archive to the primary machine rather than from the primary to the backup). I did switch primary and secondary machines a while ago, but I thought I had checked that. What would be the effect of overwriting the data files while the database is active?Do you log DDL?Not sure what that is, so I'll assume no :-)Was the table partitioned?Nope.You should also really be on 9.2.7, although I can't think of any bug that's been fixed which could be responsible for this issue.I'll look at updating. Thanks. -- Thom ---Israel BrewsterComputer SupportTechnician IIEra Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7250 x7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
Re: [GENERAL] Mysterious DB reset
On Mar 6, 2014, at 9:03 AM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 03/06/2014 09:33 AM, Israel Brewster wrote: For starters, this happened again this morning (no data prior to 4:45 am and sequence reset), so whatever is going on appears to be reoccurring. Also, I forgot to mention if it is significant: this is running on slackware liunux 14.0 On Mar 5, 2014, at 1:00 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 03/05/2014 10:22 AM, Israel Brewster wrote: My first thought was Oh, I must have a typo in my cleanup routine, such that it is deleting all records rather than only those a week old, and it's just that no one has noticed until now. So I looked at that, but changing the delete to a select appeared to produce the proper results, in that no records were selected: Well it would, if the records only go back to 4 AM this morning. In other words if no records exist before 4 AM today, no records exist before 7 days ago also or am I missing something? If the delete is correct, you are absolutely right. My first theory, however, was that I made a typo, and the delete was deleting ALL records, in which case changing it to a select would select all records. As it did not, that seems to confirm the delete is correct, and therefore not the problem. A sequence is just a special table. So what does SELECT * from the sequence show? tracking= SELECT * FROM data_id_seq; sequence_name | last_value | start_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---++-+--+-+---+-+-+---+--- data_id_seq | 1184 | 1 |1 | 9223372036854775807 | 1 | 1 | 16 | f | t Also odd is that my cleanup script runs at 1am. I have records of there being new data in the database up to 3:51am, but the oldest record currently in the DB is from 4:45am (as specified by the default of now() on the column). So I know records were added after my delete command ran, but before this reset occurred. I am not sure what you are calling the 'reset'? Did something happen between 3:51 AM and 4:45 AM? Yes: All my data was deleted and the sequence reset to 1. Also not sure why you call the 4:45 AM record the oldest, when you say you can identify records from 3:51 AM? As I mentioned, I archive the records to permanent storage. This archive process happens every hour (for various reasons). That is how I know we had records for 3:51 am: they exist in the permanent archive. However, they don't exist in the local database any more. Well something is happening. See my notes on logging below to help track down the cause. Yep. So my question is, aside from someone going in and mucking about in the wee hours of the morning, what could possibly cause this behavior? What sort of event could cause all data to be deleted from the table, and the sequence to be reset? Especially while there is an active connection? Thanks for any ideas, however wild or off the wall :-) What is in the Postgres/system logs for the time period(s) you mention? The postgres log has a lot of errors in it, some of which MAY explain the issue. For example: cp: cannot create regular file '/mnt/pglogs/000100040094': Permission denied LOG: archive command failed with exit code 1 DETAIL: The failed archive command was: test ! -f /mnt/pglogs/000100040094 cp pg_xlog/000100040094 /mnt/pglogs/000100040094 WARNING: transaction log file 000100040094 could not be archived: too many failures LOG: received smart shutdown request LOG: autovacuum launcher shutting down LOG: shutting down LOG: database system is shut down Might be good to explain your archive setup. Ok, here goes: We have the primary system which receives the data and handles all requests for said data. There is also a hot standby server keep in sync with streaming replication. The WALs are archived to a NFS share on this machine. Once an hour a python script runs that a) Selects all unsynced records from the postgresql db, b) stores a subset of them in our permanent archive, and c) marks the previously selected records as synced (UPDATE data SET syncd=true WHERE id in (...) ) Additionally, I have a) a script that runs at 8:00pm every evening that uses pg_dump to dump the contents of the database to a backup file, and b) a script that runs at 8:00 each morning that rsync's various config files and scripts (such as my data retrieval scripts) from the primary machine to a backup location on the secondary machine. None of the scripts run anywhere near the apparent 4:40ish cutoff time for my data Make sense? Probably not the best setup, but then that's what happens when you figure out stuff
Re: [GENERAL] Mysterious DB reset
On Mar 6, 2014, at 10:03 AM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 03/06/2014 10:43 AM, Israel Brewster wrote: On Mar 6, 2014, at 9:03 AM, Adrian Klaver adrian.kla...@aklaver.com wrote: Well something is happening. See my notes on logging below to help track down the cause. Yep. Might be good to explain your archive setup. Ok, here goes: We have the primary system which receives the data and handles all requests for said data. There is also a hot standby server keep in sync with streaming replication. The WALs are archived to a NFS share on this machine. Once an hour a python script runs that a) Selects all unsynced records from the postgresql db, b) stores a subset of them in our permanent archive, and c) marks the previously selected records as synced (UPDATE data SET syncd=true WHERE id in (...) ) Additionally, I have a) a script that runs at 8:00pm every evening that uses pg_dump to dump the contents of the database to a backup file, and b) a script that runs at 8:00 each morning that rsync's various config files and scripts (such as my data retrieval scripts) from the primary machine to a backup location on the secondary machine. None of the scripts run anywhere near the apparent 4:40ish cutoff time for my data Are all the scripts running from one machine? If so, have you checked that the times are set correctly on the various machines? Three different machines (due to OS requirements), but yeah the times all appear to be correct. Make sense? Probably not the best setup, but then that's what happens when you figure out stuff for yourself rather than having formal training :-) I'm DEFINITELY open to suggestions :-) 'Makes sense' is context sensitive. It really depends on what you want to achieve. My procedure is to define the end result first and then work backwards from there. Good point. However, I was asking more in the general did I explain it well enough to be understood rather than in the is it a sensible setup aspect. The rest of the sentence was just me being insecure :-D I'll get those in the config, and we'll see what happens tomorrow morning. Hopefully that will give more information. Thanks for the link and information! --- Israel Brewster Computer Support Technician II Era Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x7293 --- BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Mysterious DB reset
I have a Postgresql 9.2.4 database containing real-time tracking data for our aircraft for the past week (theoretically). It is populated by two different processes: one that runs every few minutes, retrieving data from a number of sources and storing it in the DB, and one that has an "always on" connection to the DB streaming data into the database in realtime (often several records per second). To keep the database size manageable I have a cron job that runs every evening to delete all records that are more than a week old, after archiving a subset of them in permanent storage.This morning my boss e-mailed me, complaining that only a couple of aircraft were showing up in the list (SELECT distinct(tail) FROM data being the command that populates the list). Upon looking at the data I saw that it only went back to 4am this morning, rather than the week I was expecting. My first thought was "Oh, I must have a typo in my cleanup routine, such that it is deleting all records rather than only those a week old, and it's just that no one has noticed until now". So I looked at that, but changing the delete to a select appeared to produce the proper results, in that no records were selected:DELETE FROM data WHERE pointtimenow() AT TIME ZONE 'UTC'-interval '7 days';Then I noticed something even more odd. My database has an id column, which is defined as a SERIAL. As we all know, a serial is a monotonically increasing number that is not affected by deletes. However, the oldest record in my database, from 4am this morning, had an id of 1. Even though I KNOW there was data in the system yesterday. Even if my DELETE command was wrong and deleted ALL records, that shouldn't have reset the SERIAL column to 1! I also know that I have not been in the database mucking around with the sequence value - to be completely honest, I don't even know the exact command to reset it - I'd have to google it if I wanted to.Also odd is that my cleanup script runs at 1am. I have records of there being new data in the database up to 3:51am, but the oldest record currently in the DB is from 4:45am (as specified by the default of now() on the column). So I know records were added after my delete command ran, but before this reset occurred.So my question is, aside from someone going in and mucking about in the wee hours of the morning, what could possibly cause this behavior? What sort of event could cause all data to be deleted from the table, and the sequence to be reset? Especially while there is an active connection? Thanks for any ideas, however wild or off the wall :-) ---Israel BrewsterComputer SupportTechnician IIEra Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7250 x7293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
Re: [GENERAL] cross-database time extract?
On Dec 29, 2009, at 5:41 AM, Adrian Klaver wrote: On Monday 28 December 2009 8:58:38 am Israel Brewster wrote: On Dec 24, 2009, at 12:53 PM, Adrian Klaver wrote: On Thursday 24 December 2009 1:44:58 pm Israel Brewster wrote: This is sort of a PostgreSQL question/sort of a general SQL question, so I apologize if this isn't the best place to ask. At any rate, I know in PostgreSQL you can issue a command like 'SELECT time(timestamp_column) from table_name' ... --- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- select cast(timestamp_column as time) from table_name Didn't realize you could do that- thanks. This does work, sort of... In PosgreSQL, it returns the time portion of the timestamp as desired. However, in SQLite, while the function runs, it returns the year portion of the timestamp, rather than the time. That would seem to be a SQLite issue/question however. Thanks for the suggestion. -- Adrian Klaver akla...@comcast.net It would seem that the best solution is your original one of SELECT time(timestamp_field). This works in the three databases you mentioned with the provision that you have to double quote time in Postgres. Agreed. It's fairly easy to add the quotes when needed, after which everything works as desired. Thanks for all the feedback and explanations! -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general --- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD -- 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] cross-database time extract?
On Dec 24, 2009, at 12:53 PM, Adrian Klaver wrote: On Thursday 24 December 2009 1:44:58 pm Israel Brewster wrote: This is sort of a PostgreSQL question/sort of a general SQL question, so I apologize if this isn't the best place to ask. At any rate, I know in PostgreSQL you can issue a command like 'SELECT time(timestamp_column) from table_name' to get the time part of a timestamp. The problem is that this command for some reason requires quotes around the time function name, which breaks the command when used in SQLite (I don't know about MySQL yet, but I suspect the same would be true there). The program I am working on is designed to work with all three types of databases (SQLite, PostgreSQL, and MySQL) so it would be nice (save me some programing) if there was a single SQL statement to get the time portion of a timestamp that would work with all three. Is there such a beast? On a related note, why do we need the quotes around time for the function to work in PostgreSQL? the date function doesn't need them, so I know it's not just a general PostgreSQL formating difference. Thanks :) --- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- select cast(timestamp_column as time) from table_name Didn't realize you could do that- thanks. This does work, sort of... In PosgreSQL, it returns the time portion of the timestamp as desired. However, in SQLite, while the function runs, it returns the year portion of the timestamp, rather than the time. That would seem to be a SQLite issue/question however. Thanks for the suggestion. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general --- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD -- 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] defining yuor own commands in PG ?
On Dec 21, 2009, at 9:34 AM, Pavel Stehule wrote: 2009/12/21 Israel Brewster isr...@frontierflying.com: On Dec 19, 2009, at 2:59 AM, Filip Rembiałkowski wrote: 2009/12/18 Gauthier, Dave dave.gauth...@intel.com Can you define your own commands in PG. In psql, yes: \set sel 'SELECT * FROM' :sel clients; \set desc '\\d' :desc table E.g., if users from other DBs use “describe foo” to get the metadata for foo, is there a way I can create a command “describe” to = “\d” ? But what's wrong with \d? For me, its like the first thing people learn when practicing postgres. It is even faster to type than DESCRIBE, right? Just to put in my 2¢, I write a program that can use a MySQL, PostgreSQL, or SQLite database as its backend, depending on user preference. As a result, I spend time in all three databases each day. It can get a bit confusing at times trying to remember ok, in this terminal window I need to do a 'describe foo', in that one I need to do a '\d foo' and in the third '.schema foo'. So being able to simplify that at least somewhat is nice :) That said, I'd be just as happy getting MySQL to recognize the \d command as I would be getting postgres to recognize describe-perhaps more so. do you know gnome-db console? That looks very handy. Of course, I'm using a Mac, no linux, so it may not work for me, but I'll have to see if I can get it to compile and run for me. Thanks for the pointer! http://blogs.gnome.org/vivien/2007/10/25/sql-console/ http://library.gnome.org/devel/libgda/unstable/gda-sql-manual-run.html regards Pavel IMO, when you will bend postgres to mimick other DBMS, you will hide its real power from users. -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/ --- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general --- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] cross-database time extract?
This is sort of a PostgreSQL question/sort of a general SQL question, so I apologize if this isn't the best place to ask. At any rate, I know in PostgreSQL you can issue a command like 'SELECT "time"(timestamp_column) from table_name' to get the time part of a timestamp. The problem is that this command for some reason requires quotes around the "time" function name, which breaks the command when used in SQLite (I don't know about MySQL yet, but I suspect the same would be true there). The program I am working on is designed to work with all three types of databases (SQLite, PostgreSQL, and MySQL) so it would be nice (save me some programing) if there was a single SQL statement to get the time portion of a timestamp that would work with all three. Is there such a beast? On a related note, why do we need the quotes around "time" for the function to work in PostgreSQL? the date function doesn't need them, so I know it's not just a general PostgreSQL formating difference. Thanks :) ---Israel BrewsterComputer SupportTechnician IIFrontier Flying Service Inc.5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7250 x293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
Re: [GENERAL] defining yuor own commands in PG ?
On Dec 19, 2009, at 2:59 AM, Filip Rembiałkowski wrote:2009/12/18 Gauthier, Dave dave.gauth...@intel.com Can you define your own commands in PG. In psql, yes:\set sel 'SELECT * FROM':sel clients;\set desc '\\d' :desc table E.g., if users from other DBs use “describe foo” to get the metadata for foo, is there a way I can create a command “describe” to = “\d” ? But what's wrong with "\d"? For me, its like the first thing people learn when practicing postgres. It is even faster to type than DESCRIBE, right?Just to put in my 2¢, I write a program that can use a MySQL, PostgreSQL, or SQLite database as its backend, depending on user preference. As a result, I spend time in all three databases each day. It can get a bit confusing at times trying to remember "ok, in this terminal window I need to do a 'describe foo', in that one I need to do a '\d foo' and in the third '.schema foo'". So being able to simplify that at least somewhat is nice :) That said, I'd be just as happy getting MySQL to recognize the \d command as I would be getting postgres to recognize describe-perhaps more so.IMO, when you will bend postgres to mimick other DBMS, you will hide its real power from users.-- Filip RembiałkowskiJID,mailto:filip.rembialkow...@gmail.comhttp://filip.rembialkowski.net/ ---Israel BrewsterComputer SupportTechnician IIFrontier Flying Service Inc.5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7250 x293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
Re: [GENERAL] Build universal binary on Mac OS X 10.6?
On Dec 2, 2009, at 5:16 PM, Tom Lane wrote: Israel Brewster isr...@frontierflying.com writes: Well, I'm not trying to use the server or client programs from this build - I just want the universal libraries for my programs. My point in this last section, however, doesn't necessarily extend as far as actual function, but rather is just with the build. MySQL and SQLite build for multiple architectures quite happily, Postgres doesn't build at all except for single architectures (the way I am trying at least). Well, it's been done. On 10.5. By myself among others. Thus the reason I think there is something wrong about the way I am going about this, and thus my question to this list. If I didn't think it was possible, I wouldn't bother asking how to do it :-) Searching the PG archives for prior discussions I find http://archives.postgresql.org/pgsql-hackers/2008-07/msg00884.php which describes success with multiple -arch flags in CFLAGS plus hand creation of relevant .h files. Thanks for the link. It explains why I wasn't finding anything in my searches - I was searching for OS X, while the post references Darwin. I'll keep that distinction in mind in the future - may help me solve my problems on my own, without needing outside help :-) That said, the post hilights a few points: 1) From the second paragraph of that post: If you add something like -arch i386 -arch ppc to CFLAGS and build normally, you get real working multiarch binaries and libraries. Which is exactly the problem that started this whole thread - on 10.6, you DON'T (or at least I don't) get real working multiarch binaries and libraries. In fact, you don't get anything - the compile fails. Already we see that this post does not address my issue in any form, as it references a successful compile resulting in unusable binaries- not a failed compile, which is what I am dealing with. The post goes on to say that if you only do that, only the arch you build on will work, but as I have already pointed out, I don't care - I just need the libraries. My app already runs fine on multiple platforms, I just need the multi-arch libraries to link against. From a Mac OS X 10.6 build machine - I already have it working just fine on a 10.5 build machine (with no header hacking), albeit with Postgres 8.2. 2) The post explains quite elegantly my point about Postgres being more difficult to create a universal binary with than other programs. I have compiled Qt, MySQL, SQLite, and several of my own programs for all four architectures, and never once needed to hack around with header files to get it to work. See, for example, this: http://www.malisphoto.com/tips/mysql-on-os-x.html#Anchor-Build . A Single configure command, with the right arguments, creates a 4- way universal binary of MySQL on Mac OS X. That said, this really isn't an issue for me - I mean, it's not like I'm rebuilding Postgres every day or anything, so If I need to hack around with headers or whatever to get the build to work, that's fine. However, see point 1 - apparently the headers aren't the issue, because according to the posts you linked, incorrect headers result in non-functioning binaries, NOT in a failed build (it explicitly said the build itself worked). [snip] On the whole I'd still recommend building the reference .h files on the actual target arch rather than trusting cross-compile to create them correctly. If I was simply trying to build the Postgres server and/or client to run on my machines, I would agree. However, as that is not what I am doing (I'm not using the client or server portions of the build at all) this is simply not an option, for two reasons. First, it would require that I have four different machines to build on: ppc 32 and 64 bit, and Intel 32 and 64 bit. I could probably get away with two (ppc and intel 32 bit) but regardless it would still require multiple build machines, and that will never happen. Secondly, even if I had the machines, it wouldn't help me achieve what I need - a single, universal binary build of my application that can run on as many Mac OS X machines as possible. There is no way I am going to be distributing different binaries of my application for different machine architectures. That might be fine for your average Linux/Unix user, perhaps even for your average PosgreSQL database admin- but not for the general public to whom my app is targeted. Most of my target audience is going to go machine type? I dunno... it's a mac. So I need to distribute an application that will run on a mac regardless of what chip is inside. For that, I need a single, universal, build of the Postgres libraries. Doing multiple builds on multiple machines is, quite simply, not an option. I thank you for the time you have taken trying to explain this to me, and I apologize if I am being dense or stubborn. I'm not trying to be difficult
Re: [GENERAL] Build universal binary on Mac OS X 10.6?
On Dec 3, 2009, at 10:54 AM, Tom Lane wrote: Israel Brewster isr...@frontierflying.com writes: 1) From the second paragraph of that post: If you add something like -arch i386 -arch ppc to CFLAGS and build normally, you get real working multiarch binaries and libraries. Which is exactly the problem that started this whole thread - on 10.6, you DON'T (or at least I don't) get real working multiarch binaries and libraries. In fact, you don't get anything - the compile fails. Already we see that this post does not address my issue in any form, The reason it's failing is that you continue to ignore the important point: you need arch-specific header files. So it WAS just me being dense. Figures :P Your explanation makes perfect sense now, thanks. Although in my defense, everything that I read up till now implied that the compile should work, it would just be the generated binaries that wouldn't (how else do you interpret that quite I included?). However with your explanation, it makes sense why the compile would fail. Again, my apologies. So what it boils down to, if I now understand you correctly, is that (since you obviously can only have one set of headers per build) the only way to make this work is pretty much exactly what I ended up doing: build for each architecture separately (even if on the same machine) and then lipo the results together. I can live with that. Then the reason this is necessary on Postgres, and not with other software I have dealt with is that Postgres has 64 bit specific code, while the others don't? I know my code doesn't. Or maybe I was just doing 32 bit builds of the others, and so never ran into this sort of thing. Hmm. Oh well, it works. My profuse thanks for the explanations and bearing with me. I don't really recall whether I hit this in the experiment I did last year. It's possible, maybe even likely, that the code was different then and happened not to have any compiler-visible inconsistencies when the header was wrong for the target arch. Or perhaps the 10.6 compiler has better error checking than the 10.5? That would explain why the build would succeed on 10.5 (but give unusable binaries for other platforms) but die on 10.6. That doesn't change the fact that it'd fail at runtime whether the compiler could detect a problem or not. Well, the libraries I created in the past from the 8.2 code work just fine on both PPC 32 bit and Intel 64 bit. But then, that was 8.2. The code probably changed between 8.2 and 8.4 though :-D. Thanks again! There's really no way around building the correct header files if you want a usable multiarch library. regards, tom lane --- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD -- 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] Build universal binary on Mac OS X 10.6?
On Dec 2, 2009, at 1:03 AM, Dave Page wrote: 2009/12/2 Grzegorz Jaśkiewicz gryz...@gmail.com: On Tue, Dec 1, 2009 at 11:43 PM, Israel Brewster isr...@frontierflying.com wrote: I am trying to create a static, universal (x86 and ppc at least - 64 bit architectures are optional) build of the PostgreSQL libraries on a Mac OS X 10.6 machine. The command line I am using is as follows: 10.6 is intel only, so why do you even bother with PPC ? 10.6 supports building of PPC binaries (in theory at least) for use on older OSs, Exactly. If I was building for my own personal use, I wouldn't bother with PPC, or even i386 - i'd just go with x86_64. However, as I am developing software for wide distribution, I want it to be able to run on as many machines/OS's as possible. That said, I did sort of get this to work. What I ended up doing was building for each architecture separately (but on the same machine), then using lipo to combine the resulting libraries. When I took all but one architecture flag out of the configure string I used, it worked- regardless of which architecture I left in. I haven't had a chance to test this fully yet, but so far it seems to have worked - lipo reports all three architectures (I didn't bother with ppc64), and in the initial compile phase at least Qt is happy (still dealing with other unrelated compile issues there). Apparently PostgreSQL will happily build for any one of the platforms under Mac OS X 10.6, but trying to build for multiple platforms at once gives it heartburn. Given that, I tend to think there was something wrong with the way I was trying to do it - something missing/wrong from the CFLAGS or LDFLAGS or the like. I'm somewhat curious though. I didn't have any difficulties making universal builds of MySQL and SQLite by simply passing multiple -arch flags to CFLAGS and LDFLAGS. It makes me wonder what PostgreSQL is doing differently that causes problems? Thanks for the feedback and advice! -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general --- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD -- 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] Build universal binary on Mac OS X 10.6?
On Dec 2, 2009, at 8:26 AM, Tom Lane wrote: Israel Brewster isr...@frontierflying.com writes: That said, I did sort of get this to work. What I ended up doing was building for each architecture separately (but on the same machine), then using lipo to combine the resulting libraries. When I took all but one architecture flag out of the configure string I used, it worked- regardless of which architecture I left in. I haven't had a chance to test this fully yet, but so far it seems to have worked - The server executables will probably not work, except on the arch you built on. The client programs might accidentally fail to fail; I'm not sure whether they contain any dependencies on the arch- specific values that are extracted by configure. You really need to create pg_config.h contents that are correct for the specific arch you're trying to compile for. The last time I tried this, the only good way to do that was by running configure on the particular architecture. (Maybe 10.6 has got some cute way around that, but I doubt it.) I'm somewhat curious though. I didn't have any difficulties making universal builds of MySQL and SQLite by simply passing multiple -arch flags to CFLAGS and LDFLAGS. Can't speak to SQLite, but I know quite well that mysql has got essentially the same issues as PG with having arch-specific configure output. Have you actually tested those universal builds on any arch except where you built them? Well, I'm not trying to use the server or client programs from this build - I just want the universal libraries for my programs. My point in this last section, however, doesn't necessarily extend as far as actual function, but rather is just with the build. MySQL and SQLite build for multiple architectures quite happily, Postgres doesn't build at all except for single architectures (the way I am trying at least). Granted, it's entirely possible that the server/client built by MySQL doesn't work on other platforms, but the build does - thus my curiosity. FWIW, I have tested a program of mine linked against the multi- architecture builds of the MySQL and SQLite libraries on other platforms, and they do work. But then, I didn't need to do any fancy work with multiple builds and lipo with them. Actually, back when I was on Mac OS 10.5 I managed to get a universal build of Postgres 8.2 that worked - there's an old thread of mine from when I was having problems with that as well. But I'm trying to get updated here, and seem to be running into all new problems :-) regards, tom lane --- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Build universal binary on Mac OS X 10.6?
I am trying to create a static, universal (x86 and ppc at least - 64 bit architectures are optional) build of the PostgreSQL libraries on a Mac OS X 10.6 machine. The command line I am using is as follows:CFLAGS="-arch i386 -arch ppc -arch x86_64" ./configure --disable-sharedThe configure runs fine, as does the build until it gets to the following error:gcc -no-cpp-precomp -arch i386 -arch ppc -arch x86_64 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -I../../../../src/include -c -o heaptuple.o heaptuple.cheaptuple.c: In function 'heap_fill_tuple':heaptuple.c:196: error: duplicate case valueheaptuple.c:196: error: previously used hereheaptuple.c: In function 'heap_fill_tuple':heaptuple.c:196: error: duplicate case valueheaptuple.c:196: error: previously used herelipo: can't figure out the architecture type of: /var/tmp//ccKn3btt.outmake[4]: *** [heaptuple.o] Error 1make[3]: *** [common-recursive] Error 2make[2]: *** [access-recursive] Error 2make[1]: *** [all] Error 2make: *** [all] Error 2I have tried both with PostgreSQL version 8.4.1 and 8.3.8 with the same result. I really just need the library for my apps to link against, I don't care if the server or client programs compile. What am I doing wrong here? How can I get this to work? Thanks. ---Israel BrewsterComputer SupportTechnician IIFrontier Flying Service Inc.5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7250 x293---BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701; item1.X-ABADR:us CATEGORIES:General X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson END:VCARD
