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 […]

14,564 total views, 12 views today

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 […]

12,031 total views, 16 views today

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 […]

22,946 total views, 22 views today

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) […]

4,337 total views, 6 views today

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 2,350 total views, 6 views today

2,350 total views, 6 views today

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 36,834 total views, 10 views today

36,834 total views, 10 views today