How to Install PostgreSQL on Debian 12
Introduction
PostgreSQL is an open-source relational database management system (RDBMS) that supports Structured Query Language (SQL). The database server is suitable for storing data and running queries in a wide variety of information systems. PostgreSQL's most notable performance features include multi-version concurrency control, transaction support, foreign keys, user-defined data types, and custom views.
This article explains how to install and enable access control for the PostgreSQL database server on Debian 12.
Prerequisites
Before you begin:
- Deploy a Debian 12 Cloud Compute instance on Vultr.
- Access the instance using SSH.
- Create a non-root user with sudo privileges and switch to the user.
- Update the instance.
Install PostgreSQL
PostgreSQL is available in the default package repositories on Debian 12. Follow the steps below to install PostgreSQL using APT.
Install
postgresql-common
package to create the latest PostgreSQL version information.console$ sudo apt install -y postgresql-common
The above package creates a new repository information script (
/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
) containing the latest PostgreSQL version details.Run the PostgreSQL repository setup script.
console$ sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
Output:
This script will enable the PostgreSQL APT repository on apt.postgresql.org on your system. The distribution codename used will be bookworm-pgdg. Press Enter to continue, or Ctrl-C to abort.
Press Enter to add the repository when prompted.
Update the package information index.
console$ sudo apt update
Display all the available PostgreSQL versions that you can install.
console$ sudo apt-cache policy postgresql
Output:
postgresql: Installed: (none) Candidate: 16+262.pgdg120+1 Version table: 16+262.pgdg120+1 500 500 https://apt.postgresql.org/pub/repos/apt bookworm-pgdg/main amd64 Packages 15+248 500 500 https://deb.debian.org/debian bookworm/main amd64 Packages 500 https://debian.mirror.constant.com bookworm/main amd64 Packages
Based on the above output:
- No PostgreSQL version is installed on the server.
- PostgreSQL version
16
is available in the PostgreSQL repository. - PostgreSQL version
15
is available in the default Debian 12 repository.
Install the PostgreSQL database server package.
console$ sudo apt install postgresql -y
Manage the PostgreSQL System Service
The PostgreSQL database server runs under the postgresql
system service. Follow the steps below to manage the PostgreSQL service using systemctl
.
Enable the PostgreSQL service to automatically start at boot.
console$ sudo systemctl enable postgresql
Start the PostgreSQL service.
console$ sudo systemctl start postgresql
View the PostgreSQL service status and verify that it's running.
console$ sudo systemctl status postgresql
Output:
ā postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; preset: enabled) Active: active (exited) since Tue 2024-08-20 02:04:21 UTC; 12min ago Main PID: 1971 (code=exited, status=0/SUCCESS) CPU: 1ms Aug 20 02:04:21 postgresql-demo systemd[1]: Starting postgresql.service - PostgreSQL RDBMS... Aug 20 02:04:21 postgresql-demo systemd[1]: Finished postgresql.service - PostgreSQL RDBMS.
Stop the PostgreSQL service.
console$ sudo systemctl stop postgresql
Restart the PostgreSQL service.
console$ sudo systemctl restart postgresql
Secure the PostgreSQL Server
By default, PostgreSQL creates an administrative postgres
user. The user authenticates without a password using the Unix domain socket. Follow the steps below to enable password authentication to secure PostgreSQL:
Access the PostgreSQL database console as the user
postgres
.console$ sudo -u postgres psql
Modify the user to use password authentication. Replace
Secur3Passw0rd
with a strong password.sqlpostgres=# ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'Secur3Passw0rd';
Exit the database console.
sqlpostgres=# \q
Open the
/etc/postgresql/16/main/pg_hba.conf
main PostgreSQL configuration file using a text editor likenano
.console$ sudo nano /etc/postgresql/16/main/pg_hba.conf
Find the following configuration directive.
inilocal all postgres peer
Edit the last column and change it from
peer
toscram-sha-256
to enable password authentication for the localpostgres
user.inilocal all postgres scram-sha-256
Find the following directive for all PostgreSQL users.
inilocal all all peer
Replace
peer
withscram-sha-256
to enable password authentication.inilocal all all scram-sha-256
Save and close the file.
Restart PostgreSQL to apply the configuration changes.
console$ sudo systemctl restart postgresql
Access the PostgreSQL console as
postgres
.console$ psql -U postgres -W
Enter the password you set earlier and press :key_enter when prompted.
Create a new
db_admin
database role and grantLOGIN
andCREATEDB
privileges to allow the user to log in to the database server and create databases.sqlpostgres=# CREATE ROLE db_admin WITH LOGIN CREATEDB ENCRYPTED PASSWORD 'MyS3curePassWD!';
Access PostgreSQL
PostgreSQL includes the psql
CLI utility you can use to access the database console by default. You can also use graphical tools such as pgAdmin to manage the PostgreSQL databases. Follow the steps below to access the PostgreSQL database server using the psql
utility:
Access the PostgreSQL database server as
db_admin
and enter the password you created earlier.console$ psql -U db_admin -d postgres -W
Create a sample
school
database.sqlpostgres=> CREATE DATABASE school;
Switch to the new database.
sqlpostgres=> \c school;
Enter
db_admin
role and press Enter when prompted.Output:
You are now connected to database "school" as user "db_admin".
Create a sample
teachers
table with4
columns.sqlschool=> CREATE TABLE teachers ( staff_id SERIAL PRIMARY KEY, first_name CHARACTER VARYING (100) NOT NULL, last_name CHARACTER VARYING (100) NOT NULL, joining_date DATE NOT NULL );
The above command creates a new table with the following column specifications:
staff_id
: Uses theSERIAL
data type and aPRIMARY KEY
constraint.first_name
andlast_name
: Use theCHARACTER VARYING
data type to store variable length data with a limit of100
characters and aNOT NULL
constraint to restrict empty values.joining_date
: Uses theDATE
data type to store date data.
Add sample data to the
teachers
table.sqlschool=> INSERT INTO teachers (first_name, last_name, jioning_date) VALUES ('Emily', 'Pritt', '08-22-2024'), ('Dennis', 'Jacobs', '08-13-2024');
Query the
teachers
table to view the available records.sqlschool=> SELECT * FROM teachers;
Output:
staff_id | first_name | last_name | joining_date ----------+------------+-----------+-------------- 1 | Emily | Pritt | 2024-08-22 2 | Dennis | Jacobs | 2024-08-13 (2 rows)
Exit the PostgreSQL console.
sqlschool=> \q
Conclusion
You installed PostgreSQL on Debian 12, created a sample database, and used psql
to access the database server. You can deploy PostgreSQL in your web applications to store data, set up a data warehouse for data analytics, or handle geographical data. For more information, visit the PostgreSQL documentation.