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
– Vao Tsun, Commented Apr 7, 2017 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
– Rohit Dhankar, Commented Apr 7, 2017 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
– Rohit Dhankar, Commented Apr 7, 2017 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
– Rohit Dhankar, Commented Apr 7, 2017 at 11:51

Community · Accepted Answer · 2017-04-13 12:42:36Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related