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');

970 total views, 6 views today


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

or

postgres=# create database testdb with owner facar;


Drop database

postgres=# dropdb testdb

Postgres_logo

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)

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 “192.168.20.12” -p 5432 (connect testdb with facar user on database that is 192.168.20.12 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 “192.168.20.12” -p 5432 (connect testdb with facar user on database that is 192.168.20.12 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               192.168.20.12/32          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,113 total views, 2 views today

Tags: Database Administration, PostgreSQL, PostgreSQL Administration