public inbox for [email protected]help / color / mirror / Atom feed
pg_restore Question 7+ messages / 5 participants [nested] [flat]
* pg_restore Question @ 2025-06-21 10:10 Edwin UY <[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 12:58 Furkan Shaikh <[email protected]> parent: Edwin UY <[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-22 09:55 vrms <[email protected]> parent: Edwin UY <[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
* Re: pg_restore Question @ 2025-06-22 09:57 Ron Johnson <[email protected]> parent: Furkan Shaikh <[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-22 10:52 Edwin UY <[email protected]> parent: 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-22 16:57 Ron Johnson <[email protected]> parent: Edwin UY <[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-22 17:51 Rui DeSousa <[email protected]> parent: Ron Johnson <[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
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