How to choose from - Multiple versions of PostgreSql on Ubuntu desktop

Question

I have version 9.5 installed . For creating a local backup from Heroku i need version 9.6.1 as thats the one on Heroku - otherwise pg__dump fails with an error.

Ubuntu console dump below -

(py27infoBOT) dhankar@dhankar-VPCEB44EN:~$ psql --version
psql (PostgreSQL) 9.6.2
(py27infoBOT) dhankar@dhankar-VPCEB44EN:~$ 
(py27infoBOT) dhankar@dhankar-VPCEB44EN:~$ pg_config --version
PostgreSQL 9.6.2
(py27infoBOT) dhankar@dhankar-VPCEB44EN:~$ 
(py27infoBOT) dhankar@dhankar-VPCEB44EN:~$ sudo su - postgres
postgres@dhankar-VPCEB44EN:~$ 
postgres@dhankar-VPCEB44EN:~$ psql -c 'SELECT version();'
                                                     version                                                     
-----------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 row)

postgres@dhankar-VPCEB44EN:~$ 
postgres@dhankar-VPCEB44EN:~$

UPDATE - As suggested --

~$ locate pg_dump
/etc/alternatives/pg_dump.1.gz
/etc/alternatives/pg_dumpall.1.gz
/usr/bin/pg_dump
/usr/bin/pg_dumpall
/usr/lib/postgresql/9.5/bin/pg_dump
/usr/lib/postgresql/9.5/bin/pg_dumpall
/usr/share/man/man1/pg_dump.1.gz
/usr/share/man/man1/pg_dumpall.1.gz
/usr/share/postgresql/9.5/man/man1/pg_dump.1.gz
/usr/share/postgresql/9.5/man/man1/pg_dumpall.1.gz

Further update , used pg_lsclusters :~

(py27infoBOT) dhankar@dhankar-VPCEB44EN:~$ 
(py27infoBOT) dhankar@dhankar-VPCEB44EN:~$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5432 online postgres /var/lib/postgresql/9.5/main /var/log       /postgresql/postgresql-9.5-main.log
9.6 main    5433 online postgres /var/lib/postgresql/9.6/main /var/log    /postgresql/postgresql-9.6-main.log

(py27infoBOT) dhankar@dhankar-VPCEB44EN:~$

you question in subject does not correlate to body. Please edit the question — Vao Tsun, Apr 07 '17 at 11:34
Need to clarify further --- have had version Postgres 9.5 on local machine . Have used the same for local Django app . Same app ported on Heroku. While backing up Heroku DB on local with - heroku pg:pull DATABASE_URL ...etc . got an ....ERROR -- `heroku-cli: Pulling postgresql-shaped-51549 ---> localDB_infobot_7APR_1300 pg_dump: server version: 9.6.1; pg_dump version: 9.5.6 pg_dump: aborting because of server version mismatch pg_restore: [archiver] input file is too short (read 0, expected 5)` Tried - upgrading my local 16.4 - to 9.6 with sudo apt get - but failed as seen below --- — Rohit Dhankar, Apr 07 '17 at 11:42
Further did this - `$ sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" $ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add-` got a success prompt `OK` — Rohit Dhankar, Apr 07 '17 at 11:46
Finally was able to download Postgres 9.6.2 -- got the following in console - `Creating new cluster 9.6/main ... config /etc/postgresql/9.6/main data /var/lib/postgresql/9.6/main locale en_IN socket /var/run/postgresql port 5433 update-alternatives: using /usr/share/postgresql/9.6/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode Setting up postgresql-contrib-9.6 (9.6.2-1.pgdg16.04+1) ... Processing triggers for libc-bin (2.23-0ubuntu7) ...` — Rohit Dhankar, Apr 07 '17 at 11:51
update your question with results of either of `locate pg_dump` or `whereis pg_dump` or sudo find / -name pg_dump` or all — Vao Tsun, Apr 07 '17 at 11:52
Further when i followed this question here - i got conflicting inputs , what do i do now ? http://stackoverflow.com/questions/13733719/which-version-of-postgresql-am-i-running?rq=1 — Rohit Dhankar, Apr 07 '17 at 11:53
run `updatedb` and then `locate pg_dump` again. if result does not change - you did not install 9.6 — Vao Tsun, Apr 07 '17 at 12:04
Actually both were running - have stopped 9.5 now . Now 9.6 is running on - port 5433 , will figure out how to connect to same - thanks for your time and efforts you were really helpful - thanks @Vao Tsun — Rohit Dhankar, Apr 07 '17 at 12:58

score 1 · Answer 1 · edited Apr 13 '17 at 12:42

Seems like i should document the solution that worked for me - I edited the .conf file for 9.6.2 to listen on port 5432 -

"config_file=/etc/postgresql/9.6/main/postgresql.conf"

Current status as below -

Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5432 down   postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

Further Update :~ Issue not resolved - now referring this very educative answer from user - Erwin Brandstetter , https://dba.stackexchange.com/questions/21006/how-to-run-specific-version-8-4-9-1-of-postgresql-pg-command-e-g-pg-dump/21007#21007 . Further question - How do i use the $ pg_dump --cluster 9.6/main , with the Heroku CLI command which is :- heroku pg:pull DATABASE_URL localDB_infobot --app infobot-co-in .

The standalone - $ pg_dump --cluster 9.6/main at local console results in - another ERROR

As of now - as a temporary solution , resorting to DB backup , download and local restore -

heroku pg:backups:capture heroku pg:backups:download pg_restore --verbose --clean --no-acl --no-owner -h localhost -U user_name -d database_name latest.dump

How to choose from - Multiple versions of PostgreSql on Ubuntu desktop

1 Answers1