Обсуждение: pg_restore Question
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
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
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.
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
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, <edwin.uy@gmail.com> 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
This is why I do all backups, restores, upgrades, etc through cron.On Sat, Jun 21, 2025 at 8:59 AM Furkan Shaikh <fs626261@gmail.com> 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, <edwin.uy@gmail.com> 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!
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 <ronljohnsonjr@gmail.com> wrote:This is why I do all backups, restores, upgrades, etc through cron.On Sat, Jun 21, 2025 at 8:59 AM Furkan Shaikh <fs626261@gmail.com> 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, <edwin.uy@gmail.com> 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
On Jun 22, 2025, at 12:57 PM, Ron Johnson <ronljohnsonjr@gmail.com> 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.