PostgreSQL Database Administration Essential Training

Set up the test environment by installing pgadmin, Oracle VirtualBox, Ubuntu ISO image, and Windows ISO image or OVA file to configure a Linux server and Windows Server.

Download the latest Pgadmin release from the official site, install it on Windows, and finish setup; Pgadmin is the cross-platform tool to manage and interact with databases in the course.

Install Oracle VirtualBox by downloading the Windows host platform package, running the installer with Python core dependencies, finishing setup, and launching VirtualBox; next, download the Ubuntu desktop for this course.

Install PostgreSQL on Windows by downloading the Windows installer, selecting version 12, configuring the data directory on the D drive, setting the PostgreSQL superuser password, and launching PgAdmin 4.

Configure PostgreSQL to accept connections by backing up and editing postgresql.conf and pg_hba.conf, enabling listen_addresses, md5 authentication, restarting the server, and setting the postgres password.

Learn how to create a new database in Postgres by writing a database statement, assign an owner, choose a template, encoding, and tablespace, and set the connection limits.

Learn to modify a PostgreSQL database with the alter database statement, changing attributes such as owner, template, connection limit, and allow connections by super users or DB owners.

Rename a PostgreSQL database using the alter database syntax, after connecting to a different database and terminating active connections; only super users with create database privilege may rename.

Use the alter database command to change a database owner by specifying the database name and the new owner. Requires create db privilege or a super user like Postgres.

Use the alter database command to move a database to a new tablespace, relocating its tables and indexes, with super user or database owner privileges.

Learn how to alter a PostgreSQL database by changing the connection limit, renaming databases, managing active sessions, and changing ownership using the alter database commands and backend termination tools.

Explore PostgreSQL table spaces and how they map logical objects to physical disk locations, including default table spaces such as pkg default table space and pkg underscore global table space.

Create a tablespace with the create tablespace statement, set an owner and absolute path, and avoid names starting with pkg underscore; assign it to databases, tables, or indexes.

Use the PostgreSQL alter tablespace statement to rename a tablespace, change the owner, and set parameters, then verify changes by refreshing table spaces and reviewing properties.

Learn how to safely drop a tablespace in Postgres, including using drop tablespace with if exists, verifying dependencies, ensuring the tablespace is empty, and noting owners and super users.

Learn how PostgreSQL uses roles to manage access, create login roles and users, assign privileges, set password and expiry, limit connections, and distinguish super users from database creators.

Learn to grant privileges in PostgreSQL using the grant statement, assigning select, insert, update, delete on tables or schemas to a login role, using schema-level grants and multiple privileges.

Learn how to revoke specific or all privileges in PostgreSQL using the revoke statement, targeting tables or schemas and roles, with a practical demo showing permission changes.

Learn to use alter role in PostgreSQL to modify attributes such as super user, createdb, createrole, login, replication, connection limits, and password, and understand rules for who can change them.

Learn how to drop roles in Postgres by reassigning ownership of objects, revoking permissions, and dropping the role across databases, with steps for both superuser and non-superuser cases.

Learn how to back up a single PostgreSQL database using the pkg_dump tools, including directing output, selecting formats (custom, tar, plaintext SQL script file), and the speed of command-line backups.

Learn to back up all databases with the ppg tool, which exports a single script and hinders parallel restore, or use a shell loop for sequential backups.

Compress postgres backups with gzip, either piping during backup or after, observe significant size reductions, and use verbose output and logs to monitor any errors.

Split large Postgres backups into multiple files of a chosen size using a pipe command and the -B option, easing transfers to another OS, with directory backups covered next.

Learn how to back up a remote Postgres database from cloud infrastructure using host, port, username, password, and role parameters, highlighting differences from local backups.

Use parallelism with pg_dump to cut backup time for PostgreSQL databases, balancing jobs with CPU. In directory format, backups are compressed by default, demonstrated with the DVD rental database.

Restore PostgreSQL databases using SQL and PKG Restore tools for plaintext and custom backups, after dropping all connections. Manage errors, enable parallel restores, and restore specific objects.

Learn how pgAgent enables scheduling and automation for Postgres tasks, running as a daemon or service on the database server or a separate server, with more powerful scheduling than cron.

Install the pgAgent daemon on UNIX systems, typically with root privileges or sudo apt install ppg agent on Ubuntu; Windows offers a prebuilt installer that auto-creates the service.

Configure the PPG agent by creating the PPG agent extension in the maintenance database, running create extension PPG agent via the query tool, and setting the daemon to run jobs.

set up the pgAgent daemon by creating a PostgreSQL connection string, using a UNIX domain socket, and appending it to the agent command to run in the background.

Create a pgAgent job to insert data every minute, configuring a single step with a SQL script, selecting the target database, and validating execution via history and statistics.

Explore how periodic vacuuming keeps Postgres databases healthy, from automated vacuum demon to manual scheduling with PPG agent or CRON, and adjust parameters for your workload.

Learn how postgres vacuuming reclaims disk space by removing old row versions while preserving visibility for transactions. Compare standard vacuum with vacuum full and adopt a demand-driven, incremental vacuum strategy.

Learn how PostgreSQL uses table statistics gathered by analyze during vacuum to drive query plans, and how the art of vacuum demon keeps statistics up to date for better performance.

Update and document the visibility map for each table to mark pages with only invisible tuples, enabling vacuum to skip them and allow index-only scans to avoid table fetches.

Regularly vacuum all tables to prevent transaction ID wraparound failures. Freeze old rows so their effects stay visible to current and future transactions with periodic vacuuming every 2 billion transactions.

Automate vacuum and analyze tasks with the autovacuum daemon and its Art of Vacuum Launcher, using worker processes to distribute work across databases.