pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedAbout backups
26+ messages / 8 participants
[nested] [flat]
* About backups
@ 2026-01-26 16:01 felix.quintgz@yahoo.com
0 siblings, 2 replies; 26+ messages in thread
From: felix.quintgz@yahoo.com @ 2026-01-26 16:01 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Is there a way to implement the SQL Server command 'BACKUP DATABASE'?
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?
Thank you very much.
Regards
Félix
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: About backups
@ 2026-01-26 16:05 Laurenz Albe <laurenz.albe@cybertec.at>
parent: felix.quintgz@yahoo.com
1 sibling, 0 replies; 26+ messages in thread
From: Laurenz Albe @ 2026-01-26 16:05 UTC (permalink / raw)
To: felix.quintgz@yahoo.com; pgsql-general@lists.postgresql.org
On Mon, 2026-01-26 at 16:01 +0000, felix.quintgz@yahoo.com wrote:
> Is there a way to implement the SQL Server command 'BACKUP DATABASE'?
Probably not. What does it do?
> Is there a way to see the restores performed on a database?
Maybe your backup system has a log?
> Is there an equivalent table to msdb.dbo.restorehistory in SQL Server?
Judging from the name, no.
> Is there a way to implement an equivalent if one doesn't exist?
I don't think so.
Different database management system work in different ways, sorry.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: About backups
@ 2026-01-26 16:10 Adrian Klaver <adrian.klaver@aklaver.com>
parent: felix.quintgz@yahoo.com
1 sibling, 2 replies; 26+ messages in thread
From: Adrian Klaver @ 2026-01-26 16:10 UTC (permalink / raw)
To: felix.quintgz@yahoo.com; pgsql-general@lists.postgresql.org
On 1/26/26 08:01, felix.quintgz@yahoo.com 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
>
> 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?
>
> Thank you very much.
> Regards
>
> Félix
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: About backups
@ 2026-01-26 16:53 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 1 reply; 26+ messages in thread
From: Ron Johnson @ 2026-01-26 16:53 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Mon, Jan 26, 2026 at 11:11 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
> On 1/26/26 08:01, felix.quintgz@yahoo.com 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] 26+ messages in thread
* Re: About backups
@ 2026-01-26 17:44 felix.quintgz@yahoo.com
parent: Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 1 reply; 26+ messages in thread
From: felix.quintgz@yahoo.com @ 2026-01-26 17:44 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Any method that allows me to know who accessed the database and when.
This is necessary for auditing the database. It's a requirement for financial applications.
I can't use a table within the database because it gets overwritten upon restoration.
I don't have admin access to the database server; in SQL Server, I resolved this using signed stored procedures.
On Monday, January 26, 2026 at 11:10:50 AM GMT-5, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 1/26/26 08:01, felix.quintgz@yahoo.com 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
>
> 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?
>
> Thank you very much.
> Regards
>
> Félix
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: About backups
@ 2026-01-26 17:52 felix.quintgz@yahoo.com
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 26+ messages in thread
From: felix.quintgz@yahoo.com @ 2026-01-26 17:52 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
This is a requirement for financial applications; the user can perform a backup whenever they want, but they can't access the database.
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 <ronljohnsonjr@gmail.com> wrote:
On Mon, Jan 26, 2026 at 11:11 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 1/26/26 08:01, felix.quintgz@yahoo.com 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] 26+ messages in thread
* Re: About backups
@ 2026-01-26 18:04 Christophe Pettus <xof@thebuild.com>
parent: felix.quintgz@yahoo.com
1 sibling, 1 reply; 26+ messages in thread
From: Christophe Pettus @ 2026-01-26 18:04 UTC (permalink / raw)
To: felix.quintgz@yahoo.com; +Cc: pgsql-general@lists.postgresql.org
> On Jan 26, 2026, at 09:52, felix.quintgz@yahoo.com 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.
> This is a requirement for financial applications; the user can perform a backup whenever they want, but they can't access the database.
> 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.
There is currently no supported way of backing up a PostgreSQL database via an SQL command. You could, in theory, use the COPY command to dump each individual table, but that's probably not what you are looking for (since it would also require a fairly sophisticated restore process).
pg_dump can run in the application container, and connect to the database in the database container, just like the application does. There's no requirement that pg_dump run on the database host. That's probably the best direction in this case.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: About backups
@ 2026-01-26 18:22 Ron Johnson <ronljohnsonjr@gmail.com>
parent: felix.quintgz@yahoo.com
1 sibling, 1 reply; 26+ messages in thread
From: Ron Johnson @ 2026-01-26 18:22 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Mon, Jan 26, 2026 at 12:52 PM <felix.quintgz@yahoo.com> 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 <
> ronljohnsonjr@gmail.com> wrote:
>
> On Mon, Jan 26, 2026 at 11:11 AM Adrian Klaver <adrian.klaver@aklaver.com>
> wrote:
> On 1/26/26 08:01, felix.quintgz@yahoo.com 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] 26+ messages in thread
* Re: About backups
@ 2026-01-26 18:30 felix.quintgz@yahoo.com
parent: Christophe Pettus <xof@thebuild.com>
0 siblings, 1 reply; 26+ messages in thread
From: felix.quintgz@yahoo.com @ 2026-01-26 18:30 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Can I copy pg_dump directly into the container?
Or is there an installer just for the PostgreSQL utilities?
On Monday, January 26, 2026 at 01:04:17 PM GMT-5, Christophe Pettus <xof@thebuild.com> wrote:
> On Jan 26, 2026, at 09:52, felix.quintgz@yahoo.com 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.
> This is a requirement for financial applications; the user can perform a backup whenever they want, but they can't access the database.
> 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.
There is currently no supported way of backing up a PostgreSQL database via an SQL command. You could, in theory, use the COPY command to dump each individual table, but that's probably not what you are looking for (since it would also require a fairly sophisticated restore process).
pg_dump can run in the application container, and connect to the database in the database container, just like the application does. There's no requirement that pg_dump run on the database host. That's probably the best direction in this case.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: About backups
@ 2026-01-26 18:37 felix.quintgz@yahoo.com
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: felix.quintgz@yahoo.com @ 2026-01-26 18:37 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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 <ronljohnsonjr@gmail.com> wrote:
On Mon, Jan 26, 2026 at 12:52 PM <felix.quintgz@yahoo.com> 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 <ronljohnsonjr@gmail.com> wrote:
On Mon, Jan 26, 2026 at 11:11 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 1/26/26 08:01, felix.quintgz@yahoo.com 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] 26+ messages in thread
* Re: About backups
@ 2026-01-26 18:40 Christophe Pettus <xof@thebuild.com>
parent: felix.quintgz@yahoo.com
0 siblings, 0 replies; 26+ messages in thread
From: Christophe Pettus @ 2026-01-26 18:40 UTC (permalink / raw)
To: felix.quintgz@yahoo.com; +Cc: pgsql-general@lists.postgresql.org
> On Jan 26, 2026, at 10:30, felix.quintgz@yahoo.com wrote:
>
> Can I copy pg_dump directly into the container?
>
> Or is there an installer just for the PostgreSQL utilities?
The postgresql-client-18 (or whichever version you are using) has the client utilities but not the server.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: About backups
@ 2026-01-26 18:42 Christophe Pettus <xof@thebuild.com>
parent: felix.quintgz@yahoo.com
0 siblings, 2 replies; 26+ messages in thread
From: Christophe Pettus @ 2026-01-26 18:42 UTC (permalink / raw)
To: felix.quintgz@yahoo.com; +Cc: pgsql-general@lists.postgresql.org
> On Jan 26, 2026, at 10:37, felix.quintgz@yahoo.com 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] 26+ messages in thread
* Re: About backups
@ 2026-01-26 19:23 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Christophe Pettus <xof@thebuild.com>
1 sibling, 0 replies; 26+ messages in thread
From: Ron Johnson @ 2026-01-26 19:23 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Mon, Jan 26, 2026 at 1:42 PM Christophe Pettus <xof@thebuild.com> wrote:
>
>
> > On Jan 26, 2026, at 10:37, felix.quintgz@yahoo.com 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] 26+ messages in thread
* Re: About backups
@ 2026-01-26 19:24 felix.quintgz@yahoo.com
parent: Christophe Pettus <xof@thebuild.com>
1 sibling, 3 replies; 26+ messages in thread
From: felix.quintgz@yahoo.com @ 2026-01-26 19:24 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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 <xof@thebuild.com> wrote:
> On Jan 26, 2026, at 10:37, felix.quintgz@yahoo.com 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] 26+ messages in thread
* Re: About backups
@ 2026-01-26 19:30 Ron Johnson <ronljohnsonjr@gmail.com>
parent: felix.quintgz@yahoo.com
2 siblings, 1 reply; 26+ messages in thread
From: Ron Johnson @ 2026-01-26 19:30 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Mon, Jan 26, 2026 at 2:25 PM <felix.quintgz@yahoo.com> 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] 26+ messages in thread
* Re: About backups
@ 2026-01-26 19:46 felix.quintgz@yahoo.com
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 26+ messages in thread
From: felix.quintgz@yahoo.com @ 2026-01-26 19:46 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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 <ronljohnsonjr@gmail.com> wrote:
On Mon, Jan 26, 2026 at 2:25 PM <felix.quintgz@yahoo.com> 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] 26+ messages in thread
* Re: About backups
@ 2026-01-26 20:02 Adrian Klaver <adrian.klaver@aklaver.com>
parent: felix.quintgz@yahoo.com
2 siblings, 1 reply; 26+ messages in thread
From: Adrian Klaver @ 2026-01-26 20:02 UTC (permalink / raw)
To: felix.quintgz@yahoo.com; pgsql-general@lists.postgresql.org
On 1/26/26 11:24, felix.quintgz@yahoo.com 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 <xof@thebuild.com> wrote:
>> On Jan 26, 2026, at 10:37, felix.quintgz@yahoo.com 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
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: About backups
@ 2026-01-26 20:11 Christophe Pettus <xof@thebuild.com>
parent: felix.quintgz@yahoo.com
2 siblings, 0 replies; 26+ messages in thread
From: Christophe Pettus @ 2026-01-26 20:11 UTC (permalink / raw)
To: felix.quintgz@yahoo.com; +Cc: pgsql-general@lists.postgresql.org
> On Jan 26, 2026, at 11:24, felix.quintgz@yahoo.com 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] 26+ messages in thread
* Re: About backups
@ 2026-01-26 20:16 felix.quintgz@yahoo.com
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 26+ messages in thread
From: felix.quintgz@yahoo.com @ 2026-01-26 20:16 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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 <adrian.klaver@aklaver.com> wrote:
On 1/26/26 11:24, felix.quintgz@yahoo.com 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 <xof@thebuild.com> wrote:
>> On Jan 26, 2026, at 10:37, felix.quintgz@yahoo.com 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
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: About backups
@ 2026-01-27 02:59 rob stone <floriparob@tpg.com.au>
parent: felix.quintgz@yahoo.com
0 siblings, 1 reply; 26+ messages in thread
From: rob stone @ 2026-01-27 02:59 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On Mon, 2026-01-26 at 17:44 +0000, felix.quintgz@yahoo.com wrote:
>
> Any method that allows me to know who accessed the database and when.
>
> This is necessary for auditing the database. It's a requirement for
> financial applications.
> I can't use a table within the database because it gets overwritten
> upon restoration.
>
>
A user has access to the application and logs on. You record that in a
table of successful log-ons. You also need a table of unsuccessful log-
on attempts. E.g, mis-typed password, access window expired, etc.
None of that data is lost when a database restore occurs.
You haven't said if you intend doing hot backups or cold backups.
Have you read Chapter 25 of the documentation?
>I don't have admin access to the database server; in SQL Server, I
>resolved this using signed stored procedures.
Most IT departments have a person known as the DBA. They are involved
in the design of the database to fit the application and after it goes
live are usually responsible for checking the back-ups.
You haven't stated what your role is with the development of this
application.
Rob
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: About backups
@ 2026-01-28 20:28 felix.quintgz@yahoo.com
parent: rob stone <floriparob@tpg.com.au>
0 siblings, 2 replies; 26+ messages in thread
From: felix.quintgz@yahoo.com @ 2026-01-28 20:28 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
The application is used in environments where there are no dba; in fact, the requirements specify that a dba cannot have access to the database, so signed records are used, trigger to prevent data changes, protection of protection triggers, etc.
Too many cases of theft.
My role is DBA and developer at my organization, but the application is distributed to other companies.
The old application has been running unattended for over 15 years without problems in many companies without a DBA.
The certification body simply said it was written in a very outdated language and revoked its certification. Now I'm reprogramming it in a modern language and with a different database.
Almost all of my work has been with SQL Server and desktop applications.
Cold and hot backups have taken me by surprise.
In SQL Server, I could perform a backup in the middle of user activity without any problems, so from what I had read about pg_dump, it was the same, but now I have many doubts.
What happens if I start a backup in the middle of a user transaction?
The transaction can end before or after the backup ends, and it can also start before or after the backup begins.
On Monday, January 26, 2026 at 10:00:23 PM GMT-5, rob stone <floriparob@tpg.com.au> wrote:
On Mon, 2026-01-26 at 17:44 +0000, felix.quintgz@yahoo.com wrote:
>
> Any method that allows me to know who accessed the database and when.
>
> This is necessary for auditing the database. It's a requirement for
> financial applications.
> I can't use a table within the database because it gets overwritten
> upon restoration.
>
>
A user has access to the application and logs on. You record that in a
table of successful log-ons. You also need a table of unsuccessful log-
on attempts. E.g, mis-typed password, access window expired, etc.
None of that data is lost when a database restore occurs.
You haven't said if you intend doing hot backups or cold backups.
Have you read Chapter 25 of the documentation?
>I don't have admin access to the database server; in SQL Server, I
>resolved this using signed stored procedures.
Most IT departments have a person known as the DBA. They are involved
in the design of the database to fit the application and after it goes
live are usually responsible for checking the back-ups.
You haven't stated what your role is with the development of this
application.
Rob
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: About backups
@ 2026-01-28 20:29 Christophe Pettus <xof@thebuild.com>
parent: felix.quintgz@yahoo.com
1 sibling, 0 replies; 26+ messages in thread
From: Christophe Pettus @ 2026-01-28 20:29 UTC (permalink / raw)
To: felix.quintgz@yahoo.com; +Cc: pgsql-general@lists.postgresql.org
> On Jan 28, 2026, at 12:28, felix.quintgz@yahoo.com wrote:
>
> What happens if I start a backup in the middle of a user transaction?
> The transaction can end before or after the backup ends, and it can also start before or after the backup begins.
pg_dump takes a snapshot at the start of the dump, so the dump is consistent at a transaction boundary. Of course, only transactions that have already committed at the time of the snapshot will appear in the dump.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: About backups
@ 2026-01-28 20:31 Guillaume Lelarge <guillaume.lelarge@dalibo.com>
parent: felix.quintgz@yahoo.com
1 sibling, 1 reply; 26+ messages in thread
From: Guillaume Lelarge @ 2026-01-28 20:31 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 28/01/2026 21:28, felix.quintgz@yahoo.com wrote:
> The application is used in environments where there are no dba; in fact, the requirements specify that a dba cannot have access to the database, so signed records are used, trigger to prevent data changes, protection of protection triggers, etc.
> Too many cases of theft.
>
> My role is DBA and developer at my organization, but the application is distributed to other companies.
> The old application has been running unattended for over 15 years without problems in many companies without a DBA.
> The certification body simply said it was written in a very outdated language and revoked its certification. Now I'm reprogramming it in a modern language and with a different database.
> Almost all of my work has been with SQL Server and desktop applications.
>
> Cold and hot backups have taken me by surprise.
> In SQL Server, I could perform a backup in the middle of user activity without any problems, so from what I had read about pg_dump, it was the same, but now I have many doubts.
>
> What happens if I start a backup in the middle of a user transaction?
> The transaction can end before or after the backup ends, and it can also start before or after the backup begins.
>
Doesn't matter at all. You'll get a consistent backup.
--
Guillaume Lelarge
Consultant
https://dalibo.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: About backups
@ 2026-01-29 17:58 PetSerAl <petseral@gmail.com>
parent: Guillaume Lelarge <guillaume.lelarge@dalibo.com>
0 siblings, 1 reply; 26+ messages in thread
From: PetSerAl @ 2026-01-29 17:58 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On Thu, Jan 28, 2026 at 8:32 PM Guillaume Lelarge
<guillaume.lelarge@dalibo.com> wrote:
> Doesn't matter at all. You'll get a consistent backup.
But be aware of not MVCC-safe commands like TRUNCATE.
If transaction with such command intersect with beginning of backup,
then it may be not consistent.
It will see effects of TRUNCATE, but will not see effects of other
commands in such transaction.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: About backups
@ 2026-01-29 18:37 Adrian Klaver <adrian.klaver@aklaver.com>
parent: PetSerAl <petseral@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Adrian Klaver @ 2026-01-29 18:37 UTC (permalink / raw)
To: PetSerAl <petseral@gmail.com>; pgsql-general@lists.postgresql.org
On 1/29/26 9:58 AM, PetSerAl wrote:
> On Thu, Jan 28, 2026 at 8:32 PM Guillaume Lelarge
> <guillaume.lelarge@dalibo.com> wrote:
>> Doesn't matter at all. You'll get a consistent backup.
>
> But be aware of not MVCC-safe commands like TRUNCATE.
> If transaction with such command intersect with beginning of backup,
> then it may be not consistent.
> It will see effects of TRUNCATE, but will not see effects of other
> commands in such transaction.
>
>
From here:
https://www.postgresql.org/docs/current/mvcc-caveats.html
"Some DDL commands, currently only TRUNCATE and the table-rewriting
forms of ALTER TABLE, are not MVCC-safe. This means that after the
truncation or rewrite commits, the table will appear empty to concurrent
transactions, if they are using a snapshot taken before the DDL command
committed. This will only be an issue for a transaction that did not
access the table in question before the DDL command started — any
transaction that has done so would hold at least an ACCESS SHARE table
lock, which would block the DDL command until that transaction completes."
And the more general case described here:
https://www.postgresql.org/message-id/14781.1266626391@sss.pgh.pa.us
"
> My questions are: can making DDL changes during a dump cause this
error? Are the queries used by pg_dump transactionally consistent, i.e.
do they run in a transaction and get a single view of the database
system catalogs? Other than finer coordination of jobs, how can this
situation be avoided?
...
The window for this sort of thing isn't very large, because the first
thing pg_dump does is acquire AccessShareLock on every table it intends
to dump, and past that point it won't be possible for anyone to modify
the table's DDL. But it can happen.
...
"
There is a small window for this happening in any case. Read the rest of
the case for suggestions to mitigate.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: About backups
@ 2026-01-29 19:04 PetSerAl <petseral@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 26+ messages in thread
From: PetSerAl @ 2026-01-29 19:04 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org
On Thu, Jan 29, 2026 at 9:37 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> The window for this sort of thing isn't very large,
That window can be arbitrary large. It includes time waiting for locks
to be released from tables of interest.
--to reduce deadlocks take strongest lock first
--TRUNCATE requare ACCESS EXCLUSIVE
LOCK tablename;
--large amount of work
SELECT pg_sleep(10);
TRUNCATE tablename;
Now you have +10 seconds for the window for tablename and all
following tables in lock order.
IMHO, hidden data loss from TRUNCATE is much more sinister, than error
from ALTER TABLE.
^ permalink raw reply [nested|flat] 26+ messages in thread
end of thread, other threads:[~2026-01-29 19:04 UTC | newest]
Thread overview: 26+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-26 16:01 About backups felix.quintgz@yahoo.com
2026-01-26 16:05 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-01-26 16:10 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-01-26 16:53 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-01-26 17:52 ` felix.quintgz@yahoo.com
2026-01-26 18:04 ` Christophe Pettus <xof@thebuild.com>
2026-01-26 18:30 ` felix.quintgz@yahoo.com
2026-01-26 18:40 ` Christophe Pettus <xof@thebuild.com>
2026-01-26 18:22 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-01-26 18:37 ` felix.quintgz@yahoo.com
2026-01-26 18:42 ` Christophe Pettus <xof@thebuild.com>
2026-01-26 19:23 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-01-26 19:24 ` felix.quintgz@yahoo.com
2026-01-26 19:30 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-01-26 19:46 ` felix.quintgz@yahoo.com
2026-01-26 20:02 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-01-26 20:16 ` felix.quintgz@yahoo.com
2026-01-26 20:11 ` Christophe Pettus <xof@thebuild.com>
2026-01-26 17:44 ` felix.quintgz@yahoo.com
2026-01-27 02:59 ` rob stone <floriparob@tpg.com.au>
2026-01-28 20:28 ` felix.quintgz@yahoo.com
2026-01-28 20:29 ` Christophe Pettus <xof@thebuild.com>
2026-01-28 20:31 ` Guillaume Lelarge <guillaume.lelarge@dalibo.com>
2026-01-29 17:58 ` PetSerAl <petseral@gmail.com>
2026-01-29 18:37 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-01-29 19:04 ` PetSerAl <petseral@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox