PostgreSQL Administration

The PostgreSQL administration covers the most important PostgreSQL database server administration activities. We will discuss roles and groups administration, tablespaces management, databases backup and restore.

PostgreSQL Roles

In PostgreSQL, a role is an account. A role that has login right is called a user. A role may be a member of other roles. A role that contains other roles is known as a group. In this section, you will learn how to manage roles and groups effectively.

• PostgreSQL Roles Management: introduces you to PostgreSQL roles concept and shows you how to create user roles and group roles by using PostgreSQL  CREATE ROLE statement.

Backup and 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 – introduces you to practical ways to back up your databases by using PostgreSQL backup tool including pg_dump and pg_dumpall.
• Restore –  shows you various ways to restore PostgreSQL databases by using psql and pg_restore tools.

Tablespace Management

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. In this section, we will show you how to manage tablespaces in PostgreSQL effectively.

• Creating Tablespace – introduces you to PostgreSQL tablespace and shows you how to create tablespaces by using CREATE TABLESPACE statement.
• Changing Tablespace – shows you how to rename, change owner and set the parameter for a tablespace by using ALTER TABLESPACE statement.
• Delete Tablespaces – learns how to delete tablespaces by using DROP TABLESPACE statement.

PostgreSQL Tips

• Reset Password – shows you how to reset forgotten password of the postgres user.
• psql Commands – gives you the most common psql command to help you query data from PostgreSQL faster and more effective.
• Describe Table – gets information on a particular table.
• Show Databases – lists all databases in the current database server
• Show Tables – shows all tables in the current database.