Posts

Predefined roles introduced in PostgreSQL 14 reduced my responsibilities

Image
  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 af

An Overview of PostgreSQL Backend Architecture

Image
  1. Introduction PostgreSQL backend is a collection of processes forked from the main process called  Postmaster . Each forked process has different roles and responsibilities in the backend. This article describes the responsibility of core backend processes that power the PostgreSQL system as we know it today. The overall PostgreSQL backend architecture can be illustrated by the image below: Postmaster is the first process to be started who has control of all the backend processes and is responsible for accepting and closing the database connections. At start up, the postmaster forks several backend processes that are intended to process different aspects of backend tasks, which we will be covering in this blog. When a user initiates a connection to the PostgreSQL database, the client process will send an authentication message to the Postmaster main process. The Postmaster main process authenticates the user according to the authentication methods configured and will fork a new ses

Pre-Checks for Oracle 19c to PostgreSQL Migration

  Migrating from one database management system to another is a complex task that requires careful planning and execution. If you are planning to migrate your data from Oracle 19c to PostgreSQL, there are several pre-checks that you should perform to ensure a successful migration. In this post, we will discuss some of the pre-checks that you should consider before migrating from Oracle 19c to PostgreSQL. Data Type Compatibility The first pre-check that you should perform is to ensure that the data types used in Oracle 19c are compatible with PostgreSQL. PostgreSQL supports most of the data types used in Oracle 19c, but there are some differences in the way they are implemented. For example, Oracle 19c supports the BLOB data type, whereas PostgreSQL supports the BYTEA data type. You should review your database schema and make sure that all the data types used in Oracle 19c are supported in PostgreSQL. Indexes and Constraints PostgreSQL supports both unique and non-unique indexes, wherea

Postgresql - Automatic database startup

Image
  Colleagues hello to all. In today's article, I will tell you how to set up automatic startup of the Postgresql database. As you remember, in the last article, I showed you how to install the Postgresql database from the source files. But if you restart the server or the server crashes, your database will not start after the server starts. Download and install Postgresql 14 on Linux CentOS/RHEL 8/7 TAR The first thing we will start with is to create a script that will be executed after the server is restarted. $. vim /home/postgres/pgsql_autostart.sh   After creating the  pgsql_autostart.sh file, add the following contents to it: #!/bin/bash #Automatic database start after server reboot export PGHOME=/app/postgresql export LD_LIBRARY_PATH=/app/postgresql/lib export PGDATA=/app/postgresql/pgdatabase/data export PATH=$PGHOME/bin:$PGDATA:$PATH pg_ctl start   Next, we assign the necessary rights to this file. $. chmod +x pgsql_autostart.sh   Now add this file to crontab. $. crontab -e

All about pg_hba.conf(authentication methods- Postgresql)

Image
  pg_hba.conf is the PostgreSQL access policy configuration file, which is located in the /var/lib/pgsql/10/data/ directory (PostgreSQL10) by default. The configuration file has 5 parameters, namely: TYPE (host type), DATABASE (database name), USER (user name), ADDRESS (IP address and mask), METHOD (encryption method) host all all 192.168.109.103/22 md5 host dbName user 192.168.109.106/22 trust Modify the server-side pg_hba.conf file Make the shell can connect to the postgres database secretly: Modify the authentication file $PGDATA/pg_hba.conf, add the following lines, and reload to make the configuration take effect immediately. host pankajconnect postgresql 192.168.8.103/32 trust Reload to take effect: pg_ctl reload -D $PGDATA Examples: 1. Allow local login to the database using PGAdmin3, database address  localhost, user user1, database user1db: host user1db user1 127.0.0.1/32 md5 2. Allow 10.1.1.0~10.1.1.255 network segments to log in to the database: host all all 10.1.1.0/24 md5