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.
- Open a terminal and run the command $ sudo apt update to update Ubuntu OS.
- 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) ... - 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).
- 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. - You can run the command sudo systemctl start postgresql.service to start the PostgreSQL server.
- 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.
- 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=#
- 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
- Then restart the PostgreSQL server with the below command.
sudo service postgresql restart
- 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'
- Type \q to quit the PostgreSQL interactive console.
postgres-# \q
- 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"
- 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.
- All the PostgreSQL database server configuration files are saved in the directory /etc/postgresql/12/main.
- 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
- 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.
- 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
- If you do not install the curl, then run the command sudo apt install curl to install it.
- 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 - 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.
- 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]
- 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.
- 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.
- Now you can run the command sudo apt install pgadmin4 to install PgAdmin4 desktop and web modules.
- Run the command sudo apt install pgadmin4-desktop to install the PgAdmin4 desktop module only.
- 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
- Run the command sudo apt install pgadmin4-web to install the PgAdmin4 web module only.
- 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
- From the above output, you can see the PgAdmin web manager URL is http://127.0.0.1/pgadmin4.
- When you open the URL page, you need to input the email_address & password you configured in the above process to log in.