OnlineSkillBuilder for Postgresql DBA

  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 ...

  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, wh...

  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 ...

  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....