I'm running Percona MySQL 8.0.42-33 on RHEL 9.6. I have a problem when attempting point-in-time-recovery, where I can restore the database from the mysqldump backup, but the application of the binlog isn't updating anything in the database. There are no errors, and verbose output shows the statements being executed, but there are no changes in the database. At first I thought this could be specific to the database I was attempting to restore, but running similar tests in our sandbox instance produced the same result. This is using a replica server that has replication stopped, and I reset the replica and master data before starting this process if that makes any difference. Example Steps 1. create database test03 2. mysqldump --all-databases --log-error BACKUP_LOG --max-allowed-packet=1G --single-transaction --verbose --flush-logs --source-data=2 | gzip > BACKUP_DIR/BACKUP_FILE 3. `CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );` 4. drop database test03 5. mysql < BACKUP_DIR/BACKUP_FILE 6. mysqlbinlog -vv log_bin.000002 --start-position=197 --stop-position=518 | mysql -v 7. use test03 8. show tables - Empty set (0.00 sec) In this case the test03 database is restored from backup, but the Persons table is not added when processing the binlog. I've verified that I'm not read-only or anything like that, and I can see the transaction in the binlog:

#250717  9:32:22 server id 5  end_log_pos 518 CRC32 0x798e87b9  Query   thread_id=125856        exec_time=0     error_code=0    Xid = 112497
use test03/*!*/;
SET TIMESTAMP=1752759142/*!*/;
SET @@session.pseudo_thread_id=125856/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=5/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
)
/*!*/;

along with in the verbose output from mysql: -------------- CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ) -------------- If someone can point me in the right direction it would be greatly appreciated. I'm at a loss for what to check next, and searches have produced no results thus far.

I am using Pgbackrest to backup and restore postgresql databases. Backup is okay, but when I want to restore database to point in time or last backup, although database files are located in data directory, database falls into invalid state, become unusable. This is my archive command in postgresql.conf: archive_command = 'pgbackrest --stanza=test archive-push %p' Do I need to set **recovery_target** and **recovery_target_time** options in postgresql.conf? These are the commands that I used to restore: sudo -u postgres pgbackrest --stanza=test restore --delta --set=[backup name] sudo -u postgres pgbackrest --stanza=test restore --delta --set=[backup name] --type=time --target="[timestamp]" And this is my pgbackrest.conf file: [global] repo1-cipher-pass=7X+//0/qTMU9FUmCgf3yw45IRpt9NWCQBwQiyL9sPTeT1bkWSNXOpl6yV7wKyoiL repo1-cipher-type=aes-256-cbc repo1-path=/var/lib/pgbackrest repo1-retention-full=2 log-level-console=info log-level-file=debug start-fast=y delta=y archive-async=y spool-path=/var/spool/pgbackrest archive-push-queue-max=100GiB archive-get-queue-max=100GiB [global:archive-get] process-max=5 [global:archive-push] process-max=5 [test] pg1-path=/var/lib/pgsql/15/data It seems correct to me. If anyone sees a mistake, or have suggestion/solution, please write comment/answer. I would be very thankful.

Im writing a script to restore a postgres standby server that was out of sync with the master node for a while. I would like the following behavior 1. Check if i can go with pg_rewind 2. If pg_rewind fails, then use pg_basebackup The big deal for me is about the pg_rewind because even if pg_rewind succeeds, it does not guarantee that the standby server will start successfully. In some cases the standby server needs some old wal file of the master (which can be provided using

) and if it is not found, you get this :

: requested WAL segment 00000002000000050000007C has already been removed

So i would like, in addition to pg_rewind successful response, to find a way to ensure that every wal file that will be required on server startup is already available before starting the server. And if it is not available, I continue directly with the second option (perform a full backup with pg_basebackup) Any help ...

I am practising Point in Time Recovery (PITR) on PostgreSQL 13 against accidental data deletion. Since PostgreSQL offers multiple parameters to perform PITR, I am bit confused choosing between Transaction XID and LSN. If an accidental DELETE statement removed a large number of rows, and I obtained the corresponding XID and starting LSN from the WAL dump, would performing PITR yield the same result regardless of whether I use the XID or LSN? Is there a risk of losing other committed transactions if I restore using the XID?

I am new to PostgreSQL and come from a SQL Server world. I am trying to create a transactional log backup process for PostgreSQL to run under Windows. I believe the terminology is PITR and Archiving but I am trying to find the best tool for this and instructions under windows commandline/SQL code I have looked at pgBackRest but cannot see how to install and configure under windows. I am running PostgreSQL 16

I have tried to recover a database, using the Point-in-Time Recovery method, through the postgresql.conf file, setting the recovery_target_time parameter at a specific time in past, but I cannot start the cluster and recover the database, I am getting the following output in the log file: 2024-08-08 09:37:11.429 CEST LOG: database system was interrupted; last known up at 2024-07-13 21:12:24 CEST cp: cannot stat '/appl/pgsql/15/bkp/WAL/00000002.history': No such file or directory 2024-08-08 09:37:11.443 CEST LOG: starting point-in-time recovery to 2024-07-14 20:00:00+02 2024-08-08 09:37:11.443 CEST LOG: invalid primary checkpoint record 2024-08-08 09:37:11.443 CEST PANIC: could not locate a valid checkpoint record 2024-08-08 09:37:11.556 CEST LOG: startup process (PID 3492124) was terminated by signal 6: Aborted 2024-08-08 09:37:11.556 CEST LOG: aborting startup due to startup process failure 2024-08-08 09:37:11.558 CEST LOG: database system is shut down I have seen different questions similar to mine, but also different answers, so I am not sure what can be the problem. What I can imagine is that it must have something to do with the version of the database, which is the 15, I have seen some things related to backup have changed since this version has been released, but I am not sure. Has anyone had this issue before?

TL;DR Would you recommend using CDC or pg_wal-based recovery to reproduce the state of the database in an arbitrary time, or stick with joining SCD2 tables (with start end end date for every record) and query all data before given date? We are developing a new microservice to support our application. For audit purposes our calculations need to be reproducible, so the current plan is to use SCD2 with every record having a start_date and an end_date. This way the state of the database at a given time can be reproduced if calculation from the past needs to be reproduced (if ever requested). Joining and maintaining versioned SCD2 tables seem to be cumbersome, but that is the current plan. Would making the database store only the current state, i.e. updates would actually update a record, and not just mark it obsolete and insert a new state; and archiving the replica level WAL files with sufficiently good granularity, and rebuilding the state on-demand work? Or keeping an audit table and rebuild from that? The scale/magnitude is around a 100k records per year. Any thoughts welcome. P.S. we don't have a DBA on the team.

According to user comments PostgreSQL data checksums have very minimal runtime overhead (both CPU and storage) but would allow (among other things) using pg_rewind for point in time recovery (PITR). However, data checksums are not enabled by default and enabling it on already existing HA cluster is not possible without pretty significant downtime. (If I've understood correctly, you cannot enable checksums on hot-standby only and promote it as new master once enabling the checksums were complete on the hot-standby.) Is there some poorly known issues if data checksums were enabled by default? Or is the default state (checksums disabled) just *due historical reasons* even though enabling data checksums would make much more sense in all cases?

I don't think my question makes any sense with the answer, but simply to confirm my doubt? I understand that PITR recovery is done with logs from the same database release (this is what I learned in the IBM Db2 LUW database concept). But my doubt is, If I restore the backup of the PostgreSQL instance from source server to the destination where the database version of the source is Postgres 13 and the destination server running on PostgreSQL 14. Is it possible to use WAL from a lower version to a higher version for PIT recovery in the PostgreSQL database?