Steve Crawford

I think it would be helpful to know *why* you do this (backup,
forensics, development, shifting load among virtual machines or ???) as
well as a better idea of how often you do this, how long it takes and
the amount of downtime you consider acceptable. That way we may be
better able to come up with a way of solving the actual problem you are
facing.

There are a number of non-core replication solutions such as Slony and
many can migrate a single database or even specific table(s).

Cheers,
Steve

--
Sent via pgsql-admin mailing list (pgsql-admin*******)
To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin

Guess I needed to wait for the coffee to absorb. I've come up with an
initial working solution (perhaps excess use of CTE but it's useful for
testing/debugging over different portions of collected data):

with

report_time as (
select
     1365526800::int as list_end
),

report_ranges as (
select
     extract(epoch from date_trunc('day', abstime(list_end)))::int as
day_start,
     greatest(list_end-3600, extract(epoch from date_trunc('day',
abstime(list_end)))::int) as list_start,
     list_end
from
     report_time
),

today_events as (
select
     unit_id,
     event_time,
     status
from
     event_log d,
     report_ranges r
where
     d.event_time >= r.day_start and
     d.event_time <= r.list_end
),

unit_id_list as (
select
     distinct unit_id,
     coalesce((select
          i.event_time
      from
          today_events i
      where
          i.unit_id = o.unit_id and
          i.event_time <= r.list_end
      order by
          event_time desc
      limit 1
      offset 49), r.day_start) as first_event
from
     event_log o,
     report_ranges r
where
     event_time between r.list_start and r.list_end
)

select
     unit_id,
     (select
          count(*)
      from
          today_events ii
      where
          ii.unit_id = oo.unit_id and
          ii.event_time >= oo.first_event) as events,
     (select
          sum (case when status = -6 then 1 else 0 end)
      from
          today_events ii
      where
          ii.unit_id = oo.unit_id and
          ii.event_time >= oo.first_event) as live_answer
from
     unit_id_list oo
order by
     unit_id
;

Cheers,
Steve

I'm seeking ideas on the best way to craft the following query. I've
stripped everything down to the bare essentials and simplified it below.

Input data has a timestamp (actually an int received from the system in
the form of a Unix epoch), a unit identifier and a status:

  event_time | unit_id | status
------------+---------+--------
  1357056011 |      60 |      1
  1357056012 |     178 |      0
  1357056019 |     168 |      0
  1357056021 |       3 |      0
  1357056021 |       4 |      1
  1357056021 |     179 |      0
  1357056022 |       0 |      1
  1357056022 |       1 |      0
  1357056023 |       2 |      0
  1357056024 |       9 |      0
  1357056025 |       5 |      0
  1357056025 |       6 |      0
  1357056026 |       7 |      1

A given unit_id cannot have two events at the same time (enforced by
constraints).

Given a point in time I would like to:

1. Identify all distinct unit_ids with an entry that exists in the
preceding hour then

2. Count both the total events and sum the status=1 events for the most
recent 50 events for each unit_id that fall within a limited period
(e.g. don't look at data earlier than midnight). So unit_id 60 might
have 50 events in the last 15 minutes while unit_id 4 might have only 12
events after midnight.

The output would look something like:

  unit_id | events | status_1_count
---------+--------+----------------
       1  |     50 |             34
       2  |     27 |             18
       1  |     50 |             34
       1  |      2 |              0

Each sub-portion is easy and while I could use external processing or
set-returning functions I was hoping first to find the secret-sauce to
glue everything together into a single query.

Cheers,
Steve

Read more »