Posts

PostgreSQL Logical Replication

What is Logical Replication ? Before introducing Logical replication, PostgreSQL replication works by shipping WAL files to standby server which applies the changes. In traditional replication mechanism standby is copy of all data or database (cluster). PostgreSQL 10 published with support of Logical Replication. One of the uses of logical replication is to allow low- or no-downtime upgrading between PostgreSQL major versions. (Logical replication can also be used for moving data between instances on different operating systems or CPU architectures or with different low-level configuration settings such as block size or locale side grading if you will.) The advantage of using logical replication to upgrade is that the application can continue to run against the old instance while the data synchronisation happens. There only needs to be a small outage while the client connections are switched over.  Logical replication uses PUBLICATION on Master & SUBSCRIPTION on Stand...
2 comments
Read more

PgBouncer Authentication

Image
What Is pgbouncer ? pgbouncer is a lightweight PostgreSQL connection pooler. Any target application can be connected to pgbouncer as if it were a PostgreSQL server, and pgbouncer will create a connection to the actual server, or it will reuse one of its existing connections. The aim of pgbouncer is to lower the performance impact of opening new connections to PostgreSQL. To know more about pgbouncer visit  https://pgbouncer.github.io/usage.html How pgbouncer work? As shown in below diagram , this is default authentication method most of us are using.When user try to connect database with specified user & password , PgBouncer will perform a lookup of the provided username/password against userlist.txt to verify that the credentials are correct, and then the username/password are sent to Postgres for an actual database login. What's wrong with using userlist.txt ? A general process is  You create user/role in database. Get md5 password from p...
2 comments
Read more

How To Install PostgreSQL 11 On Ubuntu 18.04

What is new in PostgreSQL-11 ? The PostgreSQL Global Development Group has released an update to all supported versions of our database system, including 11.2, 10.7, 9.6.12, 9.5.16, and 9.4.21. This release changes the behaviour in how PostgreSQL interfaces with  fsync()  and includes fixes for partitioning and over 70 other bugs that were reported over the past three months. To see what changed in detail you can visit  PostgreSQL-11.2 Lets move to installation of PostgreSQL-11 Step 1: Add PostgreSQL Repository to Ubuntu Very first thing you need to do is add repository key , this key is to authenticate and validate packages from repository  wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main" > /etc/apt/sources.list.d/PostgreSQL.list' Step 2: Update and Install PostgreSQL Now key are added, run the...
Post a Comment
Read more

PostgreSQL Job scheduler “pg_cron”

What is pg_cron ? pg_cron is a simple, cron-based job scheduler for PostgreSQL that runs inside the database as an extension. A background worker initiates commands according to their schedule by connecting to the local database as the user that scheduled the job. pg_cron supports PostgreSQL (9.5 or higher version). Why We need it ? Running periodic maintenance jobs or removing old data is a common requirement in PostgreSQL. A simple way to achieve this is to configure cron or another external daemon to periodically connect to the database and run a command. Installation of pg_cron Assuming PostgreSQL server is already installed On Ubuntu sudo apt-get -y install postgresql-10-cron OR you can install it from source code as well - Step 1 : Download source code from git pg_cron export PATH=/usr/local/pgsql/bin:$PATH wget https://github.com/citusdata/pg_cron/archive/master.zip unzip master cd pg_cron-master/ make make install Ste...
4 comments
Read more