The ALTER DEFAULT PRIVILEGES command allows us to set the privileges that will be applied to objects created in the future. It’s important to note that this does not affect privileges assigned to existing objects; default privileges can be set globally for objects created in the current database or in specified schemas. There are many […]
06
2024
Dispelling Myths About PostgreSQL Default Privileges
05
2023
The Magic of PITR, pg_upgrade, and Logical Replication When Used Together for PostgreSQL Version Upgrades
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:
- Size of the database
- An approach that we are considering with or without a hard link
- 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.
- Verify the PostgreSQL parameters
- Create the replication user
- Identify tables without Primary or Unique Indexes
- Set replica identity
- Create the PUBLICATION
- Create a REPLICATION SLOT
- Take a physical backup
- Get the current LSN position from the primary
- Configure PITR parameters
- Perform PITR with the LSN
- Create the PostgreSQL-15 cluster
- Perform major version upgrade using pg_upgrade
- ANALYZE upgraded database
- Create the SUBSCRIPTION
- Advance the SUBSCRIPTION
- Enable the SUBSCRIPTION
- 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.
04
2023
PostgreSQL Role Inheritance in Reverse: Discovering Descendant Roles in Reverse Gear
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:
- Input Parameter: The function takes a single input parameter,
username,
which is the role you want to start from. - 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.
- Recursive Step: The recursive part of the query identifies roles that inherit permissions from other roles, effectively tracing the hierarchy upward.
- Result: The query returns a table with columns for the child role, parent role, depth (level in the hierarchy), and the inheritance path.
- 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.
29
2023
Find the WAL Count Between Two Segments in PostgreSQL
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.
22
2022
PostgreSQL Role Inheritance at a Glance
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.