Install and set up PostgreSQL and PgAdmin in Linux

There are two ways to install PostgreSQL in your system.

Himanshu Patel

6 min readSep 4, 2022

Install PostgreSQL directly in your system (based on what OS you have).
First, install Docker and then install PostgreSQL on top of docker. (We take this approach because docker installation is OS specific but on top of docker when we install any image — like PostgreSQL here — then it is OS independent, which means the installation step of PostgreSQL on top of docker and its usage will remain independent of OS)

Steps

Install docker in your system.

Refer official website dependent on your OS.

Install PostgreSQL image on top of docker

$ docker run --name my-own-postgres -e POSTGRES_PASSWORD=my_password -p 5432:5432 -d postgres

Flag --name assign a specific name to the container we started.
Flag -e set an environment variable inside the container POSTGRES_PASSWORD. This variable is used by the PostgreSQL image to assign the root password of Postgres.
Flag -d will run the container in the background in “detached” mode. This means your terminal becomes free for the next commands and will not show the scripts running to start PostgreSQL.
Flag -p indicates the mapping between the host port and the container port respectively. The First 5432 is for the host and the next 5432 is for the container. All requests sent to port 5432 on the local host will be redirected to the same port within the container (where PostgreSQL will be listening).
You will see something like this on command docker ps

$ docker ps
CONTAINER ID   IMAGE      COMMAND                  CREATED         STATUS         PORTS                                       NAMES
ef2331c777c1   postgres   "docker-entrypoint.s…"   3 seconds ago   Up 2 seconds   0.0.0.0:5432->5432/tcp, :::5432->5432/tcp   my-own-postgres

To ensure the setup is PostgreSQL is installed correctly move inside the container and check for some postgres user (default user created on installation).
Move inside container

$ docker exec -it my-own-postgres bash
root@ef2331c777c1:/#

Enter psql command line via different ways, with and without password (\q is to quit from shell and exit is to quit from the container)

---------------------Without hostname and password------------------root@ef2331c777c1:/# psql -U postgres
psql (14.4 (Debian 14.4-1.pgdg110+1))
Type "help" for help.
postgres=# \q----------------------------With Hostname---------------------------
root@ef2331c777c1:/# psql -U postgres -h localhost
psql (14.4 (Debian 14.4-1.pgdg110+1))
Type "help" for help.postgres=# \q---------------------------With Password----------------------------
root@ef2331c777c1:/# psql -U postgres -h localhost -W
Password: 
psql (14.4 (Debian 14.4-1.pgdg110+1))
Type "help" for help.postgres=# ----------------------------List Databases--------------------------
postgres=# \lList of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)postgres=#

Install PgAdmin (to access PostgreSQL easily — optional)

Set up PgAdmin using a docker image of PgAdmin. Notice here the machine port is 5050 so when we will access the PgAdmin console in the browser then we have to visit a URL like http://localhost:5050/login, Also remember the login and password this will be used for login in PgAdmin.

$ docker run --name pgadmin -e "PGADMIN_DEFAULT_EMAIL=name@example.com" -e "PGADMIN_DEFAULT_PASSWORD=admin" -p 5050:80 -d dpage/pgadmin4

To connect to the PostgreSQL database make sure the previous PostgreSQL container is running and get its IPAddress as

$ docker inspect --format='{{.Name}} - {{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' $(docker ps -aq)
/pgadmin - 172.17.0.3
/my-own-postgres - 172.17.0.2

Put the IP of PostgresSQL (172.17.0.2) in the connection setting of PgAdmin. Click on Server > Register > Server then enter below details

Network Setting for connection of PgAdmin to PostgreSQL

Hostname: IP Address of PostgreSQL container.

Port: Machine post of PostgresSQL (not the container port)

DB Name: by default postgres DB is created on startup.

Username: Username we choose while starting PostgresSQL container (not PgAdmin)

Password: Password of PostgreSQL DB

Once connected you can see something like this.

We can install PgAdmin on the local machine as well and make it connect to docker PostgreSQL, but then the PgAdmin hostname might be localhost

Wait! There is something more. What if I want the same PSQL shell here in PgAdmin?

This way of installing PgAdmin is perfectly ok, for all operations except in one case when we want to access the PSQL shell in PgAdmin like this one

I tried but could not get this in the PgAdmin of docker. But we can easily get this in the PgAdmin4 we install in the OS directly. But there is one catch. This direct installation will refer to binaries like psql and psql_dump which comes with the installation of Postgres on top of the OS directly. I think this is the reason why PgAdmin running on the docker instance could not show the option of PSQL shell because one container process (PgAdmin container) can’t refer to the binaries of Postgres present in another container (Postgres container).

This will need the installation of Postgres and PgAdmin on top of OS and once we are able to access PgAdmin we will make a connection to both the database (OS Postgres and Docker Postgres).

Install Postgres in Linux OS (Ubuntu) as (the first command spans over two lines but that’s one)

$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -$ sudo apt-get update$ sudo apt-get -y install postgresql

Enter the PSQL shell as below

$ sudo -u postgres psql
[sudo] password for ubuntu_user: 
psql (14.0 (Ubuntu 14.0-1.pgdg21.04+1))
Type "help" for help.

postgres=#

After installing Postgres on Linux OS determine its binary location and keep this safe as we need to provide this binary folder path in PgAdmin in order to access the same PSQL shell.

$ which psql
/usr/bin/psql

Also, make sure the process is up and running. (In case of docker Postgres is running on port 5432 then the OS Postgres will automatically choose 5433 port)

$ sudo systemctl status postgresql.service
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Sun 2022-09-04 16:22:28 IST; 4h 6min ago
   Main PID: 10856 (code=exited, status=0/SUCCESS)
        CPU: 3ms

If not then start using the below command

$ sudo systemctl start postgresql.service

Install PgAdmin now

# Install the public key for the repository (if not done previously):$ sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add

# Create the repository configuration file:$ sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

# Install for both desktop and web modes:$ sudo apt install pgadmin4