🐘 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 is no password.

Change the postgres password

# login as the postgres OS user and open the psql console
sudo -u postgres psql

# set the password for the PostgreSQL superuser account named 'postgres'
# \password <username> — prompts interactively, does not appear in history or process list
\password postgres
\q

---

💡 Tooltips

All modifications must be done with the postgres user:

Create user in database

To create a database user in postgres:

# -P prompts for a password at creation time
createuser -P 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

# set password interactively — does not appear in history or process list
\password db_user
\q

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

List all databases

To list all the databases:

# login to postgres
psql
\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