public inbox for [email protected]
help / color / mirror / Atom feedRe: About backups
10+ messages / 4 participants
[nested] [flat]
* Re: About backups
@ 2026-01-26 18:22 Ron Johnson <[email protected]>
2026-01-26 18:37 ` Re: About backups [email protected]
0 siblings, 1 reply; 10+ messages in thread
From: Ron Johnson @ 2026-01-26 18:22 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
On Mon, Jan 26, 2026 at 12:52 PM <[email protected]> wrote:
> I'm having a problem with this. I'm repurposing an old application written
> in Visual Basic 6 that did allow backups through signed stored procedures.
You must change your expectations and way of thinking. *Postgresql is not
SQL Server*, and thus cannot be managed the same way as SQL Server. That
is a fact of life which you must accept.
> This is a requirement for financial applications; the user can perform a
> backup whenever they want, but they can't access the database.
>
"ssh to a Linux account dedicated to pgbackrest" within the application is
my first thought. Note, though, that pgbackrest does not have BACKUP
DATABASE's COPY_ONLY feature. If you need that, pg_dump is your
only option.
> The new application is web-based, deployed in containers, and the database
> server container is not the same as the application's, so I can't use
> pg_dump in the application, or at least I don't know how to do it.
>
> On Monday, January 26, 2026 at 12:31:48 PM GMT-5, Ron Johnson <
> [email protected]> wrote:
>
> On Mon, Jan 26, 2026 at 11:11 AM Adrian Klaver <[email protected]>
> wrote:
> On 1/26/26 08:01, [email protected] wrote:
>
> > Is there a way to implement the SQL Server command 'BACKUP DATABASE'?
> Not from within the Postgres instance.
> You will need to use:
>
> https://www.postgresql.org/docs/current/app-pgdump.html
> Felix, pg_dump is a logical export tuned for speed and multithreading.
> Almost certainly not what you want.
> pgbackrest is the equivalent of BACKUP DATABASE and BACKUP LOG. It's an
> external program (stuffing everything in the database engine is not The
> Unix Way) which typically you run from cron. Redrirect stdout and stderr to
> a log file with a timestamp in the name. (That, at least, is what I've
> been doing for 8 years. It works perfectly.)
> pgbackrest also has an "info" option which gives you details of all the
> backups currently in the repository. >
>
> > Is there a way to see the restores performed on a database?
>
> > Is there an equivalent table to msdb.dbo.restorehistory in SQL Server?
>
> > Is there a way to implement an equivalent if one doesn't exist?
>
> From what I understand there are various ways of doing this in SQL
>
> Server, which way are you interested in?
> --
> Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
> <Redacted> lobster!
>
>
>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: About backups
2026-01-26 18:22 Re: About backups Ron Johnson <[email protected]>
@ 2026-01-26 18:37 ` [email protected]
2026-01-26 18:42 ` Re: About backups Christophe Pettus <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: [email protected] @ 2026-01-26 18:37 UTC (permalink / raw)
To: [email protected]
I can't change my expectations. It's either you do it or I won't certify you, and you won't be able to use the application.
That's how a certification body works, and there's nothing I can do about it.
On Monday, January 26, 2026 at 01:23:05 PM GMT-5, Ron Johnson <[email protected]> wrote:
On Mon, Jan 26, 2026 at 12:52 PM <[email protected]> wrote:I'm having a problem with this. I'm repurposing an old application written in Visual Basic 6 that did allow backups through signed stored procedures.
You must change your expectations and way of thinking. Postgresql is not SQL Server, and thus cannot be managed the same way as SQL Server. That is a fact of life which you must accept. This is a requirement for financial applications; the user can perform a backup whenever they want, but they can't access the database.
"ssh to a Linux account dedicated to pgbackrest" within the application is my first thought. Note, though, that pgbackrest does not have BACKUP DATABASE's COPY_ONLY feature. If you need that, pg_dump is your only option.
The new application is web-based, deployed in containers, and the database server container is not the same as the application's, so I can't use pg_dump in the application, or at least I don't know how to do it.
On Monday, January 26, 2026 at 12:31:48 PM GMT-5, Ron Johnson <[email protected]> wrote:
On Mon, Jan 26, 2026 at 11:11 AM Adrian Klaver <[email protected]> wrote:
On 1/26/26 08:01, [email protected] wrote:
> Is there a way to implement the SQL Server command 'BACKUP DATABASE'?
Not from within the Postgres instance.
You will need to use:
https://www.postgresql.org/docs/current/app-pgdump.html
Felix, pg_dump is a logical export tuned for speed and multithreading. Almost certainly not what you want.
pgbackrest is the equivalent of BACKUP DATABASE and BACKUP LOG. It's an external program (stuffing everything in the database engine is not The Unix Way) which typically you run from cron. Redrirect stdout and stderr to a log file with a timestamp in the name. (That, at least, is what I've been doing for 8 years. It works perfectly.)
pgbackrest also has an "info" option which gives you details of all the backups currently in the repository. >
> Is there a way to see the restores performed on a database?
> Is there an equivalent table to msdb.dbo.restorehistory in SQL Server?
> Is there a way to implement an equivalent if one doesn't exist?
From what I understand there are various ways of doing this in SQL
Server, which way are you interested in?
--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: About backups
2026-01-26 18:22 Re: About backups Ron Johnson <[email protected]>
2026-01-26 18:37 ` Re: About backups [email protected]
@ 2026-01-26 18:42 ` Christophe Pettus <[email protected]>
2026-01-26 19:23 ` Re: About backups Ron Johnson <[email protected]>
2026-01-26 19:24 ` Re: About backups [email protected]
0 siblings, 2 replies; 10+ messages in thread
From: Christophe Pettus @ 2026-01-26 18:42 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
> On Jan 26, 2026, at 10:37, [email protected] wrote:
>
> I can't change my expectations. It's either you do it or I won't certify you, and you won't be able to use the application.
> That's how a certification body works, and there's nothing I can do about it.
Can you articulate the specific requirement? I assume it's not "the database can be backed up completely by issuing an SQL command." If we know what the precise requirement is, we might be able to provide more specific guidance.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: About backups
2026-01-26 18:22 Re: About backups Ron Johnson <[email protected]>
2026-01-26 18:37 ` Re: About backups [email protected]
2026-01-26 18:42 ` Re: About backups Christophe Pettus <[email protected]>
@ 2026-01-26 19:23 ` Ron Johnson <[email protected]>
1 sibling, 0 replies; 10+ messages in thread
From: Ron Johnson @ 2026-01-26 19:23 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
On Mon, Jan 26, 2026 at 1:42 PM Christophe Pettus <[email protected]> wrote:
>
>
> > On Jan 26, 2026, at 10:37, [email protected] wrote:
> >
> > I can't change my expectations. It's either you do it or I won't certify
> you, and you won't be able to use the application.
> > That's how a certification body works, and there's nothing I can do
> about it.
>
> Can you articulate the specific requirement? I assume it's not "the
> database can be backed up completely by issuing an SQL command."
But it is! That's exactly how you -- and IIRC *the only* way to -- backup
SQL Server databases (typically from Agent, which is like pg_cron but much
more featureful, and completely integrated into SSMS and SQL Server).
Every CLI or GUI method of doing a backup calls BACKUP DATABASE and/or
BACKUP LOG behind the scenes.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: About backups
2026-01-26 18:22 Re: About backups Ron Johnson <[email protected]>
2026-01-26 18:37 ` Re: About backups [email protected]
2026-01-26 18:42 ` Re: About backups Christophe Pettus <[email protected]>
@ 2026-01-26 19:24 ` [email protected]
2026-01-26 19:30 ` Re: About backups Ron Johnson <[email protected]>
2026-01-26 20:02 ` Re: About backups Adrian Klaver <[email protected]>
2026-01-26 20:11 ` Re: About backups Christophe Pettus <[email protected]>
1 sibling, 3 replies; 10+ messages in thread
From: [email protected] @ 2026-01-26 19:24 UTC (permalink / raw)
To: [email protected]
A full database backup is the requirement. A database restore is optional, but that's negotiable; the backup is not.
All of this must be done without access to the server or the database itself, solely through the application, and the user must have the necessary permissions within the application.
The postgresql-client option is probably the right one.
I still have the restore history part to do, but the main thing is the backup.
On Monday, January 26, 2026 at 01:42:55 PM GMT-5, Christophe Pettus <[email protected]> wrote:
> On Jan 26, 2026, at 10:37, [email protected] wrote:
>
> I can't change my expectations. It's either you do it or I won't certify you, and you won't be able to use the application.
> That's how a certification body works, and there's nothing I can do about it.
Can you articulate the specific requirement? I assume it's not "the database can be backed up completely by issuing an SQL command." If we know what the precise requirement is, we might be able to provide more specific guidance.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: About backups
2026-01-26 18:22 Re: About backups Ron Johnson <[email protected]>
2026-01-26 18:37 ` Re: About backups [email protected]
2026-01-26 18:42 ` Re: About backups Christophe Pettus <[email protected]>
2026-01-26 19:24 ` Re: About backups [email protected]
@ 2026-01-26 19:30 ` Ron Johnson <[email protected]>
2026-01-26 19:46 ` Re: About backups [email protected]
2 siblings, 1 reply; 10+ messages in thread
From: Ron Johnson @ 2026-01-26 19:30 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
On Mon, Jan 26, 2026 at 2:25 PM <[email protected]> wrote:
> A full database backup is the requirement. A database restore is optional,
> but that's negotiable;
Lol that's a big fat fail.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: About backups
2026-01-26 18:22 Re: About backups Ron Johnson <[email protected]>
2026-01-26 18:37 ` Re: About backups [email protected]
2026-01-26 18:42 ` Re: About backups Christophe Pettus <[email protected]>
2026-01-26 19:24 ` Re: About backups [email protected]
2026-01-26 19:30 ` Re: About backups Ron Johnson <[email protected]>
@ 2026-01-26 19:46 ` [email protected]
0 siblings, 0 replies; 10+ messages in thread
From: [email protected] @ 2026-01-26 19:46 UTC (permalink / raw)
To: [email protected]
I stopped arguing with the bureaucracy many years ago; I never won.
If they want a 10,000-page report, they'll get it, and that last part was a real case that happened to me.
On Monday, January 26, 2026 at 02:30:40 PM GMT-5, Ron Johnson <[email protected]> wrote:
On Mon, Jan 26, 2026 at 2:25 PM <[email protected]> wrote:
A full database backup is the requirement. A database restore is optional, but that's negotiable;
Lol that's a big fat fail.
--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: About backups
2026-01-26 18:22 Re: About backups Ron Johnson <[email protected]>
2026-01-26 18:37 ` Re: About backups [email protected]
2026-01-26 18:42 ` Re: About backups Christophe Pettus <[email protected]>
2026-01-26 19:24 ` Re: About backups [email protected]
@ 2026-01-26 20:02 ` Adrian Klaver <[email protected]>
2026-01-26 20:16 ` Re: About backups [email protected]
2 siblings, 1 reply; 10+ messages in thread
From: Adrian Klaver @ 2026-01-26 20:02 UTC (permalink / raw)
To: [email protected]; [email protected]
On 1/26/26 11:24, [email protected] wrote:
> A full database backup is the requirement. A database restore is optional, but that's negotiable; the backup is not.
> All of this must be done without access to the server or the database itself, solely through the application, and the user must have the necessary permissions within the application.
> > The postgresql-client option is probably the right one.
FYI, in Postgres 17+ you have in the client program pg_basebackup the
option to do incremental backups.
In addition there are third party tools that offer more options for backups:
https://pgbarman.org/
https://pgbackrest.org/
> I still have the restore history part to do, but the main thing is the backup.
>
> On Monday, January 26, 2026 at 01:42:55 PM GMT-5, Christophe Pettus <[email protected]> wrote:
>> On Jan 26, 2026, at 10:37, [email protected] wrote:
>>
>> I can't change my expectations. It's either you do it or I won't certify you, and you won't be able to use the application.
>> That's how a certification body works, and there's nothing I can do about it.
>
> Can you articulate the specific requirement? I assume it's not "the database can be backed up completely by issuing an SQL command." If we know what the precise requirement is, we might be able to provide more specific guidance.
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: About backups
2026-01-26 18:22 Re: About backups Ron Johnson <[email protected]>
2026-01-26 18:37 ` Re: About backups [email protected]
2026-01-26 18:42 ` Re: About backups Christophe Pettus <[email protected]>
2026-01-26 19:24 ` Re: About backups [email protected]
2026-01-26 20:02 ` Re: About backups Adrian Klaver <[email protected]>
@ 2026-01-26 20:16 ` [email protected]
0 siblings, 0 replies; 10+ messages in thread
From: [email protected] @ 2026-01-26 20:16 UTC (permalink / raw)
To: [email protected]
Thank you all so much.
With what they've given me, I can continue in my job.
On Monday, January 26, 2026 at 03:02:45 PM GMT-5, Adrian Klaver <[email protected]> wrote:
On 1/26/26 11:24, [email protected] wrote:
> A full database backup is the requirement. A database restore is optional, but that's negotiable; the backup is not.
> All of this must be done without access to the server or the database itself, solely through the application, and the user must have the necessary permissions within the application.
> > The postgresql-client option is probably the right one.
FYI, in Postgres 17+ you have in the client program pg_basebackup the
option to do incremental backups.
In addition there are third party tools that offer more options for backups:
https://pgbarman.org/
https://pgbackrest.org/
> I still have the restore history part to do, but the main thing is the backup.
>
> On Monday, January 26, 2026 at 01:42:55 PM GMT-5, Christophe Pettus <[email protected]> wrote:
>> On Jan 26, 2026, at 10:37, [email protected] wrote:
>>
>> I can't change my expectations. It's either you do it or I won't certify you, and you won't be able to use the application.
>> That's how a certification body works, and there's nothing I can do about it.
>
> Can you articulate the specific requirement? I assume it's not "the database can be backed up completely by issuing an SQL command." If we know what the precise requirement is, we might be able to provide more specific guidance.
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: About backups
2026-01-26 18:22 Re: About backups Ron Johnson <[email protected]>
2026-01-26 18:37 ` Re: About backups [email protected]
2026-01-26 18:42 ` Re: About backups Christophe Pettus <[email protected]>
2026-01-26 19:24 ` Re: About backups [email protected]
@ 2026-01-26 20:11 ` Christophe Pettus <[email protected]>
2 siblings, 0 replies; 10+ messages in thread
From: Christophe Pettus @ 2026-01-26 20:11 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
> On Jan 26, 2026, at 11:24, [email protected] wrote:
>
> A full database backup is the requirement. A database restore is optional, but that's negotiable; the backup is not.
> All of this must be done without access to the server or the database itself, solely through the application, and the user must have the necessary permissions within the application.
In that case, running pg_dump on the application server is probably the way to go. pg_dump can produce a single file that can be used to do a full restore, and it's smaller than an equivalent bindary backup (since it includes index definitions, but not the contents of the index itself). Of course, if it's a 100GB database, you'll end up with a huge file no matter what, but nothing to do about that.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2026-01-26 20:16 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-26 18:22 Re: About backups Ron Johnson <[email protected]>
2026-01-26 18:37 ` [email protected]
2026-01-26 18:42 ` Christophe Pettus <[email protected]>
2026-01-26 19:23 ` Ron Johnson <[email protected]>
2026-01-26 19:24 ` [email protected]
2026-01-26 19:30 ` Ron Johnson <[email protected]>
2026-01-26 19:46 ` [email protected]
2026-01-26 20:02 ` Adrian Klaver <[email protected]>
2026-01-26 20:16 ` [email protected]
2026-01-26 20:11 ` Christophe Pettus <[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