From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: How to schedule long running SQL job
Date: Thu, 20 Jul 2023 11:54:49 -0500
Message-ID: <1ffcd55d-b163-f40f-5881-2311d30e6a70@gmail.com> (raw)
In-Reply-To: <DM4PR19MB597871C85F73C84E0783AA5FD33EA@DM4PR19MB5978.namprd19.prod.outlook.com>
References: <DM4PR19MB59786FDE7F5052955D319F40D339A@DM4PR19MB5978.namprd19.prod.outlook.com>
<0644DDC9-A86B-411F-A70B-DD2C99D7950F@gmail.com>
<DM4PR19MB597858D95BA6A938AC76EF8BD339A@DM4PR19MB5978.namprd19.prod.outlook.com>
<02144dd8-cece-b9e9-5903-f9170293d87a@gmail.com>
<DM4PR19MB5978392B17E41CF7A6C4C17FD33EA@DM4PR19MB5978.namprd19.prod.outlook.com>
<4b29b4fd-db99-4bc2-7973-0d8e744b7674@gmail.com>
<DM4PR19MB597871C85F73C84E0783AA5FD33EA@DM4PR19MB5978.namprd19.prod.outlook.com>
PgAdmin *requires* a web server (which comes built-in to the Windows
stand-alone version); you don't run it "in" Postgresql.
On 7/20/23 09:07, M Sarwar wrote:
> Ron,
>
> Yes, my pgAdmin is on my laptop.
> Can I run pgAdmin any where else in Postgres / RDS / AWS environment?
> Thanks,
> Sarwar
>
> ----------------------------------------------------------------------------
> *From:* Ron <ronljohnsonjr@gmail.com>
> *Sent:* Thursday, July 20, 2023 9:26 AM
> *To:* pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> *Subject:* Re: How to schedule long running SQL job
> PgAdmin is on your laptop, no?
>
> But, of course, you can always spin up a small AWS Linux VM to run psql,
> scheduled "manual" VACUUM, ANALYZE, etc.
>
> On 7/19/23 19:20, M Sarwar wrote:
>> I thought that something needs to come from AWS side but definitely I can
>> try this approach of psql and Windows Scheduler.
>> Thanks,
>> Sarwar
>>
>> ----------------------------------------------------------------------------
>> *From:* Ron <ronljohnsonjr@gmail.com> <mailto:ronljohnsonjr@gmail.com>
>> *Sent:* Wednesday, July 19, 2023 8:09 PM
>> *To:* pgsql-admin@lists.postgresql.org
>> <mailto:pgsql-admin@lists.postgresql.org>
>> <pgsql-admin@lists.postgresql.org> <mailto:pgsql-admin@lists.postgresql.org>
>> *Subject:* Re: How to schedule long running SQL job
>> Install psql (don't ask me how) on your laptop, then use Windows Task
>> Scheduler to run your query.
>>
>> On 7/19/23 18:57, M Sarwar wrote:
>>> From my laptop on powershell and dos prompt, it is saying tht pg_cron is
>>> not recognized.
>>> Do I need anything new installation for this?
>>> Thanks,
>>> Sarwar
>>>
>>> ----------------------------------------------------------------------------
>>> *From:* John Scalia <jayknowsunix@gmail.com> <mailto:jayknowsunix@gmail.com>
>>> *Sent:* Wednesday, July 19, 2023 7:45 PM
>>> *To:* M Sarwar <sarwarmd02@outlook.com> <mailto:sarwarmd02@outlook.com>
>>> *Cc:* pgsql-admin@lists.postgresql.org
>>> <mailto:pgsql-admin@lists.postgresql.org>
>>> <pgsql-admin@lists.postgresql.org> <mailto:pgsql-admin@lists.postgresql.org>
>>> *Subject:* Re: How to schedule long running SQL job
>>> Try pg_cron. I know later versions support it.
>>>
>>> Sent from my iPad
>>>
>>>> On Jul 19, 2023, at 7:41 PM, M Sarwar <sarwarmd02@outlook.com>
>>>> <mailto:sarwarmd02@outlook.com> wrote:
>>>>
>>>>
>>>> Hi,
>>>> Db: Postgres
>>>> Cloud: aws / RDS
>>>> Tool using so far: PgAdmin
>>>>
>>>> SQL got aborted after running for 1 hr 40 minutes from pgadmin.
>>>> I need CSV file output.
>>>> What is the recommended approach for this issue.
>>>>
>>>> SQL:
>>>>
>>>> SELECT P1.FILE_ID,
>>>>
>>>> F.FILENAME,
>>>>
>>>> P1.STAGE,
>>>>
>>>> P1.SERIAL_NUMBER,
>>>>
>>>> P1.TEST_IMAGE_SET_VALUE,
>>>>
>>>> SUBSTRING(P1.SERIAL_NUMBER, '[A-Z][0-9]+') AS
>>>> MCM_ID_SERIAL_NUMBER,
>>>>
>>>> P1.RUN_ID,
>>>>
>>>> SPLIT_PART(P1.RUN_ID, ':', 1) TEST_LOT
>>>>
>>>> FROM BRONX.TEST_PART_DETAILS_ALL_MCM P1,
>>>>
>>>> BRONX.FILES_METADATA F
>>>>
>>>> WHERE F.FILE_ID = P1.FILE_ID
>>>>
>>>> AND EXISTS (
>>>> SELECT SUBSTRING(P2.SERIAL_NUMBER, '[A-Z][0-9]+') AS MCM_ID2 ,
>>>>
>>>> COUNT(DISTINCT SPLIT_PART(P2.RUN_ID, ':', 1)) AS NUM_DISTINCT_TEST_LOTS
>>>>
>>>> FROM BRONX.TEST_PART_DETAILS_ALL_MCM P2
>>>>
>>>> WHERE SUBSTRING(P2.SERIAL_NUMBER, '[A-Z][0-9]+') =
>>>> SUBSTRING(P1.SERIAL_NUMBER, '[A-Z][0-9]+')
>>>>
>>>> GROUP BY MCM_ID2
>>>>
>>>> HAVING COUNT(DISTINCT SPLIT_PART(P2.RUN_ID, ':', 1)) > 1
>>>>
>>>> )
>>>>
>>>> order BY MCM_ID_SERIAL_NUMBER,
>>>>
>>>> TEST_LOT
>>>>
>>>> ;
>>>>
>>>> I used to use crontab or OEM to schedule these types jobs in the past.
>>>> Now it is on RDS/AWS.
>>>>
>>>>
>>>> Thanks,
>>>> Sarwar
>>>>
>>
>> --
>> Born in Arizona, moved to Babylonia.
>
> --
> Born in Arizona, moved to Babylonia.
--
Born in Arizona, moved to Babylonia.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: How to schedule long running SQL job
In-Reply-To: <1ffcd55d-b163-f40f-5881-2311d30e6a70@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox