Install PostgreSQL on Debian

Step-by-step guide to installing and configuring PostgreSQL on Debian, including user roles, database setup and firewall settings.

To install and configure PostgreSQL on your Debian, follow these steps:

Step 1: Install PostgreSQL

1. Update your package lists to ensure you have the latest information about available packages:

   sudo apt update

2. Install PostgreSQL and the PostgreSQL contrib package (which includes additional utilities):

   sudo apt install postgresql postgresql-contrib

3. Once the installation is complete, verify the installation and check the status:

   sudo systemctl status postgresql

Step 2: Configure PostgreSQL

1. Switch to the PostgreSQL user :

   sudo -i -u postgres

2. Access the PostgreSQL prompt :

   psql

3. Create a new PostgreSQL role :

   CREATE ROLE your_user_name WITH LOGIN PASSWORD 'your_password';

   Replace your_user_name with your desired username and your_password with the desired password.

4. Grant privileges to the new role :

   ALTER ROLE your_user_name WITH SUPERUSER;

5. Create a new database (optional) :

   CREATE DATABASE your_database_name OWNER your_user_name;

6. Exit PostgreSQL :

   \q

7. Test the connection :

   psql -U your_user_name -d your_database_name -h 127.0.0.1 -W

8. Close the psql session:

   Ctrl+D

Step 4: Allow PostgreSQL Through the Firewall (if applicable)

If using ufw as your firewall, allow PostgreSQL’s default port:

sudo ufw allow 5432/tcp

Step 5: Verify the Installation

psql -U your_user_name -d your_database_name

Enable PostgreSQL to Start on Boot

Ensure PostgreSQL starts automatically after a reboot:

sudo systemctl enable postgresql

That’s it! You now have a fully functioning PostgreSQL installation, ready for local or remote connections.