Shulou(Shulou.com)06/01 Report--

This article mainly shows you "how to install and configure PostgreSQL on Ubuntu", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to install and configure PostgreSQL on Ubuntu" this article.

PostgreSQL (also known as Postgres) is a powerful free and open source relational database management system (RDBMS), which has gained a high reputation in the industry in terms of reliability, stability and performance. It is designed to handle tasks of all sizes. It is cross-platform and is the default database for macOS Server.

If you like the easy-to-use SQL database manager, then PostgreSQL will be the right choice. PostgreSQL not only provides additional features while being compatible with standard SQL, but also can be greatly extended by users, who can add data types, functions, and perform more operations.

I've talked about installing MySQL on Ubuntu before. In this article, I'll show you how to install and configure PostgreSQL so that you can use it at any time to meet any of your needs.

Install PostgreSQL on Ubuntu

PostgreSQL can be obtained from the Ubuntu main repository. However, like many other development tools, it may not be the latest version.

First use the apt command in the terminal to check the version of PostgreSQL available in the Ubuntu repository:

Apt show postgresql

In my Ubuntu 18.04, it shows that the available version of PostgreSQL is 10 (10x 190 means version 10) and PostgreSQL version 11 has been released.

Package: postgresqlVersion: 10+190Priority: optionalSection: databaseSource: postgresql-common Origin: Ubuntu

Based on this information, you can decide whether to install the version provided by Ubuntu or get the latest release of PostgreSQL.

I'm going to introduce you to these two methods:

Method 1: install PostgreSQL through the Ubuntu repository

In the terminal, install PostgreSQL using the following command:

Sudo apt updatesudo apt install postgresql postgresql-contrib

Enter your password according to the prompt, and depending on your network speed, the program will be installed in a few seconds to minutes. Speaking of which, check the various network bandwidths in Ubuntu at any time.

What is postgresql-contrib?

The postgresql-contrib, or contrib package, contains utilities and features that are not part of the PostgreSQL core package. In most cases, it is best to install the contrib package with the PostgreSQL core.

Method 2: install the latest version of PostgreSQL 11 in Ubuntu

To install PostgreSQL 11, you need to add the official PostgreSQL repository and certificate to sources.list, and then install it from there.

Don't worry, it's not complicated. Just follow these steps.

First add the GPG key:

Wget-- quiet-O-https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add-

Now, use the following command to add the repository. If you are using Linux Mint, you must manually replace the Ubuntu version number on which your Mint is based:

Sudo sh-c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release-cs`-pgdg main" > > / etc/apt/sources.list.d/pgdg.list'

Everything is ready now. Install PostgreSQL using the following command:

Sudo apt updatesudo apt install postgresql postgresql-contrib

PostgreSQL GUI application

You can also install the GUI application (pgAdmin) for managing PostgreSQL databases:

Sudo apt install pgadmin4

PostgreSQL configuration

You can check if PostgreSQL is running by executing the following command:

Service postgresql status

With the service command, you can start, shut down, or restart postgresql. Enter service postgresql and press enter to list all options. Now, log in to the user.

By default, PostgreSQL creates a special user postgres with permissions. To actually use PostgreSQL, you must first log in to the account:

Sudo su postgres

Your prompt will change to something similar to the following:

Postgres@ubuntu-VirtualBox:/home/ubuntu$

Now, use psql to start PostgreSQL Shell:

Psql

You should see the following prompt:

Postgress=#

You can type\ Q to exit, enter\? Get help.

To view all existing tables, enter the following command:

\ l

The output is similar to the following figure (click Q to exit the view):

PostgreSQL Tables

Using the\ du command, you can view the PostgreSQL user:

PostgreSQLUsers

You can change the password of any user, including postgres, using the following command:

ALTER USER postgres WITH PASSWORD 'my_password'

Note: replace postgres with the user name you want to change, and my_password with the required password. Also, don't forget the semicolon after each command.

It is recommended that you create another user (the default postgres user is not recommended). To do this, use the following command:

CREATE USER my_user WITH PASSWORD 'my_password'

Run\ du and you will see the user, but the my_user user does not have any attributes. Let's add superuser privileges to it:

ALTER USER my_user WITH SUPERUSER

You can delete a user using the following command:

DROP USER my_user

To log in with a different user, log out with the\ Q command, and then log in with the following command:

Psql-U my_user

You can use the-d parameter to connect directly to the database:

Psql-U my_user-d my_db

You can use other existing users to call PostgreSQL. For example, I use ubuntu. To log in, perform the following naming from the terminal:

Psql-U ubuntu-d postgres

Note: you must specify a database (by default, it will try to connect you to the same database as the user name you logged in).

If you encounter the following error:

Psql: FATAL: Peer authentication failed for user "my_user"

Be sure to log in as the correct user and edit / etc/postgresql/11/main/pg_hba.conf with administrator privileges:

Sudo vim / etc/postgresql/11/main/pg_hba.conf

Note: replace 11 (e.g. 10) with your version.

Replace the line shown below:

Local all postgres peer

To be replaced by:

Local all postgres md5

Then restart PostgreSQL:

Sudo service postgresql restart

Using PostgreSQL is the same as using other SQL types of databases. Since this article is intended to help you with the preliminary setup, there are no specific commands involved. However, here is a very useful point for reference! In addition, man psql and documentation are also very useful.

The above is all the contents of the article "how to install and configure PostgreSQL on Ubuntu". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.