In this section we will discuss several things you can do with postgresql in terms of management. Let's start with user 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:

1. Create the role with password
2. Allow the role in the pg_hba.conf

So let's get going:

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 ROLE jcaruana  with password 'yxKEt045ZKcWiiu2YBxJ' INHERIT LOGIN;

grant dev to jcaruana;
grant select on all tables in schema public to dba;

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

ALTER USER integration_playson WITH PASSWORD 'xjaR3IvMDXF9NvPHwVSe';

forumdb=> SELECT * FROM pg_roles WHERE rolname = 'luca';
-[ RECORD 1 ]--+---------
rolname        | luca
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcanlogin    | t
rolreplication | f
rolconnlimit   | 1
rolpassword    | ********
rolvaliduntil  |
rolbypassrls   | f
rolconfig      |
oid            | 16390

forumdb=# \x
Expanded display is on.
forumdb=# SELECT * FROM pg_authid WHERE rolname = 'luca';
-[ RECORD 1 ]--+------------------------------------
oid            | 16390
rolname        | luca
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcanlogin    | t
rolreplication | f
rolbypassrls   | f
rolconnlimit   | 1
rolpassword    | md5bd18b4163ec8a3ad833d867a5933c8ec
rolvaliduntil  |

forumdb=> SELECT r.rolname, g.rolname AS group,
                 m.admin_option AS is_admin
          FROM pg_auth_members m
               JOIN pg_roles r ON r.oid = m.member
               JOIN pg_roles g ON g.oid = m.roleid
          ORDER BY r.rolname;
  rolname   |        group         | is_admin
------------+----------------------+----------
 enrico     | book_authors         | f
 enrico     | forum_admins         | f
 luca       | forum_stats          | f
 luca       | book_authors         | f
 pg_monitor | pg_read_all_settings | f
 pg_monitor | pg_read_all_stats    | f
 pg_monitor | pg_stat_scan_tables  | f
 test       | forum_stats          | f
(8 rows)

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:

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

hunter_dev=# \copy (SQL STATEMENT) to 'out.csv' with csv header

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

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:

[root@s24-se-db01 pg_wal]# /usr/pgsql-11/bin/pg_controldata -D /var/lib/pgsql/11/data/
pg_control version number:            1100
Catalog version number:               201809051
Database system identifier:           6742286774524545149
Database cluster state:               in production
pg_control last modified:             Fri 21 May 2021 21:37:51 CEST
Latest checkpoint location:           B6D/62D62990
Latest checkpoint's REDO location:    B6D/62941780
Latest checkpoint's REDO WAL file:    0000000100000B6D00000062                    <- This entry
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:774839045
Latest checkpoint's NextOID:          300868
Latest checkpoint's NextMultiXactId:  75180307
Latest checkpoint's NextMultiOffset:  30502311
Latest checkpoint's oldestXID:        600002360
Latest checkpoint's oldestXID's DB:   16406
Latest checkpoint's oldestActiveXID:  760450727
Latest checkpoint's oldestMultiXid:   62073622
Latest checkpoint's oldestMulti's DB: 16407
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Fri 21 May 2021 21:35:46 CEST
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    logical
wal_log_hints setting:                off
max_connections setting:              1500
max_worker_processes setting:         28
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996

Before officially cleaning we can do a dry run:

[root@s24-se-db01 pg_wal]# /usr/pgsql-11/bin/pg_archivecleanup -n /var/lib/pgsql/11/data/pg_wal/ 0000000100000B6D00000061
[root@s24-se-db01 pg_wal]#

Then we can do the actual delete:

[root@s24-se-db01 pg_wal]# /usr/pgsql-11/bin/pg_archivecleanup -d /var/lib/pgsql/11/data/pg_wal/ 0000000100000B6D00000061
pg_archivecleanup: keeping WAL file "/var/lib/pgsql/11/data/pg_wal//0000000100000B6D00000061" and later
pg_archivecleanup: removing file "/var/lib/pgsql/11/data/pg_wal//0000000100000B6A0000008C"
pg_archivecleanup: removing file "/var/lib/pgsql/11/data/pg_wal//0000000100000B6400000053"
pg_archivecleanup: removing file "/var/lib/pgsql/11/data/pg_wal//0000000100000B5400000008"
pg_archivecleanup: removing file "/var/lib/pgsql/11/data/pg_wal//0000000100000B550000007A"
pg_archivecleanup: removing file "/var/lib/pgsql/11/data/pg_wal//0000000100000B4C0000000E"
pg_archivecleanup: removing file "/var/lib/pgsql/11/data/pg_wal//0000000100000B65000000E1"
pg_archivecleanup: removing file "/var/lib/pgsql/11/data/pg_wal//0000000100000B480000008F"
pg_archivecleanup: removing file "/var/lib/pgsql/11/data/pg_wal//0000000100000B61000000BC"
[root@s24-se-db01 pg_wal]#