PostgreSQL installation on Ubuntu/Debian and RHEL/CentOS, creating users and databases, basic SQL commands, remote connection setup, and backup strategies. Step-by-step guide.
PostgreSQL is one of the most powerful open-source relational database management systems in the world. With ACID compliance, advanced data types, JSON support, and strong extensibility, it serves a wide range of use cases from enterprise applications to small projects. This guide covers PostgreSQL installation, basic management, and configuration.
On Ubuntu and Debian systems, PostgreSQL can be installed from official repositories:
# Update package list
apt update
# Install PostgreSQL
apt install -y postgresql postgresql-contrib
# Check service status
systemctl status postgresql
# Enable automatic startup
systemctl enable postgresql
For a more recent version, you can add the official PostgreSQL repository:
# Install required tools
apt install -y curl ca-certificates
# Add PostgreSQL signing key
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
# Add repository
echo 'deb https://apt.postgresql.org/pub/repos/apt '$(lsb_release -cs)'-pgdg main' > /etc/apt/sources.list.d/pgdg.list
# Update and install
apt update
apt install -y postgresql-16
# Add official PostgreSQL repository
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Disable the built-in PostgreSQL module
dnf -qy module disable postgresql
# Install PostgreSQL 16
dnf install -y postgresql16-server postgresql16-contrib
# Initialize the database
/usr/pgsql-16/bin/postgresql-16-setup initdb
# Start and enable the service
systemctl enable --now postgresql-16
# Check service status
systemctl status postgresql-16
psql --version
# Output: psql (PostgreSQL) 16.x
# Service info
psql -U postgres -c 'SELECT version();'
During installation, a system user named postgres is created. To connect to the database:
# Switch to the postgres system user
su - postgres
# Connect to the PostgreSQL console
psql
# Connect directly as root
sudo -u postgres psql
# Connect to a specific database
sudo -u postgres psql -d mydb
-- In the PostgreSQL console
-- Create a new user
CREATE USER appuser WITH PASSWORD 'StrongPassword123!';
-- Create a superuser (use with caution)
CREATE USER adminuser WITH PASSWORD 'AdminPass!' SUPERUSER;
-- List users
\du
-- Change user password
ALTER USER appuser WITH PASSWORD 'NewPassword456!';
-- Grant database creation privilege
ALTER USER appuser CREATEDB;
-- Create a database
CREATE DATABASE myapp OWNER appuser ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8' TEMPLATE template0;
-- List databases
\l
-- Connect to a database
\c myapp
-- Grant full privileges to user
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;
-- Schema privilege (PostgreSQL 15+)
GRANT ALL ON SCHEMA public TO appuser;
-- Drop a database
DROP DATABASE old_database;
-- Create a table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Insert data
INSERT INTO users (username, email) VALUES ('john', 'john@example.com');
-- Query data
SELECT * FROM users;
SELECT username, email FROM users WHERE id = 1;
-- Update data
UPDATE users SET email = 'new@example.com' WHERE id = 1;
-- Delete data
DELETE FROM users WHERE id = 5;
-- List tables
\dt
-- View table structure
\d users
-- Create an index
CREATE INDEX idx_users_email ON users(email);
By default, PostgreSQL only accepts connections from localhost. To enable remote access, you need to edit two files.
# Find the config file location
sudo -u postgres psql -c 'SHOW config_file;'
# Edit
nano /etc/postgresql/16/main/postgresql.conf
# Change the listen_addresses line
listen_addresses = '*'
# Port (default 5432)
port = 5432
nano /etc/postgresql/16/main/pg_hba.conf
# Add a line for remote connections (at the end of the file)
# TYPE DATABASE USER ADDRESS METHOD
host myapp appuser 192.168.1.0/24 scram-sha-256
host all all 0.0.0.0/0 scram-sha-256
# Restart the service
systemctl restart postgresql
# Open port with UFW
ufw allow 5432/tcp
# With iptables
iptables -A INPUT -p tcp --dport 5432 -j ACCEPT
Exposing the PostgreSQL port (5432) to the internet is a security risk. Use an SSH tunnel or VPN whenever possible. If remote access is required, restrict it to specific IP addresses.
# Backup a single database
pg_dump -U postgres myapp > myapp_backup.sql
# Compressed format (faster restore)
pg_dump -U postgres -Fc myapp > myapp_backup.dump
# Backup all databases
pg_dumpall -U postgres > all_databases.sql
# Backup from a remote server
pg_dump -h 192.168.1.100 -U postgres myapp > myapp_remote.sql
# Restore from SQL format
psql -U postgres myapp < myapp_backup.sql
# Restore from compressed format
pg_restore -U postgres -d myapp myapp_backup.dump
# Restore all databases
psql -U postgres < all_databases.sql
# Edit crontab
crontab -e
# Take a backup every night at 02:00
0 2 * * * pg_dump -U postgres myapp | gzip > /backup/myapp_$(date +\%Y\%m\%d).sql.gz
# Delete backups older than 30 days
0 3 * * * find /backup -name 'myapp_*.sql.gz' -mtime +30 -delete
# Start the service
systemctl start postgresql
# Stop the service
systemctl stop postgresql
# Restart the service
systemctl restart postgresql
# Reload configuration (no restart needed)
systemctl reload postgresql
# View logs
journalctl -u postgresql -f
tail -f /var/log/postgresql/postgresql-16-main.log
PostgreSQL is a reliable and high-performance database system. After installation, do not neglect security configuration, perform regular backups, and make sure to properly configure authentication methods for remote access. PostgreSQL runs smoothly on REXE servers and delivers reliable performance under high traffic.
PostgreSQL offers full ACID compliance, advanced data types (JSON, JSONB, arrays, hstore), window functions, and strong extensibility. MySQL generally offers simpler setup and management, while PostgreSQL performs better on complex queries and large datasets. PostgreSQL is recommended for new projects.
Connect to the PostgreSQL console with sudo -u postgres psql, then run ALTER USER postgres WITH PASSWORD 'NewPassword'; To change the system user password, use sudo passwd postgres.
Check three things: 1) Verify that listen_addresses = '*' is set in postgresql.conf, 2) Check that you added a host line for the relevant IP in pg_hba.conf, 3) Verify that port 5432 is open in the firewall (ufw status or iptables -L). Remember to restart the service after making changes.
pg_dump takes a consistent snapshot and is very reliable. For large databases, use -Fc (custom format); this format supports parallel restore and produces smaller file sizes. For critical systems, also consider more advanced backup methods such as WAL archiving or streaming replication.
PostgreSQL uses port 5432 by default. To change it, update the port = 5432 line in /etc/postgresql/16/main/postgresql.conf with your desired port number and restart the service. Remember to update pg_hba.conf and your connection strings as well.
Yes, each version can run on a different port. For example, PostgreSQL 14 can run on the default port 5432 and PostgreSQL 16 on port 5433. Each version has its own data directory and service unit. Use the pg_lsclusters command to list all existing clusters.
Step-by-step MySQL and MariaDB installation on Linux, mysql_secure_installation, creating databases and users, basic SQL commands, remote access configuration, and performance tuning.
Redis and Memcached installation on Ubuntu/Debian, basic commands, persistence settings, TTL configuration, Redis vs Memcached comparison, and application integration.
Step-by-step phpMyAdmin installation on Apache and Nginx, SSL configuration, user management, IP restrictions, and security hardening best practices.