Point-in-Time Recovery (PITR) with pg_basebackup in PostgreSQL 18
pg_basebackup is a useful tool for creating a base backup of a PostgreSQL cluster. When combined with continuous WAL archiving, it can be used to perform Point-in-Time Recovery (PITR).
In this article, I will demonstrate how to create a base backup using pg_basebackup and then perform PITR to a specific point in time.
1. Check the Current PostgreSQL Cluster
First, let’s check the current state of the PostgreSQL cluster.
psql (18.0)
WARNING: Console code page (720) differs from Windows code page (1256)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+----------------------------+----------------------------+--------+-----------+-----------------------
postgres | postgres | UTF8 | libc | English_United States.1256 | English_United States.1256 | | | =Tc/postgres +
| | | | | | | | postgres=CTc/postgres
template0 | postgres | UTF8 | libc | English_United States.1256 | English_United States.1256 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | English_United States.1256 | English_United States.1256 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
usefdb | postgres | UTF8 | libc | English_United States.1256 | English_United States.1256 | | |2. Configure WAL Archiving
Before creating the base backup, we need to configure PostgreSQL for WAL archiving.
The following parameters are configured in:
D:\postgres18_windows_installed\data\postgresql.confwal_level = replica
archive_mode = on
archive_command = 'copy "%p" "E:\\Archive\\%f"'The wal_level setting determines how much information is written to the WAL. The replica level is sufficient for WAL archiving and physical replication.
archive_mode = on enables WAL archiving, while archive_command specifies how completed WAL segments are copied to the archive location.
In this example, the WAL archive directory is:
E:\ArchiveBecause archive_mode cannot be changed without restarting the server, we restart PostgreSQL:
C:\Users\Win10-991005> pg_ctl restart
waiting for server to shut down....
done
server stopped
waiting for server to start....2026-08-12 22:32:04 +0330 LOG: redirecting log output to logging collector process
2026-08-12 22:32:04 +0330 HINT: Future log output will appear in directory "log".
done
server started3. Generate a WAL Segment
We can force PostgreSQL to switch to a new WAL segment by using pg_switch_wal():
postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/1D0020C0
(1 row)We can then verify that the WAL segment has been archived:
C:\Windows\system32>dir e:\Archive\*
08/12/2026 10:32 PM <DIR> .
08/12/2026 10:32 PM <DIR> ..
08/12/2026 10:32 PM 16,777,216 00000005000000000000001D
1 File(s) 16,777,216 bytes
2 Dir(s) 7,594,000,384 bytes freeAt this point, WAL archiving is working and we can create the base backup.
4. Create a Base Backup with pg_basebackup
The base backup can be created with the following command:
C:\Users\Win10-991005> pg_basebackup -D "E:\backup" -Ft -P -U postgres
Password:
32844/32844 kB (100%), 1/1 tablespaceIn this example:
- -D “E:\backup” specifies the destination directory.
- -Ft tells pg_basebackup to use tar format.
- -P displays progress information.
- -U postgres specifies the PostgreSQL user used to connect to the server.
The backup directory contains the following files:
C:\Windows\system32>dir E:\backup\*
08/12/2026 10:34 PM 184,439 backup_manifest
08/12/2026 10:34 PM 33,632,768 base.tar
08/12/2026 10:34 PM 33,558,528 pg_wal.tar
3 File(s) 67,375,735 bytes
2 Dir(s) 7,476,281,344 bytes free5. Generate Some Test Data
Now we will create a table and insert some test data.
postgres=# create table tb_2026_08_12(id serial primary key,name varchar(50));
CREATE TABLE
postgres=# insert into tb_2026_08_12 values(1,’Vahid Yousefzadeh’);
INSERT 0 1
6. Determine the PITR Target Time
Now I want to restore the database to a point after the first row was inserted but before the second row was inserted.
First, I switch WAL and record the current time:
postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/214ADFF0
(1 row)postgres=# select now();
now
----------------------------------
2026-08-12 22:36:26.779349+03:30
(1 row)Next, I insert another row:
postgres=# insert into tb_2026_08_12 values(2,'Payan Rafat');
INSERT 0 1Then I switch WAL again:
postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/22000160
(1 row)At this point, the WAL archive contains the WAL required to replay the changes between the base backup and our target time.
7. Prepare the Environment for Recovery
Now we stop PostgreSQL:
C:\Users\Win10-991005>pg_ctl stop
waiting for server to shut down.... 1 file(s) copied.
.. done
server stoppedFor this demonstration, I remove the existing data directory:
C:\Users\Win10-991005>rmdir /s /q "D:\postgres18_windows_installed\data"Then I create an empty data directory again:
C:\Users\Win10-991005>mkdir "D:\postgres18_windows_installed\data" 8. Restore the Base Backup
Because the base backup was created in tar format, we can extract into the base.tar PostgreSQL data directory:
C:\Users\Win10-991005>tar -xvf "E:\backup\base.tar" -C "D:\postgres18_windows_installed\data"9. Create recovery.signal
To tell PostgreSQL to enter archive recovery, we create an empty file named recovery.signal in the data directory:
C:\Users\Win10-991005> type nul > "D:\postgres18_windows_installed\data\recovery.signal"When PostgreSQL starts with recovery.signal present, it enters recovery mode and uses the recovery configuration parameters to determine how recovery should proceed.
10. Configure PITR
The following parameters are added to postgresql.conf:
restore_command = 'copy "E:\\Archive\\%f" "%p"'
recovery_target_time = '2026-08-12 22:36:26'
recovery_target_action = 'promote'The restore_command tells PostgreSQL how to retrieve archived WAL files. %f is replaced with the requested WAL file name, while %p is replaced with the path where PostgreSQL expects the file to be restored.
The recovery_target_time specifies the point in time at which recovery should stop.
Finally, recovery_target_action = ‘promote’ tells PostgreSQL to end recovery and promote the server to a normal read-write state once the recovery target has been reached.
11. Start PostgreSQL and Perform PITR
Now we can start PostgreSQL:
C:\Users\Win10-991005> pg_ctl start
waiting for server to start....2026-08-12 22:47:03 +0330 LOG: redirecting log output to logging collector process
2026-08-12 22:47:03 +0330 HINT: Future log output will appear in directory "log".
done
server startedThe PostgreSQL log shows that archive recovery has started:
2026-08-12 22:47:03 +0330 LOG: starting PostgreSQL 18.0 on x86_64-windows, compiled by msvc-19.44.35217, 64-bit
2026-08-12 22:47:03 +0330 LOG: listening on IPv6 address "::", port 5432
2026-08-12 22:47:03 +0330 LOG: listening on IPv4 address "0.0.0.0", port 5432
2026-08-12 22:47:03 +0330 LOG: database system was interrupted; last known up at 2026-08-12 22:34:34 +0330
2026-08-12 22:47:06 +0330 LOG: starting backup recovery with redo LSN 0/20000028, checkpoint LSN 0/20000080, on timeline ID 5
2026-08-12 22:47:06 +0330 LOG: restored log file "000000050000000000000020" from archive
2026-08-12 22:47:06 +0330 LOG: starting point-in-time recovery to 2026-08-12 22:36:26+03:30
2026-08-12 22:47:06 +0330 LOG: redo starts at 0/20000028
2026-08-12 22:47:06 +0330 LOG: completed backup recovery with redo LSN 0/20000028 and end LSN 0/20000120
2026-08-12 22:47:06 +0330 LOG: consistent recovery state reached at 0/20000120
2026-08-12 22:47:06 +0330 LOG: database system is ready to accept read-only connections
2026-08-12 22:47:06 +0330 LOG: restored log file "000000050000000000000021" from archive
2026-08-12 22:47:06 +0330 LOG: restored log file "000000050000000000000022" from archive
2026-08-12 22:47:06 +0330 LOG: recovery stopping before commit of transaction 850, time 2026-08-12 22:36:53.547442+03:30
2026-08-12 22:47:06 +0330 LOG: redo done at 0/220000E8 system usage: CPU: user: 0.01 s, system: 0.07 s, elapsed: 0.36 s
2026-08-12 22:47:06 +0330 LOG: last completed transaction was at log time 2026-08-12 22:36:02.679092+03:30
2026-08-12 22:47:06 +0330 LOG: selected new timeline ID: 6
2026-08-12 22:47:06 +0330 LOG: archive recovery complete
2026-08-12 22:47:06 +0330 LOG: checkpoint starting: end-of-recovery immediate wait
2026-08-12 22:47:08 +0330 LOG: checkpoint complete: wrote 1024 buffers (5.6%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 2 recycled; write=0.486 s, sync=0.968 s, total=1.499 s; sync files=335, longest=0.009 s, average=0.003 s; distance=32768 kB, estimate=32768 kB; lsn=0/220000E8, redo lsn=0/220000E8
2026-08-12 22:47:08 +0330 LOG: database system is ready to accept connectionsThe new timeline ID is important. After PITR completes and PostgreSQL is promoted, it creates a new timeline so that the recovered database can continue generating WAL independently from the original timeline.
Finally, PostgreSQL becomes available for normal connections:
LOG: database system is ready to accept connections12. Verify the Recovery
Now we can connect to the database and check the table:
postgres=# select * from tb_2026_08_12;
id | name
----+-------------------
1 | Vahid Yousefzadeh
(1 row)The second row:
2 | Payan Rafatis not present because it was inserted after the selected PITR target.
Therefore, the recovery successfully restored the PostgreSQL cluster to the requested point in time.
Comments
Post a Comment