Predefined roles introduced in PostgreSQL 14 reduced my responsibilities

 

User management is a crucial role in the database, and the DBA should assign suitable permissions to users. For example, some users require read-only access to all tables, while others require read/write access (application user).

Now, if I want to allow the read or read-write access to the user, I need to perform specific instructions on the database.

Let's take example for read and read-write

Read

1. Grant select on all existing tables in schema to user

2. Alter default privileges for feature tables

3. Grant usage on schema

 

Read-Write

 

1. Grant select,insert,delete,update on all existing tables in schema to user

2. Alter default privileges for feature tables

3. Grant usage on schema

 

If there are comparable requirements for additional users, it is preferable to construct separate roles with different permissions, such as read and write roles, and then grant the roles to users depending on the requirements.

 

I used to follow the mentioned procedure, but not anymore after PostgreSQL 14. A new set of predefined roles has been introduced in PostgreSQL 14, which will simplify DBA work while providing permission to users, as seen below.

 

 

 

If I intend to perform the same activity to give read or read-write, I will only follow easy procedures.

 

Read

 

Grant pg_read_all_data to user ;

 

Read-Write

 

Grant pg_read_all_data,pg_write_all_data to user ;

 

Refer : https://www.postgresql.org/docs/14/predefined-roles.html

 

 

 

 

 

 

Enhancement to Logical replication in PostgreSQL 16

 

Big transactions used to cause slowness on the target database, but not anymore. In PostgreSQL 16, a new option called parallel(in subscriber) was introduced, which speeds up writing to the target database.Before PostgreSQL 16, for big transactions, the publisher transmits data via multiple streams. On the subscriber-side, the apply worker writes modifications into temporary files and applies the complete transaction after receiving the commit.

  Let's understand new parameter,catalog table change related to above

 Parameter:

 max_parallel_apply_workers_per_subscription: Default value is 2 and these workers are taken from max_logical_replication_workers.  Maximum number of parallel apply workers per subscription. This parameter controls the amount of parallelism for streaming of in-progress transactions with subscription parameter streaming = parallel.

Catalog table:

 In pg_subscription, substream column gives information about the option used while creating the subscriber.

I did small testing to check advantage of this option. I have created two databases on same server running on 5432, 5433. 

Table create and Insert

create table replication_tabl(id bigint primary key, rand_data_1 varchar(50),dt timestamp,rand_data_2 varchar(50));
insert into replication_tabl values(generate_series(1,10000000),md5(random()::text),now(),md5(random()::text));

 

Set parameters in postgresql.conf, entry in pg_hba.conf for replication user.

postgresql.conf

wal_level='logical'

pg_hba.conf

host     blog     postgres     127.0.0.1/32     md5

Restart the db instance after settingup the parameters.

 

Without Parallel option:

psql -d blog -h 127.0.0.1 -p 5432 "create publication blog_pub for table replication_tabl;"
psql -d blog -h 127.0.0.1 -p 5433 create subscription blog_sub connection 'dbname=blog host=127.0.0.1 port=5432 password=passsword user=postgres ' publication blog_pub;

Workers running on operating system

postgres: logical replication launcher
postgres: logical replication apply worker for subscription 16403

 

 Parallel option :

psql -d blog -h 127.0.0.1 -p 5432 "create publication blog_pub for table replication_tabl;"
psql -d blog -h 127.0.0.1 -p 5433 -c "create subscription blog_sub connection 'dbname=blog host=127.0.0.1  port=5432' publication blog_pub WITH (streaming=parallel);"

Workers running on operating system

postgres: logical replication parallel apply worker for subscription
postgres: parallel worker for PID 747256
postgres: parallel worker for PID 747256

Restart the db instance after settingup the parameters.

below tables shows time taken to apply data on target database. 

 

 

Note: When you are planing to use this option please analyze your workload and resource.
 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

How to produce DDLs for Views and MViews using pg_dump in the absence of a GUI client.

 

pg_dump is a utility for backing up a PostgreSQL database. It creates consistent backups even when the database is being used concurrently. pg_dump does not prevent other users from using the database.

There are various switches/options supported in the pg_dump and we will be seeing usage of -t option. This option allows to pull pattern match DDL alone with data(only for tables)

-t - option can be used to dump the definition of matching views, materialized views, foreign tables, and sequences

Example: 

 

I created view as given below

postgres=# \d+ test_vw

View "public.test_vw"

Column | Type | Collation | Nullable | Default | Storage | Description

--------+-----------------------+-----------+----------+---------+----------+-------------

i | integer | | | | plain |

seq | double precision | | | | plain |

name | character varying(10) | | | | extended |

View definition:

SELECT i,

i_gen::double precision / 10::double precision AS seq,

name

FROM test;

Let's generate DDL of view using pg_dump.

pg_dump -t public.test_vw

 

pg_dump output:

pg_dump: creating VIEW "public.test_vw"

--

-- TOC entry 216 (class 1259 OID 34287)

-- Name: test_vw; Type: VIEW; Schema: public; Owner: postgres

--

CREATE VIEW public.test_vw AS

SELECT i,

((i_gen)::double precision / (10)::double precision) AS seq,

name

FROM public.test;

ALTER VIEW public.test_vw OWNER TO postgres;

-- Completed on 2024-03-02 04:04:31 UTC

--

-- PostgreSQL database dump complete

--