PostgreSQL Administration

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

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 – remove a database permanently using the DROP DATABASE statement.
Copy a Database – copy a database within a database server or from one server to another.
Get Database Object Sizes – introduce you to various handy functions to get the database size, table, and indexes.

Section 2. Managing Schemas

Schema – introduce the schema concept and explain 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 change 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 is stored in the file system. The tablespaces are very useful in many cases such as managing large tables and improving database performance.

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.
Delete Tablespaces – learn how to delete tablespaces by using DROP TABLESPACE statement.

Section 4. Roles & Privileges

PostgreSQL represents user accounts as roles. Roles that can log in are 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 the concept of the role and show you how to create roles and groups.
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.
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 one with dependent objects.
Role membership – learn how to create group roles to manage role membership better.
SET ROLE – show you how to temporarily switch the current role to one of its group roles using the SET ROLE statement.
CURRENT_USER – discover how to get the currently logged-in user and show you the difference between current_user and session_user.
List roles – show you how to list all roles on the PostgreSQL server.
Superuser – learn about a special role called superuser 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.

Section 5. Backup & Restore Databases

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

Backup – introduce you to practical ways to perform a logical backup of a database or all databases in a PostgreSQL cluster using the pg_dump and pg_dumpall tools.
Restore – show how to restore a PostgreSQL database from an archive file using the pg_restore tool.

Section 6. 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.