0% found this document useful (0 votes)

989 views78 pages

Postgres Installation and Management Guide

The document contains tutorials about installing, configuring, and troubleshooting PostgreSQL. It includes articles on how to install PostgreSQL on Mac and Linux, create and drop databases, upgrade PostgreSQL versions, clone databases to remote servers, enable archive mode, move tables and databases, change the data directory and port number, access external data files and databases, setup streaming replication and monitoring, and resolve common errors.

Uploaded by

Mohd Yasin

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as DOCX, PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

989 views78 pages

Postgres Installation and Management Guide

Uploaded by

Mohd Yasin

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as DOCX, PDF, TXT or read online on Scribd

You are on page 1/ 78

POSTGRES ARTICLES

 How To Install Postgres Database On Mac Os/Linux

 How To Create A Database In Postgres
 How To Drop A Database In Postgres
 How To Upgrade Postgres To New Version(from 12 to 14)
 How To Clone A Postgres Database To Remote Server
 How To Enable And Disable Archive Mode In Postgres
 How To Move A Tablespace To New Directory In Postgres
 How To Create Tablespace In Postgres With Streaming Replication
 How To Move A Database To A New Tablespace In Postgres
 How To Change Postgres Data Directory
 How To Make A Postgres Database Readonly
 How To Change Port Number In Postgres
 How To Access Csv Files On File System Using File_fdw
 How To Create Database Link In Postgres
 How To Access Oracle Database From Postgres
 How To Setup Streaming Replication In Postgres
 EDB Failover Manager (EFM) For Managing Streaming Replication
 Monitor Sql Queries In Postgres Using Pg_stat_statements

TROUBLESHOOTING:
 ERROR: Permission Denied For Schema In Postgres
 Psql: Error: Could Not Connect To Server: FATAL: No Pg_hba.Conf Entry For Host
 Trigger File Validation Failed. Could Not Start Agent As Standby
 Edb Efm Start Failed With Authentication Failed Error On Standby

One More
 Automate Oracle Client Installation Using Ansible

===============================
How To Install Postgres Database On Mac Os/Linux
In this tutorial, i have explained steps for installing EDB postgres database on macos. The steps
are similar for linux system also. It will be a GUI method.

Download the software:
Link – > https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

Download the executable according to your platform.

2. Start the downloaded executable:

Now we have successfully installed the postgres.

3. Validate the installation:
Go to terminal :

Now login with postgres user and connect to server for validation:

Now lets dive into postgres 🙂

How To Create A Database In Postgres

Creating a database in postgres is very easy and it takes just few seconds. create database
command is used to create the database.In this tutorial we will show different option with create
database command.

NOTE – > When we install and initialise postgres cluster, by default two template database
will be created, one is template1( which is the standard system database) and template0
( which is the secondary standard system database).
whenever we create create database command by default it will use the template of
template1.template0 database should never be altered.

1. Creating a database with default option:( Simplest command):
postgres=# create database DBATEST;
CREATE DATABASE

2. Create database with specific tablespace:

postgres=# create database DBATEST with tablespace ts_postgres;
CREATE DATABASE

While mentioning tablespace name make sure the tablespace is already present in the postgres
cluster:

3. Create database with options like encoding, and template

postgres#CREATE DATABASE "DBATEST"
WITH TABLESPACE ts_postgres
OWNER "postgres"
ENCODING 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE template0;

— View database information from psql prompt:

postgres=# \l+
postgres=# \list+
postgres# select * from pg_database;

<< Note – alternatively database can be created using pgadmin GUI tool also >>>

How To Drop A Database In Postgres

There are multiple ways to drop a database in postgres .

DEMO:
In this article, we will drop the database named: DBACLASS.
postgres=# \list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
DBACLASS | postgres | UTF8 | C | C | --- >>>>>>> THIS ONE
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)

Method 1: ( using drop database command).

$psql -d postgres
password:

postgres=# drop database "DBACLASS";;

ERROR: database "DBACLASS" is being accessed by other users
DETAIL: There is 1 other session using the database.
Drop command failed, because the some sessions are already connected to the database. Lets
clear them.
postgres=# select application_name,client_hostname,pid,usename from
pg_stat_activity where datname='DBACLASS';

application_name | client_hostname | pid | usename

-------------------------+-----------------+-------+----------
pgAdmin 4 - DB:DBACLASS | | 12755 | postgres
(1 row)

postgres=# select pg_terminate_backend(pid) from pg_stat_activity where

pid='12755';
pg_terminate_backend
----------------------
t
(1 row)

postgres=# select application_name,client_hostname,pid,usename from

pg_stat_activity where datname='DBACLASS';
application_name | client_hostname | pid | usename
------------------+-----------------+-----+---------
(0 rows)

Now no sessions are present on the database, So we can proceed with drop command.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host
"localhost" at port "5432".

postgres=# \list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
DBACLASS | postgres | UTF8 | C | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)

postgres=# drop database "DBACLASS";

DROP DATABASE

Method 2: (using dropdb executable command)

Go to bin location of the postgres home_location at os level and run dropdb command.
postgres$ pwd
/Library/PostgreSQL/10/bin

postgres$ ./dropdb -e "DBACLASS"

Password:
SELECT pg_catalog.set_config('search_path', '', false)
DROP DATABASE "DBACLASS";

Method 3: (Using pgadmin tool)

In this method, we will drop the database using pgadmin gui tool.

How To Upgrade Postgres To New Version (from 12 to 14)

In this article, we will explain , How to upgrade from postgres 12 to postgres 14 version. This
will be considered as major upgrade.

Minor upgrade is like upgrading from 9.6 version to 9.7 version. For minor upgrade, You just
need to upgrade the rpms. Which We will do that in another article.

1. First download and install the postgres 14

https://yum.postgresql.org/rpmchart/
-rwxr-xr-x 1 root root 1556440 Apr 26 15:01 postgresql14-14.2-
1PGDG.rhel7.x86_64.rpm
-rwxr-xr-x 1 root root 698212 Apr 26 15:01 postgresql14-contrib-14.2-
1PGDG.rhel7.x86_64.rpm
-rwxr-xr-x 1 root root 273192 Apr 26 15:01 postgresql14-libs-14.2-
1PGDG.rhel7.x86_64.rpm
-rwxr-xr-x 1 root root 5783224 Apr 26 15:01 postgresql14-server-14.2-
1PGDG.rhel7.x86_64.rpm
Make sure to install the rpms in below sequence only. Otherwise you will dependency errors.
[root@dbahost14 pg_13]# yum install postgresql14-libs-14.2-
1PGDG.rhel7.x86_64.rpm
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-
manager
Examining postgresql14-libs-14.2-1PGDG.rhel7.x86_64.rpm: postgresql14-libs-
14.2-1PGDG.rhel7.x86_64
Marking postgresql14-libs-14.2-1PGDG.rhel7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package postgresql14-libs.x86_64 0:14.2-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================
==============================================================================
=====================
Package Arch Version
Repository Size
==============================================================================
==============================================================================
=====================
Installing:
postgresql14-libs x86_64 14.2-
1PGDG.rhel7 /postgresql14-libs-14.2-1PGDG.rhel7.x86_64
919 k

Transaction Summary
==============================================================================
==============================================================================
=====================
Install 1 Package

Total size: 919 k

Installed size: 919 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
Installing : postgresql14-libs-14.2-1PGDG.rhel7.x86_64
1/1
Verifying : postgresql14-libs-14.2-1PGDG.rhel7.x86_64
1/1
rhel-7-server-rpms/7Server/x86_64/productid
| 2.1 kB 00:00:00

Installed:
postgresql14-libs.x86_64 0:14.2-1PGDG.rhel7

Complete!
[root@dbahost14 pg_12]# yum install postgresql14-14.2-1PGDG.rhel7.x86_64.rpm
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-
manager
Examining postgresql14-14.2-1PGDG.rhel7.x86_64.rpm: postgresql14-14.2-
1PGDG.rhel7.x86_64
Marking postgresql14-14.2-1PGDG.rhel7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package postgresql14.x86_64 0:14.2-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================
==============================================================================
=====================
Package Arch Version
Repository Size
==============================================================================
==============================================================================
=====================
Installing:
postgresql14 x86_64 14.2-
1PGDG.rhel7 /postgresql14-14.2-1PGDG.rhel7.x86_64
7.8 M

Total size: 7.8 M

Installed size: 7.8 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql14-14.2-1PGDG.rhel7.x86_64
1/1
failed to link /usr/bin/psql -> /etc/alternatives/pgsql-psql: /usr/bin/psql
exists and it is not a symlink
failed to link /usr/bin/clusterdb -> /etc/alternatives/pgsql-clusterdb:
/usr/bin/clusterdb exists and it is not a symlink
failed to link /usr/bin/createdb -> /etc/alternatives/pgsql-createdb:
/usr/bin/createdb exists and it is not a symlink
failed to link /usr/bin/createuser -> /etc/alternatives/pgsql-createuser:
/usr/bin/createuser exists and it is not a symlink
failed to link /usr/bin/dropdb -> /etc/alternatives/pgsql-dropdb:
/usr/bin/dropdb exists and it is not a symlink
failed to link /usr/bin/dropuser -> /etc/alternatives/pgsql-dropuser:
/usr/bin/dropuser exists and it is not a symlink
failed to link /usr/bin/pg_dump -> /etc/alternatives/pgsql-pg_dump:
/usr/bin/pg_dump exists and it is not a symlink
failed to link /usr/bin/pg_dumpall -> /etc/alternatives/pgsql-pg_dumpall:
/usr/bin/pg_dumpall exists and it is not a symlink
failed to link /usr/bin/pg_restore -> /etc/alternatives/pgsql-pg_restore:
/usr/bin/pg_restore exists and it is not a symlink
failed to link /usr/bin/reindexdb -> /etc/alternatives/pgsql-reindexdb:
/usr/bin/reindexdb exists and it is not a symlink
failed to link /usr/bin/vacuumdb -> /etc/alternatives/pgsql-vacuumdb:
/usr/bin/vacuumdb exists and it is not a symlink
Verifying : postgresql14-14.2-1PGDG.rhel7.x86_64
1/1

Installed:
postgresql14.x86_64 0:14.2-1PGDG.rhel7

Complete!

[root@dbahost14 pg_12]# yum install postgresql14-server-14.2-

1PGDG.rhel7.x86_64.rpm
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-
manager
Examining postgresql14-server-14.2-1PGDG.rhel7.x86_64.rpm: postgresql14-
server-14.2-1PGDG.rhel7.x86_64
Marking postgresql14-server-14.2-1PGDG.rhel7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package postgresql14-server.x86_64 0:14.2-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================
==============================================================================
=====================
Package Arch Version
Repository Size
==============================================================================
==============================================================================
=====================
Installing:
postgresql14-server x86_64 14.2-
1PGDG.rhel7 /postgresql14-server-14.2-1PGDG.rhel7.x86_64
22 M

Total size: 22 M
Installed size: 22 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql14-server-14.2-1PGDG.rhel7.x86_64
1/1
Verifying : postgresql14-server-14.2-1PGDG.rhel7.x86_64
1/1

Installed:
postgresql14-server.x86_64 0:14.2-1PGDG.rhel7

Complete!

[root@dbahost14 pg_12]# yum install postgresql14-contrib-14.2-

1PGDG.rhel7.x86_64.rpm
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-
manager
Examining postgresql14-contrib-14.2-1PGDG.rhel7.x86_64.rpm: postgresql14-
contrib-14.2-1PGDG.rhel7.x86_64
Marking postgresql14-contrib-14.2-1PGDG.rhel7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package postgresql14-contrib.x86_64 0:14.2-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================
==============================================================================
=====================
Package Arch Version
Repository Size
==============================================================================
==============================================================================
=====================
Installing:
postgresql14-contrib x86_64 14.2-
1PGDG.rhel7 /postgresql14-contrib-14.2-
1PGDG.rhel7.x86_64 2.4 M

Total size: 2.4 M

Installed size: 2.4 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql14-contrib-14.2-1PGDG.rhel7.x86_64
1/1
Verifying : postgresql14-contrib-14.2-1PGDG.rhel7.x86_64
1/1

Installed:
postgresql14-contrib.x86_64 0:14.2-1PGDG.rhel7

Complete!
[root@dbahost14 pg_12]#
2. Initialise the postgres 14 version cluster.
-bash-4.2$ /usr/pgsql-14/bin/initdb -D /var/lib/pgsql/14/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".

The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/pgsql/14/data ... ok

creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Riyadh
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections

You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

./pg_ctl -D /var/lib/pgsql/14/data -l logfile start

3. Run the postgres upgrade with check option.

existing data_directory(PG12) – > /var/lib/pgsql/12/data
existing postgres binary(PG12) – > /usr/pgsql-12/bin
new data_directory(PG14) – > /var/lib/pgsql/14/data
new postgres binary(PG14) – > /usr/pgsql-14/bin
It will not do the actual upgrade , it will do the precheck.
-bash-4.2$ pwd
/usr/pgsql-14/bin
-bash-4.2$ /usr/pgsql-14/bin/pg_upgrade -d /var/lib/pgsql/12/data -D
/var/lib/pgsql/14/data -b /usr/pgsql-12/bin -B /usr/pgsql-14/bin -c

could not open log file "pg_upgrade_internal.log": Permission denied

Failure, exiting
-- To avoid this error, go to /tmp location and run teh command again.
-bash-4.2$ pwd
/usr/pgsql-14/bin
-bash-4.2$ cd /tmp
-bash-4.2$ /usr/pgsql-14/bin/pg_upgrade -d /var/lib/pgsql/12/data -D
/var/lib/pgsql/14/data -b /usr/pgsql-12/bin -B /usr/pgsql-14/bin -c
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok

Clusters are compatible

-- Alternatively you can mention --link option

-bash-4.2$ /usr/pgsql-14/bin/pg_upgrade -d /var/lib/pgsql/12/data -D

/var/lib/pgsql/14/data -b /usr/pgsql-12/bin -B /usr/pgsql-14/bin --link --
check
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok

Clusters are compatible

NOTE – > If you not using link option, then if your postgres cluster size is
of 100 GB, then those 100GB will be copied to the new cluster. But if you are
using link option, then it will just create symobolic link, Instead of copying

4. Run the actual upgrade:
You need to shutdown the existing cluster before the starting upgrade,
otherwise below error.
cd /tmp
-bash-4.2$ /usr/pgsql-14/bin/pg_upgrade -d /var/lib/pgsql/12/data -D
/var/lib/pgsql/14/data -b /usr/pgsql-12/bin -B /usr/pgsql-14/bin --link
There seems to be a postmaster servicing the old cluster.
Please shutdown that postmaster and try again.
Failure, exiting

-bash-4.2$ /usr/pgsql-12/bin/pg_ctl stop -D /var/lib/pgsql/12/data

waiting for server to shut down.... done
server stopped

-bash-4.2$ /usr/pgsql-14/bin/pg_upgrade -d /var/lib/pgsql/12/data -D

/var/lib/pgsql/14/data -b /usr/pgsql-12/bin -B /usr/pgsql-14/bin --link
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok

If pg_upgrade fails after this point, you must re-initdb the

new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_xact to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
ok
Adding ".old" suffix to old global/pg_control ok
If you want to start the old cluster, you will need to remove
the ".old" suffix from /var/lib/pgsql/12/data/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.

Linking user relation files

ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to delete old cluster ok
Checking for extension updates ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
/usr/pgsql-14/bin/vacuumdb --all --analyze-in-stages

Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
-bash-4.2$

5. Start the new postgres cluster.

-bash-4.2$ /usr/pgsql-14/bin/pg_ctl start -D /var/lib/pgsql/14/data
waiting for server to start....2022-04-26 22:51:53.194 +03 [18271] LOG:
redirecting log output to logging collector process
2022-04-26 22:51:53.194 +03 [18271] HINT: Future log output will appear in
directory "log".
done
server started

Once your upgrade is successful, you can delete your old cluster using the
script.delete_old_cluster.sh
./delete_old_cluster.sh

How To Clone A Postgres Database To Remote Server

In the below tutorial, we will explain how to clone a POSTGRES database to remote server.

Source postgres details:

host -> dbaclass-local
db_name -> dbatest — > This database need to be migrated.
port -> 5444
superuser -> enterprisedb

Remote postgres server details:

host -> dbaclass-remote
port -> 5444
superuser -> enterprisedb
exiting_db -> postgres

1. Test whether you are able to connect from local to remote existing database.[on local]
local$ psql --host dbaclass-remote -p 5444 -d postgres
psql (12.3.4)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "enterprisedb" on host
"dbaclass-remote" at port "5444".

3. Now run the clone script using pg_dump command:[RUN ON LOCAL SERVER]
syntax – > pg_dump -C -d database – U username | psql -h remotehost -U
username -d remote_database

Here remote_database is an existing database in the remote postgres cluster, so that this

command can connect to an existing database and run the create database command.
-bash-4.2$ pg_dump -C -d dbatest -U enterprisedb | psql -h dbaclass-remote -U
enterprisedb -d postgres

SET
SET
SET
SET
SET
SET
SET
set_config
------------

(1 row)

SET
SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "dbatest" as user "enterprisedb".
SET
SET
SET
SET
SET
SET
SET
set_config
------------

(1 row)

SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE SCHEMA
ALTER SCHEMA
CREATE EXTENSION
COMMENT
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 12544
COPY 160
COPY 12544
COPY 12544

How To Enable And Disable Archive Mode In Postgres

Below are the steps for enabling archive mode or wal archiving in postgres.

STEPS TO ENABLE ARCHIVE MODE IN POSTGRES:

1. Check archive setting in the postgres config file:
postgres=# select name,setting from pg_settings where name like 'archive%' ;
name | setting
-------------------------+------------
archive_cleanup_command |
archive_command | (disabled)
archive_mode | off --- >>>
archive_timeout | 0
(4 rows)

postgres=# show wal_level

wal_level
-----------
minimal
(1 row)

If wal_le

2. Now alter below parameters:

Alternatively you can change below parameter directly in the postgres.conf file:

postgres=# alter system set archive_mode=on;

ALTER SYSTEM

postgres=# alter system set archive_command='test ! -f

/var/lib/edb/as12/archive/%f && cp %p /var/lib/edb/as12/archive/%f';
ALTER SYSTEM

postgres=# alter system set wal_level=replica;

ALTER SYSTEM

3. Now restart the postgres instance:

[root@]# systemctl stop edb-as-12
[root@]# systemctl start edb-as-12

-- ALTERNATIVELY YOU CAN USE PG_CTL COMMAND:

export PGDATA=/var/lib/edb/as12/data
pg_ctl stop
pg_ctl start

4. Now check archive mode:

postgres=# select name,setting from pg_settings where name like 'archive%';
name | setting
-------------------------+---------------------------------------------------
archive_cleanup_command |
archive_command | test ! -f /var/lib/edb/as12/archive/%f && cp %p
/var/lib/edb/as12/archive/%f
archive_mode | on
archive_timeout | 0
(4 rows)

postgres=# show wal_level;

wal_level
-----------
replica
(1 row)

5. Do manual log switch and check whether archive is generating or not:

postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/1D392648
(1 row)

postgres=# select pg_switch_wal();

pg_switch_wal
---------------
0/1E000000
(1 row)

[enterprisedb@localhost archive]$ pwd

/var/lib/edb/as12/archive

-rw-------. 1 enterprisedb enterprisedb 16777216 Oct 20 19:44

00000001000000000000001C
-rw-------. 1 enterprisedb enterprisedb 16777216 Oct 20 19:56
00000001000000000000001D

STEPS TO DISABLE ARCHIVE MODE:

1. Set archive_mode to off:
postgres=# alter system set archive_mode=off;
ALTER SYSTEM

2. Now restart the postgres instance:

[root@]# systemctl stop edb-as-12
[root@]# systemctl start edb-as-12

3. Check archive mode:

postgres=# select name,setting from pg_settings where name like 'archive%';
name | setting
-------------------------
+-----------------------------------------------------------------------------
-
archive_cleanup_command |
archive_command | test ! -f /var/lib/edb/as12/archive/%f && cp %p
/var/lib/edb/as12/archive/%f
archive_mode | off
archive_timeout | 0
(4 rows)

How To Move A Tablespace To New Directory In Postgres

Below are the steps for moving a tablespace to a new file system/mount point in postgres.

EXAMPLE:
TABLESPACE_NAME – > proddb_ts
TABLESPACE_OID – 19847
NEW MOUNT POINT – > /new_fs/data

1. Get the tablespace details and existing path:

postgres=# select * from pg_tablespace;

oid | spcname | spcowner | spcacl

| spcoptions
-------+------------+----------
+------------------------------------------------------+------------
1663 | pg_default | 10 |
|
1664 | pg_global | 10 |
|
19847 | prodb_ts | 10 |
{enterprisedb=C/enterprisedb,devusr=C*/enterprisedb} |
(3 rows)

Currently the tablespace path is /old_fs/edb

2. Stop the postgres instance:

NOTE – > Here i am using edb postgres , so used edb-as-12 service name, you
can use pg_ctl stop command also.

[rootedb]# service edb-as-12 status

INFO: [PID: 58076]

INFO: [CMD: /usr/edb/as12/bin/edb-postgres]
MSG: [edb-as-12 is running]
INFO: [Please see service script file /var/log/edb/as12/edb-as-12_script.log
for details]

[rootedb]# service edb-as-12 stop

INFO: [PID: 58076]

INFO: [CMD: /usr/edb/as12/bin/edb-postgres]

Stopping edb-as-12 [ OK ]

MSG: [edb-as-12 stopped]

INFO: [Please see service script file /var/log/edb/as12/edb-as-12_script.log

for details]

3. Move the directoty to the new path:

-bash-4.1$ mv /old_fs/data /new_fs/data

4. Now Do the relinking:

-bash-4.1$ echo $PGDATA
/var/lib/edb/as12/data

-bash-4.1$ cd $PGDATA/pg_tblspc

-bash-4.1$ ls -ltr
total 0
lrwxrwxrwx 1 enterprisedb enterprisedb 13 Jun 10 16:24 19847 -> /old_fs/data
-bash-4.1$ pwd
/var/lib/edb/as12/data/pg_tblspc

--- Relink command

-bash-4.1$ ln -fs /new_fs/data 19847
/dmdata06/edb:
total 12
4 .. 4 . 4 edb

19847:
total 8
4 .. 0 edb 4 .

-bash-4.1$ ls -ltr
total 0
lrwxrwxrwx 1 enterprisedb enterprisedb 13 Oct 15 16:22 19847 -> /new_fs/data

5. Star the postgres instance:

[root@edb]# service edb-as-12 start
Starting edb-as-12 [ OK ]

INFO: [PID: 34285]

INFO: [CMD: /usr/edb/as12/bin/edb-postgres -D /var/lib/edb/as12/data -p 5444]
MSG: [edb-as-12 started]
INFO: [Please see service script file /var/log/edb/as12/edb-as-12_script.log
for details]

psql (12.3.4)
Type "help" for help.

Now check the tablespace information:

How To Create Tablespace In Postgres With Streaming Replication

Creating tablespace in a standalone postgres server is a very straight forward. But what if we
need to create a tablespace in postgres with streaming replication enabled. Will the tablespace be
created automatically in the standby?

Let’s see the below demo to find out.

1. Check streaming replication detail[ RUN ON PRIMARY ]

Expanded display is on.

postgres=# select * from pg_stat_replication

postgres-# ;
-[ RECORD 1 ]----+---------------------------------
pid | 6075
usesysid | 10
usename | enterprisedb
application_name | walreceiver

client_addr | 10.20.30.41. ---- > This is the hot standby server.

2. Create tablespace in primary:

-- Create directory structure

primary$ mkdir -p /pgdata/alm_ts

posgres# create tablespace alm_ts location '/pgdata/alm_ts';

CREATE TABLESPACE

3. Check whether standby (replication) server status:

-- Run on primary server

postgres=# select * from pg_stat_replication;

(0 rows)

-- Standby server log snippet:

2021-06-15 10:48:23 +03 LOG: started streaming WAL from primary at 0/5000000
on timeline 1
2021-06-15 10:59:51 +03 FATAL: directory "/pgdata/alm_ts" does not exist
2021-06-15 10:59:51 +03 HINT: Create this directory for the tablespace before
restarting the server.
2021-06-15 10:59:51 +03 CONTEXT: WAL redo at 0/5000520 for Tablespace/CREATE:
16385 "/pgdata/alm_ts"
2021-06-15 10:59:51 +03 LOG: startup process (PID 18171) exited with exit
code 1
2021-06-15 10:59:51 +03 LOG: terminating any other active server processes
2021-06-15 10:59:51 +03 LOG: database system is shut down

We can see the standby server is down , because the directory structure of the
new tablespace is not present.So to fix it just create the same directory
structure in standby.
4. Create directory structure in standby server
-- on standby
standby$ mkdir -p /pgdata/alm_ts

5. Now start the postgres cluster on standby:

-- Start the postgres on standby
root# systemctl start edb-as-12

-- Run on standby (
postgres#=# SELECT pg_is_in_recovery();
pg_is_in_recovery
───────────────────
t
(1 row)

Here t means recovery is running fine .

-- check the log
2021-06-15 15:05:19 +03 LOG: database system was shut down in recovery at
2021-06-15 15:05:14 +03
2021-06-15 15:05:19 +03 LOG: entering standby mode
2021-06-15 15:05:19 +03 LOG: redo starts at 0/70000D8
2021-06-15 15:05:19 +03 LOG: consistent recovery state reached at 0/70001C0
2021-06-15 15:05:19 +03 LOG: invalid record length at 0/70001C0: wanted 24,
got 0
2021-06-15 15:05:19 +03 LOG: database system is ready to accept read only
connections
2021-06-15 15:05:20 +03 LOG: started streaming WAL from primary at 0/7000000
on timeline 1

--- Run replication status from primary:

postgres# \x

postgres=# select * from pg_stat_replication

The standby server started successfully.

Summary – > In postgres cluster setup with streaming replication , before creating tablespace on
primary, create the tablespace directory structure on standby server .

How To Move A Database To A New Tablespace In Postgres

This article explains how to move a database to a new tablespace in postgres. This is same as
changing the default tablespace for a postgres database. For this activity we have make sure that
no application sessions are connecting to that database. We can say this activity will need some
downtime.

Objective – We will move the database prod_crm from pg_default tablespace to

crm_tblspc;

Check existing tablespace details:

Alter database command

prod_crm=# alter database prod_crm set tablespace crm_tblspc;
ERROR: cannot change the tablespace of the currently open database

prod_crm=#
Above error is because, we are connecting to the same database for which we
are altering. We should issue the command by connecting to a different
database.

-- Connect a different database

prod_crm=# \q
-bash-4.2$ psql -d postgres -p 5432
Password for user enterprisedb:
psql.bin (11.8.15)
Type "help" for help.

postgres=# alter database prod_crm set tablespace crm_tblspc;

ERROR: database "prod_crm" is being accessed by other users
DETAIL: There are 11 other sessions using the database.

New error says, the few sessions are using the database. So we need kill them.

Find the sessions using db:

postgres=# select usename,pid,application_name from pg_stat_activity where
datname='prod_crm';
usename | pid | application_name
------------+-------+------------------------
PROD_CRM | 15225 | PostgreSQL JDBC Driver
PROD_CRM | 15283 | PostgreSQL JDBC Driver
PROD_CRM | 15285 | PostgreSQL JDBC Driver
PROD_CRM | 15286 | PostgreSQL JDBC Driver
PROD_CRM | 15287 | PostgreSQL JDBC Driver
PROD_CRM | 15290 | PostgreSQL JDBC Driver
PROD_CRM | 15291 | PostgreSQL JDBC Driver
PROD_CRM | 15292 | PostgreSQL JDBC Driver
PROD_CRM | 15298 | PostgreSQL JDBC Driver
PROD_CRM | 15300 | PostgreSQL JDBC Driver
PROD_CRM | 15303 | PostgreSQL JDBC Driver
(11 rows)

As all the sessions are from PROD_CRM, so we need to kill the all the sessions
of that user.

Kill the sessions for user PROD_CRM

postgres=# select pg_terminate_backend(pid) from pg_stat_activity where
usename='PROD_CRM';
pg_terminate_backend
----------------------
t
t
t
t
t
t
t
t
t
t
t
(11 rows)

alternatively you can kill the sessions individually using below command.
select pg_terminate_backend(pid) from pg_stat_activity where pid=’15225′

postgres=# alter database prod_crm set tablespace crm_tblspc;

ALTER DATABASE

postgres=#

prod_crm=# \l+ prod_crm

1. Check existing data directory

postgres=# show data_directory;
data_directory
------------------------
/var/lib/edb/as11/data
(1 row)

2. Create new directory structure

-bash-4.2$ mkdir -p /oradata/pgdata
-bash-4.2$ chmod 0700 /oradata/pgdata

Note – if 600 permission is not provided to the directory, then PG cluster
service will fail with below error.
Active: failed (Result: exit-code) since Sun 2021-06-13 06:46:52 +03; 8s ago
Process: 761 ExecStart=/usr/edb/as11/bin/edb-postmaster -D ${PGDATA}
(code=exited, status=1/FAILURE)
Process: 753 ExecStartPre=/usr/edb/as11/bin/edb-as-11-check-db-dir ${PGDATA}
(code=exited, status=0/SUCCESS)
Main PID: 761 (code=exited, status=1/FAILURE)
Jun 13 06:46:52 scdvlbss14 systemd[1]: Starting EDB Postgres Advanced Server
11…
Jun 13 06:46:52 scdvlbss14 edb-postmaster[761]: 2021-06-13 06:46:52 +03 FATAL:
data directory “/oradata/pgdata” has invalid permissions
Jun 13 06:46:52 scdvlbss14 edb-postmaster[761]: 2021-06-13 06:46:52 +03
DETAIL: Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
Jun 13 06:46:52 scdvlbss14 systemd[1]: edb-as-11.service: main process exited,
code=exited, status=1/FAILURE
Jun 13 06:46:52 scdvlbss14 systemd[1]: Failed to start EDB Postgres Advanced
Server 11.
Jun 13 06:46:52 scdvlbss14 systemd[1]: Unit edb-as-11.service entered failed
state.
Jun 13 06:46:52 scdvlbss14 systemd[1]: edb-as-11.service failed.

3. Stop postgres service

-bash-4.2$ systemctl status edb-as-11
● edb-as-11.service - EDB Postgres Advanced Server 11
Loaded: loaded (/usr/lib/systemd/system/edb-as-11.service; disabled; vendor
preset: disabled)
Active: active (running) since Mon 2020-12-28 11:22:01 +03; 5 months 14
days ago
Main PID: 30787 (edb-postmaster)
Tasks: 10
Memory: 8.2G
CGroup: /system.slice/edb-as-11.service
├─25828 postgres: checkpointer
├─25829 postgres: background writer
├─25831 postgres: walwriter
├─25832 postgres: autovacuum launcher
├─25835 postgres: archiver last was 0000000100000010000000C8
├─25836 postgres: stats collector
├─25837 postgres: dbms_aq launcher
├─25838 postgres: logical replication launcher
├─30787 /usr/edb/as11/bin/edb-postmaster -D /var/lib/edb/as11/data
└─30790 postgres: logger
-bash-4.2$

-- Run stop command

-bash-4.2$ systemctl stop edb-as-11

4. Copy the data directory to new location:

-bash-4.2$ cd /var/lib/edb/as11/data
-bash-4.2$ cp -r * /oradata/pgdata/
-bash-4.2$ ls -ltr /oradata/pgdata/

5. Update postgres service file:[ as root user ]

It is not advisable to edit service file from default location. So copy it
to /etc/systemd/system location and edit it there.

[root]# cp /usr/lib/systemd/system/edb-as-11.service /etc/systemd/system/

[root]# cd /etc/systemd/system/
[rootsystem]# ls -ltr edb*
-rw-r--r-- 1 root root 1396 Jun 13 06:41 edb-as-11.service

-- Edit below path, pointing to new data_directory

[root@scdvlbss14 system]# vi edb-as-11.service

# Location of database directory

Environment=PGDATA=/oradata/pgdata
PIDFile=/oradata/pgdata/postmaster.pid

In our environment, data_directory, hba_file, ident_file parameters were
commented in postgres.conf file, i.e the values are getting picked from
service file. So we are not doing any change to postgres.conf file.

# FILE LOCATIONS
#-----------------------------------------------------------------------------
-

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

#data_directory = 'ConfigDir' # use data in another directory

# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
# (change requires restart)

So Please crosscheck postgres.conf file, If these parameters were defined and

uncommented, then you need to update the path in this file also.
6 . Reload the systemctl daemon:
[root#] systemctl daemon-reload

7. Start the postgres service:
[root]# systemctl start edb-as-11

-- Check status:
[root]# systemctl status edb-as-11
● edb-as-11.service - EDB Postgres Advanced Server 11
Loaded: loaded (/etc/systemd/system/edb-as-11.service; disabled; vendor
preset: disabled)
Active: active (running) since Sun 2021-06-13 06:47:34 +03; 6s ago
Process: 1741 ExecStartPre=/usr/edb/as11/bin/edb-as-11-check-db-dir $
{PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 1748 (edb-postmaster)
Tasks: 10
Memory: 202.8M
CGroup: /system.slice/edb-as-11.service
├─1748 /usr/edb/as11/bin/edb-postmaster -D /oradata/pgdata
├─1751 postgres: logger
├─1753 postgres: checkpointer
├─1754 postgres: background writer
├─1755 postgres: walwriter
├─1756 postgres: autovacuum launcher
├─1757 postgres: archiver
├─1758 postgres: stats collector
├─1759 postgres: dbms_aq launcher
└─1760 postgres: logical replication launcher

Jun 13 06:47:33 dbhost1 systemd[1]: Starting EDB Postgres Advanced Server

11...
Jun 13 06:47:33 dbhost1 edb-postmaster[1748]: 2021-06-13 06:47:33 +03 LOG:
listening on IPv4 address "0.0.0.0", port 5435
Jun 13 06:47:33 dbhost1 edb-postmaster[1748]: 2021-06-13 06:47:33 +03 LOG:
listening on IPv6 address "::", port 5435
Jun 13 06:47:33 dbhost1 edb-postmaster[1748]: 2021-06-13 06:47:33 +03 LOG:
listening on Unix socket "/tmp/.s.PGSQL.5435"
Jun 13 06:47:34 dbhost1 edb-postmaster[1748]: 2021-06-13 06:47:34 +03 LOG:
redirecting log output to logging collector process
Jun 13 06:47:34 dbhost1 edb-postmaster[1748]: 2021-06-13 06:47:34 +03 HINT:
Future log output will appear in directory "log".
Jun 13 06:47:34 dbhosg1 systemd[1]: Started EDB Postgres Advanced Server 11.

8. Check the data_directory value:

postgres=# show data_directory;
data_directory
-----------------
/oradata/pgdata
(1 row)

We have successfully changed the data_directory.

How To Make A Postgres Database Readonly
In postgres you can make a particular database or all databases of a postgres cluster.
default_transaction_read_only parameter controls whether database is in read only or read
write .
default_transaction_read_only -> on ( for read only )
default_transaction_read_only -> off ( for read write )

For making single db read only.( DB_NAME – > edbstore)

–connect to database other than the edbstore ( here i connected to default postgres db)
[enterprisedb@localhost ~]$ psql -d postgres
psql (12.4.5)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "enterprisedb" via socket in
"/tmp" at port "5444".

postgres=# alter database edbstore set default_transaction_read_only=on;

ALTER DATABASE

— Now restart the postgres cluster(either using pg_ctl or service_name)

[root@localhost ~]# systemctl stop edb-as-12
[root@localhost ~]# systemctl start edb-as-12

— Now login to database and check:

edbstore=# \conninfo
You are connected to database "edbstore" as user "enterprisedb" via socket in
"/tmp" at port "5444".
edbstore=# create table test as select * from pg_settings;
ERROR: cannot execute CREATE TABLE AS in a read-only transaction

We can see it is not allowing write operations.

Now if you wish to remove the database from read only mode, then
postgres=# \conninfo
You are connected to database "postgres" as user "enterprisedb" via socket in
"/tmp" at port "5444".

postgres=# alter database edbstore set default_transaction_read_only=off;

ALTER DATABASE

[root@localhost ~]# systemctl stop edb-as-12

[root@localhost ~]# systemctl start edb-as-12

For making all the databases of the postgres cluster read only:
postgres=# show default_transaction_read_only;
default_transaction_read_only
-------------------------------
off
(1 row)

postgres=# alter system set default_transaction_read_only=on;

ALTER SYSTEM

-- Restart the pg cluster:

[root@localhost ~]# systemctl stop edb-as-12
[root@localhost ~]# systemctl start edb-as-12

How To Change Port Number In Postgres

This article explains how to change the port number in postgres. We will change the port
from 5444 to 5432.

1. Check the existing port details

postgres=# \conninfo
You are connected to database "postgres" as user "enterprisedb" via socket in
"/tmp" at port "5444".

postgres=# show config_file;

config_file
---------------------------------
/pgdata/data/postgresql.conf
(1 row)

[enterprisedb@master ~]$ cat /pgdata/data/postgresql.conf | grep 'port'

port = 5444 # (change requires restart)

2. Update the port in postgresql.conf file:

-- change the port from 5444 to 5432

[enterprisedb@master ~]$ cat /pgdata/data/postgresql.conf | grep 'port'

port = 5432

3. Restart postgres services:

pg_ctl stop -D /pgdata/data
pg_ctl start -D /pgdata/data

Alternatively you can restart the service, if configured.

root# systemctl stop edb-as-11
root# systemctl start edb-as-11

4. Check whether port has been updated

[enterprisedb@master ~]$psql -d postgres -p 5432
postgres=# \x
Expanded display is on.

postgres=# select * from pg_settings where name='port';

We can see , the port has been updated to 5432.

Now If any streaming replication is enabled, then we need to update the
primary server port in recovery.conf file of standby server.

5. Check for any streaming replication ( run On primary server)

It shows replication is enabled to server 10.20.30.77(standby server). So we

need to update the recovery.conf file in that standby server.

6. Update the recovery.conf file in standby server.

-- recovery.conf file resides inside data directory.
[enterprisedb@standby]$ cat /pgdata/data/recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=enterprisedb password=edbpostgres#123
host=10.20.30.76 port=5432 sslmode=prefer sslcompression=0 krbsrvname=postgres
target_session_attrs=any'
primary_slot_name = 'slot1'

7. Updating the postgresql.conf file:

Just like primary, if you want to change the listening port from 5444 to 5432
in standby( just like primary) also, then update the postgresql.conf file in
standby server also. Otherwise you can continue with the same port.

[enterprisedb@master ~]$ cat /pgdata/data/postgresql.conf | grep 'port'

port = 5432 # (change requires restart)

8. Restart the pg services in standby server.

pg_ctl stop -D /pgdata/data
pg_ctl start -D /pgdata/data

Alternatively you can restart the service, if configured.

root# systemctl stop edb-as-11
root# systemctl start edb-as-11

8. Check replication status on standby:

postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------
+-----------------------------------------------------------------------------
------------------------------------------------------------------------------
-------------------------------------------
pid | 8124
status | streaming
receive_start_lsn | 0/D000000
receive_start_tli | 1
received_lsn | 0/F001AB8
received_tli | 1
last_msg_send_time | 01-JUN-21 16:59:57.746814 +03:00
last_msg_receipt_time | 01-JUN-21 16:59:57.747272 +03:00
latest_end_lsn | 0/F001AB8
latest_end_time | 01-JUN-21 09:54:24.322036 +03:00
slot_name | slot1
sender_host | 10.20.30.76
sender_port | 5432
conninfo | user=enterprisedb password=******** dbname=replication
host=10.20.30.76 port=5432 fallback_application_name=walreceiver
sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any
How To Access Csv Files On File System Using File_fdw

File_fdw is an extension which can be used to access flat files like csv in the server’s file system.

Lets see the below DEMO:

1. Check whether file_fdw extension is available on server or not:

2. Now create the extension:

3. Now create a server:

edb=# create server file_server foreign data wrapper file_fdw;
CREATE SERVER

4. prepare the csv file:

-bash-4.2$ cat /var/lib/edb/test.csv
1, ram, MANAGER
2, RAVI , HR
3, SAMUEL , IRC
4, ABDUL , LEAD
5, PABLO , MANAGER

5. Now create the foreign table:

edb=# create foreign table file_test ( empid int, emp_name varchar , dept_name
varchar) server file_server options (filename '/var/lib/edb/test.csv',format
'csv');
CREATE FOREIGN TABLE

6. Try to access the foreign table:

Now change some data in file and see the output again.
-bash-4.2$ cat /var/lib/edb/test.csv
100,ram,MANAGER
200,RAVI,HR
300,SAMUEL,IRC
400,ABDUL,LEAD
500,PABLO,MANAGER

edb=# select * from file_test;

What will happen if we try to update the table???

edb=# delete from file_test;
ERROR: cannot delete from foreign table “file_test”

i.e these tables cannot be changed using insert/update/delete command. these

are read only tables.
Check the explain plan:
edb=# explain analyze select * from file_test;
QUERY PLAN
------------------------------------------------------------------------------
--------------------------
Foreign Scan on file_test (cost=0.00..1.10 rows=1 width=68) (actual
time=0.049..0.059 rows=5 loops=1)
Foreign File: /var/lib/edb/test.csv
Foreign File Size: 76 b
Planning Time: 0.186 ms
Execution Time: 0.213 ms
(5 rows)

How To Create Database Link In Postgres

This article explain how can we access data from a remote postgres database using db_link. In
postgres we call achieve this using foreign data wrappers.

1. Create extension dblink if not present.

postgres=# create extension dblink;
CREATE EXTENSION

2. Create Foreign server pointing to remote db details:

postgres=# CREATE SERVER pg_rep_db FOREIGN DATA WRAPPER dblink_fdw OPTIONS (
host '10.21.100.131' ,dbname 'postgres' , port '5444');
CREATE SERVER

pg_rep_db | enterprisedb | dblink_fdw | | |

| (host '10.21.100.131', dbname 'postgres', port '5444') |

(2 rows)

3. Create user mapping details:

postgres=# CREATE USER MAPPING FOR enterprisedb SERVER pg_rep_db OPTIONS
( user 'dba_raj' ,password 'dba_raj');
CREATE USER MAPPING
postgres=#

postgres=# \deu+
List of user mappings
Server | User name | FDW options
------------+--------------+----------------------------------------
pg_rep_db | enterprisedb | ("user" 'dba_raj', password 'dba_raj')

4. Test db_link connection:

postgres=# SELECT dblink_connect('my_new_conn', 'pg_rep_db');
dblink_connect
----------------
OK
(1 row)

5. Retrieve data using db_link:

postgres=# select * from dblink('pg_rep_db','select object_name from test') as
object_list(object_list varchar );
object_list
---------------------------------------------------
PG_AGGREGATE_FNOID_INDEX
PG_AM_NAME_INDEX
PG_AM_OID_INDEX
PG_AMOP_FAM_STRAT_INDEX

postgres=#
postgres=# select * from dblink('pg_rep_db','select count(*) from test') as
total_count(total_count int);
total_count
-------------
4122
(1 row)

How To Access Oracle Database From Postgres

This article explain From postgres, how can we fetch oracle db data. For this we need to use
foreign data wrapper and foreign table concepts.
DEMO:

1. Create oracle client on postgres server:

2. Install oracle_fdw extension on postgres server:
Download the package from github – https://github.com/laurenz/oracle_fdw

[root]# export
ORACLE_HOME=/var/lib/edb/app/enterprisedb/product/19.0.0/client_1

[root]# export PATH=/usr/edb/as12/bin:$PATH

[root]# cd /pgdata
[root]# cd oracle_fdw-master/

[root]# make
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-
statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-
security -fno-strict-aliasing -fwrapv -fexcess-precision=standard
-I/usr/libexec/edb-as12-icu53.1/include/ -O2 -g -pipe -Wall -Wp,-
D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-
size=4 -grecord-gcc-switches -m64 -mtune=generic -I/usr/include/et -fPIC
-I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/sdk/include"
-I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/oci/include"
-I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/rdbms/public"
-I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/"
-I/usr/include/oracle/21/client64 -I/usr/include/oracle/19.14/client64
-I/usr/include/oracle/19.12/client64 -I/usr/include/oracle/19.12/client
-I/usr/include/oracle/19.11/client64 -I/usr/include/oracle/19.11/client
-I/usr/include/oracle/19.10/client64 -I/usr/include/oracle/19.10/client
-I/usr/include/oracle/19.9/client -I/usr/include/oracle/19.9/client64
-I/usr/include/oracle/19.8/client -I/usr/include/oracle/19.8/client64
-I/usr/include/oracle/19.6/client -I/usr/include/oracle/19.6/client64
-I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64
-I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64
-I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64
-I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64
-I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64
-I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I. -I./
-I/usr/edb/as12/include/server -I/usr/edb/as12/include/internal
-I/usr/libexec/edb-as12-icu53.1/include/ -I/usr/include/et -D_GNU_SOURCE
-I/usr/include/libxml2 -I/usr/include -c -o oracle_utils.o oracle_utils.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-
statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-
security -fno-strict-aliasing -fwrapv -fexcess-precision=standard
-I/usr/libexec/edb-as12-icu53.1/include/ -O2 -g -pipe -Wall -Wp,-
D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-
size=4 -grecord-gcc-switches -m64 -mtune=generic -I/usr/include/et -fPIC
-I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/sdk/include"
-I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/oci/include"
-I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/rdbms/public"
-I"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/"
-I/usr/include/oracle/21/client64 -I/usr/include/oracle/19.14/client64
-I/usr/include/oracle/19.12/client64 -I/usr/include/oracle/19.12/client
-I/usr/include/oracle/19.11/client64 -I/usr/include/oracle/19.11/client
-I/usr/include/oracle/19.10/client64 -I/usr/include/oracle/19.10/client
-I/usr/include/oracle/19.9/client -I/usr/include/oracle/19.9/client64
-I/usr/include/oracle/19.8/client -I/usr/include/oracle/19.8/client64
-I/usr/include/oracle/19.6/client -I/usr/include/oracle/19.6/client64
-I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64
-I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64
-I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64
-I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64
-I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64
-I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I. -I./
-I/usr/edb/as12/include/server -I/usr/edb/as12/include/internal
-I/usr/libexec/edb-as12-icu53.1/include/ -I/usr/include/et -D_GNU_SOURCE
-I/usr/include/libxml2 -I/usr/include -c -o oracle_gis.o oracle_gis.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-
statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-
security -fno-strict-aliasing -fwrapv -fexcess-precision=standard
-I/usr/libexec/edb-as12-icu53.1/include/ -O2 -g -pipe -Wall -Wp,-
D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-
size=4 -grecord-gcc-switches -m64 -mtune=generic -I/usr/include/et -fPIC -
shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o
-L/usr/edb/as12/lib -L/usr/libexec/edb-as12-icu53.1/lib/
-L/usr/lib64/llvm5.0/lib -L/usr/lib64 -Wl,--as-needed
-Wl,-rpath,'/usr/edb/as12/lib',--enable-new-dtags
-L"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/"
-L"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/bin"
-L"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/lib"
-L"/var/lib/edb/app/enterprisedb/product/19.0.0/client_2/lib/amd64" -lclntsh -
L/usr/lib/oracle/21/client64/lib -L/usr/lib/oracle/19.14/client64/lib
-L/usr/lib/oracle/19.12/client64/lib -L/usr/lib/oracle/19.12/client/lib
-L/usr/lib/oracle/19.11/client64/lib -L/usr/lib/oracle/19.11/client/lib
-L/usr/lib/oracle/19.10/client64/lib -L/usr/lib/oracle/19.10/client/lib
-L/usr/lib/oracle/19.9/client/lib -L/usr/lib/oracle/19.9/client64/lib
-L/usr/lib/oracle/19.8/client/lib -L/usr/lib/oracle/19.8/client64/lib
-L/usr/lib/oracle/19.6/client/lib -L/usr/lib/oracle/19.6/client64/lib
-L/usr/lib/oracle/19.3/client/lib -L/usr/lib/oracle/19.3/client64/lib
-L/usr/lib/oracle/18.5/client/lib -L/usr/lib/oracle/18.5/client64/lib
-L/usr/lib/oracle/18.3/client/lib -L/usr/lib/oracle/18.3/client64/lib
-L/usr/lib/oracle/12.2/client/lib -L/usr/lib/oracle/12.2/client64/lib
-L/usr/lib/oracle/12.1/client/lib -L/usr/lib/oracle/12.1/client64/lib
-L/usr/lib/oracle/11.2/client/lib -L/usr/lib/oracle/11.2/client64/lib

[root]# make install

/bin/mkdir -p '/usr/edb/as12/lib'
/bin/mkdir -p '/usr/edb/as12/share/extension'
/bin/mkdir -p '/usr/edb/as12/share/extension'
/bin/mkdir -p '/usr/share/doc/extension'
/bin/install -c -m 755 oracle_fdw.so '/usr/edb/as12/lib/oracle_fdw.so'
/bin/install -c -m 644 .//oracle_fdw.control '/usr/edb/as12/share/extension/'
/bin/install -c -m 644 .//oracle_fdw--1.2.sql .//oracle_fdw--1.0--1.1.sql
.//oracle_fdw--1.1--1.2.sql '/usr/edb/as12/share/extension/'
/bin/install -c -m 644 .//README.oracle_fdw '/usr/share/doc/extension/'

3. Get postgres system service file.

[rootdata]# systemctl status edb-as-12.service
● edb-as-12.service - EDB Postgres Advanced Server 12
Loaded: loaded (/usr/lib/systemd/system/edb-as-12.service; disabled; vendor
preset: disabled)
Active: active (running) since Mon 2022-04-18 18:23:33 +03; 17s ago
Main PID: 13437 (edb-postmaster)
Tasks: 12
CGroup: /system.slice/edb-as-12.service
├─13437 /usr/edb/as12/bin/edb-postmaster -D /var/lib/edb/as12/data
├─13440 postgres: logger
├─13442 postgres: checkpointer
├─13443 postgres: background writer
├─13444 postgres: walwriter
├─13445 postgres: autovacuum launcher
├─13446 postgres: stats collector
├─13447 postgres: dbms_aq launcher
├─13448 postgres: logical replication launcher

.
.
.
.............
--- as it is under /usr/lib/ location. better to copy the file to /etc/ and
update the file there itself.

cp /usr/lib/systemd/system/edb-as-12.service /etc/systemd/system/edb-as-
12.service

NOTE – > If you using pg_ctl command manually to stop and start postgres
cluster(instead of service), then you just need to define the variables inside
bash_profile. and restart using pg_ctl .
4. Update the service file with environment variables:
-bash-4.2$ cat /etc/systemd/system/edb-as-12.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades. If you want to customize, the
# best way is to create a file "/etc/systemd/system/edb-as-12.service",
# containing
# .include /lib/systemd/system/edb-as-12.service
# ...make your changes here...
# For more info about custom unit files, see
#
http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_
custom_unit_file.3F

# Note: changing PGDATA will typically require adjusting SELinux

# configuration as well.

[Unit]
Description=EDB Postgres Advanced Server 12
After=syslog.target network.target

[Service]
Type=notify
Environment=ORACLE_HOME=/var/lib/edb/app/enterprisedb/product/19.0.0/client_1
Environment=TNS_ADMIN=/var/lib/edb/app/enterprisedb/product/19.0.0/client_1/
bin
Environment=LD_LIBRARY_PATH=/var/lib/edb/app/enterprisedb/product/19.0.0/
client_1/lib
User=enterprisedb
Group=enterprisedb
# Location of database directory
Environment=PGDATA=/var/lib/edb/as12/data
PIDFile=/var/lib/edb/as12/data/postmaster.pid

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster

OOMScoreAdjust=-1000

ExecStartPre=/usr/edb/as12/bin/edb-as-12-check-db-dir ${PGDATA}
ExecStart=/usr/edb/as12/bin/edb-postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300

[Install]
WantedBy=multi-user.target
5. Reload the daemon and restart the postgres cluster:
[root~]# systemctl daemon-reload
[root ~]# systemctl stop edb-as-12.service
[root ~]# systemctl start edb-as-12.service

6. Create the extension oracle_fdw;

postgres=# create extension oracle_fdw;
CREATE EXTENSION
postgres=#

postgres=# \dx oracle*

7. Create foreign server:

postgres=# create server ora_gamedb foreign data wrapper oracle_fdw options
(dbserver '10.21.45.64:1524/AMIIFDEV');
CREATE SERVER
postgres=# \des+
List of
foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type |
Version | FDW options |
Description
------------+--------------+----------------------+-------------------+------
+---------+--------------------------------------------------------+
-------------
ora_gamedb | enterprisedb | oracle_fdw | | |
| (dbserver '10.21.45.64:1524/AMIIFDEV') |

(2 rows)

8. Create user mapping:

— provide oracle database username and password
postgres=# create user mapping for enterprisedb server ora_gamedb options
(USER 'dba_raj' , password 'dba_raj');
CREATE USER MAPPING
postgres=# \deu+
List of user mappings
Server | User name | FDW options
------------+--------------+----------------------------------------
ora_gamedb | enterprisedb | ("user" 'dba_raj', password 'dba_raj')

9. Now create the foreign table to access oracle data:

The table (EMP) structure has to be same as that of oracle database

postgres=# create foreign table EMP (EMP_NAME VARCHAR2(20), EMP_ID numeric)

server ora_gamedb options ( schema 'DBA_RAJ' , table 'EMP');
CREATE FOREIGN TABLE

postgres=# select count(*) from EMP;

count
-------
1
^
postgres=# select * from emp;
emp_name | emp_id
----------+--------
KALAM | 1
(1 row)

COMMON ISSUES DURING SETUP:

1. If environmental variables are not set properly, then below error.
postgres=# select count(*) from EMP;
ERROR: error connecting to Oracle: OCIEnvCreate failed to create environment
handle
DETAIL:
Make sure to set env values as explained in point 4 of this article.

2. If ld_library_path is not set or incorrect , then below error

postgres=# create extension oracle_fdw;
ERROR: could not load library "/usr/edb/as12/lib/oracle_fdw.so":
libclntsh.so.19.1: cannot open shared object file: No such file or directory
Make sure to set LD_LIBRARY_PATH values as explained in point 4 of this
article.

How To Setup Streaming Replication In Postgres

In this article, we will explain how to configure streaming replication in postgres from existing
primary database to a new standby database( slave).

ENVIRONMENT DETAILS:
PRIMARY SERVER= 10.20.30.40
STANDBY SERVER =10.20.30.41
REPLICATION MODE- ASYNC
NOTE – We are using edb postgres advanced server for our environment. You can use the
postgres open version also. Steps are same for both database flavours.

Configure master server for streaming replication:

postgres=# \conninfo
You are connected to database "postgres" as user "enterprisedb" via socket in
"/tmp" at port "5444".

3. Update pg_hba.conf file primary server

Add host entries for standby server(10.20.30.41) in primary
sever(10.20.30.410) pg_hba.conf file:
updated pg_hba.conf file:

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only

local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
host all all 10.20.30.41/32 md5
# IPv6 local connections:
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 ident
host replication all ::1/128 ident
host replication all 10.20.30.41/32 md5

4. Update postgres.conf file in primary server.

Update the postgrs.conf file with below values.

postgres=# show config_file;

config_file
---------------------------------
/postdata/data/postgresql.conf
(1 row)

vi /pgdata/data/postgresql.conf
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'cp %p /postdata/pg_archive/%f' # command to use to
archive a logfile segment
max_wal_senders = 10 # max number of walsender processes
# (change requires restart)
wal_keep_segments = 50 # in logfile segments; 0 disables
wal_level = replica # minimal, replica, or logical

5. Restart postgres services:

systemctl stop edb-as-11
systemctl start edb-as-11

CONFIGURE STANDBY SERVER FOR STREAMING REPLICATION:

1. Install postgres binary on standby server.
On the standby server , just install the postgres binary. No need to initialise the postgres cluster.
Install EDB postgres on Linux.

2. Restore full backup of master on standby server.

-- Run below command on standby server
-- here 10.20.30.40 is primary server ip

standby$ /usr/edb/as11/bin/pg_basebackup -D /pgdata/edbdata/ -X fetch -p 5444

-U enterprisedb -h 10.20.30.40 -R

Now check whether files are available under /pgdata/edbdata directory in standby server.

4. Check the content of recovery.conf file.

Verify the recovery.conf file and add parameters if anything is missing.

cat /postdata/edbdata/recovery.conf

[enterprisedb@SCPrLBTB77 edbdata]$ cat recovery.conf

standby_mode = 'on'
primary_conninfo = 'user=enterprisedb password=edb#pgadmindb host=10.20.30.40
port=5444 sslmode=prefer sslcompression=0 krbsrvname=postgres
target_session_attrs=any'
promote_trigger_file='/postdata/edbdata/trigg.file'
recovery_target_timeline=’latest’

4. Update the pg_hba.conf file.

Add host entries for primary server(10.20.30.40) in standby sever(10.20.30.41)
pg_hba.conf file:

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only

local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
host all all 10.20.30.40/32 md5
# IPv6 local connections:
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 ident
host replication all ::1/128 ident
host replication all 10.20.30.40/32 md5

5. Start the postgres services in standby server.
systemctl stop edb-as-11
systemctl start edb-as-11

6. Verify the streaming replication:

-- Run below queries on standby db server:

postgres=# select pg_is_wal_replay_paused();
pg_is_wal_replay_paused
-------------------------
f

Note - f means, recovery is running fine. t means it is stopped.

postgres=# select pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(),

pg_last_xact_replay_timestamp();
-[ RECORD 1 ]-----------------+---------------------------------
pg_last_wal_receive_lsn | 0/401F658
pg_last_wal_replay_lsn | 0/401F658
pg_last_xact_replay_timestamp | 27-MAY-21 16:26:18.704299 +03:00

postgres=# select * from pg_stat_wal_receiver;

-[ RECORD 1 ]---------
+------------------------------------------------------------------------
pid | 7933
status | streaming
receive_start_lsn | 0/4000000
receive_start_tli | 1
received_lsn | 0/401F658
received_tli | 1
last_msg_send_time. | 27-MAY-21 20:29:39.599389 +03:00
last_msg_receipt_time | 27-MAY-21 20:29:39.599599 +03:00
latest_end_lsn. | 0/401F658
latest_end_time | 27-MAY-21 16:31:20.815183 +03:00
slot_name |
sender_host | 10.20.30.40
sender_port | 5444
conninfo | user=enterprisedb passfile=/postdata/enterprisedb/.pgpass
dbname=replication host=10.20.30.40 port=5444
fallback_application_name=walreceiver sslmode=prefer sslcompression=0
krbsrvname=postgres target_session_attrs=any

Our replication setup has been completed.

In the next article , we will explain how to setup EDB failover manager.

EDB Failover Manager (EFM) For Managing Streaming Replication

EDB failover manager, popularly known as EFM is a component of EDB postgres platform, is
used for handling switchover and failover (including auto failover) in postgres cluster having
streaming replication.

EFM cluster consists of below

1. One active primary server ( also master node), where transactions are happening
2. One or more standby database, which are synced with primary through streaming replication.
3. Witness server helps efm cluster in decision making , during master or standby failover
scenario.
However if you have more than one standby node, then witness server is not required.

NOTE – > The default name of efm cluster is efm

MORE INFORMATION ABOUT EFM ON EDB WEBSITE:

https://www.enterprisedb.com/products/postgresql-automatic-failover-manager-cluster-high-
availability

PRE-REQUISITES:
Streaming replication is already running fine between primary and standby server (Find steps
here)
Witness server has connectivity with both primary and standby server.

ENVIRONMENT DETAILS:

MASTER DB SERVER( MASTER) 10.20.30.40
STANDBY DB SERVER (SLAVE) 10.20.30.41
WITNESS SERVER 10.20.30.42

1. Update pg_hba.conf file on both primary and standby server

Add host entries for primary and standby server in pg_hba.conf file of both servers and reload
the config.

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only

local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
host all all 10.20.30.40/32 md5
host all all 10.20.30.41/32 md5
host all all 10.20.30.42/32 md5

postgres=# select pg_reload_conf();

pg_reload_conf
----------------
t
(1 row)

2. Installing and configuring EFM on primary node:

1. Install efm package:

yum install edb-efm39

2. Encrypt db user password:

here we are using the db user enterprisedb for efm configuration.Make sure
this user is a super user.

[enterprisedb@SCPrLBTB76 bin]$ /usr/edb/efm-3.9/bin/efm encrypt efm

This utility will generate an encrypted password for you to place in your
EFM cluster property file: /etc/edb/efm-3.9/efm.properties

Please enter the password and hit enter: --- Here put password of enterprisedb
user

Please enter the password again to confirm:

The encrypted password is: 923dfba06cc8258dfd9c01de499e27ed

Please paste this into your efm.properties file

db.password.encrypted=923dfba06cc8258dfd9c01de499e27ed

efm.properties file:
3. Copy and provide permission to property files:
root# cd /etc/edb/efm-3.9
root# cp efm.properties.in efm.properties
root# cp efm.nodes.in efm.nodes
root# chmod 666 efm.properties
root# chmod 666 efm.nodes

4. Update the efm.properties file:

db.user=enterprisedb
db.password.encrypted=923dfba06cc8258dfd9c01de499e27ed
db.port=5432
db.database=edb
db.service.owner=enterprisedb
db.bin=/usr/edb/as11/bin
db.data.dir=/pgdata/edbdata
db.config.dir=/pgdata/edbdata
[email protected]
bind.address=10.20.30.40:7800
admin.port=8001
is.witness=false
ping.server.ip=10.20.30.50 -- > all the nodes should be able to ping this
server.
efm.loglevel=TRACE

NOTE – > ping.server.ip can be any ip , which has connectivity to all ips. If you have
connectivity to internet, then leave it to default value.
For more information on properties file
https://www.enterprisedb.com/edb-docs/d/edb-postgres-failover-manager/user-guides/user-
guide/3.7/cluster_properties.html

5. Start efm service:
systemctl start edb-efm-3.9

6. Add standby and witness node ip in allow-node list:

/usr/edb/efm-3.9/bin/efm allow-node efm 10.20.30.41
/usr/edb/efm-3.9/bin/efm allow-node efm 10.20.30.42

7. Check efm status:

[root@dbhost40 efm-3.9]# /usr/edb/efm-3.9/bin/efm cluster-status efm
Cluster Status: efm

Agent Type Address Agent DB VIP

-----------------------------------------------------------------------
Master 10.20.30.40 UP UP

Allowed node host list:

10.20.30.40

Membership coordinator: 10.20.30.40

Standby priority host list:

(List is empty.)

Promote Status:

DB Type Address WAL Received LSN WAL Replayed LSN

Info

---------------------------------------------------------------------------
Master 10.20.30.40 0/F001AB8

No standby databases were found.

3. Installing and configuring EFM on standby node:

1. Install efm package:
yum install edb-efm39

2. Copy and provide permission to property files:

root# cd /etc/edb/efm-3.9
root# cp efm.properties.in efm.properties
root# cp efm.nodes.in efm.nodes
root# chmod 666 efm.properties
root# chmod 666 efm.nodes

3. Update the efm.properties file:

Either you can update the efm.properties file directly , or copy the file from
master node.

The only change needed is the bind.address. rest are same as master node.
db.user=enterprisedb
db.password.encrypted=923dfba06cc8258dfd9c01de499e27ed
db.port=5432
db.database=edb
db.service.owner=enterprisedb
db.bin=/usr/edb/as11/bin
db.data.dir=/pgdata/edbdata
db.config.dir=/pgdata/edbdata
[email protected]
bind.address=10.20.30.41:7800
admin.port=8001
is.witness=false
ping.server.ip=10.20.30.50
efm.loglevel=TRACE

4. Update the efm.nodes file :

Add the primary node ip and witness node ip:

[root@dbhost41 efm-3.9]# cat efm.nodes

# List of node address:port combinations separated by whitespace.
# The list should include at least the membership coordinator's address.

10.20.30.40:7800 10.20.30.42:7800
5. Start efm service:
systemctl start edb-efm-3.9

6. Check the cluster status:

[root@dbhost41 ~]$ /usr/edb/efm-3.9/bin/efm cluster-status efm
Cluster Status: efm

Agent Type Address Agent DB VIP

-----------------------------------------------------------------------
Master 10.20.30.40 UP UP
Standby 10.20.30.41 UP UP

Allowed node host list:

10.20.30.40 10.20.30.41

Membership coordinator: 10.20.30.40

Standby priority host list:

10.20.30.41

Promote Status:
DB Type Address WAL Received LSN WAL Replayed LSN
Info

---------------------------------------------------------------------------
Master 10.20.30.40 0/F001AB8
Standby 10.20.30.41 0/F000000. 0/F001AB8

Standby database(s) in sync with master. It is safe to promote.

4. Installing and configuring EFM on witness node:

1. Install efm package:
yum install edb-efm39

2. Copy and provide permission to property files.

root# cd /etc/edb/efm-3.9
root# cp efm.properties.in efm.properties
root# cp efm.nodes.in efm.nodes
root# chmod 666 efm.properties
root# chmod 666 efm.nodes

3. Update efm.properties file:

Either you can update the efm.properties file directly , or copy the file from
master node.
The only change needed is the bind.address and is.witness =true. Rest are same
as master node.

db.user=enterprisedb
db.password.encrypted=923dfba06cc8258dfd9c01de499e27ed
db.port=5432
db.database=edb
db.service.owner=enterprisedb
db.bin=/usr/edb/as11/bin
db.data.dir=/pgdata/edbdata
db.config.dir=/pgdata/edbdata
[email protected]
bind.address=10.20.30.42:7800
admin.port=8001
is.witness=true
ping.server.ip=10.21.172.21
efm.loglevel=TRACE

4. Add primary node and standby node ips :

[root@dbhost42 efm-3.9]# cat efm.nodes
# List of node address:port combinations separated by whitespace.
# The list should include at least the membership coordinator's address.
10.20.30.40:7800 10.20.30.41:7800

5. Start efm services:

systemctl start edb-efm-3.9

6. Check cluster status:

[root@dbhost42 ~]$ /usr/edb/efm-3.9/bin/efm cluster-status efm
Cluster Status: efm

Agent Type Address Agent DB VIP

-----------------------------------------------------------------------
Master 10.20.30.40 UP UP
Standby 10.20.30.41 UP UP
Witness 10.20.30.42 UP N/A

Allowed node host list:

10.20.30.40 10.20.30.41 10.20.30.42

Membership coordinator: 10.20.30.40

Standby priority host list:

10.20.30.41

Promote Status:
DB Type Address WAL Received LSN WAL Replayed LSN
Info

---------------------------------------------------------------------------
Master 10.20.30.40 0/F001AB8
Standby 10.20.30.41 0/F000000. 0/F001AB8

Standby database(s) in sync with master. It is safe to promote.

With this our EFM setup completed . In the next article, we will explain different switchover and
failover scenarios.

Monitor Sql Queries In Postgres Using Pg_stat_statements

PG_STAT_STATEMENTS module is useful in monitoring and tracking sql queries in postgres.
By default this module , this option is disabled. We need to enable this feature explicitly.
In this article, we will explain how to enable pg_stat_statements module and how to use it.

1. Check whether pg_stat_statement module is present or not:

postgres=# \d pg_stat_statements

Did not find any relation named "pg_stat_statements".

-- List down the extensions

pg_stat_statements extension is not present.

2. Create the extension:

postgres=# create extension pg_stat_statements;
CREATE EXTENSION

3. Update parameters in postgres.conf file:

Add pg_stat_statements to shared_preload_libraries parameter;

vi postgres.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max=20000
pg_stat_statements.track= top
If shared_preload_libraries parameter has already some value defined, , then
just append the value in postgres.conf file

shared_preload_libraries = '$libdir/dbms_pipe,$libdir/edb_gen,$libdir/dbms_aq,
$libdir/pg_stat_statements'

pg_stat_statements.max – sets the max number of statements PostgreSQL will

keep track of. The Default is 5000.
pg_stat_statements.track – Default is top.
all – Tracks all statements, including those inside function calls
top – Tracks only statements issued by clients
none – disable collection

If shared_preload_libraries parameter not defined properly, then below error will come.
postgres=# select * from pg_stat_statements ;
ERROR: pg_stat_statements must be loaded via shared_preload_libraries

5. Restart the postgres cluster service:
systemctl stop edb-as-11
systemctl start edb-as-11

6. Check whether pg_stat_statements is available or not

postgres=# show shared_preload_libraries;
shared_preload_libraries
------------------------------------------------------------------------------
$libdir/dbms_pipe,$libdir/edb_gen,$libdir/dbms_aq,$libdir/pg_stat_statements
(1 row)

postgres=# select count(*) from pg_stat_statements;

count
-------
1
(1 row)

7. Monitoring using pg_stat_statements:

postgres# SELECT substring(query, 1, 50) AS query,
round(total_time::numeric, 2) AS total_time,
calls,
round(mean_time::numeric, 2) AS mean,
round((100 * total_time /
sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

query. | total_time | calls |

mean | percentage_cpu
----------------------------------------------------+------------+-------
+------+----------------
select * from pg_stat_statements | 0.85 | 1 |
0.85 | 38.77
SELECT e.extname AS "Name", e.extversion AS "Versi | 0.55 | 1 |
0.55 | 24.95
WITH released_messages AS ( UPDATE sys.callback_qu | 0.17 | 2 |
0.08 | 7.57
WITH released_messages AS ( UPDATE sys.callback_qu | 0.16 | 2 |
0.08 | 7.11
show shared_preload_libraries | 0.10 | 1 |
0.10 | 4.56
WITH released_messages AS ( UPDATE sys.callback_qu | 0.09 | 2 |
0.04 | 3.87
WITH messages AS ( SELECT msgid FROM sys.callbac | 0.04 | 2 |
0.02 | 1.71
DELETE FROM sys.callback_queue_table qt WHERE qt. | 0.04 | 2 |
0.02 | 1.60
SELECT MIN(qt.next_event_time) FROM sys.callback | 0.04 | 2 |
0.02 | 1.67

SQL queries having high i/o activity

postgres# select userid::regrole, dbid, query,queryid,mean_time/1000 as
mean_time_seconds
from pg_stat_statements
order by (blk_read_time+blk_write_time) desc
limit 10;

Top time consuming queries

postgres# select userid::regrole, dbid, query ,calls, total_time/1000 as
total_time_seconds ,min_time/1000 as min_time_seconds,max_time/1000 as
max_time_seconds,mean_time/1000 as mean_time_seconds
from pg_stat_statements
order by mean_time desc
limit 10;

limit 10;
-[ RECORD 1 ]------
+-----------------------------------------------------------------------------
------------------------------------------------------------------------------
--
----------------------
userid | enterprisedb
dbid | 15846
query | insert into test select * from test
calls | 9
total_time_seconds | 2.722928186
min_time_seconds | 0.003885998
max_time_seconds | 1.395848226
mean_time_seconds | 0.302547576222222
-[ RECORD 2 ]------
+-----------------------------------------------------------------------------
------------------------------------------------------------------------------
--
----------------------
userid | enterprisedb
dbid | 15846
query | insert into test select * from pg_tables
calls | 5
total_time_seconds | 0.003757356
min_time_seconds | 0.00065117
max_time_seconds | 0.001032883
mean_time_seconds | 0.0007514712
-[ RECORD 3 ]------
+-----------------------------------------------------------------------------
------------------------------------------------------------------------------
--
----------------------

Queries with high memory usage:

postgres=# select userid::regrole, dbid, queryid,query from
pg_stat_statements
order by (shared_blks_hit+shared_blks_dirtied) desc limit 10;
8. Flush data from pg_stat_statements:
pg_stat_statements_reset() function will reset data from pg_stat_statements
table.

To Flush all data from pg_stat_statements:

postgres=# select count(*) from pg_stat_statements;
count
-------
22
(1 row)

postgres=# SELECT pg_stat_statements_reset();

pg_stat_statements_reset
--------------------------
(1 row)

postgres=# select count(*) from pg_stat_statements;

count
-------
1
(1 row)

To flush data of a particular database:( feature available from postgres 12 onwards only)
-- Find the dbid for the database
postgres=# select pg_stat_statements.dbid,datname,count(*) from
pg_stat_statements join
pg_database on pg_stat_statements.dbid=pg_database.oid group by
pg_stat_statements.dbid,datname;
dbid | datname | count
-------+----------+-------
15846 | edb | 3
15845 | postgres | 18
(2 rows)

postgres# select pg_stat_statements_reset(0, 15845, 0);

pg_stat_statements_reset
--------------------------
(1 row)

To flush a particular query:( Available from postgres 12 onwards only)

-- Find the queryid
postgres=# select userid::regrole, dbid, queryid,query from
pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit
10;
limit 10;
-[ RECORD 1 ]------
+-----------------------------------------------------------------------------
------------------------------------------------------------------------------
--
----------------------
userid | enterprisedb
dbid | 15846
queryid | 123573657
query | insert into test select * from test
-[ RECORD 2 ]------
+-----------------------------------------------------------------------------
------------------------------------------------------------------------------
--
----------------------

postgres# select pg_stat_statements_reset(0, 0, 123573657);

pg_stat_statements_reset
--------------------------
(1 row)

More details regarding pg_stat_statements can be found here( CLICK LINK)

TROUBLESHOOTING:
ERROR: Permission Denied For Schema In Postgres
PROBLEM:
We have granted select privilege on one table of a schema to another user. Even after that the
user was getting permission denied error while selecting data.

— Privilege was granted like this

PRIMDB=# grant all privileges on table SCOTT.SERVER_LOAD_INFO to prim_user;
GRANT

Now connect to prim_user and run the select statement:

psql -d PRIMDB -U prim_user

PRIMDB=> select * from SCOTT.SERVER_LOAD_INFO;

ERROR: permission denied for schema SCOTT
LINE 1: select * from SCOTT.SERVER_LOAD_INFO;

SOLUTION:
We need to provide usage privilege on that schema to other user also.
As per postgres note:
By default, users cannot access any objects in schemas they do not own. To
allow that, the owner of the schema must grant the USAGE privilege on the
schema.
NSMD2SIT=# grant usage on schema SCOTT to prim_user;
-- Now run the select statment:

Psql: Error: Could Not Connect To Server: FATAL: No Pg_hba.Conf Entry For Host

PROBLEM:
While connecting to a remote database with psql got below error.
-bash-4.2$hostname -i
192.168.2.3
-bash-4.2$ psql –host 192.268.8.0 -p 5444 -d postgres
psql: error: could not connect to server: FATAL: no pg_hba.conf entry for host
“192.168.2.3”, user “enterprisedb”, database “postgres”, SSL off

SOLUTION:
In the above scenario, i was trying to connect to a database on remote host 192.268.8.0 from the
local ip 192.168.2.3.

So we need to give authentication for the local ip inside pg_hba.conf file of remote server.

login to the remote server 192.268.8.0 and update the pg_hba.conf file:
pg_hba.conf can be found inside PGDATA directory.

vi pg_hba.conf

# "local" is for Unix domain socket connections only

local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
host all all 192.168.2.3/32 trust
# IPv6 local connections:

Now reload the config:

-bash-4.2$ psql -d postgres
psql (12.3.4)
Type "help" for help.

postgres=# SELECT pg_reload_conf();

pg_reload_conf
----------------
t
(1 row)

postgres=# select * from pg_hba_file_rules;

line_number | type | database | user_name | address |
netmask | auth_method | options | error
-------------+-------+---------------+-----------+--------------
+-----------------------------------------+-------------+---------+-------
80 | local | {all} | {all} | |
| peer | |
82 | host | {all} | {all} | 127.0.0.1 |
255.255.255.255 | ident | |
83 | host | {all} | {all} | 192.168.2.3 |
255.255.255.255 | trust | |
85 | host | {all} | {all} | ::1 |
ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | ident | |
88 | local | {replication} | {all} | |
| peer | |
89 | host | {replication} | {all} | 127.0.0.1 |
255.255.255.255 | ident | |
90 | host | {replication} | {all} | ::1 |
ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | ident | |
(7 rows)

Now test the connection:

-bash-4.2$ psql --host 192.268.8.0 -p 5444 -d postgres
psql (12.3.4)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "enterprisedb" on host
"192.268.8.0" at port "5444".
postgres=#

Trigger File Validation Failed. Could Not Start Agent As Standby

PROBLEM:
I have received below error , while starting efm agent on standby server.

[root@dbhost41 edbdata]# systemctl start edb-efm-3.9

Job for edb-efm-3.9.service failed because the control process exited with
error code. See "systemctl status edb-efm-3.9.service" and "journalctl -xe"
for details.

cat /var/log/efm-3.9/startup-efm.log
2021-06-15 14:58:06 Trigger file validation failed. Could not start agent as
standby. See logs for more details.

SOLUTION:
1. Check the trigger_file parameter value.
postgres=# \x
Expanded display is on.

postgres=# select * from pg_settings where name='promote_trigger_file';

-[ RECORD 1 ]---
+-------------------------------------------------------------------
name | promote_trigger_file
setting | --- >> It is blank, means no values is
set.
unit |
category | Replication / Standby Servers
short_desc | Specifies a file name whose presence ends recovery in the
standby.
extra_desc |
context | sighup
vartype | string
source | default
min_val |
max_val |
enumvals |
boot_val |
reset_val |
sourcefile |
sourceline |
pending_restart | f

Above output shows, promote_trigger_file parameter in not set in the config file.

2. Uncomment and update the promote_trigger_file parameter in postgres.conf file

vi postgres.conf

promote_trigger_file='/postgres/edbdata/trigger5444'

3. Reload the configuration:

postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)

Now we can see the value is update, lets restart the efm.

4. Start efm and check status:

[root@dbhost41 edbdata]# systemctl start edb-efm-3.9

[root@dbhost41 ~]# systemctl status edb-efm-3.9

● edb-efm-3.9.service - EnterpriseDB Failover Manager 3.9
Loaded: loaded (/usr/lib/systemd/system/edb-efm-3.9.service; disabled;
vendor preset: disabled)
Active: active (running) since Tue 2021-06-15 15:08:20 +03; 3h 20min ago
Process: 1660 ExecStart=/bin/bash -c /usr/edb/efm-3.9/bin/runefm.sh start $
{CLUSTER} (code=exited, status=0/SUCCESS)
Main PID: 1740 (java)
Tasks: 27
CGroup: /system.slice/edb-efm-3.9.service
└─1740 /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.292.b10-
1.el7_9.x86_64/jre/bin/java -cp /usr/edb/efm-3.9/lib/EFM-3.9.jar -Xmx128m
com.enterprisedb.efm.main.ServiceCom...

Jun 15 15:08:16 dbhost41 systemd[1]: Starting EnterpriseDB Failover Manager

3.9...
Jun 15 15:08:17 dbhost41 sudo[1757]: efm : TTY=unknown ; PWD=/ ;
USER=root ; COMMAND=/usr/edb/efm-3.9/bin/efm_root_functions validatedbowner
efm
Jun 15 15:08:17 dbhost41 sudo[1777]: efm : TTY=unknown ; PWD=/ ;
USER=enterprisedb ; COMMAND=/usr/edb/efm-3.9/bin/efm_db_functions
validaterecoveryconf efm
Jun 15 15:08:17 dbhost41 sudo[1795]: efm : TTY=unknown ; PWD=/ ;
USER=enterprisedb ; COMMAND=/usr/edb/efm-3.9/bin/efm_db_functions
validatedbconf efm
Jun 15 15:08:17 dbhost41 sudo[1813]: efm : TTY=unknown ; PWD=/ ;
USER=enterprisedb ; COMMAND=/usr/edb/efm-3.9/bin/efm_db_functions
validatepgbin efm
Jun 15 15:08:17 dbhost41 sudo[1849]: efm : TTY=unknown ; PWD=/ ;
USER=root ; COMMAND=/usr/edb/efm-3.9/bin/efm_root_functions dbservicestatus
efm
Jun 15 15:08:17 dbhost41 sudo[1873]: efm : TTY=unknown ; PWD=/ ;
USER=enterprisedb ; COMMAND=/usr/edb/efm-3.9/bin/efm_db_functions
validatepromotetriggerfil...rigger_file
Jun 15 15:08:20 dbhost41 systemd[1]: Started EnterpriseDB Failover Manager
3.9.
Hint: Some lines were ellipsized, use -l to show in full.
[root@dbhost88 ~]#

[root@dbhost41 ~]# /usr/edb/efm-3.9/bin/efm cluster-status efm

Cluster Status: efm

Agent Type Address Agent DB VIP

-----------------------------------------------------------------------
Master 10.20.30.40 UP UP
Standby 10.20.30.41 UP UP

Allowed node host list:

10.20.30.40 10.20.30.41

Membership coordinator: 10.20.30.40

Standby priority host list:

10.20.30.41

Promote Status:

DB Type Address WAL Received LSN WAL Replayed LSN

Info

---------------------------------------------------------------------------
Master 10.20.30.40 0/70001C0
Standby 10.20.30.41 0/7000000 0/70001C0

Standby database(s) in sync with master. It is safe to promote.

Edb Efm Start Failed With Authentication Failed Error On Standby

PROBLEM:
We have rebooted both the primary and standby postgres nodes. After the reboot , the enterprise
failover manager(EFM) , was not getting started on the standby node.
PRIMARY NODE – 10.20.30.40
STANDBY NODE – 10.20.30.41

[root@STANDBY efm-3.9]# systemctl start edb-efm-3.9

Job for edb-efm-3.9.service failed because the control process exited with
error code. See "systemctl status edb-efm-3.9.service" and "journalctl -xe"
for details.

-- Check the log

cat /var/log/efm-3.9/efm.log

at com.enterprisedb.efm.nodes.EfmAgent.run(EfmAgent.java:211)
at
com.enterprisedb.efm.main.ServiceCommand.main(ServiceCommand.java:111)
2021-06-28 11:14:10 com.enterprisedb.efm.nodes.EfmAgent run ERROR: Exception
starting service
java.lang.SecurityException: authentication failed

SOLUTION:
Check the cluster status on primary node:
[root@PRIMARY ~]# /usr/edb/efm-3.9/bin/efm cluster-status efm
Cluster Status: efm

Agent Type Address Agent DB VIP

-----------------------------------------------------------------------
Master 10.20.30.40 UP UP

Allowed node host list:

10.20.30.40

Membership coordinator: 10.20.30.40

Standby priority host list:

(List is empty.)

Promote Status:

DB Type Address WAL Received LSN WAL Replayed LSN

Info

---------------------------------------------------------------------------
Master 10.20.30.40 3/80000D0

No standby databases were found.

Here, we don’t see the entry for standby server. So let’s add that in allow
node list.

Run allow-node on primary

[root@PRIMARY ~]# /usr/edb/efm-3.4/bin/efm allow-node efm 10.20.30.41

Start the efm service on standby server.

[root@STANDBY efm-3.9]# systemctl start edb-efm-3.9

It succeeded this time. Check the cluster status again.

Cluster status:
[root@STANDBY ~]# /usr/edb/efm-3.9/bin/efm cluster-status efm
Cluster Status: efm

Agent Type Address Agent DB VIP

-----------------------------------------------------------------------
Master 10.20.30.40 UP UP
Standby 10.20.30.41 UP UP

Allowed node host list:

10.20.30.40 10.20.30.41

Membership coordinator: 10.20.30.40

Standby priority host list:

10.20.30.41

Promote Status:

DB Type Address WAL Received LSN WAL Replayed LSN

Info

---------------------------------------------------------------------------
Master 10.20.30.40 3/80001B0
Standby 10.20.30.41 3/80001B0 3/80001B0

Standby database(s) in sync with master. It is safe to promote.

POSTGRES ARTICLES - DBACLASS DBACLASS

Automate Oracle Client Installation Using Ansible
We can use the power of ansible to automate the oracle client installation task on multiple
servers.In this article, we will explain how we will achieve this by executing an ansible playbook
from ansible control node.

Ansible control node is any machine where ansible tool is installed.

IMPORTANT POINTS:
Oracle 19c client will be installed on all nodes.
Make sure the servers where client need to be installed has connectivity from ansible control
server

Below are the steps:

1. Copy the oracle 19c client software (zip file) to ansible control server.
[ansible_node] ls -ltr /oracle/LINUX.X64_193000_client.zip

2. Prepare the inventory file:

The list of servers where oracle 19c client will be installed.
[ansiblel_node] $ cat /home/ansible/ansible-scipts/host_inv
[appserver]
linux_host22 ansible_host=10.20.86.60 ansible_connection=ssh ansible_user=root
ansible_ssh_pass=dbaclass@123
linux_host29 ansible_host=10.20.86.61 ansible_connection=ssh ansible_user=root
ansible_ssh_pass=dbaclass@123
linux_host34 ansible_host=10.20.86.62 ansible_connection=ssh ansible_user=root
ansible_ssh_pass=dbaclass@123

3. Prepare the client installation response file:

[ansible_node] $ cat /home/ansible/oracle_client_19c.rsp

#-----------------------------------------------------------------------------
--
# Do not change the following system generated value.
#-----------------------------------------------------------------------------
--
oracle.install.responseFileVersion=/oracle/install/
rspfmt_clientinstall_response_schema_v19.0.0

#-----------------------------------------------------------------------------
--
# Unix group to be set for the inventory directory.
#-----------------------------------------------------------------------------
--
UNIX_GROUP_NAME=oinstall
#-----------------------------------------------------------------------------
--
# Inventory location.
#-----------------------------------------------------------------------------
--
INVENTORY_LOCATION=/oracle/app/oraInventory
#-----------------------------------------------------------------------------
--
# Complete path of the Oracle Home
#-----------------------------------------------------------------------------
--
ORACLE_HOME=/oracle/app/oracle/product/19c/client_1
#-----------------------------------------------------------------------------
--
# Complete path of the Oracle Base.
#-----------------------------------------------------------------------------
--
ORACLE_BASE=/oracle/app/oracle
oracle.install.client.installType=Administrator

#-----------------------------------------------------------------------------
--

4. Prepare the ansible playbook:

[ansible_node] $ cat oracle_client_installation.yml
- hosts: appserver
strategy: free
user: bvunix
become: yes
become_method: su
become_user: oracle
tasks:
- name: check existance of mount point
command: mountpoint -q /oracle
register: volume_stat
failed_when: False
- name: Copy client software
unarchive: src=/oracle/LINUX.X64_193000_client.zip dest=/oracle/
- name: Copy response file
copy: src=/home/ansible/oracle_client_19c.rsp dest=/oracle mode=0777
- name: Install Oracle Client
command: "/oracle/client/runInstaller -silent -showProgress -
ignorePrereq -ignoreSysPrereqs -waitforcompletion -responseFile
/oracle/oracle_client_19c.rsp"
register: client_runinstaller_output
failed_when: "'Successfully Setup Software' not in
client_runinstaller_output.stdout"

5. Execute the playbook:

[ansible-node]$ ansible-playbook oracle_client_installation.yml -i
/home/ansible/ansible-scipts/host_inv

PLAY [linux_host22,linux_host29,linux_host34]
******************************************************************************
*****************************************************
Tuesday 06 July 2021 20:30:15 +0300 (0:00:00.048) 0:00:00.048
**********
Tuesday 06 July 2021 20:30:15 +0300 (0:00:00.011) 0:00:00.060
**********
Tuesday 06 July 2021 20:30:15 +0300 (0:00:00.010) 0:00:00.071
**********

TASK [Gathering Facts]

******************************************************************************
****************************************************************************
[WARNING]: Module remote_tmp /home/oracle/.ansible/tmp did not exist and was
created with a mode of 0700, this may cause issues when running as another
user. To avoid this,
create the remote_tmp dir with the correct permissions manually
ok: [linux_host34]
ok: [linux_host22]
Tuesday 06 July 2021 20:30:18 +0300 (0:00:02.977) 0:00:03.048
**********
Tuesday 06 July 2021 20:30:18 +0300 (0:00:00.043) 0:00:03.091
**********

TASK [check existance of mount point]

******************************************************************************
*************************************************************
changed: [linux_host34]
Tuesday 06 July 2021 20:30:19 +0300 (0:00:01.304) 0:00:04.397
**********
changed: [linux_host22]
Tuesday 06 July 2021 20:30:19 +0300 (0:00:00.088) 0:00:04.486
**********

TASK [Gathering Facts]

TASK [check existance of mount point]

******************************************************************************
*************************************************************
changed: [linux_host29]
Tuesday 06 July 2021 20:30:21 +0300 (0:00:01.329) 0:00:06.699
**********

TASK [Copy client software]

******************************************************************************
***********************************************************************
changed: [linux_host34]
Tuesday 06 July 2021 20:31:36 +0300 (0:01:14.560) 0:01:21.259
**********
changed: [linux_host22]
Tuesday 06 July 2021 20:31:38 +0300 (0:00:02.122) 0:01:23.382
**********

TASK [Copy response file]

******************************************************************************
*************************************************************************
changed: [linux_host34]
Tuesday 06 July 2021 20:31:40 +0300 (0:00:01.840) 0:01:25.222
**********
changed: [linux_host22]
Tuesday 06 July 2021 20:31:41 +0300 (0:00:01.072) 0:01:26.295
**********

TASK [Copy client software]

******************************************************************************
***********************************************************************
changed: [linux_host29]
Tuesday 06 July 2021 20:31:45 +0300 (0:00:04.660) 0:01:30.956
**********

TASK [Copy response file]

TASK [Install Oracle Client]

******************************************************************************
**********************************************************************
changed: [linux_host22]
changed: [linux_host34]
changed: [linux_host29]

PLAY RECAP
******************************************************************************
******************************************************************************
**********
linux_host22 : ok=5 changed=4 unreachable=0 failed=0
skipped=0 rescued=0 ignored=0
linux_host29 : ok=5 changed=4 unreachable=0 failed=0
skipped=0 rescued=0 ignored=0
linux_host34 : ok=5 changed=4 unreachable=0 failed=0
skipped=0 rescued=0 ignored=0

Tuesday 06 July 2021 20:33:33 +0300 (0:01:45.191) 0:03:18.935

**********
==============================================================================
=
Install Oracle Client
------------------------------------------------------------------------------
-------------------------------------------------------------------- 105.19s
Copy client software
------------------------------------------------------------------------------
---------------------------------------------------------------------- 74.56s
Gathering Facts
------------------------------------------------------------------------------
----------------------------------------------------------------------------
2.98s
Copy response file
------------------------------------------------------------------------------
-------------------------------------------------------------------------
2.79s
check existance of mount point
------------------------------------------------------------------------------
------------------------------------------------------------- 1.33s

We have successfully installed oracle client on all the mentioned servers.

Automate oracle client installation using ansible - DBACLASS DBACLASS

Performing Database Backups
No ratings yet
Performing Database Backups
20 pages
Rman Command Document
No ratings yet
Rman Command Document
22 pages
EDB Server 10.1.5 Installation Guide
No ratings yet
EDB Server 10.1.5 Installation Guide
15 pages
12c Rac Course Content
No ratings yet
12c Rac Course Content
4 pages
Postgresql Dba: Learn Basic Rdbms Terms and Concepts
No ratings yet
Postgresql Dba: Learn Basic Rdbms Terms and Concepts
7 pages
Automated Patching: Pivot From Manual: To Scalable With Oracle Database Lifecycle Management Pack
No ratings yet
Automated Patching: Pivot From Manual: To Scalable With Oracle Database Lifecycle Management Pack
35 pages
Oracle to SQL Server Data Sync Guide
No ratings yet
Oracle to SQL Server Data Sync Guide
25 pages
21C RAC to Non-RAC Standby Setup Guide
No ratings yet
21C RAC to Non-RAC Standby Setup Guide
8 pages
Oracle Transparent Data Encryption
No ratings yet
Oracle Transparent Data Encryption
9 pages
PostgreSQL Installation Guide
No ratings yet
PostgreSQL Installation Guide
55 pages
Oracle RAC Backup and Recovery Guide
No ratings yet
Oracle RAC Backup and Recovery Guide
10 pages
Steps To Implement Schema Level Oracle Streams
No ratings yet
Steps To Implement Schema Level Oracle Streams
8 pages
Data Guard Best Practices Guide
100% (1)
Data Guard Best Practices Guide
7 pages
Oracle Goldengate Concepts and Architecture
No ratings yet
Oracle Goldengate Concepts and Architecture
23 pages
DB2 Administration Guide
No ratings yet
DB2 Administration Guide
1,556 pages
Oracle Recovery Manager (RMAN)
No ratings yet
Oracle Recovery Manager (RMAN)
10 pages
Q04. How To Check The Datapump Import Jobs Are Running or Not ?
No ratings yet
Q04. How To Check The Datapump Import Jobs Are Running or Not ?
6 pages
Oracle GoldenGate Best Practices - Extract ASM Connection Methods v2-ID1390268.1
No ratings yet
Oracle GoldenGate Best Practices - Extract ASM Connection Methods v2-ID1390268.1
10 pages
Is LBA Mandatory For SCAN Listener in Oracle RAC?
100% (1)
Is LBA Mandatory For SCAN Listener in Oracle RAC?
8 pages
DB Refresh Screen Shots
100% (2)
DB Refresh Screen Shots
28 pages
Oracle 12c Audit Verification Guide
No ratings yet
Oracle 12c Audit Verification Guide
2 pages
Data Gard
No ratings yet
Data Gard
8 pages
RMAN Database Restore and Recovery Steps
No ratings yet
RMAN Database Restore and Recovery Steps
6 pages
Oracle Multitenant on Exadata
No ratings yet
Oracle Multitenant on Exadata
31 pages
Rman Restore and Recovery Scenarios
No ratings yet
Rman Restore and Recovery Scenarios
7 pages
Rman Recovery: Purpose of Complete Database Recovery
No ratings yet
Rman Recovery: Purpose of Complete Database Recovery
6 pages
About Oracle TDE (Transparent Data Encryption)
No ratings yet
About Oracle TDE (Transparent Data Encryption)
1 page
LATCH - Enq TX - Index Contention
No ratings yet
LATCH - Enq TX - Index Contention
2 pages
Db2 Cheat Sheet
No ratings yet
Db2 Cheat Sheet
6 pages
Business Continuity For EBS Using Oracle 11g Physical Standby DB (Oracle E-Business Suite Technology)
No ratings yet
Business Continuity For EBS Using Oracle 11g Physical Standby DB (Oracle E-Business Suite Technology)
7 pages
Migrate From Oracle To Postgresql With Azure: Webinar Series
100% (1)
Migrate From Oracle To Postgresql With Azure: Webinar Series
21 pages
RMAN Backup Guide for DBAs
100% (2)
RMAN Backup Guide for DBAs
37 pages
Undo Tablespace Management Guide
No ratings yet
Undo Tablespace Management Guide
8 pages
Document For Manually Upgrading Oracle Database 11.2.0.3 To 11.2.0.4
No ratings yet
Document For Manually Upgrading Oracle Database 11.2.0.3 To 11.2.0.4
35 pages
Encrypted RMAN Backup Tips
No ratings yet
Encrypted RMAN Backup Tips
4 pages
Oracle 12c DBA Guide for IT Professionals
100% (1)
Oracle 12c DBA Guide for IT Professionals
51 pages
Oracle Streams Step by Step
No ratings yet
Oracle Streams Step by Step
16 pages
DR Rac Build Referred Link:: Diskgroup
No ratings yet
DR Rac Build Referred Link:: Diskgroup
6 pages
Oracle 10g Database Cloning Guide
No ratings yet
Oracle 10g Database Cloning Guide
3 pages
Useful Netezza Queries and Tips
No ratings yet
Useful Netezza Queries and Tips
6 pages
Database Hang Oracle Solution
100% (1)
Database Hang Oracle Solution
15 pages
Oracle DBA Interview Questions
No ratings yet
Oracle DBA Interview Questions
16 pages
Exadata 21.2.2.0.0 Release and Update (33120692) (Doc ID 2781458.1) 2781458.1
No ratings yet
Exadata 21.2.2.0.0 Release and Update (33120692) (Doc ID 2781458.1) 2781458.1
2 pages
Quick Explanation of Undo Tablespace in Oracle
No ratings yet
Quick Explanation of Undo Tablespace in Oracle
4 pages
Complete Checklist For Manual Upgrades To 11gR2
No ratings yet
Complete Checklist For Manual Upgrades To 11gR2
15 pages
Defining Multiple Replicats To Increase GoldenGate Performance
No ratings yet
Defining Multiple Replicats To Increase GoldenGate Performance
6 pages
Opening Standby For Read - Write
No ratings yet
Opening Standby For Read - Write
7 pages
Configure HugePages for Oracle DB
No ratings yet
Configure HugePages for Oracle DB
5 pages
Oracle Database 19c - Data Guard Administration Workshop
No ratings yet
Oracle Database 19c - Data Guard Administration Workshop
5 pages
Complete Checklist For Manual Upgrades To 11gR2
No ratings yet
Complete Checklist For Manual Upgrades To 11gR2
23 pages
Oracle Data Guard Features Guide
100% (1)
Oracle Data Guard Features Guide
54 pages
PostgreSQL Tutorial
100% (1)
PostgreSQL Tutorial
13 pages
Advanced SQL Injections - @CyberFreeCourses
No ratings yet
Advanced SQL Injections - @CyberFreeCourses
57 pages
DAY - 10 Topic: Postgres Installation: Postgres Database Administrator Course BY: Mukesh Chaurasia
No ratings yet
DAY - 10 Topic: Postgres Installation: Postgres Database Administrator Course BY: Mukesh Chaurasia
28 pages
Databasecommandinstal
No ratings yet
Databasecommandinstal
7 pages
Install PostgreSQL on RHEL/Fedora Guide
No ratings yet
Install PostgreSQL on RHEL/Fedora Guide
7 pages
PostgreSQL RPM Installation PGDG
No ratings yet
PostgreSQL RPM Installation PGDG
7 pages
1 Introduction2PostgreSQL
No ratings yet
1 Introduction2PostgreSQL
27 pages
Bersania - Postgresql Installation On Ubuntu - BW
No ratings yet
Bersania - Postgresql Installation On Ubuntu - BW
3 pages
Install PostgreSQL & pgAdmin on Win 10
No ratings yet
Install PostgreSQL & pgAdmin on Win 10
4 pages
Microsoft SQL Licensing Explained
No ratings yet
Microsoft SQL Licensing Explained
11 pages
Postgres DBA Interview Questions
100% (2)
Postgres DBA Interview Questions
13 pages
Oracle 10g Data Guard Setup Guide
No ratings yet
Oracle 10g Data Guard Setup Guide
3 pages
Thinus Presentation 1 1226310290044894 9
No ratings yet
Thinus Presentation 1 1226310290044894 9
49 pages
Configuration of Oracle 10g Datagaurd-Pavan
No ratings yet
Configuration of Oracle 10g Datagaurd-Pavan
14 pages
Oracle Data Pump Export/Import Guide
No ratings yet
Oracle Data Pump Export/Import Guide
17 pages
Oracle Database Administration: Division of Continuing Education
No ratings yet
Oracle Database Administration: Division of Continuing Education
2 pages
Oracle 10g ASM Setup on RHEL Guide
No ratings yet
Oracle 10g ASM Setup on RHEL Guide
27 pages
Significance of Zul Hijjah's First Ten Days
No ratings yet
Significance of Zul Hijjah's First Ten Days
1 page
Database Performance Tuning Guide
No ratings yet
Database Performance Tuning Guide
6 pages
MAA WP 10gR2 ClientFailoverBestPractices
No ratings yet
MAA WP 10gR2 ClientFailoverBestPractices
30 pages
Estimate Size of Last RMAN Backup
No ratings yet
Estimate Size of Last RMAN Backup
1 page
MAA WP 10gR2 SwitchoverFailoverBestPractices PDF
No ratings yet
MAA WP 10gR2 SwitchoverFailoverBestPractices PDF
23 pages
Rockchip RK30xx TRM V2.0
No ratings yet
Rockchip RK30xx TRM V2.0
1,142 pages
Java Data Types and Values Explained
No ratings yet
Java Data Types and Values Explained
12 pages
Virtual Memory Statistics with vmstat
No ratings yet
Virtual Memory Statistics with vmstat
3 pages
How To Sell Hillstone 301. A Series NGFW v5.5r9
No ratings yet
How To Sell Hillstone 301. A Series NGFW v5.5r9
48 pages
Understanding Parallel Arrays in Java
No ratings yet
Understanding Parallel Arrays in Java
23 pages
Computer Science & Engineering: KCT College of Engg and Tech. Village Fatehgarh Distt - Sangrur
No ratings yet
Computer Science & Engineering: KCT College of Engg and Tech. Village Fatehgarh Distt - Sangrur
19 pages
Brinkmeyer 1980
No ratings yet
Brinkmeyer 1980
2 pages
BJT Common Emitter Amplifier Experiment
No ratings yet
BJT Common Emitter Amplifier Experiment
3 pages
EER28 Transformer Datasheet
No ratings yet
EER28 Transformer Datasheet
5 pages
Devourer Life
No ratings yet
Devourer Life
233 pages
Difference Between Vsphere, ESXi and Vcenter PDF
No ratings yet
Difference Between Vsphere, ESXi and Vcenter PDF
3 pages
Wonderware ® FactorySuite™ (InBatch Premier User's Guide)
100% (1)
Wonderware ® FactorySuite™ (InBatch Premier User's Guide)
710 pages
2023 Winter Question Paper (Msbte Study Resources)
No ratings yet
2023 Winter Question Paper (Msbte Study Resources)
4 pages
SNMP: Challenges in Network Management
No ratings yet
SNMP: Challenges in Network Management
4 pages
Naukri ManishTanwar (8y 0m)
No ratings yet
Naukri ManishTanwar (8y 0m)
2 pages
Zimbra Outgoing Mail Attachment Scanning
No ratings yet
Zimbra Outgoing Mail Attachment Scanning
2 pages
JavaScript Basics and Examples
No ratings yet
JavaScript Basics and Examples
29 pages
555 Timer: by D.V.Kamat, Faculty Department of E&C Engg., MIT
No ratings yet
555 Timer: by D.V.Kamat, Faculty Department of E&C Engg., MIT
21 pages
Parag Gulwani: Professional Summary
No ratings yet
Parag Gulwani: Professional Summary
4 pages
CSV Data Storage for Production Control
No ratings yet
CSV Data Storage for Production Control
3 pages
Inm C Userguide DataReportingProcedures
No ratings yet
Inm C Userguide DataReportingProcedures
31 pages
TCL TK Tutorial
No ratings yet
TCL TK Tutorial
30 pages
DXB2038 RUS 02 B3 - 3 Ericsson Faulty Report - 1
No ratings yet
DXB2038 RUS 02 B3 - 3 Ericsson Faulty Report - 1
1 page
C - Hello World
No ratings yet
C - Hello World
5 pages
IOT Assignment-2
No ratings yet
IOT Assignment-2
32 pages
DV456 Manual
100% (1)
DV456 Manual
99 pages
M0128a1d RMC1600
No ratings yet
M0128a1d RMC1600
35 pages
8051 Memory Organization
No ratings yet
8051 Memory Organization
7 pages
Module 1 Notes
No ratings yet
Module 1 Notes
7 pages
Important Questions BBA-109
No ratings yet
Important Questions BBA-109
1 page

Postgres Installation and Management Guide

Uploaded by

Postgres Installation and Management Guide

Uploaded by

POSTGRES ARTICLES

 How To Install Postgres Database On Mac Os/Linux

Now lets dive into postgres 🙂

How To Create A Database In Postgres

2. Create database with specific tablespace:

3. Create database with options like encoding, and template

— View database information from psql prompt:

How To Drop A Database In Postgres

Method 1: ( using drop database command).

postgres=# drop database "DBACLASS";;

application_name | client_hostname | pid | usename

postgres=# select pg_terminate_backend(pid) from pg_stat_activity where

postgres=# select application_name,client_hostname,pid,usename from

postgres=# drop database "DBACLASS";

Method 2: (using dropdb executable command)

postgres$ ./dropdb -e "DBACLASS"

Method 3: (Using pgadmin tool)

How To Upgrade Postgres To New Version (from 12 to 14)

1. First download and install the postgres 14

Total size: 919 k

Total size: 7.8 M

[root@dbahost14 pg_12]# yum install postgresql14-server-14.2-

[root@dbahost14 pg_12]# yum install postgresql14-contrib-14.2-

Total size: 2.4 M

The database cluster will be initialized with locale "en_US.UTF-8".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/pgsql/14/data ... ok

initdb: warning: enabling "trust" authentication for local connections

Success. You can now start the database server using:

./pg_ctl -D /var/lib/pgsql/14/data -l logfile start

3. Run the postgres upgrade with check option.

could not open log file "pg_upgrade_internal.log": Permission denied

*Clusters are compatible*

-- Alternatively you can mention --link option

-bash-4.2$ /usr/pgsql-14/bin/pg_upgrade -d /var/lib/pgsql/12/data -D

*Clusters are compatible*

-bash-4.2$ /usr/pgsql-12/bin/pg_ctl stop -D /var/lib/pgsql/12/data

-bash-4.2$ /usr/pgsql-14/bin/pg_upgrade -d /var/lib/pgsql/12/data -D

If pg_upgrade fails after this point, you must re-initdb the

Linking user relation files

5. Start the new postgres cluster.

How To Clone A Postgres Database To Remote Server

Source postgres details:

Remote postgres server details:

Here remote_database is an existing database in the remote postgres cluster, so that this

How To Enable And Disable Archive Mode In Postgres

STEPS TO ENABLE ARCHIVE MODE IN POSTGRES:

postgres=# show wal_level

2. Now alter below parameters:

postgres=# alter system set archive_mode=on;

postgres=# alter system set archive_command='test ! -f

postgres=# alter system set wal_level=replica;

3. Now restart the postgres instance:

-- ALTERNATIVELY YOU CAN USE PG_CTL COMMAND:

4. Now check archive mode:

postgres=# show wal_level;

5. Do manual log switch and check whether archive is generating or not:

postgres=# select pg_switch_wal();

[enterprisedb@localhost archive]$ pwd

-rw-------. 1 enterprisedb enterprisedb 16777216 Oct 20 19:44

STEPS TO DISABLE ARCHIVE MODE:

2. Now restart the postgres instance:

3. Check archive mode:

How To Move A Tablespace To New Directory In Postgres

1. Get the tablespace details and existing path:

oid | spcname | spcowner | spcacl

Currently the tablespace path is /old_fs/edb

2. Stop the postgres instance:

[rootedb]# service edb-as-12 status

INFO: [PID: 58076]

[rootedb]# service edb-as-12 stop

INFO: [PID: 58076]

MSG: [edb-as-12 stopped]

INFO: [Please see service script file /var/log/edb/as12/edb-as-12_script.log

3. Move the directoty to the new path:

4. Now Do the relinking:

Clusters are compatible

Clusters are compatible