PostgreSQL

# Update package manager
sudo apt update
 
# Install PostgreSQL
sudo apt install postgresql postgresql-contrib -y
 
# Start and enable PostgreSQL
sudo systemctl start postgresql
sudo systemctl enable postgresql
 
# Verify installation
sudo systemctl status postgresql
psql --version

# Install repository
sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y
 
# Install PostgreSQL
sudo yum install postgresql13-server postgresql13-contrib -y
 
# Initialize database
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
 
# Start and enable PostgreSQL
sudo systemctl start postgresql-13
sudo systemctl enable postgresql-13

# Switch to postgres user
sudo -i -u postgres
 
# Access psql prompt
psql
 
# Change postgres password
ALTER USER postgres WITH PASSWORD 'new_password';
FLUSH PRIVILEGES;
\q

# Connect as postgres
sudo -u postgres psql
 
# Create new database
CREATE DATABASE dbname;
 
# Create new user
CREATE USER username WITH PASSWORD 'password';
 
# Grant privileges
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
 
# List databases
\l
 
# List users/roles
\du
 
# Connect to database
\c dbname
 
# Exit psql
\q

# Start PostgreSQL
sudo systemctl start postgresql
 
# Stop PostgreSQL
sudo systemctl stop postgresql
 
# Restart PostgreSQL
sudo systemctl restart postgresql
 
# Check status
sudo systemctl status postgresql
 
# View logs
sudo tail -f /var/log/postgresql/postgresql-*.log

# Find PostgreSQL configuration directory
sudo find / -name "postgresql.conf"
 
# Example: /etc/postgresql/13/main/postgresql.conf
sudo nano /etc/postgresql/13/main/postgresql.conf
 
# Change listen_addresses
listen_addresses = '*'

# Edit pg_hba.conf
sudo nano /etc/postgresql/13/main/pg_hba.conf
 
# Add remote connection (change version as needed)
host    all             all             0.0.0.0/0               md5
host    all             all             ::/0                    md5
 
# Restart PostgreSQL
sudo systemctl restart postgresql

# UFW (Ubuntu)
sudo ufw allow 5432
 
# Firewalld (CentOS)
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload

# Backup single database
sudo -u postgres pg_dump dbname > dbname.sql
 
# Backup all databases
sudo -u postgres pg_dumpall > all_databases.sql
 
# Restore database
sudo -u postgres psql dbname < dbname.sql
 
# Restore all databases
sudo -u postgres psql < all_databases.sql
 
# Backup to compressed format
sudo -u postgres pg_dump -F c dbname > dbname.dump
 
# Restore from compressed
sudo -u postgres pg_restore -d dbname dbname.dump

# Main configuration
sudo nano /etc/postgresql/13/main/postgresql.conf
 
# Common settings
max_connections = 200
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 64MB
random_page_cost = 1.1
log_statement = 'all'
log_duration = on

# Stop PostgreSQL
sudo systemctl stop postgresql
 
# Remove PostgreSQL
sudo apt remove postgresql postgresql-contrib -y
sudo apt autoremove -y
 
# Remove data
sudo rm -rf /var/lib/postgresql
sudo rm -rf /etc/postgresql
sudo rm -rf /var/log/postgresql

# Stop PostgreSQL
sudo systemctl stop postgresql-13
 
# Remove PostgreSQL
sudo yum remove postgresql13-server postgresql13-contrib -y
 
# Remove data
sudo rm -rf /var/lib/pgsql