SRI SRI's Postgresql Knowledge

  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

    Backup and Recovery is one of the most essential jobs for every database administrator. We all know the importance of the backups for the databases. Here I am not going to write its importance, rather about the different types of backup tools available for the PostgreSQL server. In PostgreSQL, we can perform both logical and physical backups as same as MySQL Logical Backups (Text Backup) Physical Backups (Copying data files + Redo Log files)                     In this blog, we are going to compare the tools in PostgreSQL which performs logical backups. In our previous post, we had made a similar kind of comparison of logical backup tools available for the MySQL database( Mysqldump vs Mysqlpump vs Mydumper ). Logical Backups:-              Logical Backups are simple and the textual representation of the data in the databases. It contains insert queries and create statements to reproduce the data in the destination. In PostgreSQL, we are having two logical backup tools  Pg_dump Pg_d

  In this post you will learn about how to restore a PostgreSQL database in Point in time. Open source databases are taking over other conventional Database softwares at very high speed. For me personally, I really like the way PostgreSQL is designed and it’s simple architecture. I have tried to write it down every possible steps which will help you to restore your Postgres database. Below Steps will be same, but few commands may vary with any other backup tool. PITR PostgreSQL Allow you to restore database to a specific moment in time PITR. It make use of live database files and WAL files. This method can only backup and restore the whole cluster, for individual database use pg_dump. It can be done when the DB is online. Step 1. If archive logs not enable then enable it, otherwise go to Step3. Create Directory for archive logs mkdir /postgres/postgres12.2/wal_archives Make changes in postgresql.conf Postgresql.conf is present under your data directory, in my case it is in /postgres/po