Personal blog of Yzmir Ramirez

Recently, I was reading a brilliant blog by Perconian David Gonzalez titled The 1-2-3 for PostgreSQL Logical Replication Using an RDS Snapshot, exploring the intricacies of setting up logical replication on Amazon RDS using RDS snapshots. It was a fascinating read, shedding light on leveraging AWS snapshots’ capabilities to avoid initial data copy within the RDS environment.

Inspired by David’s insights, I embarked on a journey to explore logical replication from a different perspective – within the realm of on-premises server databases. 

We have several PostgreSQL versions that support logical decoding to replicate data changes from a source database to a target database, which is a cool and very powerful tool that gives the option to replicate all the tables in a database, only one schema, a specific set of tables, or even only some columns/rows. Also, it is a helpful method for version upgrades since the target database can run on a different (minor or major) PostgreSQL version.

In this post, we describe the simplest and most basic steps we used to set up the logical replication solution, avoiding the initial copy of data from the source database to the target and creating the target instance using the PITR/Standby database instance and pg_upgrade. Certainly, you can take advantage of this when you work with a large data set, and the initial copy could lead to long timeframes or network saturation.

The scenario

Service considerations

In this exercise, we wanted to perform a major version upgrade from PostgreSQL v12.16 to PostgreSQL v15.4. We could perform a direct upgrade using the build-in option pg_upgrade, but that requires a downtime window that can vary depending on the:

1. Size of the database
2. An approach that we are considering with or without a hard link
3. ANALYZE time after the pg_upgrade

Therefore, by considering David’s blog, we chose logical replication as the mechanism to achieve the objective. We can aim for a quicker switch-over if we create the new instance in the desired version and just replicate all the data changes. Then, we need a small downtime window just to move the traffic from the original instance to the upgraded one.

Prerequisites

• A DB user with privileges to create the PUBLICATION on source and the SUBSCRIPTION on target. 
• Enough disk space on the same server to fit one more database of the same size; the target database can also be hosted on another server to avoid the write load.

While we’ve contemplated taking a physical backup and conducting PITR, it’s worth noting that PITR can also be executed on the existing replica or delayed replica to expedite the process.

The 1-2-3 Steps

Per the title of this post, the next is the list of steps to perform the PITR, then pg_upgrade to v15.4, and then set up a PostgreSQL logical replication between a PostgreSQL v12.16 and a v15.4.

1. Verify the PostgreSQL parameters
2. Create the replication user
3. Identify tables without Primary or Unique Indexes
4. Set replica identity
5. Create the PUBLICATION
6. Create a REPLICATION SLOT
7. Take a physical backup
8. Get the current LSN position from the primary
9. Configure PITR parameters
10. Perform PITR with the LSN
11. Create the PostgreSQL-15 cluster
12. Perform major version upgrade using pg_upgrade
13. ANALYZE upgraded database
14. Create the SUBSCRIPTION
15. Advance the SUBSCRIPTION 
16. Enable the SUBSCRIPTION
17. Plan for the cutover

1. Verify the PostgreSQL parameters

Ensure the PostgreSQL parameters are set correctly for physical backups and logical replication.

demodb=# select name,setting from pg_settings where name in (
        'wal_level',
        'max_worker_processes',
        'max_replication_slots',
        'max_wal_senders') ;
         name          | setting
-----------------------+---------
 max_replication_slots | 10
 max_wal_senders       | 10
 max_worker_processes  | 8
 wal_level             | replica
(4 rows)

2. Create the Replication User

Create a replication user and grant the necessary privileges.

demodb=# CREATE USER pgrepuser WITH password 'SECRET' replication;
CREATE ROLE
demodb=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO pgrepuser;
GRANT

3. Identify Tables without Primary or Unique indexes

List tables without primary keys or unique indexes.

demodb=# SELECT schemaname, relname AS table_name
FROM pg_stat_user_tables
WHERE pg_stat_user_tables.relid NOT IN (
    SELECT indexrelid
    FROM pg_index
    WHERE indisprimary OR indisunique
);
 schemaname |   table_name
------------+-----------------
 public     | test_log_update
(1 row)

4. Set replica identity

Set the replica identity to “FULL” for tables without primary keys or unique indexes.

demodb=# ALTER TABLE public.test_log_update REPLICA IDENTITY FULL;
ALTER TABLE

5. Create the PUBLICATION

Create a publication for all tables.

demodb=# CREATE PUBLICATION pglogical_rep01 FOR ALL TABLES;
CREATE PUBLICATION

6. Create a REPLICATION SLOT

Create a replication slot to capture changes.

demodb=# SELECT pg_create_logical_replication_slot('pglogical_rep01', 'pgoutput');
 pg_create_logical_replication_slot
------------------------------------
 (pglogical_rep01,0/1776FD0)
(1 row)

PITR steps
7. Take a physical backup

Take a physical backup from the primary database.

pg_basebackup -h 127.0.0.1 -p 5432 -S slot_logical_upgrade -R -c fast -Xs -P -D /data/PG12_new -C
32610/32610 kB (100%), 1/1 tablespace

8. Modify PostgreSQL configuration

Adjust parameters in the postgresql.conf of the new data directory.

port = 5433
data_directory = '/data/PG12_new'
hba_file = '/data/PG12_new/pg_hba.conf'
external_pid_file = '/var/run/postgresql/PG12_new.pid'

9. Get the Current LSN

Retrieve the current LSN from the primary database.

demodb=# SELECT pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 0/9000060
(1 row)

10. Configure PITR parameters

Add PITR parameters to postgresql.auto.conf in the new data directory.

recovery_target_lsn='0/9000060' 
recovery_target_action='promote'

11. Start the new instance to perform the PITR with LSN

Start the new PostgreSQL instance. This will perform the PITR with LSN and promote the database after the recovery.

/usr/lib/postgresql/12/bin/pg_ctl -D /data/PG12_new start
.
2023-11-08 05:31:06.030 UTC [108718] LOG:  listening on IPv4 address "0.0.0.0", port 5433
.
server started

Here, we have the database instance ready with v12.16 recovered till the LSN mentioned.

Perform the pg_upgrade

12. Create the PostgreSQL 15 cluster

Initialize the PostgreSQL 15 cluster (this will be our target database).

/usr/lib/postgresql/15/bin/initdb -D /data/PG15

13. Copy configuration files

Copy configuration files from the PG12 cluster to the PG15 cluster.

cp /data/PG12_new/pg_hba.conf /data/PG12_new/pg_ident.conf /data/PG12_new/postgresql.conf /data/PG15/

14. Modify PG15 configuration

Adjust parameters in the postgresql.conf of the PG15 cluster.

port = 5434
data_directory = '/data/PG15'
hba_file = '/data/PG15/pg_hba.conf'
external_pid_file = '/var/run/postgresql/PG15.pid'

15. Stop the PG12 PITR cluster

Halt the PostgreSQL v12.16 PITR cluster to proceed with pg_upgrade.

/usr/lib/postgresql/12/bin/pg_ctl -D /data/PG12_new stop
waiting for server to shut down.... done
server stopped

16. Perform pg_upgrade

Execute the pg_upgrade process.

/usr/lib/postgresql/15/bin/pg_upgrade -d /data/PG12_new -D /data/PG15 -p 5433 -P 5434 -b /usr/lib/postgresql/12/bin -B /usr/lib/postgresql/15/bin --link
 /usr/lib/postgresql/15/bin/pg_upgrade -d /data/PG12_new -D /data/PG15 -p 5433 -P 5434 -b /usr/lib/postgresql/12/bin -B /usr/lib/postgresql/15/bin --link
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
.
Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
.
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /usr/lib/postgresql/15/bin/vacuumdb --all --analyze-in-stages

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

17. Start the new PG15 cluster

/usr/lib/postgresql/15/bin/pg_ctl -D /data/PG15 start

18. Perform ANALYZE process

Start the ANALYZE process as part of the post-upgrade activity and proceed with the next steps in parallel.

/usr/lib/postgresql/15/bin/vacuumdb --all --analyze-in-stages

Target Database Side

19. Create subscription

Set up a subscription to replicate data from the source database to the target.

demodb=# CREATE SUBSCRIPTION pglogical_sub01
CONNECTION 'host=127.0.0.1 port=5432 dbname=demodb user=pgrepuser password=SECRET'
PUBLICATION pglogical_rep01
WITH (
    COPY_DATA = false,
    create_slot = false,
    enabled = false,
    connect = true,
    slot_name = 'pglogical_rep01'
);
CREATE SUBSCRIPTION

20. Advance the subscription
20.1 Get the subscription ID

emodb=# SELECT 'pg_'||oid::text AS "external_id" FROM pg_subscription WHERE subname = 'pglogical_sub01';
 external_id
-------------
 pg_16413
(1 row)

20.2 Advance the replication origin using the pre-fetched LSN

demodb=# SELECT pg_replication_origin_advance('pg_16413', '0/9000060');
 pg_replication_origin_advance
-------------------------------

(1 row)

20.3 Enable the subscription

Enable the subscription to start receiving replicated data.

demodb=# ALTER SUBSCRIPTION pglogical_sub01 ENABLE;
ALTER SUBSCRIPTION

Once we are done with all the steps, the data changes should flow from the source database to the target; we can check the status at the pg_stat_replication view on the source side.

Cutover
21. Reset the sequences

Before opening the database for the applications, it’s crucial to handle sequences by resetting them with the maximum values from their corresponding columns. We have used the below query to reset the sequences:

demodb=# DO
$$
DECLARE
v_sql text DEFAULT '';
BEGIN
FOR v_sql in (
SELECT
    'SELECT SETVAL(' ||
       quote_literal(quote_ident(sequence_namespace.nspname) || '.' || quote_ident(class_sequence.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(pg_attribute.attname)|| '), 1) ) FROM ' ||
       quote_ident(table_namespace.nspname)|| '.'||quote_ident(class_table.relname)|| ';'
FROM pg_depend
    INNER JOIN pg_class AS class_sequence
        ON class_sequence.oid = pg_depend.objid
            AND class_sequence.relkind = 'S'
    INNER JOIN pg_class AS class_table
        ON class_table.oid = pg_depend.refobjid
    INNER JOIN pg_attribute
        ON pg_attribute.attrelid = class_table.oid
            AND pg_depend.refobjsubid = pg_attribute.attnum
    INNER JOIN pg_namespace as table_namespace
        ON table_namespace.oid = class_table.relnamespace
    INNER JOIN pg_namespace AS sequence_namespace
        ON sequence_namespace.oid = class_sequence.relnamespace
ORDER BY sequence_namespace.nspname, class_sequence.relname
)
LOOP
--raise notice '%',v_sql;
EXECUTE v_sql;
END LOOP;
END
$$;

22. Route the traffic to the upgraded instance

When the time is right, perform the cutover and redirect all traffic to the PostgreSQL-15 instance, ensuring a smooth transition to the upgraded version.

Conclusion

Logical replication combined with Point-In-Time Recovery (PITR) in PostgreSQL offers a powerful strategy for version upgrades without significant downtime. Inspired by insights from RDS and David’s blog, we explored how this approach applies to on-premises databases. This approach will empower database managers to evolve their systems with minimal interruption, leveraging the best of PITR and logical replication for smoother version upgrades.

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.

 

Download Percona Distribution for PostgreSQL Today!

In our previous blog post, PostgreSQL Role Inheritance at a Glance, we delved into the concept of role inheritance in PostgreSQL. We discussed how roles can inherit permissions from other roles, simplifying access control in your database. But what if you need to discover which roles inherit from a specific role? That’s where our new function, “role_inheritance_reverse,” comes into play.

Introducing function role_inheritance_reverse

The role_inheritance_reverse function can be a powerful SQL for PostgreSQL administrators and security experts. It allows you to navigate the role hierarchy in reverse, starting from a specified role and tracing all the descendant roles that inherit permissions from it, directly or indirectly.

Here’s a quick overview of the function

CREATE OR REPLACE FUNCTION role_inheritance_reverse(username character varying)
RETURNS TABLE(username character varying, parent_role character varying, depth integer, inherit_path text)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  WITH RECURSIVE cte AS (
    SELECT member, roleid as child, 1 as d, ''::name as path FROM pg_auth_members WHERE pg_get_userbyid(roleid) = usrname
    UNION ALL
    SELECT m.roleid, m.member as member_of, d + 1, path || '<-' || pg_get_userbyid(cte.child) as path
    FROM cte
    JOIN pg_auth_members m ON m.roleid = cte.child WHERE d < 20
  )
  SELECT distinct pg_get_userbyid(child)::varchar as username,
                  pg_get_userbyid(child)::varchar as parent_role,
                  d::int as depth,
                  substr(path::text || '<-' || pg_get_userbyid(child), 3) as path
  FROM cte
  ORDER BY 3;
END;
$$;

How does role_inheritance_reverse work?

The role_inheritance_reverse function starts with a specified role (given as username) and then explores the role hierarchy backward. Here’s how it works:

1. Input Parameter: The function takes a single input parameter, username, which is the role you want to start from.
2. Recursive Query: The magic happens inside a common table expression (CTE) with a recursive query. Initially, it selects the direct child roles of the specified role.
3. Recursive Step: The recursive part of the query identifies roles that inherit permissions from other roles, effectively tracing the hierarchy upward.
4. Result: The query returns a table with columns for the child role, parent role, depth (level in the hierarchy), and the inheritance path.
5. Ordering: The results are ordered by depth, providing a clear view of the inheritance structure.

To understand this better, let’s revisit the scenario we discussed in the previous blog post – PostgreSQL Role Inheritance at a Glance. Imagine that we have several roles within the database, as outlined below:

postgres=# du
                                   List of roles
 Role name |                         Attributes                     | Member of
-----------+--------------------------------------------------------+----------
 A         |                                                        | {B}
 B         | Cannot login                                           | {E,D}
 C         |                                                        | {E,D,B}
 D         | Cannot login                                           | {}
 E         | Cannot login                                           | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

As illustrated above, role D plays the role of a parent to both B and C, while B takes on the role of a parent to C and A. As a result, both A and C directly inherit permissions from B and indirectly from D. In simpler language, we can view A and C as indirect descendants of D within the role hierarchy. While recognizing this inheritance pattern is relatively straightforward when dealing with a small number of roles, it can become considerably more complex as the number of roles grows. The role_inheritance_reverse function simplifies the task of identifying role inheritance, even in more extensive role hierarchies.

Let’s execute the function for a role “D”:

postgres=# SELECT * FROM role_inheritance_reverse('D');
 username | parent_role | depth | inherit_path
----------+-------------+-------+--------------
 D        | D           |     1 | D
 B        | B           |     2 | D<-B
 C        | C           |     2 | D<-C
 A        | A           |     3 | D<-B<-A
 C        | C           |     3 | D<-B<-C
(5 rows)

Practical use cases

Understanding role inheritance can be incredibly useful in various scenarios:

• Security audits: Determine which roles inherit permissions from sensitive roles, helping you assess potential security risks.
• Access control: Tailor access permissions for child roles based on their inheritance, ensuring a granular and secure access control strategy.
• Troubleshooting: Resolve access-related issues by identifying how roles inherit permissions, enabling you to pinpoint and rectify access problems.
• Documentation: Document your role hierarchy in reverse for reference, compliance, and auditing purposes.

Conclusion

The role_inheritance_reverse function is a valuable addition to your PostgreSQL utility queries. It empowers you to explore role inheritance in reverse, uncovering all the roles that inherit from a specific role.

So, whether you’re conducting a security audit, fine-tuning access control, or simply documenting your role hierarchy, the role_inheritance and role_inheritance_reverse functions are here to make your PostgreSQL role management more efficient and transparent.

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.

 

Download Percona Distribution for PostgreSQL Today!

The PostgreSQL Write-Ahead Log (WAL) is a recording location within the Postgres cluster, capturing all modifications made to the cluster’s data files before being written to the heap. During crash recovery, the WAL contains sufficient data for Postgres to restore its state to the point of the last committed transaction.

Use cases

There may arise circumstances where it becomes necessary to determine the numerical difference between the WAL files. For instance, when recovering from a significant delay or while configuring replication on a sizable database, the recovery process can be time-consuming as new WAL files are replayed. Initially, when setting up replication, the server may not permit login access. In such cases, calculating the disparity in the number of WAL files can provide an estimation of the recovery time, allowing for an assessment of the lag.

Another practical application for calculating the difference between WAL files is in the context of the archiver process. Determining the variance between WAL files makes it possible to estimate the number of remaining files that are yet to be archived.

To calculate the difference between two WAL files, let’s understand the WAL file name format.
The name format for PostgreSQL Write-Ahead Logs (WAL) files is TTTTTTTTXXXXXXXXYYYYYYYY, a 24-character hexadecimal representation of the LSN (Log Sequence Number) associated with the WAL record. The LSN is a unique identifier for each WAL record.

In format TTTTTTTTXXXXXXXXYYYYYYYY, ‘T’ is the timeline, ‘X’ is the high 32-bits from the LSN(Segment number), and ‘Y’ is the low 32-bits of the LSN.

For example, a WAL file name might look like this: “0000000100001234000000AB”.
Here’s a breakdown of the components in the example:

– “00000001”: This represents the timeline ID. It is usually 1 for the default timeline.
– “00012340”: This represents the WAL file number, indicating the sequential order of the WAL file within the timeline.
– “000000AB”: This is the hexadecimal representation of the segment file number.

We can determine the numerical difference between two WAL files with the below sql:

SELECT ABS(('x' || SUBSTRING(current_wal, 1, 8))::bit(32)::int - ('x' || SUBSTRING(old_wal, 1, 8))::bit(32)::int) +
       ABS(('x' || SUBSTRING(current_wal, 9, 8))::bit(32)::int*256 - ('x' || SUBSTRING(old_wal, 9, 8))::bit(32)::int*256) +
       ABS(('x' || SUBSTRING(current_wal, 17))::bit(32)::int - ('x' || SUBSTRING(old_wal, 17))::bit(32)::int)
FROM (select '000000330000006900000047' current_wal, '0000003200000069000000AB' old_wal) as wal_segs;

Let’s create a PostgreSQL function that facilitates the calculation of the numerical difference between two WAL files, making it more convenient to use to determine the variance between them.

CREATE OR REPLACE FUNCTION public.get_walfile_diff(current_wal text, old_wal text)
RETURNS numeric
LANGUAGE plpgsql
AS $function$
DECLARE
v_wal_diff numeric;
BEGIN
IF length(current_wal) != 24 OR length(old_wal) != 24 THEN
RAISE EXCEPTION 'Invalid wal file length';
END IF;
v_wal_diff := (
ABS(('x' || SUBSTRING(current_wal, 1, 8))::bit(32)::int - ('x' || SUBSTRING(old_wal, 1, 8))::bit(32)::int) +
ABS(('x' || SUBSTRING(current_wal, 9, 8))::bit(32)::int*256 - ('x' || SUBSTRING(old_wal, 9, 8))::bit(32)::int*256) +
ABS(('x' || SUBSTRING(current_wal, 17))::bit(32)::int - ('x' || SUBSTRING(old_wal, 17))::bit(32)::int)
);
RETURN v_wal_diff;
END;
$function$;

Examples

postgres=# SELECT public.get_walfile_diff('000000330000006900000047','000000330000006900000048');
 get_walfile_diff
------------------
                1
(1 row)
postgres=# SELECT public.get_walfile_diff('000000330000006900000047','0000003300000069000000AB');
 get_walfile_diff
------------------
              100
(1 row)

Overall, being able to calculate the numerical difference between WAL files contributes to effective management and understanding of Postgres database recovery/archiver processes.

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.

 

Download Percona Distribution for PostgreSQL Today!

PostgreSQL manages database access permissions using the concept of roles. A role can be either a database user or a group of database users, depending on how the role is set up. Roles can own the database objects and assign privileges on those objects to other roles to control who has access to which objects. Furthermore, it is possible to grant membership in a role to another role, thus allowing the member role to use privileges assigned to another role.

PostgreSQL lets you grant permissions directly to the database users. However, as a good practice for security and ease of user-account management, it is recommended that you create multiple roles with specific sets of permissions based on application and access requirements and then assign the appropriate roles to each user. Such assignment of roles can become complex if we assign a role to another role that is already a parent role of some other role.

To understand this with simple words, consider we have multiple roles inside the database as below:

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 A         |                                                            | {B}
 B         | Cannot login                                               | {E,D}
 C         |                                                            | {E,D,B}
 D         | Cannot login                                               | {}
 E         | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

As you can see above, role A is a member of B and B is a member of D and E, so A is inheriting permissions from B directly and from D and E indirectly.  With a small set of roles, we can quickly identify the inheritance by looking at the output of \du, but it would become difficult with a large set of roles. 

The below SQL query can be used to get the role inheritance/cascading:

WITH RECURSIVE cte AS (
SELECT oid ,oid as member_of,1 as d,''::name as path FROM pg_roles r where r.rolname !~ '^pg_'
UNION
SELECT m.roleid,m.member as member_of ,d+1,path||'->'||pg_get_userbyid(cte.oid) as path
FROM cte
JOIN pg_auth_members m ON m.member = cte.oid where d<15
)
SELECT distinct pg_get_userbyid(member_of)::varchar as username, pg_get_userbyid(oid)::varchar as parent_role ,d::int as depth, substr(path::text||'->'||pg_get_userbyid(oid),3) as path FROM cte where d > 1 ORDER BY depth;

username | parent_role | depth |  path
----------+-------------+-------+---------
A        | B           |     2 | A->B
B        | D           |     2 | B->D
B        | E           |     2 | B->E
C        | B           |     2 | C->B
C        | D           |     2 | C->D
C        | E           |     2 | C->E
B        | D           |     3 | A->B->D
B        | D           |     3 | C->B->D
B        | E           |     3 | A->B->E
B        | E           |     3 | C->B->E
(10 rows)

We can also create a function to get the inherited role details for the specific role:

CREATE OR REPLACE FUNCTION role_inheritance(usrname character varying) RETURNS TABLE(username character varying, parent_role character varying, depth integer, inherit_path text)
LANGUAGE plpgsql
AS $$
begin
return query
WITH RECURSIVE cte AS (
SELECT oid ,oid as member_of,1 as d,''::name as path FROM pg_roles r where r.rolname=usrname
UNION
SELECT m.roleid,m.member as member_of ,d+1,path||'->'||pg_get_userbyid(cte.oid) as path
FROM cte
JOIN pg_auth_members m ON m.member = cte.oid where d<15
)
SELECT distinct pg_get_userbyid(member_of)::varchar as username, pg_get_userbyid(oid)::varchar as parent_role ,d::int as depth, substr(path::text||'->'||pg_get_userbyid(oid),3) as path FROM cte WHERE d > 1 order by 3;
end;
$$;

Execute the function for a specific role:

postgres=# select * from role_inheritance('A');
username | parent_role | depth | inherit_path
----------+-------------+-------+--------------
A        | B           |     2 | A->B
B        | D           |     3 | A->B->D
B        | E           |     3 | A->B->E
(3 rows)

Overall, PostgreSQL roles can be used very effectively to handle permissions if we know the impact of granting them. We encourage you to try our product Percona Distribution for PostgreSQL, trusted by numerous global brands across many industries, for a unified experience to monitor, manage, secure, and optimize database environments.

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.

Download Percona Distribution for PostgreSQL Today!