Cyberithub

How to manage multiple PostgreSQL Instances Using pgenv

Advertisements

In this article, we will see how to manage multiple PostgreSQL Instances Using pgenv. If you are looking to install and manage multiple versions of postgresql in your Server then pgenv or PostgreSQL binary manager is one of the best option you can choose here. Using pgenv, it is very easy to install, stop and start postgresql cluster instances. This tool is used by Database developers, administrators and all other professionals who usually test their applications compatibility with multiple PostgreSQL versions. We will see the steps to install multiple PostgreSQL instances in below section.

How to manage multiple PostgreSQL Instances Using pgenv

How to manage multiple PostgreSQL Instances Using pgenv

Also Read: How to Install PostgreSQL DB on RHEL/CentOS 7/8

Step 1: Prerequisites

a) You should have a running Linux Server

b) You should have apt-get and git utility installed in your System.

c) You should have sudo access to run privileged commands.

d) You should have a non-root account to run all the PostgreSQL commands.

 

Step 2: Install required libraries

Pgenv requires few of the libraries to be already installed in your System before installing PostgreSQL instances, so we will first go ahead and install all the libraries using below apt-get install command.

postgres@cyberithub:~$ sudo apt-get install lib32readline8 libreadline-gplv2-dev lib32tinfo6 libc6-i386 libtinfo-dev
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
dmeventd libaio1 libdevmapper-event1.02.1 liblvm2cmd2.03 libreadline5 thin-provisioning-tools
Use 'apt autoremove' to remove them.
The following additional packages will be installed:
lib32tinfo6 libc6-i386
The following NEW packages will be installed:
lib32readline8 lib32tinfo6 libc6-i386
0 upgraded, 3 newly installed, 0 to remove and 181 not upgraded.
Need to get 2,945 kB of archives.
After this operation, 15.7 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 http://in.archive.ubuntu.com/ubuntu focal-updates/main amd64 libc6-i386 amd64 2.31-0ubuntu9.7 [2,725 kB]
Get:2 http://in.archive.ubuntu.com/ubuntu focal/main amd64 lib32tinfo6 amd64 6.2-0ubuntu2 [86.6 kB]
Get:3 http://in.archive.ubuntu.com/ubuntu focal/main amd64 lib32readline8 amd64 8.0-4 [133 kB]
Fetched 2,945 kB in 1s (2,489 kB/s)
Selecting previously unselected package libc6-i386.
.............................

 

Step 3: Install pgenv

You can go to official GitHub repo and download the latest repo files in your local System using below git clone command. This will create a local directory called pgenv and save all the repo files inside it.

postgres@cyberithub:~$ git clone https://github.com/theory/pgenv
Cloning into 'pgenv'...
remote: Enumerating objects: 795, done.
remote: Counting objects: 100% (150/150), done.
remote: Compressing objects: 100% (67/67), done.
remote: Total 795 (delta 63), reused 134 (delta 51), pack-reused 645
Receiving objects: 100% (795/795), 226.51 KiB | 698.00 KiB/s, done.
Resolving deltas: 100% (368/368), done.

You can export this local directory using PATH environment variable as shown below. But please note here that this is just the temporary way to export the PATH. Closing the current terminal session would result in loss of current changes. So in order to make this permanent, add below entry into ~/.profile or ~/.bashrc file.

postgres@cyberithub:~$ export PATH=$PATH:./pgenv/bin

 

Step 4: Install PostgreSQL Version 12.0 and 12.1

To install PostgreSQL version 12.0, you can use pgenv build 12.0 command as shown below.

postgres@cyberithub:~$ pgenv build 12.0
........................................
/usr/bin/mkdir -p '/root/pgenv/pgsql-12.0/bin'
/usr/bin/install -c vacuumlo '/root/pgenv/pgsql-12.0/bin'
make[2]: Leaving directory '/root/pgenv/src/postgresql-12.0/contrib/vacuumlo'
make[1]: Leaving directory '/root/pgenv/src/postgresql-12.0/contrib'
PostgreSQL, contrib, and documentation installation complete.
pgenv configuration written to file /root/pgenv/config/12.0.conf
PostgreSQL 12.0 built

Similarly to install version 12.1, you can use pgenv build 12.1 command as shown below.

postgres@cyberithub:~$ pgenv build 12.1
..................................
make[2]: Entering directory '/root/pgenv/src/postgresql-12.1/contrib/vacuumlo'
/usr/bin/mkdir -p '/root/pgenv/pgsql-12.1/bin'
/usr/bin/install -c vacuumlo '/root/pgenv/pgsql-12.1/bin'
make[2]: Leaving directory '/root/pgenv/src/postgresql-12.1/contrib/vacuumlo'
make[1]: Leaving directory '/root/pgenv/src/postgresql-12.1/contrib'
PostgreSQL, contrib, and documentation installation complete.
pgenv configuration written to file /root/pgenv/config/12.1.conf
PostgreSQL 12.1 built

 

Step 5: Check all Installed PostgreSQL Instances

To check all the install postgresql versions, you need to use pgenv versions command as shown below.

postgres@cyberithub:~$ pgenv versions
12.0 pgsql-12.0
12.1 pgsql-12.1

 

Step 6: Start an Instance

Now that you have two different versions of PostgreSQL installed in the Server, you can decide which instance to start by using pgenv use command. For example here we are starting instance 12.0 using pgenv use 12.0 command as shown below.

postgres@cyberithub:~$ pgenv use 12.0

WARNING:
Your PATH enrvironemnt variable does not seem to include

/var/lib/postgresql/pgenv/pgsql/bin

as an entry. You will not be able to use the currently
selected PostgreSQL binaries.

HINT:
Adjust your PATH variable to include

/var/lib/postgresql/pgenv/pgsql/bin

for instance

export PATH=/var/lib/postgresql/pgenv/pgsql/bin:$PATH

Already using PostgreSQL 12.0
PostgreSQL 12.0 started
Logging to /var/lib/postgresql/pgenv/pgsql/data/server.log

 

Step 7: Connect to the Instance

Once the instance is started, you can connect to that instance using psql -U postgres -h localhost command as shown below. Here you can notice the instance that you are currently connected with.

postgres@cyberithub:~$ psql -U postgres -h localhost
psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1), server 12.0)
Type "help" for help.

postgres=#

 

Step 8: Stop Current Instance

If you are looking to change the PostgreSQL instance then first you need to stop the current version by simply using pgenv stop command as shown below.

postgres@cyberithub:~$ pgenv stop

WARNING:
Your PATH enrvironemnt variable does not seem to include

/var/lib/postgresql/pgenv/pgsql/bin

as an entry. You will not be able to use the currently
selected PostgreSQL binaries.

HINT:
Adjust your PATH variable to include

/var/lib/postgresql/pgenv/pgsql/bin

for instance

export PATH=/var/lib/postgresql/pgenv/pgsql/bin:$PATH

PostgreSQL 12.0 stopped

 

Step 9: Start another Instance

Then to start another version you can use pgenv use 12.1 command as shown below.

postgres@cyberithub:~$ pgenv use 12.1

WARNING:
Your PATH enrvironemnt variable does not seem to include

/var/lib/postgresql/pgenv/pgsql/bin

as an entry. You will not be able to use the currently
selected PostgreSQL binaries.

HINT:
Adjust your PATH variable to include

/var/lib/postgresql/pgenv/pgsql/bin

for instance

export PATH=/var/lib/postgresql/pgenv/pgsql/bin:$PATH

PostgreSQL 12.0 not running
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /var/lib/postgresql/pgenv/pgsql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Kolkata
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

/var/lib/postgresql/pgenv/pgsql/bin/pg_ctl -D /var/lib/postgresql/pgenv/pgsql/data -l logfile start

PostgreSQL 12.1 started
Logging to /var/lib/postgresql/pgenv/pgsql/data/server.log

 

Step 10: Connect to Instance

Like previous version, you can also connect to the current running version by using same psql -U postgres -h localhost command as shown below.

postgres@cyberithub:~$ psql -U postgres -h localhost
psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1), server 12.1)
Type "help" for help.

postgres=#

Leave a Comment