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 1uSxoB-00FSPF-At for pgsql-admin@arkaria.postgresql.org; Sat, 21 Jun 2025 12:58:59 +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 1uSxo6-007Mql-P3 for pgsql-admin@arkaria.postgresql.org; Sat, 21 Jun 2025 12:58:55 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uSxo6-007Mqc-79 for pgsql-admin@lists.postgresql.org; Sat, 21 Jun 2025 12:58:55 +0000 Received: from mail-pg1-x536.google.com ([2607:f8b0:4864:20::536]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uSxo5-003EgT-0W for pgsql-admin@lists.postgresql.org; Sat, 21 Jun 2025 12:58:53 +0000 Received: by mail-pg1-x536.google.com with SMTP id 41be03b00d2f7-b271f3ae786so2081826a12.3 for ; Sat, 21 Jun 2025 05:58:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1750510732; x=1751115532; 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=7yT8TKI1hyN7pClOEBwdtKTwndrJUGrBKXXQ0o6itdw=; b=labg5l1U8tkY0Z3muKNOmiJJshnj+cyOuirMalsNmC93j+Zan5PDoUo61pr8QFy/fC IpkPWX5UftaWwiDm9K+r7VVeGCap1eJPk11jJTXD0/UfyK/IZiLsrIpp5lEUP4e2YA/1 0Ob7RbRYBFXe3RMXHSdDwS9Y+oCP7DtHreBcPasJB5kbe0YRSxclnvmSB1pBbF2pgSDL fMBCMXUPRPuPh5JNq6aZ00rrt1xYIesvHlj1Xakc9CA7mXfJjnNoJisenEzc6pXqSRTE jDQWfcKm3Kjhr0wRh/FHiKB0OP1IYce8Bc0HOlN3JSVmot6MvzzKICPKOCOXiQD+ybUV 3AYw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1750510732; x=1751115532; 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=7yT8TKI1hyN7pClOEBwdtKTwndrJUGrBKXXQ0o6itdw=; b=bKKAQ/Fb9FDAULSnWRq678l7ouoBJA7V5wsm2kJpZquioVF8JpDJbLmJXoKwYYR3OZ yshab2BgIzDs0j0EdXwvPgbnWXzqT9jyjybj+VHjzNzrMmWJYDPM2zjWn2yJ5whtDMmc aaH82TTmP7fN+voJcwyweiIAw1kiVuzXntEw+OPSKEJYfB1QRJ5CKQzE2D4FI0Yf7kpR ZoKgGx2HDnpu9oWEFuISbBwXn+VRf7H5HlsT2vXIeoERjrHDtogU+0NJoWG2wh0RSuKl NWvrGRUbdILNWtONZMlVFMqybsY90+KGUmmJZrvz9bTVZd3qHG+Bvb4pLSHWvHsj5RIk 7BCg== X-Gm-Message-State: AOJu0YzwyLPv1Ah6SYpnOFCjSLME6AZjNPw/iiLbu2io2Zqi6Vt4CxZQ 7v7F3N45GfIXaO2TdX7QKyFqM1uLPkkW3u9wN2UTQU7kgxsIQ42flV3hgHGNGvedqOP+M/Ofc4n ESUoZuEK6C1KyO9Zn+MixyD/i6nfCrTBoHZdqLhulp4M= X-Gm-Gg: ASbGnctnbIY3W60yHIs42yv0ZymHEU7hNVLHQDm4/WdARMdQyOwoZ2UzFUHut4CJo0E O3mqO6D1BwVc6fOZY71eZ3hj8+6Mxw91Gp9V/Html24hZqPjmb5WFgcyo/f9qJ/0+YucfdIgO8c MKOy9fI5e19XJREG+v+2v7g+HZemkIHTIrXih4HEHUeTO1AOBbLAlEMfI6VT8= X-Google-Smtp-Source: AGHT+IGGoo0KkGzz2PFC0kXWBdznsGqCoj/zz3xM/g7hYwTZoGY1OFt0amENh20A2bfKDPTSVBEcK100kYI1coJicA8= X-Received: by 2002:a17:90b:274d:b0:311:ed2:b758 with SMTP id 98e67ed59e1d1-3159d629e62mr9859598a91.3.1750510731619; Sat, 21 Jun 2025 05:58:51 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Furkan Shaikh Date: Sat, 21 Jun 2025 18:28:39 +0530 X-Gm-Features: AX0GCFtTIJPerY856uz7cxPAmeR5BpeVXnmjRA-dbw960sdAKgLTLUsJWs4Y3hA Message-ID: Subject: Re: pg_restore Question To: Edwin UY Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="00000000000055725f0638148a89" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000055725f0638148a89 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable - *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 you see most of your tables On Sat, 21 Jun, 2025, 3:41=E2=80=AFpm Edwin UY, wrote: > 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 > > > --00000000000055725f0638148a89 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

  • = No Definitive Proof:=C2=A0Without logs, you cannot get a timestamp= ed log entry saying "pg_restore started/finished." All these meth= ods provide=C2=A0indirect=C2=A0evi= dence.

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

  • Ot= her Causes:=C2=A0Some of these patterns (like recent statistics) c= ould also be caused by an aggressive=C2=A0VACUUM FULL, a major data import through other me= ans, or an application bug that resets sequences.

Conclusion

The most reliable indicators without direct logs a= re a=C2=A0sudden and uniform resetting of=C2=A0last_vacuum/last_analyze=C2=A0timestamps to=C2=A0NULL=C2=A0or very r= ecent values across=C2=A0all=C2=A0= user tables, combined with a potential change in object OIDs (if y= ou tracked them) or unexpected sequence values. If you see most of your tab= les


On Sat, 21 Jun, 2025, 3:41=E2=80=AFpm Edwin U= Y, <edwin.uy@gmail.com> wro= te:
Hi,

Wi= thout access to the dumpfile or log file, is there any way to check whether= =C2=A0a database has been restore either by pg_restore or other means?

Re= gards,
Edd


=
--00000000000055725f0638148a89--