PG19 Hacktober: 31 Days of New Features: Logical Replication Without the Restart: wal_level=replica, Subscriptions via postgres_fdw and pg_createsubscriber

PostgreSQL 19 introduces new features for data replication and making it easier to explore different approaches to moving and synchronizing data between PostgreSQL instances. In this blog, we will explore how logical replication works, how replication-related configuration parameters such as wal_level affect the process, and whether replication can be configured without restarting PostgreSQL after changing wal_level from replica to logical.

We will also explore how postgres_fdw can be used to access data from remote PostgreSQL databases and how pg_createsubscriber simplifies the process of converting a physical standby into a logical replication subscriber.

Background / Context

wal_level = replica & effective_wal_level (Dynamic WAL Escalation)

• The Problem: Previously, logical replication required wal_level = logical and a PostgreSQL restart.
• Production Challenge: Restarting busy production databases required downtime, and keeping wal_level = logical could increase WAL overhead.
• PostgreSQL 19 Improvement: The new effective_wal_level feature dynamically adjusts the effective WAL level based on replication requirements.
• How It Works: The server can remain configured with wal_level = replica, while the effective level can automatically increase to logical when needed, without a restart.
• Benefit: This reduces configuration effort and avoids planned restarts just to enable logical replication

Subscriptions via postgres_fdw

• The Problem: Previously, logical replication subscriptions required connection strings with host, username, and credentials specified directly in CREATE SUBSCRIPTION.
• Management Challenge: Updating connection details or credentials across multiple subscriptions required manual changes.
• PostgreSQL 19 Improvement: Subscriptions can use connection details defined through postgres_fdw server objects.
• How It Works: DBAs can centrally manage remote server connections using CREATE SERVER and CREATE USER MAPPING.
• Benefit: This simplifies connection management, reduces duplication, and makes subscription configuration easier to maintain.

Integration with pg_createsubscriber

• The Problem: Before PostgreSQL 19, pg_createsubscriber required the publisher to have wal_level = logical configured and the server restarted.
• Migration Challenge: Converting a physical standby into a logical subscriber avoids copying all table data again, which can significantly reduce migration time.
• PostgreSQL 19 Improvement: Dynamic WAL-level management can help enable logical replication without restarting the primary server.
• How It Works: pg_createsubscriber converts an existing physical standby into a logical subscriber, using the existing data copy as its starting point.
• Benefit: This can simplify major-version upgrades and reduce migration time and downtime, subject to the tool’s prerequisites and replication requirements being met.

What’s New in PostgreSQL 19?

Dynamic WAL level: Logical replication can be enabled on demand even when wal_level = replica, without restarting the server. The new effective_wal_level parameter reports the current effective level.

postgres_fdw (Foreign Data Wrapper) allows one PostgreSQL database to access tables stored in another PostgreSQL database without copying all the data locally.

• For example, a PostgreSQL 19 database can access a table from a remote PostgreSQL 18 database using a foreign table.
• Simplified subscriptions: Use CREATE SUBSCRIPTION ... SERVER instead of repeating connection strings for every subscription.
• Centralized credentials: Manage remote connection details using CREATE SERVER and CREATE USER MAPPING.
• Better query performance: PostgreSQL 19 includes improvements to postgres_fdw, including additional opportunities to execute operations on the remote server.

pg_createsubscriber converts an existing physical standby into a logical replication subscriber. It uses the data already present on the standby instead of copying all the initial table data again.

• Physical standby: Start with a standby that has the same data as the primary.
• Create publication and replication slot: Create the required objects on the publisher.
• Set a replication starting point: Identify the WAL location from which logical replication must begin.
• Promote the target: Recover the standby to the selected WAL location and promote it.
• Create subscription: Configure the target to receive logical changes from the publisher.
• Start replication: Enable the subscription so new changes are applied to the target.
• Important prerequisites
  • The source and target must use the same PostgreSQL major version as the pg_createsubscriber utility.
  • The target must be a physical standby with the same system identifier as the source before conversion.
  • The publisher must accept connections from the target.
  • The publisher must have wal_level set to replica or logical.
  • Ensure sufficient max_replication_slots, max_wal_senders, and target-side logical replication workers exist.
  • Required WAL must remain available; configure max_slot_wal_keep_size = -1 as documented for this procedure.

Hands-On Example

(i) Logical replication now works dynamically based on ‘effective_wal_level’ parameter without changing wal_level=’logical’ or needing a restart.

Step1: Prepare publisher with table and creates a publication and observe the parameters wal level and effective_wal_level.

vagrant@pg2:~$ sudo -u postgres psql -d postgres
psql (19rc1 (Ubuntu 19~rc1-1~20261019.0815.g16dd9090236.pgdg22.04+1))
Type "help" for help.

postgres=# CREATE TABLE public.hardware (
    id integer PRIMARY KEY,
    product_name text,
    quantity integer
);
CREATE TABLE
postgres=# INSERT INTO public.hardware VALUES
(1, 'Laptop', 10),
(2, 'Keyboard', 25),
(3, 'Mouse', 50);
INSERT 0 3
postgres=#
postgres=# SHOW wal_level;
 wal_level
-----------
 replica
(1 row)

postgres=# SHOW effective_wal_level;
  effective_wal_level
---------------------
 replica
(1 row)

Step2: Create subscription on subscriber node.

postgres=# CREATE SUBSCRIPTION hardware_sub
CONNECTION 'host=192.168.56.12 port=5432 dbname=postgres user=repl_user1 password=mydb'
PUBLICATION hardware_pub
WITH (
    copy_data = true,
    create_slot = true,
    enabled = true
);
NOTICE:  created replication slot "hardware_sub" on publisher
CREATE SUBSCRIPTION
postgres=# SELECT subname,
       pid,
       received_lsn,
       latest_end_lsn,
       latest_end_time
FROM pg_stat_subscription;
   subname    |  pid  | received_lsn | latest_end_lsn |        latest_end_time
--------------+-------+--------------+----------------+-------------------------------
 hardware_sub | 86472 | 0/01CC3810   | 0/01CC3810     | 2026-10-20 10:51:39.983919+00
(1 row)

postgres=#
postgres=#  select * from hardware;
 id | product_name | quantity
----+--------------+----------
  1 | Laptop       |       10
  2 | Keyboard     |       25
  3 | Mouse        |       50
(3 rows)

Step3: Observe the parameters on publisher node.

postgres=# SHOW wal_level;
 wal_level
-----------
 replica
(1 row)

postgres=# SHOW effective_wal_level;
 effective_wal_level
---------------------
 logical
(1 row)

postgres=#

(ii) Now PostgreSQL Foreign Data Wrapper (FDW) allows you to access and query data from external databases or remote PostgreSQL servers as if they were local tables.Using postgres_fdw, we can perform cross-database queries between PostgreSQL servers without manually copying the data.The below shows a sample example.

Step1: create database and tables and values in pulbilcations.

 CREATE TABLE public.products 
    product_id integer PRIMARY KEY,
    product_name text NOT NULL,
    price numeric(10,2)
);
CREATE TABLE

Step2: On publisher create a sequence

fdw_pub_lab=# CREATE PUBLICATION products_pub
FOR TABLE public.products;
CREATE PUBLICATION
fdw_pub_lab=# SELECT *
FROM public.products
ORDER BY product_id;
 product_id | product_name | price
------------+--------------+-------
(0 rows)

fdw_pub_lab=# INSERT INTO public.products (product_id, product_name, price)
VALUES
    (1, 'Laptop', 65000),
    (2, 'Keyboard', 1500),
    (3, 'Mouse', 700);
INSERT 0 3
fdw_pub_lab=# CREATE SEQUENCE public.products_product_id
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    CACHE 1;
CREATE SEQUENCE
fdw_pub_lab=# ALTER TABLE public.products
ALTER COLUMN product_id
SET DEFAULT nextval('public.products_product_id');
ALTER TABLE
fdw_pub_lab=# SELECT setval(
    'public.products_product_id',
    COALESCE((SELECT MAX(product_id) FROM public.products), 0) + 1,
    false
);
 setval
--------
      4
(1 row)

fdw_pub_lab=# SELECT pg_get_serial_sequence(
    'public.products',
    'product_id'
);
 pg_get_serial_sequence
------------------------

(1 row)

fdw_pub_lab=# ALTER SEQUENCE public.products_product_id
OWNED BY public.products.product_id;
ALTER SEQUENCE
fdw_pub_lab=# SELECT *
FROM public.products
ORDER BY product_id;
 product_id | product_name |  price
------------+--------------+----------
          1 | Laptop       | 65000.00
          2 | Keyboard     |  1500.00
          3 | Mouse        |   700.00
(3 rows)

Step3: Create a serve fdw and create mapping user on subscriber.

fdw_sub_lab=# CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER postgres_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
    host '192.168.56.13',
    port '5432',
    dbname 'fdw_pub_lab'
);
NOTICE:  extension "postgres_fdw" already exists, skipping
CREATE EXTENSION
ERROR:  server "postgres_server" already exists
fdw_sub_lab=# CREATE USER MAPPING FOR postgres
SERVER postgres_server
OPTIONS (
    user 'repl_user',
    password 'YOUR_PASSWORD'
);
CREATE USER MAPPING
fdw_sub_lab=# CREATE USER MAPPING FOR postgres
SERVER postgres_server
OPTIONS (
    user 'repl_user',
    password 'mydb'
);

Step 4: Create subscription

fdw_sub_lab=# CREATE SUBSCRIPTION products_sub
SERVER postgres_server
PUBLICATION products_pub
WITH (
    copy_data = true,
    create_slot = true,
    enabled = true
);
NOTICE:  created replication slot "products_sub" on publisher
CREATE SUBSCRIPTION
fdw_sub_lab=# SELECT subname, subenabled
FROM pg_subscription
WHERE subname = 'products_sub';

Step 5: Verify subscription

#Insert records on publisher andcheck in subscriber
fdw_pub_lab=# INSERT INTO public.products (product_id, product_name, price)
VALUES
    (5, 'Laptop', 750.00),
    (6, 'Keyboard', 45.00),
    (7, 'Wireless Mouse', 25.00),
    (8, 'USB Hub', 15.00),
    (9, 'Webcam', 60.00);
INSERT 0 5
fdw_pub_lab=# SELECT * FROM public.products ORDER BY product_id;
 product_id |  product_name  |  price
------------+----------------+----------
          1 | Laptop         | 65000.00
          2 | Keyboard       |  1500.00
          3 | Mouse          |   700.00
          5 | Laptop         |   750.00
          6 | Keyboard       |    45.00
          7 | Wireless Mouse |    25.00
          8 | USB Hub        |    15.00
          9 | Webcam         |    60.00


SELECT *
FROM pg_stat_subscription
WHERE subname = 'products_sub';
   subname    | subenabled
--------------+------------
 products_sub | t

#Check the replication 
fdw_sub_lab=# SELECT
    srrelid::regclass AS table_name,
    srsubstate
FROM pg_subscription_rel;
 table_name | srsubstate
------------+------------
 products   | r
(1 row)

fdw_sub_lab=# SELECT *
FROM public.products
ORDER BY product_id;
 product_id |  product_name  |  price
------------+----------------+----------
          1 | Laptop         | 65000.00
          2 | Keyboard       |  1500.00
          3 | Mouse          |   700.00
          5 | Laptop         |   750.00
          6 | Keyboard       |    45.00
          7 | Wireless Mouse |    25.00
          8 | USB Hub        |    15.00
          9 | Webcam         |    60.00
(8 rows)

(iii) pg_createsubscribe:

Here we are changing physical standby into logical subscribe and verify whether the records are displaying or not.The below is the sample example.

Step1: Prepare publisher, create database in publisher and check the parameters and check whether wal_level=logical or not.

postgres=# \c pgc_admin;
connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "pgc_admin" does not exist
Previous connection kept
postgres=# \c pgc_lab
You are now connected to database "pgc_lab" as user "postgres".
pgc_lab=# SELECT name, setting, unit, source, sourcefile, pending_restart
FROM pg_settings
WHERE name IN (
    'listen_addresses',
    'wal_level',
    'max_wal_senders',
    'max_replication_slots',
    'max_worker_processes',
    'max_logical_replication_workers',
    'max_slot_wal_keep_size'
)
ORDER BY name;
              name               | setting | unit |       source       |               sourcefile                | pending_restart
---------------------------------+---------+------+--------------------+-----------------------------------------+-----------------
 listen_addresses                | *       |      | configuration file | /etc/postgresql/19/main/postgresql.conf | f
 max_logical_replication_workers | 4       |      | default            |                                         | f
 max_replication_slots           | 10      |      | default            |                                         | f
 max_slot_wal_keep_size          | -1      | MB   | default            |                                         | f
 max_wal_senders                 | 10      |      | default            |                                         | f
 max_worker_processes            | 8       |      | default            |                                         | f
 wal_level                       | logical |      | configuration file | /etc/postgresql/19/main/postgresql.conf | f
(7 rows)

Step 2: Check whether the pg_createsubscriber utility exists in the subscriber node.

vagrant@pg2:~$ /usr/lib/postgresql/19/bin/pg_createsubscriber --version
/usr/lib/postgresql/19/bin/pg_createsubscriber --help
pg_createsubscriber (PostgreSQL) 19rc1 (Ubuntu 19~rc1-1~20261019.0815.g16dd9090236.pgdg22.04+1)
pg_createsubscriber creates a new logical replica from a standby server.

Usage:
  pg_createsubscriber [OPTION]...

Options:
  -a, --all                       create subscriptions for all databases except template
                                  databases and databases that don't allow connections
  -d, --database=DBNAME           database in which to create a subscription
  -D, --pgdata=DATADIR            location for the subscriber data directory
  -l, --logdir=LOGDIR             location for the log directory
  -n, --dry-run                   dry run, just show what would be done
  -p, --subscriber-port=PORT      subscriber port number (default 50432)
  -P, --publisher-server=CONNSTR  publisher connection string
  -s, --socketdir=DIR             socket directory to use (default current dir.)
  -t, --recovery-timeout=SECS     seconds to wait for recovery to end
  -T, --enable-two-phase          enable two-phase commit for all subscriptions
  -U, --subscriber-username=NAME  user name for subscriber connection
  -v, --verbose                   output verbose messages
      --clean=OBJECTTYPE          drop all objects of the specified type from specified
                                  databases on the subscriber; accepts: "publications"
      --config-file=FILENAME      use specified main server configuration
                                  file when running target cluster
      --publication=NAME          publication name
      --replication-slot=NAME     replication slot name
      --subscription=NAME         subscription name
  -V, --version                   output version information, then exit
  -?, --help                      show this help, then exit

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

Step3: Save the password and create a target directry “/var/lib/postgresql/19/pgc_target” and assigns ownership to the PostgreSQL service account.

vagrant@pg2:~$ sudo -u postgres bash -c \
"printf '%s\n' '192.168.56.13:5432:*:pgc_admin:mydb' > /var/lib/postgresql/.pgpass && chmod 600 /var/lib/postgresql/.pgpass"


vagrant@pg2:~$ sudo install -d -o postgres -g postgres \
  /var/lib/postgresql/19/pgc_target
vagrant@pg2:~$ sudo ls -la /var/lib/postgresql/19/pgc_target
total 8
drwxr-xr-x 2 postgres postgres 4096 Oct 20 07:26 .
drwxr-xr-x 4 postgres postgres 4096 Oct 20 07:26 ..

Step4: Create target configuration file ,We need a target configuration file because we are running a second PostgreSQL instance on the standby/replica node (pg2) for the pg_createsubscriber lab, separate from the existing PostgreSQL 19 main instance.

vagrant@pg2:~$ sudo tee /tmp/pgc_target.conf >/dev/null <<'EOF'
include '/etc/postgresql/19/main/postgresql.conf'

data_directory = '/var/lib/postgresql/19/pgc_target'
port = 50432
listen_addresses = 'localhost'

max_worker_processes = 10
max_logical_replication_workers = 4
max_active_replication_origins = 10
EOF

sudo chown postgres:postgres /tmp/pgc_target.conf
vagrant@pg2:~$

Step5: Take a physical backup from publisher

vagrant@pg2:~$ sudo -u postgres /usr/lib/postgresql/19/bin/pg_basebackup \
  -h 192.168.56.13 \
  -p 5432 \
  -U pgc_admin \
  -D /var/lib/postgresql/19/pgc_target \
  -R -X stream -P -w
46927/46927 kB (100%), 1/1 tablespace
vagrant@pg2:~$
It takes a physical backup of the publisher and configures the target as a standby server.

Step6: Check the PostgreSQL target cluster and begin converting a physical standby into a logical subscriber using pg_createsubscriber.

vagrant@pg2:~$ sudo -u postgres /usr/lib/postgresql/19/bin/pg_controldata \
  /var/lib/postgresql/19/pgc_target \
  | grep -E 'Database system identifier|Database cluster state'
Database system identifier:           7698438310987059736
Database cluster state:               in production

vagrant@pg2:~$ sudo -u postgres /usr/lib/postgresql/19/bin/pg_createsubscriber \
  -D /var/lib/postgresql/19/pgc_target \
  --config-file=/tmp/pgc_target.conf \
  -P "host=192.168.56.13 port=5432 dbname=pgc_lab user=pgc_admin" \
  -d pgc_lab \
  -p 50432 \
  -s /var/run/postgresql/pgc_target \
  -U postgres \
  -v
pg_createsubscriber: validating publisher connection string
pg_createsubscriber: validating subscriber connection string
pg_createsubscriber: checking if directory "/var/lib/postgresql/19/pgc_target" is a cluster data directory
pg_createsubscriber: getting system identifier from publisher
pg_createsubscriber: system identifier is 7698438310987059736 on publisher
pg_createsubscriber: getting system identifier from subscriber
pg_createsubscriber: system identifier is 7698438310987059736 on subscriber
pg_createsubscriber: starting the standby server with command-line options
2026-10-20 08:14:16.251 UTC [133090] LOG:  starting PostgreSQL 19rc1 (Ubuntu 19~rc1-1~20261019.0815.g16dd9090236.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit
2026-10-20 08:14:16.259 UTC [133090] LOG:  listening on Unix socket "/var/run/postgresql/pgc_target/.s.PGSQL.50432"
2026-10-20 08:14:16.319 UTC [133095] LOG:  database system was shut down in recovery at 2026-10-20 08:13:34 UTC
2026-10-20 08:14:16.330 UTC [133095] LOG:  entering standby mode
2026-10-20 08:14:16.346 UTC [133095] LOG:  redo starts at 0/04000060
2026-10-20 08:14:16.346 UTC [133095] LOG:  consistent recovery state reached at 0/04000138
2026-10-20 08:14:16.346 UTC [133095] LOG:  invalid record length at 0/04000170: expected at least 24, got 0
2026-10-20 08:14:16.346 UTC [133090] LOG:  database system is ready to accept read-only connections
pg_createsubscriber: server was started
pg_createsubscriber: checking settings on subscriber
pg_createsubscriber: checking settings on publisher
2026-10-20 08:14:16.505 UTC [133096] LOG:  started streaming WAL from primary at 0/04000000 on timeline 1
pg_createsubscriber: stopping the subscriber
2026-10-20 08:14:16.579 UTC [133090] LOG:  received fast shutdown request
2026-10-20 08:14:16.588 UTC [133090] LOG:  aborting any active transactions
2026-10-20 08:14:16.589 UTC [133096] FATAL:  terminating walreceiver process due to administrator command
2026-10-20 08:14:16.589 UTC [133096] DETAIL:  Signal sent by PID 133090, UID 113.
2026-10-20 08:14:16.641 UTC [133093] LOG:  shutting down
2026-10-20 08:14:16.702 UTC [133090] LOG:  database system is shut down
pg_createsubscriber: server was stopped
pg_createsubscriber: creating publication "pg_createsubscriber_16417_14cbaac0" in database "pgc_lab"
pg_createsubscriber: creating the replication slot "pg_createsubscriber_16417_14cbaac0" in database "pgc_lab" on publisher
pg_createsubscriber: starting the subscriber
2026-10-20 08:14:17.302 UTC [133104] LOG:  starting PostgreSQL 19rc1 (Ubuntu 19~rc1-1~20261019.0815.g16dd9090236.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit
2026-10-20 08:14:17.303 UTC [133104] LOG:  listening on Unix socket "/var/run/postgresql/pgc_target/.s.PGSQL.50432"
2026-10-20 08:14:17.338 UTC [133109] LOG:  database system was shut down in recovery at 2026-10-20 08:14:16 UTC
2026-10-20 08:14:17.342 UTC [133109] LOG:  entering standby mode
2026-10-20 08:14:17.354 UTC [133109] LOG:  redo starts at 0/04000060
2026-10-20 08:14:17.355 UTC [133109] LOG:  consistent recovery state reached at 0/04000170
2026-10-20 08:14:17.355 UTC [133104] LOG:  database system is ready to accept read-only connections
2026-10-20 08:14:17.356 UTC [133109] LOG:  invalid record length at 0/04000170: expected at least 24, got 0
pg_createsubscriber: server was started
pg_createsubscriber: waiting for the target server to reach the consistent state
2026-10-20 08:14:17.437 UTC [133110] LOG:  started streaming WAL from primary at 0/04000000 on timeline 1
2026-10-20 08:14:17.456 UTC [133109] LOG:  recovery stopping before WAL location (LSN) "0/040007E8"
2026-10-20 08:14:17.459 UTC [133109] LOG:  redo done at 0/040007E8 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.10 s
2026-10-20 08:14:17.461 UTC [133109] LOG:  last completed transaction was at log time 2026-10-20 08:14:16.832863+00
2026-10-20 08:14:17.464 UTC [133110] FATAL:  terminating walreceiver process due to administrator command
2026-10-20 08:14:17.464 UTC [133110] DETAIL:  Signal sent by PID 133109, UID 113.
2026-10-20 08:14:17.483 UTC [133109] LOG:  selected new timeline ID: 2
2026-10-20 08:14:17.670 UTC [133109] LOG:  archive recovery complete
2026-10-20 08:14:17.682 UTC [133107] LOG:  checkpoint starting: end-of-recovery fast wait
2026-10-20 08:14:17.779 UTC [133107] LOG:  checkpoint complete: end-of-recovery fast wait: wrote 8 buffers (0.0%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.030 s, sync=0.033 s, total=0.108 s; sync files=8, longest=0.008 s, average=0.005 s; distance=1 kB, estimate=1 kB; lsn=0/040007E8, redo lsn=0/040007E8
2026-10-20 08:14:17.827 UTC [133104] LOG:  database system is ready to accept connections
pg_createsubscriber: target server reached the consistent state
pg_createsubscriber: hint: If pg_createsubscriber fails after this point, you must recreate the physical replica before continuing.
pg_createsubscriber: dropping publication "pg_createsubscriber_16417_14cbaac0" in database "pgc_lab"
pg_createsubscriber: creating subscription "pg_createsubscriber_16417_14cbaac0" in database "pgc_lab"
pg_createsubscriber: setting the replication progress (node name "pg_24620", LSN 0/040007E8) in database "pgc_lab"
pg_createsubscriber: enabling subscription "pg_createsubscriber_16417_14cbaac0" in database "pgc_lab"
pg_createsubscriber: stopping the subscriber
2026-10-20 08:14:18.873 UTC [133104] LOG:  received fast shutdown request
2026-10-20 08:14:18.881 UTC [133104] LOG:  aborting any active transactions
2026-10-20 08:14:18.930 UTC [133107] LOG:  shutting down
2026-10-20 08:14:18.941 UTC [133107] LOG:  checkpoint starting: shutdown fast
2026-10-20 08:14:19.038 UTC [133107] LOG:  checkpoint complete: shutdown fast: wrote 11 buffers (0.1%), wrote 1 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.020 s, sync=0.037 s, total=0.108 s; sync files=11, longest=0.010 s, average=0.004 s; distance=2 kB, estimate=2 kB; lsn=0/040013E0, redo lsn=0/040013E0
2026-10-20 08:14:19.116 UTC [133104] LOG:  database system is shut down
pg_createsubscriber: server was stopped
pg_createsubscriber: modifying system identifier of subscriber
pg_createsubscriber: system identifier is 7698660412759062489 on subscriber
pg_createsubscriber: running pg_resetwal on the subscriber
pg_createsubscriber: successfully reset WAL on the subscriber
pg_createsubscriber: Done!

Output:

vagrant@pg2:~$ sudo -u postgres psql \
  -h /var/run/postgresql \
  -p 50432 \
  -d pgc_lab
psql (19rc1 (Ubuntu 19~rc1-1~20261019.0815.g16dd9090236.pgdg22.04+1))
Type "help" for help.

pgc_lab=# 


pgc_lab=# SELECT pg_is_in_recovery();
-[ RECORD 1 ]-----+--
pg_is_in_recovery | f

pgc_lab=# SELECT * FROM public.employees ORDER BY emp_id;
-[ RECORD 1 ]--------
emp_id     | 1
emp_name   | Ravi
department | IT
salary     | 50000.00
-[ RECORD 2 ]--------
emp_id     | 2
emp_name   | Sita
department | HR
salary     | 45000.00
-[ RECORD 3 ]--------
emp_id     | 3
emp_name   | Kiran
department | DBA
salary     | 60000.00

pgc_lab=# SELECT
    subname,
    pid,
    received_lsn,
    latest_end_lsn,
    last_msg_receipt_time
FROM pg_stat_subscription;
-[ RECORD 1 ]---------+-----------------------------------
subname               | pg_createsubscriber_16417_14cbaac0
pid                   | 133139
received_lsn          | 0/04000968
latest_end_lsn        | 0/04000968
last_msg_receipt_time | 2026-10-20 09:24:59.992324+00

Why It Matters?

Use case

1.Near-Zero Downtime Major Version Upgrades

Organizations can upgrade multi-terabyte databases by creating a physical standby node, running pg_createsubscriber to instantly transform it into a logical subscriber, and catching up to the primary node. Because PostgreSQL 19 eliminates the primary server restart for wal_level, the entire upgrade pipeline is bootstrapped without a single second of production downtime.

2.Centralized, Secure Multi-Tenant Replication

SaaS providers replication configurations are simplified by using the new postgres_fdw integration to handle subscription connections. Instead of hardcoding vulnerable, loose connection strings and credentials into dozens of individual tenant subscriptions, administrators can centrally update a single Foreign Server object without touching active replication pipelines.

Caveats / Things to Watch Out For

Limitations of postgres_fdw

• Network dependency: Queries depend on the remote server being available and the network connection working.
• Performance: Large queries may be slow because data must travel between servers.
• Data types: Foreign table columns should match the remote table’s data types and collations to avoid unexpected results.
• Transaction limitations: Remote transactions are supported, but two-phase commit (prepared transactions coordinated across servers) is not supported.
• SQL limitations: Some operations, such as INSERT ... ON CONFLICT DO UPDATE, are not supported by postgres_fdw.
• Security: Remote access requires appropriate permissions, user mappings, and secure credential management.
• Statistics: Local query-planning estimates can be inaccurate when remote table statistics are outdated.

Limitiations for pg_createsubscriber:

• The target is promoted and becomes a writable logical subscriber; it is no longer a physical standby.
• If conversion fails after promotion, the target data directory may not be recoverable. Rebuilding the standby may be necessary.
• DDL changes are not replicated by logical replication.
• Existing downstream physical standbys can break because the target’s system identifier is changed during conversion.
• It avoids copying initial table data over the network, but the target must still recover to the selected replication starting point.

Conclusion

PostgreSQL 19 provides a useful improvements in replication and database management. pg_createsubscriber helps convert a physical standby into a logical subscriber without copying all existing table data again. postgres_fdw allows PostgreSQL to access remote tables and reuse foreign server connection settings for logical replication subscriptions. The improvements to WAL-level management can reduce the need for manual configuration changes and restarts when logical replication is required, while wal_level = replica remains the configured setting in supported scenarios. Together, these features can simplify replication setup, reduce operational effort, and support more flexible PostgreSQL deployments. Always validate the prerequisites and test these features in a non-production environment before applying them to production.

References

• https://www.postgresql.org/docs/19/postgres-fdw.html
• https://www.postgresql.org/docs/19/runtime-config-wal.html#GUC-WAL-LEVEL
• https://www.postgresql.org/docs/19/app-pgcreatesubscriber.html