Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uTO0l-003ryl-GN for pgsql-admin@arkaria.postgresql.org; Sun, 22 Jun 2025 16:57:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uTO0h-00Dvg0-9n for pgsql-admin@arkaria.postgresql.org; Sun, 22 Jun 2025 16:57:39 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uTO0g-00Dvfr-TG for pgsql-admin@lists.postgresql.org; Sun, 22 Jun 2025 16:57:39 +0000 Received: from mail-oa1-x30.google.com ([2001:4860:4864:20::30]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uTO0f-003VE7-1B for pgsql-admin@lists.postgresql.org; Sun, 22 Jun 2025 16:57:39 +0000 Received: by mail-oa1-x30.google.com with SMTP id 586e51a60fabf-2e9a38d2a3aso2721830fac.3 for ; Sun, 22 Jun 2025 09:57:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1750611454; x=1751216254; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=wkkfRhd9J02sbGAJC4JooBoALgE7g6iRqWgPcah3RJc=; b=HrqwOkL9M0rrrLEWKMg5x+efjI11CRi+Fwcv2v7jLEU0nnQeByZ7FCW9DRkUdfEXOj fkuUqgiIu/G0NipTkdjiJi05ncxbzZHi5YMyDhzdAIXstXcB9aWb+PbmkolNNzELZaBT 0qnT3hAOGWmwlKbao074P47jw/AwN3DFZXEkQzsy6tmnjDg1SJkaI7IChm69rs7FvC1s 4JA5XrPIjlLzIoQQmsGHe+YjBswL9xT4WZT3t9/R2zwAF7YPcPTCMUt/8xP6juK1YU7y H4UPDHPUki+S6vTvfGv6vaCDbMet0qm3Wc3bFlGuhT+Z5lSeY7EjUCrGRmYSm/Uhcg2m b9vg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1750611454; x=1751216254; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=wkkfRhd9J02sbGAJC4JooBoALgE7g6iRqWgPcah3RJc=; b=CxkZ4vvwBbw3NaROTvToM0IafGaGu8rLqqZ8N4DznDrfErBVz/PAgeoOHNzQzLC5eN M5qF0DN2kABzYP2qE8PzSmATLn6rXW8LCVd109Z8NJDXcniYiQPeLCaTF0SGFMq7flfZ qUf4kkK9oeneGIAFpKp2TVXF0kNVDIGMk+ylJ3o5vwbuuUmU78F9pNwKoT7L8b2knby1 fKBEeMpfmFTpeu5Y+ITL96UuqSGV5B2h4mqRBR7ZQ8kE1rhgxerGmg6d7dtxqqqWR4l+ UJuHYMM/9zKX+hU9C6WyLo6Da5uzcbKv43cWAwgquiBKmwZNjJAn5yAQBRfwmnkxVDQk hOeQ== X-Gm-Message-State: AOJu0YxdHYR0w4aOmO/HjagORzcQ/eOeM3qKKvqyol2UcWo/uSFKDaId +wsWcVJm7WRq+zqUlbL0ySgNGCAdj9oo1Liobw0bTp923fzPuWxLwLg8WUVU72yo/eeNhpsrwvN yWgwZECX4TH6UyTQxi/04MRlstUUvcZoNLg== X-Gm-Gg: ASbGncsaSeB9AhRln+nUosgUwy/GuDCMG09ZGO7ep1q6d3/4oOQOsYazcoTzaKC0U24 IM9esMQreuOTOKbyMciqMMhKiIKrNKKsMtgw6cK1C+hyp6eaU2c7B1EzCbWNG4Be1nJYxqQ0X1U 0UVxk15aq+6n26O4wnrI99SNjMeIK4c1jR+7/+066q2QSj X-Google-Smtp-Source: AGHT+IECZXhbr9dAOAP5ICdyD7fLTdUfzfCrnoooxwiSLEUR0iLrQYrS1UpE3DP9karA+tP2wHhhgn4w8XLo4/Eyj6k= X-Received: by 2002:a05:6870:830e:b0:2e8:7471:6350 with SMTP id 586e51a60fabf-2eeda561139mr6512846fac.1.1750611454303; Sun, 22 Jun 2025 09:57:34 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Sun, 22 Jun 2025 12:57:23 -0400 X-Gm-Features: Ac12FXykIXxmosHOz0vDigBFQQ5rJqjFA4VjTbgkSRzZEl-a9HIcv11tlgwpvb4 Message-ID: Subject: Re: pg_restore Question To: Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000dfa29106382bfd3d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000dfa29106382bfd3d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable It would be handy if pg_class had created_on timestamp, created_by oid, altered_on timestamp, altered_by oid fields, but alas they don't exist. On Sun, Jun 22, 2025 at 6:52=E2=80=AFAM Edwin UY wrote= : > Yes, Samurai Jack, I mean Ron --- just kidding. That is my preference too= . > When you work with several people who are 'Senior' DBA, it's difficult to > go to a debate / argument of sort that we should be doing it like this :( > Will continue to check things around. > Kinda hoping there are some kind of timestamps when a table / index gets > created. > > P.S.: > I really wish I can properly learn PostgreSQL hands-on in the real world > as a remote intern somewhere. > > On Sun, Jun 22, 2025 at 9:58=E2=80=AFPM Ron Johnson > wrote: > >> This is why I do all backups, restores, upgrades, etc through cron. >> >> On Sat, Jun 21, 2025 at 8:59=E2=80=AFAM Furkan Shaikh wrote: >> >>> >>> - >>> >>> *No Definitive Proof:* Without logs, you cannot get a timestamped >>> log entry saying "pg_restore started/finished." All these methods pr= ovide >>> indirect evidence. >>> - >>> >>> *Requires Prior Knowledge:* Most effective indicators rely on you >>> having some memory or previous records of the database's state (e.g.= , >>> typical sequence values, expected bloat, average last-vacuum times). >>> - >>> >>> *Other Causes:* Some of these patterns (like recent statistics) >>> could also be caused by an aggressive VACUUM FULL, a major data >>> import through other means, or an application bug that resets sequen= ces. >>> >>> Conclusion >>> >>> The most reliable indicators without direct logs are a *sudden and >>> uniform resetting of last_vacuum/last_analyze timestamps to NULL or ver= y >>> recent values across all user tables*, combined with a potential change >>> in object OIDs (if you tracked them) or unexpected sequence values. If = you >>> see most of your tables >>> >>> On Sat, 21 Jun, 2025, 3:41=E2=80=AFpm Edwin UY, wr= ote: >>> >>>> Hi, >>>> >>>> Without access to the dumpfile or log file, is there any way to check >>>> whether a database has been restore either by pg_restore or other mean= s? >>>> >>>> Regards, >>>> Edd >>>> >>> --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000dfa29106382bfd3d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

It would be handy if pg_cl= ass had created_on timestamp, created_by oid, altered_on timestamp, altered= _by oid fields, but alas they don't exist.=C2=A0
=
On Sun, Jun 22, 2025 at 6:52=E2=80=AFAM Edwin UY= <edwin.uy@gmail.com> wrote= :
Yes, Samurai Jack, I= mean Ron --- just kidding.=C2=A0That is my preference too.=C2=A0
When you work with several people who are 'Senior' DBA, it's d= ifficult to go to a debate / argument of sort that we should be doing it li= ke this :( Will continue to check things around.
Kinda hoping the= re are some kind of timestamps when a table / index gets created.=C2=A0

P.S.:
I really wish I can properly learn Po= stgreSQL hands-on in the real world as a remote intern somewhere.

On S= un, Jun 22, 2025 at 9:58=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com> wrote= :
This is why I do all backups, restores, upgrades, etc through cron.=

On Sat, Jun 21, 2025 at 8:59=E2=80=AF= AM Furkan Shaikh <fs626261@gmail.com> wrote:

  • No Definitive Proof:=C2= =A0Without logs, you cannot get a timestamped log entry saying "pg_res= tore started/finished." All these methods provide=C2=A0indirect=C2=A0evidence.

  • Requires Prior Knowledge:=C2=A0Most effective indicators= rely on you having some memory or previous records of the database's s= tate (e.g., typical sequence values, expected bloat, average last-vacuum ti= mes).

  • Other Causes:=C2=A0Some o= f these patterns (like recent statistics) could also be caused by an aggres= sive=C2=A0VACUUM FULL, a major data import through other means, or an application bug that = resets sequences.

Conclusion

The mos= t reliable indicators without direct logs are a=C2=A0sudden and uni= form resetting of=C2=A0= last_vacuum/last= _analyze=C2=A0timestamps to=C2=A0NULL=C2=A0or very recent values across=C2=A0all=C2=A0user tables, combined w= ith a potential change in object OIDs (if you tracked them) or unexpected s= equence values. If you see most of your tables


On Sat, 21 Jun, 2025, 3:4= 1=E2=80=AFpm Edwin UY, <edwin.uy@gmail.com> wrote:
Hi,

<= /span>
Without access to th= e dumpfile or log file, is there any way to check whether=C2=A0a database h= as been restore either by pg_restore or other means?

Regards,
Edd
=
=

--=
Dea= th to <Redacted>, and butter sauce.
Don't boil me, I'm st= ill alive.
<Redacted> lobster!
--000000000000dfa29106382bfd3d--