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 1uTIJZ-002X2F-H9 for pgsql-admin@arkaria.postgresql.org; Sun, 22 Jun 2025 10:52:45 +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 1uTIJW-00CN56-6j for pgsql-admin@arkaria.postgresql.org; Sun, 22 Jun 2025 10:52:42 +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 1uTIJV-00CN4v-Oy for pgsql-admin@lists.postgresql.org; Sun, 22 Jun 2025 10:52:42 +0000 Received: from mail-pl1-x62d.google.com ([2607:f8b0:4864:20::62d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uTIJU-003SfK-0F for pgsql-admin@lists.postgresql.org; Sun, 22 Jun 2025 10:52:41 +0000 Received: by mail-pl1-x62d.google.com with SMTP id d9443c01a7336-2352400344aso32677655ad.2 for ; Sun, 22 Jun 2025 03:52:39 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1750589557; x=1751194357; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=FWVkXu10PjUAUJrE4tXc3k5wEDo51ZjvPX31m1FyJE0=; b=Zz/LWL1vWq9zzbNgBdWY1QVJhSDyxXCQ6zRbTXonjwXrS5exCmBVTTNMJyZoyV3guC aCzRVIGhJV0Dgp9vAqsMK7mftCh86b9Xg21A+p0/4ciJIy842pSdISsoziH6KxSddTuk J00+YLDAxWkw4VD/a/ZqnAbUTAgQp0gR+Lipdnqkcyb5ygn41pJN6ciCiATgEWKURCiC 9gzKQOjSSQxqvVPmR0JcIv3FijNZZj0uHovS3D8u7lbpqixeRdexcRuMfsFGEmsEn850 v/SrFWj+8RHeO3/1d6YksEAYLXu35vwL+69FxEWoFGzX6q2z7Ih1EjqGiwxuaYYKR4p7 5CEg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1750589557; x=1751194357; h=cc: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=FWVkXu10PjUAUJrE4tXc3k5wEDo51ZjvPX31m1FyJE0=; b=V2s86EoBmQoFZDbvMhlBXh0cWV+/ZK1l1l21DgX1chdpCVZizmdYBF4hWAlxq8tPXc 1OVFhr9H2UBsWG9glyG0vhxPN6rDN1DrbJ+64agtiaIIZ6hgAK3aCqvEPPZsC9uKRzZ8 d0MSZzPxvcrrisntKx9/7WTaTWPkjpotAvmMJ3nCSX3SW36E9UmrNu9M5xWZMMGj/+oh 7RDLBvzDu2aWkplCUeXvMfiQtzZqUEINwZ6u+nf6q5hqE1d474OuOt4LswhAWWXttse0 kP8KtsdQ2XtQpuiHgT13rZFZJqJg0Mm5Kr80YI/I0EXSr1zibjulBVuA52l3P1f7I4lL UF3Q== X-Gm-Message-State: AOJu0YybkJGVVaXhwOk2SjP8SK3pDwAtaIjaoy4jwjUbXDsIJjXm+Nsq uUKEujAyqYvaQzzhnL3U1+kuFbW5fLN/XbF7cgQ7JXzP7jZ/Dys+Srx7Nii1dT1JnZGMYVWx8ue veA6jb9QGAeu6dS/lj/TGfO4E9nBEYFo= X-Gm-Gg: ASbGncvTAIerYh6dHujzTgcehAJF0NNhLvL4uTYwGBJNjDdX+rbCW51RFqD9CJFKLOD pn+ciavERLBX486z4yUWMp/Ib9sOiqZc/S5NwsB0SVBogKpNZryklJI3CeZnOxDxGLhZ4gDmERt uemLmv9Fxw7NNBZ4mgb5e/cSdUJtCXHPAGdrrr4l+WLsA= X-Google-Smtp-Source: AGHT+IFXmgwdIlMP1OMkJyH+3lAszMb5cxI06Lw8c8uCkVItKPHVo3NYDyQU3Mx0/vsoxMrr4RPfTdZr7fJIM2Qvzrc= X-Received: by 2002:a17:902:d541:b0:235:f3e6:4680 with SMTP id d9443c01a7336-237d989699dmr132488225ad.21.1750589557459; Sun, 22 Jun 2025 03:52:37 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Edwin UY Date: Sun, 22 Jun 2025 22:52:00 +1200 X-Gm-Features: Ac12FXw1eEMO62gK6ZCk8tAKZgM_WDupNMHddqSGkmb-9WXmaj-CIB5Ea3FNaNE Message-ID: Subject: Re: pg_restore Question To: Ron Johnson Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000b85147063826e429" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b85147063826e429 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 provide >> 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 sequences. >> >> Conclusion >> >> The most reliable indicators without direct logs are a *sudden and >> uniform resetting of last_vacuum/last_analyze timestamps to NULL or very >> recent values across all user tables*, combined with a potential change >> in object OIDs (if you tracked them) or unexpected sequence values. If y= ou >> see most of your tables >> >> On Sat, 21 Jun, 2025, 3:41=E2=80=AFpm Edwin UY, wro= te: >> >>> 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 means= ? >>> >>> Regards, >>> Edd >>> >>> >>> > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --000000000000b85147063826e429 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Yes, Samurai Jack, I mean Ron --- just kidding.=C2=A0= That is my preference too.=C2=A0
When you work with several peopl= e who are 'Senior' DBA, it's difficult to go to a debate / argu= ment of sort that we should be doing it like this :( Will continue to check= things around.
Kinda hoping there are some kind of timestamps wh= en a table / index gets created.=C2=A0

P.S.:
=
I really wish I can properly learn PostgreSQL hands-on in the real wor= ld as a remote intern somewhere.

On Sun, 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=AFAM Furkan Shaikh <fs626261@gmail.com>= wrote:

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

  • Requires Prior Kno= wledge:=C2=A0Most effective indicators rely on you having some mem= ory or previous records of the database's state (e.g., typical sequence= values, expected bloat, average last-vacuum times).

  • Other Causes:=C2=A0Some of these patterns (like recen= t statistics) could also be caused by an aggressive=C2=A0VACUUM FULL, a major data import t= hrough other means, or an application bug that resets sequences.

    • Conclusion

    The most reliable indicators withou= t direct logs are a=C2=A0sudden and uniform resetting of=C2=A0last_vacuum/last_analyze=C2=A0timesta= mps to=C2=A0NULL= =C2=A0or very recent values across=C2=A0a= ll=C2=A0user tables, combined with a potential change in ob= ject OIDs (if you tracked them) or unexpected sequence values. If you see m= ost of your tables


On Sat, 21 Jun, 2025, 3:41=E2=80=AFpm Edwin UY, <<= a href=3D"mailto:edwin.uy@gmail.com" target=3D"_blank">edwin.uy@gmail.com> wrote:
Hi,
<= div>
Without access to the dumpfile or log file, is t= here any way to check whether=C2=A0a database has been restore either by pg= _restore or other means?

Regards,
Edd
=



--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--000000000000b85147063826e429--