PostgreSQL Administration

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

PostgreSQL database and schema management

In this section, we will show you how to manage databases in PostgreSQL including creating databases, modifying existing database’s features and deleting databases. In addition, the schema management is covered.

• Create a new database – create a new database using CREATE DATABASE statement.
• Modify an existing database – modify the features of an existing database using the ALTER DATABASE statement.
• Drop a database – removes a database permanently using  DROP DATABASE statement.
• Copy a PostgreSQL database – copy a database within a database server or from a server to another.
• Get database object sizes – introduces you various handy function to get the size of a database, a table, indexes, etc.

PostgreSQL roles administration

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.

PostgreSQL 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.

• PostgreSQL Backup – introduces you to practical ways to backup your databases by using PostgreSQL backup tool including pg_dump and pg_dumpall.
• PostgreSQL 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.

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

PostgreSQL tips

• PostgreSQL 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.
• PostgreSQL Describe Table – gets information on a particular table.
• PostgreSQL Show Databases – lists all databases in the current database server
• PostgreSQL Show Tables – shows all tables in the current database.