PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device
Hi everyone, I am use a native logical replication in PostgreSQL 12 on Ubuntu 16.04 and all is working fine until i faced this error below. ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device Is there someone who faced this problem? Any idea how I can solve it ? BTW: I don't have any problem with space. Best Regard -- Jaurès FOUTE
Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device
Le mar. 30 juin 2020 à 21:23, Bruce Momjian a écrit : > On Tue, Jun 30, 2020 at 01:16:58PM -0700, Adrian Klaver wrote: > > On 6/30/20 11:03 AM, FOUTE K. Jaurès wrote: > > > Hi everyone, > > > > > > I am use a native logical replication in PostgreSQL 12 on Ubuntu 16.04 > > > and all is working fine until i faced this error below. > > > > From below it looks like you are using pg_logical which is a third party > > package. It is what the builtin logical replication is derived from, but > it > > is not the same thing. So what version of pg_logical are you using? > > > > > > > > ERROR: could not rename file > > > "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to > > > "pg_logical/snapshots/1A-7C00D890.snap": No space left on device > > > > > > Is there someone who faced this problem? > > > Any idea how I can solve it ? > > > > > > BTW: I don't have any problem with space. > > > > The error says you do. > > Where is pg_logical/snapshots/ mounted? > > Are there specific restrictions on that mount? > > I would also look at your kernel log. > [image: image.png] > > -- > Bruce Momjian https://momjian.us > EnterpriseDB https://enterprisedb.com > > The usefulness of a cup is in its emptiness, Bruce Lee > > -- Jaurès FOUTE Technology Consultant ISNOV SARL - Business Technology Consulting Tel: +237 79395671 / +237 96248793 Email: *jauresfo...@gmail.com *, jauresmelki...@yahoo.fr Compte Skype: jauresmelkiore
Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device
Le mar. 30 juin 2020 à 22:36, Adrian Klaver a écrit : > On 6/30/20 1:47 PM, FOUTE K. Jaurès wrote: > > > > > > Le mar. 30 juin 2020 à 21:23, Bruce Momjian > <mailto:br...@momjian.us>> a écrit : > > > > On Tue, Jun 30, 2020 at 01:16:58PM -0700, Adrian Klaver wrote: > > > On 6/30/20 11:03 AM, FOUTE K. Jaurès wrote: > > > > Hi everyone, > > > > > > > > I am use a native logical replication in PostgreSQL 12 on > > Ubuntu 16.04 > > > > and all is working fine until i faced this error below. > > > > > > From below it looks like you are using pg_logical which is a > > third party > > > package. It is what the builtin logical replication is derived > > from, but it > > > is not the same thing. So what version of pg_logical are you > using? > > > > I only install Postgresql 12. I dont use pg_logical > > My mistake, I remembered the location for the logical files wrong. > > > > > > /var/lib/postgresql/12/main/pg_logical (default location) > > From /var/lib/postgresql/12/main/pg_logical what does du -sh show? > Please show results as text, you can copy from terminal screen. > root@hybride:/var/lib/postgresql/12/main/pg_logical# du -sh 16K . > > > > > > > Are there specific restrictions on that mount? > > > > image.png > > > > > > I would also look at your kernel log. > > > > -- > >Bruce Momjian mailto:br...@momjian.us>> > > https://momjian.us > >EnterpriseDB https://enterprisedb.com > > > >The usefulness of a cup is in its emptiness, Bruce Lee > > > > > > > > -- > > Jaurès FOUTE > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- Jaurès FOUTE Technology Consultant ISNOV SARL - Business Technology Consulting Tel: +237 79395671 / +237 96248793 Email: *jauresfo...@gmail.com *, jauresmelki...@yahoo.fr Compte Skype: jauresmelkiore
Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device
Le mer. 1 juil. 2020 à 00:11, raf a écrit : > FOUTE K. Jaurčs wrote: > > > Hi everyone, > > > > I am use a native logical replication in PostgreSQL 12 on Ubuntu 16.04 > and > > all is working fine until i faced this error below. > > > > ERROR: could not rename file > > "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to > > "pg_logical/snapshots/1A-7C00D890.snap": No space left on device > > > > Is there someone who faced this problem? > > Any idea how I can solve it ? > > > > BTW: I don't have any problem with space. > > > > Best Regard > > > > -- > > Jaurčs FOUTE > > If you really haven't run out of space, > you might have run out of inodes. > The -i option of df should show you. > 100,000 empty files could cause that. > I wouldn't think that renaming would > require a new inode, but I also wouldn't > think that renaming would require any > more space on a file system. The result of df -i Sys. de fichiers Inœuds IUtil. ILibre IUti% Monté sur udev 3065149433 30647161% /dev tmpfs 3072780665 30721151% /run /dev/sdb259973632 356029 596176031% / tmpfs 3072780 7 30727731% /dev/shm tmpfs 3072780 10 30727701% /run/lock tmpfs 3072780 17 30727631% /sys/fs/cgroup /dev/sdb1 0 00 - /boot/efi tmpfs 3072780 19 30727611% /run/user/108 tmpfs 3072780 5 30727751% /run/user/1001 tmpfs 3072780 5 30727751% /run/user/1000 > > cheers, > raf > > > > -- Jaurès FOUTE Technology Consultant ISNOV SARL - Business Technology Consulting Tel: +237 79395671 / +237 96248793 Email: *jauresfo...@gmail.com *, jauresmelki...@yahoo.fr Compte Skype: jauresmelkiore
Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device
Le mer. 1 juil. 2020 à 15:45, Adrian Klaver a écrit : > On 6/30/20 10:51 PM, FOUTE K. Jaurès wrote: > > > > > > > > > From /var/lib/postgresql/12/main/pg_logical what does du -sh show? > > Please show results as text, you can copy from terminal screen. > > > > root@hybride:/var/lib/postgresql/12/main/pg_logical# du -sh > > 16K . > > > > Has the problem occurred again? > Yes 2020-07-01 19: 19: 21.868 WAT [24204] ERROR: could not receive data from WAL stream: ERROR: could not rename file "pg_logical / snapshots / 1E-8567EB0.snap.22195.tmp" to "pg_logical / snapshots / 1E-8567EB0.snap": No space left on device > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- Jaurès FOUTE Technology Consultant ISNOV SARL - Business Technology Consulting Tel: +237 79395671 / +237 96248793 Email: *jauresfo...@gmail.com *, jauresmelki...@yahoo.fr Compte Skype: jauresmelkiore
Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device
Le mer. 1 juil. 2020 à 17:11, Alvaro Herrera a écrit : > On 2020-Jun-30, FOUTE K. Jaurčs wrote: > > > Hi everyone, > > > > I am use a native logical replication in PostgreSQL 12 on Ubuntu 16.04 > and > > all is working fine until i faced this error below. > > > > ERROR: could not rename file > > "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to > > "pg_logical/snapshots/1A-7C00D890.snap": No space left on device > > What are your fsync and wal_sync_method settings? > All the two setting are commented #fsync = on #wal_sync_method = fsync > > -- > Įlvaro Herrerahttps://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > -- Jaurès FOUTE Technology Consultant ISNOV SARL - Business Technology Consulting Tel: +237 79395671 / +237 96248793 Email: *jauresfo...@gmail.com *, jauresmelki...@yahoo.fr Compte Skype: jauresmelkiore
Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device
Le mer. 1 juil. 2020 à 22:15, Adrian Klaver a écrit : > On 7/1/20 11:21 AM, FOUTE K. Jaurès wrote: > > > > > > > Has the problem occurred again? > > > > Yes > > 2020-07-01 19: 19: 21.868 WAT [24204] ERROR: could not receive data from > > WAL stream: ERROR: could not rename file "pg_logical / snapshots / > > 1E-8567EB0.snap.22195.tmp" to "pg_logical / snapshots / > > 1E-8567EB0.snap": No space left on device > > What are the permissions on?: > > pg_logical/snapshots/ > > postgres root@hybride:/var/lib/postgresql/12/main/pg_logical# ll total 20 drwx-- 4 postgres postgres 4096 juil. 1 20:21 ./ drwx-- 19 postgres postgres 4096 juil. 1 20:16 ../ drwx-- 2 postgres postgres 4096 mars 31 11:58 mappings/ -rw--- 1 postgres postgres 56 juil. 1 20:21 replorigin_checkpoint drwx-- 2 postgres postgres 4096 mars 31 11:58 snapshots/ > > > > > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > > > > > -- > > Jaurès FOUTE > > Technology Consultant > > ISNOV SARL - Business Technology Consulting > > Tel: +237 79395671 / +237 96248793 > > Email: _jauresfo...@gmail.com <mailto:jauresfo...@gmail.com>_, > > jauresmelki...@yahoo.fr <mailto:jauresmelki...@yahoo.fr> > > Compte Skype: jauresmelkiore > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- Jaurès FOUTE Technology Consultant ISNOV SARL - Business Technology Consulting Tel: +237 79395671 / +237 96248793 Email: *jauresfo...@gmail.com *, jauresmelki...@yahoo.fr Compte Skype: jauresmelkiore
Re: PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device
Hello Raf, Tom, After many tests of proposal solutions, I am not able to solve the issue. Seems that It is a bug ? Le jeu. 2 juil. 2020 à 01:59, Tom Lane a écrit : > raf writes: > > FOUTE K. Jaurès wrote: > >> The result of df -i > >> > >> Sys. de fichiers Inœuds IUtil. ILibre IUti% Monté sur > >> udev 3065149433 30647161% /dev > >> tmpfs 3072780665 30721151% /run > >> /dev/sdb259973632 356029 596176031% / > >> tmpfs 3072780 7 30727731% /dev/shm > >> tmpfs 3072780 10 30727701% /run/lock > >> tmpfs 3072780 17 30727631% /sys/fs/cgroup > >> /dev/sdb1 0 00 - /boot/efi > >> tmpfs 3072780 19 30727611% /run/user/108 > >> tmpfs 3072780 5 30727751% /run/user/1001 > >> tmpfs 3072780 5 30727751% /run/user/1000 > > > So that's not it. It would take ~60 million > > files to fill up your / inode table. I can't > > think of another explanation for that error > > message if df without -i also shows free space. > > I'm going to take a shot in the dark and ask if the root file system > is XFS. It doesn't take too much googling to find out that XFS has a > reputation for reporting ENOSPC when there seems to be more than enough > room left. Apparently, it can do so as a result of fragmentation problems > even though the disk as a whole has lots of space --- for one cautionary > example see this thread: > > https://www.spinics.net/lists/linux-xfs/msg22856.html > > typo-ishly titled "ENSOPC on a 10% used disk". It looks like the XFS crew > installed a fix for the underlying bug ... but that thread is from 2018 > and you're running a 2015 Ubuntu release. > > Or, to cut to the chase: maybe updating to a less hoary kernel would help. > If you are stuck with this Ubuntu release for some reason, consider using > a less bleeding-edge-at-the-time file system. > > regards, tom lane > -- Jaurès FOUTE Technology Consultant ISNOV SARL - Business Technology Consulting Tel: +237 79395671 / +237 96248793 Email: *jauresfo...@gmail.com *, jauresmelki...@yahoo.fr Compte Skype: jauresmelkiore
PostgresQL 12 - could not connect to server: FATAL: the database system is in recovery mode
Hello, How can i solve this error ? -- Jaurès FOUTE
Re: PostgresQL 12 - could not connect to server: FATAL: the database system is in recovery mode
Hello, The problem is related to my previous email "PostgreSQL 12 - ERROR: could not rename file "pg_logical/snapshots/1A-7C00D890.snap.13594.tmp" to "pg_logical/snapshots/1A-7C00D890.snap": No space left on device" . After stopping the replication postgresql has restarted to respond. Le lun. 13 juil. 2020 à 14:58, Srinivasa T N a écrit : > > > On Mon, Jul 13, 2020, 7:16 PM FOUTE K. Jaurès > wrote: > >> Hello, >> >> How can i solve this error >> > > More info required > > Regards, > Seenu. > >> >> -- >> Jaurès FOUTE >> > -- Jaurès FOUTE Technology Consultant ISNOV SARL - Business Technology Consulting Tel: +237 79395671 / +237 96248793 Email: *jauresfo...@gmail.com *, jauresmelki...@yahoo.fr Compte Skype: jauresmelkiore
PostgreSQL make too long to start.
It is make sense that PostgreSQL make too long to start, About 20 minutes. I'm using PostgreSQL 12 intalling on Ubuntu Server 18.04 and my database is about 25 GO of data.
Questions about Logical Replication - Issue ???
Hi everyone, Situation: - A Master Database on the HQ - i make a dump of the master database to the Subdivision Server - I create à Publication like: CREATE PUBLICATION iNOV_MasterData_Table_Pub FOR TABLE M_Product; On the Master Database - On the Subdivision Server, I create a Subscription like: CREATE SUBSCRIPTION iNOV_MasterData_Table_XXX_Sub CONNECTION '' PUBLICATION iNOV_MasterData_Table_Pub; - On the log, I have this error: - 2020-07-30 14:32:59.366 WAT [8022] ERROR: duplicate key value violates unique constraint "m_product_pkey" 2020-07-30 14:32:59.366 WAT [8022] DETAIL: Key (m_product_id)=(1001426) already exists. 2020-07-30 14:32:59.366 WAT [8022] CONTEXT: COPY m_product, line 1 2020-07-30 14:32:59.369 WAT [1536] LOG: background worker "logical replication worker" (PID 8022) exited with exit code 1 What can I do to solve this? Is it normal ? It BTW: When I create Subscription With the option (copy_data = false), I am able to replicate the new record. -- Jaurès FOUTE
Re: Questions about Logical Replication - Issue ???
hello Kyotaro, thx for you feedback and clarification. Le ven. 31 juil. 2020 à 02:13, Kyotaro Horiguchi a écrit : > Hi, > > At Thu, 30 Jul 2020 14:54:08 +0100, FOUTE K. Jaurès > wrote in > > Hi everyone, > > > > Situation: > > > >- A Master Database on the HQ > >- i make a dump of the master database to the Subdivision Server > >- I create à Publication like: CREATE PUBLICATION > >iNOV_MasterData_Table_Pub FOR TABLE M_Product; On the Master Database > >- On the Subdivision Server, I create a Subscription like: CREATE > >SUBSCRIPTION iNOV_MasterData_Table_XXX_Sub CONNECTION '' > >PUBLICATION iNOV_MasterData_Table_Pub; > >- On the log, I have this error: > > - 2020-07-30 14:32:59.366 WAT [8022] ERROR: duplicate key value > > violates unique constraint "m_product_pkey" > > 2020-07-30 14:32:59.366 WAT [8022] DETAIL: Key > > (m_product_id)=(1001426) already exists. > > 2020-07-30 14:32:59.366 WAT [8022] CONTEXT: COPY m_product, line 1 > > 2020-07-30 14:32:59.369 WAT [1536] LOG: background worker "logical > > replication worker" (PID 8022) exited with exit code 1 > > > > What can I do to solve this? Is it normal ? It > > BTW: When I create Subscription With the option (copy_data = false), I > am > > able to replicate the new record. > > As you know, initial table copy happens defaultly at subscription > creation (the COPY command in the above log lines was doing that). If > you are sure that the publisher table is in-sync with the subscriber > one, you can use copy_data=false safely and it's the proper operation. > > regards. > > -- > Kyotaro Horiguchi > NTT Open Source Software Center > -- Jaurès FOUTE
ERROR: canceling statement due to user request
Hello, How can I Solve this error ? ERROR: canceling statement due to user request Best Regards, -- Jaurès FOUTE
Re: ERROR: canceling statement due to user request
Ok, Just want if we can avoid this error message, With some config for exemple ? Le mer. 5 août 2020 à 11:07, Paul Förster a écrit : > Hi Jaurès, > > > On 05. Aug, 2020, at 11:35, FOUTE K. Jaurès > wrote: > > > > Hello, > > > > How can I Solve this error ? > > > > ERROR: canceling statement due to user request > > simple: don't make the user hit ctrl-c or otherwise interrupt a > transaction in progress. > > As soon, as a transaction is interrupted, you'll get the message. This is > mostly informational and not necessarily an error per se. > > Cheers, > Paul -- Jaurès FOUTE
Re: ERROR: canceling statement due to user request
Le mer. 5 août 2020 à 11:57, Pavel Stehule a écrit : > > > st 5. 8. 2020 v 12:51 odesílatel FOUTE K. Jaurès > napsal: > >> Ok, >> Just want if we can avoid this error message, With some config for >> exemple ? >> > > it is not possible > Ok Thx for feedback > > Pavel > >> >> Le mer. 5 août 2020 à 11:07, Paul Förster a >> écrit : >> >>> Hi Jaurès, >>> >>> > On 05. Aug, 2020, at 11:35, FOUTE K. Jaurès >>> wrote: >>> > >>> > Hello, >>> > >>> > How can I Solve this error ? >>> > >>> > ERROR: canceling statement due to user request >>> >>> simple: don't make the user hit ctrl-c or otherwise interrupt a >>> transaction in progress. >>> >>> As soon, as a transaction is interrupted, you'll get the message. This >>> is mostly informational and not necessarily an error per se. >>> >>> Cheers, >>> Paul >> >> >> >> -- >> Jaurès FOUTE >> > -- Jaurès FOUTE
ERROR: terminating logical replication worker due to timeout
Hello Everyone, How can I handle this error. Any idea ? I am usin PostgreSQL 12 on both Server. The error appear on the slave. My OS is Ubuntu Server 18.04 -- Jaurès FOUTE
Call a Normal function inside a Trigger Function
Hello, Is it possible to call a function inside a trigger function ? Any idea or link are welcome. Thanks in advance Best Regards -- Jaurès FOUTE
Re: Call a Normal function inside a Trigger Function
Can I have an example please? Or a link On Sun, 16 Apr 2023, 17:08 Pavel Stehule, wrote: > Hi > > > ne 16. 4. 2023 v 16:15 odesílatel FOUTE K. Jaurès > napsal: > >> Hello, >> >> Is it possible to call a function inside a trigger function ? >> Any idea or link are welcome. Thanks in advance >> > > sure, there is not any limit. > > Regards > > Pavel > > >> >> Best Regards >> -- >> Jaurès FOUTE >> >
Re: Call a Normal function inside a Trigger Function
Thanks @Adrian Klaver
It's clear for me now.
On Sun, 16 Apr 2023, 20:13 Adrian Klaver, wrote:
> On 4/16/23 11:47, FOUTE K. Jaurès wrote:
> > Can I have an example please? Or a link
>
> create table trg_test (id integer, fld_1 varchar, fld_2 boolean);
>
>
> CREATE OR REPLACE FUNCTION public.child_fnc(token character varying)
> RETURNS void
> LANGUAGE plpgsql
> AS $function$
> BEGIN
> IF token = 'yes' THEN
> RAISE NOTICE 'Child';
> END IF;
> END;
> $function$
> ;
>
>
> CREATE OR REPLACE FUNCTION public.parent_fnc()
> RETURNS trigger
> LANGUAGE plpgsql
> AS $function$
> BEGIN
> RAISE NOTICE 'Id is %', NEW.id;
> RAISE NOTICE 'Fld_1 is %', NEW.fld_1;
> RAISE NOTICE 'Parent';
> PERFORM child_fnc('yes');
> RETURN NEW;
> END;
> $function$
> ;
>
> create trigger test_trg before insert on trg_test for each row execute
> function parent_fnc();
>
> insert into trg_test values (1, 'dog', 'f');
> NOTICE: Id is 1
> NOTICE: Fld_1 is dog
> NOTICE: Parent
> NOTICE: Child
> INSERT 0 1
>
> >
> > On Sun, 16 Apr 2023, 17:08 Pavel Stehule, > <mailto:pavel.steh...@gmail.com>> wrote:
> >
> > Hi
> >
> >
> > ne 16. 4. 2023 v 16:15 odesílatel FOUTE K. Jaurès
> > mailto:jauresfo...@gmail.com>> napsal:
> >
> > Hello,
> >
> > Is it possible to call a function inside a trigger function ?
> > Any idea or link are welcome. Thanks in advance
> >
> >
> > sure, there is not any limit.
> >
> > Regards
> >
> > Pavel
> >
> >
> > Best Regards
> > --
> > Jaurès FOUTE
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
Materialized Views - Way to refresh automatically (Incrementaly)
Hello Everyone, Is There a way on PostgresQL 14 to automatically increment a Materialized Views ? -- Jaurès FOUTE
Re: Materialized Views - Way to refresh automatically (Incrementaly)
hello Thomas, Thanks, I'll check it out. Le jeu. 11 mai 2023 à 12:01, Thomas Boussekey a écrit : > Hello > > Le jeu. 11 mai 2023, 12:46, FOUTE K. Jaurès a > écrit : > >> Hello Everyone, >> >> Is There a way on PostgresQL 14 to automatically increment a Materialized >> Views ? >> > > Have a look at pg_ivm extension https://github.com/sraoss/pg_ivm > >> >> -- >> Jaurès FOUTE >> > > Hope this helps, > Thomas > >> >> -- Jaurès FOUTE
PG_Cron - Error Message Connection failed
Hello everyone, Can someone help me to understand this issue. I installed and configured pg_cron on the server and I was able to connect without providing a password using psql command line. But when i schedule a job, like this: SELECT cron.schedule( 'TEST','30 seconds', $$SELECT 1$$); the result show [image: image.png] Any help is welcome -- Jaurès FOUTE
Re: PG_Cron - Error Message Connection failed
hello Fabricio, the listen_addresses is set to * the result of psql command line [image: image.png] The job on pg_cron [image: image.png] Le ven. 12 mai 2023 à 17:05, Fabricio Pedroso Jorge a écrit : > Try checking the view cron.job, column "nodename", if it's set to > "localhost", change it (via an UPDATE) to the address defined in > "listen_address" parameter and see if this works. > > > Em sex., 12 de mai. de 2023 às 17:00, FOUTE K. Jaurès < > jauresfo...@gmail.com> escreveu: > >> Hello everyone, >> >> Can someone help me to understand this issue. I installed and configured >> pg_cron on the server and I was able to connect without providing a >> password using psql command line. >> But when i schedule a job, like this: SELECT cron.schedule( 'TEST','30 >> seconds', $$SELECT 1$$); >> the result show >> [image: image.png] >> >> Any help is welcome >> >> -- >> Jaurès FOUTE >> > > > -- > *Fabrício Pedroso Jorge.* > Database Architect / Database Engineer > > *LinkedIn Profile* > http://br.linkedin.com/in/fabriciojorge > > *Contacts:* > + 353 085 8221706 > fpjb...@gmail.com > -- Jaurès FOUTE
Re: PG_Cron - Error Message Connection failed
Understand @Adrian Klaver . the log show: 2023-05-12 17:30:19.327 WAT [46190] LOG: cron job 8 starting: SELECT 1 2023-05-12 17:30:19.339 WAT [46190] LOG: cron job 8 connection failed Le ven. 12 mai 2023 à 17:23, Adrian Klaver a écrit : > On 5/12/23 09:22, FOUTE K. Jaurès wrote: > > 1) Please do not top post. Use inline posting > > 2) Do not use images, copy and paste as text. > > > hello Fabricio, > > > > the listen_addresses is set to * > > the result of psql command line > > image.png > > The job on pg_cron > > image.png > > What does the Postgres log show when pg_cron is trying to make connections? > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > -- Jaurès FOUTE
Re: PG_Cron - Error Message Connection failed
Le ven. 12 mai 2023 à 20:21, Adrian Klaver a écrit : > On 5/12/23 09:41, FOUTE K. Jaurès wrote: > > Understand @Adrian Klaver <mailto:adrian.kla...@aklaver.com> . > > the log show: > > 2023-05-12 17:30:19.327 WAT [46190] LOG: cron job 8 starting: SELECT 1 > > 2023-05-12 17:30:19.339 WAT [46190] LOG: cron job 8 connection failed > > Well that did not go anywhere. > > In your original post in the image under the database column it looks > like, to my old eyes, there is a list of database names. > > Is that the case? > > Copy and paste that content in your reply. > Hello @Adrian Klaver sorry for the delais. Copy of content of cron.job ? > > > > > Le ven. 12 mai 2023 à 17:23, Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> a écrit : > > > > On 5/12/23 09:22, FOUTE K. Jaurès wrote: > > > > 1) Please do not top post. Use inline posting > > > > 2) Do not use images, copy and paste as text. > > > > > hello Fabricio, > > > > > > the listen_addresses is set to * > > > the result of psql command line > > > image.png > > > The job on pg_cron > > > image.png > > > > What does the Postgres log show when pg_cron is trying to make > > connections? > > > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > > > > > -- > > Jaurès FOUTE > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > -- Jaurès FOUTE
Re: PG_Cron - Error Message Connection failed
Le sam. 13 mai 2023 à 15:59, Adrian Klaver a écrit : > On 5/13/23 00:54, FOUTE K. Jaurès wrote: > > > > > > > > > In your original post in the image under the database column it looks > > like, to my old eyes, there is a list of database names. > > > > Is that the case? > > > > Copy and paste that content in your reply. > > > > > > Hello @Adrian Klaver <mailto:adrian.kla...@aklaver.com> sorry for the > > delais. > > Copy of content of cron.job ? > > The text version of whatever was in the image in this post: > i'm using Ubuntu server 22.04 LTS with PostgreSQL 13 I install pg_cron using the command: sudo apt-get -y install postgresql-13-cron > > > https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.gmail.com > > Or at least what was in the database column. > > > -- > > Jaurès FOUTE > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > -- Jaurès FOUTE
Re: PG_Cron - Error Message Connection failed
Le dim. 14 mai 2023 à 16:12, Adrian Klaver a écrit : > On 5/14/23 00:20, FOUTE K. Jaurès wrote: > > > > > > Le sam. 13 mai 2023 à 15:59, Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> a écrit : > > > > On 5/13/23 00:54, FOUTE K. Jaurès wrote: > > > > > > > > > > > > > > In your original post in the image under the database column > > it looks > > > like, to my old eyes, there is a list of database names. > > > > > > Is that the case? > > > > > > Copy and paste that content in your reply. > > > > > > > > > Hello @Adrian Klaver <mailto:adrian.kla...@aklaver.com > > <mailto:adrian.kla...@aklaver.com>> sorry for the > > > delais. > > > Copy of content of cron.job ? > > > > The text version of whatever was in the image in this post: > > > > > > i'm using Ubuntu server 22.04 LTS with PostgreSQL 13 > > I install pg_cron using the command: sudo apt-get -y install > > postgresql-13-cron > > Not what was requested. > > In the post linked to here: > > > https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.gmail.com > > there was an image of the result of: > > SELECT cron.schedule( 'TEST','30 seconds', $$SELECT 1$$); > > Provide that result as text. > > jobid | schedule | command | nodename | nodeport | database | username | active | jobname ---++--+---+--++--++- 8 | 30 seconds | SELECT 1 | localhost | 5692 | databasename | postgres | t | TEST (1 row) > > > > > > > > https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.gmail.com > < > https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.gmail.com > > > > > > Or at least what was in the database column. > > > > > -- > > > Jaurès FOUTE > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > > > > > -- > > Jaurès FOUTE > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > -- Jaurès FOUTE
Re: PG_Cron - Error Message Connection failed
Le dim. 14 mai 2023 à 21:12, Adrian Klaver a écrit : > On 5/14/23 13:04, FOUTE K. Jaurès wrote: > > > > > > Le dim. 14 mai 2023 à 16:12, Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> a écrit : > > > > > Not what was requested. > > > > In the post linked to here: > > > > > https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.gmail.com > < > https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.gmail.com > > > > > > there was an image of the result of: > > > > SELECT cron.schedule( 'TEST','30 seconds', $$SELECT 1$$); > > > > Provide that result as text. > > > > jobid | schedule | command | nodename | nodeport | > > database | username | active | jobname > > > ---++--+---+--++--++- > > 8 | 30 seconds | SELECT 1 | localhost | 5692 | databasename | > > postgres | t | TEST > > (1 row) > > That is not the same result as in the image, it had additional fields: > runid, job_pid, status, return_message, start_time. > Okay, Understands now, this result come from cron.job_run_details that show the status of running job Select * from cron.job_run_details where jobid =8 order by runid desc limit 5 jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time ---+---+-++--+--++---++-- 8 | 9276 | | inov_victoria_v71_rdec21_00_ibackuppro_rep | postgres | SELECT 1 | failed | connection failed || 8 | 9275 | | inov_victoria_v71_rdec21_00_ibackuppro_rep | postgres | SELECT 1 | failed | connection failed || 8 | 9274 | | inov_victoria_v71_rdec21_00_ibackuppro_rep | postgres | SELECT 1 | failed | connection failed || 8 | 9272 | | inov_victoria_v71_rdec21_00_ibackuppro_rep | postgres | SELECT 1 | failed | connection failed || 8 | 9271 | | inov_victoria_v71_rdec21_00_ibackuppro_rep | postgres | SELECT 1 | failed | connection failed || (5 rows) > > Also the database field had not been edited. In the original image it > looks like there was multiple databases named. From what I understand of > pg_cron a job can only run on one database at a time. > No, it's just one database. > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > -- Jaurès FOUTE
Re: PG_Cron - Error Message Connection failed
Le lun. 15 mai 2023 à 05:12, FOUTE K. Jaurès a écrit : > > > Le dim. 14 mai 2023 à 21:12, Adrian Klaver a > écrit : > >> On 5/14/23 13:04, FOUTE K. Jaurès wrote: >> > >> > >> > Le dim. 14 mai 2023 à 16:12, Adrian Klaver > > <mailto:adrian.kla...@aklaver.com>> a écrit : >> >> > >> > Not what was requested. >> > >> > In the post linked to here: >> > >> > >> https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.gmail.com >> < >> https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.gmail.com >> > >> > >> > there was an image of the result of: >> > >> > SELECT cron.schedule( 'TEST','30 seconds', $$SELECT 1$$); >> > >> > Provide that result as text. >> > >> > jobid | schedule | command | nodename | nodeport | >> > database | username | active | jobname >> > >> ---++--+---+--++--++- >> > 8 | 30 seconds | SELECT 1 | localhost | 5692 | databasename | >> > postgres | t | TEST >> > (1 row) >> >> That is not the same result as in the image, it had additional fields: >> runid, job_pid, status, return_message, start_time. >> > Okay, Understands now, this result come from cron.job_run_details that > show the status of running job > Select * from cron.job_run_details where jobid =8 order by runid desc > limit 5 > jobid | runid | job_pid | database | > username | command | status | return_message | start_time | end_time > > ---+---+-++--+--++---++-- > 8 | 9276 | | inov_victoria_v71_rdec21_00_ibackuppro_rep | > postgres | SELECT 1 | failed | connection failed || > 8 | 9275 | | inov_victoria_v71_rdec21_00_ibackuppro_rep | > postgres | SELECT 1 | failed | connection failed || > 8 | 9274 | | inov_victoria_v71_rdec21_00_ibackuppro_rep | > postgres | SELECT 1 | failed | connection failed || > 8 | 9272 | | inov_victoria_v71_rdec21_00_ibackuppro_rep | > postgres | SELECT 1 | failed | connection failed || > 8 | 9271 | | inov_victoria_v71_rdec21_00_ibackuppro_rep | > postgres | SELECT 1 | failed | connection failed || > (5 rows) > >> >> Also the database field had not been edited. In the original image it >> looks like there was multiple databases named. From what I understand of >> pg_cron a job can only run on one database at a time. >> > No, it's just one database. > >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> >> > > -- > Jaurès FOUTE > Hello @Adrian Klaver . I've just finished a test on PostgreSQL 14 and everything works as expected. -- Jaurès FOUTE
PostgreSQL 13 - Logical Replication - ERROR: could not receive data from WAL stream: SSL SYSCALL error: EOF detected
Hello everyone, I have a replication between PostgreSQL 12 to 13 in the production system. Using Ubuntu 18.04 LTS We have this error today. *2023-05-18 18:34:04.374 WAT [117322] ERROR: could not receive data from WAL stream: SSL SYSCALL error: EOF detected2023-05-18 18:34:04.381 WAT [118393] LOG: logical replication apply worker for subscription "inov_transactionnal_table_bertoua_sub_00" has started2023-05-18 18:34:04.423 WAT [1039] LOG: background worker "logical replication worker" (PID 117322) exited with exit code 12023-05-18 18:47:51.485 WAT [66836] postgres@inov_henrietfreres_v71_00 LOG: could not receive data from client: Connection timed out.* *Any idea how to solve this?* -- Jaurès FOUTE
Re: PostgreSQL 13 - Logical Replication - ERROR: could not receive data from WAL stream: SSL SYSCALL error: EOF detected
Le ven. 19 mai 2023 à 05:02, Kyotaro Horiguchi a écrit : > At Thu, 18 May 2023 21:00:08 +0100, FOUTE K. Jaurès > wrote in > > Hello everyone, > > > > I have a replication between PostgreSQL 12 to 13 in the production > system. > > Using Ubuntu 18.04 LTS > > We have this error today. > > > > > > > > *2023-05-18 18:34:04.374 WAT [117322] ERROR: could not receive data from > > WAL stream: SSL SYSCALL error: EOF detected > > 2023-05-18 18:34:04.381 WAT [118393] LOG: logical replication apply > worker for subscription "inov_transactionnal_table_bertoua_sub_00" has > started > > 2023-05-18 18:34:04.423 WAT [1039] LOG: background worker "logical > replication worker" (PID 117322) exited with exit code 1 > > 2023-05-18 18:47:51.485 WAT [66836] postgres@inov_henrietfreres_v71_00 > LOG: could not receive data from client: Connection timed out.* > > > > *Any idea how to solve this?* > > According to the message, the SSL-encrypted replication connection got > disconnected unexpectedly. I suppose it is due to the death of the > upstream server or some reasons outside of PostgreSQL. It seems like > the issue had been persisting for a few minutes after that. Other than > the server's death, I doubt some network hardware problems or changes > of firewall or networking setup of the OS. I think it would be good > idea to check for them first. > Hello @Kyotaro Horiguchi Thx for feedback. BTW I didn't have any network issues. > > regards. > > -- > Kyotaro Horiguchi > NTT Open Source Software Center > -- Jaurès FOUTE
ERROR: could not start WAL streaming: ERROR: replication slot "XXX" does not exist
Hello EveryOne, How can I solve Issue. ??? *ERROR: could not start WAL streaming: ERROR: replication slot "XXX" does not exist2021-03-27 11:48:33.012 WAT [1090] LOG: background worker "logical replication worker" (PID 8458) exited with exit code 12021-03-27 11:48:38.019 WAT [8461] LOG: logical replication apply worker for subscription "XXX has started* All working fine a few days ago but this morning I have this issue. -- Jaurès FOUTE
Re: ERROR: could not start WAL streaming: ERROR: replication slot "XXX" does not exist
Curiously the slot is not dropped. Le sam. 27 mars 2021 à 20:31, Atul Kumar a écrit : > As per your error, it seems replication slot has been dropped. > > On Saturday, March 27, 2021, FOUTE K. Jaurès > wrote: > >> Hello EveryOne, >> >> How can I solve Issue. ??? >> >> >> >> *ERROR: could not start WAL streaming: ERROR: replication slot "XXX" >> does not exist2021-03-27 11:48:33.012 WAT [1090] LOG: background worker >> "logical replication worker" (PID 8458) exited with exit code 12021-03-27 >> 11:48:38.019 WAT [8461] LOG: logical replication apply worker for >> subscription "XXX has started* >> >> All working fine a few days ago but this morning I have this issue. >> >> -- >> Jaurès FOUTE >> > -- Jaurès FOUTE
PostgreSQL 12 - ERROR: invalid attribute number 2 for ad_user_pkey - Urgent
Hello, I have a strange issue in a production database on a customer and need to help to know how to solve the problem. Any select on the table other than pg_catalog table throws the error FATAL: catalog is missing 1 attribute(s) for Any idea how to solve this issue is really appreciated -- Jaurès FOUTE
Re: PostgreSQL 12 - ERROR: invalid attribute number 2 for ad_user_pkey - Urgent
No, I am using PostgreSQL 12 (Server and Client) Le mer. 1 sept. 2021 à 16:12, Rob Sargent a écrit : > > > On Sep 1, 2021, at 8:08 AM, FOUTE K. Jaurès wrote: > > Hello, > > I have a strange issue in a production database on a customer and need to > help to know how to solve the problem. > > Any select on the table other than pg_catalog table throws the error FATAL: > catalog is missing 1 attribute(s) for > > Any idea how to solve this issue is really appreciated > > > Any chance there’s a version mis-match between client and server? > > -- Jaurès FOUTE
Re: PostgreSQL 12 - ERROR: invalid attribute number 2 for ad_user_pkey - Urgent
I already restart the server (x3) Le mer. 1 sept. 2021 à 16:25, David G. Johnston a écrit : > On Wed, Sep 1, 2021 at 8:08 AM FOUTE K. Jaurès > wrote: > >> Any idea how to solve this issue is really appreciated >> > > Have you restarted the server? > > In pg_attribute for one of the problematic tables are all of the columns > present that should be (i.e., is this a catalog contents error or, say, a > relation cache or lookup failure)? > > The client application shouldn't have anything to do with these errors - > the server is doing all of the work. > > David J. > > -- Jaurès FOUTE
Re: PostgreSQL 12 - ERROR: invalid attribute number 2 for ad_user_pkey - Urgent
For all table except the pg_catalo table. The dump on the database is not possible too. (pg_dump: error: invalid column numbering in table "xx") Le mer. 1 sept. 2021 à 16:27, Adrian Klaver a écrit : > On 9/1/21 8:08 AM, FOUTE K. Jaurès wrote: > > Hello, > > > > I have a strange issue in a production database on a customer and need > > to help to know how to solve the problem. > > > > Any select on the table other than pg_catalog table throws the error > > FATAL: catalog is missing 1 attribute(s) for > > To be clear this only happens when you select a particular user table? > > Also the error message should have end 'for relid ', can you provide > that information. > > > > > Any idea how to solve this issue is really appreciated > > > > > > -- > > Jaurès FOUTE > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- Jaurès FOUTE
Re: PostgreSQL 12 - ERROR: invalid attribute number 2 for ad_user_pkey - Urgent
Hello Tom, Thank You for your Answer. It solves the problem. Thank you very much for your support. Le mer. 1 sept. 2021 à 16:46, Tom Lane a écrit : > =?UTF-8?Q?FOUTE_K=2E_Jaur=C3=A8s?= writes: > > For all table except the pg_catalo table. > > The dump on the database is not possible too. (pg_dump: error: invalid > > column numbering in table "xx") > > It seems that pg_attribute is messed up. If you are really lucky, > it might be only a problem in pg_attribute's indexes, in which case > reindexing pg_attribute would fix it. However, I recommend proceeding > on the assumption that you have possibly-irrecoverable damage. Before > you do ANYTHING, make a complete filesystem-level backup of the cluster > (stop the server, then do "tar cf backup.tar $PGDATA" or equivalent). > > Even if reindexing pg_attribute seems to fix it, I'd counsel then doing > a pg_dump and restore, in hopes of curing any other problems that may > have stemmed from the same root cause. > > Speaking of root cause, have you had any crashes lately? Is your > Postgres up-to-date? How about the underlying OS? > > regards, tom lane > -- Jaurès FOUTE
Re: PostgreSQL 12 - ERROR: invalid attribute number 2 for ad_user_pkey - Urgent
After running: REINDEX TABLE pg_catalog.pg_attribute ; Le mer. 1 sept. 2021 à 17:54, FOUTE K. Jaurès a écrit : > Hello Tom, > > Thank You for your Answer. It solves the problem. > Thank you very much for your support. > > Le mer. 1 sept. 2021 à 16:46, Tom Lane a écrit : > >> =?UTF-8?Q?FOUTE_K=2E_Jaur=C3=A8s?= writes: >> > For all table except the pg_catalo table. >> > The dump on the database is not possible too. (pg_dump: error: invalid >> > column numbering in table "xx") >> >> It seems that pg_attribute is messed up. If you are really lucky, >> it might be only a problem in pg_attribute's indexes, in which case >> reindexing pg_attribute would fix it. However, I recommend proceeding >> on the assumption that you have possibly-irrecoverable damage. Before >> you do ANYTHING, make a complete filesystem-level backup of the cluster >> (stop the server, then do "tar cf backup.tar $PGDATA" or equivalent). >> >> Even if reindexing pg_attribute seems to fix it, I'd counsel then doing >> a pg_dump and restore, in hopes of curing any other problems that may >> have stemmed from the same root cause. >> >> Speaking of root cause, have you had any crashes lately? Is your >> Postgres up-to-date? How about the underlying OS? >> >> regards, tom lane >> > > > -- > Jaurès FOUTE > -- Jaurès FOUTE
PostgreSQL - Ordering Table based of Foreign Key
Hello, I want to order tables based on the foreign key so that I can delete tables one by one without facing "ERROR: update or delete on table "table" violates foreign key constraint. DETAIL: Key is still referenced from table" Any help is appreciated. Thank you in advance. Best regards Jaurès FOUTE
Re: PostgreSQL - Ordering Table based of Foreign Key
Thanks for your input but I want to make a delete with where clause On Sun, 3 Oct 2021, 10:26 Andreas Joseph Krogh, wrote: > På søndag 03. oktober 2021 kl. 10:49:49, skrev Thomas Kellerer < > sham...@gmx.net>: > > FOUTE K. Jaurès schrieb am 03.10.2021 um 09:48: > > I want to order tables based on the foreign key so that I can delete > > tables one by one without facing "ERROR: update or delete on table > > "table" violates foreign key constraint. DETAIL: Key is still > > referenced from table" > > You can create the foreign key constraints with the "ON DELETE CASCADE" > option. > Then Postgres will handle dependencies automatically for you. > > But that means that *all* DELETEs will be cascaded. > > Unfortunately, there is no DELETE FROM ... CASCADE option (similar to DROP) > > > There is TRUNCATE ... CASCADE > https://www.postgresql.org/docs/14/sql-truncate.html > > -- > Andreas Joseph Krogh >
Re: PostgreSQL - Ordering Table based of Foreign Key
I need to delete data from about 100 table in the production system for a specific client without stopping application. On Sun, 3 Oct 2021, 17:19 Adrian Klaver, wrote: > On 10/3/21 7:40 AM, FOUTE K. Jaurès wrote: > > Thanks for your input but I want to make a delete with where clause > > Then Thomas Kellerer's suggestion of having "ON DELETE CASCADE" on the > FK would seem to be what you need. If not then you will need to provide > more information about what you are trying to achieve? > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: PostgreSQL - Ordering Table based of Foreign Key
Hello, Thx for all the feedback. After googling that, I saw this link that can be a way I can go. https://stackoverflow.com/questions/51279588/sort-tables-in-order-of-dependency-postgres <https://stackoverflow.com/questions/51279588/sort-tables-in-order-of-dependency-postgres> Le dim. 3 oct. 2021 à 22:33, David G. Johnston a écrit : > > > On Sun, Oct 3, 2021, 00:48 FOUTE K. Jaurès wrote: > >> Hello, >> >> I want to order tables based on the foreign key so that I can delete >> tables one by one without facing "ERROR: update or delete on table >> "table" violates foreign key constraint. DETAIL: Key is still referenced >> from table" >> > > As others have said this is why on delete cascade exists. Unfortunately > this does require some advanced planning as changing it on the fly doesn't > really work. > > I do not believe there is a built-in way to return an ordered listing of > dependent relations given a base relation as an input. But the catalog > entries do exist should you wish to build such yourself. > > That said maybe deferred constraint evaluation will work so that at least > the order doesn't matter. But you still.would.need to know which tables to > write delete commands for. > > There is some recent discussion on making this work in a more > user-friendly away but that would be only available in v15 at best. > > David J. > > >> -- Jaurès FOUTE
