pg_dump: server version: 9.5.10; pg_dump version: 9.2.13 (Mismatch)

Scenario : Today when running pg_dump command to take backup got the following error

-bash-4.2$ /var/lib/pgsql/pgbackup.sh
pg_dump: server version: 9.5.10; pg_dump version: 9.2.13
pg_dump: aborting because of server version mismatch
pg_dump: server version: 9.5.10; pg_dump version: 9.2.13
pg_dump: aborting because of server version mismatch
pg_dump: server version: 9.5.10; pg_dump version: 9.2.13
pg_dump: aborting because of server version mismatch
iti_20171214-093346.bak                                                                                                                                     100%    0     0.0KB/s   00:00   
protrack_20171214-093346.bak                                                                                                                                100%    0     0.0KB/s   00:00   
sdwm_20171214-093346.bak                                                                                                                                    100%    0     0.0KB/s   00:00   
-bash-4.2$

Solution

Check the version of psql, pg_dump, pg_restore

-bash-4.2$ psql --version
psql (PostgreSQL) 9.2.13
-bash-4.2$
-bash-4.2$ pg_dump --version
pg_dump (PostgreSQL) 9.2.13
-bash-4.2$ which pg_dump
/bin/pg_dump
-bash-4.2$

[root@inhouse-dmz-db1 backups]# su - postgres
Last login: Wed Dec 13 16:48:52 BDT 2017 on pts/1
-bash-4.2$ psql -version
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.

postgres=#

-bash-4.2$ ps aux | grep postgres
root      3747  0.0  0.0 209916  3208 pts/0    S    Dec11   0:00 su - postgres
postgres  3748  0.0  0.0 116284  3064 pts/0    S    Dec11   0:00 -bash
postgres  6592  0.0  0.1 382880 21048 ?        Ss   Dec13   0:01 postgres: iti iti 172.17.205.122(46656) idle
postgres  6593  0.0  0.0 380896 14904 ?        Ss   Dec13   0:00 postgres: iti iti 172.17.205.122(46657) idle
postgres  6594  0.0  0.2 391480 38560 ?        Ss   Dec13   0:03 postgres: iti iti 172.17.205.122(46658) idle
postgres  7745  0.0  0.0 380744 15608 ?        Ss   Dec13   0:02 postgres: iti iti 172.17.205.122(46669) idle
postgres  7746  0.0  0.0 379972  7264 ?        Ss   Dec13   0:00 postgres: iti iti 172.17.205.122(46670) idle
postgres  7747  0.0  0.3 387940 63532 ?        Ss   Dec13   0:04 postgres: iti iti 172.17.205.122(46671) idle
postgres  7749  0.0  0.2 392080 48544 ?        Ss   Dec13   0:06 postgres: iti iti 172.17.205.122(46672) idle
postgres  7750  0.0  0.0 379272  4104 ?        Ss   Dec13   0:00 postgres: iti iti 172.17.205.122(46673) idle
postgres  7752  0.0  0.0 379272  4104 ?        Ss   Dec13   0:00 postgres: iti iti 172.17.205.122(46674) idle
root     10788  0.0  0.0 209908  3204 pts/1    S    09:40   0:00 su - postgres
postgres 10789  0.0  0.0 116164  2848 pts/1    S    09:40   0:00 -bash
postgres 11003  0.0  0.0 139492  1632 pts/1    R+   09:46   0:00 ps aux
postgres 11004  0.0  0.0 112648   952 pts/1    S+   09:46   0:00 grep --color=auto postgres
postgres 13213  0.0  0.2 377704 42080 ?        S    Dec12   0:06 /usr/pgsql-9.5/bin/postgres -D /pgdata/9.5/data
postgres 13228  0.0  0.0 190360  1588 ?        Ss   Dec12   0:00 postgres: logger process 
postgres 13232  0.0  0.6 378276 109772 ?       Ss   Dec12   0:01 postgres: checkpointer process 
postgres 13233  0.0  0.3 377964 62180 ?        Ss   Dec12   0:01 postgres: writer process 
postgres 13234  0.0  0.0 377704  5960 ?        Ss   Dec12   0:03 postgres: wal writer process 
postgres 13235  0.0  0.0 378848  2872 ?        Ss   Dec12   0:05 postgres: autovacuum launcher process 
postgres 13236  0.0  0.0 192480  1624 ?        Ss   Dec12   0:00 postgres: archiver process   last was 000000010000000000000068
postgres 13237  0.0  0.0 192780  2020 ?        Ss   Dec12   0:11 postgres: stats collector process 
postgres 13261  0.0  0.0 379012  3204 ?        Ss   Dec12   0:02 postgres: wal sender process repuser 172.17.205.178(40716) streaming 0/69356E28
postgres 14289  0.0  0.0 107932   624 pts/0    S+   Dec13   0:00 tail -f postgresql-Wed.log
-bash-4.2$

-bash-4.2$ /usr/pgsql-9.5/bin/postgres --version
postgres (PostgreSQL) 9.5.10
-bash-4.2$

Now relink the pg_dump and pg_restore with 9.5.10 version

root@inhouse-dmz-db1 backups]# cd /usr/bin/
[root@inhouse-dmz-db1 bin]# ls -lrt pg*
-rwxr-xr-x. 1 root root 428600 Jan 25  2014 pgawk
-rwxr-xr-x. 1 root root  28320 Oct  6  2014 pgrep
-rwxr-xr-x. 1 root root 125656 Jun 15  2015 pg_restore
-rwxr-xr-x. 1 root root  75600 Jun 15  2015 pg_dumpall
-rwxr-xr-x. 1 root root 308744 Jun 15  2015 pg_dump
-rwxr-xr-x. 1 root root  28648 Jun 15  2015 pg_config
lrwxrwxrwx. 1 root root     37 Dec 10 16:47 pg_basebackup -> /etc/alternatives/pgsql-pg_basebackup
[root@inhouse-dmz-db1 bin]# mv pg_dump pg_dump.bak
   
[root@inhouse-dmz-db1 bin]# ln -s /usr/pgsql-9.5/bin/pg_dump ./
[root@inhouse-dmz-db1 bin]# ls -lrt pg_r*
-rwxr-xr-x. 1 root root 125656 Jun 15  2015 pg_restore
[root@inhouse-dmz-db1 bin]#

[root@inhouse-dmz-db1 ~]# su - postgres
Last login: Thu Dec 14 09:40:05 BDT 2017 on pts/1
-bash-4.2$ pg_dump --version
pg_dump (PostgreSQL) 9.5.10
-bash-4.2$ 

-bash-4.2$ pg_restore --version
pg_restore (PostgreSQL) 9.2.13
-bash-4.2$

[root@inhouse-dmz-db1 bin]# mv pg_restore pg_restore.bak
[root@inhouse-dmz-db1 bin]# ln -s /usr/pgsql-9.5/bin/pg
   
[root@inhouse-dmz-db1 bin]# ln -s /usr/pgsql-9.5/bin/pg_restore ./
[root@inhouse-dmz-db1 bin]# ls -lrt pg_r*
-rwxr-xr-x. 1 root root 125656 Jun 15  2015 pg_restore
[root@inhouse-dmz-db1 bin]#

-bash-4.2$ pg_restore --version
pg_restore (PostgreSQL) 9.5.10
-bash-4.2$ 

Now try to take backup again !!!!!!!!!!!!!

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