Some basic commands for working with a PostgreSQL database.
Initially there is a user postgres. We can enter the database through postgres:
Or in one command:
Set password for postgres user:
We can also enter to PostgreSQL through other users. Before that this user must be created on Linux.
Add New Role to PostgreSQL
Before working with a new user, we must also create him in the database:
or outside postgres:
Answer the questions:
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:
Or with owner:
Outside of postgres’s user:
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.:
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
b) Or we can immediately connect to psql in one action:
If we are already logged in as a new user
a) If the username is the same as the database name
b) Not the same
Exit to root user:
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 a user for this database with a password (if not created above):
Set UTF-8 encoding:
Next, set the default transaction isolation scheme to read committed . In this case, reading from uncommitted data is blocked:
Set UTC time standard:
Grant our user access to manage the database:
Let’s see the list of databases:
If you need to delete a user:
DROP USER ploshadka;
Deleting a database: