Category: PostgreSQL

5 Dec by Amzad Ali Tags: ,

PostgreSQL Streaming Replication

  Master Server / Primary Server Set Up 1. Configuration Changes in file “postgresql.conf“ [crayon-5c3e650193247229242102/]   2. Configuration Changes in file “pg_hba.conf” Make an entry for replication user with Slave server IP: [crayon-5c3e65019324f677223493/]   3. Restart the PostgreSQL service in Primary for changes to get impact: [crayon-5c3e650193251949702706/]   Slave Server/ Secondary Server Set Up 1.Stop […]
2 Dec by Faruk Erdem

Foreign Data Wrapper Between PostgreSQL and Oracle

  In the article “Postgresql Foreign Data Wrapper“, we created Foreign Data Wrapper to read data between two PostgreSQLs. And we have created Foreing Data Wrapper Between PostgreSQL and MSSQL in the article named “PostgreSQL Foreign Data Wrapper Between PostgreSQL and MSSQL“. In this article, we will create Foreign Data Wrapper between PostgreSQL and Oracle. […]
27 Nov by Faruk Erdem

How To Install Oracle Client For PostgreSQL On Centos

  Oracle_client is required to connect to remote Oracle databases. For Oracle Foreign Data Wrapper, this installation needs to be done as follows. First of all, we download the rpm package from oracle’s site at the link below. https://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html We download the rpm package from the link below to our machine.   Run the following […]
25 Nov by Faruk Erdem

make: gcc : Command not found make:*** [oracle_fdw.o] Error 127

  Sometimes the standard installation is not sufficient for the extension package you want to install. The reason for this is that the extension has different dependencies. If you get the error as follows when you run the Make command without installing the dependencies, it is because there are no required packages. gcc -O2 -g […]
9 Nov by Faruk Erdem

Universal Unique İdentifier (UUID) Data Type In PostgreSQL

  Uuid is the abbreviation for “universal unique identifier”. It consists of 36 characters, a set of numbers and letters, and its most important feature is: Its extremely unique. To use this feature on PostgreSQL, we need to install uuid-ossp extension. We can install extension with the following command; [crayon-5c3e650193eea853630465/]   After running the command […]
8 Nov by Faruk Erdem

How to Change the Schema Name in PostgreSQL

We may want to change the schema name because of some needs. For this we can use the following script. First, we list the schemes with the command \dn as follows.   We change the name of the faruk scheme with the help of the following command. [crayon-5c3e6501940bf089206415/]   We changed the schema name to […]
7 Nov by Şahap Aşçı

PostgreSQL – List the connections from the IP block

One of the useful data types of PostgreSQL is the inet data type. inet is used to define IPv4 and IPv6 addresses. The ip address can also be stored with the subnet if desired. It supports a total of 17 operators, including basic operators “<,>, =” operators. In addition to this, with the help of […]
1 Nov by Faruk Erdem

How To Find and Kill Long Running Queries In PostgreSQL

  You can use the following queries to monitor the performance of the database or to find and kill queries that cause lock. The following query returns queries that last longer than 5 minutes. [crayon-5c3e65019446e452009055/]   You can find queries that have ExclusiveLock with the following query. [crayon-5c3e650194473988260554/]   We have found your query with […]
30 Oct by Engin Yilmaz

UPGRADE POSTGRESQL 10 To 11

  Before I start the upgrade process, I want to make the following critical warning: You will need to re-configure your postgresql.conf and pg_hba.conf files. Because the files will be reset after upgrade. We are installing Postgresql 11 with the following commands. [crayon-5c3e65019469b926295460/]   We are performing the initdb operation in the Postgresql 11 database. [crayon-5c3e6501946a0692807036/] […]