PostgreSQL Database Administration

Section 1. Managing PostgreSQL Databases #

This section teaches you how to efficiently create, change, and manage databases.

• Create Database – Learn how to create a new database.
• Drop Database – Drop a database from the PostgreSQL server.
• Create Roles – Understand the concept of roles in PostgreSQL and how to create roles.
• Grant Privileges – Grant one or more privileges on a database object to a role.
• Alter Database – Change database settings, ownership, connection limits, and tablespace assignments of an existing database.
• Rename Database – Give you the exact steps to rename database safely and effectively.

Section 2. Managing PostgreSQL Schemas #

In PostgreSQL, schemas help organize database objects into logical groups. This section explores how to create, modify, and manage schemas effectively.

• PostgreSQL Schemas – Discover the concepts of schemas and learn how to use the to group database objects.
• Create Schema – Learn how to create a new schema.
• Alter Schema – Rename a schema or change the owner to a new one.
• Drop Schema – Drop one or more schemas and their objects.

Section 3. Managing PostgreSQL Tablespaces #

• Tablespaces – Learn about tablespaces and how to create and use them to store database objects including tables and indexes.
• Alter Tablespaces – How to rename, change the owner, and set or reset a tablespace’s parameters.
• Drop Tablespaces – Show you how to move database objects out of a tablespace and safely delete it

Section 4. PostgreSQL Roles & Privileges #

• Revoke Privileges – Remove previously granted privileges from a role.
• Alter Roles – Modify role’s attributes, rename a role, and set configuration parameters for a role.
• Role Membership – Manage role memberships for better access control.
• Drop Roles – Safely delete roles including their dependent objects.
• SET ROLE – Temporarily switch to a group role to perform specific tasks.
• Row-Level Security (RLS) – Implement RLS to restrict access to specific rows based on user-defined conditions, enhancing data security.
• PostgreSQL Password File – Learn how to store connection parameters securely using a password file and use it to automatically connect to PostgreSQL.

Section 5. Backup & Restore PostgreSQL Databases #

This section shows you how to back up and restore PostgreSQL databases.

• pg_dump – Export a single PostgreSQL database to a file.
• pg_dumpall – Export all databases and other global objects (roles, tablespaces, and configuration parameters) of a PostgreSQL instance to a single SQL file.
• Copy a PostgreSQL Database – Show you step-by-step how to copy a PostgreSQL database within the same database server or migrate it to another database server.