How to install Postgresql on Ubuntu

by ddos ·

PostgreSQL is a high-performance, SQL- compatible database management system. PostgreSQL is used by many applications. PostgreSQL is installed on Ubuntu with one command:

sudo apt install postgresql

During installation, a postgres user is created with local authentication.

We enter the database as the postgres user:

sudo –u postgres psql

Creating a superuser with password authentication

Create a new user:
ddos – username;
Dvs@$)$ – password.

CREATE USER ddos WITH password ‘Dvs@$)$’;

Grant superuser rights:

Exiting postgres:
\q
Now you can connect to a user rootuser with password authentication. Since the rootuser user does not have his own database, we will connect to the postgres database :
psql –h localhost –U ddos –d postgres –W
Parameters:
-h – server;
-U – username;
-d – database;
-W – use password authentication.

Create user and database

Go to the psql management interface and create another user:
username – username
MyPassword – password

CREATE USER username WITH password ‘MyPassword’;

Create a database:
user_db – database name;
MyPassword1 – password.

CREATE DATABASE user_db;

We give the username user rights to the user_db database:

GRANT ALL ON DATABASE user_db TO user;

Exiting postgres:

\q

Table operations

Create a users table with login and password fields :

CREATE TABLE users (login CHAR(64), password CHAR(64));

Delete table:

DROP TABLE users;

Useful psql commands :

  • Show users: \du
  • Show Databases: \l
  • Show tables in the current database: \dt
  • Show columns in a table: \d table_name
  • Show the PostgreSQL version: SELECT version();
  • help for SQL statements: \h
  • help for psql commands: \?

Tags: install Postgresqlubuntu