PostgreSQL Tutorial: PostgreSQL Administration

The PostgreSQL administration covers the most important PostgreSQL database server administration activities.

Section 1. Managing Databases

In this section, you will learn how to manage databases in PostgreSQL including creating databases, modifying existing database features, and deleting databases.

• Create Database – create a new database using CREATE DATABASE statement.
• Alter Database – modify the features of an existing database using the ALTER DATABASE statement.
• Rename Database – change the name of the database to a new one.
• Drop Database – removes a database permanently using the DROP DATABASE statement.
• Copy a Database – copy a database within a database server or from a server to another.
• Get Database Object Sizes – introduce you to various handy functions to get the size of a database, a table, and indexes.
• Locales and encodings – introduce you the locales and encodings in PostgreSQL.

Section 2. Managing Schemas

• Schema – introduce the schema concept and explains how the schema search path works in PostgreSQL.
• Create Schema – show you how to create a new schema in a database.
• Alter Schema – rename a schema or changes its owner to the new one.
• Drop schema – delete one or more schemas with their objects from a database.

Section 3. Managing Tablespaces

PostgreSQL tablespaces allow you to control how data stored in the file system. The tablespaces are very useful in many cases such as managing large tables and improving database performance.

• When to use tablespaces? – learn when tablespaces are useful in PostgreSQL.
• Creating Tablespaces – introduce you to PostgreSQL tablespaces and shows you how to create tablespaces by using CREATE TABLESPACE statement.
• Changing Tablespaces – show you how to rename, change owner and set the parameter for a tablespace by using ALTER TABLESPACE statement.
• Moving a Tablespace – learn how to move a tablespace in PostgreSQL.
• Delete Tablespaces – learn how to delete tablespaces by using DROP TABLESPACE statement.
• Using temporary tablespaces – learn how to use temporary tablespaces, when and why to use it.

Section 4. Roles & Privileges

PostgreSQL represents accounts as roles. Roles that can log in called login roles or users. Roles that contain other roles are called group roles. In this section, you will learn how to manage roles and groups effectively.

• Create role - introduce you to roles concept and show you how to create roles and groups by using the CREATE ROLE statement.
• Grant – show you how to grant privileges on database objects to a role.
• Revoke – guide you on revoking granted privileges on database objects from a role.
• Performing authorization using roles and privileges – show you how to perform authorization using roles and privileges in PostgreSQL.
• Assigning Privileges – show you how to assign privileges in PostgreSQL.
• Alter role – show you how to use the ALTER ROLE statement to modify the attributes of roles, rename roles, and set the configuration parameters.
• Drop role – learn how to drop a role especially a role that has dependent objects.
• Role membership – learn how to create group roles to better manage role membership.
• SET ROLE – show you how to use the SET ROLE statement to temporarily change the current role within a database session.
• CURRENT_USER – discover how to get the currently logged-in user and show you the difference between current_user and session_user.
• List user roles – show you how to list all roles on the PostgreSQL server.
• Manage superusers – introduce you how to manage superusers in PostgreSQL.
• Row Level Security – show you how to use row-level security (RLS) to restrict rows returned by a query based on a condition.
• Column Level Security – show you how to enable column-level security to control access to individual columns in a table.

Section 5. Security Administration

• Configuring encryption of communication using SSL – show you how to setup the encryption of data over communication using SSL in PostgreSQL.
• Enabling certificate authentication using SSL – show you how to enable certificate authentication using SSL in PostgreSQL.
• Audit through Logging – show you how to setup audit logging in PostgreSQL.
• Check password strength – guide you on how to check password strength in PostgreSQL.
• Protecting Against SQL Injection – guide you on how to secure PostgreSQL against SQL injection attacks.

Section 6. Backup & Restore Databases

This section shows you how to use various PostgreSQL backup and restore tools including pg_dump, pg_dumpall, psql, pg_restore, pg_basebackup and pgAdmin to backup and restore databases.

• Backup databases using pg_dump – introduce you to practical ways to back up your databases by using PostgreSQL backup tool including pg_dump and pg_dumpall.
• Restore a database backed up using pg_dump – show you various ways to restore PostgreSQL databases by using psql and pg_restore tools.
• Backing up a database cluster using pg_basebackup – show you how to use pg_basebackup to back up your database cluster.
• Restore a backup taken using pg_basebackup – show you how to restore a backup taken using pg_basebackup.
• Automatically Backup Database – show you how to automate your backup processes in PostgreSQL.
• Enable WAL Archiving – show you how to enable WAL archiving in PostgreSQL.
• Setting WAL Archiving Command – show you how to setup WAL archiving command in PostgreSQL.
• Incremental Backup – show you how to perform incremental backups in PostgreSQL.

Section 7. Replication Techniques

Replication is one of the most important aspects of achieving high availability. Any unexpected failures on a database server could cause downtime for an application or a business. Configuring replication is thus the right practice to ensure that there is an option to perform failover in the event of disasters.

• Setting up Streaming Replication – guides you through setting up a streaming replication deployment.
• Setting up Logical Replication – guides you through setting up a logical replication deployment.
• Using pg_rewind to re-synchronize a demoted master – introduce you, how the old master that has diverged from the replication cluster can be quickly added back to the cluster as a standby.
• Automatic failover using repmgr – show you how to set up and configure repmgr for automatic failover in PostgreSQL.
• High Availability using Virtual IP – show you how to achieve high availability for PostgreSQL using virtual IP.
• High Availability with Pacemaker and Corosync – guides you through the process of setting up a high-availability cluster using Pacemaker and Corosync on Linux.
• Automatic failover with PAF – introduce you the PostgreSQL Automatic Failover (PAF) solution by ClusterLabs.
• Synchronize data to Elasticsearch using Logstash – guides you through setting up a Logstash deployment, to synchronize Elasticsearch with a PostgreSQL database.

Section 8. Troubleshooting

• Troubleshooting deadlocks – guide you on how to troubleshooting deadlocks in PostgreSQL.
• Dealing with corrupted blocks – guide you on how to troubleshooting the corrupted blocks in PostgreSQL.
• Dealing with corrupt TOAST values – guide you on how to troubleshooting the corrupt TOAST values in PostgreSQL.
• Dealing with statistics corruption – guide you on how to troubleshooting the statistics corruption in PostgreSQL.
• Dealing with PL/pgSQL runtime errors – guide you on how to troubleshooting the PL/pgSQL runtime errors in PostgreSQL.
• How does page caches affect query performance? – guide you on how to troubleshooting the page caches in PostgreSQL.
• Dealing with transaction wraparound – guide you on how to troubleshooting the transaction wraparound in PostgreSQL.
• Examining backend memory usage – guide you on how to troubleshooting the backend memory usage in PostgreSQL.
• Logging backtrace for errors in functions – guide you on how to log backtrace for errors in specified functions.
• Reasons Partition Pruning Not Work – introduce you to the major reasons why the partition pruning not work in PostgreSQL.
• Dealing with WAL accumulation in pg_wal directory – guide you on how to troubleshooting WAL accumulation in the pg_wal directory in PostgreSQL.
• Troubleshooting low free memory on server – guide you on how to troubleshooting low free memory on PostgreSQL server.
• Troubleshooting low cache hit ratio – guide you on how to troubleshooting low cache hit ratio in PostgreSQL.
• Dealing with the ‘xmin before relfrozenxid’ error – guide you on how to troubleshooting the error “found xmin … from before relfrozenxid …”.
• Troubleshooting Parallel Query – introduce you to the major reasons why the parallel query not work in PostgreSQL.
• Dealing with error ‘could not resize shared memory segment’ – guide you on how to troubleshooting the error “could not resize shared memory segment”.
• Dealing with disk full on server – guide you on how to troubleshooting the disk full issue on PostgreSQL server.

Section 9. Upgrade PostgreSQL

• Check PostgreSQL Version – introduce you various ways to check the PostgreSQL version on your system.
• Minor Version Update – show you how to perform a minor version upgrade in PostgreSQL.
• Major version upgrade using pg_upgrade – show you how to perform a major version upgrade using pg_upgrade.
• Online Upgrade – show you how to upgrade a major version of PostgreSQL online.

Section 10. PostgreSQL Tips

• Reset Password – show you how to reset the forgotten password of the postgres user.
• psql Commands – give you the most common psql command to help you query data from PostgreSQL faster and more effectively.
• Describe Table – get information on a particular table.
• Show Databases – list all databases in the current database server.
• Show Tables – show all tables in the current database.
• Moving pg_wal directory – move the pg_wal directory that stores WAL segments to a different disk or a faster disk.
• Reset Cumulative Statistics – introduce you what the pg_stat_reset function does, when and why to use it, and the impact it has on your PostgreSQL database.
• Configuring a connection pool – introduce you what a connection pooler is, and how to configure it.
• Understand Freezing – show you what is the freeze in PostgreSQL.
• Managing Freezing – show you how to manage freezing in PostgreSQL.
• Enable Data Checksums With Minimum Downtime – show you how to enable data checksums with minimum downtime in PostgreSQL.
• Controlling resource consumption using Linux cgroup2 – show you how to control resource consumption on a PostgreSQL server using Linux cgroup2.