Configuring PostgreSQL on Linux

Configure PostgreSQL on Linux

What is PostgreSQL?

PostgreSQL is a powerful and advanced open-source relational database management system. It's a robust alternative to MySQL, especially useful for applications that require advanced features like custom data types, complex functions, and ACID transactions.

Install PostgreSQL

On Ubuntu/Debian:

sudo apt update
sudo apt install postgresql postgresql-contrib

On CentOS/RHEL:

sudo yum install postgresql-server postgresql-contrib
sudo postgresql-setup initdb

On Fedora:

sudo dnf install postgresql-server postgresql-contrib
sudo postgresql-setup --initdb

Start and enable PostgreSQL

sudo systemctl start postgresql
sudo systemctl enable postgresql

Verify it's running:

sudo systemctl status postgresql

Access PostgreSQL

PostgreSQL creates a user called postgres by default. To access:

sudo -u postgres psql

Or switch to postgres user first:

sudo su - postgres
psql

Initial configuration

1. Change postgres user password

sudo -u postgres psql
ALTER USER postgres PASSWORD 'your_secure_password';
\q

2. Create a new user and database

sudo -u postgres createuser --interactive username
sudo -u postgres createdb database_name -O username

Or from psql:

sudo -u postgres psql
CREATE USER username WITH PASSWORD 'password';
CREATE DATABASE database_name OWNER username;
GRANT ALL PRIVILEGES ON DATABASE database_name TO username;
\q

Configure remote access (optional)

1. Edit configuration file

sudo nano /etc/postgresql/[version]/main/postgresql.conf

Find and modify:

listen_addresses = '*'

2. Configure authentication

sudo nano /etc/postgresql/[version]/main/pg_hba.conf

Add at the end of the file:

# Allow remote connections
host    all             all             0.0.0.0/0               md5

3. Restart PostgreSQL

sudo systemctl restart postgresql

4. Configure firewall

sudo ufw allow 5432/tcp

Basic PostgreSQL commands

Connect to a database:

psql -U username -d database_name

Useful commands inside psql:

• \l - List all databases
• \c database_name - Connect to a database
• \dt - List all tables
• \d table_name - Describe a table
• \du - List all users
• \q - Exit psql
• \h - SQL command help
• \? - psql command help

Create and manage tables

-- Create a table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');

-- Query data
SELECT * FROM users;

-- Update data
UPDATE users SET name = 'John Doe' WHERE id = 1;

-- Delete data
DELETE FROM users WHERE id = 1;

Manage users and permissions

-- Create user
CREATE USER new_user WITH PASSWORD 'password';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE database_name TO new_user;

-- Grant privileges on specific tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO new_user;

-- Revoke privileges
REVOKE ALL PRIVILEGES ON DATABASE database_name FROM new_user;

-- Delete user
DROP USER username;

Backup and restore

Full backup:

sudo -u postgres pg_dump database_name > backup.sql

Compressed backup:

sudo -u postgres pg_dump -Fc database_name > backup.dump

Restore backup:

sudo -u postgres psql database_name < backup.sql

Restore compressed backup:

sudo -u postgres pg_restore -d database_name backup.dump

Optimize PostgreSQL

1. Configure performance parameters

sudo nano /etc/postgresql/[version]/main/postgresql.conf

Important parameters:

shared_buffers = 256MB          # Shared memory
effective_cache_size = 1GB       # Available memory for cache
maintenance_work_mem = 64MB     # Memory for maintenance operations
work_mem = 16MB                 # Memory for sorting operations

2. Analyze and optimize tables

ANALYZE table_name;
VACUUM table_name;
VACUUM FULL table_name;  # Completely reorganizes the table

Check status and logs

See active processes:

sudo -u postgres psql -c "SELECT * FROM pg_stat_activity;"

See database size:

sudo -u postgres psql -c "SELECT pg_size_pretty(pg_database_size('database_name'));"

See logs:

sudo tail -f /var/log/postgresql/postgresql-[version]-main.log

Common problems

Error: "peer authentication failed"

• Modify pg_hba.conf to use md5 instead of peer.
• Restart PostgreSQL after the change.

Error: "could not connect to server"

• Verify PostgreSQL is running: sudo systemctl status postgresql
• Check listen_addresses configuration.
• Check firewall.

Error: "permission denied"

• Verify user permissions on the database.
• Check pg_hba.conf configuration.

Main differences with MySQL

• PostgreSQL uses SERIAL instead of AUTO_INCREMENT
• Commands end with ; but there are also special commands that start with \
• PostgreSQL is stricter with data types
• PostgreSQL supports more advanced data types (arrays, JSON, etc.)

Need help?

If you're having trouble configuring PostgreSQL, open a ticket from the billing.baires.host panel or contact us for support.

You can also reach us through our social media:

• Instagram: @baires_host
• Discord (active support): https://discord.gg/dzjauatAFN
• Linktree: https://linktr.ee/baires.host