With PostgreSQL 15 version, logical replication became applicable by filtering on the tables by column and row. In this way, replication can be made in the desired columns and rows in the tables. Logical Replication wal_level=logical For logical replication, the wal_level parameter must be logical in the postgresql.conf file . By default, the value of wal_level is replica . The replica value provides the necessary wal files for streaming…
Category: PostgreSQL
PostgreSQL 15 New Features
PostgreSQL version 15 was released and made available on October 13, 2022. PostgreSQL, the most advanced open source database, brought many new features with its 15th version. PostgreSQL 15 New Features Merge Command With the release of PostgreSQL 15, the Merge command was also made available. MERGE allows you to write a conditional SQL query that…
How to Use pg_rewind in PostgreSQL 12
pg_rewind is a PostgreSQL tool since 9.5 version. It provides synchronization between standby and old primary. Especially, pg_rewind provides advantage for big databases. You can sync databases without using pg_basebackup. pg_rewind scans the old primary database’s pgdata folder and identifies data blocks changed during the switch to standby database, then copies only changed blocks from…
PostgreSQL 12 Streaming Replication Installation and Failover Operations on CentOS 7
PostgreSQL has few feature for standby technology. I generally use streaming replication. This is very easy and confident. Streaming replication use a replication user for replication operations.Streaming replication allows a standby server to stay more up-to-date than is possible with file-based log shipping. The standby connects to the primary, which streams WAL records to the…
How to Using Dblink With DML Operations in PostgreSQL 9
Dblinks provide to connect another databases. Also you can do DML operations with using dblink. I will use dblink to connect two PostgreSQL databases on this post. Also I will tell how to use select, update and delete with dblink. Firstly, you have to create dblink entension with related user which is using dblink. Create…
PostgreSQL Hot Standby (Streaming Replication) Database Installation and Failover Operations
PostgreSQL has few feature for standby technology. I generally use streaming replication. This is very easy and confident. Streaming replication use a replication user for replication operations.Streaming replication allows a standby server to stay more up-to-date than is possible with file-based log shipping. The standby connects to the primary, which streams WAL records to the…
PostgreSQL 10 New Features
PostgreSQL 10 has very good new features. In my opinion, mainly new features are Logical Replication, Improved Parallel Query, Scram Authentication, Declarative Partitioning. Logical Replication : PostgreSQL has had physical replication that often called streaming replication since version 9.0, but this requires replicating the entire database, cannot tolerate writes in any form on the standby…
PostgreSQL Upgrade From 9.5 Version To 9.6 Version With PG_UPGRADE
pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/reload typically required for major version upgrades, e.g. from 8.4.7 to the current major release of PostgreSQL. It is not required for minor version upgrades, e.g. from 9.0.1 to 9.0.4. Major PostgreSQL…
Create Dblink From Oracle to PostgreSQL Database
Since fetching data from PostgreSQL database to Oracle database, you can create database link between two database systems. For that you can apply below operations. Test Systems PostgreSQL DB IP : 192.168.10.10 OS : Centos 7 x64 Service : postgresql.service Oracle DB IP : 192.168.10.20 OS : OEL 6 x64 PostgreSQL Side psql# create user…
PostgreSQL Frequently Using Commands
List, Add, Drop Database List databases postgres=# \d Add database postgres=# createdb testdb or postgres=# create database testdb with owner facar; Drop database postgres=# dropdb testdb List, Add, Drop, Grant User and Schema List Users postgres=# select * from pg_user; Add User postgres=# create user facar with encrypted password ‘password’; or postgres=# createuser (interactive user create)…