Linux/Shell scripts

Linux/Shell Scripts

Linux and Shell Scripts

Linux and shell scripts are powerful tools for automation and system management. Whether you're a beginner or an advanced user, mastering shell scripting can help streamline tasks, automate processes, and improve efficiency.

  • Linux & Shell scripts

    ✔ Linux Installation

    ✔ Basic Commands
    ✔ Permissions Commands
    ✔ Search Commands
    ✔ System Commands
    ✔ File Commands

    ✔ Editors - Vim
    ✔ Compression Commands
    ✔ Job Scheduling
    ✔ sysadmin Commands
    ✔ Network Commands
    ✔ File transfer Commands
    ✔ Shell - types

    ✔ Env. Variables
    ✔ Operators
    ✔ Arguments
    ✔ Conditions & Loops
    ✔ Scripts for log monitoring
    ✔ Scripts for DB and Instance monitoring

Postgres SQL - for Developers

SQL - Postgres

Postgresql SQL - Structured Query Language

SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases. It is widely used for querying, inserting, updating, and deleting data in databases. SQL is essential for database developers and administrators to interact with databases effectively. This section covers the fundamental concepts and commands of SQL, including data definition, data manipulation, and data control.

  • Introduction to Postgres SQL

    ✔ Connecting psql

    ✔ Logging in pgadmin
    ✔ Connecting to Database on windows and Linux

  • Querying Data

    ✔ Select
    ✔ Column aliases
    ✔ Order By
    ✔ Select Distinct

  • Filtering Data

    ✔ Where
    ✔ AND operator
    ✔ OR operator
    ✔ Limit
    ✔ Fetch
    ✔ In
    ✔ Between
    ✔ Like
    ✔ Is Null

  • Functions and sorting data

    ✔ Sorting rows using ORDER BY clause

    ✔ SQL row limiting clause in a query
    ✔ Describe the differences between single row and multiple row functions
    ✔ Manipulate strings with character function in the SELECT and WHERE clauses
    ✔ Manipulate numbers with the ROUND, TRUNC and MOD functions
    ✔ Perform arithmetic with date data
    ✔ Manipulate dates with the date functions

  • Joining Multiple Tables

    ✔ Joins
    ✔ Table aliases
    ✔ Inner Join
    ✔ Left Join
    ✔ Self-join
    ✔ Full Outer Join
    ✔ Cross Join
    ✔ Natural Join

  • Grouping Data

    ✔ Managing constraints

    ✔ Group By
    ✔ Having

  • Set Operations

    ✔ Union
    ✔ Intersect
    ✔ Except

  • Grouping sets, Cubes, and Rollups

    ✔ Grouping Sets
    ✔ Cube
    ✔ Rollup

  • Subquery

    ✔ Subquery
    ✔ Correlated Subquery
    ✔ ANY
    ✔ ALL
    ✔ EXISTS

  • Common Table Expressions

    ✔ PostgreSQL CTE
    ✔ Recursive query using CTEs

  • Modifying Data

    ✔ Insert
    ✔ Insert multiple rows
    ✔ Update
    ✔ Update join
    ✔ Delete
    ✔ Upsert

  • Transactions

    ✔ PostgreSQL Transactions

  • Import & Export Data

    ✔ Import CSV file into Table
    ✔ Export PostgreSQL Table to CSV file

  • Managing Tables

    ✔ Data types
    ✔ Create a table
    ✔ Select Into & Create table as
    ✔ Auto-increment column with SERIAL
    ✔ Sequences
    ✔ Identity column
    ✔ Alter table
    ✔ Rename table
    ✔ Add column
    ✔ Drop column
    ✔ Change column data type
    ✔ Rename column
    ✔ Drop table
    ✔ Truncate table
    ✔ Temporary table
    ✔ Copy a table

  • PostgreSQL Constraints

    ✔ Primary key
    ✔ Foreign key
    ✔ DELETE CASCADE
    ✔ CHECK constraint
    ✔ UNIQUE constraint
    ✔ NOT NULL constraint
    ✔ DEFAULT constraint

  • PostgreSQL Data Types in Depth

    ✔ Boolean
    ✔ CHAR, VARCHAR, and TEXT
    ✔ NUMERIC
    ✔ DOUBLE PRECISION
    ✔ REAL
    ✔ Integer
    ✔ DATE
    ✔ Timestamp
    ✔ Interval
    ✔ TIME
    ✔ UUID
    ✔ Array
    ✔ hstore
    ✔ JSON
    ✔ User-defined data types
    ✔ Enum
    ✔ XML
    ✔ BYTEA
    ✔ Composite Types

  • Conditional Expressions & Operators

    ✔ CASE
    ✔ COALESCE
    ✔ NULLIF
    ✔ CAST

PostgreSQL DBA

PostgreSQL Database Administration

PostgreSQL DBA

PostgreSQL is an open-source relational database management system (RDBMS) that is known for its robustness, extensibility, and SQL compliance. It is widely used for various applications, from small projects to large-scale enterprise systems. PostgreSQL is known for its advanced features, including support for complex queries, transactions, and data integrity. PostgreSQL is a powerful and flexible database system that is suitable for a wide range of applications.

  • Introduction & Installation

    ✔ Database Basics and Database Types
    ✔ PostgreSQL: Advantages, Usage
    ✔ PostgreSQL as an Open Source Database
    ✔ PostgreSQL: History and Versions
    ✔ PostgreSQL: Real-time Usage
    ✔ PostgreSQL Installation on LINUX OS
    ✔ bin, data, and PATH Concepts
    ✔ Command Line Tools
    ✔ Post Installation: Environment Variables, Path, bin, and PGDATA Variables

  • PostgreSQL Architecture

    ✔ PostgreSQL Server Architecture
    ✔ Server Process and Client Process
    ✔ Postgres Program: Internal Process
    ✔ Postmaster Process and Forks
    ✔ Background Process, Connections
    ✔ Utility Processes: Bgwriter, WAL
    ✔ Checkpointer and StatsCollector
    ✔ Log Writer, Autovacuum Utilities
    ✔ Memory Segments and Usage
    ✔ Shared Buffer and WAL Buffer
    ✔ Dirty Data and Background Writer
    ✔ Background Process, Backend Process

  • Cluster Creation

    ✔ initdb Utility Introduction
    ✔ Creating Cluster using initdb
    ✔ Changing Port Number of a New Cluster

  • Database Creation and Maintenance

    ✔ Default Databases in Postgres: postgres, template0, template1
    ✔ Database Creation from Bash
    ✔ Database Creation from DB Level

  • Schema Creation and Maintenance

    ✔ Default Schema: public
    ✔ Creating Schema
    ✔ Creating Table Inside Schema
    ✔ Setting Default Schema
    ✔ Moving Tables Across Schemas

  • Tablespace Creation and Maintenance

    ✔ Tablespaces and Real-time Advantages
    ✔ pg_default, pg_global Tablespaces
    ✔ Creating and Modifying Tablespaces
    ✔ Creating Tables Inside Tablespace

  • Partitions

    ✔ Range partition
    ✔ List partition
    ✔ Hash Partition
    ✔ Composite Partitions
    ✔ Vertical Partitions

  • Users, Roles, and Privileges

    ✔ User Creation
    ✔ Assigning Privileges to User
    ✔ Dropping User
    ✔ Assigning Privileges to Role
    ✔ Assigning Role to User
    ✔ Privileges Management

  • Job Scheduling

    ✔ pg_cron Introduction
    ✔ Installing pg_cron
    ✔ Configuring pg_cron Parameters
    ✔ Scheduling Jobs

  • Accessing Other Databases

    ✔ DBlink
    ✔ Foreign Data Wrapper (FDW)

  • Backup & Restore - Part 1

    ✔ Backups: Types, Levels in Postgres
    ✔ Logical and Physical Backups
    ✔ Backup Tools: pg_dump, pg_dumpall
    ✔ Online and Offline Backups
    ✔ Logical Backups: Single Table, Multiple Tables, Single Database
    ✔ Logical Backup Options and Usage
    ✔ pg_backrest – Third-Party Backup Tool

  • Backup & Restore - Part 2

    ✔ wal_level and archive_mode Options
    ✔ archive_command with File Formats
    ✔ WAL Archival Process
    ✔ Point-In-Time Recovery (PITR)
    ✔ pg_switch_wal, pg_basebackup Utilities
    ✔ Physical Backups and Continuous Archival

  • Performance Tuning

    ✔ Transactions and Lock Management
    ✔ Table Level Locks, Row Level Locks
    ✔ Query Blocking and Deadlocks
    ✔ Lock Queue, Lock Management Options
    ✔ Page Locks, Shared and Exclusive Locks
    ✔ pg_stat_activity
    ✔ pg_cancel_backend, pg_terminate_backend
    ✔ Vacuum, Vacuum Full, Freeze, Analyze
    ✔ Partitioning
    ✔ pg_badger - Log Analysis Tool

  • Upgrade

    ✔ pg_upgrade Utility
    ✔ Checking Consistency
    ✔ Deleting Old Cluster

  • Migration

    ✔ ora2pg Introduction
    ✔ Downloading Required Files
    ✔ Setting Migration Parameters
    ✔ Migrating Data

  • High Availability & Disaster Recovery (HA-DR) with Postgres

    ✔ PostgreSQL Replication and Advantages
    ✔ WAL Sender, WAL Receiver, Startup
    ✔ pg_hba.conf Entries for Replication Host
    ✔ pg_basebackup Utility
    ✔ Single Master Replication Setup
    ✔ recovery.conf File and Configurations
    ✔ primary_conninfo and standby_mode
    ✔ Replication Setup Validations
    ✔ pg_stat_replication Monitoring
    ✔ PgBouncer Introduction
    ✔ Installing PgBouncer
    ✔ Configuring PgBouncer

  • Tools and Extensions

    ✔ pg_hint_plan
    ✔ pg_audit
    ✔ pg_stat_statements
    ✔ barman