Sep 24 2017

PostgreSQL Hot Standby (Streaming Replication) Database Installation and Failover Operations

Category: AdministrationFatih Acar @ 09:33

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 standby as they’re generated, without waiting for the WAL file to be filled.

Streaming replication is asynchronous by default, in which case there is a small delay between committing a transaction in the primary and the changes becoming visible in the standby.

If you use streaming replication without file-based continuous archiving, the server might recycle old WAL segments before the standby has received them. If this occurs, the standby will need to be reinitialized from a new base backup. You can avoid this by setting wal_keep_segments to a value large enough to ensure that WAL segments are not recycled too early, or by configuring a replication slot for the standby. If you set up a WAL archive that’s accessible from the standby, these solutions are not required, since the standby can always use the archive to catch up provided it retains enough segments.

Test Systems Features

PG Version : 9.5
Primary DB IP :
Pimary DB Hostname : pg01
Hot Standby DB IP :
Host Standby DB Hostname : pg02
Virtual Service IP :
OS : Centos 7 x64
Data directory : /pgdata/data
Archive direcytory : /pgdata/ARCHIVELOG


1- Keygen Generate And Copy

[postgres@pg01 ~] ssh-keygen -t rsa
[postgres@pg02 ~] ssh-keygen -t rsa
[postgres@pg01 ~] ssh-copy-id -i .ssh/ postgres@pg02
[postgres@pg02 ~] ssh-copy-id -i .ssh/ postgres@pg01

2- Create Streaming Replication User on Primary DB

psql# createuser -U postgres -p 5432 repuser -P -c 10 –replication

3- Primary DB pg_hba.conf configuration

[root@pg01 ~] vi /pgdata/data/pg_hba.conf

Add below line

host replication repuser md5

[root@pg01 ~] systemctl reload postgresql.service

4- Create Archive Directory on Hot Standby Server
Continue reading “PostgreSQL Hot Standby (Streaming Replication) Database Installation and Failover Operations”

15,676 total views, 18 views today

Tags: Database Administration, PostgreSQL Administration, PostgreSQL High Availability, PostgreSQL Hot Standby

Nov 11 2016

PostgreSQL Upgrade From 9.5 Version To 9.6 Version With PG_UPGRADE

Category: AdministrationFatih Acar @ 11:03

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 releases regularly add new features that often change the layout of the system tables, but the internal data storage format rarely changes. pg_upgrade uses this fact to perform rapid upgrades by creating new system tables and simply reusing the old user data files. If a future major release ever changes the data storage format in a way that makes the old data format unreadable, pg_upgrade will not be usable for such upgrades. (The community will attempt to avoid such situations.)

pg_upgrade does its best to make sure the old and new clusters are binary-compatible, e.g. by checking for compatible compile-time settings, including 32/64-bit binaries. It is important that any external modules are also binary compatible, though this cannot be checked by pg_upgrade.

pg_upgrade supports upgrades from 8.4.X and later to the current major release of PostgreSQL, including snapshot and alpha releases.

Source :


Steps Of Upgrade

  • Install PostgreSQL new release packages
  • Create new directory for 9.6
  • Initialize 9.6 version in 9.6 directory
  • Configure pg_hba.conf and postgresql.conf regarding to changed parameters of 9.5 version
  • Stop 9.5 PostgreSQL service
  • Start pg_upgrade
  • Change PostgreSQL service name and start PostgreSQL 9.6

System Information

OS : Centos 7 x64
PG : 9.5
DIR : /pgdata/9.5/data/
SERVICE : postgresql.service

Install PostgreSQL new release packages

[root@testdb /tmp]# yum install postgresql96-libs.x86_64 postgresql96.x86_64 postgresql96-server.x86_64 postgresql96-contrib.x86_64 postgresql96-devel.x86_64 postgresql96-python.x86_64 postgresql96-plpython.x86_64

Create new directory for 9.6
Continue reading “PostgreSQL Upgrade From 9.5 Version To 9.6 Version With PG_UPGRADE”

12,489 total views, 2 views today

Tags: PostgreSQL, PostgreSQL Administration

Aug 23 2016

Create Dblink From Oracle to PostgreSQL Database

Category: Administration,AdministrationFatih Acar @ 14:36

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 :
OS : Centos 7 x64
Service : postgresql.service

Oracle DB
IP :
OS : OEL 6 x64


PostgreSQL Side

psql# create user dblinkuser encrypted password ‘dblinkuserpass’;

psql# grant usage on schema “testschema” to dblinkuser;

psql# grant select on “testschema”.tbl_test to dblinkuser;

Add new line to pg_hba.conf

vi /pgdir/data/pg_hba.conf

host testdb dblinkuser md5

systemctl reload postgresql.service

Oracle Side

yum install postgresql-odbc

vi /etc/odbc.ini

Description = PG
Driver = /usr/lib64/
ServerName =
Username = dblinkuser
Password = dblinkuserpass
Port = 5432
Database = testdb
Driver = /usr/lib64/

vi $ORACLE_HOME/hs/admin/initPG.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

# HS init parameters
# ODBC specific environment variables
set ODBCINI=/etc/odbc.ini

# ODBC specific environment variables

# Environment variables required for the non-Oracle system
#set =

vi $ORACLE_HOME/network/admin/tnsnames.ora

PG =
(SID = PG)
(HS = OK)

vi $GRID_HOME/network/admin/listener.ora


lsnrctl reload

Listener reload can be adacuate to apply changes;but, sometimes not.

lsnrctl stop
lsnrctl start
lsnrctl status

Create Database Link on Oracle

CONNECT TO “dblinkuser”
IDENTIFIED BY ‘dblinkuserpass’


select * from “testschema”.”tbl_test”@PG

24,758 total views, 32 views today

Tags: Database Administration, Oracle Administration, PostgreSQL Administration

Jun 15 2016

PostgreSQL Frequently Using Commands

Category: Administration,SQLFatih Acar @ 16:03

List, Add, Drop Database

List databases

postgres=# \d

Add database

postgres=# createdb testdb


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’;


postgres=# createuser (interactive user create)

Change User Password

postgres=# \password facar
postgres=# alter user facar with encrypted password ‘newpassword’;

Create Schema

postgres=# create schema schfacar;
postgres=# grant usage on schema schfacar to facar;
postgres=# grant all on schema schfacar to facar;

Add Search Path

postgres=# alter user facar set search_path=schfacar;

Grant Permissions to User

postgres=# alter user facar superuser;
postgres=# alter user facar createdb;
postgres=# alter user facar replication;
postgres=# alter user facar bypassrls;
postgres=# grant all privileges on database testdb to facar;

Revoke Permissions from User

postgres=# alter user facar nosuperuser;
postgres=# alter user facar nocreatedb;
postgres=# alter user facar noreplication;
postgres=# alter user facar nobypassrls;
postgres=# revoke all privileges on database testdb from facar;

Connect Database

[root@testdb ~] su – postgres
-bash-4.2$ psql
-bash-4.2$ psql testdb (connect testdb)
-bash-4.2$ psql -U facar testdb (connect testdb with facar user)
-bash-4.2$ psql -U facar testdb -h “” -p 5432 (connect testdb with facar user on database that is IP and 5432 port)

[root@testdb ~]  psql
[root@testdb ~]  psql testdb (connect testdb)
[root@testdb ~]  psql -U facar testdb (connect testdb with facar user)
[root@testdb ~]  psql -U facar testdb -h “” -p 5432 (connect testdb with facar user on database that is IP and 5432 port)

Note : If you want to connect database with different user from postgres, you have to add line pg_hba.conf like that

host         all            all               md5

Information Queries

postgres=# \conninfo (current connection information)
postgres=# \dt (tables of current user schema)
postgres=# \d (list of object of current user schema)
postgres=# \d tablename (columns of tablename)
postgres=# \df (list of functions)
postgres=# \df+ (list of functions with details)
postgres=# select version(); (detailed version of database)
postgres=# \? (psql commands list for help)
postgres=# \h (list of help options)
postgres=# \h CREATE USER (help of CREATE USER command)
postgres=# \timing (open timing to show query execution time)
postgres=# \c testdb (connect testdb)
postgres=# \c testdb facar (connect testdb with facar user)
postgres=# select pg_database_size(current_database()); (size of current database as byte)
postgres=# select pg_database_size(‘testdb’); (size of testdb database as byte)
postgres=# select pg_relation_size(‘tablename’); (size of table as byte)
postgres=# select usename, datname from pg_stat_activity; (who are connected to which database now)
postgres=# select pg_reload_conf(); (to reload configurations like service postgresql reload)

4,509 total views, 2 views today

Tags: Database Administration, PostgreSQL, PostgreSQL Administration

Jun 14 2016

ERROR: database “database_name” is being accessed by other users in PostgreSQL

Category: Administration,Errors and SolutionsFatih Acar @ 08:57


When you execute drop database command, database get an error like ERROR: database “database_name” is being accessed by other users.>



Continue reading “ERROR: database “database_name” is being accessed by other users in PostgreSQL”

2,590 total views, 4 views today

Tags: PostgreSQL, PostgreSQL Administration

Apr 10 2015

How to Delete psql Command Prompt History in PostgreSQL

Category: AdministrationFatih Acar @ 09:56

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
[root@pg ~]# vi /var/lib/pgsql/.psql_history

38,799 total views, 30 views today

Tags: PostgreSQL, PostgreSQL Administration