September 26, 2023
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
- Alter Database – modify the features of an existing database using the
- Rename Database – change the name of the database to a new one.
- Drop Database – removes a database permanently using the
- 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.
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.
- Creating Tablespaces – introduce you to PostgreSQL tablespaces and shows you how to create tablespaces by using
- Changing Tablespaces – show you how to rename, change owner and set the parameter for a tablespace by using
- Delete Tablespaces – learn how to delete tablespaces by using
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
- 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 ROLEstatement 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.
- List user roles – show you how to list all roles on the PostgreSQL server.
Section 5. Backup & Restore Databases
This section shows you how to use various PostgreSQL backup and restore tools including
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
- Restore a database backed up using pg_dump – show you various ways to restore PostgreSQL databases by using
- Backing up a database cluster using pg_basebackup – show you how to use
pg_basebackupto back up your database cluster.
- Restore a backup taken using pg_basebackup – show you how to restore a backup taken using
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.