• Introduction to PostgreSQL Server and Advance Features
• Postgres Client Server Architecture
• Getting Started
• Installing the PostgreSQL Server
• Setting Environment Variables
• Creating a Cluster
• Running Server
• Configuring of PostgreSQL Server
• Connection Settings
• Security and Authentication
• Resource Settings
• WAL
• Error Reporting and Logging
• Autovacuum
• Runtime Statistics, etc.
• Server Control
• Postgres hosed based access configuration
• Postgres hosed based access configuration
• Client and Tools
• Introduction To PSQL
• Commands and Parameters
• Using graphical administration tools- pgAdmin
• Creating and Managing Databases
• Object Hierarchy
• Databases and Schemas
• Tablespaces
• Exploring Databases
• Locating the database server's message log
• Locating the database's system identifier
• Listing databases on this database server
• How much disk space does a table use?
• Which are my biggest tables?
• How many rows are there in a table?
• Quickly estimating the number of rows in a table
• Understanding object dependencies
• Obtaining Metadata
• Transactions & Concurrency Control
• Database Administration
• Performing actions on many tables
• Writing a script
• Adding/removing schemas
• Moving objects between schemas
• Adding/removing tablespaces s
• Moving objects between tablespaces
• Using materialized views
• Table Partitioning
• Extensions
• Accessing objects in other PostgreSQL databases (postgres_fdw, dblink)
• File_fdw, hstore, pgcrypto,etc.
• Security
• User Management
• Superuser
• Roles and Users
• Groups and Access Control
• Ownership, Etc. • Preventing Connections
• Checking secure password
• Auditing Changes
• Encrypting Sensitive data
• Monitoring and Diagnosis
• Real-time viewing using pgAdmin
• Checking whether a user is connected
• Checking which queries are running
• Checking which queries are active or blocked
• Knowing who is blocking a query
• Killing a specific session
• Knowing when a table was last used
• Usage of disk space by temporary data
• Understanding why queries slow down
• Producing a daily summary of log file errors
• Analyzing the real-time performance of your queries
• Performance and Concurrency
• Find and Tune Slow Running Queries
• Collecting regular statistics from pg_stat* views
• Finding out what makes SQL slow
• Speeding up queries without rewriting them
• Discovering why a query is not using an index
• Forcing a query to use an index
• Regular Maintenance
• Controlling automatic database maintenance
• Removing issues that cause bloat
• Identifying and fixing bloated tables and indexes
• Monitoring and tuning vacuum
• Updating Table Statistics
• Vacuuming
• Re-indexing
• Backup and Recovery
• Planning backups
• Backup Types
• Logical
• Pg_dump
• Pg_dumpall
• Physical
• Standalone hot physical database backup
• Hot physical backup and continuous archiving
• PgBaseBackup
• Restore
• Pg_restore
• Recovery to a point in time
• Restore Physical Backup
• Recovery of a dropped/damaged table
• Recovery of a dropped/damaged database
• Moving Data
• Exporting/Importing Data To/From A Flat File
• Replication and Upgrades
• Replication
• Replication best practices
• Streaming Replication
• Implement Hot Standby
• Replication Slots
• Logical Replication
• Using repmgr
• Handling Switchover & Failover
• Upgrading Best Practices
• Upgrading - minor releases
• Upgrading - major release(pg_upgrade)
• Migration from Oracle to Postgres using Ora2PG
Write a public review