🐘 Install PostgreSQL Server

PostgreSQL also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley.

In 1996, the project was renamed to PostgreSQL to reflect its support for SQL.

---

📥 Installation

PostgreSQL can be installed easily on debian system using apt.

Install postgres

sudo apt install postgresql postgresql-client

---

⚙️ Configuration

The default user is postgres and there are no password.

Change the postgres password

# login with account root to postgres user
su
su postgres

# create psql admin password
psql -c "ALTER USER postgres WITH password 'password'"

Important

Replace 'password' with your password.

---

💡 Tooltips

All modifications must be done with the postgres user:

Create user in database

To create a database user in postgres:

createuser db_user

Replace db_user by your user.

Create database with user access

To create a database with specific user access:

createdb my_database -O db_user

Replace my_database by your database name.
Replace db_user by your database user.

Assign password to user

Create a user password:

# login to database
psql my_database

# create user password
ALTER USER db_user WITH password 'password';
ALTER ROLE
\q

Replace my_database by your database name.
Replace db_user with your database user.
Replace 'password' with your password.

List all databases

To list all the databases:

# login to database
psql my_database
\list

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

Remove database

To remove a database:

dropdb my_database