DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

Point-in-Time Recovery (PITR) with pg_basebackup in PostgreSQL 18

#postgres #pitr #pgbasebackup #database

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 |        |           |
Enter fullscreen mode Exit fullscreen mode

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.conf
wal_level = replica
archive_mode = on
archive_command = 'copy "%p" "E:\\Archive\\%f"'
Enter fullscreen mode Exit fullscreen mode

for Linux:

[postgres@OL8 ~]$ vi /var/lib/pgsql/18/data/postgresql.conf

wal_level = replica
archive_mode = on
archive_command = 'cp "%p" "/archive/%f"'
Enter fullscreen mode Exit fullscreen mode

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:\Archive
Enter fullscreen mode Exit fullscreen mode

Because 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 started
Enter fullscreen mode Exit fullscreen mode

3. 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)
Enter fullscreen mode Exit fullscreen mode

We can then verify that the WAL segment has been archived:

C:\Users\Win10-991005>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 free
Enter fullscreen mode Exit fullscreen mode

At 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 tablespace
Enter fullscreen mode Exit fullscreen mode

In 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:\Users\Win10-991005> 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 free
Enter fullscreen mode Exit fullscreen mode

5. 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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

Next, I insert another row:

postgres=# insert into tb_2026_08_12 values(2,'Payan Rafat');
INSERT 0 1
Enter fullscreen mode Exit fullscreen mode

Then I switch WAL again:

postgres=# select pg_switch_wal();
 pg_switch_wal
---------------
0/22000160
(1 row)
Enter fullscreen mode Exit fullscreen mode

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 stopped
Enter fullscreen mode Exit fullscreen mode

For 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"
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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 started
Enter fullscreen mode Exit fullscreen mode

The 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 connections
The 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.
Enter fullscreen mode Exit fullscreen mode

Finally, PostgreSQL becomes available for normal connections:

LOG: database system is ready to accept connections

12. 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)
Enter fullscreen mode Exit fullscreen mode

Only the first row exists.

The second row:

2 | Payan Rafat
Enter fullscreen mode Exit fullscreen mode

is 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.

Top comments (0)