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…
Tag: PostgreSQL Administration
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…
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 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)…
ERROR: database “database_name” is being accessed by other users in PostgreSQL
Error When you execute drop database command, database get an error like ERROR: database “database_name” is being accessed by other users.> Solution
How to Delete psql Command Prompt History in PostgreSQL
When you use psql command prompt, system automaticly saves your prompts. If you use password when user creates operation, this situation can occur security risk. Therefore, you can delete this prompts with editing psql_history file like below. Find And Delete psql Prompts [root@pg ~]# locate .psql_history /var/lib/pgsql/.psql_history [root@pg ~]# vi /var/lib/pgsql/.psql_history