public inbox for [email protected]help / color / mirror / Atom feed
Re: About backups 10+ messages / 7 participants [nested] [flat]
* Re: About backups @ 2026-01-26 16:10 Adrian Klaver <[email protected]> 0 siblings, 2 replies; 10+ messages in thread From: Adrian Klaver @ 2026-01-26 16:10 UTC (permalink / raw) To: [email protected]; [email protected] 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 > > 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 [email protected] ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: About backups @ 2026-01-26 16:53 Ron Johnson <[email protected]> parent: Adrian Klaver <[email protected]> 1 sibling, 0 replies; 10+ messages in thread From: Ron Johnson @ 2026-01-26 16:53 UTC (permalink / raw) To: pgsql-generallists.postgresql.org <[email protected]> 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 17:44 [email protected] parent: Adrian Klaver <[email protected]> 1 sibling, 1 reply; 10+ messages in thread From: [email protected] @ 2026-01-26 17:44 UTC (permalink / raw) To: [email protected] 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 <[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 > > 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 [email protected] ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: About backups @ 2026-01-27 02:59 rob stone <[email protected]> parent: [email protected] 0 siblings, 1 reply; 10+ messages in thread From: rob stone @ 2026-01-27 02:59 UTC (permalink / raw) To: [email protected] On Mon, 2026-01-26 at 17:44 +0000, [email protected] 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] 10+ messages in thread
* Re: About backups @ 2026-01-28 20:28 [email protected] parent: rob stone <[email protected]> 0 siblings, 2 replies; 10+ messages in thread From: [email protected] @ 2026-01-28 20:28 UTC (permalink / raw) To: [email protected] 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 <[email protected]> wrote: On Mon, 2026-01-26 at 17:44 +0000, [email protected] 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] 10+ messages in thread
* Re: About backups @ 2026-01-28 20:29 Christophe Pettus <[email protected]> parent: [email protected] 1 sibling, 0 replies; 10+ messages in thread From: Christophe Pettus @ 2026-01-28 20:29 UTC (permalink / raw) To: [email protected]; +Cc: [email protected] > On Jan 28, 2026, at 12:28, [email protected] 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] 10+ messages in thread
* Re: About backups @ 2026-01-28 20:31 Guillaume Lelarge <[email protected]> parent: [email protected] 1 sibling, 1 reply; 10+ messages in thread From: Guillaume Lelarge @ 2026-01-28 20:31 UTC (permalink / raw) To: [email protected] On 28/01/2026 21:28, [email protected] 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] 10+ messages in thread
* Re: About backups @ 2026-01-29 17:58 PetSerAl <[email protected]> parent: Guillaume Lelarge <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: PetSerAl @ 2026-01-29 17:58 UTC (permalink / raw) To: [email protected] On Thu, Jan 28, 2026 at 8:32 PM Guillaume Lelarge <[email protected]> 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] 10+ messages in thread
* Re: About backups @ 2026-01-29 18:37 Adrian Klaver <[email protected]> parent: PetSerAl <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: Adrian Klaver @ 2026-01-29 18:37 UTC (permalink / raw) To: PetSerAl <[email protected]>; [email protected] On 1/29/26 9:58 AM, PetSerAl wrote: > On Thu, Jan 28, 2026 at 8:32 PM Guillaume Lelarge > <[email protected]> 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/[email protected] " > 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] 10+ messages in thread
* Re: About backups @ 2026-01-29 19:04 PetSerAl <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: PetSerAl @ 2026-01-29 19:04 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: [email protected] On Thu, Jan 29, 2026 at 9:37 PM Adrian Klaver <[email protected]> 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] 10+ messages in thread
end of thread, other threads:[~2026-01-29 19:04 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 16:10 Re: About backups Adrian Klaver <[email protected]> 2026-01-26 16:53 ` Ron Johnson <[email protected]> 2026-01-26 17:44 ` [email protected] 2026-01-27 02:59 ` rob stone <[email protected]> 2026-01-28 20:28 ` [email protected] 2026-01-28 20:29 ` Christophe Pettus <[email protected]> 2026-01-28 20:31 ` Guillaume Lelarge <[email protected]> 2026-01-29 17:58 ` PetSerAl <[email protected]> 2026-01-29 18:37 ` Adrian Klaver <[email protected]> 2026-01-29 19:04 ` PetSerAl <[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