In this article we are going to learn how to install and get PostgreSQL up and running on Ubuntu server. PostgreSQL is a powerful, open source object relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data work loads.
Step 1: Installing PostgreSQL
First update your server packages by issuing the following command.
$ sudo apt update $ sudo apt upgrade -y
Create file repository
Use the following script to create a file repository for PostgreSQL.
$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
After you have added the following script, now you can add GPG key.
Import GPG Key
Import GPG key pair in order to for it to be signed correctly. Use the following command to import the key.
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
You will get an ok as output meaning you have successfully added the GPG key.
Next we will run the updates so that introduced changes can take effect immediately.
$ sudo apt update
Now we can install the latest version of PostgreSQL
Install PostgrSQL 14 on Ubuntu 20.04
Use the following command to install specific version of PostgreSQL
$ sudo apt install postgresql-14 -y
Sample output will be as follows
.... Creating config file /etc/postgresql-common/createcluster.conf with new version Building PostgreSQL dictionaries from installed myspell/hunspell packages... Removing obsolete dictionary files: Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service. Setting up postgresql-14 (14.1-2.pgdg20.04+1) ... Creating new PostgreSQL cluster 14/main ... /usr/lib/postgresql/14/bin/initdb -D /var/lib/postgresql/14/main --auth-local peer --auth-host scram-sha-256 --no-instructions 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 "C.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /var/lib/postgresql/14/main ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... Etc/UTC creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok update-alternatives: using /usr/share/postgresql/14/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode Processing triggers for systemd (245.4-4ubuntu3.13) ... Processing triggers for man-db (2.9.1-1) ... Processing triggers for libc-bin (2.31-0ubuntu9.2) ...
To switch into Postgres account use the following command.
$ sudo -i -u postgres
You can access the Postgres acccount by using the following command.
$ psql psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1)) Type "help" for help. postgres=#
Step 2: Creating a new role
Login to postgres account in order to create a new role
$ createuser --interactive Enter name of role to add: nextgentips Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) y Shall the new role be allowed to create more new roles? (y/n) y
Step 3: Creating a new Database
Postgres authentication system assumes that for any role used to log in, that role will have a database with the same name.
For example we created a user with the name user, this role will attempt to connect to the database which is called user by default. Use creatdb to create appropriate database.
To access the PostgreSQL command line, you need a Linux account with the same name as the role and database name. If there is no such user, you can create one using the command:
$ sudo adduser nextgentips
Once this new account is available, you can either switch over and connect to the database by typing:
$ sudo -i -u nextgentips $ psql
To connect to a different database, use the following command.
$ psql -d postgres
PostgreSQL is now installed in your system. You can now explore more by reading the PostgreSQL documentations.