PostgreSQL

Some basic commands for working with a PostgreSQL database.

PostgreSQL Authorization

Initially there is a user postgres. We can enter the database through postgres:

sudo su - postgres
psql

Or in one command:

sudo -u postgres psql

Set password for postgres user:

\password postgres

We can also enter to PostgreSQL through other users. Before that this user must be created on Linux.

Quit:

\q
exit

Add New Role to PostgreSQL

Before working with a new user, we must also create him in the database:

createuser --interactive

or outside postgres:

sudo -u postgres createuser --interactive

Answer the questions:

Enter name of role to add: ploshadka
Shall the new role be a superuser? (y/n) y

Create PostgreSQL Database

For authentication in PostgreSQL, our new user needs the same name as the role and database name in PostgreSQL:

createdb ploshadka

Or with owner:

createdb ploshadka --owner ploshadka

Outside of postgres’s user:

sudo -u postgres createdb ploshadka

Delete db:

dropdb ploshadka

Delete db:

dropdb ploshadka

Or if it doesn’t work then:

DROP DATABASE ploshadka;

If you see the error:

dropdb: error: database removal failed: ERROR: database “…” is being accessed by other users
DETAIL: There is 1 other session using the database.

You need to stop all processes that are using this database and repeat the command again. In Ubuntu for e.g.:

sudo systemctl stop some-service

PostgreSQL with New User

Now we will enter to PostgreSQL database through the user ploshadka. We can do this in different ways.

If we are logged in as root

a) Log in as a new user and log into PostgreSQL

su - ploshadka
psql name_of_base

b) Or we can immediately connect to psql in one action:

sudo -u ploshadka psql name_of_base

If we are already logged in as a new user

a) If the username is the same as the database name

psql

b) Not the same

psql name_of_base

Other commands

Exit to root user:

\q
exit

Check connection:

\conninfo

You are connected to database "ploshadka" as user "ploshadka" via socket in "/var/run/postgresql" at port "5432".

Work with PostgreSQL DB and User

A semicolon is required at the end of the command. Otherwise, on Ubuntu the commands may not work. But on Mac OS, the semicolon is not needed.

Create a database (if not created above):

CREATE DATABASE ploshadka;

Create a user for this database with a password (if not created above):

CREATE USER ploshadka WITH PASSWORD '123';

Set UTF-8 encoding:

ALTER ROLE ploshadka SET client_encoding TO 'utf8';

Next, set the default transaction isolation scheme to read committed . In this case, reading from uncommitted data is blocked:

ALTER ROLE ploshadka SET default_transaction_isolation TO 'read committed';

Set UTC time standard:

ALTER ROLE ploshadka SET timezone TO 'UTC';

Grant our user access to manage the database:

GRANT ALL PRIVILEGES ON DATABASE ploshadka TO ploshadka;

Let’s see the list of databases:

\list

If you need to delete a user:

DROP OWNED BY ploshadka;
DROP USER ploshadka;

Deleting a database: