Overview

In this section we will discuss several things you can do with postgresql in terms of management. Let's start with user management:

User & Role Management

Every role has a unique name and also an OID value, which represents the role as a numerical value. This is similar to how users are represented in the Unix system (and many others) where the numerical value of a role is used only internally.

User management in postgresql is quite different than other databases. In PostgreSQL, you should remember one thing, EVERYTHING IS ROLE. You have roles which can connect (e.g. users) and roles which cannot connect (groups). You can nest roles one into another and thus granding privileges and groups to other users.

In order to create a user in PostgreSQL, you have to:

Create the role with password

Allow the role in the pg_hba.conf

So let's get going:

User Management

Again, user is a role which can connect and has password, that is the only difference between user and group in PostgreSQL. So to create a user we can use the following:

Create a User

Grant role

Password

To reset the password, we can use the following command:

Reset password

Roles

List Roles

List Users

List Users with their roles

Database Management

When it comes to database management, there are MANY possibilities of what you want to do. Let's see how we can dump data to CSV:

Dump tp CSV

We can use the COPY tool from WITHIN the database as follows:

Copy data to CSV

That will execute teh SQL statement and put the data into a CSV file with the name of: “out.csv” and it will put the header on the top

Clean pg_wal folder

Now, to clean pg_wal folder, we need to back them up. Assuming, we have done that OR that we don't care, we can use the following process:

Find the latest checkpoint

Find latest checkpoint file

Clean all WAL files after that checkpoint

Before officially cleaning we can do a dry run:

Dry run

Then we can do the actual delete:

Clean WAL Files