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 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=#

Step by Step to move datafile location in PostgreSQL

Purpose: 
This procedure is tested in my lab. Before following this procedure please test it in your test environment

 
Step 1: Check current Installation location abd Data location

[root@pg_primary data]# ps -ef | grep post
postgres  2226     1  0 Dec06 ?        00:00:01 /usr/pgsql-9.5/bin/postgres -D /var/lib/pgsql/9.5/data
root      2295     1  0 Dec06 ?        00:00:00 /usr/libexec/postfix/master -w
postfix   2297  2295  0 Dec06 ?        00:00:00 qmgr -l -t unix -u
postgres  2302  2226  0 Dec06 ?        00:00:00 postgres: logger process 
postgres  2380  2226  0 Dec06 ?        00:00:00 postgres: checkpointer process 
postgres  2381  2226  0 Dec06 ?        00:00:01 postgres: writer process 
postgres  2382  2226  0 Dec06 ?        00:00:02 postgres: wal writer process 
postgres  2383  2226  0 Dec06 ?        00:00:00 postgres: autovacuum launcher process 
postgres  2384  2226  0 Dec06 ?        00:00:00 postgres: archiver process 
postgres  2385  2226  0 Dec06 ?        00:00:01 postgres: stats collector process 
root      3702  3503  0 Dec06 pts/0    00:00:00 tail -f postgresql-Wed.log
postgres  3802  2226  0 Dec06 ?        00:00:01 postgres: wal sender process repuser 192.168.159.134(57966) streaming 0/34494368
postfix  19003  2295  0 Dec06 ?        00:00:00 pickup -l -t unix -u
root     19631  3713  0 00:25 pts/1    00:00:00 grep --color=auto post
[root@pg_primary data]#
-----Or-----
[root@pg_primary ~]# su - postgres
Last login: Wed Dec  6 01:13:14 EST 2017 on pts/1
-bash-4.2$ psql
psql (9.5.10)
Type "help" for help.

postgres=# SHOW data_directory;
     data_directory     
-------------------------
 /var/lib/pgsql/9.5/data
(1 row)

postgres=#

Step 2: Create a directory where Postgresql will move

[root@pg_primary ~]# mkdir /postgresql
[root@pg_primary ~]# chown -R postgres:postgres /postgresql/
[root@pg_primary ~]#
[root@pg_primary ~]# chmod 700 /postgresql/

Step 3: Move PostgreSQL Installation file to new created directory

[root@pg_primary data]# rsync -av /var/lib/pgsql/ /postgresql/


9.5/main/archive/000000010000000000000036
9.5/main/archive/000000010000000000000037

sent 1491083782 bytes  received 54419 bytes  20015277.87 bytes/sec
total size is 1490755229  speedup is 1.00
[root@pg_primary data]#


Step 4) Stop the Postgresql service Take a backup of Original FILE
[root@pg_primary data]# systemctl stop postgresql-9.5.service
[root@pg_primary postgresql]# mv /var/lib/pgsql/ /var/lib/pgsql_backup
[root@pg_primary postgresql]#

Step 5) Modify the postgresql.conf file

[root@pg_primary data]# vim /postgresql/9.5/data/postgresql.conf

#data_directory = 'ConfigDir'           # use data in another directory
data_directory = '/postgresql/9.5/data/'
                                        # (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf'     # host-based authentication file
hba_file = '/postgresql/9.5/data/pg_hba.conf'
                                        # (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
ident_file = '/postgresql/9.5/data/pg_ident.conf'
                                        # (change requires restart)

Step 6) Modify the Daemon configuration file 

[root@pg_primary data]# vim /usr/lib/systemd/system/postgresql-9.5.service

#Environment=PGDATA=/var/lib/pgsql/9.5/data/
Environment=PGDATA=/postgresql/9.5/data/

Step 7) Reload the deamon and start service

[root@pg_primary data]# systemctl daemon-reload
[root@pg_primary data]# systemctl status postgresql-9.5.service
postgresql-9.5.service - PostgreSQL 9.5 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-9.5.service; enabled)
   Active: active (running) since Thu 2017-12-07 02:59:21 EST; 2s ago
     Docs: https://www.postgresql.org/docs/9.5/static/
  Process: 22590 ExecStop=/usr/pgsql-9.5/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS)
  Process: 22606 ExecStart=/usr/pgsql-9.5/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 (code=exited, status=0/SUCCESS)
  Process: 22601 ExecStartPre=/usr/pgsql-9.5/bin/postgresql95-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 22610 (postgres)

Step 8) Now check the datafile location

[root@pg_primary data]# ps -ef | grep postgre
postgres 22610     1  0 02:59 ?        00:00:00 /usr/pgsql-9.5/bin/postgres -D /postgresql/9.5/data
postgres 22611 22610  0 02:59 ?        00:00:00 postgres: logger process 
postgres 22613 22610  0 02:59 ?        00:00:00 postgres: checkpointer process 
postgres 22614 22610  0 02:59 ?        00:00:00 postgres: writer process 
postgres 22615 22610  0 02:59 ?        00:00:00 postgres: wal writer process 
postgres 22616 22610  0 02:59 ?        00:00:00 postgres: autovacuum launcher process 
postgres 22617 22610  0 02:59 ?        00:00:00 postgres: archiver process   failed on 000000010000000000000038
postgres 22618 22610  0 02:59 ?        00:00:00 postgres: stats collector process 
root     22673  3503  0 03:00 pts/0    00:00:00 tail -f postgresql-Thu.log
postgres 22761 22610  0 03:02 ?        00:00:00 postgres: wal sender process repuser 192.168.159.134(58787) streaming 0/39000548
root     22925  3713  0 03:11 pts/1    00:00:00 grep --color=auto postgre
[root@pg_primary data]#

postgres=# SHOW data_directory;
    data_directory   
----------------------
 /postgresql/9.5/data
(1 row)

postgres=#

"/var/lib/pgsql/data" is missing or empty. in postgresql Database

Problem: When trying to check the service status of  postgresql  as root on RHEL 7 server, i got the following error message

[root@localhost RHEL-7.0 Server.x86_64]# systemctl status postgresql.service 
postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled)
   Active: failed (Result: exit-code) since Mon 2017-12-04 01:37:16 EST; 7s ago
  Process: 14151 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE)

Dec 04 01:37:16 localhost.localdomain postgresql-check-db-dir[14151]: "/var/lib/pgsql/data" is missing or empty.
Dec 04 01:37:16 localhost.localdomain postgresql-check-db-dir[14151]: Use "postgresql-setup initdb" to initialize the database cluster.
Dec 04 01:37:16 localhost.localdomain postgresql-check-db-dir[14151]: See /usr/share/doc/postgresql-9.2.7/README.rpm-dist for mor...ion.
Dec 04 01:37:16 localhost.localdomain systemd[1]: postgresql.service: control process exited, code=exited status=1
Dec 04 01:37:16 localhost.localdomain systemd[1]: Failed to start PostgreSQL database server.
Dec 04 01:37:16 localhost.localdomain systemd[1]: Unit postgresql.service entered failed state.
Hint: Some lines were ellipsized, use -l to show in full.
[root@localhost RHEL-7.0 Server.x86_64]#

Reason:

initdb creates a new PostgreSQL database cluster. A database cluster is a collection of databases that are managed by a single server instance.

Creating a database cluster consists of creating the directories in which the database data will live, generating the shared catalog tables (tables that belong to the whole cluster rather than to any particular database), and creating the template1 and postgres databases. When you later create a new database, everything in the template1 database is copied. (Therefore, anything installed in template1 is automatically copied into each database created later.) The postgres database is a default database meant for use by users, utilities and third party applications.

Solution:


[root@localhost RHEL-7.0 Server.x86_64]# service postgresql initdb
Hint: the preferred way to do this is now "postgresql-setup initdb"
Initializing database ... OK

[root@localhost RHEL-7.0 Server.x86_64]#

[root@localhost RHEL-7.0 Server.x86_64]# systemctl stop postgresql.service
[root@localhost RHEL-7.0 Server.x86_64]# systemctl start postgresql.service
[root@localhost RHEL-7.0 Server.x86_64]# systemctl status postgresql.service
postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled)
   Active: active (running) since Mon 2017-12-04 01:40:51 EST; 4s ago
  Process: 46413 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
  Process: 46408 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 46418 (postgres)
   CGroup: /system.slice/postgresql.service
           ├─46418 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
           ├─46419 postgres: logger process 
           ├─46421 postgres: checkpointer process 
           ├─46422 postgres: writer process 
           ├─46423 postgres: wal writer process 
           ├─46424 postgres: autovacuum launcher process 
           └─46425 postgres: stats collector process 

Dec 04 01:40:50 localhost.localdomain systemd[1]: Starting PostgreSQL database server...
Dec 04 01:40:51 localhost.localdomain systemd[1]: Started PostgreSQL database server.
[root@localhost RHEL-7.0 Server.x86_64]#