public inbox for [email protected]
help / color / mirror / Atom feedpg_restore Question
7+ messages / 5 participants
[nested] [flat]
* pg_restore Question
@ 2025-06-21 10:10 Edwin UY <[email protected]>
2025-06-21 12:58 ` Re: pg_restore Question Furkan Shaikh <[email protected]>
2025-06-22 09:55 ` Re: pg_restore Question vrms <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Edwin UY @ 2025-06-21 10:10 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore Question
2025-06-21 10:10 pg_restore Question Edwin UY <[email protected]>
@ 2025-06-21 12:58 ` Furkan Shaikh <[email protected]>
2025-06-22 09:57 ` Re: pg_restore Question Ron Johnson <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Furkan Shaikh @ 2025-06-21 12:58 UTC (permalink / raw)
To: Edwin UY <[email protected]>; +Cc: Pgsql-admin <[email protected]>
-
*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 pm Edwin UY, <[email protected]> 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
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore Question
2025-06-21 10:10 pg_restore Question Edwin UY <[email protected]>
2025-06-21 12:58 ` Re: pg_restore Question Furkan Shaikh <[email protected]>
@ 2025-06-22 09:57 ` Ron Johnson <[email protected]>
2025-06-22 10:52 ` Re: pg_restore Question Edwin UY <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2025-06-22 09:57 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
This is why I do all backups, restores, upgrades, etc through cron.
On Sat, Jun 21, 2025 at 8:59 AM Furkan Shaikh <[email protected]> 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 you
> see most of your tables
>
> On Sat, 21 Jun, 2025, 3:41 pm Edwin UY, <[email protected]> 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
>>
>>
>>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore Question
2025-06-21 10:10 pg_restore Question Edwin UY <[email protected]>
2025-06-21 12:58 ` Re: pg_restore Question Furkan Shaikh <[email protected]>
2025-06-22 09:57 ` Re: pg_restore Question Ron Johnson <[email protected]>
@ 2025-06-22 10:52 ` Edwin UY <[email protected]>
2025-06-22 16:57 ` Re: pg_restore Question Ron Johnson <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Edwin UY @ 2025-06-22 10:52 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>
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 PM Ron Johnson <[email protected]> wrote:
> This is why I do all backups, restores, upgrades, etc through cron.
>
> On Sat, Jun 21, 2025 at 8:59 AM Furkan Shaikh <[email protected]> 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 you
>> see most of your tables
>>
>> On Sat, 21 Jun, 2025, 3:41 pm Edwin UY, <[email protected]> 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
>>>
>>>
>>>
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore Question
2025-06-21 10:10 pg_restore Question Edwin UY <[email protected]>
2025-06-21 12:58 ` Re: pg_restore Question Furkan Shaikh <[email protected]>
2025-06-22 09:57 ` Re: pg_restore Question Ron Johnson <[email protected]>
2025-06-22 10:52 ` Re: pg_restore Question Edwin UY <[email protected]>
@ 2025-06-22 16:57 ` Ron Johnson <[email protected]>
2025-06-22 17:51 ` Re: pg_restore Question Rui DeSousa <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2025-06-22 16:57 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
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 protected]> 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 PM Ron Johnson <[email protected]>
> wrote:
>
>> This is why I do all backups, restores, upgrades, etc through cron.
>>
>> On Sat, Jun 21, 2025 at 8:59 AM Furkan Shaikh <[email protected]> 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 you
>>> see most of your tables
>>>
>>> On Sat, 21 Jun, 2025, 3:41 pm Edwin UY, <[email protected]> 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
>>>>
>>>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore Question
2025-06-21 10:10 pg_restore Question Edwin UY <[email protected]>
2025-06-21 12:58 ` Re: pg_restore Question Furkan Shaikh <[email protected]>
2025-06-22 09:57 ` Re: pg_restore Question Ron Johnson <[email protected]>
2025-06-22 10:52 ` Re: pg_restore Question Edwin UY <[email protected]>
2025-06-22 16:57 ` Re: pg_restore Question Ron Johnson <[email protected]>
@ 2025-06-22 17:51 ` Rui DeSousa <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Rui DeSousa @ 2025-06-22 17:51 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: Pgsql-admin <[email protected]>
> On Jun 22, 2025, at 12:57 PM, Ron Johnson <[email protected]> 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)
Attachments:
[application/octet-stream] audit.sql (2.6K, 3-audit.sql)
download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_restore Question
2025-06-21 10:10 pg_restore Question Edwin UY <[email protected]>
@ 2025-06-22 09:55 ` vrms <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: vrms @ 2025-06-22 09:55 UTC (permalink / raw)
To: [email protected]
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.
On 21.06.25 12:10, 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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-06-22 17:51 UTC | newest]
Thread overview: 7+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-06-21 10:10 pg_restore Question Edwin UY <[email protected]>
2025-06-21 12:58 ` Furkan Shaikh <[email protected]>
2025-06-22 09:57 ` Ron Johnson <[email protected]>
2025-06-22 10:52 ` Edwin UY <[email protected]>
2025-06-22 16:57 ` Ron Johnson <[email protected]>
2025-06-22 17:51 ` Rui DeSousa <[email protected]>
2025-06-22 09:55 ` vrms <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox