Maintaining Multiple Postgres Version

#postgres #database

Sometimes we need to have different Postgres versions in our local machine. We just need to know the port the versions are running and we are good to go.

For this let's install two versions of Postgres 9.6 and 11 via commands

Install Postgres 9.6

sudo apt-get install postgresql-9.6

Install Postgres 11

sudo apt-get install postgresql-11

Lists the postgres clusters

pg_lsclusters

start-stop the clusters

sudo pg_ctlcluster 9.6 main start
sudo pg_ctlcluster 9.6 main stop
sudo pg_ctlcluster 11 main start
sudo pg_ctlcluster 11 main stop

connects to 9.6

sudo -i -u postgres psql -p 5432

connects to 11

sudo -i -u postgres psql -p 5433

By changing the port only we can restore and dump db according to postgres version

dumps and restores 9.6

pg_dump --host localhost --port 5432 --username "postgres" --encoding UTF8 --verbose --file "{{db_backup_path}}" "{{db_name}}"
pg_restore --host 127.0.0.1 --port 5432 --username "postgres" --dbname "{{db_name}}" --verbose "{{db.backup}}"

dumps and restores 11

pg_dump --host localhost --port 5433 --username "postgres" --encoding UTF8 --verbose --file "{{db_backup_path}}" "{{db_name}}"
pg_restore --host 127.0.0.1 --port 5433 --username "postgres" --dbname "{{db_name}}" --verbose "{{db.backup}}"