Subject: pg_restore Question

From: Edwin UY <[email-masked]>
Date: Sat, 21 Jun 2025 22:10:52 +1200

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

---

From: Furkan Shaikh <[email-masked]>
Date: Sat, 21 Jun 2025 18:28:39 +0530

-

   *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

---

From: vrms <[email-masked]>
Date: Sun, 22 Jun 2025 11:55:06 +0200

the bash history might give you an idea.
You could not find anything though whether you did a restore from within 
the psql console, but that has a history too.

---

From: Ron Johnson <[email-masked]>
Date: Sun, 22 Jun 2025 05:57:37 -0400

This is why I do all backups, restores, upgrades, etc through cron.

On Sat, Jun 21, 2025 at 8:59 AM Furkan Shaikh <[email-masked]> wrote:

>    *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).
>    -
>
>    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
>

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Edwin UY <[email-masked]>
Date: Sun, 22 Jun 2025 22:52:00 +1200

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.

---

From: Ron Johnson <[email-masked]>
Date: Sun, 22 Jun 2025 12:57:23 -0400

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 AM Edwin UY <[email-masked]> wrote:

> I really wish I can properly learn PostgreSQL hands-on in the real world
> as a remote intern somewhere.
>
>>>    *No Definitive Proof:* Without logs, you cannot get a timestamped
>>>    log entry saying "pg_restore started/finished." All these methods provide
>>>    indirect evidence.
>>>    -
>>>
>>>    *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
>>>

---

From: Rui DeSousa <[email-masked]>
Date: Sun, 22 Jun 2025 13:51:06 -0400

> On Jun 22, 2025, at 12:57 PM, Ron Johnson <[email-masked]> wrote:
> 
> 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. 
> 

Here’s a script that will achieve just that; including when the table is dropped.

Regards,
Rui

demo=# \i audit.sql 
CREATE SCHEMA
CREATE EXTENSION
SET
CREATE TABLE
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE EVENT TRIGGER
CREATE EVENT TRIGGER
CREATE TABLE
demo=# create table xo(i int);
CREATE TABLE
demo=# alter table xo add column d text;
ALTER TABLE
demo=# drop table xo;
DROP TABLE
demo=# select * from ddl_audit;
 audit_id | transaction_xact |       transaction_time        | inet_addr  | application | session  |                sql_id                |     command     | object_type |           object_name           
----------+------------------+-------------------------------+------------+-------------+----------+--------------------------------------+-----------------+-------------+---------------------------------
        1 |            27156 | 2025-06-22 12:43:13.139543-04 | dead::beef | psql        | postgres | eaf15c1c-881a-5982-ab98-c8ceb14163cf | alter sequence  | sequence    | dba.ddl_audit_audit_id_seq
        2 |            27156 | 2025-06-22 12:43:13.139543-04 | dead::beef | psql        | postgres | eaf15c1c-881a-5982-ab98-c8ceb14163cf | create sequence | sequence    | dba.ddl_audit_audit_id_seq
        3 |            27156 | 2025-06-22 12:43:13.139543-04 | dead::beef | psql        | postgres | eaf15c1c-881a-5982-ab98-c8ceb14163cf | create table    | table       | dba.ddl_audit
        4 |            27156 | 2025-06-22 12:43:13.139543-04 | dead::beef | psql        | postgres | eaf15c1c-881a-5982-ab98-c8ceb14163cf | create index    | index       | dba.ddl_audit_pkey
        5 |            27157 | 2025-06-22 12:43:22.64344-04  | dead::beef | psql        | postgres | 85cca161-e608-52e8-b1f7-077b71af28b5 | create table    | table       | dba.xo
        6 |            27158 | 2025-06-22 12:43:38.602159-04 | dead::beef | psql        | postgres | 49721262-8953-588f-a587-9791fabbe326 | alter table     | table       | dba.xo
        7 |            27159 | 2025-06-22 12:43:42.338722-04 | dead::beef | psql        | postgres | 036fb819-e8d9-5030-8071-8dc3a78bc0eb | drop table      | table       | dba.xo
        8 |            27159 | 2025-06-22 12:43:42.338722-04 | dead::beef | psql        | postgres | 036fb819-e8d9-5030-8071-8dc3a78bc0eb | drop table      | type        | dba.xo
        9 |            27159 | 2025-06-22 12:43:42.338722-04 | dead::beef | psql        | postgres | 036fb819-e8d9-5030-8071-8dc3a78bc0eb | drop table      | type        | dba.xo[]
       10 |            27159 | 2025-06-22 12:43:42.338722-04 | dead::beef | psql        | postgres | 036fb819-e8d9-5030-8071-8dc3a78bc0eb | drop table      | toast table | pg_toast.pg_toast_1109086
       11 |            27159 | 2025-06-22 12:43:42.338722-04 | dead::beef | psql        | postgres | 036fb819-e8d9-5030-8071-8dc3a78bc0eb | drop table      | index       | pg_toast.pg_toast_1109086_index
(11 rows)

demo=# select * from sql_audit;
                sql_id                |          created_on           |                                         sql                                          
--------------------------------------+-------------------------------+--------------------------------------------------------------------------------------
 eaf15c1c-881a-5982-ab98-c8ceb14163cf | 2025-06-22 12:43:13.139543-04 | create table if not exists ddl_audit (                                              +
                                      |                               |   audit_id bigint generated always as identity primary key                          +
                                      |                               |   , transaction_xact xid8 not null default pg_current_xact_id()                     +
                                      |                               |   , transaction_time timestamptz not null default transaction_timestamp()           +
                                      |                               |   , inet_addr inet not null default coalesce(inet_client_addr(), 'dead::beef'::inet)+
                                      |                               |   , application text not null default current_setting('application_name')           +
                                      |                               |   , session name not null default session_user                                      +
                                      |                               |   , sql_id uuid not null default sql_id(current_query())                            +
                                      |                               |   , command varchar(25) not null                                                    +
                                      |                               |   , object_type varchar(20) not null                                                +
                                      |                               |   , object_name text                                                                +
                                      |                               | );
 85cca161-e608-52e8-b1f7-077b71af28b5 | 2025-06-22 12:43:22.64344-04  | create table xo(i int);
 49721262-8953-588f-a587-9791fabbe326 | 2025-06-22 12:43:38.602159-04 | alter table xo add column d text;
 036fb819-e8d9-5030-8071-8dc3a78bc0eb | 2025-06-22 12:43:42.338722-04 | drop table xo;
(4 rows)