How To Install PostgreSQL & PgAdmin On Ubuntu

This article will tell you how to install the PostgreSQL database server and PgAdmin ( PostgreSQL GUI Manager ) On Ubuntu Linux.

1. Install PostgreSQL Database Server On Ubuntu Steps.

  1. Open a terminal and run the command $ sudo apt update to update Ubuntu OS.
  2. Then run the command $ sudo apt install postgresql postgresql-contrib to install the PostgreSQL database server.
    $ sudo apt install postgresql postgresql-contrib
    Reading package lists... Done
    Building dependency tree       
    Reading state information... Done
    The following additional packages will be installed:
      libllvm10 libpq5 postgresql-12 postgresql-client-12 postgresql-client-common postgresql-common sysstat
    Suggested packages:
      postgresql-doc postgresql-doc-12 libjson-perl isag
    The following NEW packages will be installed:
      libllvm10 libpq5 postgresql postgresql-12 postgresql-client-12 postgresql-client-common postgresql-common postgresql-contrib sysstat
    0 upgraded, 9 newly installed, 0 to remove and 167 not upgraded.
    Need to get 30.6 MB of archives.
    After this operation, 121 MB of additional disk space will be used.
    ......
    
        pg_ctlcluster 12 main start
    
    Ver Cluster Port Status Owner    Data directory              Log file
    12  main    5432 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
    update-alternatives: using /usr/share/postgresql/12/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz)
     in auto mode
    Setting up sysstat (12.2.0-2ubuntu0.1) ...
    
    Creating config file /etc/default/sysstat with new version
    update-alternatives: using /usr/bin/sar.sysstat to provide /usr/bin/sar (sar) in auto mode
    Created symlink /etc/systemd/system/multi-user.target.wants/sysstat.service → /lib/systemd/system/sysstat.service.
    Setting up postgresql-contrib (12+214ubuntu0.1) ...
    Setting up postgresql (12+214ubuntu0.1) ...
    Processing triggers for systemd (245.4-4ubuntu3.11) ...
    Processing triggers for man-db (2.9.1-1) ...
    Processing triggers for libc-bin (2.31-0ubuntu9.2) ...
  3. If you meet an error message like Waiting for cache lock: Could not get lock /var/lib/dpkg/lock-frontend. It is held by process 2253 (unattended-upgr)… 0s when you run the above command, you can run the command sudo kill -9 2253(process id) to unlock the lock-frontend file. You can read the article How To Fix The Error waiting for cache lock: Could not get lock /var/lib/dpkg/lock-frontend. It is held by process 13083 (unattended-upgr).
  4. After installing the PostgreSQL server, you can run the command sudo systemctl status postgresql.service to check it’s status.
    $ sudo systemctl status postgresql.service
    [sudo] password for jerry: 
    ● postgresql.service - PostgreSQL RDBMS
         Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
         Active: active (exited) since Fri 2022-01-28 10:38:57 CST; 2h 4min ago
       Main PID: 4149 (code=exited, status=0/SUCCESS)
          Tasks: 0 (limit: 4633)
         Memory: 0B
         CGroup: /system.slice/postgresql.service
    
    Jan 28 10:38:57 jerry-VirtualBox systemd[1]: Starting PostgreSQL RDBMS...
    Jan 28 10:38:57 jerry-VirtualBox systemd[1]: Finished PostgreSQL RDBMS.
  5. You can run the command sudo systemctl start postgresql.service to start the PostgreSQL server.
  6. You can run the command sudo systemctl restart postgresql.service to restart the PostgreSQL server.

2. How To Login To The PostgreSQL Database Server In Terminal.

  1. Open a terminal and run the command sudo -u postgres psql to go to the PostgreSQL database server interactive console.
    $ sudo -u postgres psql
    psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
    Type "help" for help.
    
    postgres=# 
    
  2. If you can not log in to the PostgreSQL interactive console, you should check the file /etc/postgresql/12/main/pg_hba.conf and make sure the below line exists.
    # Database administrative login by Unix domain socket
    local   all             postgres                                peer
  3. Then restart the PostgreSQL server with the below command.
    sudo service postgresql restart
  4. The PostgreSQL DB server contains a default user postgres, you can change the user’s password with the below command.
    postgres=# ALTER USER postgres PASSWORD 'postgres'
  5. Type \q to quit the PostgreSQL interactive console.
    postgres-# \q
  6. Now you can run the command psql postgres postgres to login to the PostgreSQL DB server.
    $ psql postgres postgres
    psql: error: FATAL:  Peer authentication failed for user "postgres"
    
  7. If you meet the above error psql: error: FATAL: Peer authentication failed for user “postgres”, you can read the article How To Resolve PSQL: Fatal: Peer Authentication Failed For User “postgres” Error When Login PostgreSQL In Command Line to learn more.
  8. All the PostgreSQL database server configuration files are saved in the directory /etc/postgresql/12/main.
  9. We can list the files with the list -al command.
    /etc/postgresql/12/main$ ls -l
    total 56
    drwxr-xr-x 2 postgres postgres  4096 Jan  28 10:39 conf.d
    -rw-r--r-- 1 postgres postgres   315 Jan  28 10:39 environment
    -rw-r--r-- 1 postgres postgres   143 Jan  28 10:39 pg_ctl.conf
    -rw-r----- 1 postgres postgres  4931 Jan  28 13:58 pg_hba.conf
    -rw-r----- 1 postgres postgres  1636 Jan  28 10:39 pg_ident.conf
    -rw-r--r-- 1 postgres postgres 26926 Jan  28 10:39 postgresql.conf
    -rw-r--r-- 1 postgres postgres   317 Jan  28 10:39 start.conf
    
  10. Open the file postgresql.conf, you can see the default server listening port number (5432) and other settings.

3. How To Install The PostgreSQL Database Server Manager PgAdmin.

  1. First, run the command sudo curl –version to make sure you have installed the curl on your Ubuntu OS.
    $ sudo curl --version
    curl 7.68.0 (x86_64-pc-linux-gnu) libcurl/7.68.0 OpenSSL/1.1.1f zlib/1.2.11 brotli/1.0.7 libidn2/2.2.0 libpsl/0.21.0 (+libidn2/2.2.0) libssh/0.9.3/openssl/zlib nghttp2/1.40.0 librtmp/2.3
    
  2. If you do not install the curl, then run the command sudo apt install curl to install it.
  3. Run the command sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add to install the repository public key.
    $ sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
      % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                     Dload  Upload   Total   Spent    Left  Speed
    100  3935  100  3935    0     0   4011      0 --:--:-- --:--:-- --:--:--  4007
    OK
    
  4. Run the command $ 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’ to create the PgAdmin 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'
    Hit:1 http://security.ubuntu.com/ubuntu focal-security InRelease
    Hit:2 http://cn.archive.ubuntu.com/ubuntu focal InRelease                
    Hit:3 http://cn.archive.ubuntu.com/ubuntu focal-updates InRelease        
    Hit:4 http://cn.archive.ubuntu.com/ubuntu focal-backports InRelease      
    Ign:5 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal pgadmin4 InRelease
    Err:6 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal pgadmin4 Release
      Certificate verification failed: The certificate is NOT trusted. The certificate chain uses expired certificate.  Could not handshake: Error in the certificate verification. [IP: 147.75.85.69 443]
    Reading package lists... Done                      
    E: The repository 'https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal pgadmin4 Release' does not have a Release file.
    N: Updating from such a repository can't be done securely, and is therefore disabled by default.
    N: See apt-secure(8) manpage for repository creation and user configuration details.
  5. From the above output, we can see there is a certificate error like below. If this error is not fixed, the second error can not be fixed also.
    Err:6 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal pgadmin4 Release Certificate verification failed: The certificate is NOT trusted. The certificate chain uses expired certificate. Could not handshake: Error in the certificate verification. [IP: 147.75.85.69 443]
  6. So we should run the command sudo apt install ca-certificates to install a certificate to fix the certificate error.
    $ sudo apt install ca-certificates
    Reading package lists... Done
    Building dependency tree       
    Reading state information... Done
    The following packages will be upgraded:
      ca-certificates
    1 upgraded, 0 newly installed, 0 to remove and 165 not upgraded.
    Need to get 0 B/145 kB of archives.
    After this operation, 1,024 B disk space will be freed.
    Preconfiguring packages ...
    (Reading database ... 189329 files and directories currently installed.)
    Preparing to unpack .../ca-certificates_20210119~20.04.2_all.deb ...
    Unpacking ca-certificates (20210119~20.04.2) over (20210119~20.04.1) ...
    Setting up ca-certificates (20210119~20.04.2) ...
    Updating certificates in /etc/ssl/certs...
    0 added, 1 removed; done.
    Processing triggers for man-db (2.9.1-1) ...
    Processing triggers for ca-certificates (20210119~20.04.2) ...
    Updating certificates in /etc/ssl/certs...
    0 added, 0 removed; done.
    Running hooks in /etc/ca-certificates/update.d...
    done.
    
  7. Now run the command $ 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’ again, you can see the 2 errors has been fixed.
    $ 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'
    Hit:1 http://security.ubuntu.com/ubuntu focal-security InRelease                                                               
    Hit:2 http://cn.archive.ubuntu.com/ubuntu focal InRelease                                                                      
    Hit:3 http://cn.archive.ubuntu.com/ubuntu focal-updates InRelease         
    Get:4 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal pgadmin4 InRelease [4,217 B]
    Hit:5 http://cn.archive.ubuntu.com/ubuntu focal-backports InRelease
    Get:6 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal pgadmin4/main amd64 Packages [5,498 B]
    Get:7 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal pgadmin4/main all Packages [3,093 B]
    Fetched 12.8 kB in 2s (5,166 B/s)
    Reading package lists... Done
    Building dependency tree       
    Reading state information... Done
    166 packages can be upgraded. Run 'apt list --upgradable' to see them.
    
  8. Now you can run the command sudo apt install pgadmin4 to install PgAdmin4 desktop and web modules.
  9. Run the command sudo apt install pgadmin4-desktop to install the PgAdmin4 desktop module only.
  10. Now you can go to the /usr/pgadmin4/bin folder and then run the command script pgadmin4 to open the PgAdmin manager window.
    $ cd /usr/pgadmin4/bin/
    
    /usr/pgadmin4/bin$ ./pgadmin
  11. Run the command sudo apt install pgadmin4-web to install the PgAdmin4 web module only.
  12. Run the command sudo /usr/pgadmin4/bin/setup-web.sh to initialize and configure the PgAdmin4 web server.
    $ sudo /usr/pgadmin4/bin/setup-web.sh
    [sudo] password for jerry: 
    Setting up pgAdmin 4 in web mode on a Debian based platform...
    Creating configuration database...
    NOTE: Configuring authentication for SERVER mode.
    
    Enter the email address and password to use for the initial pgAdmin user account:
    
    Email address: [email protected]
    Password: 
    Retype password:
    pgAdmin 4 - Application Initialisation
    ======================================
    
    Creating storage and log directories...
    We can now configure the Apache Web server for you. This involves enabling the wsgi module and configuring the pgAdmin 4 application to mount at /pgadmin4. Do you wish to continue (y/n)? y
    The Apache web server is running and must be restarted for the pgAdmin 4 installation to complete. Continue (y/n)? y
    Apache successfully restarted. You can now start using pgAdmin 4 in web mode at http://127.0.0.1/pgadmin4
    
  13. From the above output, you can see the PgAdmin web manager URL is http://127.0.0.1/pgadmin4.
  14. When you open the URL page, you need to input the email_address & password you configured in the above process to log in.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Clicky