Installing Postgresql on Debian/Ubuntu

PostgreSQL, often simply Postgres, is an object-relational database management system with an emphasis on extensibility and standards compliance.PostgreSQL is used by many applications. PostgreSQL is installed on Ubuntu/Debian with one command:

aptget install postgresql

When installed, a postgres user with a local authentication of the Ident Authentication type is created.

We enter the database under the user postgres:

sudo u postgres psql

Creating a Superuser with Password Authentication

Create a new user:
root – username;
MyPassword is the password.

We give the rights of the superuser:

Exit from psql :

Now you can connect to a user root with password authentication:
Since user root does not have its own database, we will connect to the postgres database :

-h – server;
-U – username;
-d – database;
-W – use password authentication.

Creating a User and Database

Again, go into the management interface psql and create another user:
user – the username;

Create a database:
user_db – the name of the database;
MyPassword1 is the password.

give the user user rights to the user_db database :

Exit from psql :

Now you can connect to the created database:

Operations with tables

Create a users table with the login and password fields :

Delete table:

Useful commands in the psql interface :

Show users:

Show databases:

Show tables in the current database:

Show columns in the table:

Show PostgreSQL version:

Show the help of SQL statements:

Show the help of psql commands: