In one of our previous blogs, a custom method for switching from PostgreSQL physical replication to logical replication was discussed, using pg_create_logical_replication_slot and pg_replication_slot_advance. PostgreSQL 17, set to be released this year, introduces the pg_createsubscriber utility, simplifying the conversion from physical to logical replication. This post will cover using pg_createsubscriber to convert physical to logical […]
Jul
03
2024
03
2024
--
Easy Guide to Switching PostgreSQL Physical Replication to Logical Replication with pg_createsubscriber
Dec
14
2023
14
2023
--
How to Convert PostgreSQL Streaming Replication to Logical Replication
While a hallmark feature of PostgreSQL is streaming replication, sometimes data demands a more refined and adaptable replication approach to address different requirements. Logical replication offers a distinct advantage over traditional streaming replication by providing a more detailed and selective approach to data replication, enabling organizations to replicate specific tables selectively and even filter out particular types of data.
In this blog post, we will look into the steps of transforming an existing PostgreSQL streaming replication node into a logical replication node. This method facilitates a smooth transition to logical replication without the need to start from scratch, allowing organizations to harness the enhanced capabilities of logical replication effectively.
Pre-requisites
To switch from the current streaming replication to logical replication, confirm that the primary node fulfills the following prerequisites:
- Set the wal_level to logical.
- Adjust max_replication_slots and max_wal_senders as needed.
- Ensure all tables have a replica identity set.
Steps to convert streaming replication to logical replication:
The lab setup includes two servers: primary (172.31.46.28) and standby (172.31.39.50), currently configured with streaming replication. The following steps will guide the transition from streaming to logical replication.
1. Verify that the current streaming replication setup is synchronized and that there is no lag between the primary and standby nodes.
percona=# select usename,client_addr,state,replay_lag from pg_stat_replication;
usename | client_addr | state | replay_lag
---------+--------------+-----------+------------
percona | 172.31.39.50 | streaming |
(1 row)
percona=# select slot_name,slot_type,active from pg_replication_slots;
slot_name | slot_type | active
-----------------------+-----------+--------
physical_replica_slot | physical | t
(1 row)
2. Create a logical replication slot on the primary node.
percona=# select pg_create_logical_replication_slot('logical_replica_slot', 'pgoutput');
pg_create_logical_replication_slot
------------------------------------
(logical_replica_slot,0/7000180)
(1 row)
percona=# select * from pg_replication_slots ;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn | wal_status | safe_wal_size | two_phase
-----------------------+----------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------+------------+---------------+-----------
physical_replica_slot | | physical | | | f | t | 116306 | | | 0/7000180 | | reserved | | f
logical_replica_slot | pgoutput | logical | 16384 | percona | f | f | | | 768 | 0/7000148 | 0/7000180 | reserved | | f
(2 rows)
3. Create publication for tables on the primary node.
percona=# CREATE PUBLICATION my_pub FOR ALL TABLES; CREATE PUBLICATION
4. Next, promote the intended standby node to a standalone node.
percona=# SELECT pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row) postgres=# SELECT pg_promote(); pg_promote ------------ t (1 row) postgres=# SELECT pg_is_in_recovery(); pg_is_in_recovery ------------------- f (1 row)
5. Review the PostgreSQL log file on the promoted standby server and take note of the Log Sequence Number (LSN) associated with the “redo done at” message. In the below log file, the LSN is 0/7000640.
promoted standby postgresql Logfile: 2023-12-04 13:41:11.527 UTC [37749] LOG: received promote request 2023-12-04 13:41:11.527 UTC [37750] FATAL: terminating walreceiver process due to administrator command 2023-12-04 13:41:11.527 UTC [37749] LOG: invalid record length at 0/7000678: wanted 24, got 0 2023-12-04 13:41:11.527 UTC [37749] LOG: redo done at 0/7000640 system usage: CPU: user: 0.00 s, system: 0.01 s, elapsed: 1638.50 s 2023-12-04 13:41:11.527 UTC [37749] LOG: last completed transaction was at log time 2023-12-04 13:40:25.619889+00 2023-12-04 13:41:11.535 UTC [37749] LOG: selected new timeline ID: 2 2023-12-04 13:41:11.622 UTC [37749] LOG: archive recovery complete 2023-12-04 13:41:11.633 UTC [37747] LOG: checkpoint starting: force 2023-12-04 13:41:11.637 UTC [37746] LOG: database system is ready to accept connections 2023-12-04 13:41:11.639 UTC [37883] WARNING: archive_mode enabled, yet archiving is not configured 2023-12-04 13:41:11.955 UTC [37747] LOG: checkpoint complete: wrote 5 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.305 s, sync=0.005 s, total=0.322 s; sync files=5, longest=0.004 s, average=0.001 s; distance=1 kB, estimate=14745 kB
The log entry “redo done at 0/7000640” provides details about the most recently applied transaction location. We utilize this Log Sequence Number (LSN) to progress the logical replication slot created in step two. Consequently, transactions occurring after this point will be applied to the newly promoted node.
6. On the primary server, advance the logical replication slot created in step two.
percona=# select pg_replication_slot_advance('logical_replica_slot', '0/7000640');
pg_replication_slot_advance
----------------------------------
(logical_replica_slot,0/7000640)
7. On the standby server, create the subscription for the publication created in step three.
percona=# CREATE SUBSCRIPTION my_sub CONNECTION 'user=percona password=<redacted> host=<host_ip> port=5432' PUBLICATION my_pub WITH (copy_data=false, slot_name='logical_replica_slot', create_slot=false);
8. At this stage, logical replication will commence, which can be verified by inspecting the replication status on the primary server.
percona=# select slot_name,slot_type,active from pg_replication_slots;
slot_name | slot_type | active -----------------------+-----------+--------
physical_replica_slot | physical | f
logical_replica_slot | logical | t
(2 rows)
percona=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 116806
usesysid | 16434
usename | percona
application_name | my_sub
client_addr | 172.31.39.50
client_hostname |
client_port | 52512
backend_start | 2023-12-04 13:52:46.200631+00
backend_xmin |
state | streaming
sent_lsn | 0/7002148
write_lsn | 0/7002148
flush_lsn | 0/7002148
replay_lsn | 0/7002148
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2023-12-04 14:06:19.464122+00
9. It’s important to remove the current physical replication slot to avoid filling up the primary server’s disk.
postgres=# select pg_drop_replication_slot('physical_replica_slot');
pg_drop_replication_slot
--------------------------
(1 row)
The steps covered in this process make the transition to logical replication smoother, allowing for selective replication and improved data management across different parts of your database system. This change provides more control over how you manage and share data within PostgreSQL.
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.
Sep
25
2023
25
2023
--
pgBackRest’s Expire and Backup With Custom Lock-Path Files
PostgreSQL is a powerful and popular open source relational database management system. Ensuring the safety of your PostgreSQL data through regular backups is an important concern for database administrators. pgBackRest is a highly efficient and versatile tool for managing PostgreSQL backups. In this blog post, we’ll explore a technique to help your backup strategy: running pgBackRest’s expire and backup processes in parallel while utilizing custom lock-file paths.
Percona and PostgreSQL work better together.
Try Percona Distribution for PostgreSQL today.
This approach comes in handy when the “expire” command, responsible for cleaning up old backups, takes longer than expected and causes problems. It can clash with the next backup attempt, resulting in an error message like this:
ERROR: [050]: unable to acquire lock on file '/tmp/pgbackrest/backup/backup.lock': Resource temporarily unavailable HINT: is another pgBackRest process running?
This issue tends to occur when your backup storage is located offsite, your database is quite large, and the backup process generates a lot of files. Combined, these factors can make the “expire” step take a long time to finish, disrupting your backup schedule and causing conflicts with new backups.
To overcome these potential conflicts and further enhance your PostgreSQL backup strategy, we will delve into using different lock-paths for the parallel execution of pgBackRest’s expire and backup processes. Before we proceed with an example, let’s understand the idea behind a custom lock-path.
By default, pgBackRest uses the same lock-file path for the backup and expire processes. This can result in conflicts when these processes run in parallel. To avoid such conflicts, we can configure pgBackRest to use separate lock-file paths for each process. This way, they won’t interfere with each other, enabling efficient parallel execution.
Example: Parallelizing pgBackRest backup and expire with Custom Lock-File Paths
In the example below, we’ll set up pgBackRest with minimal settings to illustrate its functionality:
primary database server: 172.31.45.240 pgbackrest server: 172.31.32.62
On the primary database:
We have the following pgBackRest configuration, which includes information about the repository path and repository host details.
postgres@ip-172-31-45-240:~$ cat /etc/pgbackrest/pgbackrest.conf [global] repo1-path=/var/lib/pgbackrest repo1-retention-full=2 repo1-host=172.31.32.62 repo1-host-user=postgres [demo] pg1-path=/var/lib/postgresql/14/main pg1-port=5432
On the pgBackRest server:
We maintain two configuration files, specifically /etc/pgbackrest/pgbackrest.conf and /etc/pgbackrest/expire.conf. These files play distinct roles in our backup and expiration processes.
The /etc/pgbackrest/pgbackrest.conf file contains crucial information related to our primary database server. Notably, it includes a critical setting named lock-path=/tmp/pgbackrest/backup.lock. This lock-path is utilized during backup operations.
postgres@ip-172-31-32-62:~$ cat /etc/pgbackrest/pgbackrest.conf [global] repo1-path=/var/lib/pgbackrest repo1-host-user=postgres [demo] pg1-path=/var/lib/postgresql/14/main pg1-host=172.31.45.240 pg1-host-user=postgres pg1-port=5432 lock-path=/tmp/pgbackrest/backup.lock
On the other hand, the second configuration file, /etc/pgbackrest/expire.conf, focuses on managing the retention and expiration of backups from our primary database server. It specifies repo1-retention-full=2 as a critical setting, determining the retention period for full backups. In this case, with repo1-retention-full=2, pgBackRest will retain the most recent two full backups. Furthermore, it also defines lock-path=/tmp/pgbackrest/expire.lock, which is specifically used during the expire operation. Below is the content of the expire configuration file:
postgres@ip-172-31-32-62:~$ cat /etc/pgbackrest/expire.conf [global] repo1-path=/var/lib/pgbackrest repo1-host-user=postgres repo1-retention-full=2 [demo] pg1-path=/var/lib/postgresql/14/main pg1-host=172.31.45.240 pg1-host-user=postgres pg1-port=5432 lock-path=/tmp/pgbackrest/expire.lock
Moving forward, we will execute a pgBackRest check command to verify the correct configuration of the archive_command settings for both archiving and backups within the designated stanza. This command will conduct checks across all repositories and databases configured on the host where the command is executed.
postgres@ip-172-31-32-62:~$ pgbackrest check --stanza=demo --log-level-console=info 2023-09-11 13:41:43.794 P00 INFO: check command begin 2.47: --exec-id=49365-ba0eb42b --log-level-console=info --pg1-host=172.31.45.240 --pg1-host-user=postgres --pg1-path=/var/lib/postgresql/14/main --pg1-port=5432 --repo1-path=/var/lib/pgbackrest --stanza=demo 2023-09-11 13:41:45.500 P00 INFO: check repo1 configuration (primary) 2023-09-11 13:41:45.703 P00 INFO: check repo1 archive for WAL (primary) 2023-09-11 13:41:46.407 P00 INFO: WAL segment 00000001000000010000000A successfully archived to '/var/lib/pgbackrest/archive/demo/14-1/0000000100000001/00000001000000010000000A-5f8afc66975f348a66165bbfe45aae6c38dae4ad.gz' on repo1 2023-09-11 13:41:46.511 P00 INFO: check command end: completed successfully (2720ms)
Repository info:
On the backup server, we have three full backups in the repo:
postgres@ip-172-31-32-62:~$ pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail info stanza: demo status: ok cipher: none db (current) wal archive min/max (14): 000000010000000100000005/00000001000000010000000C full backup: 20230911-084334F timestamp start/stop: 2023-09-11 08:45:36+00 / 2023-09-11 08:46:54+00 wal start/stop: 000000010000000100000005 / 000000010000000100000005 database size: 1.7GB, database backup size: 1.7GB repo1: backup set size: 295MB, backup size: 295MB full backup: 20230911-092018F timestamp start/stop: 2023-09-11 09:21:33+00 / 2023-09-11 09:23:23+00 wal start/stop: 000000010000000100000009 / 000000010000000100000009 database size: 1.7GB, database backup size: 1.7GB repo1: backup set size: 295MB, backup size: 295MB full backup: 20230911-135947F timestamp start/stop: 2023-09-11 13:59:47+00 / 2023-09-11 14:01:34+00 wal start/stop: 00000001000000010000000C / 00000001000000010000000C database size: 1.7GB, database backup size: 1.7GB repo1: backup set size: 295MB, backup size: 295MB
Next, we will simultaneously execute the backup and expire processes using their respective configuration files. This action will trigger both the backup and expiration operations. The expire operation will remove a full backup, specifically the one named 20230911-084334F, in accordance with the repo1-retention-full=2 setting.
postgres@ip-172-31-32-62:~$ date;pgbackrest --config=/etc/pgbackrest/expire.conf --stanza=demo --log-level-console=detail expire Mon Sep 11 14:04:49 UTC 2023 2023-09-11 14:04:49.030 P00 INFO: expire command begin 2.47: --config=/etc/pgbackrest/expire.conf --exec-id=49974-34c92e8e --lock-path=/tmp/pgbackrest/expire.lock --log-level-console=detail --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo 2023-09-11 14:04:49.032 P00 INFO: repo1: expire full backup 20230911-084334F 2023-09-11 14:04:49.040 P00 INFO: repo1: remove expired backup 20230911-084334F 2023-09-11 14:04:49.086 P00 DETAIL: repo1: 14-1 archive retention on backup 20230911-092018F, start = 000000010000000100000009 2023-09-11 14:04:49.087 P00 INFO: repo1: 14-1 remove archive, start = 000000010000000100000005, stop = 000000010000000100000008 2023-09-11 14:04:49.087 P00 INFO: expire command end: completed successfully (64ms)
Full backup also started at the exact same time and ran simultaneously.
postgres@ip-172-31-32-62:~$date; pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --stanza=demo --log-level-console=detail backup --type=full Mon Sep 11 14:04:49 UTC 2023 INFO: backup command begin 2.47: --config=/etc/pgbackrest/pgbackrest.conf --exec-id=50270-c22c874e --lock-path=/tmp/pgbackrest/backup.lock --log-level-console=detail --pg1-host=172.31.45.240 --pg1-host-user=postgres --pg1-path=/var/lib/postgresql/14/main --pg1-port=5432 --repo1-path=/var/lib/pgbackrest --stanza=demo --type=full full backup: 20230911-140450F timestamp start/stop: 2023-09-11 14:04:50+00 / 2023-09-11 14:06:36+00 wal start/stop: 00000001000000010000000E / 00000001000000010000000E database size: 1.7GB, database backup size: 1.7GB repo1: backup set size: 295MB, backup size: 295MB
Note: Whenever a backup begins, if the pgbackrest.conf file lacks a retention setting, pgBackRest will not trigger the expire command. Instead, it will provide a space constraints warning, which can be disregarded since we handle the expire operation independently.
INFO: backup command begin 2.47: --config=/etc/pgbackrest/pgbackrest.conf --exec-id=53321-0623da5d --lock-path=/tmp/pgbackrest/backup.lock --log-level-console=detail --pg1-host=172.31.45.240 --pg1-host-user=postgres --pg1-path=/var/lib/postgresql/14/main --pg1-port=5432 --repo1-path=/var/lib/pgbackrest --stanza=demo --type=full WARN: option 'repo1-retention-full' is not set for 'repo1-retention-full-type=count', the repository may run out of space HINT: to retain full backups indefinitely (without warning), set option 'repo1-retention-full' to the maximum. <backup starts > P00 INFO: expire command begin 2.47: --config=/etc/pgbackrest/pgbackrest.conf --exec-id=53321-0623da5d --lock-path=/tmp/pgbackrest/backup.lock --log-level-console=detail --repo1-path=/var/lib/pgbackrest --stanza=demo P00 INFO: option 'repo1-retention-archive' is not set - archive logs will not be expired P00 INFO: expire command end: completed successfully (9ms)
Using this approach, backup and expiration can be initiated as separate processes, preventing the prolonged duration of the expiration operation from hindering subsequent backup attempts. However, maintaining effective monitoring of the repository’s storage capacity is essential to prevent it from running out of space.
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.
May
08
2023
08
2023
--
How To Start Logical Replication in PostgreSQL for Specific Tables Based on a pg_dump
In simpler terms, logical replication in PostgreSQL is a way to copy data from one database to another in a more flexible and customizable manner compared to physical replication. Instead of copying the entire database, logical replication focuses on replicating changes made to individual rows or transactions. To set up logical replication, you create a publication on the source database and a subscription on the target database. When you enable the subscription, a process called the logical replication launcher identifies it and starts an application worker process. The worker then synchronizes the tables by launching table-sync workers, which copy the changes from the source to the target.
This blog post will examine an alternative method for conducting the initial data synchronization in PostgreSQL. Instead of utilizing the table synchronization process by accelerating the synchronization process through a data dump, we will explore the usage of pg_dump and pg_restore.
Lab environment – The lab environment contains below servers:
- One primary and two replicas running on Postgres version 12
- One server running on PostgreSQL version 15
| Server Details | IP Address | PostgreSQL Version |
| Primary | 10.0.0.25 | 12 |
| Replica1 | 10.0.0.80 | 12 |
| Replica2 | 10.0.0.40 | 12 |
| Logical replica (Target Server) | 10.0.0.221 | 15 |
In this lab, I’ve set up two replicas to make this approach work. I needed to pause the WAL replay process, so having two replicas, including replica2, helps ensure that ongoing replication, which may be important for meeting business requirements, isn’t affected by this approach.
Step 1: Create a publication on the primary server
The first step is to create a publication on the primary server, which defines the tables that will be replicated. Connect to the primary server using the PostgreSQL command line tool psql, and execute the following commands
percona=# create publication my_pub; CREATE PUBLICATION percona=# alter publication my_pub add table table1; ALTER PUBLICATION
These commands create a publication named my_pub and add the table table1 to it. The publication is now ready to replicate changes made to table1 on the primary server.
Step 2: Create a logical replication slot on the primary server
Next, we need to create a logical replication slot on the primary server to capture the changes made to the table1. Execute the following command in the psql console on the primary server:
percona=# SELECT pg_create_logical_replication_slot('my_pub_slot', 'pgoutput');
pg_create_logical_replication_slot
------------------------------------
(my_pub_slot,0/1C8BEB58)
This command creates a logical replication slot named my_pub_slot using the pgoutput output plugin, which is a built-in output plugin for logical replication in PostgreSQL. The slot will capture the changes made to the table1 and store them for replication.
Step 3: Create a subscription on the target server
On the target logical replication server, we need to create a subscription that connects to the primary server and replicates the changes made to table1. Connect to the target server using psql, and execute the following commands. Note that we have omitted the password from the command, as it is located in the .pgpass file
percona=# CREATE SUBSCRIPTION my_pub_subscription CONNECTION 'host=10.0.0.25 port=5432 dbname=percona' PUBLICATION my_pub WITH (copy_data = false, create_slot=false, enabled=false, slot_name=my_pub_slot); CREATE SUBSCRIPTION
This command creates a subscription named my_pub_subscription that connects to the primary server at IP address 10.0.0.25, using the Percona database, and replicates the publication my_pub, which includes table1. The subscription is created with the following options:
copy_data=false: This option specifies that we will not copy the data from the publisher using logical replication, but instead, we will use the pg_dump tool to copy the data later.
create_slot=false: This option specifies that we will not create a replication slot on the target server, as we have already created a logical replication slot on the primary server.
enabled=false: This option specifies that the subscription will not be enabled immediately, as we want to perform some additional steps before enabling it.
slot_name=my_pub_slot: This option specifies the name of the logical replication slot that we created on the primary server.
Step 4: Pause replication on replica2::10.0.0.40 to pg_dump a data snapshot for table1 and restore it on the target logical replication node
On replica2:
postgres@ip-10-0-0-40:~$ psql -d percona -c 'select pg_wal_replay_pause() ' pg_wal_replay_pause --------------------- (1 row) postgres@ip-10-0-0-40:~$ psql -d percona -c 'select pg_is_wal_replay_paused() ' pg_is_wal_replay_paused ------------------------- t (1 row)
NOTE: For additional details on how to use and apply pg_dump, I suggest taking a look at my colleague’s blog posts, which covers several topics, including simple backup and restore, PostgreSQL upgrade using pg_dump and pg_restore, PostgreSQL upgrade using pg_dumpall, and working with PostgreSQL dump manifests.
Now replication is paused on replica2, and transactions on the primary node won’t be replicated to replica2. To confirm whether the replay is paused on replica2, we insert a new record into table1 on the primary server, increasing the total count to 1,000,001. We then check the record count on replica1 and replica2. Replica1 shows the expected count of 1,000,001, while replica2 has a replay paused status of “t” and a record count of 1,000,000, indicating that the newly inserted record has not been replicated to it due to the pause in replication.
On primary server: Count and insert records in table1.
postgres@ip-10-0-0-25:~$ psql -d percona -tc 'select count(*) from table1'
1000000
percona=# insert into table1(name,age) values('KB','29');
INSERT 0 1
percona=# select count(*) from table1;
count
---------
1000001
(1 row)
We can verify whether the newly inserted record has been replicated to replica1 and replica2. Since replication is paused on replica2, it will not have the record we inserted.
Replica1:
postgres@ip-10-0-0-80:~$ psql -d percona -U postgres -tc 'select count(*) from table1;' 1000001
Replica2:
postgres@ip-10-0-0-40:~$ psql -p 5434 -d percona -U postgres -tc 'select pg_is_wal_replay_paused();' t postgres@ip-10-0-0-40:~$ psql -p 5434 -d percona -U postgres -tc 'select count(*) from table1;' 1000000
Step 5: Note the replay LSN for replica2 (e.g., 0/1C8BEC40) on the primary
postgres@ip-10-0-0-25:~$ psql -d percona -c 'select client_addr, application_name, replay_lsn from pg_stat_replication where application_name = 'replica2';' client_addr | application_name | replay_lsn -------------+------------------+------------ 10.0.0.40 | replica2 | 0/1C8BEC40 (2 rows)
Step 6: Dump only the data for table1 from replica2 as we already have the schema on the target node
postgres@ip-10-0-0-40:~$ pg_dump -d percona --data-only --table=table1 -Fc > table1_dmp.db
Step 7: Restore the data on the target node
pg_restore -h 10.0.0.221 -U postgres -d percona table1_dmp.db
NOTE: The custom compression format employed by pg_dump has several advantages, such as reducing the size of dump files and speeding up backup and restore processes. It also enables restoring multiple tables simultaneously by utilizing the jobs switch. However, when restoring data from a dump file, it’s essential to include constraints, if they exist, on the target tables after restoring the data. This can be done by using the section=post-data switch.
Step 8: Resume the logical replication worker process to capture continuous data changes for table1
On the primary node, there may be more data in table1 compared to replica2 and the target logical replication node. Once we enable logical replication, the delta data (i.e., the changes that occur after the logical replication is set up) will be replicated by the logical replication worker to the target server. This ensures that any new changes made to table1 on the primary node will be accurately replicated to the target server through logical replication.
postgres@ip-10-0-0-25:~$ psql -d percona -c 'select count(*) from table1' count --------- 1000100 (1 row)
Step 9: On the target server, advance the replication origin on the target to the location from replay_lsn (gathered from replica2) in Step5 for the logical replica target
percona=# select roident, subname, roname from pg_subscription sub, pg_replication_origin ro where 'pg_' || sub.oid = ro.roname;
roident | subname | roname
---------+----------------------+----------
1 | my_pub_subscription | pg_16407
(1 row)
percona=# select pg_replication_origin_advance('pg_16407', '0/1C8BEC40');
pg_replication_origin_advance
-------------------------------
(1 row)
pg_replication_origin_advance is used for replication progress for the given node to the given location. This is primarily useful for setting up the initial location or setting a new location after configuration changes and similar.
Step 10: Enable the subscription
percona=# ALTER SUBSCRIPTION my_pub_subscription ENABLE; ALTER SUBSCRIPTION
After successfully enabling the subscription, we can observe that the changes in table1 are being logically replicated to the target server.
postgres@ip-10-0-0-221:~$ psql -d percona psql (15.2 (Ubuntu 15.2-1.pgdg22.04+1)) Type "help" for help. percona=# SELECT COUNT(*) FROM table1; -[ RECORD 1 ]-- count | 1000100
This confirms that the data in table1 is being replicated from the source to the target server through logical replication. Going forward, any changes made to table1 on the source server will be replicated to the target server as well. It’s important to note that resuming replication for replica2 to ensure that the changes are properly applied. This can be done using the pg_wal_replay_resume() function in PostgreSQL:
postgres=# SELECT pg_wal_replay_resume(); pg_wal_replay_resume ---------------------- (1 row)
When considering the implementation of this method for sizable tables, it is essential to keep a close eye on the replication slot age to avert any potential wraparound complications. Furthermore, refrain from utilizing the count(*) function on extensive tables, as it can consume a lot of resources and take up a significant amount of time.
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.
Nov
15
2022
15
2022
--
pgBackRest Restoration Scenarios
In one of our previous posts—pgBackRest: A Great Backup Solution and a Wonderful Year of Growth—we discussed the installation of pgBackRest and the backup, quick restore command. It would be ideal to have a restoration activity scheduled as per business needs so that backups are validated and provide critical metrics on recovery time. In this blog post, we will check out some restoration scenarios concerning the pgBackRest backup tool.
Scenarios
- Restoring the backup on a different host
- Restoring a single database from the Postgres cluster
- Restoring the backup to a specific point in time
- Restoring the backup on a different host to start as a streaming standby
- Restoring only the delta changes
To proceed with testing the above-mentioned restoration scenarios, we need the following:
- pgBackRest of the same version installed on source and target machines
- Repository information i.e, the place where we store our backups could be S3, GCS, Azure buckets, or dedicated storage.
- The target server should have access to the repository host via TLS or SSH
For testing the above-mentioned scenarios, I have a PostgreSQL primary server, two replica servers, a repository server, and a spare server.
My repository information: a FULL and INCR backup is available in the repository.
postgres@pgBackRest:~$ pgBackRest --config=/etc/pgBackRest/pgBackRest.conf --log-level-console=detail --stanza=demo info
stanza: demo
status: ok
cipher: none
db (current)
wal archive min/max (11): 00000001000000000000001E/00000001000000000000002A
full backup: 20221104-102722F
timestamp start/stop: 2022-11-04 10:27:22 / 2022-11-04 10:27:32
wal start/stop: 000000010000000000000028 / 000000010000000000000028
database size: 37.6MB, database backup size: 37.6MB
repo1: backup set size: 4.5MB, backup size: 4.5MB
incr backup: 20221104-102722F_20221104-102807I
timestamp start/stop: 2022-11-04 10:28:07 / 2022-11-04 10:28:12
wal start/stop: 00000001000000000000002A / 00000001000000000000002A
database size: 37.6MB, database backup size: 8.3KB
repo1: backup set size: 4.5MB, backup size: 402B
backup reference list: 20221104-102722F
Restoring the backup on a different host
In this scenario, we will test the backup by restoring it to the spare server. My spare server’s pgBackRest conf has the information about the repository host, repository path, repository host user, and required PostgreSQL version installed and access to the repository.
pgBackRest can be used entirely by command line parameters but having a configuration file has more convenience. Below is my spare server pgBackRest configuration file.
postgres@spareserver:~$ cat /etc/pgbackrest/pgbackrest.conf [global] # repo details repo1-path=/pgrdbackups # repository storage derails repo1-host=192.168.64.18 # repository host address repo1-host-user=postgres # user details to access the repo [demo] pg1-path=/var/lib/postgresql/11/main # data_dir path to restore the backup
I will go ahead and restore the backup available in my repository onto the spare server. By default, it restores the latest available backup set available in the repository.
postgres@spareserver:~$ pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --log-level-console=info --stanza=demo restore 2022-11-04 10:39:42.175 P00 INFO: restore command begin 2.41: --config=/etc/pgBackRest/pgBackRest.conf --exec-id=16557-1a840ffa --log-level-console=info --pg1-path=/var/lib/postgresql/11/main --repo1-host=192.168.64.18 --repo1-host-user=postgres --repo1-path=/pgrdbackups --stanza=demo 2022-11-04 10:39:42.959 P00 INFO: repo1: restore backup set 20221104-102722F_20221104-102807I, recovery will start at 2022-11-04 10:28:07 2022-11-04 10:39:50.104 P00 INFO: write /var/lib/postgresql/11/main/recovery.conf 2022-11-04 10:39:50.116 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started) 2022-11-04 10:39:50.124 P00 INFO: restore size = 37.6MB, file total = 1535 2022-11-04 10:39:50.125 P00 INFO: restore command end: completed successfully (7954ms) <change archive_commad on the spare server> and start the database pg_ctl -D <data_dir> start
We need to make sure the archive_command is altered to a different path or disabled to prevent Postgres from writing archives to the existing repo upon starting on the spare server.
Restoring a single database from the Postgres cluster
In this scenario, we will restore only a single database on the spare server. I have below mentioned databases on my primary database.
Primary server:
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
mydb | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
percona | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
postgres=# \l+ percona
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
---------+----------+----------+---------+---------+-------------------+--------+------------+-------------
percona | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | 157 MB | pg_default
We will restore only the Percona database to my spare server.
postgres@spareserver:~$ pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --log-level-console=info --stanza=demo --db-include=percona restore 2022-11-04 10:58:05.869 P00 INFO: restore command begin 2.41: --config=/etc/pgBackRest/pgBackRest.conf --db-include=percona --exec-id=16647-4f3aa57d --log-level-console=info --pg1-path=/var/lib/postgresql/11/main --repo1-host=192.168.64.18 --repo1-host-user=postgres --repo1-path=/pgrdbackups --stanza=demo 2022-11-04 10:58:07.088 P00 INFO: repo1: restore backup set 20221104-105706F, recovery will start at 2022-11-04 10:57:06 2022-11-04 10:58:15.791 P00 INFO: write /var/lib/postgresql/11/main/recovery.conf 2022-11-04 10:58:15.805 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started) 2022-11-04 10:58:15.816 P00 INFO: restore size = 187.4MB, file total = 1548 2022-11-04 10:58:15.817 P00 INFO: restore command end: completed successfully
Start the Postgres cluster on the spare server and check the Percona database.
postgres@spareserver:~$ pg_ctlcluster 11 main start
postgres@spareserver:~$ psql postgres
psql (14.5 (Ubuntu 2:14.5-3.jammy), server 11.17 (Ubuntu 2:11.17-3.jammy))
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
mydb | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
percona | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
postgres=# \l+ percona
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
---------+----------+----------+---------+---------+-------------------+--------+------------+-------------
percona | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | 157 MB | pg_default |
After restoring the specific database you will still get to see the other user databases as well. However, when a user will try to connect to them (i.e. apart from the database specified with –db-include option) the connection will not happen and the user will get a FATAL error like this: “FATAL: relation mapping file “base/xx/pg_filenode.map” contains invalid data”.
Restoring the backup to a specific point in time
Point-in-time recovery is possible with the pgBackRest, Consider a table/database has been dropped and needs to get restored. In this situation, we need the timestamp of the drop event, pgBackRest backup, and archives.
On my primary node, I have a table named pitr in the Percona database. This table has dropped at timestamp 2022-11-04 14:24:32.231309+05:30.
percona=# \dt+ pitr
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+------+-------+----------+-------------+------------+-------------
public | pitr | table | postgres | permanent | 8192 bytes |
(1 row)
percona=# select now();drop table pitr;
now
----------------------------------
2022-11-04 14:24:32.231309+05:30
(1 row)
DROP TABLE
percona=# select pg_switch_wal();
pg_switch_wal
---------------
0/7B0162A0
(1 row)
By using the pgBackRest restore option with the recovery type command we can achieve Point-In-Time-Recovery, by default this type restores the archive to the end of the stream, In this scenario, we will specify the exact time stamp to restore the table, xid and lsn can also be provided to the type command.
pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --log-level-console=info --stanza=demo --db-include=percona --type=time "--target=2022-11-04 14:24:31" restore 2022-11-04 14:35:35.389 P00 INFO: restore command begin 2.41: --config=/etc/pgBackRest/pgBackRest.conf --db-include=percona --exec-id=17804-ca30e9a3 --log-level-console=info --pg1-path=/var/lib/postgresql/11/main --repo1-host=192.168.64.18 --repo1-host-user=postgres --repo1-path=/pgrdbackups --stanza=demo --target="2022-11-04 14:24:31" --type=time 2022-11-04 14:35:36.279 P00 INFO: repo1: restore backup set 20221104-105706F, recovery will start at 2022-11-04 10:57:06 2022-11-04 14:35:44.054 P00 INFO: write /var/lib/postgresql/11/main/recovery.conf 2022-11-04 14:35:44.069 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started) 2022-11-04 14:35:44.084 P00 INFO: restore size = 187.4MB, file total = 1548 2022-11-04 14:35:44.086 P00 INFO: restore command end: completed successfully
Start the Postgres cluster and check the pitr table.
postgres@spareserver:~$ pg_ctlcluster 11 main start
percona=# \dt pitr
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | pitr | table | postgres
Restoring the backup on a different host to start as a streaming standby
This approach will be useful in case we need to rebuild a crashed standby node or add a new standby to the existing Postgres cluster.
Primary server:
Two replica servers are connected to the primary server.
postgres=# select usename,client_addr,sync_state,state from pg_catalog.pg_stat_replication ; usename | client_addr | sync_state | state ---------+---------------+------------+----------- repl | 192.168.64.16 | async | streaming repl | 192.168.64.17 | async | streaming
Let’s go ahead and add another replica using the pgBackRest.
postgres@spareserver:~/11/main$ pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --log-level-console=info --stanza=demo --type=standby --recovery-option=primary_conninfo='user=repl password=<redacted> host=192.168.64.10 application_name=spare' restore 2022-11-04 15:02:49.142 P00 INFO: restore command begin 2.41: --config=/etc/pgBackRest/pgBackRest.conf --exec-id=18057-b13cb8f4 --log-level-console=info --pg1-path=/var/lib/postgresql/11/main --recovery-option="primary_conninfo=user=repl password=test host=192.168.64.10 application_name=spare" --repo1-host=192.168.64.18 --repo1-host-user=postgres --repo1-path=/pgrdbackups --stanza=demo --type=standby 2022-11-04 15:02:49.642 P00 INFO: repo1: restore backup set 20221104-105706F, recovery will start at 2022-11-04 10:57:06 2022-11-04 15:02:57.767 P00 INFO: write /var/lib/postgresql/11/main/recovery.conf 2022-11-04 15:02:57.778 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started) 2022-11-04 15:02:57.791 P00 INFO: restore size = 187.4MB, file total = 1548 2022-11-04 15:02:57.792 P00 INFO: restore command end: completed successfully
The replica server is up and running, Check the pg_stat_replication on the primary node.
postgres=# select usename,client_addr,sync_state,state from pg_catalog.pg_stat_replication ; usename | client_addr | sync_state | state ---------+---------------+------------+----------- repl | 192.168.64.16 | async | streaming repl | 192.168.64.17 | async | streaming repl | 192.168.64.19 | async | streaming (3 rows)
Restoring only the delta changes
The delta option allows pgBackRest to automatically determine which files in the database cluster directory can be preserved and which ones need to be restored from the backup, Consider the situation as we already have a backup restored on a spare server and now we will perform the delta restore on the spare server again to get the delta data that has been backed up since the last restore.
postgres@spareserver:~$ pg_ctlcluster 11 main stop postgres@spareserver:~$ pgbackrest --config=/etc/pgbackrest/pgbackrest.conf --log-level-console=info --stanza=demo --delta restore 2022-11-04 15:36:57.101 P00 INFO: restore command begin 2.41: --config=/etc/pgBackRest/pgBackRest.conf --delta --exec-id=18175-ac9d25dc --log-level-console=info --pg1-path=/var/lib/postgresql/11/main --repo1-host=192.168.64.18 --repo1-host-user=postgres --repo1-path=/pgrdbackups --stanza=demo 2022-11-04 15:36:58.130 P00 INFO: repo1: restore backup set 20221104-153559F, recovery will start at 2022-11-04 15:35:59 2022-11-04 15:36:58.136 P00 INFO: remove invalid files/links/paths from '/var/lib/postgresql/11/main' 2022-11-04 15:37:04.155 P00 INFO: write /var/lib/postgresql/11/main/recovery.conf 2022-11-04 15:37:04.168 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started) 2022-11-04 15:37:04.181 P00 INFO: restore size = 202.2MB, file total = 2136
Overall, pgBackRest is an incredibly popular open-source database backup tool available for PostgreSQL. We also encourage you to try our product Percona Distribution for PostgreSQL, as pgBackRest tool is part of our distribution for backups.