Proposal to define a single datetime to be used in all filenames across a snapshot

[stephane-klein]

Hello,

I'm writing scripts to automate some PostgreSQL database restore tasks based on pg_back.
Here's what my process roughly looks like.

I've implemented a script that lists the snapshots present on Object Storage:

$ ./scripts/list_postgresql_dump_backup_snapshots.sh
myproject 2025-04-15T14:34:02Z
myproject 2025-04-15T16:47:46Z
otherproject 2025-04-15T14:44:46Z
foobar 2025-04-15T14:45:50Z
...

I then have another script that restores a specific instance of PostgreSQL:

$ ./scripts/restore_myproject_postgres.sh "2025-04-17T08:05:07Z"

However, I'm encountering a problem, the dates of a "snapshot" may be different:

$ tree
/var/backups/postgresql/
└── myproject
 ├── hba_file_2025-04-17T08:05:07Z.out
 ├── hba_file_2025-04-17T08:05:07Z.out.age
 ├── ident_file_2025-04-17T08:05:07Z.out
 ├── ident_file_2025-04-17T08:05:07Z.out.age
 ├── pg_globals_2025-04-17T08:05:07Z.sql
 ├── pg_globals_2025-04-17T08:05:07Z.sql.age
 ├── pg_settings_2025-04-17T08:05:07Z.out
 ├── pg_settings_2025-04-17T08:05:07Z.out.age
 ├── postgres_2025-04-17T08:05:08Z.dump
 └── postgres_2025-04-17T08:05:08Z.dump.age

In this example, 2025-04-17T08:05:07Z is different from 2025-04-17T08:05:08Z.

Why this difference? Because the following functions execute time.Now() independently:

• here for dump
• here for dumpGlobals
• here for dumpSettings
• here for dumpConfigFiles

Potentially, all files can have a different datetime.

What I propose in this issue is to generate a datetime with time.Now() at the start of the run function and then use this value in the dump, dumpGlobals, dumpSettings and dumpConfigFiles functions.

Tradeoff: the datetime indicated in the postgres_....dump file name would no longer be the precise date when pg_dump was started. I don't think this is very important.

What do you think?

I'm thinking of submitting a Pull Request later today.

Best regards,
Stéphane

[stephane-klein]

I'm thinking of submitting a Pull Request later today.

Here it is: #147

[orgrim]

Hi,

I think having the closest date to the actual run of the dump is important, for example if one has a cluster with 10 x 200 GB databases, the actual date of the last dump may be way later than the datetime in the filename.

You use case makes sense, what about adding a new option like --fixed-dump-times to implement it?

(another approch would be to use prefixes, I've never tried with timestamps as prefix though)

Regards,

[stephane-klein]

You use case makes sense, what about adding a new option like --fixed-dump-times to implement it?

@orgrim ok, I'll implement this option 👍

[stephane-klein]

You use case makes sense, what about adding a new option like --fixed-dump-times to implement it?

I believe I understand that --fixed-dump-times is a suggestion from you and not a definitive requirement.

I've taken the liberty to think about several naming alternatives that could describe this functionality. Here are some proposals:

• --uniform-snapshot-timestamp
• --set-single-filename-timestamp-by-snapshot
• --uniform-snapshot-filename-timestamp
• --snapshot-wide-timestamp
• --uniform-timestamp
• --common-file-timestamp

I intend to start the implementation with --uniform-snapshot-timestamp, which seems to be a good compromise between precision and conciseness.

However, I remain completely flexible regarding this choice.

@orgrim What do you think about --uniform-snapshot-timestamp ?

Would you prefer that I keep your initial proposal --fixed-dump-times or does one of the suggested alternatives seem more appropriate to you?

[stephane-klein]

I intend to start the implementation with --uniform-snapshot-timestamp, which seems to be a good compromise between precision and conciseness.

It's implemented, see this comment: #147 (comment)

[orgrim]

Hi,

I prefer --fixed-dump-times or --uniform-timestamp. IMO, pg_back creates dumps, not snapshots (I feel that "snapshot" represents a higher level idea that can use pg_back for the dump part) so introducing the word snapshot might be confusing.

Regards

[stephane-klein]

I prefer --fixed-dump-times or --uniform-timestamp. IMO, pg_back creates dumps, not snapshots (I feel that "snapshot" represents a higher level idea that can use pg_back for the dump part) so introducing the word snapshot might be confusing.

@orgrim I've updated the Pull Request #147.

I've replaced --uniform-snapshot-timestamp with --uniform-timestamp.

Does this work for you?