At windows:
Connect to server using psql:
psql
\dx; //lists extensions
Plpgsql : default extension
select * from pg_available_extensions; //list of extensions
citext:
create extension citext; //case insensitive
create table t1(name text);
insert into t1 values(‘diya’);
insert into t1 values(‘DIYA’);
select * form t1 where name=’diya’;
one row
create table t2(name citext);
insert into t1 values(‘diya’);
insert into t1 values(‘DIYA’);
select * form t1 where name=’diya’;
2 rows
Hstore:
Create extension hstore;
create table student(id int,marks hstore);
insert into student values(1,'"maths"=>85,"social"=>80,"science"=>80');
insert into student values(2,'"maths"=>75,"social"=>85,"science"=>90');
select * from student;
select marks from student;
select marks->'maths' as maths_marks from student;
pgcrypto:
create extension pgcrypto;
create table credential(name text,pwd text,pwd_enc text);
insert into credential values('bilvika','akivlib',pgp_sym_encrypt('akivlib', 'koenig'));
select * from credential;
select pwd,pgp_sym_decrypt('place encrypted value here','koenig') as decrypt from credential;
select
pwd,pgp_sym_decrypt('\xc30d0407030205216d185da88e226ad2380114480371db0b91f8df3fc859da
1efec6250fe0f208e408011b4d7da69def31dc9e9fbfe097dacb043f99aadcfb170f846163745b2c7ab9',
'koenig') as decrypt from credential;
select
pgp_sym_decrypt(‘\xc30d04070302d3c834966c6a80dd7bd238016cc1a45d51ccc9ae7bf2270be66764
1a0bc50434706fe27bd83f6244901c05748cab746dd66c250ef499889ccc5bec855b2bc8229ff4fe’,’koen
ig’);
At VM: linux(centos)
Connect to centos 7 and start the server
su – root
password: mysql
su - postgres
export PATH=$PATH:/usr/pgsql-14/bin
pg_ctl -D /var/lib/pgsql/14/data start
connect to the server:
psql -U postgres -d postgres
select * from pg_available_extensions; --extensions are not available by default, install the package
\dx
Select * from pg_extension;
Open another terminal terminal 2:
su – root
password : mysql
Extensions are not added as built in
Add extensions using:
yum install postgresql14-contrib.x86_64 -y
Come back to terminal 1:
select * from pg_available_extensions;
\dx
Select * from pg_extension;
Dblink:
create database demo;
\c demo;
create extension dblink;
SELECT dblink_connect('conn','dbname=postgres user=postgres password=postgres');
CREATE TABLE A_demo (id serial, detail text);
\c postgres
create table B_postgres (id serial, detail text);
INSERT INTO B_postgres(detail) VALUES ('abc'), ('def');
\c demo
SELECT * FROM dblink('conn', 'select * from B_postgres') AS t1(id integer, detail text);
Select * from A_demo;
INSERT INTO A_demo SELECT id, detail
FROM dblink('conn', 'SELECT id, detail FROM B_postgres') AS t(id integer, detail text);
Select * from A_demo;
flat file: file_fdw
CREATE EXTENSION file_fdw;
CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
\q
vi /var/lib/pgsql/[Link]
Add contents in [Link]
id,name
1,abc
2,xyz
3,pqr
psql
CREATE FOREIGN TABLE file_to_tbl( id integer, name varchar(40)) SERVER file_server
OPTIONS ( delimiter ',', filename '/var/lib/pgsql/[Link]', format 'csv', header 'true');
Note: Insert and delete can not be done on foreign table with file_fdw extension
\dE : to list foreign tables
Postgres_fdw (We use 2 VMs)
1. Connect as root user: su – root
password:mysql
a. Disable firewall and selinux at both the VMs
i. systemctl stop firewalld
ii. systemctl disable firewalld
b. cd /etc/selinux/
i. ls
ii. vi config
1. edit selinux=disabled
2. Edit [Link] file at the main VM(VM1):
a. Listen addressess=”*” and remove comment for this
3. Edit pg_hba.conf file: (VM1)
a. Add ipconfiguration ipv4
i. Host all all ipaddress-of-vm2/32 trust
4. Restart the server
pg_ctl -D /var/lib/pgsql/14/data restart
psql
At vm1:
create database org;
\c org;
create table e1(id int);
insert into e1 values(1);
insert into e1 values(2);
At vm2:
psql
Create database employee;
\c employee
CREATE EXTENSION postgres_fdw;
CREATE SERVER f_org FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'org',host
' ip_address of vm1',port '5432');
\des (to list server objects)
CREATE USER MAPPING FOR postgres SERVER f_org OPTIONS (user 'postgres', password
'postgres');
To import complete schema:
import foreign schema public from server f_org into public;
To import individual table:
create foreign table emp_fr(id int) server f_org options(schema_name 'public',table_name 'emp');
select * from e1;
Using dblink to connect databases on 2 servers:
IP address of VM2 has to be added to pg_hba.conf file of VM1:
At vm1:
\c org
Create table faculty;
Insert into faculty values(1);
Select * from faculty;
At VM2:
Create extension dblink;
select dblink_connect('conn','dbname=org port=5432 host=ip address of vm1 user=postgres
password=postgres');
select * from dblink('conn','select id from org1') as t1(id int);