ERROR: could not open extension control file "/usr/pgsql-9.5/share/extension/tablefunc.control": No such file or directory

Error:

[root@said extension]# su - postgres
Last login: Wed Dec 13 14:33:47 BDT 2017 on pts/1
-bash-4.2$ psql -U sors
psql (9.2.13, server 9.5.10)
WARNING: psql version 9.2, server version 9.5.
         Some psql features might not work.
Type "help" for help.

sors=# CREATE EXTENSION tablefunc;
ERROR:  could not open extension control file "/usr/pgsql-9.5/share/extension/tablefunc.control": No such file or directory
sors=# exit
sors-# \q
-bash-4.2$ exit

Reason: 

postgresql95-contrib package is not installed or wrong version installed

The tablefunc module includes various functions that return tables (that is, multiple rows). These functions are useful both in their own right and as examples of how to write C functions that return multiple rows.

Solution:

[root@said extension]# ls -lrt
total 12
-rw-r--r--. 1 root root 381 Nov  8 23:31 plpgsql--unpackaged--1.0.sql
-rw-r--r--. 1 root root 179 Nov  8 23:31 plpgsql.control
-rw-r--r--. 1 root root 332 Nov  8 23:31 plpgsql--1.0.sql
[root@said extension]# pwd


root@said yum.repos.d]# yum install postgresql-contrib
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Resolving Dependencies
--> Running transaction check
---> Package postgresql95-contrib.x86_64 0:9.5.10-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================
 Package                                              Arch                                   Version                                             Repository                              Size
==============================================================================================================================================================================================
Installing:
 postgresql95-contrib                                 x86_64                                 9.5.10-1PGDG.rhel7                                  pgdg95                                 533 k

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

Total download size: 533 k
Installed size: 1.8 M
Is this ok [y/d/N]: y
Downloading packages:
postgresql95-contrib-9.5.10-1PGDG.rhel7.x86_64.rpm                                                                                                                     | 533 kB  00:00:01   
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : postgresql95-contrib-9.5.10-1PGDG.rhel7.x86_64                                                                                                                             1/1
  Verifying  : postgresql95-contrib-9.5.10-1PGDG.rhel7.x86_64                                                                                                                             1/1

Installed:
  postgresql95-contrib.x86_64 0:9.5.10-1PGDG.rhel7                                                                                                                                           

Complete!



[root@said yum.repos.d]# cd /usr/pgsql-9.5/share/extension/
[root@said extension]# ls -lrt
total 932
-rw-r--r--. 1 root root   394 Nov  8 23:31 timetravel--unpackaged--1.0.sql
-rw-r--r--. 1 root root   156 Nov  8 23:31 timetravel.control
-rw-r--r--. 1 root root   486 Nov  8 23:31 timetravel--1.0.sql
-rw-r--r--. 1 root root   134 Nov  8 23:31 tcn.control
-rw-r--r--. 1 root root   274 Nov  8 23:31 tcn--1.0.sql
-rw-r--r--. 1 root root  1160 Nov  8 23:31 tablefunc--unpackaged--1.0.sql
-rw-r--r--. 1 root root   174 Nov  8 23:31 tablefunc.control
-rw-r--r--. 1 root root  2153 Nov  8 23:31 tablefunc--1.0.sql


[root@said extension]# su - postgres
Last login: Wed Dec 13 14:55:33 BDT 2017 on pts/1
-bash-4.2$ psql  -U sors sors -c "CREATE EXTENSION tablefunc"
CREATE EXTENSION
-bash-4.2$ pwd
/var/lib/pgsql

step by step to switch over Primary to Standby and switch back to Primary in PostgreSQL Database

Switchover/Switchback in PostgreSQL 9.5 Database

Step 1: Shutdown postgresql service of Primary Database

[root@pg_primary ~]# su - postgres
Last login: Mon Dec 11 05:44:38 EST 2017 on pts/0
-bash-4.2$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
 application_name |   state   | sync_priority | sync_state
------------------+-----------+---------------+------------
 walreceiver      | streaming |             1 | sync
(1 row)

-bash-4.2$ exit
logout
[root@pg_primary ~]# systemctl stop postgresql-9.5.service
[root@pg_primary ~]#

Step 2: Check for sync status and recovery status of Standby before promoting it:

[root@pg_standby data]# su - postgres
Last login: Mon Dec 11 05:42:01 EST 2017 on pts/1
-bash-4.2$ psql -c 'select pg_last_xlog_receive_location() "receive_location",pg_last_xlog_replay_location() "replay_location",pg_is_in_recovery() "recovery_status";'
 receive_location | replay_location | recovery_status
------------------+-----------------+-----------------
 0/40000098       | 0/40000098      | t
(1 row)

-bash-4.2$
-bash-4.2$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
 application_name | state | sync_priority | sync_state
------------------+-------+---------------+------------
(0 rows)

-bash-4.2$

Step 3: Open the Standby as new Primary by pg_ctl promote or creating a trigger file.

-bash-4.2$ grep trigger_file /var/lib/pgsql/9.5/data/recovery.conf
trigger_file = '/tmp/postgresql.trigger.5432'
-bash-4.2$ touch /tmp/postgresql.trigger.5432
-bash-4.2$ psql -c "select pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 f
(1 row)

-bash-4.2$

-bash-4.2$ tail -f /var/lib/pgsql/9.5/data/pg_log/postgresql-Mon.log
< 2017-12-11 06:29:54.114 EST >LOG:  redo done at 0/40000028
< 2017-12-11 06:29:54.115 EST >LOG:  last completed transaction was at log time 2017-12-11 05:55:43.441569-05
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/000000010000000000000040’: No such file or directory
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000002.history’: No such file or directory
< 2017-12-11 06:29:54.138 EST >LOG:  selected new timeline ID: 2
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000001.history’: No such file or directory
< 2017-12-11 06:29:54.492 EST >LOG:  archive recovery complete
< 2017-12-11 06:29:54.830 EST >LOG:  MultiXact member wraparound protections are now enabled
< 2017-12-11 06:29:54.911 EST >LOG:  database system is ready to accept connections
< 2017-12-11 06:29:54.936 EST >LOG:  autovacuum launcher started

---Now Standby become Primary Database----
[root@pg_standby data]# su - postgres
Last login: Mon Dec 11 05:42:01 EST 2017 on pts/1
-bash-4.2$ psql
psql (9.5.10)
Type "help" for help.

postgres=# select * from sebl;
 org_name | address
----------+---------
(0 rows)

postgres=# insert into sebl values('Southeast Bank Ltd.','Eunoos Center');
INSERT 0 1
postgres=#

Standby has been promoted as master and a new timeline followed which you can notice in logs

Note: In Primary (Old Standby) recovery.conf file become recovery.done after switchover

--===========************=========---------
Prepare New Standby for data sync with Primary
--===========************=========---------
Step 4. Restart old Primary as standby and allow to follow the new timeline by passing "recovery_target_timline='latest'" in $PGDATA/recovery.conf file.

-bash-4.2$ cat recovery.conf
recovery_target_timeline = 'latest'
standby_mode = 'on'
primary_conninfo = 'host=192.168.159.135 port=5432 user=repuser password=repuser@application_name=pgslave001'
restore_command = 'cp /var/lib/pgsql/9.5/main/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'
archive_cleanup_command = '/usr/pgsql-9.5/bin/pg_archivecleanup -d /var/lib/pgsql/9.5/main/archive %r  >> cleanup.log'
-bash-4.2$


Slave Server (old Primary) Configurations: postgresql.conf:

hot_standby = on






Step 5. Verify the new Standby status.

-bash-4.2$ psql -p 5432 -c "select pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 t
(1 row)

-bash-4.2$
-------------Testing------
1. Insert data in primary
2. Check the data in primary and standby
3. try to insert data in standby

--=====================================================--
--------=========Switchback Steps==========--------------
--=====================================================--

Step 1: Check the Sync Status in Primary

postgres=# insert into sebl values('Prime Bank Ltd','Motijill');
INSERT 0 1
postgres=# select application_name, state, sync_priority, sync_state from pg_stat_replication;
 application_name |   state   | sync_priority | sync_state
------------------+-----------+---------------+------------
 walreceiver      | streaming |             0 | async
(1 row)

postgres=#

Step 2: Shutdown postgresql service of Primary Database

[root@pg_standby data]# systemctl stop postgresql-9.5.service
[root@pg_standby data]#

Step 3: Check for sync status and recovery status of Standby before promoting it:

[root@pg_primary data]# su - postgres
Last login: Wed Dec 13 00:15:09 EST 2017 on pts/3
-bash-4.2$ psql -c 'select pg_last_xlog_receive_location() "receive_location",pg_last_xlog_replay_location() "replay_location",pg_is_in_recovery() "recovery_status";'
 receive_location | replay_location | recovery_status
------------------+-----------------+-----------------
 0/4A000098       | 0/4A000098      | t
(1 row)

-bash-4.2$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
 application_name | state | sync_priority | sync_state
------------------+-------+---------------+------------
(0 rows)

-bash-4.2$


Step 4: Open the Standby as new Primary by pg_ctl promote or creating a trigger file.

-bash-4.2$ grep trigger_file /var/lib/pgsql/9.5/data/recovery.conf
trigger_file = '/tmp/postgresql.trigger.5432'
-bash-4.2$ touch /tmp/postgresql.trigger.5432
-bash-4.2$ cat /tmp/postgresql.trigger.5432
cat: /tmp/postgresql.trigger.5432: No such file or directory
-bash-4.2$ psql -c "select pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 f
(1 row)

-bash-4.2$ cat /tmp/postgresql.trigger.5432
cat: /tmp/postgresql.trigger.5432: No such file or directory
-bash-4.2$

-----Check the log file of new Primary---

[root@pg_primary pg_log]# tail -f postgresql-Wed.log
< 2017-12-13 00:36:38.466 EST >LOG:  redo done at 0/4A000028
< 2017-12-13 00:36:38.466 EST >LOG:  last completed transaction was at log time 2017-12-13 00:30:26.653672-05
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000002000000000000004A’: No such file or directory
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000003.history’: No such file or directory
< 2017-12-13 00:36:38.488 EST >LOG:  selected new timeline ID: 3
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000002.history’: No such file or directory
< 2017-12-13 00:36:38.615 EST >LOG:  archive recovery complete
< 2017-12-13 00:36:38.621 EST >LOG:  MultiXact member wraparound protections are now enabled
< 2017-12-13 00:36:38.623 EST >LOG:  database system is ready to accept connections
< 2017-12-13 00:36:38.624 EST >LOG:  autovacuum launcher started

---Now Standby become Primary Database----

[root@pg_primary data]# su - postgres
Last login: Wed Dec 13 00:34:10 EST 2017 on pts/3
-bash-4.2$ psql
psql (9.5.10)
Type "help" for help.

postgres=# select * from sebl;
      org_name       |    address
---------------------+---------------
 Southeast Bank Ltd. | Eunoos Center
 Estern Bank Ltd.    | Gulshan
 Bangladesh Bank     | Motijill
 Prime Bank Ltd      | Motijill
(4 rows)

postgres=# insert into sebl values('City Bank Ltd','Gulshan');
^CCancel request sent
WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
INSERT 0 1
postgres=# select * from sebl;
      org_name       |    address
---------------------+---------------
 Southeast Bank Ltd. | Eunoos Center
 Estern Bank Ltd.    | Gulshan
 Bangladesh Bank     | Motijill
 Prime Bank Ltd      | Motijill
 City Bank Ltd       | Gulshan
(5 rows)

postgres=#

Standby has been promoted as master and a new timeline followed which you can notice in logs
Note: In Primary (Old Standby) recovery.conf file become recovery.done after switchover
--***************--
[root@pg_primary data]# ls -lrt reco*
-rw-r--r-- 1 postgres postgres 607 Dec 11 23:11 recovery.done
[root@pg_primary data]# pwd
/var/lib/pgsql/9.5/data
[root@pg_primary data]#

--===========************=========---------
Step 5: Prepare New Standby for data sync with Primary
--===========************=========---------

Restart old Primary as standby and allow to follow the new timeline by passing "recovery_target_timline='latest'" in $PGDATA/recovery.conf file.

-bash-4.2$ cat recovery.conf
recovery_target_timeline = 'latest'
standby_mode = 'on'
primary_conninfo = 'host=192.168.159.135 port=5432 user=repuser password=repuser@application_name=pgslave001'
restore_command = 'cp /var/lib/pgsql/9.5/main/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'
archive_cleanup_command = '/usr/pgsql-9.5/bin/pg_archivecleanup -d /var/lib/pgsql/9.5/main/archive %r  >> cleanup.log'
-bash-4.2$


Slave Server (old Primary) Configurations: postgresql.conf:

hot_standby = on

Step 6: Start Postgresql service in Standby

[root@pg_standby data]# systemctl start postgresql-9.5.service
[root@pg_standby data]#

---Check the log file of Standby and Primary---------

-------------Standby-----------
[root@pg_standby pg_log]# tail -f postgresql-Wed.log
< 2017-12-13 00:53:13.857 EST >LOG:  replication terminated by primary server
< 2017-12-13 00:53:13.857 EST >DETAIL:  End of WAL reached on timeline 2 at 0/4A000098.
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000003.history’: No such file or directory
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000004.history’: No such file or directory
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000003.history’: No such file or directory
< 2017-12-13 00:53:13.897 EST >LOG:  new target timeline is 3
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000003000000000000004A’: No such file or directory
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000002000000000000004A’: No such file or directory
< 2017-12-13 00:53:13.903 EST >LOG:  restarted WAL streaming at 0/4A000000 on timeline 3
< 2017-12-13 00:53:17.037 EST >LOG:  redo starts at 0/4A000098

------------Primary---------
[root@pg_primary pg_log]# tail -f postgresql-Wed.log
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000003.history’: No such file or directory
< 2017-12-13 00:36:38.488 EST >LOG:  selected new timeline ID: 3
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000002.history’: No such file or directory
< 2017-12-13 00:36:38.615 EST >LOG:  archive recovery complete
< 2017-12-13 00:36:38.621 EST >LOG:  MultiXact member wraparound protections are now enabled
< 2017-12-13 00:36:38.623 EST >LOG:  database system is ready to accept connections
< 2017-12-13 00:36:38.624 EST >LOG:  autovacuum launcher started
< 2017-12-13 00:40:43.387 EST >WARNING:  canceling wait for synchronous replication due to user request
< 2017-12-13 00:40:43.387 EST >DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
< 2017-12-13 00:53:13.298 EST >LOG:  standby "walreceiver" is now the synchronous standby with priority 1


Step 7. Verify the new Standby status.

[root@pg_standby data]# su - postgres
Last login: Wed Dec 13 00:30:07 EST 2017 on pts/1
-bash-4.2$ psql -p 5432 -c "select pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 t
(1 row)

-bash-4.2$

-------------Testing------
1. Insert data in primary
2. Check the data in primary and standby
3. try to insert data in standby

step by step to change replication status (sync or async) in postgresql database

If you don't want transactions to wait until a synchronous replica acknowledges a commit,
then you don't want synchronous replication. You want async replication and should simply
remove the synchronous_standby_names

Step 1: Check sync status in Primary Database

[root@pg_primary ~]# su - postgres
Last login: Sun Dec 10 22:45:00 EST 2017 on pts/0
-bash-4.2$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
 application_name |   state   | sync_priority | sync_state
------------------+-----------+---------------+------------
 walreceiver      | streaming |             1 | sync
(1 row)

-bash-4.2$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
 application_name |   state   | sync_priority | sync_state
------------------+-----------+---------------+------------
 walreceiver      | streaming |             1 | sync
(1 row)

Step 2: Modify the postgresql.conf file of Primary Database

-bash-4.2$
-bash-4.2$ vim /var/lib/pgsql/9.5/data/postgresql.conf
#synchronous_standby_names = '' # standby servers that provide sync rep
#synchronous_standby_names = 'pgslave001'
synchronous_standby_names = 'walreceiver'

--Comment the Values synchronous_standby_names--
#synchronous_standby_names = 'walreceiver'

Step 3: Restart the postgresql service

[root@pg_primary ~]# systemctl stop postgresql-9.5.service
[root@pg_primary ~]# systemctl start postgresql-9.5.service


Step 4: Check Database Sync Status

[root@pg_primary ~]# su - postgres
Last login: Mon Dec 11 05:35:05 EST 2017 on pts/0
-bash-4.2$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
 application_name |   state   | sync_priority | sync_state
------------------+-----------+---------------+------------
 walreceiver      | streaming |             0 | async
(1 row)

-bash-4.2$ psql -x -c "select * from pg_stat_replication;"
-[ RECORD 1 ]----+-----------------------------
pid              | 5923
usesysid         | 16393
usename          | repuser
application_name | walreceiver
client_addr      | 192.168.159.135
client_hostname  |
client_port      | 39369
backend_start    | 2017-12-11 05:39:48.74948-05
backend_xmin     |
state            | streaming
sent_location    | 0/3E0000D0
write_location   | 0/3E0000D0
flush_location   | 0/3E0000D0
replay_location  | 0/3E0000D0
sync_priority    | 0
sync_state       | async

-bash-4.2$

----Check With data Primary and Standby--------
----In Primary------
[root@pg_primary ~]# su - postgres
Last login: Mon Dec 11 05:35:05 EST 2017 on pts/0
-bash-4.2$ psql
psql (9.5.10)
Type "help" for help.

postgres=# insert into sebl values('NRB Bank Ltd','Gulshan');
INSERT 0 1
postgres=#

-----In Standby------
[root@pg_standby data]# su - postgres
Last login: Mon Dec 11 05:34:07 EST 2017 on pts/1
-bash-4.2$ psql
psql (9.5.10)
Type "help" for help.

postgres=# select * from sebl;
      org_name       |    address   
---------------------+---------------
 Southeast Bank Ltd. | Eunoos Center
 Estern Bank Ltd.    | Gulshan
 Bangladesh Bank     | Motijill
 Prime Bank Ltd      | Motijill
 City Bank Ltd       | Gulshan
 Brac Bank Ltd       | Gulshan
 Exim Bank Ltd       | Gulshan
 MTBL Bank Ltd       | Gulshan
 Meghna Bank Ltd     | Gulshan
 Uttara Bank Ltd     | Motijill
 UCB Bank Ltd        | Gulshan
 NRB Bank Ltd        | Gulshan
(12 rows)

postgres=#