This article explains how to move a database to a new tablespace in postgres. This is same as changing the default tablespace for a postgres database.   For this activity we have make sure that no application sessions are connecting to that database. We can say this activity will need some downtime. Objective – We will […]

PROBLEM: We have rebooted both the primary and standby postgres nodes. After the reboot , the enterprise failover manager(EFM) , was not getting started on the standby node. PRIMARY NODE – 10.20.30.40 STANDBY NODE – 10.20.30.41 [root@STANDBY efm-3.9]# systemctl start edb-efm-3.9 Job for edb-efm-3.9.service failed because the control process exited with error code. See “systemctl […]

Creating tablespace in a standalone postgres server is a very straight forward. But what if we need to create a tablespace  in postgres with streaming replication enabled. Will the tablespace be created automatically in the standby? Let’s see the below demo to find out. 1. Check streaming replication detail[ RUN ON PRIMARY ] Expanded display […]

PROBLEM: I have received below error , while starting efm agent on standby server. [root@dbhost41 edbdata]# systemctl start edb-efm-3.9 Job for edb-efm-3.9.service failed because the control process exited with error code. See “systemctl status edb-efm-3.9.service” and “journalctl -xe” for details. cat /var/log/efm-3.9/startup-efm.log 2021-06-15 14:58:06 Trigger file validation failed. Could not start agent as standby. See […]

PG_STAT_STATEMENTS module is useful in monitoring and tracking sql queries in postgres. By default this module , this option is disabled. We need to enable this feature explicitly. In this article, we will explain how to enable pg_stat_statements module and how to use it. 1. Check whether pg_stat_statement module is present or not: postgres=# \d […]

Below article explains how to change postgres data directory to a new location existing data_directory – > /var/lib/edb/as11/data New data_directory -> /oradata/pgdata postgres os user – > enterprisedb 1. Check existing data directory postgres=# show data_directory; data_directory ———————— /var/lib/edb/as11/data (1 row) 2. Create new directory structure -bash-4.2$ mkdir -p /oradata/pgdata -bash-4.2$ chmod 0700 /oradata/pgdata   […]

                       EDB failover manager, popularly known as EFM is a component of EDB postgres platform, is used for handling switchover and failover (including auto failover) in postgres cluster having streaming replication. EFM cluster consists of below 1. One active primary server ( also master node), […]

In this article, we will explain how to configure streaming replication in postgres (11)from existing primary database to a new standby database( slave). If your postgres version is 12 or higher , then follow below link: How to configure streaming replication in postgres 14 ENVIRONMENT DETAILS: PRIMARY SERVER= 10.20.30.40 STANDBY SERVER =10.20.30.41 REPLICATION MODE- ASYNC […]

This article explains how to change the port number in postgres. We will change the port from 5444 to 5432 . 1. Check the existing port details postgres=# select * from pg_settings where name=’port’; -[ RECORD 1 ]—+—————————————————– name | port setting | 5444 unit | category | Connections and Authentication / Connection Settings short_desc […]

PROBLEM: While connecting to a remote database with psql got below error. -bash-4.2$hostname -i 192.168.2.3 -bash-4.2$ psql –host 192.268.8.0 -p 5444 -d postgres psql: error: could not connect to server: FATAL: no pg_hba.conf entry for host “192.168.2.3”, user “enterprisedb”, database “postgres”, SSL off SOLUTION: In the above scenario, i was trying to connect to a […]