May 16 2018

How to Using Dblink With DML Operations in PostgreSQL 9

Category: Administration,Errors and Solutions,SQLFatih Acar @ 11:45

Dblinks provide to connect another databases. Also you can do DML operations with using dblink. I will use dblink to connect two PostgreSQL databases on this post. Also I will tell how to use select, update and delete with dblink.

Firstly, you have to create dblink entension with related user which is using dblink.

Create Dblink Extension

postgres@testdb ~]# psql

psql> create extension dblink

Dblink Connection Test

psql> select dblink_connect('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword');

If result is OK, your connection test is successful.

Select Operation With Dblink

psql> select * from dblink('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','select id,name from remotetable') 
as t(id numeric(10,0),name character varying(100));

Update Operation With Dblink

psql> select dblink_exec('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','update remotetable set name=''Fatih'' where id = 1');

You can use dynamic variable to where condition.

psql> select dblink_exec('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','update remotetable set name=''Fatih'' where id = ' || ID || '');

If you get an error like "ERROR: query has no destination for result data, if you want to discard the results of a select, use perform instead.", you can use perform statement instead of select.

psql> perform dblink_exec('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','update remotetable set name=''Fatih'' where id = 1');

Delete Operation With Dblink

psql> select dblink_exec('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','delete from remotetable where id = 1');

You can use dynamic variable to where condition.

psql> select dblink_exec('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','delete from remotetable where id = ' || ID || '');

If you get an error like "ERROR: query has no destination for result data, if you want to discard the results of a select, use perform instead.", you can use perform statement instead of select.

psql> perform dblink_exec('dbname=remotedbname host=remotedatabaseipaddress user=remoteusertoconnectdatabase password=remoteuserpassword','delete from remotetable where id = 1');

2,198 total views, 12 views today

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”

16,628 total views, 22 views today

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

Jun 16 2017

PostgreSQL 10 New Features

Category: AdministrationFatih Acar @ 15:51

PostgreSQL 10 has very good new features. In my opinion, mainly new features are Logical Replication, Improved Parallel Query, Scram Authentication, Declarative Partitioning.

Logical Replication :  PostgreSQL has had physical replication that often called streaming replication since version 9.0, but this requires replicating the entire database, cannot tolerate writes in any form on the standby server, and is useless for replicating across versions or database systems.  PostgreSQL has had logical decoding that basically change capture since version 9.4, which has been embraced with enthusiasm, but it could not be used for replication without an add-on of some sort.  PostgreSQL 10 adds logical replication which is very easy to configure and which works at table granularity, clearly a huge step forward.  It will copy the initial data for you and then keep it up to date after that.

Improved Parallel Query : While PostgreSQL 9.6 offers parallel query, this feature has been significantly improved in PostgreSQL 10, with new features like Parallel Bitmap Heap Scan, Parallel Index Scan, and others.  Speedups of 2-4 x are common with parallel query, and these enhancements should allow those speedups to happen for a wider variety of queries.

SCRAM Authentication :  PostgreSQL offers a remarkable variety of different authentication methods, including methods such as Kerberos, SSPI, and SSL certificate authentication, which are intended to be highly secure.  However, sometimes users just want to use passwords managed by the PostgreSQL server itself.  In existing releases, this can be done either using the passwordauthentication type, which just sends the user-supplied password over the wire, or via the md5authentication type, which sends a hashed and salted version of the password over the wire.  In the latter approach, stealing the hashed password from the database or sniffing it on the wire is equivalent to stealing the password itself, even if you can’t compute a preimage.  PostgreSQL 10 introduces scram authentication, specifically SCRAM-SHA-256, which is much more secure.  Neither the information which the server stores on disk nor the contents of an authentication exchange suffice for the server to impersonate the client.  Of course, the substitution of SHA-256 for MD5 is also a substantial improvement. One point to note is that, unless you are using libpq, you will not be able to use this feature unless your particular client driver has been updated with SCRAM support, so it may be a while before this feature is universally available.

Declarative Partitioning : In previous versions of PostgreSQL, PostgreSQL supported only table inheritance, which could be used to simulate table partitioning, but it was complicated to set up and the performance characteristics were not that great.  In PostgreSQL 10, it’s possible to do list or range partitioning using dedicated syntax, and INSERT performance has been greatly improved.

Also, you can look other new features from here.


2,756 total views, 2 views today

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,823 total views, 10 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

26,296 total views, 36 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,655 total views, 6 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,786 total views, no 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

40,604 total views, 25 views today

Tags: PostgreSQL, PostgreSQL Administration

Jun 05 2012

RAID (Redundant Array of Independent Disks)

RAID (redundant array of independent disks; originally redundant array of inexpensive disks) is a way of storing the same data in different places (thus, redundantly) on multiple hard disks. By placing data on multiple disks, I/O operations can overlap in a balanced way, improving performance. Since multiple disks increases the mean time between failures, storing data redundantly also increases fault tolerance.

A RAID appears to the operating system to be a single logical hard disk. RAID employs the technique of disk striping, which involves partitioning each drive’s storage space into units ranging from a sector (512 bytes) up to several megabytes. The stripes of all the disks are interleaved and addressed in order.

In a single-user system where large records, such as medical or other scientific images, are stored, the stripes are typically set up to be small (perhaps 512 bytes) so that a single record spans all disks and can be accessed quickly by reading all disks at the same time.

In a multi-user system, better performance requires establishing a stripe wide enough to hold the typical or maximum size record. This allows overlapped disk I/O across drives.

There are types of RAID

RAID-0: The idea of RAID 0 is to increase performance. When storing information using the striping feature, the data will be split block by block between the two hard disks. Block one will be send to disk one, block two will be sent to disk two, block three will be sent to disk one and block 4 will be sent to disk 2 and so on. This is much faster than a single disk because when reading the data off the disks the twp of them will be working at the same time to retrieve the same file virtually doubling the speed or retrieval and so virtually halving the time of retrieval. As I mentioned this is a performance setup. Should any one of the disks fail the whole array will become corrupt. Most of the files will be split between disks and so will be rendered useless. If you don’t have important data on your computer or you have regular backups of what you do need, then a RAID 0 setup would greatly increase your computers disk performance. To get the best out of this system it is wise to use two disks which are the same make and model. If this is not possible then two of the same size and RPM would be useful but not essential. If two disks of different sizes are used in this system then the logical drive will show as the smallest disk. See drive capacities under RAID at the end of this article.
RAID-1: RAID 1 or mirroring gives added security for your data at the cost of storage space. As with striping this setup uses two hard disk drives to produce a single logical drive. In this instance however the total storage space is only the size of one of the disks (the smallest one). This is because with RAID 1 any data that is written to or read from the hard disk is done on the second hard disk exactly the same. If you save a file to your machine, it will will saved on both disks at the same time. This will however affect system performance with two disks needing to be written to and with the data being the same, its no better in terms of performance unlike the stripping method. However there is always advantages. Mirroring, having the same data on both disks has obvious plus points when it comes to data integrity and security against disk failure. If either disk one or two should fail the other disk will take over as the solitary disk providing and storing data like it did before the failure. Again see the drive capacity section at the end of this article to learn about data redundancy and why the logical drive sizes are what they are with each of the three RAID setups.
RAID-10:RAID 0+1 or Striping + Mirroring as you would imagine is a combination of the above two setups. This setup takes the advantages of both the stripping setup and the mirroring setup. You get the increased performance of splitting the data across multiple drives, however each of these striped drives will have a mirror as well for the data backup and security against failure. The obvious drawback here is the cost involved. The minimum amount of hard disk drives used in this configuration is 4. This puts most home users out of the equation as not only do you need to buy 4 hard disks but the PSU has to cope as well.
RAID-2: This type uses striping across disks with some disks storing error checking and correcting (ECC) information. It has no advantage over RAID-3.
RAID-3: This type uses striping and dedicates one drive to storing parity information. The embedded error checking (ECC) information is used to detect errors. Data recovery is accomplished by calculating the exclusive OR (XOR) of the information recorded on the other drives. Since an I/O operation addresses all drives at the same time, RAID-3 cannot overlap I/O. For this reason, RAID-3 is best for single-user systems with long record applications.
RAID-4: This type uses large stripes, which means you can read records from any single drive. This allows you to take advantage of overlapped I/O for read operations. Since all write operations have to update the parity drive, no I/O overlapping is possible. RAID-4 offers no advantage over RAID-5.
RAID-5: This type includes a rotating parity array, thus addressing the write limitation in RAID-4. Thus, all read and write operations can be overlapped. RAID-5 stores parity information but not redundant data (but parity information can be used to reconstruct data). RAID-5 requires at least three and usually five disks for the array. It’s best for multi-user systems in which performance is not critical or which do few write operations.
RAID-6: This type is similar to RAID-5 but includes a second parity scheme that is distributed across different drives and thus offers extremely high fault- and drive-failure tolerance.
RAID-7: This type includes a real-time embedded operating system as a controller, caching via a high-speed bus, and other characteristics of a stand-alone computer. One vendor offers this system.
RAID-50 (or RAID-5+0): This type consists of a series of RAID-5 groups and striped in RAID-0 fashion to improve RAID-5 performance without reducing data protection.
RAID-53 (or RAID-5+3): This type uses striping (in RAID-0 style) for RAID-3’s virtual disk blocks. This offers higher performance than RAID-3 but at much higher cost.
Continue reading “RAID (Redundant Array of Independent Disks)”

227,998 total views, 4 views today

Tags: System Administration

Sep 21 2011

pg_dumpall with shell script and crontab in Linux

Category: Administration,Linux & UnixFatih Acar @ 13:18

You can take schedule backup with shell script. Firstly, You must create shell script file in operating system. After you can append to crontab.

Crontab is a UNIX command that creates a table or list of commands, each of which is to be executed by the operating system at a specified time. crontab is used to create the crontab file (the list) and later used to change the previously created crontab file.


crontab -e Edit your crontab file, or create one if it doesn’t already exist.
crontab -l List your crontab file.
crontab -r Remove your crontab file.
crontab -v Display the last time you edited your crontab file.

Minute Hour Day of Month Month Day of Week Command
(0-59) (0-23) (1-31) (1-12 or Jan-Dec) (0-6 or Sun-Sat)
0 2 12 * 0,6 /usr/bin/

We will take backup wirh pg_dumpall. Firstly, You must create shell script.


$>cd /etc/scripts/
Note : We created with vi statement. You have to write below script.

export today=`date +%Y%m%d`

export deleteday=`/bin/date –date=”15 days ago” +%Y%m%d`

##delete old backup script
rm -f /tmp/pgalldump_$deleteday.dump.out

##create backup script
pg_dumpall > /tmp/pgalldump_$today.dump.out

Note : After write, You close editor with ” escape > :wq! > enter “. You saved.

We wrote shell script. Now, We have to append to crontab.


$>crontab -e
10 1 * * * /etc/scripts/

Note : After write, You close editor with ” escape > :wq! > enter “. You saved. This time statement mean that shell script will execute every days of week,every month, every day, at 1 hour past 10 minute.

24,742 total views, 25 views today

Tags: Database Administration, Linux, PostgreSQL

Next Page »