Terug naar DocumentatiePostgreSQL is the most advanced open-source relational database. Very popular with Django, Rails, Node.js (Prisma, Sequelize), and modern stack applications.
02
Installation on Ubuntu/Debian
bash
sudo apt update
sudo apt install postgresql postgresql-contrib -y
# Check version and status
psql --version
sudo systemctl status postgresql
Specific version (e.g. PostgreSQL 16)
bash
# Add official PostgreSQL repository
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
# Install specific version
sudo apt install postgresql-16 -y
03
Installation on CentOS/AlmaLinux
bash
sudo dnf install postgresql-server postgresql-contrib -y
sudo postgresql-setup --initdb
sudo systemctl start postgresql
sudo systemctl enable postgresql
04
Access and postgres user
The system user postgres is the default superuser:
bash
# Access as postgres
sudo -u postgres psql
# Or
sudo su - postgres
psql
05
Database and user management
Create a database and user
sql
-- Create user
CREATE USER username WITH PASSWORD 'secure_password';
-- Create database
CREATE DATABASE dbname;
-- Assign database to user
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
-- PostgreSQL 15+: also grant the public schema
\c dbname
GRANT ALL ON SCHEMA public TO username;
\q
Useful psql commands
sql
\l -- list databases
\c dbname -- connect to a database
\dt -- list tables in current database
\du -- list users/roles
\d tablename -- structure of a table
\q -- exit
-- Database backup
-- (from shell, not from psql)
06
Backup and restore
bash
# Backup a single database
pg_dump -U username dbname > /tmp/backup.sql
# Backup with compression
pg_dump -U username -F c dbname > /tmp/backup.dump
# Backup all databases
sudo -u postgres pg_dumpall > /tmp/all-databases.sql
# Restore
psql -U username -d dbname < /tmp/backup.sql
# Restore custom format
pg_restore -U username -d dbname /tmp/backup.dump
07
Configure remote access
By default PostgreSQL accepts only localhost connections. To enable remote access:
1. Modify postgresql.conf
bash
sudo nano /etc/postgresql/16/main/postgresql.conf
# Replace 16 with your version
Find and modify:
listen_addresses = '*' # listen on all interfaces
# or: listen_addresses = 'localhost,185.100.x.x'
2. Modify pg_hba.conf
bash
sudo nano /etc/postgresql/16/main/pg_hba.conf
Add at the bottom:
# Type Database User Address Method
host dbname username 0.0.0.0/0 scram-sha-256
# To connect from a specific IP:
host dbname username 1.2.3.4/32 scram-sha-256
3. Open the port in firewall
bash
sudo ufw allow 5432/tcp
4. Restart PostgreSQL
bash
sudo systemctl restart postgresql
08
Connection from application
Node.js (with pg)
javascript
const { Pool } = require('pg')
const pool = new Pool({
host: 'localhost',
port: 5432,
database: 'dbname',
user: 'username',
password: 'secure_password',
})
Python (with psycopg2)
python
conn = psycopg2.connect(
host="localhost",
database="dbname",
user="username",
password="secure_password"
)
Django (settings.py)
python
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql',
'NAME': 'dbname',
'USER': 'username',
'PASSWORD': 'secure_password',
'HOST': 'localhost',
'PORT': '5432',
}
}
09
Basic optimizations
Modify /etc/postgresql/16/main/postgresql.conf:
ini
# Memory (adjust based on available RAM)
shared_buffers = 256MB # 25% of RAM
effective_cache_size = 1GB # 75% of RAM
work_mem = 4MB # per sort/hash operation
maintenance_work_mem = 64MB # for VACUUM, CREATE INDEX
# Connections
max_connections = 100
# WAL
wal_buffers = 16MB
checkpoint_completion_target = 0.9
bash
sudo systemctl restart postgresql
10
Monitoring
sql
-- Active connections
SELECT pid, usename, application_name, state, query
FROM pg_stat_activity
WHERE state != 'idle';
-- Database size
SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database ORDER BY pg_database_size(datname) DESC;
-- Largest tables
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC;
Gerelateerde artikelen
DeluxHost, opgericht in 2023, biedt hoogwaardige hostingoplossingen voor diverse digitale behoeften. Wij bieden gedeelde hosting, VPS en dedicated servers met geavanceerde beveiliging en wereldwijde datacenters.
© DeluxHost, Alle rechten voorbehouden. | BTW-nummer: IT17734661006
