POSTGRES

How to replicate tables in postgres using EDB replication server

EDB replication server tool can be used to replicate tables between databases. Below topologies are supported by this tool. Between postgres to postgres Between oracle to postgres and vice versa Between sql sever and postgres and vice versa In this article, we will explain steps for replicating tables between postgres to postgres database. Publication server […]

How to open postgres standby database for read write(snapshot standby)

There is no technical term called snapshot standby in postgres. If you have knowledge of oracle database, then this  snapshot standby method is there in dataguard. I.e you can open the standby database in read-write mode temporarily and do all you testing like data change schema change etc. And once your testing is done, you […]

How to do switchover in postgres

In this article we will explain how to do switchover manually . Switchover means, change of role between master and slave. postgres doesn’t provide any inbuilt command to do switchover . We will use the pg_ctl promote command with some additional steps to do the switchover. Note – There are few third party tools like […]

How to do failover in postgres

When you master server is crashed or inaccessible, then we can do failover and promote slave server as master. ENVIRONMENT DETAILS: PRIMARY SERVER(MASTER)= 10.20.30.40 STANDBY SERVER(SLAVE) =10.20.30.41 REPLICATION MODE- ASYNC POSTGRES VERSION- 14 Let’s say master becomes inaccessible, then we need to promote the slave using below command. on slave server: -bash-4.2$ which pg_ctl /usr/pgsql-14/bin/pg_ctl […]

How to configure streaming replication in postgres 14

In this article, we will explain how to configure streaming replication in postgres(14) from existing primary database to a new standby database( slave). The below steps will be same for POSTGRES 12/13/14. If you are looking for streaming replication older than postgres 12, then below link: How to setup streaming replication in postgres 11   […]

How to install postgres in linux using rpm

In this we will explain how to install postgres 14.( Though installation steps are same for all versions). 1. Download the postgres server rpms. https://yum.postgresql.org/rpmchart/ 2.Install the rpms( in the below sequence). Make sure to follow the below order while installing the rpm, Otherwise dependency error will come. total 16128 -rwxr-xr-x 1 root root 698212 […]

Generate performance report in postgres using pg_profile

If you have worked on oracle, then you must of aware of AWR report, which is used to generate workload performance report of the database for a specific duration. But in postgres there is no inbuilt function to generate performance report. However there is an extension called pg_profile can be used to generate similar performance […]

How to upgrade postgres to new version

In this article, we will explain , How to upgrade from postgres 12 to postgres 14 version. This will be considered as major upgrade. Minor upgrade is like upgrading from 9.6 version to 9.7 version. For minor upgrade, You just need to upgrade the rpms. Which We will do that in another article. 1. First […]

How to access oracle database from postgres

This article explain From postgres, how can we  fetch oracle db data. For this we need to use foreign data wrapper and foreign table concepts. DEMO: 1.Create oracle client on postgres server:     2. Install oracle_fdw extension on postgres server: Download the package from github – https://github.com/laurenz/oracle_fdw   [root]# export ORACLE_HOME=/var/lib/edb/app/enterprisedb/product/19.0.0/client_1 [root]# export PATH=/usr/edb/as12/bin:$PATH […]

How to create database link in postgres

This article explain how can we access data from a remote postgres database using db_link. In postgres we call achieve this using foreign data wrappers. 1. Create extension dblink if not present. postgres=# create extension dblink; CREATE EXTENSION postgres=# postgres=# \dx List of installed extensions Name | Version | Schema | Description ——————–+———+————+—————————————————————- dblink | […]