Granting Permissions to Postgres Roles

Granting permissions to sequences

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO www;

Change owners for all tables in Postgres

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
    LOOP
        EXECUTE 'ALTER TABLE public.' || quote_ident(r.tablename) || ' OWNER TO new_owner';
    END LOOP;
END $$;

Fixing a collation mismatch

REINDEX DATABASE <you_database_name>;

ALTER DATABASE <your-database-name> REFRESH COLLATION VERSION;

Sample Backup script in bash

Source: Software Development and Beyond

# Database credentials (replace with your details)
DB_HOST="localhost"
DB_NAME="your_database_name"
DB_USER="your_db_user"
DB_PASSWORD="your_db_password"# Backup directory
BACKUP_DIR="/var/backups/postgres"# Backup rotation (number of backups to keep)
BACKUP_ROTATE=7# Recipient email address
RECIPIENT_EMAIL="[email protected]"# Function to send email notification
send_email_alert() {
  subject="$1: PostgreSQL Backup Script"
  body="$2"
  echo -e "$body" | mail -s "$subject" "$RECIPIENT_EMAIL"
}# Create timestamped backup filename
timestamp=$(date +%Y-%m-%d_%H-%M)
backup_file="$BACKUP_DIR/postgres_$timestamp.sql.gz"# Dump database and compress the backup
pg_dump -h "$DB_HOST" -U "$DB_USER" -f "$backup_file" "$DB_NAME" &> /dev/null
if [[ $? -eq 0 ]]; then
  # Backup successful
  send_email_alert "Success" "The PostgreSQL database backup completed successfully and is stored at: $backup_file"
  
  # Rotate backups (delete oldest if exceeding retention limit)
  count=$(ls -tr "$BACKUP_DIR" | grep '^postgres_' | wc -l)
  if [[ $count -gt $BACKUP_ROTATE ]]; then
    rm -f "$BACKUP_DIR"/*.sql.gz $(ls -tr "$BACKUP_DIR" | grep '^postgres_' | head -n -$((count - BACKUP_ROTATE)))
  fi
else
  # Backup failed
  send_email_alert "Failure" "The PostgreSQL database backup failed. Please check the script logs for details."
fiecho "PostgreSQL database backup script complete."