Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [pg-cron]

15 questions
3
votes
2 answers

pg_cron: "policy ... for table ... already exists" error while restore postgres database from dump

I'm trying to restore postgresql database from dump on fresh new server. Source database uses pgcron extension, target server have pgcron extension installed. This is how I create dumps on "source" server: pg_dumpall -h hostname -p 5435 -U myuser…
AntonioK
  • 133
  • 5
3
votes
2 answers

Use pg_cron to run VACUUM on multiple tables

Reading the pg_cron documentation I saw that the examples only execute a command when scheduling a task. In a StackOverflow post I saw that a user tried to run multiple VACUUM when scheduling the task, but an error occurs. Is there a way to run…
Tom
  • 438
  • 1
  • 6
  • 22
3
votes
2 answers

"connection failed" error for pg_cron extension

Installed pg_cron and scheduled job, though there is connection error because job is not done and cron.job_run_details table shows connection failed error message. As doc says: Important: Internally, pg_cron uses libpq to open a new connection…
Oto Shavadze
  • 575
  • 1
  • 7
  • 15
2
votes
1 answer

Restart master in physical replication

I have 2 servers involving master-slave replication (physical replication). I see the replication using select * from pg_replication_slots; there is a record saying : user : rep_user` application : 12/main state : streaming I have to change…
padjee
  • 337
  • 2
  • 11
  • 25
1
vote
1 answer

pg_cron timezone issue, unable to run at specified time

Unable to run at proper timezone. I want to run two things bash scripts Stored procedure For both case I found it runs at the wrong time. Instead of 1 AM and 3 AM pg_cron runs at a different time. Why could that be? At PostgreSQL timezone +6…
Sheikh Wasiu Al Hasib
  • 273
  • 1
  • 2
  • 14
1
vote
1 answer

How to deal with 'failed' pg_cron jobs (postgres)

I have some Jobs (postgres plpgsql procedures) that I am scheduling using pg_cron library/extension and cron expressions. I have some scripts that have failed and thus logged the 'failed' status column in the cron.job_run_details table. The problem…
PainIsAMaster
  • 131
  • 4
  • 8
0
votes
0 answers

pg_cron Installed by Superuser Not Showing Records to Developers

We have pg_cron installed on our PostgreSQL 13 database. The cron schema and job table are visible to all users. When the admin user runs SELECT * FROM cron.job, it returns rows as expected. However, when any developer runs the same query, it…
Muhammad sami
  • 1
0
votes
1 answer

are pg_cron and pgaudit database specific extensions? or is one installation per server enough?

I'm quite versed in postgresql, but new to any extension other than postgis. for example, one of the first things I do when setting up an instance is to install postgis in template1, so it's available in every db I create (most of them handle…
Chris
  • 185
  • 1
  • 3
  • 7
0
votes
0 answers

pg_cron jobs are not being executed

We have installed pg_cron extension on PostgreSQL 13 database. postgresql.conf file: shared_preload_libraries = 'pg_cron' cron.database_name = 'pg_cron_db_name' Also we enabled trust authentication for connections coming from localhost in…
Lesya Klachko
  • 1
  • 1
0
votes
0 answers

pg_cron is dramatically faster than running the same query manually

I have a simple table with 75 million rows (and roughly 47 million unique ref_ids) CREATE TABLE tbl ( id serial NOT NULL PRIMARY KEY, ref_id text NOT NULL, ts timestamp NOT NULL ); And I am trying to run the following query on the table…
perennial_
  • 101
  • 1
0
votes
0 answers

Schedule Postgres pg_cron jobs to trigger by something other than Cron expression

I have some pg_cron jobs that run at night time. They basically run some plpgsql Procedures and trigger based off CRON expressions. The problem is that some of my JOBS are dependent on the jobs that run before them. I have been dealing with that by…
PainIsAMaster
  • 131
  • 4
  • 8
0
votes
1 answer

Runnig pg_cron jobs on different database and non-public schema says "No procedure matches the given name and argument types"

I'm running pg_cron jobs that of course runs from the default postgres db. I have some functions/procedures that I created in another database called test, and have ran assigned these pg_cron jobs to the correct new database via (example): select…
PainIsAMaster
  • 131
  • 4
  • 8
0
votes
1 answer

Deleting records from two-child tables in parallel (concurrently)

I have a postgres database where I am using the pg_cron extension to automatically perform some database "maintenance" at night time - Specifically, my database has some "offers" in it with timestamps, so I move those records to archive tables if…
PainIsAMaster
  • 131
  • 4
  • 8
-1
votes
1 answer

Query timeout inside a cron job

I'm running a cron job using pg_cron. The job is running some cleanup queries, that occasionally takes longer to complete. When that happens, the job fails on statement timeout of 2 min which is the default ERROR: canceling statement due to…
arik alon
  • 1