Scribd
Upload
49 views4 pages

Install PostgreSQL on Ubuntu 20.04

This document provides instructions for installing PostgreSQL on Linux. It recommends installing through the package manager for proper integration. It then outlines the specific steps to install PostgreSQL on Ubuntu, which includes adding the PostgreSQL repository, importing keys, updating packages, and installing PostgreSQL. It also describes how to connect to the PostgreSQL database server using psql as the postgres user and load a sample database.

Uploaded by

Marco Capac Yupanqui
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
49 views4 pages

Install PostgreSQL on Ubuntu 20.04

This document provides instructions for installing PostgreSQL on Linux. It recommends installing through the package manager for proper integration. It then outlines the specific steps to install PostgreSQL on Ubuntu, which includes adding the PostgreSQL repository, importing keys, updating packages, and installing PostgreSQL. It also describes how to connect to the PostgreSQL database server using psql as the postgres user and load a sample database.

Uploaded by

Marco Capac Yupanqui
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Install PostgreSQL Linux

Summary: in this tutorial, you will learn how to download and install
PostgreSQL on Linux.

Most Linux platforms such as Debian, Red Hat / CentOS, SUSE, and Ubuntu have
PostgreSQL integrated with their package management.

It is recommended that you install PostgreSQL this way since it ensures a proper
integration with the operating system including automatic patching and other
update management functionality.

To download and install PostgreSQL on Linux, you visit the download page and


select your Linux distribution.

In this tutorial, we’ll show you how to install PostgreSQL on Ubuntu 20.04.

Install PostgreSQL on Ubuntu


Once visiting the PostgreSQL download for Ubuntu page, you find the script
that allows you to install PostgreSQL on Ubuntu. You need to execute them one
by one

First, execute the following command to create the file repository configuration:

$ sudo sh -c 'echo "deb [Link] $(lsb_release -cs)-pgdg main" >


/etc/apt/[Link].d/[Link]'
Code language: Shell Session (shell)

Second, import the repository signing key:

$ wget --quiet -O - [Link] | sudo apt-key add -


Code language: Shell Session (shell)

Third, update the package list:

$ sudo apt-get update


Code language: Shell Session (shell)

Finally, install the latest version of PostgreSQL:

$ sudo apt-get install postgresql


Code language: Shell Session (shell)

If you want to install a specific version, you can use postgresql-version instead


of postgresql. For example, to install PostgreSQL version 12, you use the following
command:
$ sudo apt-get install postgresql-12
Code language: Shell Session (shell)

It will take few minutes to download and install the PostgreSQL.

Connect to the PostgreSQL database server via psql


In PostgreSQL, a user account is referred to as a role. By default, PostgreSQL
uses ident authentication.

It means that PostgreSQL will associate its roles with the system accounts of
Linux. If a role exists in PostgreSQL, the same Linux user account with the same
name is able to log in as that role.

When you installed PostgreSQL, the installation process created a user account
called postgres associated with the default postgres role.

To connect to PostgreSQL using the postgres role, you switch over to


the postgres account on your server by typing:

$ sudo -i -u postgres
Code language: Shell Session (shell)

It’ll prompt for the password of the current user. You need to provide the
password and hit the Enter keyboard.

Then, you can access the PostgreSQL using the psql by typing the following
command:

$ psql
Code language: Shell Session (shell)

You’ll access the postgres prompt like this:

postgres=#
Code language: Shell Session (shell)

From here, you can interact with the PostgreSQL like issuing a query.

To quit the PostgreSQL prompt, you run the following command:

postgres=# \q
Code language: Shell Session (shell)

This above command will bring you back to the postgres Linux command


prompt.

postgres@ubuntu-dev:~$
Code language: Shell Session (shell)
To return to your regular system user, you execute the exit command like this:

postgres@ubuntu-dev:~$ exit
Code language: Shell Session (shell)

Load the sample database


To load the sample database into the PostgreSQL database server, you follow
these steps:

First, switch over the postgres account using the following command:

$ sudo -i -u postgres
Code language: Shell Session (shell)

It’ll prompt you for the password of the current user. You need to type the
password of the current user and press the Enter keyboard.

Second, download the sample database using the curl tool:

$ curl -O [Link]
Code language: Shell Session (shell)

Third, unzip the [Link] file to get the [Link] file:

$ unzip [Link]
Code language: Shell Session (shell)

Fourth, access the PostgreSQL using the psql tool:

$ psql
Code language: Shell Session (shell)

Fifth, create the dvdrental database using the CREATE DATABASE statement:

postgres=# create database dvdrental;


Code language: Shell Session (shell)

Sixth, quit the psql by using the \q command:

postgres=# \q
Code language: Shell Session (shell)

Seventh, use the pg_restore tool to restore the dvdrental database:

$ pg_restore --dbname=dvdrental --verbose [Link]


Code language: Shell Session (shell)

Eighth, access PostgreSQL database server again using psql:


$ psql
Code language: Shell Session (shell)

Ninth, switch to the dvdental database:

postgres=# \c dvdrental
Code language: Shell Session (shell)

Now, you’re connected to the dvdrental database:

dvdrental=#
Code language: Shell Session (shell)

Finally, enter the following command to get the number of films in the film table:

dvdrental=# select count(*) from film;


Code language: Shell Session (shell)

Here is the output:

count
-------
1000
(1 row)
Code language: Shell Session (shell)

Congratulations! you have successfully installed PostgreSQL on Ubuntu, connect


to PostgreSQL database server using psql, and load the sample database.

Common questions

Powered by AI

To export a PostgreSQL database schema and data, one typically uses the pg_dump tool. For example, the command: $ pg_dump -U postgres -d database_name -F c -f backup_file.dump exports the specified database to a custom-format archive file. This file can be restored with pg_restore, providing flexibility in database backup and restoration strategies .

After PostgreSQL installation, connect to the database server by switching to the postgres account with: $ sudo -i -u postgres. Then start the psql tool by typing: $ psql. This will bring up the PostgreSQL prompt, where database queries and commands can be executed .

PostgreSQL uses ident authentication, which links PostgreSQL roles to system user accounts. This means that if a PostgreSQL role exists with the same name as a system user account, that account can log in as the PostgreSQL role without needing a password. This is significant for authentication because it simplifies the login process and ties database access permissions directly to system-level user management, enhancing integration and security .

Installing PostgreSQL via package management on Linux is recommended because it ensures proper integration with the operating system, including functionalities like automatic patching and update management. This approach can reduce maintenance load and improve system stability since the installation and updates are handled within the system's native package management workflow .

After working with the PostgreSQL administrative account, you can switch back to a regular system user by executing the 'exit' command at the Linux command prompt. This will return the shell session from the postgres account back to the originating system user account .

To install PostgreSQL on Ubuntu 20.04, first, create the file repository configuration using the command: $ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'. Second, import the repository signing key: $ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -. Third, update the package list: $ sudo apt-get update. Finally, install the latest version of PostgreSQL with: $ sudo apt-get install postgresql. Alternatively, to install a specific version, use $ sudo apt-get install postgresql-version, replacing 'version' with the specific version number .

'Ident authentication' benefits PostgreSQL by linking database roles to existing system user accounts, which simplifies authentication by eliminating the need for separate credentials. It improves security by utilizing existing operating system controls and reduces administration overhead as user management is handled centrally at the system level .

The 'pg_restore' command in PostgreSQL is used to restore a PostgreSQL database from an archive created by pg_dump or similar tools. It is particularly useful for restoring complex databases that may include multiple schemas or tables, as it can handle a variety of file formats and allows for the selective restoration of objects from the archive .

To install a specific version of PostgreSQL on Ubuntu, after adding the PostgreSQL repository, use the command: $ sudo apt-get install postgresql-version, replacing 'version' with the desired version number (e.g., postgresql-12 for version 12). This ensures precision in deployment when specific features or compatibility are required .

To load a sample database, first switch to the postgres account: $ sudo -i -u postgres. Download the sample database using curl: $ curl -O https://sp.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip. Unzip the file: $ unzip dvdrental.zip. Access PostgreSQL with the psql tool: $ psql. Create the database using the command: postgres=# create database dvdrental;. Quit psql: \. Use pg_restore to restore the database: $ pg_restore --dbname=dvdrental --verbose dvdrental.tar. Reconnect with psql: $ psql, and switch to the new database with: postgres=# \c dvdrental. Verify by executing: dvdrental=# select count(*) from film;, to see an output indicating 1000 films .

You might also like