Wednesday 13 December 2017

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 !!!!!!!!!!!!!


No comments:

Post a Comment