This is an old revision of the document!
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
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
CREATE ROLE jcaruana with password 'yxKEt045ZKcWiiu2YBxJ' INHERIT LOGIN;
Grant role
grant dev to jcaruana; grant select on all tables in schema public to dba;
Password
To reset the password, we can use the following command:
Reset password
ALTER USER integration_playson WITH PASSWORD 'xjaR3IvMDXF9NvPHwVSe';
Roles
List Roles
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 |
List Users
List Users with their roles
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)
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
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
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
[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
Clean all WAL files after that checkpoint
Before officially cleaning we can do a dry run:
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:
Clean WAL Files
[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]#