PostgreSQL Database Management- DBA Basics

Management of user, role, schema, database, tablespace, tables, and autovacuum

In the previous article, I have shown all the necessary steps to install PostgreSQL 12 on the Linux system for a single development or learning PostgreSQL environment. In the article first, I will go through PostgreSQL user, role and schema management and then I will go through Database, Tablespace, Table and Vacuum process management.

Part 1. user, role and schema management

PostgreSQL Basics — Part1

Summary commands that I used on this video are as below -

Roles [ Create | List | Drop]

create Roles
+++++++
postgres=# CREATE ROLE DEMODBA1 WITH LOGIN PASSWORD ‘password13’;
postgres=# CREATE ROLE oracledba;

List of roles
+++++++
postgres=# \du+

Drop Roles
+++++++
postgres=# DROP ROLE oracledba;

User [Create | List | Drop]

create User
++++++
User with full access
postgres=# create user pgdbadmin in role oracledba;

List of users
+++++++
postgres=# \du+

Drop User
+++++++
postgres=# drop user pgdbadmin;

Schema [Create | List | Drop]

create Schema
++++++++
User with full access
postgres=# create Schema pgdbadmin1;

List of Schema
++++++++
postgres=# \dn+

Drop Schema
+++++++++
postgres=# drop Schema pgdbadmin;

Part 2: Database, Tablespace, Table, and Vacuum management.

PostgreSQL DBA Basics — Part2

Summary commands that I used on this video are as below -

Database [Create | List | Rename | Drop ]

Create Database
+++++++++
postgres=# CREATE DATABASE portaldbtst OWNER liferay7de TABLESPACE pg_default;

List of Database
+++++++++
postgres=# \l+

Rename Database
+++++++++++++++++
postgres=# alter database “demodba1” rename to “demodb2”;

Connection to a specific database
++++++++++++++++++++
postgres=# \c demodb2

Drop Database
++++++++++++
postgres=# DROP DATABASE demodb2;

Validate
+++++++++++++
postgres=# \l+

List of tablespace
+++++++++++
postgres=# \db+

Tablespace [Create | List | Describe | Drop a table]

Create a new tablespace
++++++++++++++
Note: Avoid to create under data_directory, it will create tablespace with a message

mkdir -p /var/lib/pgsql/tbs

postgres=# create tablespace testtdbs location ‘/var/lib/pgsql/tbs’;

Move database to different tablespace
+++++++++++++++++++++
postgres=# alter database dbadb tablespace testtdbs;

Modify Default tablespace
+++++++++++++++++++++++++++++
postgres=# alter database dbadb tablespace pg_default;

Drop newly created tablespace
+++++++++++++++++++++++++++++
postgres=# drop tablespace testtdbs;

Tables [Create | List | Describe | Drop a table]

Create a table
++++++++++++++++++
postgres=# create table testdba (col1 varchar);
postgres=# create table dedlktst (code varchar, region varchar, population integer);

List of tables
++++++++++
postgres=# \dt+

Insert data to tables
++++++++++++++++++++++
postgres=# create table dedlktst (code varchar, region varchar, population integer);
postgres=# insert into dedlktst values (‘BNG’, ‘South Asia’, ‘5000’);

Describing a particular table
++++++++++++++++
postgres=# \d dedlktst

Query a particular table
++++++++++++++++
postgres=# SELECT code,region,population FROM dedlktst WHERE code IN (‘BNG’,’AUS’);

Drop a table
++++++++
postgres=# drop table testdba;

Validate
++++++++++++
postgres=# \dt+

Tables — List of relations
++++++++++++++

postgres=# \dt *.*

Autovacuum

Get the autovacuum launcher process
++++++++++++++++++++
$ ps -eaf | egrep "/post|autovacuum"
—
postgres 83571 83564 0 12:31 ? 00:00:00 postgres: autovacuum launcher process

Vacuum status
++++++++++++++++++
postgres=# show autovacuum;
autovacuum
------------
on
(1 row)

Disable Autovacuum on a Table
+++++++++++++++++++++
postgres=# create table test(col1 varchar);
CREATE TABLE
postgres=# ALTER TABLE test SET (autovacuum_enabled = false);
ALTER TABLE
postgres=# SELECT reloptions
postgres-# FROM pg_class
postgres-# WHERE relname = 'test';
reloptions
----------------------------
{autovacuum_enabled=false}
(1 row)

Database Level
+++++++++++
postgres=# SELECT set_config('autovacuum','off',false);
ERROR: parameter "autovacuum" cannot be changed now
We need to modify configuration file -

i) modify postgresql.conf file
autovacuum = off
ii) restart the database services
$ sudo /bin/systemctl restart postgresql-12
iii) validate
$ sudo su — postgres
$ psql
postgres=# show autovacuum;
autovacuum
— — — — — —
off
(1 row)

I hope this article will give you a very good understanding of how to manage user, schema, roles, tablespace, tables, and vacuum processes into PostgreSQL database.